How to Audit an AI-Built Financial Model

Step 1 of 8

Start from the filing, line by line

Open the 10-K or annual report beside the model and tie every historical line, not every subtotal. A tool that misreads a filing tends to misallocate between lines rather than invent a total, so the subtotals still agree and a reviewer who checks only revenue, operating profit and net income passes a history that is wrong underneath.

An invented example shows why it matters. The filing reports SG&A of 210 and R&D of 90; the model shows 230 and 70. Operating profit is 180 either way. Forecast SG&A at 3% growth and R&D at 15%, and the model’s operating costs come out 2.4 lower than the filing’s in the first forecast year, a gap that compounds because the faster-growing line started too small.

Record the source of each historical line in a comment or a sources column: the document, the page and the period. Watch for the usual traps: a restated prior year, where the latest filing’s comparative column replaces the original; a fiscal year that does not end in December; figures in thousands mixed with figures in millions; and a non-GAAP measure such as adjusted EBITDA taken from a results release and labelled as if it were reported.

Pass standard: every historical cell matches the filing to the unit, each with a stated source, and any restatement is noted.

Step 2 of 8

Trace every input to a source or a stated judgement

List every assumption the forecast reads: growth rates, margins, working-capital days, capex, the tax rate, the interest rate, the payout. For each, ask where it came from. Acceptable answers are a historical average the model calculates, a consensus figure with its date, management guidance with the document it appeared in, or a judgement labelled as one. An unsourced input is treated as a judgement until someone owns it.

Check that each input lives in one place. An AI-built model often restates the same assumption on two sheets, and the copy on the second sheet is the one the formulas read. Change the visible input and see whether anything moves; if nothing does, find the cell the formulas actually use.

Pass standard: one cell per assumption, each with a source or a label, and every forecast formula reading that cell.

Step 3 of 8

Hunt the hardcodes

Show formulas (Ctrl+` in Excel) and scan each forecast row. A number where a formula should be is among the commonest defects in a model a tool built, and the most dangerous, because it is right today and wrong after the first change. Look for three kinds: a whole cell typed in, a constant buried inside a formula such as =B12*1.05 where the 5% belongs on the inputs sheet, and a formula that breaks the pattern of its row in one year.

Go To Special, set to constants, selects every typed number on a sheet at once; on a calculation sheet the selection should be empty or explainable. Then read across each row: the formula in the first forecast year should be the formula in the last, with only the column moving.

Pass standard: no typed number on a calculation sheet, no constant inside a forecast formula, and every exception to a row’s pattern explained.

Step 4 of 8

Flex each key assumption and predict the result first

This is the step that separates a model that looks right from one that is right. Change one assumption at a time, say revenue growth down five points, and before looking at the outputs write down what should happen: revenue, EBITDA and cash fall, debt is repaid more slowly, leverage rises and equity value falls. Then look. Every output should move, and in the direction predicted.

An invented example: opening debt of 500, an EBITDA margin of 20%, and 40% of EBITDA left to repay debt. On revenue of 1,000, closing debt is 420. Cut revenue to 800 and it should be 436, with leverage of 2.73 times. If the closing balance was typed, it still reads 420 and leverage 2.63 times; against a 2.70 times covenant the model reports headroom on a breach.

The same test catches sign errors. Raise receivables days and cash must fall. If it rises, the change in working capital is being added rather than subtracted: on net income of 100, depreciation of 30 and receivables up from 150 to 180, operating cash flow is 100, and the reversed sign reports 160.

Pass standard: each key assumption flexed, every output moving, and every movement in the predicted direction and of a plausible size.

Step 5 of 8

Make the checks prove something

A balance check reading zero proves less in a tool-built model than in one built by hand, because tools reach for plugs. Find how cash is set. On the balance sheet it should be the closing figure of the cash flow statement and nothing else. If cash, or any other line, is the balancing item, the check reads zero by construction and proves nothing.

Remove the plug and let the check fail. The size of the failure is a clue: an amount equal to one year’s change in a balance points to a line missing from the cash flow statement, and twice a movement points to a sign error. Then add the checks that matter beyond the balance sheet: closing cash equal across the statements, retained earnings rolling forward by net income less dividends, every debt tranche tying to the balance sheet.

Pass standard: no balancing item anywhere, every check at zero in every year and every case, and each check able to fail.

Step 6 of 8

Debt, interest and the circular reference

Follow each tranche from its opening balance through drawings, mandatory repayment and any sweep to its closing balance, and confirm the closing balance is a formula of those movements. Then follow interest: it should be a rate times a balance from the debt schedule, and interest income a rate times cash. A flat or typed interest line means financing and operations are not integrated at all.

Then decide whether the model is circular, and whether it should be. Interest on average balances depends on the closing balance, which depends on cash after interest. On invented figures (opening debt of 500 at 8% and 120 of cash before interest to repay it, ignoring tax), interest on the opening balance is 40.00 and closing debt 420.00; on the average balance, solved by iteration, interest is 36.67 and closing debt 416.67.

Both are defensible if stated. Average-balance interest needs iterative calculation enabled and a circuit breaker: a switch cell that, when set, replaces the average with the opening balance so an error in the loop can be cleared. Opening-balance interest gives up a small timing difference for a model that calculates without iteration. The audit fails only a model that has neither, or a loop nobody can switch off.

Pass standard: every balance and interest line a formula of the schedule, the convention stated, and any circularity behind a working switch.

Step 7 of 8

Count the shares properly

Check the share count twice, once for the forecast and once for any value per share. In the forecast, the basic count should change with buybacks and issuance: $900 million of buybacks at $50 retires 18 million shares, and a count held flat misstates every per-share figure that follows.

For value per share, the count should be diluted by the treasury stock method. On invented figures: 100 million basic shares at $50; 6 million options with a $30 strike bring in $180 million, which buys back 3.6 million shares, adding 2.4 million net; 4 million options at $60 are out of the money and add nothing; 2 million restricted stock units are added in full. Diluted shares are 104.4 million. On an equity value of $5,500 million, the basic count gives $55.00 a share and the diluted count $52.68, an overstatement of 4.4%.

Pass standard: the forecast count moves with buybacks and issuance, and value per share uses a diluted count with its options and awards listed from the filing’s equity note.

Step 8 of 8

Formatting, units and signs

Formatting is not decoration in a model someone else will read. Inputs in one colour and formulas in another let a reviewer see at a glance which cells a person chose, and in a tool-built model the colours are often applied by appearance rather than by content. Spot-check a dozen input-coloured cells for formulas and a dozen formula-coloured cells for constants.

Then units and signs. Every row should state its unit, and a sheet should not mix thousands with millions. Pick one sign convention for costs and cash outflows and confirm the model keeps it, because a subtraction written for one convention and applied to the other doubles a line instead of removing it. Check the period labels too: a forecast headed by calendar years for a company with a September year end is a timing error waiting to be quoted.

Pass standard: colours that mean what they say, one unit per row and one sign convention across the model, and periods that match the company’s fiscal year.

Write down what you found

An audit is worth what it leaves behind. Keep a short log beside the model: each step, what was checked, what was found, what was fixed and what remains a judgement. It is the record that lets the next reader, or you in a month, trust the model without repeating the work, and it is the answer when someone asks how you know a figure is right.

The fastest way to get quick at this is to audit a model whose answers you already know. Build the schedules of a starter workbook yourself, then run every step above on your own work: tie the history, flex the drivers, break the balance check on purpose and watch where it fails.