3-Statement Model Structure for a PE Portfolio Company

What is a 3-statement model?

A 3-statement model links the profit and loss, the balance sheet, and the cash flow statement so a change in any one flows correctly through the other two. Net income lands in retained earnings, working capital movements adjust cash, and the balance sheet balances without a manual plug. If it does not balance on its own, it is a P&L projection with two extra tabs.

For a sponsor-owned company the model is also the object several other things are built from: the board package, the covenant headroom forecast, and the lender's own view of the credit.

The standard tab structure

Order matters, because it is what makes the file navigable by someone who did not build it. Left to right, in the direction the calculation flows:

The discipline that matters more than the exact tab list: inputs go in one place and outputs calculate. The moment an assumption is typed directly into the P&L tab, the model has two sources of truth and nobody can find the second one.

The linkage logic

This is the part that separates a real three-statement model from three tabs that happen to sit in one file. Three connections do the work.

Net income to retained earnings. Net income from the P&L flows to the balance sheet as an addition to retained earnings:

Closing retained earnings
  = Opening retained earnings
  + Net income for the period
  − Dividends and distributions

Net income to cash flow. The indirect cash flow statement starts at net income and reverses everything that was not cash:

Cash from operations
  = Net income
  + Depreciation and amortisation
  + Other non-cash charges
  − Increase in accounts receivable
  − Increase in inventory
  + Increase in accounts payable
  + Increase in accrued liabilities

Cash flow to the balance sheet. The closing cash balance from the cash flow statement is the cash line on the balance sheet:

Closing cash
  = Opening cash
  + Cash from operations
  + Cash from investing
  + Cash from financing

If those three are wired correctly, the balance sheet balances by construction. If it does not balance, one of them is wrong — and that is genuinely useful information rather than an annoyance.

The balance check, and why it must be loud

Put a single cell on the balance sheet tab:

Check = Total assets − (Total liabilities + Total equity)

Format it so any non-zero value is impossible to miss — large, red, at the top of the tab. Then carry it to the cover tab so it is visible on opening the file.

Do not let anyone add a plug. A "balancing adjustment" line makes the model always balance and therefore always silent, which removes the only automatic error detection the structure provides. A model that balances because of a plug is less trustworthy than one that visibly does not balance.

Use a tolerance of about $1 for floating-point rounding, not $1,000. A $900 imbalance is a real error hiding under a threshold.

Worked: how one assumption travels

Assume DSO worsens from 45 days to 60 days on annual revenue of $50M. Watch it move through all three statements.

Step 1 — the receivables balance.

AR at 45 days = $50,000,000 × (45 ÷ 365) = $6,164,384
AR at 60 days = $50,000,000 × (60 ÷ 365) = $8,219,178
Increase = $2,054,794

Step 2 — the P&L. Nothing happens. Revenue is unchanged, net income is unchanged. This is exactly why a P&L-only forecast cannot see the problem.

Step 3 — the cash flow statement. The increase in receivables is a use of cash:

Cash from operations falls by $2,054,794

Step 4 — the balance sheet. Receivables rise by $2,054,794 and cash falls by the same amount. Total assets are unchanged, so the sheet still balances — but the composition has changed from cash into receivables.

Step 5 — the consequence. If the company was holding $1.2M of cash, it now needs a revolver draw of roughly $855,000 to stay solvent. That draw carries interest, which flows back to the P&L, which reduces net income, which reduces retained earnings. The circularity is real and is the reason the debt tab has to be built with care.

A fifteen-day change in a collections assumption — the kind of thing that happens quietly when a large customer changes its payment practice — consumes two million dollars of liquidity and appears nowhere in the profit forecast.

Where these models break

Circular references done badly. Interest depends on the debt balance; the revolver balance depends on the cash shortfall; the shortfall depends on interest. This is genuinely circular. Handle it with a clearly labelled iterative calculation and a circuit breaker switch that zeroes the interest feedback, so the file can be opened and diagnosed when it starts producing #VALUE!.

Hardcoded numbers in formulas. =B14*1.03 is a growth assumption nobody will ever find. Every constant belongs on the inputs tab.

Averages where timing matters. Modelling interest on the average debt balance is fine for an annual view and wrong for a monthly one when the revolver moves inside the month.

Working capital driven by revenue when it is driven by cost. Inventory and payables follow cost of sales, not revenue. Tying them to revenue produces a model that behaves incorrectly whenever margin changes — which is precisely when you need it most.

Depreciation on a single blended rate. Acceptable early. Wrong once there is meaningful capex, because new assets start depreciating on different schedules and the fixed asset roll-forward stops tying.

No version discipline. Three files named Model_v4_FINAL_updated.xlsx is a governance problem, not a naming problem. One file, a change log on the cover tab, dated versions archived.

Scenarios, done properly

Build one model and switch it, rather than maintaining three files. A single cell on the inputs tab selects the case, and CHOOSE or INDEX picks the driver set:

Revenue growth = CHOOSE($C$4, Base_growth, Upside_growth, Downside_growth)

This keeps the cases genuinely comparable — they run through identical logic — and means a structural improvement made once applies to all three. Separate files diverge within a month.

Acquisitions belong in their own layer rather than blended into the base case, so the standalone business and the platform can still be seen separately. A sponsor will ask for exactly that decomposition.

What "handover-ready" means

The model is an asset only if someone other than its author can run it. In practice that means: a documented tab map on the cover; a consistent colour convention (blue for inputs, black for formulas, green for links to other tabs); no hidden tabs or hidden rows containing live calculations; a written monthly update routine; and a stated list of what will break if the business changes shape — a new entity, a new revenue model, a second currency.

The test is simple. Hand the file to your controller with the update instructions and leave the room. If they can produce next month's reforecast without calling you, the model is finished. If they cannot, what has been delivered is a dependency — which is the argument made at more length in what a fractional CFO costs.

Monthly grain, not quarterly

Build the model monthly even when the board only sees quarters.

Quarterly models hide the working capital cycle. A business with a seasonal build — inventory purchased in two months, sold across the following four — nets out inside a quarter and looks flat. The revolver draw needed to fund the build never appears, because the quarter began and ended with the inventory position roughly level.

Monthly grain also makes the model usable for the 13-week cash flow, which needs a monthly view to reconcile against, and for covenant forecasting, where trailing-twelve-month EBITDA has to roll month by month to show where a test tightens.

Aggregating monthly to quarterly is arithmetic. Disaggregating quarterly to monthly is guesswork. Build at the finer grain and sum upward.

A short pre-delivery checklist

Before a model is handed to anyone, these should all be true:

• The balance check reads zero in every period, with no plug line anywhere.
• Every constant lives on the inputs tab; a search for hardcoded numbers in the output tabs returns nothing.
• Turning the circuit breaker on and off changes interest and nothing else.
• Backtested periods reproduce actual results within a stated tolerance.
• The fixed asset and debt roll-forwards tie to the balance sheet.
• Someone other than the author has run one update successfully.

The last item is the only one that tests the others.