spreadsheet_model_audit v1.0.0

name: spreadsheet_model_audit description: "Audit a spreadsheet model before trusting it: hunt hardcodes in formulas, broken references, circular references, formulas that break their row or column pattern, values pasted over formulas, SUM ranges that miss a row, and totals that do not foot, then produce a severity ranked report and a fix log. Triggers when someone asks to audit, check, review, or validate a spreadsheet, Excel file, .xlsx model, or financial model, or says a total looks wrong."

Spreadsheet Model Audit

Purpose

Most spreadsheet errors are structural and findable without judging the business logic: a rate typed inside a formula, a total that stops one row short, a formula quietly replaced by a pasted number. This skill runs a fixed audit pass over a workbook, ranks what it finds by how much damage each defect can do, and leaves a fix log so the repair can be verified instead of assumed.

It covers .xlsx workbooks (Excel, Google Sheets exports, LibreOffice). It works from formula text and cached values, so it needs no spreadsheet app to run, and it states plainly which one check does need a workbook that a real app last saved.

Workflow

1. Copy the workbook and audit the copy. Never edit the original during the audit. Note the file name, who supplied it, and what decision the numbers feed.

1. Map the workbook before checking anything. List the sheets, which ones hold inputs, which hold calculations, and which hold outputs. Note hidden sheets. A model where inputs and formulas are mixed on the same cells gets flagged in the report even if every formula is sound, because the next editor cannot tell what is safe to type over. Good practice from the ICAEW Twenty Principles, paraphrased: separate inputs, workings, and outputs, be consistent in formula use, and never embed in a formula anything that might change.

1. Run the audit script:

python3 scripts/audit.py model.xlsx --out report.md --json report.json

The script is at scripts/audit.py in this skill folder and needs Python 3 with openpyxl. It checks for error values, broken references, circular references, hardcodes in formulas, inconsistent formulas, pasted values, SUM range gaps, totals that do not foot, external links, volatile functions, and hidden sheets, rows, and columns. Exit code 1 means findings at or above the default high threshold, 0 means none. Full definitions and severities are in references/error-taxonomy.md.

1. Work the findings in severity order, critical first. For each one, open the cell, trace its precedents and dependents by hand, and decide which of three things it is: a plain error, a deliberate choice that was never documented, or a false alarm from the static check. Record the decision in the fix log at references/fix-log-template.md. A deliberate circular reference or override is acceptable only when it is labelled in the sheet and named in the log.

1. Run the checks the script cannot do:

• Balance checks where the model calls for them: total assets equal total liabilities plus equity in every period, ending cash on the cash flow statement equals cash on the balance sheet, and each subtotal foots to its detail.
• Input flip test: change one key input by a round amount and confirm the outputs that should move do move. Any output that stays still is fed by a pasted value or a dead link.
• Sign pass: read down each total and confirm costs, outflows, and income carry one consistent sign convention, with no item both added and subtracted.
• Range pass on headline totals: read the SUM range out loud against the rows on screen, first row and last row included.

1. Fix on the copy, or hand the fix log to the owner and fix nothing, whichever the request asked for. Never fix silently: every change goes in the log with the before and after formula.

1. Rerun the script on the fixed copy. Then open the fixed copy in a real spreadsheet app, force recalculation, save, and rerun once more. The second rerun matters because the totals footing check only runs on cached values, and a script written workbook has none until an app saves it.

1. Prove the fixtures still work before trusting a modified script: run python3 scripts/make_fixtures.py fixtures, then audit both fixtures. broken.xlsx must report 12 findings and clean.xlsx must report 0. If either number differs, the script changed behaviour and the audit results from it are not comparable to earlier runs.

Output Contract

Deliver three things:

• The audit report from the script, with every finding ranked critical, high, medium, or low, each with sheet and cell, the defect in plain words, and the fix.
• The manual checks from step 5, each marked pass, fail, or not applicable for this model, with the numbers for any failure. For a balance failure, give the gap per period, not just the fact of it.
• The fix log, one row per finding, with the decision taken and whether the rerun verified it.

Close with one verdict line: safe to use, safe after the listed fixes, or do not use. Base it on the worst open finding. Any open critical finding means do not use.

Operating Rules

• Audit a copy. The original file is evidence.
• Separate what the script found from what you found by hand. Label each finding with its source so a reader can reproduce it.
• Treat an error value as a stop sign, not a display problem. Wrapping a cell in IFERROR before its cause is known hides the defect and keeps the wrong number in play.
• Count 0 and 1 as legitimate constants inside formulas. Flag every other embedded number and let the reviewer argue for any exception, in the log, by cell.
• Do not quote spreadsheet error rates or study figures in a report unless you verified the figure against its source for that report. The findings in the file in front of you are the evidence that matters.
• State the cached value limit whenever you report a clean totals check on a file that was never saved by a spreadsheet app: that check was skipped, not passed.
• If the workbook has macros, pivot caches, or external data connections, say so in the report and review them separately. The script does not inspect them.
• Present findings with cell addresses a person can click to, never as a count alone. "Three problems" helps nobody fix anything.