Modeling I: The 3-Statement Model

Phase 3 · Week 33 · ~20 hours

Where it sits. One cell in this week's workbook flips a five-year forecast between base, bull, and bear. Building the machine behind that cell is the eighth stop in the valuation phase, and the week the program's two mechanical spines finally weld together.

Earlier you built the three-statement linkage on paper: the ten ties, the roll-forwards, the cascade drills. It was the highest-stakes gate in Phase 1 for exactly this reason, because everything you have learned since assumes the loop is automatic. More recently you learned to forecast, with revenue architectures, the g = reinvestment × ROIC discipline, terminal values and scenarios. Now the two fuse. You will open a blank workbook and construct, cell by cell, a fully-linked three-statement model: historical spreading, ratio-driven assumptions, working-capital, PP&E, debt and equity schedules, a revolver with a cash sweep, error checks that scream when anything breaks, and that one-cell scenario toggle. The circularity you met as a concept on paper (revolver → interest → net income → cash → revolver) you will now handle three different ways in live cells, and you will know when each is the right choice.

The build-along is deliberate: every tab, row, and formula is specified, and checkpoint tables let you verify your machine against mine to the decimal. The next build bolts the DCF, comps, and LBO onto the chassis you build this week; the phase capstone and every deep dive you ever run (Phase 8, Day 2: "build a 3-statement-lite model driven by 3–5 assumptions") stand on it. A model is how an analyst thinks in public. Question 5 of the five-questions spine, what is the price implying and is that beatable, is answered with exactly this machinery, and question 2 (is ROIC durably above the cost of capital?) is checked inside it.

Budget 9h for the core build-along, done at the keyboard rather than in an armchair. Then 2h of worked examples, 3h of practice, 4h for the mini-project (a real company's model, end to end) and 2h for flashcards and the mastery check.

In the app, the Intrinsic Valuation branch's modeling layer runs on this material. Model-audit challenges hand you a seeded workbook with 3–5 planted errors, being hardcodes, sign flips and broken roll-forwards, which you find against the clock. Revolver puzzles give you the flows and a minimum cash and ask for the draw or the sweep by hand before the cell does. There are "find the hardcode" drills, and a check-failure simulator that breaks one tie in a balanced model and scores your debugging protocol. Run the audit queue daily this week.

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. It gave you 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 it 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 to M1.04 supply the statement anatomy. M2.02 gave you DSO/DIO/DPO and the cash conversion cycle, and here they stop being diagnostic outputs and become forecast drivers. M2.04 (CFO/EBITDA, FCF mechanics) tells you what the model's cash engine should look like when it's healthy. M3.04 shaped your assumptions with revenue architectures, margin logic, the g = RR × ROIC identity and fade. M3.05 taught scenarios and sensitivity, and the toggle you build here becomes 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 same workbook. Don't skip ahead, because the LBO's debt waterfall is this week's revolver logic with more tranches. M3.10's capstone needs 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 to 5 drivers. The course's data-and-tools thread is served directly too: 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.

A three-statement model is the accounting loop you already own, rebuilt as a machine with three layers: typed facts and assumptions in, schedules that roll balances in the middle, statements and checks out. The craft is keeping those layers so clean that when a check fails, the machine itself tells you where to look.

Core teaching content

4.1 What a model actually is: and the machine you are about to build

Strip the mystique first. A three-statement model is the closed loop you traced on paper, written down so a computer can re-run it under different futures. On paper you traced one year in forty minutes. In a workbook you trace five years in a tenth of a second, under any assumptions, as many times as you like. A model is a thinking tool that makes your assumptions explicit, consistent, and cheap to change. It isn't a truth machine. A model that balances isn't right, only internally consistent, and that's the precondition for being usefully wrong. Damodaran's phrase from your forecasting work applies with full force. Your forecast is a story told in numbers, and the model is the grammar that keeps the story from contradicting itself.

Hold the shape of the finished machine before laying the first brick:

INPUTS (tab A)              CALCULATIONS (tab M)                    OUTPUTS (tab X)
----------------            ------------------------------          ------------------
Scenario switch    ------>  Revenue & margin build                  Summary table
Driver blocks               Working-capital schedule                (per year, per case)
 (base/bull/bear)           PP&E corkscrew
Structural inputs  ------>  Term debt + interest          ------>   ERROR-CHECK PANEL
Historical ratios           Revolver & cash sweep                   - BS balances = 0
 (computed from             Equity & share count                    - CFS ties to cash = 0
  typed history)            ------------------------------          - sources = uses = 0
                            Income statement                        - MASTER STATUS CELL
                            Balance sheet
                            Cash flow statement

Three layers, three tabs. Every number in the model is one of three things: a fact (typed history, in blue), a choice (a typed assumption, in blue, on the inputs tab only), or a consequence (a formula in black, or a green cross-sheet link). When a model misbehaves, the first diagnostic question is always the same. Which of the three is this cell supposed to be, and is it? Most broken models die right here. Either a choice got buried inside a consequence, like a hardcoded 1.1 inside a revenue formula, or a consequence got overwritten by a "temporary" fact that became permanent.

You'll build the machine for Kaveri Consumer Products Ltd, a synthetic Indian packaged-foods company. Think of a small Dabur: brands, distribution, steady 10% growth, 15% EBITDA margins, modest debt. It's synthetic on purpose. Every number is clean, every check lands on zero, and the full solution is printed here, so you always know whether your machine matches mine. Its fiscal year ends 31 March, the Indian convention, so "FY25" means the year ended 31 March 2025. All figures are in ₹ crore. Three years are history (FY23 to FY25) and five are forecast (FY26 to FY30). The mini-project then makes you do it all again for a real company, where the numbers are dirty and the judgment is yours.

You could download a template in thirty seconds. You'll build every cell instead, for the same reason you once traced the loop by hand. The working agreement says do it by hand before you automate it, because a template teaches you where the answers go, while a blank workbook teaches you where they come from. Analysts who only ever drove templates are helpless the day the company in front of them doesn't fit one. In India, with its cash-credit working-capital facilities, Schedule III layouts, and promoter-group structures, that's most days.

4.3 Build phase 1: spread the history (typed facts, proven to articulate)

Start with the grid. Create the three tabs A, M, X. On A, row 1 holds the title (KAVERI CONSUMER PRODUCTS LTD (synthetic) — INPUTS — ₹ crore). Row 3 holds the year headers typed across C3:J3 (FY23A, FY24A, FY25A, FY26E, FY27E, FY28E, FY29E, FY30E), and row 4 the actual/estimate flags (A,A,A,E,E,E,E,E). On M and X, rows 3 and 4 link to A's in green font. In M cell C3 that's =A!C3, filled right, and the same goes for row 4. If you ever re-date the model, you change one tab and the headers follow everywhere.

The control panel occupies tab A, rows 5 to 10. Type labels in column A and values in column C, all in blue:

C7 is your first formula and your first sight of CHOOSE. It returns the 1st, 2nd, or 3rd listed item according to C6. Add a data-validation dropdown to C6 so the switch physically can't hold a 4. The menu is Data → Data validation → list of items 1,2,3, and it has the same name in all three programs. On M, put Active scenario: in A2 and =A!C7 in C2, so the model tab always announces which future it's computing. Do the same on X.

Now type the history onto tab M. Below are Kaveri's three reported years. Lay out the income statement in rows 6 to 21 and the balance sheet in rows 23 to 43 of M, row for row as mapped. The row numbers matter, because every formula here references them. If you deviate, keep your own map and translate. Type the blue cells, then enter the black-formula rows across all columns C:J now. Derived rows like gross profit are formulas even in historical columns, because a subtotal is a consequence and never a fact:

Income statement (M, rows 6–21):

The MAX in the tax formula is cheap insurance. If a stress scenario ever drives PBT negative, the model books zero tax instead of a fictitious refund. Real loss-year tax involves deferred-tax assets and carryforwards, which belong with your earlier work on liabilities and financing. A simple model floors at zero and says so in the README.

Balance sheet (M, rows 23–43):

Row 43 is the first error check, and it goes in now, before any forecasting. A check installed after the fact confirms nothing. Fill it all the way to J even though F:J are still empty. The empty forecast columns will show garbage or zeros until the revolver closes the machine, and that's fine. Verify the three historical columns: 0.0, 0.0, 0.0. For FY23, assets are 89+120+100+20+340 = 669.0, which equals 75+30+0+225+50+289. FY24 balances at 712.0 on both sides, and FY25 at 763.0. Format the check row bold, and give it conditional formatting that turns the fill red whenever the cell value ≠ 0. Keep a tolerance of ±0.01 in your head, because floating-point arithmetic can leave dust of 10⁻¹⁰, which displays as 0.0.

Historical columns are facts. They hold what the company reported. You type them, and you never calculate them. Make FY24 revenue a formula (=C7*1.1, because growth "was about 10%") and you've replaced the company's history with your own fiction. Every ratio you calibrate from it inherits the fiction. When you spread, type what the filing says, to the reported precision, and let the derived rows be formulas: subtotals, margins, checks. Sometimes the statements won't articulate to the last decimal, because real filings carry small reclassifications. When that happens, you document the gap. You never massage a typed fact to force a tie.

Prove the history articulates before you trust it, the way you did on paper. Kaveri's does, to the decimal. Take the retained-earnings roll first. For FY24 it's 289.0 + 78.0 net income − 23.0 dividends = 344.0, and for FY25 it's 344.0 + 90.0 − 27.0 = 407.0. You'll get the dividend figures with the cash flow statement later. Then the cash tie. When you build the cash flow statement, its live formulas will reconstruct FY24 and FY25 and land on the typed cash balances: 89, then 87, then 90. A model whose history doesn't balance is broken before it forecasts anything.

Now for the payoff of spreading, the driver panel. It puts computed history on the inputs tab. On tab A, each driver row runs C:E as a computed historical ratio and F:J as your assumption. Those historical cells are green, because they read M's typed facts. Past and future share one ribbon, the way a professional assumptions page reads, so you see what the business did immediately left of what you claim it will do. Compute these now. Scenario blocks around them come next. Row numbers are A-tab rows, given in full in the next section's map:

Two teaching points hide in the blanks and the parentheses. First, any ratio built on an opening balance or a flow statement loses its first year. FY23's DSO exists, because it's a same-year balance over a same-year flow. FY23's depreciation rate doesn't, because there's no FY22 PP&E in the model. A spread that shows a number in those cells is making one up. FY23 capex of ₹55 cr is given in the case pack so the text table can show 5.50%, but the workbook cell stays blank until a CFS column exists. Second, effective rates rarely equal headline rates. Kaveri's interest expense runs ~9.3% of opening term debt against a 9.0% contractual rate, and fees and timing make up the gap. Its yield on cash bounced from 4.5% to 5.7% on balance timing. You calibrate assumptions from these ratios without expecting them to be laboratory-clean.

Spread a real company once this week, for calibration, with numbers dirtier than Kaveri's. It doubles as the mini-project's first hour. For Dabur India, pull the consolidated statements from the annual report (dabur.com → investor relations, or the BSE/NSE company page) or export from screener.in (free account → company page → "export to Excel"). On ~FY25 scale you're spreading roughly ₹12,400 cr of revenue, ~46 to 47% gross margins, ~19 to 20% EBITDA margins, and in places a negative-working-capital flavor, where payables are financed by brand power. Those figures are illustrative, modeled on ~FY24/FY25 scale, so pull the actual filing. For Home Depot (US), take the 10-K from SEC EDGAR (full-text search "Home Depot 10-K"). On ~FY25 scale that's roughly $160bn revenue, ~33.4% gross margin and ~13% operating margin. It also brings two instructive wrinkles you'll have to extend the Kaveri chassis for. One is a 52/53-week retail fiscal calendar. The other is an equity section dominated by treasury stock from two decades of buybacks. Equity was pushed negative between 2019 and 2022. That was a buyback bookkeeping artifact, and the business wasn't insolvent. You'll see where treasury stock plugs into the equity schedule when you build it. Same grid, same driver panel, same checks. A standard architecture keeps the shape of the work fixed and lets only the numbers change.

4.5 Build phase 3: revenue to EBITDA: the model breathes

Enter the income-statement forecast formulas from the history map. It's fifteen minutes of typing, and your first taste of the architecture paying rent:

• F7 Revenue: =E7*(1+A!F15), the prior year grown by the ACTIVE rate. Fill right to J7. The growth memo row 8 fills right automatically.
• F9 COGS: =F7*(1-A!F20), revenue times (1 − gross margin). Driving COGS through the margin, instead of giving it its own growth rate, keeps the P&L internally consistent. One driver, one consequence.
• F12 Operating expenses: =F7*A!F25.
• Rows 10, 11, 13, 14 (gross profit, margins, EBITDA) were filled across C:J when you typed the history, and now they light up.

Checkpoint 1 (Base, switch = 1). Revenue FY26 to FY30: 1,331.0 · 1,464.1 · 1,595.9 · 1,723.5 · 1,844.2. EBITDA: 199.7 · 219.6 · 239.4 · 258.5 · 276.6. Margin holds at 15.0% every year, 40% gross minus 25% opex, flat by construction. That's worth a beat. If your base case's margin never moves, say so out loud and mean it. Rows 15 to 21 still show zeros or errors, because depreciation, interest, and tax wait on their schedules. That's correct, so resist the urge to type anything into them.

4.12 Error checks as first-class citizens: and the fault-trace protocol

An unchecked model is a rumor with formatting. The professional standard treats checks as part of the model's product, built in from the start. They're the difference between "I believe it balances" and "it is currently proven to balance, and will re-prove itself every time anyone touches anything." You already have two checks living inside M (rows 43 and 67). Now build the X tab, the aggregation layer that makes every failure visible from one screen.

Lay out the X tab with rows 1 to 4 as on the other tabs: title, scenario echo =A!C7, year headers =M!C3 filled right, A/E flags. Then add the checks:

Rows 8 to 10 are the sources-equal-uses family. Every financing flow exists twice in this architecture, once on the CFS (derived from balance-sheet deltas) and once in a schedule (derived from drivers), and each pair must agree to the paisa. That duplication is deliberate. It's triangulation. A single derivation can be wrong without anyone noticing, but two independent derivations can only be wrong loudly. SUMPRODUCT(ABS(...)) totals absolute failures across the whole panel, with blanks counting as zero, and it works identically in Excel, Sheets, and LibreOffice. Its 0.02 tolerance forgives floating-point dust of a few thousand rupees on a ₹1,800 crore model. It won't forgive a real error, whose signature is orders of magnitude larger.

Three habits complete the system. (1) Conditional-format every check row so anything not equal to 0 gets a red fill, and a failure is visible from across the room. (2) Put the master status where you live. Freeze the top rows of M and add =X!C13 next to the scenario echo, so every keystroke happens under the light. (3) Never, ever delete or override a failing check. Of all amateur habits, "plugging" does the most damage. It means typing a balancing number into the balance sheet or, worse, editing the check formula to =0. A plugged model is worse than a broken one. A broken one confesses. A plugged one testifies falsely, and every downstream valuation inherits the lie. The rule from the working agreements applies to cells too: fix it at the source, or leave it red.

The fault-trace protocol. A check just went red. Amateurs start editing formulas at random, and the error usually survives and breeds. Work the protocol instead. It's the debugging table from your paper linkage work, upgraded for a live workbook, and it finds nearly everything in under ten minutes:

1. Freeze the machine. Set the scenario to Base (C6 = 1) and the circularity switch to 0. Debug the simplest configuration that still fails. Iteration and stressed scenarios add noise. Change nothing else yet.
2. Find the first failing column. Errors propagate rightward through the daisy chains, so the leftmost red column is where the bug was born. Everything to its left is exonerated, so think only about formulas that act in that column.
3. Read the size. The imbalance is the fingerprint of the missing or doubled item, so compare it against that column's line items:

1. Check the direction. If assets exceed liabilities + equity, either an asset is overstated (a missed outflow inflating cash, a capex double-count in PP&E) or a claim is missing (revolver not linked, RE too small). The excess side names the neighborhood.
2. Audit the daisy chains. For each of the four corkscrews (cash, retained earnings, PP&E, and debt both term and revolver), confirm that the opening in each column equals the prior column's closing. Then confirm the BS row links the schedule's closing row and not its opening, which is the classic one-row miss. Each check takes thirty seconds with trace-precedents. In Excel use Formulas → Trace Precedents / Ctrl+[, in LibreOffice Tools → Detective, and in Sheets press F2 on the cell and follow the colored ranges.
3. Run the articulation audit. Go down the balance sheet line by line. Every line's year-over-year delta must appear in one CFS line and only one, or be NI/dividends inside the RE roll. Cash is row 64. Rows 48 to 52 carry AR, inventory, OCA, AP, OCL. PP&E moves through capex and depreciation, the revolver through row 59, the term loan through row 58. Share capital is static. RE moves with NI, dividends, buybacks. Any line you can't tick is the hole, and any line ticked twice is the double-count.
4. Fix at the source, then re-arm. Correct the one formula and confirm the whole check panel clears. Then re-test the entire configuration space: all three scenarios, with the circularity switch both ways (iteration on for switch = 1). A fix that survives one scenario and dies in another was a coincidence.

Hold on to the bigger lesson here. The checks don't tell you the model is right. They make your errors loud, early, and cheap. You'll make errors this week, as everyone does. Your architecture decides whether they cost you two minutes or two days, or, if a check was missing, a wrong valuation you defended in public.

4.13 Build phase 10: flip the switch: reading three futures

The machine is built, so now use it as an analyst. Change A!C6, one keystroke, and watch roughly two hundred formulas re-agree on a different future. Verify each scenario against the table. All checks must show ALL CLEAR in all three, and if a scenario breaks a check, run the fault-trace protocol:

Now read the futures the way the memo will need them. You built the toggle for what the new numbers say, more than for the fact that they change:

• Base is boring, and should be. Cash compounds, the revolver sleeps, leverage melts (net debt swings from +85 to −133 over five years). A base case with drama in it is usually a mis-specified base case.
• Bear teaches the working-capital lesson of a career. FY26 EBITDA falls 47%, from 199.7 to 106.7 across cases, which is painful but survivable. What nearly empties the till is the balance sheet. Receivables and inventory swell while payables shrink, and that consumes ~₹71 cr of cash in one year. CFO is crushed to 11.5 against ₹47 cr of capex, even after the slashing, and ₹35 cr of debt service and dividends. Recessions kill companies through working capital faster than through profits, and you now have a machine that shows it, year by year. Notice too how hard the model's CFO recovers in FY27 and FY28 (84.4, 125.6). Once days stabilize, the working-capital drain stops, even though revenue is still weak. Levels hurt once. It's the change that bleeds cash, the lesson from your working-capital work, now animated.
• Bull hides a governance question in row 97. Growth is up and margins are up, and yet by FY29 the revolver is drawing. Why? Capex at 6% of a bigger revenue base, plus ₹75 cr/yr of buybacks, outruns even bull-case cash generation once the floor binds. Your model has caught management borrowing at 10% to retire stock, the pattern you met in capital allocation, where buyback programs turn into debt-funded commitments. Whether that's brilliant or vandalism depends on whether the stock trades far below intrinsic value, and that's the capital-allocation question you've already studied. That the model surfaces it unprompted is this week's own lesson. A well-built model is a tripwire for stories that don't cash-flow.

Try two five-minute experiments before moving on. They build the reflex of interrogating the machine. (1) Raise the minimum cash in A!C10 from 50 to 150 in Base. Watch the FY26 revolver draw 52.3 (= 150 − 97.7) instantly, with every check still passing, so the valve works at any floor. (2) In Bear, set FY26 DSO/DIO/DPO to base-case values and watch CFO jump from 11.5 to about 85 (85.6). One edit proves the FY26 crisis came from working capital. Profitability wasn't the cause. Restore both afterwards. Formal sensitivity tools, meaning data tables and tornado charts, came with your scenario work and return in the next build. Excel's Data Table feature has no native Sheets equivalent, which is one reason scenario rows like yours are the more portable design.

4.15 From Kaveri to the real world: statements that don't cooperate

Kaveri's chassis is universal on purpose, but real filings will make you extend it. It's worth naming the extensions before the mini-project throws them at you.

Real balance sheets carry lines Kaveri doesn't have. Hold goodwill and intangibles flat unless they're amortizing or impairing, since a forecast that predicts impairments is a strange animal, and note the policy. Small models hold deferred tax assets and liabilities flat or fold them into an effective-rate assumption, and the README says which. Lease right-of-use assets and liabilities come below. Investments and associates hold flat or roll with share of profits. Minority interest rolls like a mini retained earnings: opening + NCI share of profit − NCI dividends. Here's the rule of thumb. Material and driveable lines get a corkscrew and a driver. Immaterial or unforecastable ones are held flat, with the choice disclosed. Every line you hold flat is an assumption of zero change. It's a forecast like any other, just a quiet one.

Standards differences change the mechanics too. You learned the economics in Phase 1, and here's how they bite a model:

Some practicalities are India-specific. Model the consolidated statements. Standalone-vs-consolidated is a promoter-group trap you learned in Phase 1, and screener.in's defaults matter, so check the toggle. On an Indian balance sheet, the working-capital facility often appears as "short-term borrowings: secured, from banks (cash credit)". That's your revolver row. Read the borrowings note for sanctioned limits versus drawn amounts, because the undrawn headroom is the liquidity story. Dividend modeling is simple after 2020, because DDT is abolished. Companies pay gross and shareholders are taxed. Promoter pledging, LODR disclosures, and contingent liabilities never enter the model's arithmetic. They go into the README and the memo, where judgment lives.

Others are US-specific. 52/53-week fiscal calendars (Home Depot, Costco) put a 2% extra week into one historical year in fourteen, so normalize growth rates or your fade logic calibrates on noise. Buybacks are the dominant equity flow, and that's where the treasury-stock row comes in. And US filers' segment disclosures (10-K Item 8 notes) usually support revenue builds by segment. That's the natural upgrade of row 7 into a small revenue schedule, the way your revenue-architecture work taught it.

The chassis plus these extensions will model perhaps 80% of non-financial companies. Banks, insurers, and NBFCs need a structurally different machine, because for them the balance sheet is the income engine. That's Phase 5's business. Until then, a careful modeler keeps one rule: don't force a lender into an operating-company chassis.

Common mistakes & how experts think differently

1. Plugging the balance sheet. The novice's balance sheet is off by ₹73 crore and the deadline is near, so a "balancing adjustment" line appears, or cash becomes =L&E − everything else. Now the model balances by definition and can never fail again. That means it can never warn again either, and the error it buried is still there, corrupting the CFS and every valuation downstream. Experts treat a red check as the model working correctly. The alarm is the feature. No model with a plug in it can be trusted, because a plug only gives the error a better hiding place.

2. Hardcodes inside formulas. =E7*1.1 runs fine, looks fine, and has just hidden a 10% growth assumption from the scenario toggle, the auditor, and future-you. A mid-formula fudge is worse: =F53+F56+F62+4.3, because something was off by 4.3 and the deadline won. Experts use a mechanical test. Select all, and every decision in the workbook must glow blue on the inputs tab. If auditors can't find your assumptions, neither can you.

3. Overwriting a formula row with values "just for one year." One cell of a row gets typed over, often during debugging. Now the row computes seven years and asserts one, and fill-right will never fix it because nobody knows it's broken. Experts protect one-formula-per-row like a structural wall. When they have to hard-set a cell temporarily, they give it a yellow fill so it can't be shipped by accident.

4. Iterative calculation switched on from day one. Someone enables it "so the circularity thing doesn't error," and from then on it resolves every accidental circular reference for the rest of the build without a word. A mistyped SUM range that includes its own total converges to a stable wrong number instead of crashing. Experts run the opposite sequence. Build acyclic, keep iteration off so accidents scream, and let the breaker switch be the only door circularity ever enters through.

5. Mixed sign conventions. COGS is positive in one block and negative in another. Dividends are entered negative on the CFS and then subtracted by the formula as well. That's the "off by exactly 2×" signature in the fault-trace table. Experts decide signs once, under Rule 6, write the convention in the README, and stop trusting their memory.

6. Working-capital days on the wrong base. DIO and DPO get computed on revenue historically but applied to COGS in the forecast, or the other way round. Each side is defensible on its own. It's the mismatch that shifts working capital by the gross margin, and nobody sees it happen. Two more errors belong to the same family. One is forecasting receivables as "% growth in AR" instead of days, which lets AR drift loose from revenue. That's the plausible-looking model where DSO walks from 45 to 71 with nobody watching. The other is hardcoding 365 while the README claims 360. Experts drive balances through the ratio. They keep one base and one convention, and they let the driver panel show past and future on one row so drift is visible.

7. The revolver modeled as an afterthought, or not at all. Cash goes negative in the bear case and the model just... reports negative cash, an asset that's really an undocumented loan at 0% interest. Or a revolver exists with no minimum cash, so the company runs on fumes. Or there's draw logic without a sweep, so the model borrows at 10% while ₹200 crore sits idle earning 4%. Experts model the policy, meaning floor, sweep, and facility. Then they read what the machine does under stress as analytical output. Bear-FY26's draw is the liquidity thesis, and treating it as mechanical noise misses the point of building it.

8. Precision worship. Daily working capital, monthly depreciation, five basis-point tax refinements, all bolted onto a revenue line that is, charitably, a ±15% guess. It's the false-precision disease from your forecasting work, relocated into cells. Experts allocate effort by leverage. Drivers get the thinking. Mechanics get the architecture, which is free once it's right. The circularity gets a switch and a shrug, because you measured it at 0.9%.

9. Building for size instead of for audit. Thirty tabs, colors as decoration, a "Calc_v7_FINAL_new" sheet nobody dares delete. The amateur equates bulk with rigor. An expert's pride runs the other way, toward the smallest model that answers the question, readable by a stranger in ten minutes, with checks visible from every tab. A model is a communication to your future self and your team, so C11's clarity standard applies to workbooks as much as to memos.

10. Trusting a model because it balances. This is the subtlest one. All checks green means the arithmetic is consistent. It says nothing about whether the future resembles it. A model wired to absurd assumptions balances perfectly. Experts hold to the hierarchy from your forecasting work: checks certify the machine, stories certify the assumptions, and neither can stand in for the other. A balanced model is where analysis starts.

Worked examples

Worked example 1: the FY26 base-case column, computed entirely by hand

Of everything here, this exercise is worth the most: before you trust the machine, become the machine once. Every figure below comes from the FY25 balance sheet and the base-case drivers you typed in. Your workbook must reproduce each one to the rupee (±0.01 for rounding). Work it on paper, then check cell by cell.

Income statement.

• Revenue = 1,210.0 × (1 + 10.0%) = 1,331.00
• COGS = 1,331.00 × (1 − 40.0%) = 798.60 → Gross profit = 532.40
• Opex = 1,331.00 × 25.0% = 332.75 → EBITDA = 532.40 − 332.75 = 199.65 (15.0%)
• Depreciation = 13.0% × opening net PP&E 374.0 = 48.62 → EBIT = 151.03
• Interest expense = 9.0% × opening term loan 175.0 + 10.0% × opening revolver 0 = 15.75
• Interest income = 4.0% × opening cash 90.0 = 3.60
• PBT = 151.03 − 15.75 + 3.60 = 138.88 → Tax = 25% × 138.88 = 34.72 → Net income = 104.16

Working capital, with each balance's delta from FY25:

• AR = 1,331.00 × 45/365 = 164.10 (Δ +14.10) · Inventory = 798.60 × 62/365 = 135.65 (Δ +10.65) · OCA = 2% × 1,331.00 = 26.62 (Δ +2.62)
• AP = 798.60 × 47/365 = 102.83 (Δ +7.83) · OCL = 3% × 1,331.00 = 39.93 (Δ +3.93)

On PP&E, capex = 5.5% × 1,331.00 = 73.21. Closing net PP&E = 374.00 + 73.21 − 48.62 = 398.59. The cell may show 398.58 or 398.59 depending on the rounding path, since it carries full precision inside.

In equity, dividends = 30% × 104.16 = 31.25 and buybacks are 0, so closing RE = 407.00 + 104.16 − 31.25 = 479.91. Shares stay at 25.0 crore, and the EPS memo comes to 104.16/25.0 = 4.17, in rupees per share.

Cash flow statement.

• CFO = 104.16 + 48.62 − 14.10 − 10.65 − 2.62 + 7.83 + 3.93 = 137.17
• CFI = −73.21 · CFF = −25.00 (term) − 31.25 (dividends) = −56.25
• Net change = 137.17 − 73.21 − 56.25 = +7.72, and opening cash of 90.00 closes at 97.72

Now the revolver valve. Cash before revolver = 90.00 + 137.17 − 73.21 − 56.25 = 97.72, a surplus of +47.72 over the ₹50 floor. Draw 0 and repayment 0, since nothing is outstanding. Closing revolver 0, closing cash 97.72.

Now prove the balance sheet. Assets: 97.72 + 164.10 + 135.65 + 26.62 + 398.59 = 822.68. Liabilities & equity: 102.83 + 39.93 + 0 + 150.00 + 50.00 + 479.91 = 822.67. That 0.01 difference is display rounding, and the cells, carrying full precision, show 0.00. Every tie you learned on paper is visible in the arithmetic. NI feeds RE (+104.16) and the top line of CFO. D&A takes its triple path: 48.62 in the IS, added back in CFO, inside the PP&E roll. Capex feeds PP&E, the working-capital deltas carry the correct signs, financing flows land on the debt and equity balances, and the net change lands on cash. One column, closed loop, no plug.

Worked example 2: bear-case FY26: a revolver draw, by hand

The bear switch (C6 = 3) reprices every driver. Here's the whole cascade, because this is the column that teaches why revolvers exist.

• Revenue = 1,210.0 × (1 − 2%) = 1,185.80. COGS at a 36% gross margin = 758.91, leaving gross profit of 426.89.
• Opex = 27% × 1,185.80 = 320.17 → EBITDA = 106.72 (9.0% margin, down from 15%)
• Depreciation stays at 48.62, because it runs on opening PP&E, so EBIT = 58.10. Interest expense of 15.75 and interest income of 3.60 give PBT of 45.95, tax of 11.49 and NI of 34.46. Profit fell 67%. Painful, but survivable.
• Now the balance sheet turns hostile. AR = 1,185.80 × 55/365 = 178.68, a Δ of +28.68 despite falling sales, because days stretched faster than revenue shrank. Inventory = 758.91 × 75/365 = 155.94 (Δ +30.94). OCA = 23.72 (Δ −0.28). AP = 758.91 × 40/365 = 83.17 (Δ −11.83). Suppliers tightened, and a falling payable consumes cash. OCL = 35.57 (Δ −0.43).
• CFO = 34.46 + 48.62 − 28.68 − 30.94 + 0.28 − 11.83 − 0.43 = 11.48. Read that against EBITDA of 106.72. The working-capital trap ate ~₹71 crore of operating cash, −71.6 net of the small OCA/OCL moves. In the base year the drag was only −15.6, where −14.10 − 10.65 − 2.62 + 7.83 + 3.93 nets out.
• Capex is slashed to 4%, so −47.43. Dividends = 30% × 34.46 = −10.34. Term repayment stays at −25.00. It's contractual, and recessions don't reschedule amortization.
• Cash before revolver = 90.00 + 11.48 − 47.43 − 25.00 − 10.34 = 18.71, which is ₹31.29 below the ₹50 floor.
• Now the valve. Draw = MAX(0, −(18.71 − 50)) = 31.29, which the cell shows as 31.28 at full precision. Repayment = −MIN(0, ...) = 0. The revolver closes at 31.3, and cash closes at 50.0, right on the floor.
• Balance proof: assets = 50.00 + 178.68 + 155.94 + 23.72 + 372.81 (= 374 + 47.43 − 48.62) = 781.15. L&E = 83.17 + 35.57 + 31.28 + 150.00 + 50.00 + 431.12 (= 407 + 34.46 − 10.34) = 781.14. That 0.01 gap is rounding, and the cells show 0.00.

Here's the analyst's sentence, which no P&L-only view could produce: Kaveri survives its recession year, but only by drawing a third of its likely sanctioned working-capital limit, while still paying a ₹10 crore dividend its board would, in reality, be debating. That sentence is the model's real output.

Worked example 3: the circularity solved by hand: fixed-point iteration you can feel

Take bear FY26 and upgrade interest to average balances (Method 2). Now interest depends on the closing revolver, which depends on the draw, which depends on interest. Iterate the way the spreadsheet does, starting from the Method-1 answer:

• Pass 0 (opening-balance solution): interest expense 15.75, revolver closing 31.28.
• Pass 1: Interest on term = 9% × (175 + 150)/2 = 14.625. Interest on revolver = 10% × (0 + 31.28)/2 = 1.564. Interest income = 4% × (90 + 50)/2 = 2.80, since closing cash is pinned at the floor. Total interest expense is 16.19 (+0.44 vs pass 0) and interest income falls by 0.80, so PBT falls 1.24 to 44.71. NI drops to 33.53 (−0.93) and dividends to 10.06 (−0.28). Cash before revolver falls by (0.93 − 0.28) = 0.65, so the draw rises to 31.94 and the revolver closes at 31.94.
• Pass 2: Revolver interest re-prices on the new closing, 10% × 31.94/2 = 1.597 (+0.033). NI falls another ~0.025, the draw rises ~0.017, and the revolver reaches 31.95.
• Pass 3: The correction is ~0.0004, below any tolerance. Converged: interest expense 16.22, NI 33.51, revolver 31.95, matching the bear figures in the circularity section.

Feel the two lessons in your fingers. First, the loop damps. Each pass's correction is a few percent of the last, the product of an interest rate and a payout leakage, so iteration converges in a handful of passes. Second, the entire fuss, fully converged, moved net income by ₹0.95 crore on ₹34 crore. That's visible, real, and second-order next to any driver. You now know from the inside what "Enable iterative calculation" does.

Worked example 4: the fault trace: diagnosing a broken model from its symptoms

A study partner sends you their Kaveri build: "the balance sheet won't balance, and weirdly the base case keeps drawing the revolver. I've been staring for an hour." You open X and read the panel like an ECG, running the fault-trace protocol:

• Steps 1 to 2 (freeze, then find the first failure): Base case, switch 0. BS check: 0.0 through FY25, then −72.9 in FY26, the first failing column, worsening to −153.6, −240.2, −331.8, −427.7. CFS-ties-to-cash check: 0.0 everywhere. Term-loan tie: 0.0. Revolver tie: 0.0. Distributions tie: +72.9, +80.7, +86.5, +91.6, +95.8. And the partner is right about the sweep. Base-case cash is pinned at 50.0 every year while the revolver climbs 25.2, 97.3, 172.7, 250.3, 329.1, a steadily compounding facility in what should be a cash-gushing base case. Something is bleeding cash that the equity side isn't seeing.
• Step 3 (read the size): −72.9 equals no single statement line, but NI − dividends = 104.16 − 31.25 = 72.91. And look at the increments. The imbalance grows each year by 80.7, 86.5, 91.6, 95.8, which match the distributions-tie failures, year for year. In the fault-trace table, that's the retained-earnings/dividends family, hit annually.
• Step 4 (direction): Assets fall short of L&E, so either cash is too low or equity is too high.
• Step 6, shortcut via the panel: The distributions tie is the smoking gun. The CFS's dividend outflow disagrees with the equity schedule's dividend by NI − div each year, to the decimal. Open the two cells. CFS row 60 reads =-F103, linked to net income instead of dividends (F104). So the CFS has been paying out the entire NI as "dividends". Its cash path loses NI − div every year, which forces the phantom revolver draws to hold the floor, while the equity schedule, which is correct, keeps the money in RE. Each statement is internally consistent, so the CFS-to-cash check passed serenely throughout. They're just consistent about different worlds. Only a cross-derivation tie could see the disagreement.
• Step 7 (fix at source, re-arm): Set F60 to =-F104 and fill right. Now the panel reads ALL CLEAR, the base-case revolver goes back to sleep, and cash resumes compounding to 183.1. Re-test bull and bear with the switch both ways, and everything is clear. Elapsed time with the protocol: four minutes. Note the deeper lesson for your journal, which is that each check sees a different failure class. The BS check saw that something was wrong, and the distributions tie saw what. A passing CFS check exonerated a whole family of suspects, and the misbehaving revolver was the economic symptom of something upstream draining cash. So checks come in panels instead of singletons, and you read the model's behavior as well as its check cells.

Worked example 5: a growth-year revolver draw, by hand (Meghna Foods, synthetic, ₹ crore)

Worked example 2 drew Kaveri's revolver in a recession. This one draws for the opposite reason, a profitable, fast-growing company out-investing its own cash. Same valve, opposite weather. Figures are to 2 dp. The workbook keeps full precision, so a by-hand sum of rounded parts can differ by ₹0.01, the rounding path worked example 1 flags. Meghna Foods (synthetic) is a small Indian packaged-foods maker. At its FY24 close it has cash 60.0, receivables 140.0, inventory 130.0, other current assets 20.0 and net PP&E 400.0, so assets total 750.0. Against that sit payables 90.0, other current liabilities 30.0, revolver 0.0, term loan 200.0, share capital 40.0 and retained earnings 390.0, so L&E is also 750.0. FY24 revenue was 1,000.0. Its FY25 base case runs revenue +14%, gross margin 38%, opex 26% of revenue, depreciation 12% of opening PP&E and capex a heavy 10% of revenue, because it's a new-plant year. The rest: DSO 50, DIO 70, DPO 45 (365-day year), OCA 2% and OCL 3% of revenue, term repayment 25.0, rates 9% term / 10% revolver / 4% on cash, tax 25%, payout 30%, minimum cash 50.0.

• Income statement. Revenue = 1,000.0 × 1.14 = 1,140.00. COGS = 1,140.00 × (1 − 0.38) = 706.80 → gross profit 433.20. Opex = 26% × 1,140.00 = 296.40 → EBITDA 136.80 (12.0%). Depreciation = 12% × 400.0 = 48.00 → EBIT 88.80. Interest expense = 9% × 200.0 + 10% × 0 = 18.00. Interest income = 4% × 60.0 = 2.40 → PBT = 88.80 − 18.00 + 2.40 = 73.20. Tax = 25% × 73.20 = 18.30 → net income 54.90.
• Working capital. AR = 1,140.00 × 50/365 = 156.16 (Δ +16.16). Inventory = 706.80 × 70/365 = 135.55 (Δ +5.55). OCA = 2% × 1,140.00 = 22.80 (Δ +2.80). AP = 706.80 × 45/365 = 87.14, a Δ of −2.86, and that falling payable consumes cash. OCL = 3% × 1,140.00 = 34.20 (Δ +4.20).
• PP&E and equity. Capex = 10% × 1,140.00 = 114.00 → closing net PP&E = 400.0 + 114.00 − 48.00 = 466.00. Capex far above depreciation is the signature of a build-out. Dividends = 30% × 54.90 = 16.47 → closing retained earnings = 390.0 + 54.90 − 16.47 = 428.43. Term loan closes at 200.0 − 25.0 = 175.00.
• Cash flow. CFO = 54.90 + 48.00 − 16.16 − 5.55 − 2.80 − 2.86 + 4.20 = 79.72. CFI = −capex = −114.00. CFF before the revolver = −25.00 (term) − 16.47 (dividend) = −41.47.
• The valve: the draw arm. Cash before the revolver = 60.0 + 79.72 − 114.00 − 41.47 = −15.75, which is ₹65.75 below the ₹50 floor. Draw = MAX(0, −(−15.75 − 50.00)) = 65.75. Repayment is 0, because nothing was outstanding. Your revolver closes at 65.75 and cash is pinned to 50.00. Net change in cash = 79.72 − 114.00 + (−41.47 + 65.75) = −10.00, so opening cash of 60.0 closes at 50.00.
• Balance sheet, proven. Assets = 50.00 + 156.16 + 135.55 + 22.80 + 466.00 = 830.51. Liabilities & equity = 87.14 + 34.20 + 65.75 + 175.00 + 40.00 + 428.43 = 830.52, and the 0.01 is display rounding. Full-precision cells read 830.52 on both sides, and the check row is 0.00.

Again, the analyst's sentence is one no P&L-only view could produce: Meghna is growing 14% and comfortably profitable, yet a single year of plant-building at 10% of sales out-runs its operating cash and forces it two-thirds into a working-capital facility. The revolver is funding growth, not distress, and the memo's job is to ask whether that new plant will earn back more than the 10% the borrowed rupees cost. The machine has surfaced a capital-allocation question on its own.

Worked example 6: a cash sweep that clears the revolver (Cascade Outdoor Co., synthetic, US $ million)

Worked example 5 drew the facility. This one repays it, and it carries the US buyback machinery, a treasury-stock repurchase, so you meet the sweep's other arm and the equity variant in one column. All figures are $ million, 2 dp. Cascade Outdoor Co. (synthetic) is a US outdoor-gear maker carrying a small revolver into the year. At its FY24 close it has cash 50.0, receivables 200.0, inventory 300.0, other current assets 30.0 and net PP&E 600.0, so assets total 1,180.0. Against them sit payables 150.0, other current liabilities 40.0, revolver 20.0, term loan 250.0, common stock & APIC 120.0, treasury stock −80.0 and retained earnings 680.0, so L&E is also 1,180.0. FY24 revenue was 2,000.0. FY25 assumptions run revenue +8%, gross margin 42%, opex 30% of revenue, depreciation 11% of opening PP&E and capex 4% of revenue. Then come DSO 34, DIO 88, DPO 44 (365-day year), OCA 1.5% and OCL 2% of revenue, term repayment 20.0, rates 7% term / 6% revolver / 3% on cash, tax 25%, payout 25%, a $40 buyback booked as treasury stock, and minimum cash 30.0.

• Income statement. Revenue = 2,000.0 × 1.08 = 2,160.00. COGS = 2,160.00 × (1 − 0.42) = 1,252.80 → gross profit 907.20. Opex = 30% × 2,160.00 = 648.00 → EBITDA 259.20. Depreciation = 11% × 600.0 = 66.00 → EBIT 193.20. Interest expense = 7% × 250.0 + 6% × 20.0 = 17.50 + 1.20 = 18.70. Interest income = 3% × 50.0 = 1.50 → PBT = 193.20 − 18.70 + 1.50 = 176.00. Tax = 25% × 176.00 = 44.00 → net income 132.00.
• Working capital. AR = 2,160.00 × 34/365 = 201.21 (Δ +1.21). Inventory = 1,252.80 × 88/365 = 302.04 (Δ +2.04). OCA = 1.5% × 2,160.00 = 32.40 (Δ +2.40). AP = 1,252.80 × 44/365 = 151.02 (Δ +1.02). OCL = 2% × 2,160.00 = 43.20 (Δ +3.20). Days barely move, so working capital consumes almost nothing, the mirror image of worked example 5's build-out.
• PP&E and equity. Capex = 4% × 2,160.00 = 86.40 → closing net PP&E = 600.0 + 86.40 − 66.00 = 620.40. Dividends = 25% × 132.00 = 33.00. The $40 buyback uses the treasury-stock method, so it leaves retained earnings alone, and closing retained earnings = 680.0 + 132.00 − 33.00 = 779.00. Instead the buyback deepens the contra to −80.0 − 40.0 = −120.00. Term loan closes at 250.0 − 20.0 = 230.00.
• Cash flow. CFO = 132.00 + 66.00 − 1.21 − 2.04 − 2.40 + 1.02 + 3.20 = 196.57. CFI = −capex = −86.40. CFF before the revolver = −20.00 (term) − 33.00 (dividend) − 40.00 (buyback) = −93.00.
• The valve: the sweep arm. Cash before the revolver = 50.0 + 196.57 − 86.40 − 93.00 = 67.17, a surplus over the $30 floor of 37.17. That surplus of 37.17 exceeds the outstanding revolver of 20.0, so the sweep repays the smaller of the two. Repayment = −MIN(20.0, 37.17) = −20.00, which retires the facility to 0.00, and the leftover surplus stays as cash. Closing cash = 67.17 − 20.00 = 47.17. This time cash lifts above the floor, because there was more spare cash than debt to absorb it. In worked example 5, by contrast, the draw pinned cash to the floor.
• Balance sheet, proven. Assets = 47.17 + 201.21 + 302.04 + 32.40 + 620.40 = 1,203.22. Liabilities & equity = 151.02 + 43.20 + 0.00 + 230.00 + 120.00 − 120.00 + 779.00 = 1,203.22. Your check row reads 0.00 to the cent, with no rounding dust at all.

The analyst's sentence: Cascade throws off far more cash than it needs, so the machine automatically retires its revolver and still ends richer in cash. But it did so while repurchasing $40 of stock, and the model has logged a capital-allocation choice, to sweep the debt and buy back shares, that the memo should test against the repurchase price. It's the same buyback question worked example 4's bull case raised for Kaveri.

Practice set

Work every problem before opening its solution (working agreement #3). Problems 1 to 4 are guided. Problems 5 to 10 are independent, and 11 to 12 are timed. Currency is ₹ crore unless stated, with a 365-day year throughout.

Problem 1 (guided: the three layers). Classify each of the following cells as fact, choice, or consequence. For each, state which tab it belongs on and what color its font should be. (a) FY24 reported revenue; (b) the FY28 bull-case gross margin; (c) FY27 forecast inventory; (d) the minimum-cash balance; (e) FY25 gross profit; (f) the master status cell; (g) a mid-build temporary hard-set of FY26 interest while you debug; (h) the year header row on the M tab.

Solution. (a) Fact: typed historical, M tab historical column, blue. (b) Choice: typed assumption, A tab scenario block, blue. (c) Consequence: formula (COGS × DIO/365), M tab schedule, black. (d) Choice: A tab control panel, blue. (e) Consequence, even in a historical column, because a subtotal is computed (=E7-E9), M tab, black. (f) Consequence: X tab, black (a formula over other checks). (g) A temporary fact standing where a consequence belongs, tolerable for minutes, never in a shipped model: yellow fill so it cannot be forgotten, then restored to formula. (h) Consequence: a green cross-sheet link (=A!C3), so the timeline has one home.

Problem 2 (guided: days both directions). (a) Revenue 730, COGS 438. Forecast DSO 50, DIO 75, DPO 40. Compute AR, inventory, AP. (b) A company shows AR of 120 against revenue of 876. What DSO is it running? (c) Your colleague computes DIO as inventory/revenue × 365 for history but forecasts inventory as COGS × DIO/365. Gross margin is 40%. If historical "DIO" reads 45 days, what will happen to modeled inventory in the first forecast year, all else equal?

Solution. (a) AR = 730 × 50/365 = 100.0. Inventory = 438 × 75/365 = 90.0. AP = 438 × 40/365 = 48.0. (b) DSO = 120/876 × 365 = 50.0 days. (c) The historical "45 days" was measured against revenue. Re-applied against COGS, which is 60% of revenue, the same 45 produces a balance only 60% as large. Modeled inventory drops ~40% across the boundary with no economic cause. Same number, different base, silent error. Neither convention is the bug. The mismatch between them is.

Problem 3 (guided: the corkscrew). PP&E opens FY1 at 500. Capex runs 12% of revenue; revenue is 1,000 in FY1 growing 10%. Depreciation is 15% of opening net PP&E. Roll the schedule two years.

Solution. FY1 opens at 500, with capex = 120 and depreciation = 75, so closing = 500 + 120 − 75 = 545. FY2 opens at 545. Capex = 12% × 1,100 = 132 and depreciation = 15% × 545 = 81.75, so closing = 545 + 132 − 81.75 = 595.25. Notice that each opening equals the prior closing. Build this in cells with the corkscrew pattern and the FY2 opening formula comes free from fill-right.

Problem 4 (guided: write the formulas). Use this build's grid: A tab row 15 growth-ACTIVE, row 30 DSO-ACTIVE, C9 days-in-year; M tab row 7 revenue. Write the exact fill-right-safe formulas for: (a) M!F7, forecast revenue; (b) M!F70, forecast receivables; (c) the ACTIVE growth selector A!F15, given scenario rows 12 to 14 and switch $C$6; (d) name each reference that carries $, and say why.

Solution. (a) =E7*(1+A!F15). (b) =F7*A!F30/A!$C$9. (c) =INDEX(F12:F14,$C$6). (d) Anchored: A!$C$9 and $C$6, single control cells that every column must keep pointing at. Unanchored: E7, F7, A!F15, A!F30, F12:F14, which have to slide one column right per year so each year reads its own driver. Everything turns on the $ decision. Anchor what is one-per-model, and slide what is one-per-year.

Problem 5 (the valve, four states). Opening cash 80, minimum-cash floor 60, opening revolver 40 (rate irrelevant here). For each independent case, compute the draw, the repayment, the closing revolver, and closing cash. Net flows before the revolver (CFO + CFI + CFF-ex-revolver): (a) +25; (b) −45; (c) −20; (d) +100.

Solution. Cash before revolver = 80 + flow, and surplus = that − 60. (a) Pre = 105, surplus +45. Draw 0, repay MIN(40, 45) = 40. That leaves revolver 0 and cash 105 − 40 = 65. (b) Pre = 35, shortfall 25. Draw 25. Revolver 65 and cash 60, pinned to the floor. (c) Pre = 60, surplus 0. Draw 0 and repay MIN(40, 0) = 0. Revolver 40, cash 60. (d) Pre = 180, surplus 120. Repayment is capped at the balance, MIN(40, 120) = 40. Revolver 0, cash 140. States (a) and (d) differ only in what the MIN bites on. In (a) it's spare cash, and in (d) it's the outstanding balance. That cap is why the revolver can never go negative.

Problem 6 (CFS signs, cold). NI 100, depreciation 40. Balance-sheet deltas: AR +30, inventory −10, AP +15, other current liabilities −5. Compute CFO, and state the two sign rules you used without looking them up.

Solution. CFO = 100 + 40 − 30 + 10 + 15 − 5 = 130. The rules: an increase in an operating asset consumes cash, so you subtract it. An increase in an operating liability releases cash, so you add it. You can derive both in one breath from "the balance grew, so cash must have gone into it / come out of it".

Problem 7 (the vintage waterfall). A company's existing net block of 200 depreciates straight-line over a remaining 4 years. Capex: FY1 100, FY2 120, FY3 140; new assets have 5-year lives with a half-year convention in the year of spend. Build the depreciation triangle and total depreciation for FY1–FY3.

Solution. Layer rows (annual charge = capex/5 = 20, 24, 28; half in year of spend):

The triangle's shape is the lesson. Every capex vintage drags a growing depreciation tail behind it. So a capex boom shows up in margins with a lag, and the flat-rate method you used for Kaveri understates depreciation while capex accelerates.

Problem 8 (the toggle). (a) Scenario rows hold FY27 revenue growth of 10% (row 12), 12% (row 13), −2% (row 14); the switch cell $C$6 reads 3. What does =INDEX(G12:G14,$C$6) return? (b) Rewrite the selector with CHOOSE. (c) Give one concrete reason to prefer INDEX over CHOOSE, and one to avoid OFFSET entirely. (d) What guards the model against someone typing 4 into the switch, and what happens if nobody built the guard?

Solution. (a) The third stacked cell: −2%. (b) =CHOOSE($C$6,G12,G13,G14). (c) INDEX takes a range, so adding a fourth scenario row inside it extends everything. CHOOSE's hand-listed arguments must be re-edited in every formula. OFFSET is volatile. It recalculates on every worksheet change, dragging large models to a crawl, and its moving-target references defeat precedent tracing. (d) Data validation (list: 1, 2, 3) on the switch. Without it, INDEX returns #REF! and every dependent cell errors loudly, which is the acceptable outcome. The disaster would be a formula that defaults without a sound instead of failing.

Problem 9 (circularity by algebra and by iteration). A revolver opens the year at 100. Before interest, the company has 20 available for repayment. Interest at 12%, charged on the average balance, reduces what it can repay rupee for rupee, so closing = 100 − (20 − interest) = 80 + interest. (a) Iterate from the opening-balance answer until closing changes by less than 0.01. (b) Solve for the exact fixed point with algebra. (c) What does the gap between pass 0 and the converged answer tell you?

Solution. (a) Pass 0: interest = 12% × 100 = 12 → closing 92. Pass 1: interest = 0.12 × (100+92)/2 = 11.52 → closing 91.52. Pass 2: 0.12 × 95.76 = 11.491 → closing 91.491. Pass 3: 0.12 × 95.746 = 11.489 → closing 91.49 (Δ < 0.01, stop). (b) c = 80 + 0.06(100 + c) → c = 86/0.94 = 91.489. (c) The loop is a damped geometric series, since each pass multiplies the correction by ~0.06. Iteration is just algebra done patiently. The gap from 92 to 91.49, half a percent of the balance, is the size of the prize the entire iterative apparatus buys. Know it, then budget your care accordingly.

Problem 10 (debug from symptoms). A Kaveri-clone's panel shows: BS check 0.0 in all historical columns, +63.0 in every forecast column, constant, not growing; CFS-to-cash 0.0 everywhere; all sources-equal-uses ties 0.0; revolver asleep in base. Diagnose the exact cell before reading on. (Kaveri facts you may use: FY25 RE 407, FY24 RE 344, FY25 NI 90, FY25 dividends 27.)

Solution. A constant-forever imbalance points to a one-time break at the A/E boundary in the fault-trace table. It isn't a per-year leak. As for direction, assets exceed L&E by 63, so a claim is understated. And 63 = 407 − 344 = FY25's retained earnings increment (NI 90 − dividends 27). The retained-earnings opening for FY26 is pointing one column left: F102 reads =D40 (FY24) instead of =E40 (FY25). Every later opening chains off closings, so the error enters once and rides forever, size-locked at 63. Fix F102 and the panel clears. Fill-right is unaffected, because only F was wrong, the kind of single-cell deviation Rule 4 exists to prevent.

Problem 11 (timed, 15 minutes: blank-sheet mini-model). Opening balance sheet: cash 40, AR 50, net PP&E 200 | AP 30, debt 100, share capital 20, retained earnings 140. One forecast year, no revolver, and assert cash stays positive. Revenue 600; total cash operating costs 80% of revenue; depreciation 10% of opening PP&E; capex 30; DSO 36.5; closing AP equal to one month (1/12) of operating costs; interest 8% on opening debt; tax 25%; dividend payout 40%; debt amortization 20. Produce the closing balance sheet and prove it balances.

Solution. P&L: EBITDA = 600 − 480 = 120. Depreciation of 20 leaves EBIT 100, and interest of 8 leaves PBT 92. Tax is 23, so NI is 69, and the dividend is 27.6. Balances: AR = 600 × 36.5/365 = 60 (Δ +10). AP = 480/12 = 40 (Δ +10). PP&E = 200 + 30 − 20 = 210, debt = 80 and RE = 140 + 69 − 27.6 = 181.4. Cash: CFO = 69 + 20 − 10 + 10 = 89. CFI = −30. CFF = −20 − 27.6 = −47.6. The net +11.4 takes cash to 51.4, positive, as asserted. Balance: assets = 51.4 + 60 + 210 = 321.4. L&E = 40 + 80 + 20 + 181.4 = 321.4. Check 0.0, closed by construction with no plug.

Problem 12 (timed, 10 minutes: read the machine). Using only the scenario table from reading the three futures, answer: (a) Bear FY26 net income is positive (34.5), so why does the revolver draw? (b) Why does bear CFO leap from 11.5 to 84.4 in FY27 even though revenue falls again? (c) What board-level decision has the bear case made without saying so, and what would you flag in a memo? (d) In bull, say what pushes the revolver into use by FY29, and which buyback test decides whether that's good capital allocation.

Solution. (a) Profit isn't cash. Working capital (AR +28.7, inventory +30.9, AP −11.8) consumes ~₹71 crore against ₹34.5 of NI, and capex, amortization, and dividends still bleed. Cash-before-revolver lands ₹31.3 below the floor. (b) The levels of DSO/DIO/DPO stay stressed but stop changing, and working capital is a change-driven cash flow. Once days plateau the drain stops and profit flows through again. (c) It keeps paying a ₹10.3 crore dividend while borrowing to hold minimum cash, and a real board would debate a cut. The memo flags dividend sustainability and revolver headroom (draw vs sanctioned limit). (d) Buybacks of 75/yr plus 6%-of-revenue capex outrun even bull-case free cash once the floor binds, so the model is borrowing at 10% to retire shares. The capital-allocation test says a buyback is value-accretive only if the shares trade below intrinsic value. So the memo must compare the assumed ₹600 buyback price against the valuation you'll build next.

Problem 13 (guided: the driver panel from history). A synthetic company reports two years (₹ cr): revenue 800.0 then 880.0; FY2 COGS 528.0; FY2 year-end receivables 108.5, inventory 90.4, payables 65.1; term loan 150.0 at the FY1 close; and for FY2, interest expense 13.5, profit before tax 110.0, tax 27.5, net income 82.5, dividends 24.75. Using a 365-day year, compute the FY2 driver panel: revenue growth, gross margin, DSO, DIO, DPO, effective tax rate, effective interest rate on opening term debt, and dividend payout. Which two of these could you not compute for FY1, and why?

Solution. Growth = 880.0/800.0 − 1 = 10.0%. Gross margin = (880.0 − 528.0)/880.0 = 40.0%. DSO = 108.5/880.0 × 365 = 45.0 days. DIO = 90.4/528.0 × 365 = 62.5 days. DPO = 65.1/528.0 × 365 = 45.0 days. Effective tax = 27.5/110.0 = 25.0%. Effective interest on opening term debt = 13.5/150.0 = 9.0%. Payout = 24.75/82.5 = 30.0%. You couldn't compute FY1's revenue growth or its interest-on-opening-debt rate, because both need a prior year the model doesn't carry. Any ratio built on an opening balance or a flow loses its first year. A spread that shows a number in those cells is inventing one.

Problem 14 (guided: reconstruct the CFS and prove it ties). A synthetic company's balance sheets (₹ cr). FY1: cash 50, receivables 90, inventory 80, net PP&E 300; payables 60, term loan 120, share capital 30, retained earnings 310. FY2: cash 63, receivables 99, inventory 88, net PP&E 315; payables 66, term loan 100, share capital 30, retained earnings 369. FY2 income/flow facts: net income 84, depreciation 30, capex 45, dividends 25, term repayment 20. Reconstruct the FY2 cash flow statement (indirect method) and show its closing cash equals the reported FY2 cash.

Solution. CFO = 84 + 30 − (99 − 90) − (88 − 80) + (66 − 60) = 84 + 30 − 9 − 8 + 6 = 103. CFI = −capex = −45. CFF = −20 (term) − 25 (dividend) = −45. Net change = 103 − 45 − 45 = +13, and opening cash 50 + 13 = 63, the reported FY2 cash to the rupee. The rest of the balance sheet re-derives too. PP&E is 300 + 45 − 30 = 315, retained earnings 310 + 84 − 25 = 369, and the term loan 120 − 20 = 100. Every FY2 balance falls out of FY1 plus the flows. That's the reconstruction test a model's history must pass before it forecasts.

Problem 15 (the treasury-stock equity roll, US $ m). A US company opens the year with common stock & APIC $500, treasury stock −$200, retained earnings $1,200, and 100.0 million shares outstanding. This year: net income $240, dividend payout 20%, and a $150 buyback executed at $60.00/share under the treasury-stock method. Compute dividends, shares repurchased, closing shares, EPS, closing treasury stock, closing retained earnings, and total closing equity. By how much did total equity change, and would that change differ under the Indian retirement method?

Solution. Dividends = 20% × 240 = $48. Shares repurchased = 150/60.00 = 2.5 m, so closing shares = 100.0 − 2.5 = 97.5 m and EPS = 240/97.5 = $2.46. Under the treasury-stock method the buyback never touches retained earnings, so closing RE = 1,200 + 240 − 48 = $1,392, and the contra deepens to −200 − 150 = −$350. Total closing equity = 500 − 350 + 1,392 = $1,542. Total equity moved by net income − dividends − buyback = 240 − 48 − 150 = +$42, from opening equity of 500 − 200 + 1,200 = 1,500 to 1,542. That +$42 doesn't depend on the method. The Indian retirement method routes the buyback through reserves instead of a treasury contra, but the total change in equity is identical. Only its geography on the statement differs.

Problem 16 (a one-year mini-model with a sweep). Opening balance sheet (₹ cr): cash 70, receivables 60, inventory 50, net PP&E 250; payables 40, revolver 40, term loan 100, share capital 25, retained earnings 225. One forecast year. Revenue 800; total cash operating costs 80% of revenue; depreciation 10% of opening PP&E; interest 9% on opening term debt and 10% on the opening revolver (ignore interest on cash). Then DSO 36.5, DIO 30, DPO 40 (365-day year); capex 6% of revenue; tax 25%; dividend payout 30%; term repayment 15; minimum cash 45. Produce the closing balance sheet, show the revolver behaviour, and prove the balance.

Solution. (2 dp, with full precision retained, so a hand-sum of rounded parts may land ±0.01 from the cell.) P&L: EBITDA = 800 − 640 = 160. Depreciation = 10% × 250 = 25 → EBIT 135. Interest = 9% × 100 + 10% × 40 = 9 + 4 = 13 → PBT 122. Tax is 30.5, so net income is 91.5 and the dividend 27.45. Balances: AR = 800 × 36.5/365 = 80.00 (Δ +20.00). Inventory = 640 × 30/365 = 52.60 (Δ +2.60). AP = 640 × 40/365 = 70.14 (Δ +30.14). Net PP&E = 250 + 48 − 25 = 273.00. Retained earnings = 225 + 91.5 − 27.45 = 289.05. Term loan 85.00. Cash: CFO = 91.5 + 25 − 20.00 − 2.60 + 30.14 = 124.03. CFI = −48. CFF before the revolver = −15 − 27.45 = −42.45. Cash before the revolver = 70 + 124.03 − 48 − 42.45 = 103.58. Valve: the surplus over the 45 floor is 58.58, and repayment = −MIN(40, 58.58) = −40, so the revolver goes to 0.00 and cash = 103.58 − 40 = 63.58. The surplus exceeded the balance owed, so cash lifts above the floor. Balance: assets = 63.58 + 80.00 + 52.60 + 273.00 = 469.18. L&E = 70.14 + 0 + 85.00 + 25.00 + 289.05 = 469.19, a 0.01 display rounding gap. Full-precision cells read 469.19 on both sides, and the check is 0.00.

Problem 17 (debug: the 2× fingerprint). A Kaveri-clone's check panel is clean through the historical columns, and then the balance-sheet check reads +44.0 on the nose in FY26. You're told the model's FY26 receivables rose by ₹22.0 cr. Name the most likely error and the cell family, explain why the imbalance is twice the receivables change, and say whether it would stay constant across later forecast years.

Solution. +44.0 = 2 × 22.0, the "exactly 2× a line item" fingerprint from the fault-trace table, which is a sign flip. The CFS's "increase in receivables" line has added the ΔAR of 22.0 (as +22.0) where it must subtract it (−22.0). The swing between the right and wrong treatment is 2 × 22.0 = 44.0, so cash is overstated by 44.0 and assets exceed liabilities & equity by 44.0. It would not stay constant. ΔAR changes every year with revenue, so the imbalance tracks 2 × each year's receivables change. That's how you tell a sign flip, which varies with the flow, from a boundary error, which stays constant forever, as in problem 10. Fix: restore the minus on row 48, =-(F25-E25).

Problem 18 (timed, 20 minutes: a two-year model, draw then sweep). Opening balance sheet (₹ cr): cash 40, receivables 50, inventory 45, net PP&E 200; payables 35, revolver 0, term loan 80, share capital 20, retained earnings 200. Minimum cash 30, 365-day year, no dividends. Year 1 (an expansion year): revenue 500; total cash operating costs 82% of revenue; depreciation 12% of opening PP&E; interest 10% on opening term debt and 12% on the opening revolver (ignore interest on cash); DSO 40, DIO 50, DPO 40; capex a heavy 18% of revenue; term repayment 10; tax 25%. Year 2 (normalising): revenue 560; costs 80% of revenue; same depreciation rate, interest rates and days; capex back to 6% of revenue; term repayment 10; tax 25%. Build both years and prove each balance sheet balances. What does the revolver do across the two years, and why?

Solution. (2 dp. The check reads 0.00 at full precision, and hand-sums of rounded parts can wobble ±0.01.) Year 1: EBITDA = 500 − 410 = 90. Depreciation = 12% × 200 = 24 → EBIT 66. Interest = 10% × 80 + 0 = 8 → PBT 58. Tax is 14.5, so net income is 43.5. AR = 500 × 40/365 = 54.79. Inventory = 410 × 50/365 = 56.16. AP = 410 × 40/365 = 44.93. Capex = 90 → net PP&E = 200 + 90 − 24 = 266.00. Retained earnings = 200 + 43.5 = 243.50. Term loan 70.00. CFO = 43.5 + 24 − (54.79 − 50) − (56.16 − 45) + (44.93 − 35) = 61.47. CFI is −90 and CFF before the revolver is −10. Cash before the revolver = 40 + 61.47 − 90 − 10 = 1.47, which is ₹28.53 below the ₹30 floor, so the revolver must draw 28.53 and cash is pinned to 30.00. Balance: assets = 30.00 + 54.79 + 56.16 + 266.00 = 406.95. L&E = 44.93 + 28.53 + 70.00 + 20.00 + 243.50 = 406.96, with 0.01 of rounding, and the full-precision check reads 0.00. Year 2 opens at the year-1 closing, so the revolver starts at 28.53. EBITDA = 560 − 448 = 112. Depreciation = 12% × 266 = 31.92 → EBIT 80.08. Interest = 10% × 70 + 12% × 28.53 = 7.00 + 3.42 = 10.42 → PBT 69.66. Tax is 17.41, so net income is 52.24. AR = 560 × 40/365 = 61.37. Inventory = 448 × 50/365 = 61.37. AP = 448 × 40/365 = 49.10. Capex 33.6 → net PP&E = 266 + 33.6 − 31.92 = 267.68. Retained earnings = 243.5 + 52.24 = 295.74. Term loan 60.00. CFO = 52.24 + 31.92 − (61.37 − 54.79) − (61.37 − 56.16) + (49.10 − 44.93) = 76.55. CFI is −33.6 and CFF before the revolver is −10. Cash before the revolver = 30 + 76.55 − 33.6 − 10 = 62.95. Its surplus over the floor is 32.95, more than the 28.53 owed, so the sweep repays the full 28.53. The revolver goes to 0.00 and cash lifts to 34.42. Balance: assets = 34.42 + 61.37 + 61.37 + 267.68 = 424.84. L&E = 49.10 + 0 + 60.00 + 20.00 + 295.74 = 424.84, and the check reads 0.00. So the revolver appears in year 1, because an 18%-of-sales expansion out-runs operating cash, worked example 5's story again. It's fully retired in year 2, once capex normalises and no fresh working-capital shock hits. Draw, then release, both proven by a zero check. Year 2's revolver interest, 3.42, runs on year 1's closing revolver through the daisy chain.

Applied mini-project: a real company through the full chassis

The task takes about 4 hours. Rebuild this week's architecture for one real company, from its actual filings. In India, choose from Dabur, Marico, Havells, Pidilite, or Trent. In the US, choose from Costco, Starbucks, Nike, Lowe's, or Colgate-Palmolive. Every one of them is a non-financial, revolver-plausible, readable filer, chosen on purpose. If you built one of these in your forecasting mini-project, choose the same company. Your forecast thinking is already done, and you'll feel the difference between forecasting on paper and forecasting in a machine. Every company is a teaching specimen and none is a recommendation.

For Indian data, take three years of consolidated statements from the annual reports, via the company IR page or the BSE/NSE company filings page. A free screener.in account gives you an export-to-Excel head start. Reconcile at least the latest year back to the actual annual report, line by line (working agreement #7). In the US, take the last 10-K from SEC EDGAR, plus the prior one for three balance-sheet years. Use stockanalysis.com or the TIKR free tier as a cross-check, never as the source of record.

Requirements.

1. Three tabs minimum (A, M, X) on one shared timeline grid: 3 historical years typed as facts, 5 forecast years computed. Full color convention.
2. The complete driver panel computed from history (growth, margins, DSO/DIO/DPO, capex %, depreciation rate, effective tax and interest rates, payout), with a two-line written justification for each forecast driver you set. That's the story behind each number.
3. All five schedules: working capital (days-based), PP&E corkscrew, term debt, revolver with minimum cash and sweep, equity with RE roll, dividends, buybacks (use the treasury-stock variant if your US pick demands it, as Costco/Lowe's/Colgate all do), and a share-count roll.
4. Three statements assembled from the schedules, as in the Kaveri build, with CFS formulas reconstructing the two historical years within rounding. Real filings will leave small unexplained residuals. Document anything you can't tie within ~1 to 2% of the cash movement, and say why: acquisitions, FX translation, reclassifications.
5. Base/bull/bear scenario blocks with an INDEX toggle; every scenario passes every check.
6. The full X-tab check panel, master status cell, and the circularity-breaker switch, wired as in the Kaveri build. Demonstrate that switch 1 with iteration on converges and switch 0 restores the acyclic model.
7. A one-page README: units, sources with dates, conventions, simplifications (what you held flat and why), and the three scenario stories in three sentences each.
8. The stress drill. Set the minimum cash to 3× your base level and raise DSO 15 days in bear, and the model must still pass all checks. Plugs and hardcodes get flushed out right here.

Scoring rubric (0 = absent/wrong · 1 = present with gaps · 2 = meets the bar; pass ≥ 16/20, per the program's written-deliverable protocol):

The gate here, from the master map, is the model audit. Your Kaveri build must reproduce every checkpoint table above, and your real-company model must score ≥16/20 on the rubric with every check passing. Log the score and your single worst miss in the journal.

Reading & resources

• The row maps and checkpoint tables above: your primary reference; the Kaveri model is fully specified here and needs no external template. Resist downloading one until yours works.
• Zerodha Varsity, "Financial Modelling" module (varsity.zerodha.com) [Free] [Beginner→Intermediate]. A full 3-statement build on an Indian company, Indian-filing anatomy throughout; the best India-native companion to the Kaveri build. Read after it, not instead of it.
• Macabacus, financial modeling tutorials (macabacus.com) [Free] [Intermediate]. Institutional-grade conventions: color coding, error checks, circularity and switches, Excel efficiency. Their "avoiding circular references" and formatting guides mirror the workbook rules and the circularity section here, and go deeper on Excel mechanics.
• The FAST Standard (fast-standard.org) [Free] [Intermediate]. A published modeling standard (Flexible, Appropriate, Structured, Transparent) famous for banning deliberate circularity and enforcing one-formula-per-row. Read the short "rules" section and notice where the house style here agrees (rules 1–7) and consciously differs (the breaker switch). Forming a view on that disagreement is an excellent exercise.
• ICAEW, Financial Modelling Code (icaew.com, free PDF) [Free] [Intermediate]. The accountancy institute's counterpart sets out principles for reviewable, auditable models.
• Simon Benninga, Financial Modeling (4th ed., MIT Press) [Paid] [Intermediate→Advanced]. The academic standard; Part II's financial-statement-modeling chapters formalize pro-forma modeling, plugs vs debt-driven closure, and circularity with full worked spreadsheets.
• Wall Street Prep and Breaking Into Wall Street (BIWS) [Paid] [Intermediate]. The two dominant practitioner courses; either's "3-statement modeling" program is a strong drilled-repetition supplement, and their free YouTube quicklessons [Free] cover revolver logic and circularity switches if the paid course is out of budget.
• Raymond Panko's spreadsheet-error research (panko.shidler.hawaii.edu) [Free] [Advanced, optional]. The academic evidence base for the workbook rules: why error rates in unaudited spreadsheets are so high, and why process (not intelligence) is the fix.
• Primary filings for the mini-project: SEC EDGAR (sec.gov/edgar, 10-K, full-text search) [Free]; BSE/NSE company filing pages and company IR sites for Indian annual reports [Free]; screener.in [Free with account] for Indian historicals export; stockanalysis.com / TIKR free tier [Free] for US cross-checks.
• Damodaran's spreadsheet library (pages.stern.nyu.edu/~adamodar → spreadsheets) [Free] [Intermediate]. Not 3-statement models but valuation engines; audit two or three of them as spreadsheets this week: notice his input discipline and how far a great model can travel on few tabs. M3.09 connects your chassis to his engines.
• Going deeper: Edward Bodmer's modeling tutorials (edbodmer.com) [Free] [Advanced]. An exhaustive, free treatment of the interest circularity, covering iterative solving, the "copper-pipe" hardcoded-values break, and debt sculpting, that goes well past the circularity section here. The right next stop before the project-finance and LBO loops of M3.09, where the circularity stops being a 0.9% footnote and becomes first-order.
• Going deeper: Paul Pignataro, Financial Modeling and Valuation (Wiley, 2nd ed.) [Paid] [Intermediate]. A full build-along on a real US company, end to end, that complements the Kaveri build with dirty-data spreading and the treasury-stock buyback mechanics of worked example 6 on an actual filer.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand — that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Building a scenario-driven three-statement model end to end — spreading history, the driver panel with ACTIVE rows, the working-capital, PP&E, equity and debt corkscrews, the cash flow statement as assembly, the revolver valve with its minimum-cash floor and sweep, the error-check panel and the fault-trace protocol, and the circularity handled behind a switch",
  "use": "Modeling is the module where a tool is most likely to hand you something that runs and is wrong, because a spreadsheet formula that computes is indistinguishable from one that is correct until a check fires. Four uses that hold up. (1) SPREADING, which is the genuinely tedious hour: hand it a filing and ask for the income statement, balance sheet and cash flow statement mapped onto your row template, with the source line item and page cited per figure and an explicit flag on anything reclassified — then you type the numbers, because the typing is what makes you notice the reclassification. (2) FORMULA REVIEW as a second pair of eyes: paste a schedule's formulas and ask what happens when each input goes to zero, goes negative, or exceeds the balance it acts on — that question finds missing MIN and MAX guards, which are exactly the bugs the Quant lens shows a scenario toggle cannot reach. (3) THE FAULT TRACE: give it the check panel's readings — which checks fail, in which column, by how much — and ask it to rank suspects against section 4.12's imbalance-pattern table. It is good at pattern-matching a fingerprint to a family; you still open the two cells. (4) TRANSLATION between the sheet and code, in both directions, so the invariants below can be run over thousands of driver combinations rather than three.",
  "tools": ["Chat assistants — Claude, ChatGPT for formula review under edge inputs, ranking fault-trace suspects against the imbalance-pattern table, and drafting the README that names every simplification", "Document/PDF AI — for spreading a filing onto your row map with the source line and page cited, and for locating the borrowings note, lease note and share-count reconciliation a chassis needs", "Coding assistants — for porting the chassis to a function, turning the X tab into assertions, and running the driver sweep and the fixed-point diagnostics below"],
  "prompt": "Here are the formulas from my revolver and debt schedules, with the row map: <PASTE>. For each formula, tell me what it returns when (a) the input is zero, (b) the input is negative, (c) the flow exceeds the balance the formula acts on, and (d) the year is the last one in the model. Name every case where the formula would produce a value that is arithmetically fine but economically impossible — a negative liability, cash below the stated floor, a repayment larger than the outstanding balance, a tax refund on a loss. Do not rewrite my formulas and do not tell me the model is correct; list the cases and the guard each one would need.",
  "verify": "Two verifications, and the first is non-negotiable. A model is verified by its checks, not by its author's confidence: every figure a tool produces must land inside a workbook whose check panel is live, and the panel must be green in all three scenarios with the circularity switch both ways before anything is believed. Never let a tool tell you a model balances — read row 43 yourself. Second, and this is what the Quant lens is about: a green panel proves the arithmetic articulates on the inputs you fed it, and three scenarios are three points in an eight-dimensional driver space. Before shipping, run the invariants over a few thousand driver combinations rather than three, because the branches your scenarios never execute are precisely where a wrong formula waits. And a tool asked whether your assumptions are reasonable will say yes in fluent paragraphs — checks certify the machine, stories certify the assumptions, and neither substitutes for the other (Guardrail 1-5).",
  "diy": "The gate is unaided: you compute a full forecast column by hand from the prior balance sheet and the driver panel, roll four corkscrews, build the cash flow statement as deltas and links, work the revolver valve through all four of its states, prove the balance sheet balances without a plug, and run the fault-trace protocol on a broken model from its symptoms alone. Nothing in this callout is admissible in that room."
}

Modern Data Analysis

By hand first. You became the machine once in Worked example 1, deriving every FY26 figure from the FY25 balance sheet and the driver panel, to the rupee. You drew the facility by hand in Worked examples 2 and 5, swept it clean in Worked example 6, iterated the circularity in your fingers in Worked example 3, and found a mis-linked dividend in four minutes with the fault-trace protocol. That lesson stays as it is. What follows won't give you a better way to build a model. It points out that the chassis you just built is a program, and that three things software engineering figured out decades ago apply to it directly.

Today's workflow.

• The driver panel is a configuration file. Tab A is already a data structure: named drivers, three candidate values each, one selector. Write it as a dictionary instead of a range and the same chassis takes it as an argument. Then the scenario switch stops being a cell and becomes a function call.
• The X tab is a test suite. Rows 6 to 12 are assertions that raise a red fill instead of an exception. Written as assert, they gain the one property a conditional format can't have: they can be run on inputs nobody looked at. The fault-trace rule that you never delete or override a failing check is the same rule that you never comment out a failing test.
• Calc_v7_FINAL_new is a version-control problem that was solved in 2005. A driver file in plain text has a history: who changed the bear-case DSO, when, and what the memo said that week. The Version Control & Git module covers the tooling. Here, the habit is what counts.

Written that way, the model can do one thing a spreadsheet can't: run over the driver space instead of over three points in it. Your scenario toggle produces fifteen forecast columns. A sweep produces as many as you want, and the invariants either hold across all of them or you've found something.

Tools & sources (IN + US). Use numpy and pandas for the chassis and the spread, plain assert or a test runner for the check panel, hypothesis for the property-based version that searches for counterexamples instead of sampling blindly, scipy.optimize.brentq for the tripwire distances below, and version control for the driver file. India: the consolidated annual report for the three statements and, specifically, the borrowings note for sanctioned versus drawn working-capital limits, since the undrawn headroom is the liquidity story the revolver row is modelling. Then the Ind AS 116 lease note if leases enter the debt schedule, screener.in exports as the fastest route to ten years of spread history, and the NSE and BSE company pages for the share-count reconciliation the equity corkscrew needs. US: EDGAR's XBRL companyfacts endpoint, which returns the statements already tagged (Revenues, NetIncomeLoss, PropertyPlantAndEquipmentNet, LineOfCreditFacilityRemainingBorrowingCapacity among them) so a spread becomes a join instead of a retyping. Then the 10-K's debt footnote for the revolver's size and covenants, and FRED (DGS10, DPRIME) for the rate assumptions the interest rows carry.

# The X tab as assertions, and the driver space as a test. R3, recomputed in-session.
import numpy as np
def valve(cash_before, revolver_open, floor):        # §4.11's two-arm valve, M rows 94–99
    surplus = cash_before - floor
    draw    = max(0.0, -surplus)                     # row 95
    repay   = -min(revolver_open, max(0.0, surplus)) # row 96 — the MAX guard is load-bearing
    return revolver_open + draw + repay, cash_before + draw + repay
rng = np.random.default_rng(0)
for _ in range(200_000):                             # not three scenarios: the reachable state space
    cb, r0, f = rng.uniform(-500, 500), rng.uniform(0, 400), rng.uniform(0, 200)
    R, C = valve(cb, r0, f)
    assert R >= -1e-9,                     "X row 11: revolver went negative"
    assert C >= f - 1e-9,                  "X row 12: cash closed below the floor"
    assert abs((R - r0) - (C - cb)) < 1e-9, "draw and sweep must move cash and the facility together"
print("200,000 states, no violation")     # drop the MAX guard and 60.0% of these states break

Verify. Port the chassis and prove it against the published figures above before you let it say anything new. The whole scenario table is the acceptance test, and either it reproduces or your port is wrong. Base revenue runs 1,331.0 · 1,464.1 · 1,595.9 · 1,723.5 · 1,844.2, net income 104.2 · 118.7 · 132.7 · 146.1 · 158.7 and closing cash 97.7 · 108.7 · 126.2 · 150.8 · 183.1, with the revolver flat at zero. Bull draws 12.3 and 49.3 in FY29 and FY30, and shares fall from 25.000 to 24.875, 24.750 and 24.625 on the ₹75 crore/₹600 buyback. Bear's 31.3 / 27.7 / 0.0 revolver runs with cash pinned at 50.0 twice, then lifting to 56.7 · 89.4 · 107.9. Next come the hand-built columns. Worked example 1's FY26 should match to the paisa (EBITDA 199.65, depreciation 48.62, PBT 138.88, net income 104.16, AR 164.0959, inventory 135.6526, AP 102.8334, PP&E 398.585, CFO 137.1749, cash 97.7219), and so should Worked example 2's bear column (EBITDA 106.722, CFO 11.4874, draw 31.2838, cash 50.00 on the floor). Then the circularity pair: interest 15.8 · 13.5 · 11.3 · 9.0 · 6.8 against 14.6 · 12.4 · 10.1 · 7.9 · 5.6, and cumulative net income 660.4 against 666.3, a difference of 0.894%. Bear FY26's average-balance interest should come to 16.2226, with the revolver at 31.9519. Finally, the two five-minute experiments. Lifting the floor to ₹150 crore draws 52.2781 in base FY26, and restoring bear FY26's days to base lifts CFO from 11.49 to 85.5593. Every balance-sheet and cash-flow check reads zero to machine precision in all three scenarios, both switch positions. One presentation note for when you compare. FY28 base interest is exactly 11.25, which the text above prints as 11.3 and a round-half-even implementation prints as 11.2. It's the same number under two rounding conventions, and a code port should say which it uses before anyone reports a discrepancy.

Quantitative lens

The circularity section measured what the loop is worth, 0.9% of cumulative net income, and then told you to spend your anxiety elsewhere. That's the right conclusion, and it can be made stronger. The loop's behaviour is analytically bounded, and that's worth knowing because it retires a widespread fear and points your attention at a real one. All figures below come from a port of the chassis above, run in-session. They're properties of Kaveri's published drivers, and they describe no real company.

The circularity is a contraction, and its rate has a closed form. Perturb the closing revolver by one rupee. Under the average-balance convention that adds r_rev/2 of interest, which after tax is (1 − t) of it. The dividend takes payout of that and the rest reduces cash, so the next pass draws (r_rev/2)(1 − t)(1 − payout) rupees more. On Kaveri's structural inputs that's 0.05 × 0.75 × 0.70 = 0.02625. Now run the real fixed point on bear FY26 from the Method-1 seed and watch it go 31.283775, 31.934349, 31.951427, 31.951875, 31.951887, converged. The successive corrections are 0.650574, 0.017078, 0.000448, 0.000012, and the ratio of each to the last is 0.02625, to five decimals, at every step. Worked example 3's hand-iterated "pass 3 correction is about 0.0004" is that number. Three passes take a ₹0.65 crore error below a lakh. The circularity section's "fifty iterations is astronomically more than needed" prices out at 0.02625⁵⁰ ≈ 9 × 10⁻⁸⁰ of the starting error.

It also can't diverge, for any company that exists. Divergence needs a loop gain of at least one, so r_rev ≥ 2/[(1 − t)(1 − payout)]. On Kaveri's tax and payout that's a revolver rate of 381%. Strip out the dividend and the tax and the requirement is still 200%. The circularity section flags the leveraged case as the one where the loop really matters. It is worse in magnitude, since more debt turns the 0.9% into several percent, but it's no worse in stability. An LBO at a 12% blended rate with a 100% cash sweep and no dividend has a gain of 0.045, a stronger contraction than Kaveri's is far from. Started from twenty seeds spanning −500 to +5,000, the bear-FY26 iteration lands on 31.951887 every single time, because the valve makes the map piecewise-linear and each branch is a contraction. So the folklore that iterative calculation "blows up" is wrong about this loop. The costs the circularity section lists are the real ones, and look at what they share. An accidental circular reference converging without a sound, and an error state circulating after its cause is fixed, are both properties of the setting being on. Neither comes from the interest loop. That's the argument for the breaker switch, restated as a theorem: you're protecting the workbook from the setting, and the model never needed protecting from the loop.

Three scenarios don't execute the whole model. Count which branches of the chassis the shipped futures reach across all fifteen forecast columns. The revolver's draw arm fires in 2 of 3 scenarios, the sweep arm in 1 of 3, the buyback row in 1 of 3, and two guards never run at all. The tax floor MAX(F19,0) is exercised in 0 of 3, since bear's worst PBT is ₹45.95 crore, comfortably positive. The term-loan guard MIN(F83, A!F57) is exercised in 0 of 3 as well, and it's worse than untested. It's unreachable. Repaying ₹175 crore at ₹25 crore a year takes seven years and the model runs five, so no setting of any operating driver can execute that branch. Both are correct formulas. Nothing shipped here tests either of them, and that's no criticism. It's the general fact about scenario toggles.

A bug in an unexecuted branch is invisible to a perfect check panel. Replace =MIN(F83,A!F57) with a bare =A!F57 and the term loan runs 150 · 125 · 100 · 75 · 50 across FY26 to FY30, identical to the correct version. Every check on the X tab reads 0.00, in all three scenarios, for all five years, with the circularity switch both ways. So the model is wrong and provably passing. Extend the same workbook to ten years, which is what a DCF will want, and the loan goes 25 · 0 · −25 · −50 · −75. That's a liability that has become an asset, and now the panel screams. The checks were never broken. They'd simply never been given an input that could break them. Sampling the operating drivers doesn't help here either, since the branch is unreachable in five years at any driver value. Only two tests find it: a longer horizon, or an explicit assertion that term_loan >= 0. That's the difference between a check and an invariant. A check fires on the columns you built. An invariant is a claim you can run against any column, including ones that don't exist yet.

Now measure how much bigger the space is than the toggle. Sample the operating drivers over plausible ranges (revenue growth −12% to +16%, gross margin 30 to 44%, opex 22 to 32% of revenue, DSO 35 to 80, DIO 45 to 100, DPO 25 to 60, capex 2 to 12% of revenue) and run the base chassis on 4,000 draws. The revolver draws in 84.3% of them, the sweep arm fires in 15.5%, and the tax floor binds in 33.0%. A branch that three scenarios reach zero times is reached by a third of plausible worlds. The valve itself has a three-dimensional state space. The code block above visits 200,000 points of it and the invariants hold at every one, while dropping the one MAX(0, …) guard in row 96 breaks 60.0% of them. Fifteen columns isn't a small sample of that space. It's fifteen points.

And the sweep buys an output the toggle can't produce: the distance to the tripwire. Hold base FY26 and move one driver until the revolver draws. DSO must stretch +13.09 days, or DIO +21.81 days. Gross margin must fall 6.33 points, or capex must rise 3.59 points of revenue. Now comes the finding that reframes the lesson from reading the three futures. Revenue growth has no tripwire at all. Cut FY26 base growth by fifty points and cash before the revolver still closes ₹130.6 crore above the floor, with CFO of ₹155.6 crore. That's higher than the base case's 137.2, because a shrinking business releases receivables and inventory faster than it loses profit. In the first year, a growth shortfall on its own is a source of cash. The scenario reading said recessions kill through working capital instead of through profits. On this chassis that statement has a sign attached, and it's the reason the bear case had to move days, margin and growth together to produce a draw at all.

Now the limits, and they bound every number above. Kaveri is synthetic and its history articulates perfectly. A real filing brings goodwill, deferred tax, leases, minority interest and reclassifications, and each of those adds a corkscrew whose guards need their own invariants. The tripwire distances in particular are properties of this balance sheet and transfer to no other company. They're also one-driver-at-a-time, which makes them the wrong number to read as a safety margin. In a real downturn, margin, days and capex discipline move together, which is what the bear case represents, and the joint distance to the facility is far shorter than any single-axis figure suggests. The 84.3% and 33.0% coverage shares depend entirely on the sampling ranges I chose. Widen the margin range and the tax floor binds more often. So re-run the sweep with your own company's plausible ranges instead of quoting these. The loop-gain formula holds for this interest convention and this sweep. Some structures have a different and possibly much larger gain, such as one where interest capitalizes into the balance it's charged on, or where a debt-sizing test resizes the facility from the coverage ratio it feeds. Those are the project-finance and distressed cases where iteration earns its reputation. Most important of all, and this restates the fault-trace lesson, 200,000 passing assertions prove that the arithmetic articulates across that region of the input space. They say nothing whatever about whether the drivers describe the future. A model wired to absurd assumptions passes every invariant in this addendum. Checks certify the machine. Stories certify the assumptions. The balanced model is where analysis starts.

Do it in code: make the driver panel a data structure and the check panel a set of assertions. Add the invariants the X tab can't express: no negative balance anywhere, cash never below the floor, every flow appearing in one place only. Put the driver file under version control, sweep the driver space before you ship, and report the distance to the tripwire as an output of the model instead of a feeling about it. See G2/CS2.06, G2/CS2.02 and G1/QM1.03.

Where this goes next: galaxy cross-links

• G2 · CS2.06 Software Engineering & AI-Assisted Development: assertions, test suites, guard clauses and the discipline of never disabling a failing test: the X tab's rules with the vocabulary and the tooling they came from.
• G2 · CS2.02 Version Control & Git: the end of Calc_v7_FINAL_new, a driver file with a history, so "who changed the bear-case DSO, and what did the memo say that week" is a question with an answer.
• G2 · DA1.02 Pandas Data Wrangling: spreading a filing into a typed frame with the row map as a schema, so a restatement is a re-run rather than a retyping, and ten years of history join in one operation.
• G1 · QM1.03 Time Series, Monte Carlo & ML: sampling a driver space properly, including why independent draws across correlated drivers understate the tails, which is the honest caveat on every coverage figure above.
• G2 · QD2.04 Reproducible Research & Deployment: a model whose every number can be regenerated from its sources months later, which is what turns a workbook from a persuasive object into an auditable one.

Teach it back & journal

Feynman prompt. Explain to a smart 15-year-old how your spreadsheet knows the balance sheet will balance five years into an imagined future, without anyone typing the answer in. Use only three things. First, the family-budget version of the loop: what you earn either sits somewhere or was spent, so the two lists must match. Second, the revolver as a credit card with two house rules, swiped only when the wallet drops below ₹500, with every spare rupee above ₹500 paying it down before anything else. Third, the idea that the computer is doing your paper arithmetic thousands of times without ever getting bored or sloppy. Then write the one-page explainer around this spine: "A model is a promise machine: facts go in one drawer, guesses in another, and everything else is arithmetic the machine re-does instantly whenever a guess changes. The checks are how the machine confesses the moment I break my own promises." If the 15-year-old asks "but what if you just typed the balancing number in?", and a good one will, your answer to that is the whole week.

Journal prompt. Be straight with yourself and record three things. (1) The first check failure you hit during the Kaveri build: what the fingerprint was (size, pattern, direction), how long it took to trace, and whether you followed the protocol or poked formulas at random before surrendering to it. Add the error class to your personal checklist. Phase 8's deep-dive template has a model-audit slot waiting for this list. (2) Which of the three cell types (fact, choice, consequence) you felt most tempted to blur when tired (typing "roughly right" history? hardcoding a rate "for now"? plugging?), and what that tells you about which corners you'll cut under real deadline pressure. (3) One sentence on what changed in how you read other people's models, now that you know what a clean one costs to build. You'll be auditing a broker model within weeks, and the blue cells will tell you more about the analyst than the price target does.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.