Skip to main content

​
Audit XLS Skill

​
What is Excel Auditing?

Excel auditing is the process of systematically reviewing a spreadsheet for errors — broken formulas, hardcoded values that should be formulas, inconsistent calculations, and structural problems that could produce incorrect outputs. In financial services, where spreadsheet models drive investment decisions worth millions or billions of dollars, auditing is not optional — it is a critical quality control step. Financial models are complex systems. A typical 3-statement model might have thousands of cells with interdependent formulas across multiple tabs. Over weeks of development, multiple people edit the model: an analyst builds the initial version, an associate adds a scenario, a VP pastes in updated numbers, and an MD requests a last-minute change. Each edit creates opportunities for error:
  • A formula gets pasted over with a hardcoded number (looks right today but will not update when assumptions change)
  • A SUM range misses a newly inserted row (totals are wrong but look plausible)
  • Someone changes a formula in one row but not in the row below it (inconsistent patterns)
  • A cross-tab reference breaks when a tab gets renamed or a row gets inserted
  • A circular reference is introduced without anyone noticing
These errors are especially dangerous because they are often silent — the model still runs, the numbers look plausible, and the output seems reasonable. But the answer is wrong. Worked example: Consider a DCF model where the WACC is calculated in cell B25 as =0.106*0.80 + 0.04125*0.20. This produces 9.31%, which is correct. But the formula hardcodes all four inputs instead of referencing cells. Three months later, the risk-free rate changes from 4.0% to 4.5%. The analyst updates the risk-free rate input cell but the WACC does not change because it does not reference that cell. The entire DCF valuation is now wrong by approximately 3-5% — enough to shift a recommendation from “buy” to “hold.” The error is invisible because the WACC of 9.31% still looks reasonable.

​
Detailed Worked Example

Let us walk through a systematic audit of a simplified DCF model spreadsheet.
1

Determine Scope and Initial Scan

The model has 4 tabs: Assumptions, Income Statement, Balance Sheet, Cash Flow / DCF.Initial scan results:
Tab: Assumptions     - 45 cells with data, 12 formulas, 33 inputs
Tab: Income Statement - 180 cells with data, 150 formulas, 30 inputs
Tab: Balance Sheet    - 220 cells with data, 190 formulas, 30 inputs
Tab: Cash Flow / DCF  - 160 cells with data, 140 formulas, 20 inputs

Total: 605 cells, 492 formulas, 113 inputs
Excel errors found: 2 (#REF! on Balance Sheet)
Circular references: 1 (Cash Flow tab)
Hidden tabs: 1 ("old_version" - contains stale data)
2

Formula-Level Checks

Check 1: Excel Errors
#TabCellErrorCause
1BSG18#REF!References deleted column (was column F before restructure)
2BSG19#REF!Same root cause — formula was =F19+E19, column F deleted
Check 2: Hardcoded Values in Formula Cells
#TabCellIssueValue
3ISD15SG&A hardcoded as $45.2M instead of formula$45.2M
4CFE30WACC hardcoded as 0.0931 instead of referencing Assumptions9.31%
5CFE35Terminal growth hardcoded as 0.025 instead of referencing Assumptions2.5%
6ISE12Revenue Year 2 hardcoded as $560M (should be =D12*(1+Assumptions!B8))$560.0M
Check 3: Inconsistent Formulas
#TabCellIssue
7ISF15Year 3 SG&A uses =F120.18 but Years 4-5 use =F12Assumptions!BB12
8CFD22Year 1 CapEx uses =D120.05 but Years 2-5 use =D12Assumptions!BB15
Check 4: Off-by-One Ranges
#TabCellIssue
9ISD20=SUM(D15:D18) but D14 (R&D expense) should be included. Correct: =SUM(D14:D18)
3

Model Integrity Checks

Balance Sheet Balance:
PeriodAssetsL + EDifferenceStatus
Year 0$450.0M$450.0M$0Pass
Year 1$502.3M$502.3M$0Pass
Year 2$558.1M$555.8M$2.3MFAIL
Year 3$618.4M$613.9M$4.5MFAIL
Year 4#REF!#REF!N/AERROR
Year 5#REF!#REF!N/AERROR
Root cause analysis: The BS imbalance starting in Year 2 traces to the SG&A hardcode in IS cell D15 (Finding #3). The hardcoded SG&A breaks the NI calculation, which breaks the RE roll-forward, which breaks the BS. Years 4-5 show #REF! due to Finding #1 (deleted column reference).Cash Tie-Out:
PeriodCF Ending CashBS CashDifferenceStatus
Year 0$80.0M$80.0M$0Pass
Year 1$95.2M$95.2M$0Pass
Year 2$112.8M$110.5M$2.3MFAIL
The cash difference matches the BS imbalance — same root cause.Retained Earnings Roll-Forward:
PeriodPrior RE + NI - DivEnding REDifference
Year 1250+250 + 52.3 - $0$302.3M$0 — Pass
Year 2302.3+302.3 + 58.1 - $0$358.1M$2.3M gap — the NI feeding RE is wrong due to the SG&A hardcode
4

Audit Report

MODEL AUDIT REPORT
Model Type: DCF
Overall Status: MAJOR ISSUES DETECTED
Critical: 4 | Warning: 5 | Info: 2

CRITICAL FINDINGS:
1. BS does not balance in Years 2-5 (gap: $2.3M-$4.5M)
   Root cause: SG&A hardcode in IS!D15
   Fix: Replace with =D12*Assumptions!$B$12

2. #REF! errors in BS Years 4-5 (cells G18, G19)
   Root cause: Column F was deleted, breaking references
   Fix: Rebuild formulas to reference correct columns

3. WACC hardcoded in CF!E30 (9.31%)
   Risk: Model will not update when CAPM inputs change
   Fix: Replace with =Assumptions!B25 (or build WACC formula)

4. Revenue Year 2 hardcoded in IS!E12 ($560M)
   Risk: Growth rate changes will not flow through
   Fix: Replace with =D12*(1+Assumptions!$B$8)

WARNING FINDINGS:
5. Terminal growth hardcoded in CF!E35 (2.5%)
6. SG&A inconsistent formula pattern (Year 3 vs Years 4-5)
7. CapEx inconsistent formula pattern (Year 1 vs Years 2-5)
8. Off-by-one SUM range in IS!D20 (misses R&D)
9. Hidden tab "old_version" contains stale calculations

INFO FINDINGS:
10. Circular reference in CF tab (interest -> cash -> debt)
    Status: Intentional, iteration toggle exists
11. Color convention not applied consistently
    (some inputs in black font instead of blue)

​
Why It Matters

The stakes of spreadsheet errors in finance are enormous:
  • Investment decisions — A DCF model with a broken formula might value a company at 50pershareinsteadof50 per share instead of 35, leading to a recommendation to overpay for an acquisition.
  • Client deliverables — A comps table with pasted-over formulas cannot be updated when new data arrives. The analyst will need to rebuild it from scratch.
  • Regulatory filings — Models used for fairness opinions or SEC filings face legal scrutiny. Errors can have regulatory and legal consequences.
  • Internal credibility — Presenting a model to an investment committee and having someone find an error undermines the entire analysis.
Research has consistently shown that the majority of complex spreadsheets contain errors. In finance, where models routinely have 10,000+ formula cells, the probability of at least one error is extremely high. Systematic auditing catches these errors before they cause harm.

​
Key Concepts

TermDefinitionWhy It Matters
Hardcoded OverrideA cell that should contain a formula but instead has a typed number.The #1 source of silent bugs. The number looks right today but will not update when assumptions change.
Formula ErrorExcel error values: #REF!, #VALUE!, #N/A, #DIV/0!, #NAME?These are obvious when you look at them but easy to miss in a large model, especially on tabs you rarely visit.
Inconsistent FormulaA formula that breaks the pattern of its neighbors in a row or column.If rows 5-9 all use =BC but row 7 uses =BD, that is likely an error.
Off-by-One RangeA SUM or AVERAGE that misses the first or last row of the intended range.Common when rows are inserted or deleted. The total looks plausible but is wrong.
Circular ReferenceA formula that directly or indirectly references itself.Can be intentional (interest calculations) or accidental. Accidental circulars produce incorrect results.
Balance Sheet BalanceAssets = Liabilities + Equity.If this does not hold for every period, the model has a structural error. Nothing else matters until this is fixed.
Cash Tie-OutCF Ending Cash = BS Cash for every period.If cash does not tie, there is a missing cash flow item or a broken link between statements.
Color ConventionBlue font = hardcoded inputs, Black font = formulas, Green = cross-tab links.Helps auditors quickly identify which cells should be formulas and which are intentional inputs.

​
How It Works

Triggers when: “audit this sheet”, “check my formulas”, “find formula errors”, “debug model”, “model won’t balance”, or “something’s off in my model.”

​
Step 1: Determine Scope

ScopeWhat It Covers
SelectionJust the currently selected range — quick formula checks
SheetThe current active sheet — formula-level checks only
ModelThe whole workbook — formula checks plus financial model integrity (BS balance, cash tie-out, roll-forwards, logic sanity)

​
Step 2: Formula-Level Checks (All Scopes)

CheckWhat to Look For
Formula errors#REF!, #VALUE!, #N/A, #DIV/0!, #NAME?
Hardcodes inside formulas=A1*1.05 — the 1.05 should be a cell reference
Inconsistent formulasA formula that breaks the pattern of its neighbors
Off-by-one rangesSUM/AVERAGE that misses the first or last row
Pasted-over formulasCell that looks like a formula but is actually hardcoded
Circular referencesIntentional or accidental
Broken cross-sheet linksReferences to cells that moved or were deleted
Unit/scale mismatchesThousands mixed with millions
Hidden rows/tabsCould contain overrides or stale calculations

​
Step 3: Model-Integrity Checks (Model Scope Only)

Balance Sheet:
  • Total Assets = Total Liabilities + Equity (every period)
  • Retained Earnings rolls forward correctly
  • If the BS does not balance, quantify the gap per period and trace the break point
Cash Flow Statement:
  • CF Ending Cash = BS Cash (every period)
  • CFO + CFI + CFF = Change in Cash
  • D&A, CapEx, and working capital changes tie to source schedules
Income Statement:
  • Revenue build ties to segment/product detail
  • Tax expense = Pre-tax income x tax rate
  • Share count ties to dilution schedule
Model-Type-Specific Bugs:
  • Discount rate applied to wrong period (mid-year vs. end-of-year convention)
  • Terminal value not discounted back to present value
  • WACC uses book values instead of market values
  • FCF includes interest expense (should be unlevered)
  • Tax shield double-counted
  • Debt paydown does not match cash sweep mechanics
  • PIK interest not accruing to principal balance
  • Management rollover not reflected in equity returns
  • Exit multiple applied to wrong EBITDA (LTM vs NTM)
  • Transaction fees not deducted from Day 1 equity
  • Accretion/dilution uses wrong share count (pre- vs. post-deal)
  • Synergies not phased in over the integration period
  • Purchase price allocation does not balance
  • Foregone interest income on cash not included
  • Working capital changes have wrong sign (increase in AR should be negative on CF)
  • Depreciation does not match PP&E schedule
  • Debt maturity schedule does not match principal payments
  • Dividends exceed net income without explanation

​
Step 4: Report

Output a findings table with severity levels:
SeverityMeaning
CriticalWrong output — BS does not balance, formula broken, cash does not tie
WarningRisky — hardcodes, inconsistent formulas, edge-case failures
InfoStyle/best-practice — color coding, layout, naming conventions
Claude reports findings but does not change anything without explicit permission. Report first, fix on request.

​
Common Mistakes

The mistake: The BS shows a 2.3MgapinYear2.TheauditoradjustsaBSlineitemby2.3M gap in Year 2. The auditor adjusts a BS line item by 2.3M to make it balance instead of tracing the root cause.Why it happens: Plugging the gap is faster than tracing the error through interconnected formulas.The fix: Never plug a gap. Trace the error back to its source: identify which period first shows the imbalance, then check the RE roll-forward, cash tie-out, and PP&E roll-forward for that period. The root cause is almost always a broken formula in one of these four areas.
The mistake: The audit verifies that all formulas calculate correctly but does not check whether cells that should be formulas have been overwritten with hardcoded values.Why it happens: Hardcoded cells do not show errors — they show perfectly reasonable numbers. They are invisible to a formula-only audit.The fix: Check every cell in the projection columns. If a cell contains a number but its neighbors contain formulas, it is likely a hardcoded override. Use Excel’s Ctrl+` (grave accent) to toggle formula view, which makes hardcodes immediately visible.
The mistake: The audit covers all visible tabs but misses a hidden tab called “old_calcs” that contains overrides affecting visible calculations.Why it happens: Hidden tabs are not visible in the tab bar. Hidden rows are not visible in the sheet.The fix: Right-click the tab bar and check for hidden sheets. Use Ctrl+Shift+9 (unhide rows) and Ctrl+Shift+0 (unhide columns) to check for hidden data. A hidden tab with active formulas can silently feed incorrect values into the visible model.
The mistake: The model works perfectly at 10% revenue growth but breaks at 0% growth (produces division by zero) or negative growth (produces negative working capital).Why it happens: The model was built and tested with a specific set of assumptions. No one tested what happens at the boundaries.The fix: Set revenue growth to 0% and check every formula. Set growth to -5% and check again. Set EBITDA to negative and verify the model handles it (taxes, leverage ratios, coverage ratios). A model that breaks under stress scenarios is not reliable.
The mistake: All formulas are technically correct (no #REF!, no hardcodes, consistent patterns) but the model’s logic is wrong — for example, CapEx is modeled as a percentage of EBITDA instead of revenue.Why it happens: The audit focuses on formula mechanics and misses whether the formula represents the correct financial concept.The fix: For each key formula, verify not just that it computes correctly but that it represents the right calculation. CapEx should be a percentage of revenue (or linked to a PP&E schedule), not a percentage of EBITDA. Working capital should be driven by DSO/DIO/DPO, not arbitrary dollar amounts.
The mistake: The model has a circular reference for interest expense that uses iterative calculation, but the iteration toggle is turned off. All interest-dependent cells show zero.Why it happens: The model was built on a computer with iterative calculation enabled. When opened on a different computer, the setting does not carry over.The fix: If the model uses intentional circular references, add a circuit breaker toggle in the Assumptions tab and document the iterative calculation requirement. The audit should verify that the toggle works and that interest calculations produce reasonable values.
The mistake: The IS shows Net Income of 52.3M,buttheCFtabstartswithNetIncomeof52.3M, but the CF tab starts with Net Income of 50.0M. Both are formulas — the CF tab references the wrong cell on the IS.Why it happens: Someone inserted a row on the IS tab, shifting the Net Income row down by one. The CF tab’s reference did not update because it was an absolute reference (DD25 instead of a named range or dynamic reference).The fix: Use named ranges for key outputs (Net_Income, Total_Assets, Ending_Cash) so references do not break when rows are inserted. During the audit, verify that every cross-tab reference points to the correct source cell by tracing precedents.
The mistake: All formulas are correct, the BS balances, cash ties out, but the model projects revenue growing at 50% annually for 10 years, producing a 30Bcompanyfroma30B company from a 200M starting point.Why it happens: The audit focused on mechanical accuracy and did not question whether the assumptions produce reasonable outputs.The fix: After the mechanical audit, do a logic and reasonableness check. Flag: revenue growth >30% sustained for >3 years, terminal value >75% of EV, EBITDA margins exceeding industry norms, hockey-stick projections with no supporting thesis.

​
Daily Workflow

The investment committee meeting is in 2 hours. The associate asks you to do a quick audit of the DCF model before the presentation.Workflow:
  1. Set scope to “model” (full workbook audit)
  2. Check BS balance for all projection periods — this is the single highest-priority check
  3. Check cash tie-out for all periods
  4. Verify WACC inputs match current market data (risk-free rate, beta, ERP)
  5. Check that the sensitivity table center cell equals the base case output
  6. Scan for hardcoded overrides in the projection columns
  7. Verify terminal value as a percentage of EV (flag if >75%)
  8. Report findings with severity levels. Critical findings must be fixed before IC. Warnings can be noted and fixed after.
A first-year analyst just completed their first 3-statement model. You are the associate responsible for reviewing it before it goes to the VP.Workflow:
  1. Full model audit: formula-level checks + integrity checks + logic checks
  2. Pay special attention to: working capital signs (the #1 first-model error), RE roll-forward, and D&A consistency
  3. Check that all inputs have cell comments citing their source
  4. Verify that projection formulas reference the Assumptions tab (not hardcoded rates)
  5. Test with scenario toggle: do all three scenarios produce reasonable outputs?
  6. Run edge cases: 0% growth, negative EBITDA
  7. Provide a marked-up list of findings for the analyst to fix, organized by priority
  8. Re-audit after fixes to verify nothing was introduced during the repair
An analyst says “my model won’t balance and I’ve been staring at it for 2 hours.” Time to systematically find the bug.Workflow:
  1. Check the BS balance row for every period. Identify the first period where the gap appears.
  2. In that period, check the RE roll-forward: Prior RE + NI - Dividends = Ending RE. If this does not hold, the error is in the NI calculation or the RE formula.
  3. If RE ties, check the cash tie-out: CF Ending Cash = BS Cash. If cash does not tie, there is a missing cash flow item.
  4. If cash ties, check PP&E: Beginning PP&E + CapEx - D&A = Ending PP&E.
  5. If PP&E ties, check debt: Beginning Debt - Repayments + New Issuances = Ending Debt.
  6. The error is almost always in one of these four areas. Once found, fix the root cause and verify all subsequent periods automatically correct.

​
Practice Exercise

Scenario: You receive a 3-statement model for a retail company and are asked to audit it before an investment committee presentation. The model has 3 tabs (IS, BS, CF) plus an Assumptions tab. Given the following findings from your initial scan:
  1. BS balances in Years 0-2 but shows a $1.5M gap in Year 3
  2. Two cells on the IS show #DIV/0! errors (Years 4-5 tax rate calculations)
  3. The CF tab has a hidden row (Row 45) containing an old WACC calculation
  4. Year 2 SG&A on the IS is 82.0M but the Assumptions tab shows SG&A should be 18% of revenue (88.2M revenue x 18% = $15.9M? That does not make sense)
  5. The sensitivity table shows the same value ($24.50) in all 25 cells
  6. D&A on the CF is 12.0M but D&A on the IS is 14.5M
Your tasks:
  1. Classify each finding as Critical, Warning, or Info
  2. For the BS imbalance, describe the debugging process you would follow
  3. For the #DIV/0! errors, identify the most likely cause and fix
  4. For the sensitivity table, explain why all cells show the same value and how to fix it
  5. For the D&A mismatch, trace the likely impact on other parts of the model
The BS imbalance in Year 3 is almost certainly caused by the D&A mismatch (Finding #6). If D&A is 14.5MontheISbutonly14.5M on the IS but only 12.0M is added back on the CF, then 2.5M of non-cash expense is treated as if it consumed cash. This overstates the reduction in PP&E by 2.5M relative to the cash impact, creating a BS gap. But the gap is 1.5M,not1.5M, not 2.5M — which means the D&A mismatch may interact with another error (perhaps the Year 3 SG&A issue) to produce the net $1.5M gap.

​
How to Add to Your Local Context

1

Install the Plugin

claude plugin install financial-analysis@financial-services-plugins
2

Add Firm Audit Standards

Edit skills/audit-xls/SKILL.md:
## Firm Audit Standards
- All models must pass BS balance check before IC presentation
- Tolerance for rounding differences: $0.01M maximum
- Flag any formula referencing more than 3 tabs (complexity risk)
- Check for VBA macros and note any macro-driven calculations
3

Set Color Convention

## Our Color Convention
- Blue (#0000FF): hardcoded inputs
- Black (#000000): formulas
- Green (#008000): cross-tab links
- Purple (#800080): same-tab links
- Flag any cell that violates this convention

​
Best Practices

These are the most important habits for effective model auditing:
  • BS balance first — If the balance sheet does not balance, everything downstream is suspect. Fix this before looking at anything else.
  • Search for hardcodes aggressively — They are the #1 source of silent bugs and the hardest to find because the numbers look correct.
  • Check sign conventions — Positive vs. negative for cash outflows is extremely common and creates errors that look plausible.
  • Test edge cases — Set revenue growth to 0% and negative. Set EBITDA to negative. See if the model breaks.
  • Check hidden tabs — Hidden worksheets can contain overrides, stale calculations, or circular reference breakers that affect visible outputs.
  • Trace root causes, never plug gaps — A balance sheet that was forced to balance by plugging is worse than one that openly shows an imbalance.