Financial Modeling Final Exam

CFO (Indirect Method)

• Net Income + Non Cash Adjustments + WC Cash Impact + Reclassification Adjustments

• Net Income + Non Cash Adjustments - Change in NWC + Reclassification Adjustments

• Net Income + Non Cash Adjustments - Operating Current Assets + Operating Current Liabilities + Reclassification Adjustments

Non Cash Adjustments

• depreciation

• amortization

• stock based compensation

• non cash impairments

• selected deferred tax movements

• unrealized gains/losses

Operating Working Capital

Non Cash Current Assets - Operating Current Liabilities

WC Adjustment / WC Cash Impact

-Change in Operating Current Assets + Change in Operating Current Liabilities

-Chang in NWC

CFO Reclassification

-Gains + Losses

Accrued vs Paid Dynamic

Cash Paid = Expenses - Change in Payable

Non Cash Sign Rule (Reconciliation Logic)

• if an item reduced net income but is non cash, it should be added back to CFO

• if an item increased net income but is non cash, it should be subtracted from CFO

PP&E Roll Forward

PP&E_t-1 + CapEx - Book Value Disposed - Depreciation + Other PP&E

Book Value vs Cash Proceeds

• Balance sheet PP&E roll forward tracks book value, and decreases by Book Value Disposed

• Cash Flow Statement records Cash Proceeds

• Gain/Loss = Cash Proceeds from Sale - Book Value Disposed

CFI Formula

-CapEx + Proceeds from Sale + Other Investing CF

-CapEx + Proceeds from Sale - Acquisitions + Sale of Investments - Purchase of Investments - Capitalized software/intangible investment

Other Investing CF

• acquisitions

• purchase/sale of investments

• capitalized software/intangibles

CFF Formula

Borrowings - Repayments + Equity Issued - Equity Repurchased - Dividends + Other Financing CF

Change in Debt

Borrowings - Repayments + Non Cash Debt Adjustments

Change in Equity

Net income - Dividends + Equity Issued - Equity Repurchased + Non Cash Equity Adjustments

Dividends Declared vs Payable

Cash Dividends Paid = Dividends Declared - Change in Dividends Payable

NOPAT

EBIT (1 - T)

FCFF

• Cash available to all capital providers

• NOPAT + D&A - Net Cap Ex - Change in NWC + Other Op Adjustments

FCFE

• Cash available to equity

• Net income + Non Cash - Net Cap Ex - Change in NWC + Net Borrowing

• FCFF - After Tax Interest + Net Borrowing

Scenario Architecture

H = fixed historical data and opening balances

M = fixed model structure

a^(s) = vector of assumptions associated with scenario s

y^(s) = resulting output vector

Choosing Scenario Drivers —> Creditor Decision Context

• collections

• refinancing access

• covenant headroom

Choosing Scenario Drivers —> Equity Decision Context

• pricing power

• margin structure

• reinvestment needs

4 Main Groups of Drivers

• Operating Drivers (Unit volume, price, product mix, gross margin, operating expense intensity)

• Working Capital Drivers (DSO, DPO, DIO)

• Investment Drivers (Capital expenditures, maintenance vs growth investment, acquisition activity)

• Financing Drivers (Borrowing spreads, refinancing access, dividend policy, minimum cash requirements)

Stress Test Families

• liquidity stresses

• covenant stresses

• margin compression stresses

Revenue Break Even

Revenue_BE = F / (1 - V)

Revenue_BE = Fixed Operating Costs / (1 - Variable Cost )

Model Risk

• Risk of making a poor decision because the model is wrong, misunderstood, misapplied, or interpreted with more confidence than the evidence warrants

• Model Risk = Design Risk + Implementation Risk + Assumption Risk + Interpretation Risk

Design Risk

Concerns the model’s structure or logic

Implementation Risk

Concerns how that logic is translated into formulas or code

Assumption Risk

Concerns the inputs supplied to the model

Interpretation Risk

Concerns how the outputs are used in analysis and decision making