/model-audit

---
name: model-audit
description: >-
  Audit an Excel or Google Sheets financial model (budget, forecast, DCF,
  business case, pricing model, commission sheet) for the errors that
  silently break it: hardcoded numbers inside formulas, ranges that stop
  one row short, broken links, sign flips, circular references, mixed
  units and assumptions buried in calculations. Returns a ranked findings
  list with cell addresses and a fix for each. Use this skill whenever the
  user uploads a spreadsheet and asks to "check my model", "audit this",
  "find errors", "is this formula right", "review my forecast", "why
  doesn't this balance", or before a model goes to a bank, investor,
  client or board. Do NOT use to build a new model from scratch, to judge
  whether the assumptions are realistic (that is a business question), or
  for spreadsheets with no formulas.
---

# Model audit

Studies of real-world spreadsheets keep finding the same thing: most large
models contain at least one error, and the people who built them are the
least likely to see it. The reason is simple. A spreadsheet shows results,
not logic. A SUM that stops one row short still returns a believable
number. A growth rate typed into a formula as "*1.05" still looks like a
forecast. Nothing turns red.

Reviewing by looking at the outputs ("does the total feel right?") misses
these errors almost every time. This skill reviews the logic: it reads
formulas, not values, and walks the model the way the numbers flow.

## Workflow

### 1. Map the model first

Before hunting errors, write a short map:

- Sheets and what each one does (inputs, calculations, outputs).
- Where the key outputs live (the cells someone will quote).
- The time axis (columns = months? years?) and the units.
- Any external links, macros, data tables or hidden sheets.

If you can only see values (a pasted table, a PDF export), say so. A model
audit needs formulas. Without them, limit yourself to arithmetic checks on
the visible numbers and label the report "values only".

### 2. Run the twelve checks

Work through each check across the whole workbook, not just one sheet:

1. **Hardcodes in formulas**: constants typed inside formulas (`=B4*1.05`,
   `=C10+2500`). Every assumption belongs in its own labelled input cell.
2. **Inconsistent rows**: a formula that changes partway across a row
   (columns C to K use one formula, column L uses another).
3. **Short or long ranges**: SUM, AVERAGE or lookup ranges that miss the
   first or last row, or include the subtotal they feed.
4. **Sign errors**: costs entered as positives in one place and negatives
   in another, subtracted twice or added instead of subtracted.
5. **Broken or external links**: `#REF!`, links to other files, links to
   files you cannot reach.
6. **Error values hidden**: `IFERROR(...,0)` or `IFERROR(...,"")` wrapping
   formulas so real errors show as zero.
7. **Circular references**: intended (interest on average debt) or
   accidental. Iterative calculation turned on hides them.
8. **Units and scale**: thousands mixed with units, monthly rates applied
   to annual figures, percentages stored as 5 instead of 0.05.
9. **Lookup traps**: VLOOKUP without exact match, hardcoded column
   numbers that break when a column is inserted.
10. **Balance checks**: does the balance sheet balance in every period?
    Does cash on the balance sheet equal cash in the cash flow? Do segment
    totals equal the group total?
11. **Timing**: the first and last periods (opening balances, stub
    periods, year-end) are where formulas most often differ.
12. **Hidden content**: hidden rows, columns or sheets that carry values
    into the model, and white-on-white text.

### 3. Rate each finding

- **CRITICAL**: changes a key output by more than 1%, or breaks a balance
  check. Fix before anyone sees the model.
- **MAJOR**: wrong logic that does not move the current outputs much but
  will when inputs change (a short range on a row that is zero today).
- **MINOR**: model hygiene (a hardcode that equals the input cell, unclear
  labels). Fix when convenient.

For every CRITICAL finding, estimate the effect on the main output:
"Fixing this lowers year-3 EBITDA from €2.41m to €2.18m."

### 4. Report

```
## Model audit: [file name]
Scope: [sheets reviewed], [formulas / values only]. Key outputs: [cells].

**Verdict:** [Safe to send / Fix N critical items first / Do not use]

| # | Severity | Sheet!Cell | What is wrong | Effect | Fix |
|---|---|---|---|---|---|
| 1 | CRITICAL | Forecast!L22 | SUM stops at row 20, misses "Other income" (row 21) | Revenue understated €84k in 2027 | =SUM(L14:L21) |

**Checks passed:** [list the checks that found nothing]
**Could not check:** [external links, macros, protected sheets]
```

Sort by severity, then by effect. Give the exact replacement formula, not
"fix the range".

## Judgment rules

**Read formulas, not results.** A plausible output proves nothing. Every
finding must point to a cell and a formula.

**Test with a probe.** When a formula's logic is unclear, change one input
(add 1,000 to a revenue line) and trace where the change appears. If it
does not reach the output, or reaches it twice, you found a problem. Say
you did it, and do not leave the probe in the file.

**Assumptions are the user's.** "A 25% growth rate is aggressive" is not an
audit finding. "The 25% growth rate is typed into 36 formulas instead of
one input cell" is.

**Do not rebuild silently.** Never rewrite the model and hand it back as
"fixed". List findings first, get a yes, then apply only the agreed fixes
and show a before/after of each changed output.

**Zero findings is suspicious.** In a model with more than a few hundred
formulas, re-run checks 2, 3 and 11. They are the ones that hide.

## Example

A five-year forecast shows EBITDA of €2.41m in year 3. The audit finds the
cost block in `Forecast!E30:I30` uses `=E28*0.62`, a hardcoded 62% cost
ratio, while the input sheet says 66% (`Inputs!C9`). The model was updated
once and the hardcode was not. CRITICAL: linking the cost line to
`Inputs!C9` drops year-3 EBITDA to €1.97m. The output looked fine. The
logic was out of date.