How to check a financial model you did not build

The file arrives with eleven tabs and a request for your sign off by Thursday. You did not build it. The person who did has left, or is busy, or built it in 2023.

The numbers look plausible. That is the problem. Models that are wrong usually look plausible, because the obvious kind of wrong gets caught by whoever built it.

The short answer

There are three different things that can be wrong, and they need three different passes. Doing them in this order saves time, because each one narrows what the next has to look at.

1. Hard errors. Broken references, #NAME?, #DIV/0!, circular references. The spreadsheet already knows about these.
2. Silent structural problems. Formulas that do not match their neighbours, a hardcoded number sitting in a row of formulas, a total whose range includes itself. Nothing flags these. They compute happily and give the wrong answer.
3. Assumptions you disagree with. Not errors at all. You find these by changing an input and watching what moves.

Most people do the first pass, feel reassured, and skip the second. The second is where the expensive mistakes live.

Why the quick approaches go wrong

Reading the formulas. Eleven tabs is thousands of cells. You will read a few dozen, find them fine, and generalise from a sample you did not choose randomly.

Relying on the spreadsheet's error checking. It is good at class one and blind to class two by design. A hardcoded 0.35 where the row above says =B4*C4 is not an error. It is a value. The application has no opinion about it.

Tracing precedents cell by cell. This works and it is how people actually find things. It is also one cell at a time, and you do not know which cell to start from, which is the entire difficulty.

Rebuilding it yourself to compare. Sometimes correct, usually not available. If you had time to rebuild it you would not need to sign off on theirs.

What to check in any tool you consider

1. Does it look for silent problems, or only errors? Ask specifically whether it reports inconsistent formulas and hardcoded values inside otherwise formula-driven ranges. That is class two, and it is the reason to use a tool at all.

2. Can it show you the chain? When it flags a cell, you need to see what feeds it and what depends on it, or you cannot judge whether the flag matters.

3. Can you test an assumption without breaking the file? Changing an input to see what moves is the third pass. You want the original values back afterwards, reliably.

4. Where does the model go? A model is usually one of the most confidential files in the business. If the tool uploads it, that is a conversation with your risk people before it is a tool choice.

The check that belongs to no product

For anything browser based, open developer tools, go to the Network tab, clear it, then load the workbook and run the analysis.

If your file is being processed elsewhere, a request has to carry it and you will see it. A tool doing the work locally shows requests for its own code and nothing containing your model. Thirty seconds, any vendor, including us.

Free ways to do it

Excel's own Formula Auditing tools cover more than people use. Trace Precedents and Trace Dependents draw the chain for a selected cell. Show Formulas switches the whole sheet from values to formulas at once, which makes an inconsistent row visible to the eye in a way that clicking never does. Error Checking will sweep class one for you.

Some Windows editions also ship the Inquire add-in, which will compare two workbooks and produce a relationship diagram. If you have it, it is worth turning on.

None of this costs anything, and for a model of a few hundred rows it is enough. The limits show up on size, and on class two, which you are still finding by looking.

Where FundQuill fits

FundQuill is our model auditor. It opens an Excel workbook (.xlsx) in the browser tab and the file stays on your machine.

The specific things it does for these three passes:

• Class one. It enumerates errors and circular references across the workbook, and will show you the actual cycle for a cell that participates in one, rather than just telling you a cycle exists.
• Class two. It reports structural problems that produce no error: formulas that break the majority shape of the run they sit in, hardcoded values inside a formula-driven range, and totals whose range includes the total cell itself. Where a correction can be worked out, it suggests one, and you decide whether to apply it.
• Chains. Precedents and dependents for any cell, so a flag comes with the context to judge it.
• Class three. You can set an input, read the outputs that moved, and put every assumption back with one reset.

None of this writes to your file. The workbook is read into the browser tab and every change, a what-if or an applied fix, happens to that copy.

Cost. The audit is free. Every check described above, the dependency tracing and the what-if pass are all available without paying. Pro adds the work that goes past checking the model as built: named scenarios and stress tests, sensitivity and tornado tables, Monte Carlo simulation, model templates, and a signed report. The Monte Carlo run is seeded, so a given run reproduces exactly rather than giving slightly different numbers each time you present it. Firm adds shared assumption libraries and an exportable trail of the fixes you applied.

We mention the split explicitly because it is the reverse of what people expect. The audit is the free part.

How it differs from the free options. Against Excel's built in auditing, the difference is class two and scale: it sweeps the whole workbook for silent structural problems rather than relying on you to spot them, and it does not need you to know which cell to start from. Against a hosted model review service, the file stays with you and you can check that yourself, though a service will bring human judgment that no linter has.

If the model is small and you are comfortable with Show Formulas and Trace Precedents, that is a real answer and it is free. This is worth a look when the workbook is large enough that reading it is not realistic, or when it is confidential enough that uploading it is not an option.