Modeling I: The 3-Statement Model

Learning objectives

When the build is finished you can:

1. Architect a workbook the professional way: inputs, calculations, and outputs separated; one consistent timeline grid on every tab; blue-font inputs, black formulas, green cross-sheet links; one formula per row filled across; zero hardcodes inside formulas. State why each rule exists in terms of error rates and auditability.
2. Spread three years of historical statements from a filing into a model as typed facts, compute the full driver-ratio panel from them (growth, margins, DSO/DIO/DPO, capex intensity, depreciation rate, effective tax and interest rates, payout), and prove the historicals articulate before forecasting a single number.
3. Build the four core schedules from blank cells: a days-based working-capital schedule, a PP&E corkscrew (and a capex-vintage depreciation waterfall when precision matters), a term-debt schedule, and an equity schedule with retained-earnings roll, dividends, buybacks, and a share-count roll.
4. Model a revolver with a cash sweep and a minimum-cash floor using MAX/MIN logic, so the facility draws exactly what the model needs and sweeps surplus cash against itself automatically, and trace why the bear case draws and the base case never does.
5. Handle the revolver–interest circularity three ways: interest on opening balances, average balances with iterative calculation enabled, and a circularity-breaker switch. Quantify the difference between them on the same model, and choose deliberately, knowing each method's failure mode.
6. Assemble the three statements from the schedules with no plugs: an income statement whose only inputs are drivers, a balance sheet where every line is a schedule link, and an indirect cash flow statement built line by line from statement deltas that ties to the balance-sheet cash to the paisa.
7. Install error checks as first-class citizens: balance-sheet-balances = 0, cash-flow-to-cash tie = 0, sources-equal-uses reconciliations between every financing line and its schedule, all aggregated into one master status cell; and run a disciplined debugging protocol when any check fails, locating the error from the size, pattern, and first year of the imbalance instead of poking formulas at random.
8. Wire a base/bull/bear scenario system with INDEX (or CHOOSE) off a single switch cell, so the entire model (statements, schedules, revolver, checks) flips cases instantly, and read what the three futures say about the business.

Prerequisites & connections

Builds on. M1.05 is the direct parent: the ten ties, the four roll-forwards (cash, retained earnings, PP&E, debt), the working-capital sign logic ("asset up, cash down"), the D&A triple path, and the revolver–interest circularity as a concept. All of that gets rebuilt in cells this week, so if any of it is rusty, re-run M1.05's cascade drill before you open the workbook. M1.02–M1.04 supply the statement anatomy. M2.02 gave you DSO/DIO/DPO and the cash conversion cycle. Here they become forecast drivers rather than diagnostic outputs. M2.04 (CFO/EBITDA, FCF mechanics) tells you what the model's cash engine should look like when it is healthy. M3.04 disciplined your assumptions (revenue architectures, margin logic, the g = RR × ROIC identity, fade); M3.05 taught scenarios and sensitivity, and the toggle you build here is their permanent home. M0.05 (tool setup) assumed you have a spreadsheet program installed; any of Excel, Google Sheets, or LibreOffice Calc works for every formula in the build, and the few places the three dialects differ are flagged in boxes.

Feeds forward. M3.09 bolts a DCF (FCFF build, WACC link, equity bridge), a comps football field, and a starter LBO onto this exact workbook. Do not skip ahead: the LBO's debt waterfall is this week's revolver logic with more tranches. M3.10's capstone requires a working model per company. Phase 5's sector playbooks swap Kaveri's consumer-goods drivers for sector-specific ones (loan growth and NIM for a bank; ARR and NRR for SaaS) on the same chassis. Phase 8's Day-2 "3-statement-lite" is this model built in ninety minutes with 3–5 drivers. The data-and-tools thread (master brief §8.3) is served directly: spreadsheet mastery, no hardcodes, keyboard fluency. And C5 in the competency map, value a business three ways, is certified partly on models descended from this one.