Spreadsheet Model Auditor

# Spreadsheet Model Auditor

Models look right when you read them cell by cell. Structural errors -- a SUM that stopped one row short, a formula overwritten with a typed number in month 7, a rate hardcoded in one copy of a formula -- are invisible to eyeballing and are exactly what gets people fired over a board number. Do not audit by reading the grid. Run the script, which reads every formula, then spend human judgment on what a script cannot know: whether the assumptions are sane.

## Workflow

### 1. Get an .xlsx

- Excel file: use it directly (.xlsx or .xlsm). Legacy .xls: convert first with `soffice --headless --convert-to xlsx file.xls`.
- Google Sheets: File > Download > Microsoft Excel (.xlsx). Read `references/google-sheets.md` for which Sheets functions survive export and what to check manually.
- CSV is not a model. It has no formulas; there is nothing structural to audit. Ask for the workbook.

### 2. Run the script

```bash
python3 scripts/audit_xlsx.py model.xlsx --json audit.json --md audit.md
```

Add `--recalc` when the report says `cached values: NO`. That happens when the file was written by a script, a BI export, or any tool that does not calculate: openpyxl can read formulas from any file, but stored results exist only if Excel (or LibreOffice) calculated and saved it. `--recalc` runs `soffice --headless --convert-to xlsx` on a copy so error, footing, and stale-value checks can run. Without LibreOffice the script still runs every formula-based check and says which value checks it skipped -- never report "no errors" for a check that was skipped.

Needs only `openpyxl` (`pip install openpyxl`). It handles 50k-formula workbooks in a few seconds.

### 3. Triage the findings

Read `audit.md`. Findings are ranked critical > high > medium > low > info, each with the cell, the formula, why it matters, and the fix. Before presenting:

- Open the cited cells yourself (the JSON has the formula). Confirm each critical and high finding is real in context; drop or downgrade what is clearly intentional (a labeled override row, a deliberate plug with a note).
- Collapse repeats. Twelve `sum_range_omission` findings across B11:M11 are one problem: "Total opex omits the Software row in every month."
- Trace the dollar impact of the top findings where you can: "Total opex is understated by 1,200/month, 14,400/year, so operating income is overstated by the same."
- `error_value` lists root causes only; propagated errors clear when the root is fixed.

The full list of checks, with examples and fixes, is in `references/error-catalog.md`. Read it when a finding type is unfamiliar or the user asks what a check means.

### 4. Review what the script cannot judge

Structural integrity is necessary, not sufficient. A perfectly built model with a 40% monthly growth rate is still wrong. Always do this pass and label it as judgment, not detection:

- **Assumptions.** List every input on the inputs sheet (or every typed number feeding formulas). For each: is it plausible for this business, sourced, and dated? Flag growth rates that compound to absurd annual numbers (3%/month is 43%/year), churn and conversion rates outside normal ranges, prices that disagree with the stated price list.
- **Units and periods.** Monthly vs annual rates mixed (an annual salary divided by 12 in one row and not another), thousands vs units, percentages typed as whole numbers, fiscal vs calendar periods, a 13th month or a missing one.
- **Sign conventions.** Costs positive-and-subtracted or negative-and-added, consistently. Check that every total's arithmetic matches the convention.
- **Timing.** Does cash follow the stated terms (net-30 revenue should not land the same month)? Do annual costs hit the right month?
- **Sensitivity of the top 3 drivers.** Identify the three inputs that move the headline output most (usually volume, price, and the largest cost). State the output at +/-10% on each, computed from the model's own structure. If the conclusion flips inside that range, say so -- that is the most useful sentence in the audit.
- **Does it answer the question?** A forecast with no cash line, a commission calc that ignores clawbacks, a pricing model with no volume discount: note what is missing.

### 5. Report

Use this shape. Lead with the verdict, not the method.

```
## Verdict
<Trustworthy / Usable after fixes / Do not use>, in one or two sentences with the
dollar impact of the worst problem.

## Must fix (critical + high)
1. <Sheet!Cell> -- <what is wrong in plain words> -- <impact> -- <exact fix>

## Should fix (medium)
## Worth knowing (low / info, grouped)

## Assumption review (judgment, not detected)
<table: input, value, concern, suggested range or question for the owner>

## Sensitivity
<top 3 drivers, output at -10% / base / +10%>

## Not checked
<anything skipped: no cached values, INDIRECT targets, macros, pivot tables>
```

Offer to fix the workbook when the user wants it: make the changes with openpyxl on a copy (never overwrite the original), re-run the audit on the copy, and show the before/after finding counts.

## What the script cannot catch

Say these limits out loud when relevant instead of implying a clean bill of health:

- Wrong logic that is internally consistent (the wrong formula copied correctly across all 12 months).
- Wrong assumptions, wrong units, wrong source data.
- Targets of INDIRECT and OFFSET (they are flagged, but their precedents are invisible), macros/VBA, Power Query, pivot table sources, data validation, conditional formatting logic.
- Values when the file has no cached results and LibreOffice is unavailable.
- LibreOffice recalculation is close to Excel but not identical for newer functions (LAMBDA, some dynamic arrays); treat value-based findings on those cells with care.

## Rules

- Cite every finding by `Sheet!Cell`. A finding without an address cannot be fixed.
- Never call a model "correct". The strongest claim is "no structural errors found by these checks", plus the assumption review.
- Separate detected (script) from judged (you). Readers weigh them differently.
- Business-neutral language: explain why it matters in terms of the decision the model drives (budget approval, price change, payout), not spreadsheet jargon.
- Do not modify the user's file unless asked, and then only a copy.

## Files

- `scripts/audit_xlsx.py` -- the auditor (JSON + markdown output).
- `references/error-catalog.md` -- every check: example, why it matters, fix, false-positive notes.
- `references/google-sheets.md` -- exporting from Sheets and Sheets-only functions.
- `examples/worked-example.md` -- a full run on a broken operating model, from script output to final report.
- `tests/build_fixtures.py`, `tests/run_tests.py` -- planted-error fixtures and the regression test.