Making it balance

A well-built three-statement model balances the moment you finish wiring it, and it keeps balancing as you change any assumption you like. You never nudge it into place. If it does not balance, that is information — a specific error in a specific period — and the balance check is the instrument that finds it. This module explains why a correctly-linked model balances at all, how to read the check like a diagnostician, and how to handle the one genuinely hard structural problem in the whole exercise: the circularity between interest, debt and cash. Sokoni's figures remain in KES throughout.

Why a linked model balances

You met the reason in the previous module: every transaction hits the balance sheet in two equal places, so the two sides cannot separate if the bookkeeping is sound. In a model, that soundness reduces to two links. The income statement feeds retained earnings through the roll-forward, so the equity side is correct; the cash-flow statement feeds the cash line, so one asset is correct. Once those two are wired, every other line — receivables, inventory, payables, PP&E, debt — is driven by its own schedule, and there is nothing left over for the sheet to disagree about. Cash is the residual: whatever the cash-flow statement leaves after operations, investment and financing, and because the accounting identity holds, that residual makes the sheet balance automatically. This is the honourable meaning of the word plug — cash quietly absorbs the combined effect of every other assumption. The word has a dishonourable meaning too: typing a number straight into the cash cell, or into retained earnings, to force Assets to equal Liabilities plus Equity when the links did not produce it. That buries a real error under a fake number and makes the model lie to everyone who trusts it. The discipline is absolute — cash is linked from the cash-flow statement and never overwritten, and when the sheet does not balance you find the broken link rather than paper over the gap.

Reading the check like a diagnostic

A non-zero check is not just a red light; it is a message with three parts. Its value often equals the line item at fault — if the check is off by exactly the year's depreciation, or exactly the dividend, that number is your first suspect. Its sign tells you which side is heavy: a positive check (assets exceed claims) means an asset is overstated or a liability or equity understated; a negative check is the reverse. And its pattern across the years tells you the type of error. A one-year spike that returns to zero is a timing error, or a flow captured in one statement but not the other and corrected the next period. A permanent step — zero until Year 3, then the same non-zero figure in every year after — is a broken stock or roll-forward link that entered in Year 3 and is carried forward by the cash or retained-earnings balance. A ramp that grows every year is a flow that recomputes wrongly each period, such as interest charged on a mis-linked debt balance. Read those three signals before you touch a single formula.

• Sign error on a single line — the check is off by twice that line, because you added what you should have subtracted. ΔNWC is the classic: adding the working-capital increase to operating cash flow instead of subtracting it.
• A link pointing at the wrong cell — often one row off, so a balance picks up the prior or next period's figure. The check breaks in the year the link is wrong.
• Dividends in the cash flow but not in retained earnings — equity is overstated, so the check goes negative and, because retained earnings rolls forward, stays broken in every year after.
• Dividends in retained earnings but not (or not on time) in the cash flow — cash is overstated, so the check goes positive; if the payment lands a period late with no dividends-payable liability to bridge it, the break shows in one year and heals the next.
• A hardcoded number overtyped into a formula cell — breaks the year it was typed; the check pins the period, then you hunt the cell.
• Interest or debt timing — interest on the wrong balance, or a repayment in the cash flow that does not match the fall in the debt line, produces a growing ramp.

The build step: confirm it, break it, find it

Start by confirming Sokoni's check reads 0.00 in all five years — it does. Now break it deliberately, the way a real model breaks: in the Year-3 cash-flow statement, add the increase in working capital instead of subtracting it — a sign error on ΔNWC, the single commonest mistake in the whole model. Year-3 operating cash flow is now overstated by twice that year's working-capital change, 2 × 12,928.77 = KES 25,857.53, so Year-3 closing cash is too high by the same amount. Look at the check: Years 1 and 2 still read 0.00, and from Year 3 onward it reads a constant +25,857.53. Two facts fall straight out. The first non-zero year is Year 3, so that is the period whose formulas you inspect — Years 1 and 2 are provably clean. And the break is a constant step, not a growing ramp, which tells you it is a one-off error in Year 3 that the cash balance then carries forward, not a formula that recomputes wrongly every year. You go to the Year-3 operating-cash-flow line, find the working-capital adjustment with the wrong sign, correct it, and watch all five checks snap back to 0.00. That is the entire debugging loop, and the check drove every step of it.

Circularity: interest, debt and cash

There is one place where a three-statement model can tie itself in a genuine knot. Interest expense depends on how much debt is outstanding. If the model repays debt out of surplus cash — a cash sweep — then debt depends on how much cash the business has. Cash depends on the cash-flow statement, which is fed by net income, which is reduced by interest expense. Interest depends on debt depends on cash depends on interest: the formula refers, eventually, back to itself, and a spreadsheet cannot resolve a self-referring formula in the ordinary way — by default it raises a circular-reference error. Our model makes a clean choice that avoids the knot entirely: interest is charged on the opening debt balance, the figure carried in from the end of last year, which is already known before this year's cash is computed. Sokoni's Year-1 interest is 12% of the KES 300,000 it owed at the start of the year — KES 36,000, full stop. Because the input to the interest calculation exists before the loop would close, there is no loop. The model stays fully auditable: every interest figure traces to a balance you can point at, and a reviewer follows it without enabling anything unusual.

The alternative, common in bank and leveraged-finance models, is to charge interest on the average of the opening and closing balances, which is more precise because it reflects debt actually paid down through the year. That precision reintroduces the circularity on purpose, and the way to live with it is to switch on the spreadsheet's iterative calculation, which lets the model loop through its own formulas repeatedly until the numbers stop moving. It works, but it is dangerous, and you must respect the danger. Iterative calculation with no safety valve is fragile in two specific ways. First, a single broken cell anywhere in the loop — one #REF!, one divide-by-zero — no longer stays local: the iteration carries the error around the circle and poisons every cell it touches, so one mistake can turn the whole model into a field of errors. Second, some assumption sets never settle; the numbers oscillate instead of converging, and the model simply returns whatever it happened to hold when the iteration limit was reached. The professional guard is a circuit breaker: a single switch cell that forces the circular interest figure to zero on command. Flip it and the loop opens, the errors clear, and you can find the real problem in a clean model; flip it back and the circularity returns. A model that relies on iterative calculation without that switch is one bad keystroke away from being unrecoverable.