Financial Modelling

Lecture 8

Vedat Mizrahi

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 1

Overview of Valuation Process
• Top down:
– Value firm as a whole
– Value firm debt
– Value convertible securities
• Convertible bonds
• Warrants
• Equity = Residual value
• "Bottom up"--direct valuation of particular security
– Most common example:
• Equity valuation = PV (dividends + anticipated terminal value)
• Bond valuation = PV (future anticipated interest + principal
repayments)
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 2
Why Sequential Valuation Process?
Analysis Result

Economy and industry Projection of sales

Firm Projection of costs

Firm risk Estimation of RADR

Firm valuation

Debt risk Debt Valuation

Valuation of convertible Valuation of stock

securities
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 3
Why Pro Forma Model of the Firm?

• Important disciplinary exercise

– It forces the analyst to be specific about how you think the
firm works
– In the process you learn a lot about what you know and
more importantly what you don’t know!

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 4

Valuation Process

Stage 1: Study the corporate environment

• Can the firm expand its sales, either along with or
independent of its industry?
• Does the firm require new fixed capital to expand
sales?
• Does expansion of sales require NWC Balance?

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 5

Valuation Process
Stage 2: Build financial model for the firm--Pro Forma
financial statements
• Integrated in accounting sense
• Balance Sheet with Income Statement
• Integrated structural view of the firm
• Incorporate projected financial ratios for firm
• historical?
• projected future?

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 6

Valuation Process
Stage 3: Convert projected financial performance to
values
• Project using pro formas
• Project firm CFOs and FCFs
• Project terminal value - the "last cash flow"
• Use appropriate RADR (rA, rW , rE)
Stage 4: Consider alternative techniques
• P/E ratios
• Market to Book ratios
• …..
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 7
Valuation Process
Stage 5: Sanity checks
• Does your valuation make any sense?
• Does it serve the user?
– Difference between M&A & portfolio
• Did we do too much "hard wiring"
– what do firm insiders think?
– are there non-quantifiable, strategic considerations?

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 8

Principles of Valuation
• Value cash flow streams as opposed to:
– valuation by ratios
– valuation by accounting measures
• Be consistent in your treatment of inflation:
– identify CFs as real or nominal
• Identify recipients of cash flow streams:
– dividends to equity holders
– interest & principal to bondholders
– convertible bonds?
– warrants?
• Make sure discount rates are appropriate: correspondingly real or
nominal incorporate tax rates of recipient
• Use several methods of valuation: “bottom up” and “top down”,
multiples, different ways of identifying discount rates
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 9
“FIND THE BALL PARK”
Overview of Pro Forma Model & Basic Setup
Modular Approach
• Core Statements
– Income Statement
– Balance Sheet
– Cash Flow Statements
• Supporting Schedules:
– Do all the detail work
– Link between core statements
– Complete the supporting schedules and export to the core statements
• Balance Sheet and Cash Flow Statements:
– “Dumb” schedules
– All projections are references from schedules

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 10

Modular Approach

Working Income
Income Debt &
Capital Statement
Statement Interest

PP&E, Cash
Cash Flow
Flow Shareholders’
Depreciation Statement
Statement Equity

Intangibles, Balance
Balance Other LT
Amortization Sheet
Sheet Items

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 11

Balance Sheet Projections
Line Item Source
Cash From the cash flow statement.
Non-Cash Current Assets From the working capital schedule.

PP&E / Fixed Assets, net From the depreciation schedule.

Goodwill, net From the amortization schedule.

Other Long Term Assets From other long term items schedule.
Non-Cash Current Liabilities From the working capital schedule.
Deferred Taxes Many approaches: (1) Keep constant; (2) project as percent of sales or
(3) From a tax schedule if detailed tax information is available.
Other Long Term Liabilities From other long term items schedule.

Short-Term Debt (Revolver) From the debt schedule.

Long-Term Debt From the debt schedule.

Shareholders’ Equity Items From the equity schedule.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 12

Cash Flow Statement Projections
Line Item Source
Cash Flow from Operating Activities:
Net Income From the income statement.
Depreciation From the depreciation schedule.
Amortization From the amortization schedule.
Change in Working Capital From the working capital schedule.
Change in Other Long-Term Assets and Liabilities From the other long-term items schedule.
Cash Flow from Investing Activities:
Capital Expenditures From the depreciation schedule.
Acquisition of Operating Division From a acquisition schedule, if appropriate.

Cash Flow from Financing Activities:

Issuance / (Repayments) of Revolver From the debt schedule (“cash sweep”).
Issuance / (Repayments) of Long-Term Debt From the debt schedule.
Issuance / (Repayments) of Equity From the equity schedule.
Dividends From the equity schedule.
Options Proceeds From the equity schedule.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 13

Final Model: Flow of Information

Net Income
Depreciation

Sales Interest Expense

COGS Income
Income Debt &
Statement
Statement Interest
Change in
Working Capital w
Flo
Amortization

sh /
Working eeC
a
ments
r y Debt
F epa s
Capital Working Net Income ebt R owing Balances
D Borr
Capital

Share Rep., Option

CapX, Dep. Cash
Cash Flow
Flow Proceeds, Div, FX Effects Shareholders’
Statement
Statement Equity
PP&E, PP&E Equity
Depreciation Balance

Beg. Cash Ending Cash Change in

Amortization Other LT Items

Intangibles, Balance
Balance Other LT
Amortization Goodwill Sheet
Sheet Other LT Items Items

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 14

Steps in Building the Model
• Step 1: Set up the Income Statement
– Start with the Annual Report Format – Keep it simple!
– Show more detailed line items if necessary.
• Step 2: Set up the Balance Sheet
– Start with the Annual Report Format.
– Simplify – Combine line items if they are immaterial to
analysis.
• Step 3: Set up the Cash Flow Statements
– Use standard format (FASB) to match the financial
reports .
– Report “Valuation Cash Flows” separately.
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 15
Basic Income Statement

Sales
Cost of Goods Sold (COGS)
Selling, General, Administrative Expenses (SG&A)
Interest
Profits Before Taxes

Taxes
Profit After Taxes

Dividends
Retained Earnings
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 16
Balance Sheet
Assets Liabilities & Owners’ Equity
Current Assets: short-term assets Current Liabilities: short-term
Cash obligations
Accounts Receivable Accounts Payable
Inventories Accrued Taxes
Current Portion of LT Debt
Short-Term Borrowing

Fixed Assets Long-Term Liabilities

Leased Property & Equipment Leased Property & Equipment
PP&E Long-Term Debt
Land

Goodwill Preferred Stock

Equity
Stock Value
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 17
Retained Earnings
Summary of “Theory of Valuation”
• Use APV when capital structure is not constant
over time (at terminal point firm is at optimal capital
structure).
• Use UFCF and discount at rA to get VU.
• PVTS can be computed by discounting tax shields
at rD.
• If rD is lower than market value, we need to
estimate the value of subsidy.
• VL = VU + PVTS + Subsidies and other benefits
from financing.
• Do not adjust discount rates on an ad hoc basis.
Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 18

3
Summary of “Theory of Valuation”
• When we investigate the interaction between the
financing & investment, we may have the following
cases:
– NPVU > 0, NPVL > 0 Invest
– NPVU < 0, NPVL < 0 Do not invest
– NPVU < 0, NPVL > 0 Invest if financing is project
specific.
Do not invest if financing is
not project specific.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 19

4
Summary of “Theory of Valuation”
• IRR on equity flows can be used with some caviats
and caution.
– Since D/E is changing there is no well defined cutoff rate
(rE) to compare the computed IRR on equity with.
– If APV had positive NPV, then we can be sure that IRR on
equity flows should be high enough to accept the project.
This rate is now is the return on equity invested in the
project.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 20

5
DCF Valuation Models: Overview

Inputs

Cash Flow Dividends Cash-flows to Equity Cash-flows to

Expected Dividends to FCFE = EBT(1-T) + Dep Firm
Shareholders - ∆NWC - CAPX – ∆D UFCF=
EBIT(1-T) + Dep
-∆NWC -CAPX

Discount Cost of Equity Cost of Capital

Rate CAPM: Riskfree Rate + (Risk Premium)Beta WACC=ke (E/V)
MFM: Riskfree Rate +Σ(Risk Premium)jBetaj +kd(1-T)(D/V)

Growth
Pattern

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 21

6
Constant Growth Model

• The value of firm, under the constant-growth model,

is a function of the expected UFCF in the next
period, the stable-growth rate, and the required rate
of return.
– V0 =UFCF1/(rW – g)
• Implicit assumptions
1. Growing at a rate less than or equal to the growth rate in the
economy in which they operate
2. Cap Ex = Depreciation

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 22

7
Valuing Equity – Two-Stage Model
• The value of any stock is the present value of the FCFE per year for the
extraordinary growth period plus the present value of the terminal price at the
end of period.

• Assumptions:
– Growing at a rate moderately higher than the nominal-growth rate in the economy
in which they operate;
– Specific sources of growth (patents, legal protections) that are expected to expire
after a fixed period;
– Free cash flows to equity are significantly different from dividends;
– Stable financial leverage.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 23

8
Mid-Point (“Holt”) Model
• The model is based upon the assumption that the earnings growth rate
starts at a high initial rate (ga) and declines linearly over the extraordinary
growth period (which is assumed to last 2H periods) to a stable growth
rate (gn). It also assumes that the dividend payout is constant over time,
and is not affected by the shifting growth rates.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 24

9
Three-Stage Model
• This model assumes an initial period of stable high growth, a second
period of declining growth, and a third period of stable growth that
lasts forever.

Vedat Mizrahi, Ph.D Kadir Has UNiversity - Financial Modelling 25

10