Someone hands you a workbook. The lender wants it by Friday, the person who built it has moved on, and you have an hour. You can’t read every formula in an hour. You can run the checks below, in this order, and stop the moment you have enough to send it back. Each step costs a little more time than the one before it and catches a different kind of mistake.
1. Recalculate it and look for errors
Open the file and press Ctrl+Alt+F9. That forces Excel to recalculate every formula, including the ones it thinks are up to date. Then go to Home, Find & Select, Go To Special, choose Formulas and tick only Errors. Every cell it selects is a formula returning #REF!, #DIV/0!, #N/A or#VALUE!. One error feeds every cell downstream of it, so a single hit on an early sheet can mean the headline number is meaningless.
While you are there, check Formulas, Calculation Options. If the workbook was saved in Manual mode, every number on screen is whatever it was when someone last pressed F9. A model in Manual mode with no note explaining why is a model nobody has recalculated in a while.
2. Find the typed-over numbers
Go To Special again, this time Constants, and run it on the calculation sheets rather than the inputs sheet. Each selected cell holds a number someone typed rather than a formula. Labels and years are fine. A typed number sitting in the middle of a row of formulas is where the builder made the model work at eleven at night, and it will never move again no matter what you change on the inputs sheet.
A well-built model marks its inputs in one color (we use yellow fill; banks tend to use blue text) and leaves formulas black. If the file has no color convention at all, you have learned something about how much care went into the rest of it.
3. Trace the headline number back to an input
Pick the number the deck quotes: closing cash, EBITDA, months of runway. Select the cell and press Ctrl+[ to jump to its precedents, then keep going. Every path should end at either an input cell or a data sheet. If a path ends at a typed number on a calculation sheet, you have found one of step two’s cells, and now you know which headline it distorts.
4. Check that it balances, every period
Two identities have to hold in every column of a three-statement model: assets equal liabilities plus equity, and the cash on the cash flow statement equals the cash on the balance sheet. If the model has a checks row, read it. If it does not, add one:
| 1 | Balance check | =ROUND(Assets-Liabilities-Equity,2) | must be 0 |
| 2 | Cash check | =ROUND(CF_ClosingCash-BS_Cash,2) | must be 0 |
Require zero, rounded to cents. A check that accepts “close to zero” hides a drift that compounds across periods until it is a real number.
5. Break it on purpose
This is the step that separates a check from decoration, and almost nobody does it. Pick a formula cell that feeds the balance sheet (a working capital line is a good choice), overwrite it with a round number, and watch the checks row. It should go red. Undo, and move on.
If the check stays green while the model is broken, the check cannot fail, and every green light on that sheet means nothing. We run this on every workbook we ship, and we call it a negative control: the deliberate corruption that proves the alarm works.
6. Find the tautology
Most checks that will not go red are comparing a number with itself by a longer route. The common case: the cash flow statement derives the change in receivables from the balance sheet (this period’s receivables minus last period’s). Type a wrong number into receivables and the cash flow absorbs it, closing cash moves to match, and the balance check passes. The model is wrong and every check is green.
The fix is an independent recompute. Receivables should be rebuilt from the driver (revenue divided by days in the period, times days sales outstanding) and compared to the balance sheet line as its own check. Do the same for inventory and payables. We found this exact tautology in our own three-statement template: it passed all nine of its checks until the negative control caught it, and it now carries three extra recomputes because of it.
7. Signs and units
Are costs negative or positive, and is that consistent across sheets? Are percentages stored as 0.05 or as 5? Look at the first period, where opening balances were typed in by hand, and at the last, where terminal growth rates and debt maturities live. A gross margin of 4,000% is a units error, and those two columns are where it hides.
8. Push on it
Go to the inputs sheet and double revenue. Cash should move in a plausible direction and by a plausible amount. Nothing that cannot go negative (inventory, share count, headcount) should go negative. Set growth to zero and the model should go flat rather than explode. Two minutes of this finds broken links faster than an afternoon of reading formulas.
9. Circular references
Formulas, Error Checking, Circular References. Interest on an average cash balance is the usual culprit. A model that needs iterative calculation switched on should say so on its README sheet and should have a switch cell that breaks the circle for debugging. If it needs iteration and does not say so, it will show different numbers on different machines.
10. Read the inputs sheet as a stranger
Every input should carry a label, a unit and a source or a date. “Growth 0.04” with no indication of whether that is monthly or annual is the most expensive four characters a model can contain. If you cannot tell what an input means without asking the builder, neither can the lender.
What to do with what you find
Write the findings as cell references, not adjectives: “Forecast!F14 is a typed number inside a formula row” is something the builder can fix in a minute; “the forecast feels off” is not. If steps five or six failed, ask for the checks to be rebuilt rather than patched, because a check that has been wrong once tends to be wrong in other places too.
If you would rather hand the file to someone, this is what a “fix an existing sheet” request on the subscription is for. The workbook comes back recalculated by a formula engine, with its inputs marked, a checks sheet that has been proved able to fail, and a note on what was changed. The same discipline is what we mean by checked work on every build.