How to Build a Three-Statement Model That Actually Balances

Why the build order is the whole technique

The three statements are a closed system. Net income flows from the income statement into retained earnings on the balance sheet and to the top of the cash flow statement. Closing cash from the cash flow statement becomes the balance sheet cash line. Every non-cash item and every balance sheet movement is an adjustment in between. If any one of those linkages is missing or double-counted, the balance sheet will not balance.

The reason build order matters is dependency. You cannot calculate interest expense until you have a debt schedule. You cannot build a debt schedule until you know cash flow before financing. You cannot know that until you have working capital and capital expenditure. So the sequence is forced, and fighting it is what causes the mess.

Step 1 — Historicals and the drivers behind them

Input three years of historical financials as hardcodes on a dedicated tab, then calculate the ratios that will become your forecast drivers: revenue growth, gross margin, operating expense as a percentage of revenue, days sales outstanding, days inventory outstanding, days payable outstanding, capital expenditure as a percentage of revenue, and the effective tax rate.

The point of this step is that your forecast assumptions should be visibly anchored to what the business has actually done. A reviewer asking "why 4% revenue growth?" should be able to see the three-year historical trend sitting directly above your assumption. Forecast drivers that appear from nowhere are the first thing diligence tests.

Do not skip the ratio calculation because you already know what you want to assume. The ratios are how you discover that the business you are modelling has margin behaviour you had not noticed.

Step 2 — Revenue and operating build

Build revenue from drivers rather than a growth percentage where the data allows: volume times price, subscribers times ARPU, stores times revenue per store. Driver-based revenue is dramatically more defensible under scrutiny, because each component can be challenged and tested separately, and because it forces you to hold a view about the business rather than about a number.

Project the cost lines with the fixed and variable split made explicit. A model that applies a flat margin percentage to a growing revenue line is implicitly assuming no operating leverage at all, which is almost never how a real business behaves. Separating fixed from variable is what makes the downside scenario meaningful — in a downturn, fixed costs are exactly what hurts.

Stop at EBIT. Interest cannot be calculated yet, because the debt schedule does not exist.

Step 3 — Working capital schedule

Model working capital from days assumptions, not as a percentage of revenue. Receivables equal DSO divided by 365 times revenue; inventory equals DIO divided by 365 times cost of goods sold; payables equal DPO divided by 365 times cost of goods sold.

The reason this matters beyond accuracy: days assumptions are challengeable and benchmarkable. A reviewer can ask whether 62 days receivable is consistent with the customer base and the sector, and that is a productive conversation. "Working capital is 12% of revenue" cannot be interrogated at all, which is why lenders push back on it.

The change in net working capital feeds the cash flow statement, and the closing balances feed the balance sheet. Both linkages, not one.

Step 4 — Fixed asset roll-forward

Opening property, plant and equipment, plus capital expenditure, less depreciation, equals closing PP&E. Simple, and routinely built wrong by depreciating the closing balance instead of the opening balance, or by forgetting that the depreciation figure has to appear in three places: as an expense on the income statement, as an add-back on the cash flow statement, and as a reduction in the roll-forward.

Where capital expenditure is material, split maintenance from growth. It changes the free cash flow conversation entirely, and it is the split that any credit or private equity reviewer will ask for.

Step 5 — The debt schedule and the cash sweep

This is the step where the model becomes integrated, and where the circularity appears. The sequence within the schedule: start from cash flow available for debt service, apply mandatory amortisation, then apply any optional prepayment via the cash sweep, then draw on the revolver if the cash balance falls below its minimum.

Interest is calculated on the debt balances, but the debt balances depend on how much cash was available after interest. Resolve it one of two ways: enable iterative calculation with a labelled circuit-breaker toggle, or calculate interest on opening balances and accept the small imprecision. For a model that will be handed to other people, the second is usually the better trade.

Only now can you return to the income statement and populate interest expense, then earnings before tax, tax and net income.

• Cash available for debt service, before any repayment decisions.
• Mandatory amortisation first — it is contractual, not optional.
• Cash sweep second, respecting any minimum cash balance the business needs to operate.
• Revolver draw last, as the balancing item when cash would otherwise go below minimum.
• Interest on each tranche at its own rate, feeding back to the income statement.

Step 6 — Close the loop and check

Net income now flows to retained earnings and to the top of the cash flow statement. Add back depreciation and any other non-cash items, apply the change in working capital, subtract capital expenditure in investing, and put debt movements and any equity movements in financing. Closing cash flows to the balance sheet.

If it balances, you are done. If it does not, resist the urge to hunt randomly — there is a standard debugging sequence and it resolves the overwhelming majority of cases in a few minutes.

When it does not balance: the debugging sequence

Work in this order. Each step is cheaper than the one after it, and the first three catch most errors.

• Check the size of the imbalance. If it equals a line item you recognise, you have found it — that item is missing from one statement or double-counted in another.
• Check whether the imbalance is constant across all periods or grows. Constant means a one-off opening balance sheet error; growing means a recurring flow is mislinked.
• Check the signs. An add-back entered as a deduction produces an imbalance of exactly twice the item.
• Confirm every non-cash expense on the income statement has a matching add-back on the cash flow statement.
• Confirm every balance sheet movement other than cash appears somewhere on the cash flow statement.
• Confirm net income hits retained earnings once and only once, and that dividends are deducted there rather than on the income statement.
• Confirm closing cash on the cash flow statement is what the balance sheet cash line references — not a separate calculation of the same thing.