🧱 1. Structuring a 3-Statement Model

A 3-statement model links the Income Statement, Balance Sheet, and Cash Flow Statement so changes flow through all financials consistently.

🔹 A. Model Structure Overview

Section Description
Inputs/Assumptions Drivers like revenue growth, margins, interest rates
Income Statement Revenue, expenses, taxes, net income
Balance Sheet Assets, liabilities, equity
Cash Flow Statement Operating, investing, and financing cash flows
Supporting Schedules Working capital, depreciation, debt, equity
Outputs/Dashboard KPIs, ratios, charts, valuation outputs

🔹 B. Linking the Statements

  • Net Income from the Income Statement flows into:

    • Retained Earnings on the Balance Sheet

    • Operating Activities in the Cash Flow Statement

  • CapEx & Depreciation affect:

    • Cash Flow from Investing

    • PP&E on the Balance Sheet

    • Depreciation Expense on Income Statement

  • Debt and Equity Changes flow through:

    • Financing section of Cash Flow

    • Liabilities and Equity on the Balance Sheet

💡 Golden Rule: The model must balance.
Assets = Liabilities + Equity always.

📊 2. Scenario and Sensitivity Analysis with Data Tables

🔹 A. Scenario Analysis

Compares multiple macro or strategic situations (e.g., Base, Upside, Downside).

  • Use a dropdown + CHOOSE() or IF() to switch between assumption sets.

  • Scenarios impact multiple drivers (e.g., revenue growth, cost inflation, tax rate)

🔹 B. Sensitivity Analysis

Tests how individual variables affect outputs (e.g., Net Income, Valuation).

Use Excel’s Data Table Tool:

  1. Set up a base case output (e.g., Net Present Value).

  2. Create a table with variable(s) to test.

  3. Go to Data → What-If Analysis → Data Table.

1-Variable Table: Vary one input (e.g., discount rate)
2-Variable Table: Vary two inputs (e.g., growth rate vs. margin)

 

🛠️ 3. Auditing Tools and Error-Checking Techniques

Accuracy is critical. Use these tools and habits to avoid mistakes:

🔹 A. Excel Tools for Auditing

Tool/Feature Use Case
Trace Precedents/Dependents See formula inputs and outputs
Evaluate Formula Step-by-step breakdown of a formula
Error Checking Identify broken formulas or references
Go To Special → Constants/Errors Highlight cells with hardcodes or errors

🔹 B. Common Error Checks

  • Check if Balance Sheet balances every period.

  • Ensure Cash Flow Statement totals to change in cash.

🔹 C. Modeling Best Practices

Practice Why It Matters
Use consistent color coding (e.g., blue for inputs, black for formulas) Enhances clarity
Keep inputs and outputs separate Easier troubleshooting
Use named ranges or dynamic references Avoids broken formulas
Include a change log or notes Helpful for collaboration and auditing

🧠 Summary Table

Component Description
3-Statement Structure Connects P&L, BS, CF for financial logic
Scenario Analysis Compares base, best, worst case models
Sensitivity Analysis Tests how key drivers affect outcomes
Auditing Tools Prevents and detects model errors
Modeling Discipline Ensures clarity, traceability, and accuracy
PreviousNext