Trim: 170 x 244 mm c01.
indd 12/18/2014 Page 4
4 FINANCIAL FORECASTING, ANALYSIS, AND MODELLING
financial asset. Similar definitions exist on other financial websites like [Link],
Divestopedia, etc. Financial modelling is a general term that means different things to different
people. In the context of this book it relates to accounting and corporate finance applications
and usually involves the preparation of detailed company-specific models used for decision-
making purposes and financial analysis. While there has been some debate in the industry as to
the nature of financial modelling – whether it is a tradecraft, such as welding, or a science – the
task of financial modelling has been gaining acceptance and rigour over the years.
Financial models can differ widely in complexity and application: some are simple 1-page
sheets built to get a “quick-and-dirty” estimate of next year’s net income. Some span more
than 40 worksheets and project various scenarios of the value of a company.
Although financial models vary in scope and use, many share common characteristics.
For example:
1. Reported financials for past years are the basis for most projection models. To fore-
cast financial statements we make use of key performance drivers derived from historical
records.
2. Projecting future years for the 3 main financial statements – the income statement, the
balance sheet, and the cash flow statement – is typically the first step. Income statement
estimates for EBITDA and interest expense as well as balance sheet leverage statistics
such as debt/equity and interest coverage are often the most important model outputs.
3. Incorporating financial statement analysis through the use of ratios. More often prof-
itability, liquidity, and solvency ratios are calculated in order to pinpoint any weaknesses
in the financial position of a company.
4. Performing valuation. Valuation involves estimating the value of a company using vari-
ous techniques although the most commonly used are comparable company multiples and
discounted cash-flow modelling.
5. Conducting various forms of sensitivity analysis after a forecast model has been
built. These analyses are often the real reason a model was built in the first place. For
example, sensitivity analysis might be used to measure the impact on one model output –
say free cash flow – from the changes of one or more model inputs, say revenue growth
or the company’s working capital needs (“What happens to free cash flow if we increase
sales growth by an extra 2% next year and at the same time reduce the payment terms to
the suppliers by 5 days?”).
Financial modelling is about decision-making. There is always a problem that needs to be
solved, resulting in the creation of a financial model.
Financial modelling is about forecasting. In the post 9/11 environment, forecasting has
become much more difficult because the economic environment has become much more vola-
tile. Since profit is not the only important variable, a projected financing plan into the future is
imperative for a business to succeed.
Financial modelling is the single most important skill-set for the aspiring finance
professional. It is as much an art as a science. Financial modelling encompasses a broad range
of disciplines used across many fields of finance. A good financial modeller must first of all
have a thorough understanding of Generally Accepted Accounting Principles (GAAP) and the
statutory accounting principles. They must know how the 3 financial statements work and how
these are linked together. They need to know corporate finance theory and be able to apply it
in valuation exercises. They will have to be adequate in forecasting. Finally, they will have to
think analytically, be good at business analysis, possess industry-specific knowledge and, last
Trim: 170 x 244 mm [Link] 12/18/2014 Page 5
Introduction 5
but not least, have strong Excel skills. The applications of the above skill-sets are immense
and somebody can develop them by applying and also by practising them.
In this book we look at the basics of most of these disciplines. In Chapter 2 we cover
the fundamentals of accounting theory and the interrelationship of the 3 financial statements.
In Chapter 3 we apply this theory in practice to build the proforma financial statements of a
sample company of interest. In Chapter 4 we examine various forecasting techniques related
to sales, costs, capital expenditures, depreciation, and working capital needs. In Chapter 5 we
cover the theory behind Discounted Cash Flow (DCF) valuation.
During the financial crisis, the G20 tasked global accounting standard-setters to work
intensively towards the objective of incorporating uncertainty into International Financial
Reporting Standards (IFRS) (e.g. favourable and unfavourable scenarios are requested in esti-
mating the fair value of an investment). In addition businesses are asked to prepare various
scenarios in order to prove that they will be financially viable into the future and thus secure
funding from their lenders or raw materials from their suppliers. Chapters 6, 7, and 8 deal with
these types of uncertainty. Chapter 6 deals with sensitivity analysis, Chapter 7 elaborates on
building multiple scenarios, and Chapter 8 introduces the Monte Carlo simulation and deals
with building up a simulation model from scratch.
For the Finance and the Accounting professional in corporate finance and investment
banking, financial modelling is largely synonymous with cash-flow forecasting and is used to
assist the management decision-making process with problems related to:
▪ Historical analysis of a company
▪ Projecting a company’s financial performance
▪ Business or security valuation
▪ Benefits of a merger
▪ Capital budgeting
▪ Scenario planning
▪ Forecasting future raw material needs
▪ Cost of capital (i.e. Weighted Average Cost of Capital (WACC)) calculations
▪ Financial statement analysis
▪ Restructuring of a company.
The same applies to the equity research analyst or the credit analyst, whether they want to
examine a particular firm’s financial projections along with competitors’ projections in order
to determine if it is a smart investment or not, or to forecast future cash flows and thus deter-
mine the degree of risk associated with the firm.
Furthermore, for the small business owner and entrepreneur who would like to project
future financial figures of his business, financial modelling will enable him to prepare so-called
proforma financial statements, which in turn will help him forecast future levels of profits as
well as anticipated borrowing.
Finally, as more and more companies become global through the acquisition/establish-
ment of international operations, there is an imminent requirement for sophisticated financial
models. These models can assist the business/financial analyst in evaluating the performance
of each country’s operations, standardize financial reporting, and analyze complex infor-
mation according to the various industry demand–supply patterns.
Financial modelling, unlike other areas of accounting and finance, is unregulated and lacks
generally accepted practice guidelines, which means that model risk is a very real concept. Only
recently certain accounting bodies, such as the Institute of Chartered Accountants in England
Trim: 170 x 244 mm [Link] 12/18/2014 Page 6
6 FINANCIAL FORECASTING, ANALYSIS, AND MODELLING
and Wales (ICAEW), published principles for good spreadsheet practice based on the FAST
Standard which is one of the first standards for financial modelling to be officially recognized.4
The FAST (Flexible Appropriate Structured Transparent) Standard is a set of rules on the struc-
ture and detailed design of spreadsheet-based models and provides both a clear route to good
model design for the individual modeller and a common style platform upon which modellers
and reviewers can rely when sharing models amongst themselves.5 Other standards include
SMART, developed by Corality, which provides guidance on how to create spreadsheets with
consistency, transparency, and flexibility6 and the Best Practice Modelling (BPM)7 published by
the Spreadsheet Standards Review Board (SSRB).8 Nevertheless the above standards have not
yet been widely adopted and the reader should be aware of the scope, benefits, and limitations
of financial modelling. Always apply the “Garbage in Garbage out” principle.
1.2 DEFINING THE INPUTS AND THE OUTPUTS OF A SIMPLE
FINANCIAL MODEL
A good model is easily recognizable. It has clearly identifiable outputs based on clearly defined
inputs and the relationship between them can be tracked through a logical audit trail. Consider
the following situation. Think of a wholesale company that wants to use a financial model to
assess the financial implications of its credit policy. Let us say that the company has a 2-term
trade credit agreement. In this agreement it offers a discount to its buyers if payment is made
within a certain period, which is typically shorter than the net payment period. For example, a
“2/10 net 30” agreement would give the buyer a discount of 2% if payment is realized by the
10th day following delivery. If the buyer fails to take advantage of the discount, there are still
20 additional days in which to pay the full price of the goods without being in default, that is,
the net period has a total duration of 30 days. Finally, as with net terms, the company could
charge penalties if the buyer still fails to meet the payment after the net term has expired. It is
expected that 30% of the company’s buyers would adopt the discount. Trade credit can be an
attractive source of funds due to its simplicity and convenience. However, trade credit is like a
loan by the company to its customer. There are 2 issues associated with loans: (a) what is the
necessary amount of the loan and (b) what is the cost of it?
Therefore, the company needs to build a model in order to estimate:
(a) the cost of the trade credit it provides to its customers, and
(b) the funding impact of it, on the basis that 70% of the company’s customers will not adopt
the discount,
given that it has an annual turnover of €10,000,000.
So the model outputs should look like this:
Cost of the Discount
Effective Annual Rate (EAR) :
In absolute terms (€):
Funding impact of the credit period
Funding needs (€):
Cost of funding per year (€):
Trim: 170 x 244 mm [Link] 12/18/2014 Page 7
Introduction 7
The Effective Annual Rate (EAR) is the cost of the discount offered by the company to
encourage buyers to pay early and is given by the following formula:
Discount percent 360
EAR = ×
(100 − Discount percent ) (Days credit is outstanding − Discount period )
As far as the above situation is concerned:
2 360
EAR = × = 36.7%
100 − 2 30 −10
This cost is really high and means that the company offering the discount is short of cash.
Under normal circumstances it could get a bank loan much more cheaply than this. On the buyer
side, as long as they can obtain a bank loan at a lower interest rate, they would be better off
borrowing at the lower rate and using the cash proceeds of the loan to take advantage of the dis-
count offered by the company. Moreover, the amount of the discount also represents a cost to the
company because it does not receive the full selling price for the product. In our case this cost is:
(1−70%) × €10,000,000 × 2% = €60,000
Apart from the above cost, if we assume that the company’s customers would wait until
the last day of the discount period to pay, i.e. the 10th day, then the company should fund 10
days of receivables for turnover equal to:
(1−70%) × €10,000,000 × (1 − 2%) = €2,940,000
The factor (1–2%) takes into account the discount. These 10 days of receivables, assum-
ing a 360-day financial year, are equal to the following amount (as we will see in Chapter 2):
10 days × €2,940,000/360 = €81,667
If the €81,667 are financed by debt and the cost of debt is 8% per year, then the company
will bear interest of:
8% × €81,667 = €6,533/year
That is, the company will bear a cost of €60,000 per year arising from the discount of 2%
plus a further cost of €6,533 as interest arising from the funding needs of the 10-day credit
period.
Concerning the 70% of the company’s customers that prefer the credit period of 30 days,
this is equivalent to turnover of:
70% × €10,000,000 = €7,000,000
This turnover, if funded for 30 days, gives rise to receivables equal to:
30 days × €7,000,000/360 = €583,333.
Again, if the €583,333 are financed by debt and the cost of debt is 8% per year, then the
company will bear interest of:
8% × €583,333 = €46,667/year.
Trim: 170 x 244 mm [Link] 12/18/2014 Page 8
8 FINANCIAL FORECASTING, ANALYSIS, AND MODELLING
To summarize: the company will bear a cost of €60,000 per year arising from the discount
of 2% plus a further cost of €6,533 as interest arising from the funding needs of the 10-day
credit period plus another cost of €46,667 as interest arising from the funding needs of the
30-day period.
All the numbers that feed into the above formulae should form the inputs of the model
and all the formulae will be part of the workings of the model as we discussed in the previous
paragraph.
Then, the inputs of the model should look like this:
Particulars UOM Values
Discount offered: (%) 2%
First term of credit: (days) 10
Second term of credit: (days) 30
Percentage of clients choosing to take the discount: (%) 30%
Company’s annual turnover: (€) 10,000,000
Company’s annual cost of debt: (%) 8%
and the outputs of the model will look like this:
Cost of the Discount
Effective Annual rate (EAR) : 36.7%
In absolute terms (€): 60,000
Funding impact of the credit terms
Funding needs (€): 665,000
Cost of funding per year (€): 53,200
where the funding impact of €665,000 is the sum of both the 10-day discount period and
the 30 credit days (€81,667 + €583,333) and €53,200 is the cost of these funds per year
at 8%.
So far you may have the impression that financial modelling is purely maths and finance.
However, for a model to be effective, precise financial calculations are not enough and are
only part of the equation. The second and equally important part is the appropriate application
of subjectivity. Financial models that combine both maths and art become the models that are
relevant and are actually used in business.9 In this direction we have used a common style for
the headings of both the inputs and the outputs. Moreover we could have used blue colour for
the inputs. We have used 3 columns to separate the particular inputs from their relevant unit of
measure (UOM) and their proposed value. We started by defining first the outputs of the model
that will answer the business question the model will need to address. Then we identified any
additional information required in order to complete the model (i.e. the cost of funds/debt for
the company). Only then did we write down all the particular formulae and calculations that
the model needs to perform.
Trim: 170 x 244 mm [Link] 12/18/2014 Page 9
Introduction 9
As a final note to this specific modelling exercise, we mentioned previously that there was
no need for any decision making. The model was constructed simply to enhance the business
understanding of a particular company policy. Should any decision need to be taken about
which credit policy is more efficient, we could model a number of different scenarios each
with various credit policies. For example we could examine 3 different policies (2/10 net 30,
2/10 net 45, and 2/10 net 60) in order to choose the most favourable one.
1.3 THE FINANCIAL MODELLING PROCESS OF MORE COMPLEX
MODELS
The financial modelling process is comprised of 4 steps as shown in Exhibit 1.1:
Step 3 Step 4
Steps
Step 1 Step 2
Design and build Check the model’s
Define the problem Specify the model
the model output
r %FGJOFUIFQSPCMFN r 1SPWJEFBTPMJEBOE r *EFOUJGZUIFPVUQVUT r $IFDLUIFNPEFMGPS
UIFNPEFMXJMMTPMWF UIPSPVHITQFDJGJDBUJPn PGUIFNPEFM FSSPST
r %FGJOFUIF PGUIFNPEFM r %FTJHOUIe r *OUSPEVDFDIFDLT
Key Actions
GVOEBNFOUBM r %PDVNFOUBOEPSHBOJ[F BTTVNQUJPOTTIFFU EJSFDUMZJOUIFGPSNT
CVTJOFTTRVFTUJPO UIFNBKPSBTTVNQUJPOT UIBUGPSNTUIFJOQVUT JFUIBUUIFCBMBODe
r "TLUIFSJHIU CZDBUFHPSJFT PGUIFNPEFM TIFFUCBMBODFT
RVFTUJPOTUP r %FDJEFUIFUJNFGSBNF r #VJMEUIFXPSLJOHT r $IFDLUIFNPEFMT
EFDPNQPTFUIF PGUIFNPEFMBOEJUT PSUIFDBMDVMBUJPn GVODUJPOBMJUZBOEJUT
QSPCMFNJOUPTJNQMFS HSBOVMBSJUZ 5JNFQFSJPET TIFFUT CVTJOFTTMPHJD
DPOTUJUVFOUT r (SPVQGJOBODJBM r ,FFQ0VUQVU *OQVU r *EFOUJGZSJTLGBDUPST
TUBUFNFOUTBDDPVOUTBT BOE8PSLJOHTTIFFUT r $IFDLUIFJOQVUTBOE
BQQSPQSJBUFGPSUIe TFQBSBUF UIFDBMDVMBUJPOTPGBMM
TQFDJGJDJOEVTUSZ GPSNVMBT
r %FDJEFUIF,FZ
1FSGPSNBODF*OEJDBUPST
,1*T UIBUOFFEUPCF
DBMDVMBUFEJOPSEFSUP
BEESFTTUIFQSPCMFNJO
UIFRVFTUJPO
r %FDJEFUIFVTFPGBOZ
TDFOBSJPPSTFOTJUJWJUZ
BOBMZTJT
EXHIBIT 1.1 The 4 fundamental steps of the financial modelling process
Let us examine each of the above steps in detail.
1.3.1 Step 1: Defining the Problem the Model Will Solve: The Fundamental Business Question
Financial modelling is used, as we mentioned previously, in order to solve various problems.
The first step of the process includes teams or individuals asking the right questions at the start
of the problem-solving process. This is sometimes hard to believe as it often seems that people
are trying to solve a problem before they have properly defined it. Asking the right questions
helps break down the problem into simpler constituents.
For example the commercial manager of the company requests the financial analyst to present
the impact on the bottom line results of the company of a New Product Development (NPD).