Excel assignment

vk4queen
ValuationFrameworks-Student.xlsx

Guidelines

Basic Valuation Methods: A Rough Guide
General Definitions
Firm Value = Operating Assets + Non-Operating Assets = Equity + Debt
Enterprise Value = Debt + Equity - Non-Operating Assets = Operating Assets
Equity Value = Operating Assets + Non-Operating Assets - Debt = Firm Value - Debt =Enterprise Value + Non-Operating Assets - Debt
Always use market values in computing the values above
Free cash flow = Cash flow available to meet the needs of lenders and the wants of investors
Free cash flow for the firm = A variation on free cash flow, eliminating the non-operating items
Free cash flow for equity = Cash flow available to meet the wants of investors in common equity: FCFE
The cash flows can be tied to the claimants in the same way the balance sheet is organized: CF for Assets = CF for Debt + CF for Equity
Relative valuation (using multiples)
P/E Ratio
Steps:
Find the "comparable" ratio of stock price to earnings per share for the subject company
Find earnings available to common shareholders for the subject company
Multiply P/E ratio times earnings available to common shareholders to find the implied equity value
Divide by shares outstanding to find the equity value per share (intrinsic value)
P/S Ratio
Steps:
Find the "comparable" ratio of stock price to revenue per share for the subject company
Find revenue for the subject company
Multiply P/S ratio times revenue to find the implied equity value
Divide by shares outstanding to find the equity value per share (intrinsic value)
EV/EBITDA Ratio
Steps:
Find the "comparable" ratio of enterprise value to EBITDA for the subject company
Find EBITDA for the subject company
Multiply EV/EBITDA ratio times EBITDA to find the value of operations (i.e. Enterprise Value)
Add the market value of any non-operating assets
Subtract the market value of debt
Divide by shares outstanding to find the equity value per share (intrinsic value)
Discounted cash flow valuation
Equity CF Method (FCFE//Ke)
Determine Free Cash Flows for Equity (FCFE)
Model the expected future cash flows for the explicit forecast period (e.g. 5 years)
Compute the terminal value of the cash flows
Discount the cash flows and terminal value using the cost of equity (Ke) to find the equity value of operations
Add the market value of any non-operating assets which were excluded from CF computations
Divide by shares outstanding to find the equity value per share (intrinsic value)
Corporate Valuation Method (FCFF//WACC)
Determine Free Cash Flows for the firm (FCFF)
Model the expected future cash flows for the explicit forecast period (e.g. 5 years)
Compute the terminal value of the cash flows
Discount the cash flows and terminal value using the weighted average cost of capital (WACC) to find the Value of Operations (i.e. Enterprise Value)
Add the market value of any non-operating assets which were excluded from CF computations
Subtract the market value of debt
Divide by shares outstanding to find the equity value per share (intrinsic value)
Adjusted Present Value (APV) Method (FCFF//Ku + TS//Ku)
Steps:
Determine Free Cash Flows for the firm (FCFF)
Model the expected future cash flows for the explicit forecast period (e.g. 5 years)
Compute the terminal value of the cash flows
Discount the cash flows and terminal value using the unlevered cost of capital (Ku) to determine the Unlevered Value of Operations
Determine the interest tax savings for the firm (Interest expense * tax rate)
Model the expected future interest tax savings for the explicit forecast period (e.g. 5 years)
Compute the terminal value of the interest tax savings
Discount the interest tax savings and terminal value using the unlevered cost of capital (Ku) to determine the value of the tax shield
Add the Unlevered Value of Operations to the Value of the Tax Shield to find Value of Operations (i.e. Enterprise Value)
Add the market value of any non-operating assets which were excluded from CF computations
Subtract the market value of debt
Divide by shares outstanding to find the equity value per share (intrinsic value)

Examples (Template)

Use the following data in the models indicated below to estimate the equity value of your subject firm, "Company X."
Benchmark P/E 15.78 Price-to-Earnings ratio from "Peer" firms
Benchmark P/S 1.55 Price-to-Sales ratio from "Peer" firms
Benchmark EV/EBITDA 13.50 Enterprise-Value-to-EBITDA ratio from "Peer" firms
t 30.00% Tax rate
Ke 15.83% Cost of equity
Kd 5.00% Cost of debt
WACC 12.04% Weighted average cost of capital
Ku 12.50% Unlevered cost of equity
Shares 1,000,000 Shares outstanding
Terminal growth rate (after year 5) ---------------------------------------------------------------------------------------------------------------------------> 4.50%
Forecast Years
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Sales revenue 9,193,548.4
EBITDA 1,203,703.7
Earnings available to common shareholders 903,041.8
Equity cash flow (FCFE) 1,220,095.694 1,275,000.000 1,332,375.000 1,392,331.875 1,454,986.809 1,520,461.216
Free cash flow (FCFF) 1,172,248.804 1,225,000.000 1,280,125.000 1,337,730.625 1,397,928.503 1,460,835.286
Interest expense 239,234.450 250,000.000 261,250.000 273,006.250 285,291.531 298,129.650
Market value of debt 5,000,000.000
Value of non-operating assets 3,000,000.000
P/E Multiples Valuation
Benchmark P/E
Earnings available to common shareholders
Equity intrinsic value
Shares
Implied price per share
P/S Multiples Valuation
Benchmark P/S
Sales Revenue
Equity intrinsic value
Shares
Implied price per share
EV/EBITDA Multiples Valuation
Benchmark EV/EBITDA
EBITDA
Enterprise value
Market value of non-operating assets
Market value of debt
Equity intrinsic value
Shares
Implied price per share
Equity Cash Flow Model: Discounting Equity Cash Flows at Cost of Equity
Ke (Cost of Equity)
Forecast Year: 1 2 3 4 5
Equity cash flow (FCFE)
Terminal value
PV
Equity value of operations
Value of non-operating assets
Equity intrinsic value
Shares
Implied price per share
Corporate Valuation Model: Discounting Free Cash Flows at WACC
WACC (Weighted Average Cost of Capital)
Forecast Year: 1 2 3 4 5
Free cash flow (FCFF)
Terminal value
PV
Value of operations
Market value of debt
Present value of non-operating assets
Equity intrinsic value
Shares
Implied price per share
Adjusted Present Value Model: Discounting Unlevered Cash Flows at Unlevered Cost of Capital
Ku (Unlevered Cost of Capital)
Forecast Year: 1 2 3 4 5
FCFF
Terminal value
PV
Unlevered value of operations
Interest tax savings
Terminal value
PV
Value of tax shield
Market value of debt
Value of non-operating assets
Equity intrinsic value
Shares
Implied price per share