The Handbook of Financial Modeling: A Practical Approach to Creating and Implementing Valuation Projection Models

By Jack Avon

Financial modeling is a crucial concept for business leaders to understand and execute effectively, but few have the tools necessary to do so. While many professionals are familiar with financial statements and accounting reports, not many are truly proficient at building an accurate and practical financial model from the ground up. The Handbook of Financial Modeling provides these skills and so much more.

          Now in its second edition, The Handbook of Financial Modeling takes into account the new tech released since its successful initial release. Author Jack Avon uses his expertise to analyze the changes and improvements in industry-wide financial modeling through the past five years, in addition to instilling core concepts for readers of all experience levels. Approaching your company’s financial issues with a modeler’s perspective will transform and improve the rest of your business career’s trajectory.

          Financial professionals, students, business leaders, aspiring CFOs, and more will come away with all the tools necessary to precisely and efficiently monitor an organization’s assets and project future performance. The engaging case studies and Avon’s expert analysis leave you prepared to monitor and predict your organization’s finances effectively. Financial modeling’s latest technology is at your fingertips, and this book’s deep understanding of the topic ensures that you stay ahead of the pack. 

What You Will Learn

• Approach financial issues and solutions from a modeler's perspective
  
• Discover the importance of thinking about end users when developing a financial model
• Plan, design, and build a fully functional financial model

Who This Book Is For

Analysts who would typically be middle management, VPs, and associates. It is also written for business graduates and MBA students.

• Language: English
• Publisher: Apress
• Release date: Nov 25, 2020
• ISBN: 9781484265406

Book preview

© Jack Avon 2021

J. AvonThe Handbook of Financial Modelinghttps://doi.org/10.1007/978-1-4842-6540-6_1

1. The Role of a Financial Modeler Today

Jack Avon¹  

(1)

Riberac, France

This chapter serves as background to financial modeling from the perspective of the individual person. There are possibilities now for individuals to enter a career as financial modeler. Many organizations will openly advertise job titles as financial modelers or business modelers.

Here, we can take a look at some real-world roles and examine their impact on financial modeling and also what they are looking for in a candidate.

The financial modeler

Financial modeling is a very specific discipline that often uses spreadsheets in the shape of Microsoft Excel. Typically, the financial modeler’s prime responsibility is to deliver a specified financial model. To deliver a financial model will require the modeler to perform several activities and tasks including

Evaluating the type of model required

Specifying and collecting the requirements of the to-be model

The design and build of the model

Validation of the data for the model

Testing and documenting of the model

Data validation and populating of the model

Security and handover

Depending on the requirements of the modeling project, a modeler could be expected to manage the financial modeling process—from the start of the project to when the model is delivered and in use. (This process is discussed in greater detail in Chapter 4.)

The backgrounds of financial modelers are varied. Once upon a time, it would have been safe to assume modelers were accountants; however, it would be a great injustice to other professions to make that assumption today. One of the reasons why the accounting profession has been a breeding ground for modelers is that much of financial modeling requires the modeler to have a good grasp of a large host of financial concepts. However, this is by no means the main requirement for being a professional financial modeler. Nowadays, the principal qualities required of financial modelers are less about professional standing and more about the commercial experience and technical abilities. To be successful as a modeler, individuals need to

Be profoundly analytical and questioning by nature

Be comfortable communicating to people of any background and at any level in an organization

Have an innate ability in identifying the key elements that drive any information output

Where financial modelers are being used

When we talk about financial modeling, we are really looking at quite a range of domains, including data analysis, financial management, project finance, business intelligence, and commercial business analysis. It’s more often than not that modelers will not necessarily have financial modeler in their job title. Instead, modeling will be a significant part of the role expectation.

Therefore, for financial modelers, roles will be disguised in other corporate-specific job titles. Many modelers can be found in areas such as

Investment analysis: Banking analysts and associates spend most of their time looking at mergers and acquisitions, raising capital, debt equity, and public offerings. There are many modelers who start out in this field.

Equity research analysis: These are analysts who routinely research industry trends and movements and also corporate trends. They make comparisons between corporations and seek to give a view on where future growth will come from. There is a heavy reliance on modeling skills in these roles.

Financial planning and analysis: The modelers are responsible for forecasting business performance and forecasting growth within an organization. This role is generally the preserve of accountants and modelers.

Venture capital: These modelers work on preparing and calculating the level of investments and returns in early-stage businesses. This is one area that is full of freelance modelers.

Insurance and actuarial: The modelers in this field are quite specialized. Most modelers who start out here will remain in insurance. There is a strong barrier to entry for many modelers because to be a modeler in an actuarial environment, one is required to be an actuary with several years of experience.

Real estate and asset management: These areas tend to use models heavily to forecast yields, amortization, cash flow, and payback benefits. This is a very popular area for freelance modelers working on a project-by-project basis. Modelers in this area tend to have several years of experience and have a very high professional indemnity insurance cover. This is an area where the risks of making mistakes can lead to litigation and therefore will be for modelers.

Project finance: Modelers assess the feasibility of a project from several different angles (not just cash flow return) and must have a very good understanding of financial concepts and financial regulatory requirements. This area attracts modelers from banking and also those who worked for the professional services firms.

Startups: There are modelers who specialize in working with startup organizations. These modelers usually are freelancers with several years of experience often in nonmodeling roles. By far, the trend for modelers in this area tends to be accountants.

A characteristic of the modelers who work within job areas listed is they are almost always business as usual (BAU) in manner as opposed to project work. BAU working is when an individual continues to produce their work regardless of the current circumstances. Project working is when an unusual circumstance arises which will have a specific start and end date.

A large proportion of financial modelers are freelancers working exclusively on projects. These freelance modelers work as consultants in just about every industry on specialist projects, working with one or more clients at the same time.

Modeling roles for the future

Although financial modeling is still in its infancy as a career path, based on demand there is clear evidence modelers will continue to be a requirement in business and commerce in the future. I would like to use just explore the idea of how financial modeling roles will look in the future.

I believe there will be a shift in the type of skills required in financial modeling. This movement is already started to happen but is at its early stages. There will be a move to more specialism based on types of businesses. The role financial modeler will all but disappear because every modeler will be expected to have the foundation in modeling and then a specialism.

The broader areas of specialism will be

Data and business intelligence

Expert systems

Commercial and business

Let me give you an example of a job title and job specification on each of the three domains.

Data and business intelligence possible job specification

The following is a list of hypothetical job specifications, and is not based on any current jobs:

Develop and maintain an end-to-end business intelligence process.

Collaborate with internal business users.

Drive requirements and specify the process.

Deliver reports and portable models across the business (financial modeling).

Create and maintain documentation (requirements, user manuals).

Identify and solve ad hoc business needs (financial modeling).

Business intelligence analysis.

Requirements

College or university degree in information technology, finance, or something similar

Data analysis and data integration skills including SQL and SSRS

Knowledge of technologies (Power BI, Tableau, QlikView, or something similar)

Excellent analytical and problem-solving skills

Advanced Excel and VBA

Prior experience in a financial modeling role

This role is reasonably general toward data analysis and business intelligence; however while currently financial modeling and business intelligence are separate skill sets, expect these to combine in the future. The role would likely be called business intelligence specialist, but would also mean financial modeling.

Expert systems’ possible job specification

Expert systems were very prominent in the early 1990s, and there were high expectations that in the future, everything would be based around these systems. The job specification would look something like this:

Design machine learning systems.

Develop machine learning applications and outputs according to business user needs (some financial modeling).

Understand the methods of selecting appropriate data set and data tools.

Seek requirements from the business communities and facilitate solutions.

Deliver reports and output models across the business (financial modeling).

Create and maintain documentation (requirements, user manuals).

Requirements

College or university degree in computer science, engineering, finance, or something similar

Postgraduate or professional membership in finance, IT, and engineering

Knowledge of data analysis principles and data governance

Advanced experience in technologies (C++, Python, R)

Knowledge of Java

Outstanding analytical and problem-solving skills (financial modeling)

Advanced Excel

Prior experience in a customer- or client-facing role

This role is heavily centered toward it and coding, but because it’s a business-facing role, there is slight dependency of analysis and problem solving which are very much part of financial modeling. Expect these types of hybrid roles to be common in the future as job roles begin to move across skill domains.

Commercial and business possible job specification

Financial modeling roles have already begun to move toward more commercial titles. As modeling becomes more accepted as a career path, expect the cross to become more pronounced. The job specification would look something like this:

Provide advice, capital raising, derivatives, and financial strategy.

Lead project workstreams.

Develop new business through building client relationships.

Have a direct relationship with clients while leading and engagement.

Be responsible for presenting financial models to clients and internally.

Create and maintain documentation (requirements, user manuals).

Requirements

College or university degree in finance, business administration, or something similar

Professional membership (ACA, ACCA, CFA, or something similar)

Be able to build models from scratch

Advanced experience technologies (Excel, VBA)

Preferable experience in project finance, debt models, BPO outsource bids, service pricing models

Minimum of 7 years in financial modeling

This role is somewhat available today in the professional services firms. However, I expect in time it will move to be a general modeling role across most businesses and will become the most prevalent financial modeling role. Notice how such a role requires a lead time of 7 years of experience. This experience is not unusual for commercial heavy roles.

Conclusion

This chapter is a walk into the future to see what financial modelers can expect from their career. Although there is some speculation on some aspects of these roles, most of the prediction is based on sound experience from the movements in industry. In Chapter 17, we will be using a case study of a real-life company to explore just how they are going to transition their modelers in the future.

© Jack Avon 2021

J. AvonThe Handbook of Financial Modelinghttps://doi.org/10.1007/978-1-4842-6540-6_2

2. Types of Financial Models

Jack Avon¹  

(1)

Riberac, France

Often when training financial modeling candidates, I am asked to describe the types of financial models. There will always be someone in the training group who will point out a model type that I have not heard about. This is hardly surprising because in actual fact, there are hundreds of types of models because a model can be whatever you want it to be.

All is not lost, though, as there are a number of model types that are used most frequently in organizations. These types of models have either come out of some form of industry standard or, in many cases, have been established by the financial modeling community as best practice models.

In this chapter, we will look at a few of these models and briefly summarize the model features, which are

Financial statements

Consolidation

Mergers and acquisition (M&A)

Leveraged buyout (LBO)

Budget and forecast models

Discounted cash flow (DCF)

This is not definitive of all financial types; however, it is safe to say these models cover more than 50% of financial models.

Financial statement models

The financial statement model is an output model which has the income statement, the cash flow, and the balance sheet linked together and dynamically driven by the inputs.

This model, while seemingly simple, does require a sound understanding of accounting concepts because of the relationship between the three statements.

Understanding the financial statements

There are several pitfalls to look for while building a financial statement model. The most damaging of the pitfalls is to understand the difference between cash basis and accrual basis.

Cash vs. accrual

In cash basis accounting, we record revenue in the accounts when we receive the cash. We also record expenses incurred when we pay for them. This means that on a cash basis system, there is no need to have accounts receivable (debtors) or accounts payable (creditors).

In the accrual basis, all revenues and expenses are recorded at the time when they accrue (not when they are paid or received). This means we will have accounts receivable and accounts payable. For example, imagine a storage company provide storing facility in January for its client. The company then sends an invoice in February to the client for January storage of $300 to be paid within 30 days.

The service has been performed (January storage), but the cash ($300) will be received between now and 30 days. The cash basis there is zero recorded for revenue in January. In accrual basis, the accounts would show revenue for January as $300.

It’s clear to see the accrual basis gives a realistic view of revenue and expense during the time periods that the services were performed. This concept of accrual basis has far-reaching implications for business and is subject to strict accounting rules. (We will cover these concepts in Chapter 9.)

What about cash flow?

The accrual basis does also have a downside. Due to the recording of movements based on when they are performed and not when cash is moved, the basis does not give a company a realistic view of its cash flow. Keep in mind that for a business, cash flow is the key to surviving.

This means when preparing accounts of an accrual basis, steps must be taken to create a cash flow statement outside of the accrual system.

All transactions related to revenues, costs, assets, and liabilities are reflected in the accounts for the period in which actual receipts or actual payments are made.

How the income statement, cash flow, and balance sheet are linked

Where the accounting knowledge comes into play is the understanding of how the three statements are linked. I will give a high-level view of this linkage, but I won’t explore the deeper accounting rationale.

Net earnings

Firstly, we need to understand the links between the income statement and balance sheet. The key here is that the result of the income statement, which is called the net earnings, net income, or net loss, must be reflected in the balance sheet as part of shareholders or owners’ equity.

Figure 2-1 shows how the income statement and balance sheet sit next to each other. See how the connection between the net earnings in the income statement is pulled into the retained earnings of the balance sheet.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig1_HTML.png

Figure 2-1

The income statement is linked with the net earnings to retained earnings

In essence, this is the amount of money that can be distributed to the owners of the business. For now, we won’t worry how the technical modeling of the net earnings and retained earnings is accomplished; we will be covering the modeling techniques through the book.

Depreciation and amortization

The next step is to link to the cash flow between the income statement and the balance sheet. Depreciation and amortization are capitalized items. This means they are not actual cash movements, but they are accounting adjustments. Therefore, we need to get them pulled out of cash flow calculations to get to our real cash amount as depicted in Figure 2-2.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig2_HTML.png

Figure 2-2

Depreciation from the balance sheet is reversed in the cash flow

You can see how in order to reflect the pulling out, the value type of the depreciation has been changed from negative in the income statement to a positive in the cash flow. Accountants will understand this as balancing between debit and credit in order to maintain a balanced set of accounts.

The working capital

The working capital are those elements of the balance sheet that fit into the current assets and the current liabilities. They tend to be elements that are in constant change day to day such as bank account, cash, and inventory accounts payables and receivables.

The cash flow and balance sheet are linked through working capital. We need to assess the movements (changes) in the balance sheet working capital between the previous period and the current period. This movement must be reflected in the cash flow in the current period as shown in Figure 2-3.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig3_HTML.png

Figure 2-3

The working capital movements are reflected in the cash flow

There are other linkages between income statement and cash flow concerning debt, interest, and taxation. I won’t go through these as they require complex accounting which is not part of this book. However, the concept of how to treat the debt, tax, and interest is similar to how we treated the working capital. The main difference being these elements also have varying schedules that must be applied based on set terms of conditions.

Getting the cash

Once the elements of retained earning, working capital, and any debt, interest, and taxation are dealt with, We should then have an opening and closing cash for each period. It is that closing cash that is then used in the balance sheet cash or bank account shown in Figure 2-4.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig4_HTML.png

Figure 2-4

The cash from the cash flow is placed into the bank account

There is a further technical problem that occurs when you link your closing cash to the balance sheet in that you will create a circular reference in the model. It is something you should avoid doing at all costs, as it creates a ripple effect in the model and is uncontrollable.

The circular reference is caused because in order to get to your cash, you need to know your working capital, which has cash in it. Once you have your cash, you put this back into your working capital.

The key to building a statement model is to keep a strict adherence to the structure. Financial statement models require a methodical and disciplined approach or else they will fail. A typical sign of a failed financial statement model is a modeler’s nightmare, the out-of-balance balance sheet. Trust me, you don’t want to be in a position of trying to correct an out-of-balance balance sheet.

Consolidation model

Consolidation models are in their basic form a set of models created within their own worksheet tab and then grouped together to form one combined output.

A feature of a good consolidation model is all the worksheets are structured in exactly the same manner with the same look and feel. The final group worksheet will also look the same.

The individual worksheets can represent a cost center, department, product, or service or even a company. What is important is that all the worksheets have some linkage so that a change in one will be reflected in the final group worksheet. Figure 2-5 shows a typical consolidation model with four companies and the consolidation.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig5_HTML.png

Figure 2-5

The consolidation of four companies

The consolidation is for the four company tabs. In Figure 2-6 and Figure 2-7, we can see Company1 and Company2. Notice both these worksheets look exactly the same structurally and are also the same as the consolidation in Figure 2-5.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig6_HTML.png

Figure 2-6

Company1

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig7_HTML.png

Figure 2-7

Company2

There are a number of modeling pitfalls to look out for when building consolidation models that must be thought through before the model is developed.

Watch the file size

Consolidating several worksheets can create a bulky excel file. Excel processing engine diminishes exponentially with the increase in file size. From experience, file sizes greater than 10MB can start to cause problems including lagging calculations, corrupt file, lack of memory, and others. This is whether the skills of the modeler will come into their own, in using techniques with formula construction to limit the file size. We will cover more about how to use the right combinations of formula in Chapter 11.

Complex links

There is a temptation when building a consolidation model to try and emulate the structure of the companies in exactly the same way they operate. Some thought will need to be applied because it’s fraught with problems.

Let’s take an example of creating a consolidation of the three financial statements income statement, cash flow statement, and balance sheet.

You can consolidate the income statements easily by just adding up of the income statement worksheet tabs. The problem starts when you try to consolidate the cash flow. The difficulty is how to take the closing cash and net earnings from individual companies into one consolidation, when there are also intercompany transfers involved. This situation does occur, and it will cause the model to become complex and confusing.

As a rule, I advise that when developing consolidations, only segment the operational parts (revenue and cost). The cash flow and balance sheet should be created just once treating all companies as one and consolidated.

Be clear about the outputs

Another issue with consolidated models is that once a model has been built and completed, the sponsor then decides they need more detailed balance sheet information at a company-by-company level, considering the balance sheet has been built at one consolidation.

For instance, the consolidated model may show the working capital as being £1m. However, with five companies, the sponsor decides they want to see each company’s working capital so that they can see which company has a weaker cash flow.

This is a nightmare scenario which can be avoided by getting confirmation from the sponsor at outset whether they need individual company balance sheets. If the sponsor does require an individual company detail, then you will need to create each company with income statement, cash flow, and balance sheet. Then, create an intercompany thread in each company and trace all the movements between the companies.

Once completed, link all the companies together into a consolidation; the check will be that the intercompany movements will zero out.

Mergers and acquisition models

Merger and acquisition models referred to as M&A models are specific purpose tools to evaluate the dilution of a merger between two or more companies, Company1 + Company2 = Merged Company, or an outright acquisition of one company and one or more target companies, Company1 = Company2 + Company 1.

There can be some confusion as to whether there is a merger or acquisition because in the end, both processes produce one consolidated company. So what is the distinction? This distinction is based on the process used to get to the final consolidated company.

It is a merger only when two or more companies come together through a mutual agreement to form a consolidated company, called an M&A process.

It is an acquisition if one company proposes to acquire another company with cash or shares.

Ironically in both a merger and acquisition, the process must be approved by shareholders of both the companies.

The M&A model nuisances

M&A models can be complex, and as a result, developing such models requires a good level of experience of the M&A process. Not all modelers will be able to build M&A models, because not all modelers understand the requirements of modeling in M&A. However, a modeler with a good base understanding of financial modeling would be able to learn how to develop an M&A model relatively quickly. There are six key steps required for the modeler to develop an M&A model:

1.

Create operational forecasts.

2.

Collect all assumptions.

3.

Create projections.

4.

Combine the companies.

5.

Make adjustments to the combined company.

6.

Accredit the deal.

Create operational forecast

In the first instance, the modeler will need to use assumptions about the future operations of the merging companies. To do this, the modeler commences the development of the financial model with a detailed operational forecast of both the target and the acquiring companies as in Figures 2-8 to 2-11.

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig8_HTML.png

Figure 2-8

The analysis of the acquiring company includes several operational schedules

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig9_HTML.png

Figure 2-9

The cash flow forecast of the acquiring company is just one of the schedules prepared by the modeler

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig10_HTML.png

Figure 2-10

The target company will also have forecast schedules prepared

../images/318276_2_En_2_Chapter/318276_2_En_2_Fig11_HTML.png

Figure 2-11

The income statement of the target company is just one of the schedules prepared by the modeler

The operational forecast should include the aspects of both companies such as

Income statement forecast

Cash flow forecast

Balance sheet forecast

Working capital forecast

Equity investments

Debt schedule

Depreciation and amortization schedule

Tax schedule

Outstanding shares

Build the assumptions

Once the operational forecast schedules have been completed, a number of assumptions can be made and verified such as

Make any accounting adjustment to the forecast.

What is the actual purchase price of the target company? (business valuation)

Will the acquisition be based on cash, shares, or both?

What will be the number of shares issued to the target company?

What will be the cash paid to the target company?

When will the acquisition take place, the specific timing?

What are the incidentals related to the consolidation of the companies and can they be quantified in money terms?

Build a forecast of the target and acquired company based on these assumptions.

Note

Valuations are performed by both the acquiring and target companies, using some well-established valuation methodologies such as the weighted average cost of capital (WACC), Perform terminal value and discounted cash flow model. These methodologies are quite technical and complex as they are models in their own right. This book will not go into any depth in these methodologies, but to say there is a great deal of information in the public domain about how to use these