Three-Statement Model Builder

Three-Statement Model

An integrated model is two artifacts sharing one workbook: a faithful transcription of what the company reported, and a forecast that is a set of arguments. The seam between them is the most important line in the file, and most of the rules below exist to keep it visible.

Evidence labels, source tiers, staleness, the readiness posture and the intake limits: .agents/skills/research-conventions/SKILL.md, read before the first figure enters the workbook.

• Fiscal period labels, per-company LTM windows, and rate against level changes: .agents/skills/research-conventions/references/market-data-rules.md.
• A filing label does not match a model row, or a subtotal will not tie to its components: .agents/skills/3-statements/references/line-items.md.
• The template needs data pulled out of a 10-K or 10-Q: .agents/skills/3-statements/references/sec-filings.md.
• Writing a linkage, a roll-forward or a schedule formula: .agents/skills/3-statements/references/formulas.md.
• Formatting a sheet the user will read: .agents/skills/3-statements/references/formatting.md.
• Designing a scenario set or a sensitivity grid: the weak-design catalogue under Step 10: Scenarios and Sensitivities in .agents/skills/dcf-model/SKILL.md applies here unchanged.

Critical Principles

Formulas, not hardcoded values. Every projection cell, roll-forward, linkage and subtotal is a live Excel formula, never a value computed in Python and written in. With openpyxl, ws["D15"] = "=D14*(1+Assumptions!$B$5)" is correct and ws["D15"] = 12500.0 is not. The only cells holding typed numbers are historical actuals and the driver assumptions on the control panel. A hardcode does not just freeze one number: it breaks every downstream integrity check silently, because the check still evaluates and still reads OK.

Build with openpyxl, recalc, audit. Build as a saved Python script (for example <task>/build_model.py), following .agents/skills/xlsx/SKILL.md: blue font for inputs, black for formulas, green for cross-sheet links, a provenance comment on every input, an explicit number format on every computed cell. Then python .agents/skills/xlsx/scripts/recalc.py <file> 30 until the status is "success", and python .agents/skills/xlsx/scripts/audit.py <file> --strict with every fail fixed. A saved script is what lets you edit one section and rerun cheaply.

Present each stage as you finish it. A PTC turn is not chat-interactive at every step, so this is not a blocking question: present the block, say what you are building next, and carry on unless the user objects. A mis-mapped tab or a wrong historical surfaces while it is still cheap to fix.

1. The architecture or the template map - which sheets exist, what each holds, where the inputs live
2. Historicals - the transcribed block, with periods and sources
3. Drivers - the control panel, each driver with its basis
4. Income statement projections - with the subtotals checked
5. Balance sheet - the balance check for every period
6. Cash flow statement - the cash tie-out for every period
7. Checks sheet - the whole sheet, with the roll-up cell reading OK
8. Cover - the workbook's first sheet, written last: tiles linking to the terminal-year revenue, EBITDA, net income, free cash flow and leverage, plus the scenario selector and the Checks roll-up

Do not populate the model end to end and present it complete.

Step 1: Architecture, or the template's map

Building fresh, the sheet architecture is fixed by three rules:

• Historicals sit on their own sheet. Reported history is a transcription with its own source notes, and no forecast formula ever writes into it. The boundary period is visibly marked, in the column header and with a border, so a reader can see at a glance where the company stops and we start. This is the seam, and a model that blurs it cannot be reviewed.
• One control panel. Every scenario switch, toggle and global assumption lives on one sheet. Nothing is switched from inside a calculation sheet, because a toggle buried in the middle of the cash flow statement is a toggle nobody finds when the model gives a strange answer.
• Calculation sheets hold formulas only. IS, BS, CF and the supporting schedules read from the historicals sheet and the control panel and hold no typed numbers of their own.

Completing somebody else's template, map it first rather than imposing this shape. Templates vary:

Map, in this order: which tabs exist and which of the above are missing; which tabs feed which; where the input cells are, by font colour and by reading the formulas; the row structure (title, units row, section headers, actual against estimate columns, period labels); the column order and whether it is consistent across tabs; and any defined names, which are usually the drivers and the key outputs and are the fastest way to find both.

Then edit the way .agents/skills/xlsx/SKILL.md says to edit somebody's workbook: load it twice, once for formulas and once with data_only=True, write only into input cells, match the existing units and sign conventions, preserve their formatting, and read their cell comments first because they are often instructions. Where a structural change is genuinely needed, a new line item or period, use python .agents/skills/xlsx/scripts/insert.py rather than openpyxl's own insert, which leaves every formula pointing at the cells that used to be there.

Done when every sheet the model needs is either present or planned, the input cells are identified rather than assumed, and the historical-to-forecast boundary is marked.

Step 2: Stage the historicals, then lay them out

Extracted facts land first as rows, one fact per row, each carrying its own source, period, line-item label, unit and sign:

company | period | canonical line item | value | unit | currency | source | as-of | reported label

Only then pivot into the wide statement layout. Doing it in this order is what keeps provenance attached to the figure through the reshape: a number that arrives straight into a wide grid loses which filing it came from the moment it lands, and the provenance comment then gets written from memory. Keep the staged rows in the build script, or on a Source Data sheet, so the pivot can be rerun.

Normalise at ingestion, not at use:

• Sign: one convention for the whole model, applied as the fact is staged. Whether expenses are positive or negative matters less than that every row agrees.
• Scale: one scale for the model, recorded per source, because a single filing prints thousands in one table and millions in another.
• Currency: one reporting currency, with balance-sheet items at spot and flow items at the period average where translation is needed, and the convention stated on the sheet.

Map every reported label to a canonical line item as it is staged, per .agents/skills/3-statements/references/line-items.md, and keep the reported label in its own column so a reviewer can see what was renamed.

When the history is not there. A driver that cannot be anchored because the reported history does not exist, a segment the company stopped disclosing, a metric it never disclosed, a company with three quarters of public life, is stated as such. Produce the model with that driver labelled assumption and its basis named, re-read the readiness posture from the table in .agents/skills/research-conventions/SKILL.md against that input state, and say in the delivery which driver is unanchored and what would anchor it. A placeholder that looks like a transcribed actual is the one failure this step exists to prevent.

Done when every historical cell traces to a staged row with a source and an as-of, the canonical mapping is recorded, and any unanchored driver is labelled.

Step 3: Set the drivers, each with a basis

A forecast is a set of arguments, and a driver with no stated basis is not an argument. Every driver on the control panel carries a basis, and the basis text is also what goes in the cell's provenance comment.

Basis is one of a closed set: management guidance, consensus, historical trend, bottom-up build, analyst judgement. The mix is the tell: a model where every driver reads historical trend has no view in it, and one where every driver reads analyst judgement has no anchor under it.

Reconcile against guidance and consensus. For the near periods, show the model's forecast beside the company's guidance and beside consensus, with the delta and its reason:

FY26 revenue | our model | guidance range | consensus (N analysts, vintage) | delta to guidance | delta to consensus | why

A forecast that sits outside guidance without saying so is a view the model is hiding from its own reader. A forecast that matches consensus exactly is a view worth stating deliberately rather than arriving at by default.

Working capital is anchored to a stated window. Days-based assumptions (DSO, DIO, DPO) come from a named historical window, and the choice of window is justified: the last four quarters for a stable business, a full cycle for a seasonal one, the post-transition periods for a company that changed its terms. Setting them to the last reported period by default carries whatever was unusual about that quarter through the entire forecast.

Capex and depreciation cohere. The capex path and the depreciation schedule are one decision, not two. A growth capex ramp with flat depreciation is a defect, not a rounding issue: new assets depreciate. Model depreciation off the asset base the capex path builds, split maintenance from growth capex, and check that the implied asset life stays stable across the forecast. In the terminal year, capex and depreciation should be converging unless something specific says otherwise.

Done when every driver has a basis and an as-of, the guidance and consensus reconciliation exists for the near periods, and the working-capital window is named.

Step 4: Build the statements and the schedules

Project the income statement from the drivers, then the balance sheet, then the cash flow statement, then close the loops. .agents/skills/3-statements/references/formulas.md carries the linkage and roll-forward formulas.

The debt schedule is complete or it is not a schedule. Model the maturity ladder tranche by tranche, mandatory amortisation, the revolver with its draw and repay mechanics, and the cash sweep if there is one. Interest expense is computed on the average balance across the period, and it reconciles to that balance. A single "total debt" line with an interest rate applied to it cannot answer the question the schedule exists for, which is whether the company can meet its maturities.

Circularity. Interest expense feeds net income, which feeds cash, which feeds the debt balance, which feeds interest expense. Enable iterative calculation (100 iterations, maximum change 0.001) and put a circuit breaker toggle on the control panel so the loop can be cut when it fails to converge. An unintentional circularity is a different thing: trace it and break it rather than switching iteration on to hide it.

Quality checks per sheet, run as the sheet is built rather than at the end:

• Income statement: historicals match the source; expense lines sum to the reported totals; every subtotal computes; the tax logic handles losses; forecast rows reference the control panel with no hardcodes; period-over-period changes are directionally sensible
• Balance sheet: assets equal liabilities plus equity in every period; cash matches the cash flow statement's ending cash; working-capital accounts tie to their schedule; retained earnings rolls forward; debt ties to the debt schedule; signs are right
• Cash flow: net income at the top of CFO matches the income statement; non-cash add-backs tie to their source schedules; working-capital changes carry the right sign (an increase in an asset is a use of cash); capex ties to the PP&E roll-forward; financing ties to the debt and equity movements on the balance sheet; ending cash matches the balance sheet and beginning cash matches the prior period
• Supporting schedules: opening balances equal prior closing balances; every roll-forward is complete (beginning + additions - deductions = ending); schedule totals tie to the statement line they feed; the assumptions used match the control panel

NOL and deferred tax, where the model carries one: the opening balance is zero for a new entity; the NOL grows only when pre-tax income is negative; utilisation is capped at 80 percent of pre-tax income before the NOL deduction under the post-2017 federal limitation; the balance never goes negative; the deferred tax asset ties to the balance sheet; and tax expense is zero when taxable income is at or below zero.

Done when every check above has been run for every period and each failure has been traced to the cell where it starts.