Balance Sheet Not Balancing: Run These Eight Identities in Order
When a balance sheet will not balance, run eight identities in order: the equation itself, the two reported totals, three fixed-asset tests, the debt roll-forward and the working-capital movement. This workbook answers four of the eight outright, one in part, and cannot answer the other three without schedules it does not keep.
Every balance-sheet break traces to one of eight identities, and checking them in order finds it faster than hunting through formula errors in the sheet.
The order matters because each identity narrows the search. The first one tells you there is a break. The next four tell you which schedule is carrying it. The last two test the two schedules that most often feed it. Working out of order means comparing formulas across nine columns and forty rows without knowing what you are looking for.
The eight below are the standard set for a three-statement model. Not every model can answer all of them. One of the more useful things you learn from running the list is which questions your model has no way to answer.
Identities 1–3: assets, liabilities and equity
Identity 1: the equation itself
Total assets equal total liabilities plus equity, in the column where it does not.
Start at the first forecast column, not the last. A model that balances in FY2030 often does so because a later adjustment absorbed an error that started in FY2026, and checking only the end of the forecast is how that error survives review. If the break appears in one column and not the next, the cause is a movement in that period: a roll-forward, a drawdown, a dividend. If it appears in every column from one point onward, the cause is a balance.
In a workbook that balances, this identity is nine checks rather than one, one per column, each with its own TRUE/FALSE. That is the difference between a model that balances somewhere and a model that balances everywhere.
Identities 2–3: which side moved
Identity 2 tests whether total assets equal the sum of the asset lines. Identity 3 does the same for liabilities and equity.
These are what tell you whether the problem is in a balance or in a subtotal. If identity 1 fails and identity 2 also fails, the asset side is internally inconsistent. A line has been double-counted, dropped, or typed over. If identity 1 fails and identities 2 and 3 both hold, then each side is a consistent sum of wrong parts, and the error is one of classification: a balance sitting on the wrong side of the statement.
Run them in the historical columns first. If the two sides already disagree in a year transcribed from published accounts, no forecast assumption is responsible and nothing downstream can be trusted.
Identities 4–6: depreciation and fixed assets
Identity 4: net PPE equals gross cost less accumulated depreciation
This is the identity that tells you whether the model keeps an asset schedule at all.
Many do not. A model that depreciates a fixed percentage of opening net PPE will roll the net balance forward perfectly and still have no answer to identity 4, because it never separated cost from accumulated depreciation. That is a legitimate simplification, and it should be written down as a limit rather than left for a reader to discover. The three-statement workbook behind this series is exactly that case: depreciation is a single 10.49% applied to opening net PPE, so the cost and accumulated columns do not exist.
Identities 5–6: the charge, and the roll
Identity 5 is closing net PPE equals opening net PPE plus capex less depreciation. Identity 6 tests that the depreciation charge in the cash-flow statement is the same number as the charge in the income statement.
Identity 5 is the one that catches a period shift, which is the most common fixed-asset error: capex landing one column early, or depreciation starting in the month of acquisition rather than the following year. Identity 6 catches the version where both sides are internally right and disagree with each other: a forecast that depreciates at 12% in the P&L and adds back 10% in the cash flow.
What a break here looks like
If identity 1 fails only in forecast columns, and identities 5 and 6 fail with it, the fixed-asset schedule is the cause and the balance sheet is the symptom. That is the reason to run the list in order: by identity 5 you already know which sheet to open.
Identities 7–8: debt and working capital
Identity 7: the debt roll-forward
Closing debt equals opening debt plus drawdowns less repayments, with the repayments reconciling to the cash-flow statement.
In a model with a revolver or a cash sweep, this is where the circularity lives, and the roll-forward cannot be checked independently of the interest calculation. In a model with a fixed amortization (3,400 repaid at 680 a year to nil), the roll-forward is a straight line and there is nothing to get wrong except a typed-over cell. A workbook can reasonably decide not to carry this as its own check in that case, provided the balance sheet identity and the second path would catch it. What a workbook should not do is carry the check and have it pass tautologically, comparing the schedule to itself.
Identity 8: the working-capital movement
The movement in working capital on the cash-flow statement equals the change in receivables, plus the change in inventory, less the change in payables, with signs that match the direction of cash.
This identity is where sign errors live. An increase in receivables consumes cash; an increase in payables provides it. A model that gets the sign right in history and wrong in forecast passes every other check, because the cash-flow statement still articulates to the balance sheet: both figures moved together, in the wrong direction.
Running the eight on a model that would not balance
The model in this series never got as far as this list. The instruction was explicit: the balance sheet has to hold in every year, and if it does not, stop and report which line breaks it. The build reported none. All nine columns balanced, on the first pass, against a 0.01 tolerance.
The result is weaker than it sounds, and worth being precise about. A first pass with no breaks means the statements are joined; it does not mean the balance sheet cannot break under changes: a driver moved far enough can still produce a period in which the identity holds arithmetically while one of the roll-forwards stops being economically sensible, which is what the stress-testing article is about.
| # | Identity | What a break points to | Answered by this workbook |
|---|---|---|---|
| 1 | Assets = liabilities + equity | Where the break appears, not why | Yes, nine checks |
| 2 | Assets = sum of asset lines | A balance or a subtotal on the asset side | Yes, four checks |
| 3 | Liabilities + equity = sum of funding lines | A balance or a subtotal on the funding side | Yes, four checks |
| 4 | Net PPE = cost − accumulated depreciation | Whether the model keeps an asset schedule | No — no cost/accumulated split |
| 5 | Net PPE rolls forward | A period shift in capex or depreciation | Yes, eight checks |
| 6 | P&L depreciation = CF depreciation | Two schedules disagreeing | Partly — implied by the roll-forward |
| 7 | Debt rolls forward | Drawdowns, repayments, or a typed-over cell | No — fixed amortization |
| 8 | Working-capital movement = ΔAR + Δinventory − ΔAP | A sign convention | No — derived, not separately tied |
The three "no" rows are the most useful part of the table. A model that cannot answer identity 4 has no asset schedule; one that cannot answer identity 7 has no debt schedule; one that cannot answer identity 8 has no working-capital schedule, only a single derived number. None of those is necessarily wrong, and this model discloses each one on its limits sheet. But each is a question a reviewer will eventually ask, and the answer is better coming from the model than from the reviewer.
When All Eight Identities Pass and the Balance Still Doesn't
Sometimes all eight identities hold and the balance sheet is still wrong. That combination has three usual causes, and they are worth working through in this order.
A period header moved. Every statement has a row of dates. If one sheet's header was typed rather than linked, an inserted column shifts the numbers without shifting the dates, and every tie still holds one column to the left. Check that the headers on the three statements are the same cells, not the same text.
A hidden row or sheet. A hidden row is not hidden from the formulas. If a schedule was moved out of sight and a total still references it, the model balances and nobody can see why. Unhide everything, then use Trace Dependents from the hidden range (or a formula-audit add-in) to see which formulas still reference it.
A named range pointing at the wrong block. Named ranges are the one Excel feature that can make a model read beautifully and behave wrongly, because a name covers a range and a range can include a row it should not. If a total is defined as a name, click the name and look at the marquee rather than the formula.
If none of that finds it, the answer is usually that the model has two balance sheets, a reported one and a derived one, and they disagree. That is the case the second path in the integration article exists to catch, and it is the one break that cannot be diagnosed from the identity alone.
Keeping the Balance Check Live in the Model
When the identity does break, the check has to be live rather than remembered. That is the difference between a workbook that is checked and one that is re-checked: Mica re-checks the balance-sheet identity on each pass, so a broken tie-out surfaces while the model is being built rather than after. The twelve ties are the same identities grouped by schedule, and the audit trail covers how to leave enough behind for the next person to run this list themselves. For the mechanics of walking a cell back to the cells it depends on, Microsoft's guide to tracing precedents and dependents is the reference; the diagnostic order above is the part it does not tell you.