Financial Modeling

Financial Modelling Oct 28, 2020

 Some questions….

• What is a model?

• What is a financial model?

Financial Modelling 2 Oct 28, 2020

 Definition

• Financial modeling is the task of building an abstract representation (a

model) of a real world financial situation. This is a mathematical model
designed to represent (a simplified version of) the performance of a
financial asset or portfolio of a business, project, or any other
investment.

• Models are simplified versions of the things they represent

• A valid model accurately represents the relevant characteristics of the

object or decision being studied

• So a large part of the art: is what is relevant and what can be abstracted
away

Financial Modelling 3 Oct 28, 2020

 Models

• Everyone uses models to make decisions.

• Types of models:

– Mental (arranging furniture)

– Visual (blueprints, road maps)

– Physical/Scale (aerodynamics, buildings)

– Mathematical (what we’ll be studying)

Financial Modelling 4 Oct 28, 2020

 Financial models-types and softwares

• Essentially the course is about mathematical models built on a

spreadsheet (such as excel) or to refine econometric models built on
statistical softwares (such as eviews / strata) “with an application on
Finance”. Former is how practitioners / professionals teach it while the
later is how academics teach it. ..in developed economies it is the middle
of the two

• The modern approach involves using propriety softwares (bloomberg,

operis, quantrix, @risk , palisade etc) or custom built softwares to model

• Yet more advanced users spend time in acquiring programming skills and
thus eventually code their models from scratch.

Financial Modelling 5 Oct 28, 2020

 Real purpose for course

• NOT to know specific models (yes that will be taught but as examples)

• To understand real world scenarios in way that one is able to study them
in a lab like environment for

 Planning

 Decision making

 Impact studies

 Valuation

 etc

Financial Modelling 6 Oct 28, 2020

 One way of looking at the purpose of models is
……….Valuation, Decision Making and Risk

Every major decision a company makes is in one way or another derived

from how much the outcome of the decision is worth. It is widely
recognized that valuation is the single financial analytical skill that
managers must master.

• Valuation analysis involves assessing

 Future cash flow levels, (cash flow is reality) and

 Risks in valuing those cash flows, whether it be the cash flow from
assets, debt or equity

• Measurement value – forecasting and risk assessment -- is a very

complex and difficult problem.

Financial Modelling 7 Oct 28, 2020

 Teaching Objectives of Model Construction

• The best and perhaps the only real way to learn modeling is under the tense
pressure of a real transaction – when a model must be created and audited
under a tight deadline.
• Oh wait …we can still do this . A tough project pressure!
• Notwithstanding this, the exercises and lecturers are intended to provide:
 A head start for those who have not created models and will have to
learn the hard way.
 Helpful ideas to experienced model builders in designing and structuring
more efficient, stable, transparent and accurate models.
• The discussion covers how to build a well structured financial model that
clearly delineates inputs, effectively presents key value drivers, uses separate
modules to organize various components, accurately computes cash flow that
is available to different debt and equity investors, and presents results of the
analysis that accurately display risks of the investment.

Financial Modelling 8 Oct 28, 2020

 Benefits of Models

• Economy - it is often less costly to analyze decision problems using

models.
• Timeliness - models often deliver needed information more quickly than
their real-world counterparts.
• Feasibility - models can be used to do things that would be impossible.
• Models give us insight & understanding that improves decision making.

Financial Modelling 9 Oct 28, 2020

 Model Objectives

Financial Modelling Oct 28, 2020

 Measurement of Risk in Financial Models

• The fundamental issue in any valuation problem is • Consider Investment Alternatives A and B, where
how to assess the risk of future cash flow A has a higher project IRR than B. Assume A has
projections. a return of 11% and B has a return of 9%.
 Financial theory
 Financial theory dictates that the CAPM • Project A or Project B would be selected through
should be used to compute the WACC,
that the un-levered beta should be used assessing the return on the projects relative to the
to estimate equity returns, that options weighted average cost of capital for each project.
pricing models should be used for credit If the WACC for A is 10% and for B is 9.5% then A
spreads, debt capacity and covenants. is selected. One must computed beta for each
 Mathematical Models investment.

 Mathematical models include beta

• Compute the distributions in cash flow of project A
adjustments for the CAPM, statistical and project B to equity holders. If the standard
models for credit analysis, Monte Carlo deviation is lower for project B, then assess the
simulation and value at risk. risk relative to the return.
 Practical Market Information
 Practical market information can be used
to gauge required equity returns, • Compute the achieved rate of return from the
required credit spreads, required ability to raise debt and then assess the return
financial ratios to achieve investment earned on equity. If the return on equity is greater
grade rating and other issues. for B then A, select project A.
 Direct Evaluation with Financial Models
 Use of financial models to directly
assess risks through sensitivity, scenario • Use judgments with respect to different variables
and simulation analysis. to evaluate different scenarios.

Financial Modelling 11 Oct 28, 2020

 A Financial Model is a Statistical Tool

• In developing a financial model, the basic thing you are doing is

summarizing a complex set of technical and economic factors into a
number (such as value per share, IRR or debt service coverage).
 Forecasting has become an essential tool for any business and it is
central to statistics -- in assessing value, credit analysis, corporate
strategy and other business functions, you must use some sort of
forecast.
 Some believe economic forecasting has limited effectiveness and
worse, is fundamentally dishonest because uncertain unanticipated
events such as the internet growth, high oil prices, sub-prime crisis,
falling dollar continually occur.
 The whole idea of modeling, like statistics, is quantification. If a
concept cannot be quantified, it is a philosophy. The fundamental
notion of statistics is presenting and summarizing information, this is
the same as a financial.

Financial Modelling 12 Oct 28, 2020

 Danger of Believing too Much in Models

• Alan Greenspan, Financial Times.

“The essential problem is that our models – both risk models and
econometric models – as complex as they have become – are still too simple to
capture the full array of governing variables that drive global economic reality. A
model, of necessity, is an abstraction from the full detail of the real world.”
• Nicholas Taleb:
In the not too distant past, say the pre-computer days, projections remained
vague and qualitative, one had to make a mental effort to keep track of them,
and it was a strain to push scenarios into the future. It took pencils, erasers,
reams of paper, and huge wastebaskets to engage in the activity. The activity of
projecting, in short, was effortful, undesirable, and marred with self doubt.
But things changed with the intrusion of the spreadsheet. When you put an
Excel spreadsheet into computer literate hands, you get projections effortlessly
extending ad infinitum. We have become excessively bureaucratic planners
thanks to these potent computer programs given to those who are incapable of
handling their knowledge.

Financial Modelling 13 Oct 28, 2020

 • Models can be used for structurable aspects of decision problems.

• Other aspects cannot be structured easily and require intuition and

judgment.

• Caution: Human judgment and intuition is not always rational!

Financial Modelling 14 Oct 28, 2020

 Framing Effects

• Refers to how decision-makers view a problem from a win-loss

perspective.

• The way a problem is framed often influences choices in irrational ways…

• Suppose you’ve been given $1000 and must choose between:

– A. Receive $500 more immediately

– B. Flip a coin and receive $1000 more if heads occurs or $0 more if

tails occurs

Financial Modelling 15 Oct 28, 2020

 • Now suppose you’ve been given $2000 and must choose between:
– A. Give back $500 immediately

– B. Flip a coin and give back $0 if heads occurs or give back $1000 if
tails occurs

Financial Modelling 16 Oct 28, 2020

 Financial Models

• A good financial model should:

 Be relatively simple
 Focus on key cash flow drivers
 Clearly convey assumptions and conclusions
 Evaluate Risks through sensitivity analysis, break-even analysis, scenario analysis
• Alternative Models
This is not easy but very
 Back of the Envelope important

 Check Overall Return on Model

 Check EV Relative to Cost of New Assets

 Deterministic
 Set a number of assumptions and translate into financial ratios and cash flow

 Stochastic
 Develop a range of possible inputs using Monte Carlo simulation. Used where there is a
good and predictable history for value drivers.

Financial Modelling 17 Oct 28, 2020

 Layout and Structure of Alternative Types
of Financial Models

A good model allows decision makers to focus on

appropriate risks and summarizes data in an efficient way –
the key valuation issues should pop out at you

Financial Modelling Oct 28, 2020

Different Model Types
• Literally hundreds of types exist. In the broadest sense models are made for the following. Do
take note that many are overlapping or existing in other categories with a different perspective

 Corporate Finance (lending, leasing, project finance, working capital etc)

 Investment banking ( private equity, Mergers & acquisitions, LBOs, IPOs , debt issues etc)

 Equities( valuation , forecasting etc)

 Treasury & fund management ( ALM management , FX trading, Equities , debt, REIT etc
investments)

 Risk Management (credit, market , liquidity , operational risks etc)

 Sales & Accounting( forecasting , budgeting ,segmentation , business analysis)

 Supply chain / opeartions (costing , timing & scheduling, reliability etc)

 HR (salaries , performance etc)

 Marketing( brand valuation, cost to benefit etc)

 Economics ( trade, labour, economy, taxation, investment , external debt, subsidies etc)

Financial Modelling 19 Oct 28, 2020

 Understanding concepts through four Different Model Types --
Corporate Models, Project Finance Models, Acquisition Models And
Merger Integration Models
• Corporate model
 A corporation has a history and it is assumed to last indefinitely (although they probably
won’t in reality.) This means that valuation of a corporation begins with historic analysis
and the models must include some kind of terminal value assumption because the cash
flows are not projected forever.
• Project finance model
 The investment is characterized by different phases and the fact that there is no history
(no matter how many times a similar new combined cycle plant is built, you don’t know
how it will work until you switch it on.) The project finance models focus on cash flows
and generally cover the entire defined lifetime of the project.
• Leveraged buyout model
 The transaction is defined by an entry price, the holding period and exit price and the
manner in which the acquisition is financed. Leveraged buyout models define manner in
which alternative financing sources are repaid and compute the return earned by equity
investors.
• Integrated consolidation model
 Computes earnings per share and other financial ratios before and after an acquisition.
This type of model considers the specific financing and accounting of the transaction as
well as cost savings generated by the transaction.

Financial Modelling 20 Oct 28, 2020

 Architecture of Models

Financial Modelling Oct 28, 2020

 Our modus operandi

1. Make a mathematical model

2. Implement it in excel

3. Play with it to find out how the answers depend on the input variables

4. Use the inbuilt mathematical functions to do complicated analyses

5. Use the excel graphic packages to make diagrams

Financial Modelling 22 Oct 28, 2020

 Seven-Step Modeling Process

Step 1: Problem Definition - Define the problem including the

objectives and the parts of the organization that must be studied.
Step 2: Data Collection – Collect the data to estimate the value of
parameters that affect the organization’s problem.
Step 3: Model Development – Develop an analytical or
simulation model.
Step 4: Model Verification – Determine whether the model is an
accurate representation of reality.

Financial Modelling 23 Oct 28, 2020

 Step 5: Optimization and Decision Making – Given the model
and a set of possible decisions, the analyst must choose the
decision that best meets the organization’s objectives.
Step 6: Model Communication to Management – The analyst
presents the model and the recommendations to the organization.
Step 7: Model Implementation – If the organization accepts the
model then the analysts assists with implementation.
Implementation must be monitored constantly to ensure that the
model enables the organization to meets its objectives.

Financial Modelling 24 Oct 28, 2020

 Notes on Good Modelling Practices

Financial Modelling Oct 28, 2020

 Best Practices and Good Practices

• It is dangerous to become obsessed with best practices in modelling

 You can become bureaucratic and waste time

 There are almost always exceptions to best practices

 Example
 Keep formulas the same, even in base year

 Use range names in all cells

 Ernst and Young: Rarely use range names

• It is much easier to define bad practice

 Long formulas are the worst single problem

 Keep inputs together and logical

Financial Modelling 26 Oct 28, 2020

 Good Modelling Practise

1. Divide the model into separate modules, beginning with an input section.

2. Compute how the value drivers determine operating revenues, operating

expenses and capital expenditures in a separate “working” module rather than
in financial statements.

3. Understand the starting point of the model as it relates to the valuation issue
(balance sheet, sources and uses statement or both).

4. Carefully define the time period of the model using codes that define alternative
phases of the analysis.

5. Work through every single balance sheet item showing the opening balance,
changes and the closing balance for each the accounts. This analysis should be
made for everything ranging from cash accounts to common equity.

6. Include separate modules for debt issues, fixed plant assets, working capital
and cash balances.

Financial Modelling 27 Oct 28, 2020

 Good Modelling Practise

7. Limit or avoid the use of macros and iterations to resolve circular references
as circular references are not present in the real world.
8. Use the balance sheet as an auditing tool and include a separate “integrity”
page of model verification checks.
9. Assure that no formulas in the output module of a model affect anything in any
other section of the model.
10. Make sure that spreadsheet columns are consistent throughout the model and
that the formulas for each column are identical (at least for the forecast period).
11. Include a “dashboard” at the top of each page of the model to monitor the
integrity and key outputs of the model.
12. Keep formulas in the model as simple as possible and clearly delineate how
each formula is derived from the inputs.
13. Use the positive number convention which holds individual elements as
positive numbers and performs additions or subtractions in the subtotal items.

Financial Modelling 28 Oct 28, 2020