1. LESSON OBJECTIVES

By the end of this lesson, you will be able to:

• Construct a fully integrated three-statement financial model (Income Statement, Balance Sheet, Cash Flow Statement) from scratch.

• Link the three statements using the cash flow waterfall and the balance sheet plug.

• Build a debt schedule with interest calculations, mandatory repayments, and cash sweep mechanisms.

• Develop a detailed depreciation and amortization schedule using multiple depreciation methods.

• Create a working capital schedule with Days Sales Outstanding (DSO), Days Inventory Outstanding (DIO), and Days Payable Outstanding (DPO).

• Implement circular references and handle them using iterative calculation settings.

• Build scenario analysis and sensitivity tables to assess the impact of key drivers on valuation.

• Design a model with error checks and audit trails to ensure accuracy and reliability.

• Apply financial modeling best practices (color coding, logical flow, modular structure).

• Prepare a board-quality model presentation with charts, tables, and key metrics.

---

2. THE THREE-STATEMENT MODEL – ARCHITECTURE AND FLOW

A three-statement model integrates the Income Statement, Balance Sheet, and Cash Flow Statement into a single, dynamically linked financial model.

THE CORE LINKAGES:

1. Net Income (Income Statement) → Retained Earnings (Balance Sheet):

   Ending_Retained_Earnings = Beginning_Retained_Earnings + Net_Income – Dividends

2. Net Income (Income Statement) → Operating Cash Flow (Cash Flow Statement):

   • CFO starts with Net Income and adjusts for non-cash items and working capital changes.

3. Balance Sheet Changes → Cash Flow Statement:

   • Increases in Assets (e.g., Accounts Receivable) are uses of cash (outflows).

   • Increases in Liabilities (e.g., Accounts Payable) are sources of cash (inflows).

4. Cash Flow Statement → Cash (Balance Sheet):

   Ending_Cash = Beginning_Cash + CFO + CFI + CFF

5. Interest Expense (Income Statement) → Debt Schedule (Balance Sheet):

   • Interest expense is calculated based on the average debt balance.

THE BALANCE SHEET PLUG (CASH FLOW BALANCING):

The model must ensure that the Balance Sheet balances. Any imbalance (unreconciled difference) is typically plugged into the Cash account or a “Plug” line item.

The Balancing Check:

Total_Assets – (Total_Liabilities + Total_Equity) = 0

If this check fails, the model is not integrated. The model must be debugged until the check passes.

---

3. BUILDING THE INCOME STATEMENT

A. REVENUE FORECASTING:

Revenue_t = Revenue_{t-1} * (1 + Revenue_Growth_t)

Revenue growth can be:

• Top-Down: Based on market size and market share projections.

• Bottom-Up: Based on user growth, average revenue per user (ARPU), and transaction volume.

• Blended: A combination of both approaches.

B. OPERATING EXPENSES:

C. CALCULATING EBITDA, EBIT, AND NET INCOME:

EBITDA = Revenue – Cost_of_Revenue – SG&A – R&D

EBIT = EBITDA – Depreciation – Amortization

Pre_Tax_Income = EBIT – Interest_Expense + Other_Income

Net_Income = Pre_Tax_Income * (1 – Tax_Rate)

---

4. BUILDING THE DEPRECIATION AND AMORTIZATION SCHEDULE

Depreciation and amortization are non-cash expenses that reduce Net Income but do not consume cash.

A. DEPRECIATION METHODS:

1. Straight-Line Depreciation:

Annual_Depreciation = (Cost – Salvage_Value) / Useful_Life

2. Declining Balance Depreciation:

Annual_Depreciation = Beginning_Book_Value * Depreciation_Rate

3. Sum-of-the-Years-Digits (SYD):

Depreciation = (Cost – Salvage_Value) * (Remaining_Useful_Life / Sum_of_Years)

B. DEPRECIATION SCHEDULE:

C. AMORTIZATION OF INTANGIBLES:

Intangibles (customer relationships, technology, patents) are amortized over their useful lives (typically 3-15 years).

Annual_Amortization = Intangible_Asset_Cost / Useful_Life

---

5. BUILDING THE WORKING CAPITAL SCHEDULE

Working capital is the difference between operating current assets and operating current liabilities.

A. FORECASTING WORKING CAPITAL COMPONENTS:

B. THE WORKING CAPITAL CALCULATION:

NWC = A/R + Inventory + Prepaids – A/P – Accrued_Expenses

ΔNWC = NWC_t – NWC_{t-1}

C. THE CASH CONVERSION CYCLE (REVISITED):

CCC = DSO + DIO – DPO

A decreasing CCC indicates improved working capital efficiency.

---

6. BUILDING THE DEBT SCHEDULE

The debt schedule tracks the company’s outstanding debt, interest payments, and repayments.

A. DEBT COMPONENTS:

• Revolving Credit Facility (Revolver): A flexible line of credit that can be drawn down and repaid as needed.

• Term Loan: A fixed principal amount with scheduled repayments.

• Senior Notes / Bonds: Fixed-rate debt with bullet repayments at maturity.

B. THE DEBT SCHEDULE STRUCTURE:

C. INTEREST EXPENSE CALCULATION:

Interest_Expense = Average_Debt * Interest_Rate

Where:

Average_Debt = (Beginning_Debt + Ending_Debt) / 2

D. CASH SWEEP MECHANISM:

A cash sweep uses excess cash to repay debt early, reducing interest expense.

1. Calculate Excess Cash: Cash balance above a minimum cash threshold (e.g., $10M).

2. Apply Sweep to Debt: Use excess cash to repay the most expensive debt first.

3. Update Interest Expense: Recalculate interest expense based on the reduced debt balance.

The Cash Sweep Formula:

Excess_Cash = Max(0, Cash_Balance – Minimum_Cash)

Mandatory_Repayment = Scheduled_Principal_Repayment

Optional_Repayment = Min(Excess_Cash, Outstanding_Debt)

Total_Repayment = Mandatory_Repayment + Optional_Repayment

---

7. BUILDING THE BALANCE SHEET

The Balance Sheet is constructed using the relationships and schedules built above.

A. ASSETS:

• Cash: Ending cash from the Cash Flow Statement.

• Accounts Receivable: From the Working Capital Schedule.

• Inventory: From the Working Capital Schedule.

• Prepaid Expenses: From the Working Capital Schedule.

• PP&E (Net): From the Depreciation Schedule.

• Intangibles (Net): From the Amortization Schedule.

• Other Assets: Historical growth or % of Revenue.

B. LIABILITIES:

• Accounts Payable: From the Working Capital Schedule.

• Accrued Expenses: From the Working Capital Schedule.

• Short-Term Debt: From the Debt Schedule.

• Long-Term Debt: From the Debt Schedule.

• Other Liabilities: Historical growth or % of Revenue.

C. EQUITY:

• Share Capital: Historical or flat.

• Additional Paid-In Capital: Historical or flat.

• Retained Earnings: Beginning Retained Earnings + Net Income – Dividends.

• Treasury Stock: Historical or flat.

D. THE BALANCE SHEET CHECK:

Total_Assets = Total_Liabilities + Total_Equity

---

8. BUILDING THE CASH FLOW STATEMENT

The Cash Flow Statement is derived from the Income Statement and the changes in the Balance Sheet.

A. OPERATING CASH FLOW (CFO):

CFO = Net_Income + Non_Cash_Charges + Changes_in_Working_Capital

Non-Cash Charges:

• Depreciation & Amortization.

• Stock-Based Compensation.

• Deferred Taxes.

Changes in Working Capital:

ΔNWC = (A/R_t – A/R_{t-1}) + (Inventory_t – Inventory_{t-1}) – (A/P_t – A/P_{t-1}) – (Accruals_t – Accruals_{t-1})

B. INVESTING CASH FLOW (CFI):

CFI = -CapEx – Acquisitions – Purchases_of_Investments

C. FINANCING CASH FLOW (CFF):

CFF = Debt_Drawdowns – Debt_Repayments + Equity_Issuance – Dividends – Stock_Repurchases

D. CASH BALANCE RECONCILIATION:

Ending_Cash = Beginning_Cash + CFO + CFI + CFF

---

9. CIRCULAR REFERENCES AND ITERATIVE CALCULATIONS

A. THE CIRCULAR REFERENCE PROBLEM:

Interest expense depends on the average debt balance, which depends on cash sweep repayments, which depend on cash balance, which depends on interest expense.

This creates a circular reference: Interest Expense → Cash Flow → Cash Balance → Cash Sweep → Debt Balance → Interest Expense.

B. SOLVING CIRCULAR REFERENCES:

1. Enable Iterative Calculation: In Excel, go to File → Options → Formulas → Enable Iterative Calculation. Set Maximum Iterations to 100 and Maximum Change to 0.0001.

2. Use Circular Reference Breakers: Build the model in stages (e.g., first run without cash sweep, then add cash sweep and re-run).

3. Use a Circular Reference Plugin: Some financial modeling software has built-in circular reference handlers.

C. THE CIRCULAR REFERENCE CHECK:

Run the model until all circular references converge (no change in values between iterations). The model is balanced when the Balance Sheet check equals zero.

---

10. SCENARIO ANALYSIS

Scenario analysis evaluates the financial model under different sets of assumptions.

A. DEFINING SCENARIOS:

B. IMPLEMENTING SCENARIOS:

Use an input cell (a dropdown menu) to select the scenario. Use nested IF statements or CHOOSE functions to switch between assumptions.

Revenue_Growth = IF(Scenario=1, 15%, IF(Scenario=2, 20%, IF(Scenario=3, 10%, 5%)))

C. SCENARIO OUTPUT:

Generate a summary table showing key metrics for each scenario:

---

11. SENSITIVITY TABLES (DATA TABLES)

Sensitivity tables show how the output changes when two input variables are varied simultaneously.

A. ONE-WAY DATA TABLE:

Vary one input variable and observe the change in the output.

B. TWO-WAY DATA TABLE:

Vary two input variables and observe the change in the output.

C. SPIDER CHARTS (TORNADO CHARTS):

Tornado charts show the sensitivity of the output to each input variable individually.

---

12. FINANCIAL MODELING BEST PRACTICES

A. COLOR CODING:

B. LOGICAL FLOW:

• Left to Right: Inputs → Calculations → Outputs.

• Top to Bottom: Revenue → Expenses → EBITDA → EBIT → Net Income.

• One Sheet: Keep each section on a separate worksheet (Inputs, Assumptions, Income Statement, Balance Sheet, Cash Flow, Schedules, Valuation).

C. MODULAR STRUCTURE:

D. AUDIT TRAILS AND ERROR CHECKS:

E. CLEAR DOCUMENTATION:

• Use clear, descriptive labels for every cell.

• Add comments explaining complex formulas.

• Include a “Model Overview” or “Read Me” sheet.

---

13. PRESENTING THE MODEL

A. KEY METRICS SUMMARY:

B. CHARTS AND GRAPHS:

1. Revenue and EBITDA Growth Chart: Shows the historical and projected growth trajectory.

2. Margin Trend Chart: Shows gross margin, operating margin, and net margin over time.

3. Debt Repayment Schedule Chart: Shows the decline in debt over time.

4. Valuation Waterfall Chart: Shows the bridge from Enterprise Value to Equity Value to Per Share Value.

5. Sensitivity Heatmap: A color-coded table showing valuation under different scenarios.

C. KEY ASSUMPTIONS DISCLOSURE:

Transparently disclose all key assumptions:

• Revenue growth rates.

• Operating margins.

• WACC and terminal growth.

• Debt financing structure.

• Depreciation and amortization policies.