FE - Modelling

Question 1

DCF Model: Assumptions

Answer 1

Block assumptions together in a table, this makes it very user-friendly. e.g.

Revenue growth - 3.0%
Revenues: 2023 & new one at growth rate
Operating costs as % of revenue - 95%
past year and new one at growth rate
Tax on profit - 15%
Tax expense:

Question 2

DCF 5 Steps

Answer 2

Forecast free cash flows

Calculate WACC to use as discount rate

Calculate terminal value - Final cash flows*TG Rate/(WACC-TG Rate)

Discount cash flows to PV - Dicount Future years & Terminal Value

Walk from EV to implied share price using the bridge (Enterprise Value = Equity Value + Debt - Cash and equiv.), run this step backwards to get to Equity Value

Question 3

Forecasting Free Cash Flows

Answer 3

EBIT
SUBTRACT Tax on EBIT (EBIT*Long run tax rate) Because we don’t want to use tax figure from Income Statement as that includes debt expense deducted
= NOPAT (Net Operating Profit After Tax

ADD Depreciation&Amortisation
SUBTRACT CapEX

(These 2 can be grouped together by deducting Change in LongTermAssets from the SoFP)

ADD/SUBTRACT Change in Operating Assets & Liabilitiesl. Subtract increases in Assets, Add increases in Liabilites (as cash has increased)

ADD/SUBTRACT Changes in other Operating Assets/Liabilities

= FCF (Cash flow that can be returned to investors)

Question 4

Terminal Value

Answer 4

TV = Final year’s FCF * (1 + Growth Rate)/WACC - Growth Rate)

Alternatively, we can use EV multiple

Question 5

Discount Factor

Answer 5

1/(1+WACC)^n

n = Period e.g. Period 2 we would use ^2

Question 6

Discounting the TV

Answer 6

Use Final Period’s DF

e.g. say we projected out for 5 years, The DF for TV would be Period 5’s DF

Question 7

The Bridge from EV to Implied Share Price

Answer 7

After completing DCF and getting an Enterprise Value, subtract Net Debt and ADD Non-Core Assets to get Equity Value

Divide this by No shares outstanding (diluted if available) to get Implied Share Price

Question 8

What to do BEFORE SENDING EXCEL FILE TO MANAGER

Answer 8

Beauty save

Ctrl+Home to move selector to top left of each page.
Put zoom on 100%

Question 9

Full 3 Statement Modelling Process
9 Steps

Answer 9

1) Input historical data for IS & SoFP
2) Calculate ratios & statistics
3) Decide on forecast assumptions
4) Build the Income Statement except for interest
5) Build the Balance Sheet except for Cash & Revolver
6) Build cash flow statementusing rules of cash (increase in asset bad for cash vice versa)
7) Use max/min to fill in BS Cash & Revolver
8) Build the interest calculations
9) Link the interest into Income Statement and deal with the circular reference

Question 10

Revolver definition

Answer 10

Revolving credit facility , like an overdaft

Beginning cash, net of revolver = BS Cash - Revolver
Ending cash, net of revolver = Beginning cash (net of revolver) + Net Cash Flow from CFS

Question 11

Step 7 of 3 statement modelling:
Using max/min

Answer 11

Go to empty cash figure in SoFP, Input =MAX(Ending cash net of revolver for that year,0)

Go to empty revolver in SoFP, inoput =MIN(0,Ending cash net of revolver for year)*-1

Question 12

Step 8 of 3 statement modelling:
Building interest calculations

Answer 12

Interest on revolver
Interest on long term debt
Interest on cash & equivalents
Net interest expense

To calculate all 3 of these: revolver expense*AVERAGE(Revolver for that year-revolver for previous year)

We use this same formula/concept for all 4 (but interchange name

Question 13

Exchange ratio & cash per share formula

Answer 13

Exchange ratio = Shares issued/Shares purchased

Cash per share = Deal debt/Shares purchased

Question 14

When to use diluted vs weighted average number of shares

Answer 14

Diluted: Use for equity value (*share price)

Weighted avg.: Use for Earnings per share

Question 15

Beta definition

Answer 15

Correlation between firm’s share price and its listed index