Financial Modeling Fundamentals

UNLOCKING CAREER POTENTIAL

Contents

• Modeling steps
• Items of note
– Operating cash
– Deriving a cash flow statement
– Debt and interest
– Circular references

2
 Review of modeling steps

Model map

Historical Assumptions
financial and
statements projections

Historical Forecast
ratio financial
analysis statements
and analysis

4
Forecast financial statements and analysis
Steps to build a model – including interest

1. Income statement
• Leave out interest

2. Balance sheet
• Leave cash and revolver (plugs) out

3. Cash flow statement

• Cash flows calculated using changes from balance sheet

4. Balance the balance sheet

• Link up cash and revolver balance from cash flow statement to balance sheet

5. Calculate and link up interest

• Build full debt schedule
• Link up interest to income statement

Design-led modeling

Characteristics of a well designed model Some modeling choices

• Tower or matrix model

Easy to
Easy to follow • Positive or negative presentation
change
• Summary or full financials
• Five or ten year forecast
Reflects the
Easy to
underlying
understand
business

6
 Modeling steps – Detail

Step 1: balance sheet and income statement historicals

• Replicate the historical balances shown in the company accounts

• Calculate totals/subtotals (do no hard-code)
• Check that net income matches source data
• Do a balance sheet check to ensure it balances

Use the SUM function if possible:

ALT + =
to calculate historical subtotals

8
Step 2: calculate ratios

• Calculate ratios using the income statement and balance sheet historical
numbers
• Historical ratios are often used as a forecast basis
– Examples:
– If forecasting sales using a growth rate, calculate a historical growth rate ratio
– If forecasting COGS as a % of sales, calculate the COGS / sales historical ratio
• Historical ratios also help identify trends
– Helps you form a view on your assumptions

Step 3: formulate forecast assumptions

• The historical ratios calculated in step 2 help to set the value of the forecast
assumptions
• (Forecast assumptions are pre-set in this model)

The forecast assumptions have been set in line with the The forecast assumptions deviate from historical trends. If
historical trends this is the case, use comments to provide explanations

10
Step 4: income statement forecast

• Place the assumption first when writing a formula

• Work vertically – focus on the first forecast year
• Use secondary calculations as necessary
• Copy subtotals across – do not recalculate them
• Copy across section by section (not line by line)
• When finished, sense check thoroughly

11

Step 5: balance sheet forecast

• Place the assumption first when writing a formula

• Leave out the plug accounts (e.g. excess cash and revolver short-term debt)
• Use SUM formulas for totals
– Do not include blank rows
• Use condary calculations as necessary
– Base analysis for PP&E, equity, debt etc

12
 Operating cash – Separating
operating cash from excess cash

Operating cash

• The minimum cash level needed to operate a business on a day-to-day basis

• Rarely disclosed in the financial statements
• Often estimated as % of sales
• Can be treated as a component of Operating Working Capital
• Total cash = Excess cash + Operating cash

14
Step 6: cash flow statement

• Structured in three sections

– Operating
– Investing
– Financing
• Capture the change in every balance sheet item using the rules of cash:
– Assets up = cash down
– L&E up = cash up and vice versa
• The change in some balance sheet items is captured by more than one item.
Examples:
– Change in retained earnings: Net income and dividends
– Change in PP&E: Capex and depreciation
• Include cash reconciliation at the end

15

Deriving a cash flow

statement
Deriving a cash flow statement
Steps to build a cash flow statement

Categorize
Use BASE
the change Apply rules Build CFS
analysis if
in each BS of cash structure
needed
account

Cash flow categories Examples • Asset  Cash  • CFO

• Operating • PPE • Asset  Cash  • CFI
• Investing • Retained earnings • L&E  Cash  • CFF
• Financing • L&E  Cash  • Cash reconciliation

17

Rules of cash

• Liabilities and equity are sources of cash Remember these 4 rules

• Direct relationship with cash flow Asset Up Cash Down
• Take the delta from the BS as follows:
Asset Down Cash Up
= This year – Last year
L&E Up Cash Up
• Assets are uses of funds L&E Down Cash Down
• Inverse relationship with cash flow
• Take the delta from the BS as follows:
= Last year – This year
or
= (This year – Last year) * -1

18
Building the cash flow statement from scratch
Example

• Start by capturing the change in equity by linking the related items

(net income, dividends, etc.) from the Calcs sheet

Remember the 4 rules

Asset Up Cash Down
Asset Down Cash Up
L&E Up Cash Up
L&E Down Cash Down

19

Building the cash flow statement from scratch

Example (cont.)

• Continue capturing the change in each balance sheet item, as needed.

For example, the change in PP&E is composed of capital expenditure and
depreciation (linked from the Calcs sheet)

Remember the 4 rules

Asset Up Cash Down
Asset Down Cash Up
L&E Up Cash Up
L&E Down Cash Down

20
Building the cash flow statement from scratch
Example (cont.)

• Link amortization from the income statement or assumptions sheet

Remember the 4 rules

Asset Up Cash Down
Asset Down Cash Up
L&E Up Cash Up
L&E Down Cash Down

21

Building the cash flow statement from scratch

Example (cont.)

• Change in operating working capital accounts

• Issuance/repayment of long-term debt

Remember the 4 rules

Asset Up Cash Down Consider grouping
the OWC accounts
Asset Down Cash Up into one line item
L&E Up Cash Up
L&E Down Cash Down

22
Building the cash flow statement from scratch
Example (cont.)

• Add subtotals and calculate net cash flow

23

Building the cash flow statement from scratch

Example (cont.)

• Derive the cash and revolver balances

Link from the historical balance sheet.

Net cash against the revolver balance

• Link the projected ending balances back to the balance sheet

– Use IF or MAX/MIN
• Check that the balance sheet balances

24
Step 7: balance the balance sheet
Plug accounts

• The forecast balance sheet is made The plug ensures that

the balance sheet
to balance using one or more
balances
designated accounts
– ‘Plugs’ Plug
– Cash is the most common plug
account
• Link cash from the cash flow Liabilities
and
statement equity
Assets
• If it does not balance, stop and
resolve

25

Stress testing

Concept Examples

• Change assumption and check how • If the dividend payout ratio decreases
the model responds from 20% to 10% of net income:
• Helps to assess whether the model – Cash balance should rise
functions as expected – Balance sheet should remain in
balance
• If the debt repayments were increased
materially:
– Cash balance should fall
– Balance sheet should remain in
balance

26
What if cash were to be negative?

Funding Negative cash represents a funding gap.

gap This gap is usually resolved in a model by
using a short-term debt line,
such as Revolver.

Assets
Liabilities
and
equity

27

Managing the plug accounts

Separating cash and revolver Helpful functions

Year 1 2 3 4 5 • IF(test, action if true, action if false)
From cash flow statement:
– If cash / revolver > 0
Cash (Revolver) 14 10 7 (3) (20) • If true: show cash
• If false: show 0
From balance sheet:
• MAX(list of cells)
Cash 14 10 7 - -
– MAX shows the largest number
Revolver - - - 3 20
– MAX(cash / revolver,0)
• Cash / revolver > 0: returns cash
• Cash / revolver < 0: returns 0

28
Step 8: Wire in the interest
Interest calculation options

• Interest on debt can be estimated and modelled based on:

– Beginning balance
– Ending balance
– Average balance
• The appropriate method depends on the timing of any debt movements
during the period

29

Interest based on beginning balances

Implicit assumption about timing of loan repayments

Example Calculation

• A company has a loan outstanding • An amount of 1,000 is outstanding

of 1,000 at the beginning of the for the whole year
year • Therefore, the interest can be
• Interest on the loan is 10% calculated using the beginning
• A loan repayment of 600 is due to balance
be paid on the last day of the year
FY beginning balance FY ending balance
1,000) 1,000)
(600)
) 400)

30
Interest based on ending balances
Implicit assumption about timing of loan repayments

Example Calculation

• A company has a loan outstanding • An amount of 400 is outstanding for

of 1,000 at the beginning of the the whole year
year • Therefore, the interest can be
• Interest on the loan is 10% calculated using the ending balance
• A loan repayment of 600 is due to
be paid on the first day of the year
FY beginning balance FY ending balance
1,000)
(600)
400) 400)

31

Interest based on average balances

Implicit assumption about timing of loan repayments

Example Calculation

• A company has a loan outstanding • Standard practice is to assume that

of 1,000 at the beginning of the the repayment occurs
year half-way through the year
• Interest on the loan is 10% • Therefore, interest is calculated
• A loan repayment of 600 is due to using the average balance during
be paid, but the timing of the the year
repayment is uncertain FY beginning balance FY ending balance
1,000) 400)

32
 Circular references

Circular references

What is a circular formula? Circular reference warnings

• A formula becomes circular when it • If iterations are off:

uses itself as an input – A warning message appears
– Circular formulae are not calculated
• The formula below in A3 is adding
• If iterations are on:
itself into the total, which causes a – No warning message
circularity – Circular formulae are calculated
(using an automatic iterative process)
A
• Status bar messages:
– On this sheet:
1 1
– In an open workbook:
2 2
– If iterations are on:
3 =SUM(A1:A3)

34
When does interest create a circular reference?

Interest based on beginning balance No circular loop

• If interest is calculated on the Revolver

Interest
beginning balance then no circular beginning
expense
balance
formula is created

Net income CFO

Revolver
Net cash
ending
flow
balance

35

When does interest create a circular reference?

Interest based on ending balance Potential circular loop

• If interest is calculated on the Revolver

ending balance then a circular ending
balance
formula is created if the net cash
flow drives the ending balance Net cash Interest
• This is an issue for residual items flow expense
such as Revolver and Cash

Net
CFO
income

36
When does interest create a circular reference?

Interest based on average balance Potential circular loop

• If interest is calculated on the Revolver

average
average balance then a circular balance
reference is created if the net cash Revolver
Interest
ending
flow drives the ending balance balance
expense

• This is an issue for residual items

such as Revolver and Cash
Net cash Net
flow income

CFO

37

Iterative process

• What is the purpose of iteration?

• Iteration calculates the formula multiple times
• In each recalculation, Excel begins with the answer from the previous
recalculation
• Iteration settings (File, Excel options, Formulas)

38
Iterations
Assumptions

Calculation Example

• 100% loan Principal + Interest = Total debt

100.00 + ? = ?
• 10% interest rate
100.00 + 10.00 = 110.00
• Interest must be paid in advance
+ 1.00 = 111.00
• But no money to pay interest, so it + 0.10 = 111.10
must be borrowed + 0.01 = 111.11
• What is the total debt borrowed?
– Principal + Interest
– But interest creates further interest

39

Iterations

Advantages Disadvantages

• Elegant solution to circular • Hides accidental circular references

references • Destabilizes model
• Numbers adjust automatically
• Allows cumulative formulas

40
Types of circular formulae

Unintentional Intentional

• Circular formulae written in error • Circular formulae that have been

• A model should never contain deliberately built by the user
unintentional circular references as • Often caused by interest
they destabilize the model calculations
• Always model with iterations off so
you are warned of unintentional
circular references

41

Circularity switch

Concept Schematic

• A mechanism allowing circular Revolver

average
formulae to be disconnected balance
• Uses an IF function Revolver
ending
Interest
expense
• Allows working with a balance

circularity-free model Switch

• Helps reset the model in case of

Net cash Net
errors flow income

CFO

42
Circularity switch Danger! Modeling with the circular toggle on and iterations
active can result in unintentional circular references

Model construction Financial model output analysis

Enable Enable
Switch iterative iterative Circular switch
calculations calculations

Off Off On On

Set to Set to
No tick Tick
0 1
43