Three-statement model

Key takeaways

Three statements wired together so net income flows to retained earnings, working capital changes hit cash, and capex feeds PP&E.
Two non-negotiable checks every period: balance sheet balances (Assets − Liabilities − Equity = 0 exactly) and cash flow ties (CF ending cash = BS cash exactly).
Interest is the classic circular reference: Interest → Net Income → Retained Earnings → Equity → Cash → Debt → Interest. Break it by computing interest on the beginning-period debt balance, not the average.
Build statement-by-statement and verify at each break. Don't build all three then check — a wrong margin assumption after all three are linked means rebuilding everything.
Most common silent bug: working-capital sign errors. An increase in AR is a use of cash (subtract on CF); an increase in AP is a source of cash (add on CF).

What "linked" actually means

Three statements are linked when changing one number propagates correctly to every other place it appears. The plumbing:

Net income (from the income statement) flows to retained earnings on the balance sheet and is the start line of the cash flow statement.
Depreciation & amortisation (a non-cash expense on the IS) is added back on the cash flow statement and accumulates against PP&E on the balance sheet.
Working capital changes (deltas in AR, inventory, AP) are computed from balance sheet movements and feed the operating section of the cash flow statement.
CapEx (cash flow investing) increases PP&E on the balance sheet.
Debt issuance / repayment (cash flow financing) updates the debt balance on the balance sheet.
Ending cash from the cash flow statement equals the cash line on the balance sheet — every period.

The accounting identity that has to hold: Total Assets = Total Liabilities + Total Equity. Every period. To the cent. If it doesn't, you have a circularity bug, a missing linkage, or a sign error.

The interest circularity problem

Interest expense creates a feedback loop:

Interest → Net Income → Retained Earnings → Equity →
Balance Sheet → Cash → Debt → Interest

If you compute interest on the average of beginning and ending debt balances, the ending balance depends on cash flow, which depends on net income, which depends on interest — Excel either warns about the circularity or returns garbage. Two ways out:

Use beginning balance for interest. The standard fix. Interest_t = Beginning Debt_t × Rate. Slight understatement of interest in years when debt is rising, slight overstatement when falling — both are immaterial in practice.
Enable iterative calculation (Excel: File → Options → Formulas → Enable iterative calc, max iterations 100, max change 0.001) and use average. Works, but every analyst opening your model has to enable iterative calc on their machine. Unnecessary friction.

Industry standard is option 1. Use beginning balance.

The drivers, by line

Income statement drivers

Revenue = Prior × (1 + Growth Rate). Growth taper from current run-rate down toward terminal rate over the projection horizon.
COGS = Revenue × COGS Margin (or unit-cost driven for some industries).
OpEx = Revenue × OpEx Ratio for each line (S&M, R&D, G&A). Always Revenue, never Gross Profit — that's how businesses actually scale.
D&A = Prior-year PP&E × Depreciation Rate (preferred — ties to the asset base) or Revenue × D&A Ratio (acceptable simpler version).
Interest = Beginning Debt × Rate (per the circularity break above).
Tax = =MAX(0, EBT × Tax Rate). Companies don't pay tax on losses; the floor matters.

Balance sheet drivers

Cash = Cash Flow Statement ending cash (this is the linkage that makes the BS balance).
AR = Revenue × DSO / 365.
Inventory = COGS × DIO / 365.
AP = COGS × DPO / 365.
PP&E = Prior PP&E + CapEx − D&A. (If this identity doesn't hold each period, you have a bug.)
Debt = Per debt schedule (beginning + issuance − repayment).
Retained Earnings = Prior RE + Net Income − Dividends.

Cash flow statement drivers

Operating: Net income + D&A − ΔWorking Capital + other non-cash.
Investing: −CapEx + asset sales / purchases.
Financing: Debt issuance / (repayment) − Dividends − Share repurchases + Issuances.
Net change in cash = sum of the above.
Ending cash = Beginning + Net change. This must equal balance sheet cash exactly.

The two checks that have to be there

Every three-statement model must carry a dedicated checks section. Two are non-negotiable; a few more are nice to have.

Check	Formula	Should equal
Balance sheet balances	`= Total Assets − Total Liabilities − Total Equity`	0 (exactly)
Cash tie-out	`= CF Ending Cash − BS Cash`	0 (exactly)
RE flow	`= Prior RE + Net Income − Dividends − Ending RE`	0
PP&E roll	`= Prior PP&E + CapEx − D&A − Ending PP&E`	0
Interest tie	`= Computed Interest − IS Interest Expense`	~0

Format the check cells: green if zero, red if non-zero. A model where any check is red is broken — fix it before showing it to anyone.

Working capital sign errors — the most common bug

On the cash flow statement, working capital movements adjust net income to cash flow. The signs are non-intuitive because cash flow is from the company's perspective, not the customer's:

AR up → customers owe you more, you collected less cash than revenue suggests → subtract on CF (use of cash).
AR down → you collected old receivables → add on CF (source of cash).
Inventory up → you bought stock that hasn't sold → subtract on CF (use).
Inventory down → you sold stock without replacing → add on CF (source).
AP up → you owe suppliers more, you held onto cash → add on CF (source).
AP down → you paid suppliers down → subtract on CF (use).

The general rule: working capital changes hit cash with the opposite sign of asset changes and the same sign as liability changes. Get any sign wrong and the balance sheet will refuse to balance.

Industry-specific adjustments

The standard template above fits most industrials, consumer, and tech companies. Several industries need different plumbing:

Industry	What changes
Banks & insurance	No COGS, no working capital. Revenue = Net Interest Income + Fee Income. Use a regulatory capital roll, not a cash sweep.
SaaS	Switch to MRR/ARR build. Track unit economics (LTV, CAC, payback). Separate sales-led vs PLG cohorts. Headcount plan drives most opex.
Real estate	Rent roll drives revenue. NOI replaces EBITDA. Cap rate replaces multiple. High depreciation is a tax shield.
Energy & mining	Capital-intensive with depletion schedules. Reserve life matters more than perpetual growth.
Telecom & utilities	Heavy D&A, regulated returns, debt-heavy capital structures. Use FFO instead of FCF for some valuations.

The most common three-statement errors

Working-capital sign errors (above). The most common silent bug — produces a balance sheet that fails to balance by the size of the working capital movement.
Circular interest — using average or ending debt balance instead of beginning. Excel warns; the model resolves to noise.
Cash plug to make BS balance. If you find the BS doesn't balance and you're tempted to plug cash to fix it — stop. The plug hides a real error elsewhere; cash should come from CF, full stop.
OpEx as % of gross profit, not revenue. Same mistake as in DCF. OpEx scales with revenue.
D&A hardcoded instead of computed from PP&E or revenue. Disconnects the asset base from depreciation.
Tax on negative EBT. Companies don't pay tax on losses. =MAX(0, EBT × Rate). Optionally track NOL carryforwards for loss-makers.
CapEx in CF but PP&E doesn't move. The PP&E roll (Prior + CapEx − D&A) must hold each period. If it doesn't, the model is broken.
Retained earnings doesn't roll. Ending RE = Prior + Net Income − Dividends. Forgetting dividends or not picking up the right Net Income is a balance-sheet-killer.
Building all three then checking. Build IS first and verify margins. Build BS and verify it balances. Build CF and verify ending cash ties. Catching errors at each stage is 10× faster than rebuilding all three.

How Smalt AI builds it

Tell your AI coworker the company and the forecast horizon — "build a three-statement for ABC, 5-year horizon, base/bear/bull." What you get:

Dependency-ordered sheets: Assumptions → Revenue Schedule → COGS → OpEx → Working Capital → CAPEX & Depreciation → Debt Schedule → Tax → Income Statement → Balance Sheet → Cash Flow Statement → Checks. Each downstream sheet pulls only from upstream sources, so the build flows top-down.
Interest computed on beginning balance per debt tranche (no circularity, no iterative calc required).
Working capital wired correctly on both BS (DSO, DIO, DPO × revenue/COGS) and CF (sign-correct deltas).
Cash plug? Never. Cash comes from the cash flow statement, full stop. If the BS doesn't balance, the checks sheet flags it red and we trace the error before delivery.
The five sanity checks built into a dedicated checks tab: BS balance, cash tie-out, RE flow, PP&E roll, interest tie. Green if zero, red if not — visible at a glance.
Industry-aware — the template adjusts for banks (no COGS, NII-driven), SaaS (MRR-driven), real estate (rent roll, NOI), without you having to ask.
Source comments on every hardcoded historical input, with date and reference. Audit-ready by default.