Financial Statement Modeling

First Step in DCF Valuation

Created by David Moore, PhD

Key Concepts

  1. Free-cash flows
    • Sales-based projections
  2. Pro-forma financial statements

Intro Callback: Valuing a stock with dividends?



$\hat{P_t}=\frac{D_{t+1}}{r-g}$

Valuation is treating free cash flows of the firm as potential dividends.

Valuation Reminder



The intrinsic value of any asset is a function of the magnitude of expected cash flows over its lifetime and the uncertainty about receiving those cash flows

First step in Valuation

Project future cash flows


  • Is Net Income cash?
  • Is change in cash from Cash Flow Statement Cash?

Free cash flow to the firm (FCFF)

Also known as Unlevered Cash flow

  • Cash flow that an enterprise produces that is "free" to be used to service the securities (debt and equity) that a firm issues to finance its operations
  • Free of:
    • Any financing effect
    • Non-cash accounting charges and other accounting "quirks"

Defining FCFF

After-tax pre-depreciation operating profit (aka operating cash flows) minus investment
  • Investment
    • Change in net working capital
      • (CA- (Cash or Excess cash))-(CL- Current portion of LTD)
    • CAPEX


$FCFF=EBITDA*(1-T_C)+Depreciation*T_C-Investment$

$FCFF=EBIT*(1-T_C)+Depreciation-Investment$

Basic FCFF Calculation

Suppose a firm has revenue of $100M and operating expenses of $60M. The cash flow statement shows a value of $20M for D&A and $20M for capital expenditures. The marginal tax rate in the US is 21%. The below shows the balances in CA and CL. What is the unlevered cash flow for this firm?
Account 2019 2018
Current Assets $45M $32M
Current Liabilities $58M $43M
Cash $6M $12M
Current Portion of LTD $8M $10M

FCFF Solution

$FCFF=EBIT*(1-T_C)+Depreciation-Investments$

$=(100-60)*(1-.21)+20 - 22$

$=29.6$

where Investments is calculated as

$ CAPEX + \Delta NonCashNWC$ $=(20+(((45-6)-(58-8))-((32-12)-(43-10)))$

Forecasting 101

Types



  1. Free-cash Flow
  2. Pro-forma

How long?



  • How long does the company last?


  • Forecast until stable growth
  • Typically 5-10 years

Forecast FCF

  1. Start with Sales growth (top-down)
  2. Make reasonable assumption about other inputs to FCF
    • Use historical values
    • Make adjustments based on:
      • Economic outlook
      • Management forecast
      • Analyst forecast
      • Your own research

What drives growth?

Investment and return

$ROE*b$, where b is retention ratio

Put differently, operating Income will grow at:

$ROC*Reinvestment Rate$,
ROC=Return on Capital=$\frac{EBIT(1-t)}{BV Debt+BV of Equity - Cash}$
Reinvestment Rate= $\frac{Net CAPEX + \Delta NWC}{EBIT(1-t)}$

Note: $\frac{ROE*b}{1-ROE*b}$ if Equity measured at end of period.

Investments

  1. CAPEX
    • Balance Sheet change in PPE as a % of Sales (net of depreciation and asset sales)
    • Cash flow statement CAPEX as a % of Sales
    • Note: May need to include intangibles
  2. Change in NWC
    • Average NWC as % of sales and apply to change in sales
    • Average change in NWC as a % of sales and apply to level of sales

Tax Rate



  1. Marginal: 21% in the USA (federal)
    • Can use 25-29% to capture state taxes
  2. Effective: Based on historical average

Internal Consistency

Do the numbers make sense?

  • Strong revenue growth requires significant investment
  • Life cycle of firm and investment
    • Start-up: Substantial working capital requirements
    • Mature firm: CAPEX tends to exceed or equal depreciation

Pro-forma Financial Statements

  • Can also model entire financial statement
  • Pull FCFF from this pro-forma financial statement
  • Requires a "plug" to balance the balance sheet
    • Plus is either Cash, Debt, or Equity
    • Acts as source of financing for incremental investment

Example 1

SIC

Example 2 (alternative template)

SIC

Pro Forma Financial Statement Example

SIC

Key Learning Outcomes

  • Free cash flows
  • Forecasting free cash flows
  • Pro forma financial statement

Next time

Discounted Cash Flows Part 1