AI can produce analysis that looks correct, is arithmetically perfect, and is still wrong — because it reports what is in the file, not what is true in the business. This is the discipline that catches the difference. The hands-on companion to the C-Suite AI Output Trust Framework.
An AI assistant will hand you a scorecard that is internally consistent, arithmetically flawless, and confidently narrated. None of that tells you whether it is true. The only thing that does is a disciplined audit — and a disciplined audit is a repeatable procedure, not a feeling.
A single, transferable method for validating AI-produced analysis before anyone acts on it. It was built on a real capital decision — a rental-fleet investment analysis sitting on roughly 400,000 transactions — and codified while the work was still on the screen. The parts fit together: six principles set the mindset, three tiers set the depth, a checklist runs the procedure, prompt templates make the output auditable from the first draft, and a lessons log keeps the team from re-learning the same trap twice.
A good audit is not about distrusting the AI. It is about being able to answer your reviewer's only real question — "How do you know this is right?" — with something better than a shrug. The answer should be: click here, follow the formula, here is the source cell, here is the tie-out.
AI assistants produce analysis that looks correct, is arithmetically accurate, and can still be wrong — because the assistant reports what is in the file, not what is true in the business. It cannot know the source export was pulled on the wrong date, excludes a status, or is missing months of history. It uses whatever is in front of it, and it describes it fluently.
That fluency is the hazard. Modern models produce structured, sourced-sounding output regardless of whether the underlying numbers hold. Tone is evidence of writing ability — not analytical accuracy. The layer that used to catch this, the junior analyst who quietly reconciled the numbers before they reached anyone senior, is exactly the layer AI is now replacing. The check has to be rebuilt deliberately, as a discipline, because it no longer happens by default.
The defense is not cleverness or suspicion. It is procedure: trace every number to a source cell, reconcile a sample to the system of record, and never let a narrative harden before the numbers underneath it have been signed off. The rest of this document is that procedure.
Before you audit an analysis, you must audit the data the analysis ran on. Audit formulas assume clean inputs. If the source has dates stored as text, numbers stored as text, inconsistent categorical encoding — MFA in some rows and Manufacturer A in others — or unresolved duplicates, then COUNTIFS, SUMIFS, and MATCH will silently produce the wrong answer. Worse, the audit will appear to "match" the analysis, because both are drawing from the same broken source.
In the fleet analysis, one categorical column carried both three-letter codes and full manufacturer names in the same field — tens of thousands of cells using the long form. A filter on the code would have silently missed roughly half the population. The numbers would have looked precise, defensible, and wrong by about 50% — nearly impossible to catch without explicitly counting unique values in the column.
On the real project, the source was cleaned in a separate pass before any analysis ran: thousands of text dates converted to real dates, time components stripped off datetime fields, thousands of text numbers coerced to real numbers, tens of thousands of full names collapsed back to codes, and a set of deprecated rows removed against a documented rule. Every one of those, left in place, would have corrupted a downstream aggregation. Hygiene is not housekeeping you do afterward — it is the zeroth step, and it gets its own phase in the checklist below.
These six carry the whole method. If you internalize nothing else, internalize these — everything downstream is machinery for applying them.
| # | Principle | What it means in practice |
|---|---|---|
| 01 | Every number must trace to a source cell. | Static values the AI typed in cannot be audited. Insist on COUNTIFS / SUMIFS / INDEX-MATCH formulas that point at the raw source. If you click a cell and see a number instead of a formula, flag it. |
| 02 | AI reports what is in the FILE, not what is TRUE. | The assistant cannot know the source is incomplete, stale, or filtered upstream. Every analysis carries this risk. The only defense is reconciling a sample to the system of record — the ERP, the GL, the source of truth. |
| 03 | Deduplication logic is where bugs hide. | When the source has multiple rows per entity — one order split across a model line and an attachment line — summing and de-duplicating give different answers. Both can be valid. Make the AI state its dedup rule explicitly, every time. |
| 04 | Match tolerances must be explicit. | "Does it match?" is not yes/no. Floating point, rounding, and timing create small gaps. Define ABS(A−B) < tolerance per metric type: counts, dollars, and percentages each get their own band (see §08). |
| 05 | Audit at multiple levels. | A single-level audit hides compensating errors. Go aggregate → segment → unit. If the total matches but the units don't sum to it, you have a bug. If the units match but the total doesn't, you have a dedup or filter issue. |
| 06 | Document filters, assumptions, and known gaps. | Record every variance, every ERP cross-check, every reason for an exclusion, next to the number it affects. A reviewer six months from now needs to know why a number is what it is, not just what it is. |
These are the specific ways AI-produced analysis goes wrong, and the specific move that catches each one. Learn the signatures; most audits are pattern-matching against this table.
| Failure mode | How it happens | How to catch it |
|---|---|---|
| Static values | The AI computes in code and pastes the answer as a hard-coded number. It can't be audited, re-run, or adjusted. | Click output cells. A bare number in the formula bar means rebuild with formulas that reference the source sheet. |
| Silent scope drift | The AI quietly excludes rows — blank type, zero cost, negatives — without saying so. Totals look reasonable but don't reconcile. | Ask for a reconciliation from the grand total: total source rows → rows excluded by each filter → rows used. |
| Wrong column / wrong sheet | The AI reads one column believing it's another, or aggregates a stale sheet instead of the refreshed one. | Spot-check two or three individual rows by hand against what the formula assumes they contain. |
| Dedup ambiguity | Multiple rows per entity. Summing and de-duplicating diverge, and the AI picks one silently. | Ask directly: "How are you handling rows where the same ID appears more than once?" Then document the choice. |
| Date boundary errors | <= vs < at month-end, fiscal vs calendar, timezone shifts on datetime fields. Off-by-one-day errors compound monthly. |
Manually count one month's source rows. Prefer >=DATE(y,m,1) AND <DATE(y,m+1,1). |
| Completeness gaps | The export is missing history, has delayed updates, or excludes statuses. The AI has no way to know — it uses the file. | Reconcile a sample back to the system of record. One or two entities is enough to surface a systemic gap. |
| Confidently wrong narrative | The AI concludes "this segment is overinvested" on numbers that are understated by a data gap. The story inherits and amplifies the error. | Never accept the interpretation until the underlying numbers are audited. The story is only as good as the data beneath it. |
Audit depth should match how expensive it is to be wrong. Three tiers, escalating in effort. The rule of thumb: always do Tier 1; do Tier 2 before anyone acts on the numbers; do Tier 3 when the decision is costly to reverse. Don't scrap a fleet on unaudited data.
On the fleet analysis, the aggregate and segment tiers both matched cleanly — the formulas were correct. It was the unit tier that revealed the source file only captured data for periods when the tracking system was updated, not a continuous history. One unit showed 196 days in the file against 338 in the ERP: a 142-day gap no formula could have caught, because the arithmetic on the available days was exactly right. Utilization percentages were directionally fine; absolute day counts and fleet-size math were understated. That finding only exists because someone went to the atomic level and tied out to the source of truth.
Work top to bottom for each analysis. This is the condensed, working version — tick items as you go (your checks are local to this page and reset on reload). The phases move from data hygiene, through pre-flight and structural checks, into formula validation, cross-validation, and sign-off.
"Match" needs a definition per metric type, or you will either chase rounding noise or wave through real errors. These are the working bands.
| Metric type | Tolerance | Formula pattern |
|---|---|---|
| Row / day counts | 0.5 (integer match) | =IF(ABS(A-B)<0.5,"✓","✗") |
| Dollar amounts | $1 to $10 (rounding) | =IF(ABS(A-B)<10,"✓","✗") |
| Percentages | 0.001 (0.1 pp) | =IF(ABS(A-B)<0.001,"✓","✗") |
| Ratios / multiples | 0.01 | =IF(ABS(A-B)<0.01,"✓","✗") |
| Large dollar aggregates | 0.1% of value | =IF(ABS(A-B)/A<0.001,"✓","✗") |
When a number lands outside tolerance, that's the beginning of the interesting part, not the end. On the fleet analysis a sub-1% gap in sales revenue traced cleanly to how duplicate order rows were aggregated — both methods defensible, the difference documented rather than forced. The variance you investigate and explain is worth more than the ten that matched.
The cleanest audit is one you designed into the prompt. Ask for formulas, source references, documented filters, and explicit dedup handling up front, and you spend far less time unwinding problems later. Customize the [BRACKETED] parts; the copy button lifts each template to your clipboard.
| Anti-pattern | Why it causes audit problems | Better |
|---|---|---|
| "Analyze this and give me insights." | Too open. The AI makes filter, dedup, and metric choices without telling you. | Specify metric, source, filters, and the business rule. |
| "Just give me the numbers." | Invites static values. Impossible to audit or re-run. | "Build with formulas only — no hard-coded numbers." |
| "Does this look right?" | The AI usually says yes; it lacks the business context to know. | "Audit this against source with a ✓/✗ per input and investigate any mismatch." |
| Pasting raw data into the chat. | The AI processes the paste, not the file — no audit trail back to the sheet. | Keep data in the workbook; point the AI at specific ranges. |
The principles and the three tiers cover a single analysis, run once. But AI-assisted analysis in finance is rarely one-shot — it's a session that drifts, data that refreshes, and a prompt history you eventually can't reconstruct. These three failures don't appear on any single audit sheet, because they happen between audits.
Over a long conversation, the AI's picture of the task shifts. Filter rules relax, definitions get quietly redefined, scope creeps. No single step is wrong — each was a reasonable response to the previous message — but by turn 40 the analysis isn't what you asked for on turn 1. Catch it: before accepting a deliverable, ask the AI to restate the business rules in force right now, and compare against the original. Scan one sheet for internal inconsistency — some formulas filtering on a condition others drop is almost always drift.
You run the analysis Monday. Friday someone re-exports the source with a fresh cut. The formulas still work — they just now describe a different reality, while the narrative written Monday stays put. Catch it: freeze a snapshot of key source values at analysis time — row count, date range, a checksum on a key amount column. Version the source sheets with dates. Cite the snapshot date wherever you share conclusions.
A month later, a stakeholder asks why the analysis excludes off-rent days. You know the AI made that call, but you can't find the turn where you confirmed it — the audit sheet captured the how, never the why. Catch it: at the end of each session, ask the AI to summarize the definitional and scope decisions with a reason for each, and paste it into the workbook. Tag each as user-requested, AI-proposed-and-approved, or AI-implicit — that last category is the one to re-examine. Keep the transcript as a permanent artifact.
The log is how a team gets smarter instead of re-discovering the same trap. Every audit that finds something non-trivial adds a row; every new analysis reads the log first. These entries are drawn from the fleet audit — abbreviated here as illustration of the shape a working log takes.
| ID | Category | Finding & mitigation | Status |
|---|---|---|---|
| L-001 | Source completeness | The daily-status export only covers periods when the tracking system was updated — not a continuous history. Several units show far fewer days than their true availability window (one showed 196 in-file vs 338 in the ERP). Understates absolute day counts and fleet-size math; utilization % stays directionally right. Mitigation: cross-check a sample to the ERP; treat absolute day counts as a lower bound; flag the gap on anything shared with decision-makers. | Open |
| L-002 | Deduplication | Order rows carry both model and attachment records. Row-level summing and order-level aggregation of sales revenue diverge by under 1% — both defensible. Mitigation: state the dedup rule on every scorecard; keep the order-level figures authoritative; document the small variance rather than forcing a reconciliation. | Documented |
| L-003 | AI scope / filter | A first aggregation pass returned zero rental models because the AI scanned only the head of a sorted file — all attachments — and concluded "no rental data exists." Mitigation: confirm row counts per filter combination before aggregating; if an expected entity returns zero, investigate rather than accept. | Resolved |
| L-004 | Formula construction | An early audit-formula pass had off-by-one row references (counting the header) and a literal value where a cell reference belonged, producing errors. Mitigation: read audit formulas back immediately; watch for #VALUE!, #REF!, #DIV/0!; spot-check row references against the actual layout. | Resolved |
| L-005 | Mixed encoding | A categorical column mixed three-letter codes with full names in the same field — a code filter would have missed roughly half the population. Mitigation: count unique values before any COUNTIFS on a categorical column; re-verify after new imports. | Resolved |
| L-006 | AI premise acceptance | Asked to replace a code that didn't exist, the AI confirmed its absence and did nothing — plausibly the user meant a similar code and mistyped. Mitigation: when instructing an AI to change data on an asserted fact, have it confirm the target exists first; treat "no matching rows, no action taken" as a yellow flag, not a green one. | Documented |
A short, sanitized trace to show the method in motion. Entity names and figures below are illustrative — the point is the structure of an audit, not the underlying data. The full worked examples live in the source workbook.
The goal of a scorecard audit is to prove a headline number decomposes into inputs that each trace to raw data, with an explicit match check on every one. Here is one model traced across its full 40-month window, January 2023 through April 2026 — every input rebuilt independently from source and reconciled. Note that each row the financial-utilization line depends on is shown above it, so the last line can be followed end to end:
| Input | Value | How it's built from source | Match |
|---|---|---|---|
| Months of data | 40 | distinct months present, Jan 2023 – Apr 2026 | ✓ |
| Total unit-days | 5,974 | COUNTIFS: this model, Type=Model, Status=Rental | ✓ |
| On-rent unit-days | 3,589 | … AND On-Rent flag = 1 | ✓ |
| Physical util % | 60.1% | 3,589 ÷ 5,974 | ✓ |
| Total rental billings | $736,289 | SUMIFS on invoice amount for this model, all 40 months | ✓ |
| Annualized billings | $220,887 | $736,289 ÷ 40 months × 12 | ✓ |
| Avg cap cost / unit | $92,464 | SUMIFS(cap cost) ÷ total unit-days | ✓ |
| Avg fleet size | 4.9 units | 5,974 unit-days ÷ 40 months ÷ 30.44 | ✓ |
| Financial util % | 48.7% | annualized billings ÷ (avg cap cost × avg fleet size) | ✓ |
Financial utilization asks a single question: of the capital tied up in this fleet, how much is earned back in rent each year? Two moves make the two sides comparable. First, annualize the billings — total rent over the window ÷ months × 12 — so a cash flow becomes a yearly rate ($736,289 over 40 months becomes $220,887 a year). Second, size the capital base — average cost per unit × the average number of units on the ground ($92,464 × 4.9 ≈ $453,000). Divide the annual rent by the capital base — $220,887 ÷ roughly $453,000 — and you land at about 48.7%: this fleet earns back a little under half its capital in rent each year. (Plug in the rounded figures shown here and you get 48.8%; the workbook's full-precision inputs give 48.7% — the 0.1-point gap is exactly the rounding tolerance from §08 at work.) It reads cleanly here because the annualization has 40 real months to average — on a single month it would be a twelvefold extrapolation from one data point, which is why a one-month version is so hard to follow.
Every input reconciles to raw source, so the formula is validated — a clean Tier-1/Tier-2 pass. And it is exactly the kind of tidy result that can lull you into stopping too early.
The same model passed the scorecard trace above cleanly; every metric tied out. Only at the unit tier — tying a sample of its individual units back to the ERP — did the real finding appear:
| Unit (anonymized) | Days in file | Days per ERP | Gap |
|---|---|---|---|
| Unit A | 196 | 338 | 142 |
| Unit B | 275 | 485 | 210 |
| Unit C | 432 | 713 | 281 |
The formulas were correct. The arithmetic on the available days was exact. The data was incomplete — the source captured days only when the tracking system was updated, not continuously. No aggregate or segment audit could have found this, because nothing in the file was internally wrong. It took dropping to the atomic level and reconciling to the system of record. That is why Tier 3 exists, and why "the formulas match" is never the same sentence as "the numbers are true."