Financial Model Audit Checklist: 30+ Checks Before You Send
Structural Checks
Before diving into formulas, verify the model’s architecture is sound. A well-structured model follows predictable patterns that make it easier to audit, update, and hand off.
| # | Check | What to Verify |
|---|---|---|
| 1 | Sheet organization | Logical tab order: Cover → Assumptions → IS → BS → CF → Schedules → Outputs. Color-code tabs by function. |
| 2 | Time flow direction | Time flows left to right. Historical periods on the left, projections on the right. Consistent across all sheets. |
| 3 | Row consistency | Same line item appears on the same row across linked sheets. Revenue on row 10 of the IS should link to row 10 references in CF. |
| 4 | Assumptions isolated | All hard-coded assumptions live on a dedicated Assumptions sheet, not scattered across the model. |
| 5 | No hardcodes in formulas | Search for numbers embedded in formulas. Every input should reference a labeled assumption cell. |
| 6 | Color coding | Blue font = hardcoded inputs. Black font = formulas. Green = links to other sheets. Consistent throughout. |
| 7 | Sign convention | Pick one convention (positive = inflow, negative = outflow) and stick to it. Document it on the cover sheet. |
Formula Integrity
Formula errors are the most common and most dangerous issues in financial models. One wrong cell reference can cascade through the entire model.
| # | Check | What to Verify |
|---|---|---|
| 8 | Formula consistency across rows | Select a row of formulas and use Ctrl+ to highlight inconsistencies. Every cell in a projection row should have the same formula pattern. |
| 9 | No broken references | Search for #REF!, #N/A, #VALUE!, #DIV/0! errors. Use Ctrl+G → Special → Formulas → Errors. |
| 10 | Circular references documented | If circular references exist, verify circuit breaker works and the circular path is documented. |
| 11 | IFERROR usage | Division formulas should be wrapped in IFERROR to handle divide-by-zero scenarios gracefully. |
| 12 | No volatile functions in heavy areas | OFFSET, INDIRECT, and NOW() recalculate every time. Avoid them in large data ranges — use INDEX MATCH instead. |
| 13 | Array formulas flagged | Any Ctrl+Shift+Enter formulas should be documented. They’re easy to accidentally break when copying. |
| 14 | Named ranges accurate | Check Name Manager (Ctrl+F3) for any named ranges pointing to #REF! or incorrect ranges. |
Balance Sheet & Financial Statement Checks
| # | Check | What to Verify |
|---|---|---|
| 15 | Balance sheet balances | Assets = Liabilities + Equity in every period. Add a check row that shows the difference — it should be zero. |
| 16 | Cash flow ties to balance sheet | Ending cash on the CF statement equals cash on the BS. Beginning cash equals prior period ending cash. |
| 17 | Retained earnings roll-forward | Beginning RE + Net Income − Dividends = Ending RE. This is where most BS imbalances originate. |
| 18 | Debt schedule ties | Ending debt on the debt schedule matches the BS. Interest expense matches the IS. |
| 19 | Working capital consistency | Working capital changes on CF match the BS movements in AR, AP, and inventory. |
| 20 | Depreciation ties | Depreciation on IS matches the schedule. Cumulative depreciation on BS matches net PP&E roll-forward. |
| 21 | Tax calculation | Tax expense = Pre-tax income × Effective tax rate. Check for NOL carryforwards and deferred tax impacts. |
Assumption Reasonableness
| # | Check | What to Verify |
|---|---|---|
| 22 | Revenue growth rates | Are growth rates realistic relative to historical performance and industry benchmarks? Flag anything above 20% for mature companies. |
| 23 | Margin trends | Do margins improve, decline, or stay flat? Is the trajectory justified by the business case? Cross-check with comparable companies. |
| 24 | Terminal assumptions | If using a DCF, verify terminal value assumptions (growth rate, exit multiple) are conservative. Terminal growth should not exceed GDP growth. |
| 25 | WACC inputs | WACC components (risk-free rate, beta, ERP, cost of debt) should be sourced and documented. |
| 26 | Sensitivity tested | Run sensitivity analysis on key assumptions. Outputs should move in the expected direction. |
Formatting & Presentation
| # | Check | What to Verify |
|---|---|---|
| 27 | Number formatting | Consistent format: dollars in thousands or millions (state units clearly), percentages to one decimal, dates in consistent format. |
| 28 | Print setup | Page breaks, headers, footers set correctly. Each sheet should print cleanly on standard paper. |
| 29 | Hidden rows/columns | Unhide all to check for hidden calculations. Group rows/columns instead of hiding them. |
| 30 | External links removed | Edit → Links → Break All. External links break when recipients don’t have the source files. |
| 31 | File size reasonable | Models over 10MB may have excess data, unused ranges, or embedded objects. Clean up before sending. |
| 32 | Cover sheet complete | Company name, model version, date, author, key assumptions summary, units, and sign convention documented. |
Final Pre-Send Checklist
Before hitting send, do these final checks:
Stress test the model. Set revenue growth to 0% and negative 10%. Does the model still function? Do debt covenants trigger? Does the revolver behave correctly? A robust model handles adverse scenarios without breaking.
Toggle the circuit breaker. Turn circular references off and on. Values should converge quickly. If they don’t, you have a structural issue.
Fresh eyes review. Have someone else look at the model, even briefly. They’ll catch formatting inconsistencies and logic gaps that you’ve become blind to after hours of building.
Save a clean copy. Remove any scratch work, test scenarios, or personal notes. Save as a new file with a clear version number.
Key Takeaways
- A model audit checklist is essential before sending any financial model — it prevents embarrassing errors and builds credibility.
- Start with structural checks (organization, color coding, sign conventions) before diving into formula verification.
- The balance sheet must balance in every period. Add automated check rows that flag imbalances in real time.
- Verify assumption reasonableness against historical data and industry benchmarks — not just formula accuracy.
- Run stress tests and toggle circuit breakers before sending. A model that breaks under adverse scenarios isn’t production-ready.
Frequently Asked Questions
How long should a model audit take?
A thorough audit of a standard three-statement model takes 2-4 hours. Complex models (LBOs, merger models) can take a full day. Allocate time proportional to the model’s complexity and the stakes involved. A quick self-audit before sending to your MD should take at least 30-60 minutes.
What’s the most common error in financial models?
Inconsistent formulas across time periods. An analyst copies a formula across a row but accidentally modifies one cell. Use Ctrl+ to check for formula inconsistencies — it highlights cells that differ from the pattern in the selected range.
Should I audit someone else’s model differently than my own?
Yes. When auditing your own model, you have context on the logic and are more likely to skip over issues. When auditing someone else’s model, start by understanding the structure and flow before checking formulas. Always check the cover sheet for documentation and verify you understand the sign convention.
What tools help with model auditing?
Excel’s built-in tools include: Trace Precedents/Dependents, Formula Auditing toolbar, Go To Special (errors, constants, formulas), and Ctrl+ for inconsistency checks. Third-party tools like Operis, Spreadsheet Detective, and Mazars offer more advanced auditing capabilities.
How do I handle errors found during an audit?
Document every error with its location, description, and suggested fix. Prioritize by impact — a wrong sign on a revenue line matters more than a formatting inconsistency. Fix structural issues first (broken links, circular reference problems), then formula errors, then formatting. Re-run the full checklist after fixes.