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.

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)

Formula-Level Checks

Check 1: Excel Errors

#	Tab	Cell	Error	Cause
1	BS	G18	#REF!	References deleted column (was column F before restructure)
2	BS	G19	#REF!	Same root cause — formula was =F19+E19, column F deleted

Check 2: Hardcoded Values in Formula Cells

#	Tab	Cell	Issue	Value
3	IS	D15	SG&A hardcoded as $45.2M instead of formula	$45.2M
4	CF	E30	WACC hardcoded as 0.0931 instead of referencing Assumptions	9.31%
5	CF	E35	Terminal growth hardcoded as 0.025 instead of referencing Assumptions	2.5%
6	IS	E12	Revenue Year 2 hardcoded as $560M (should be =D12*(1+Assumptions!B8))	$560.0M

Check 3: Inconsistent Formulas

#	Tab	Cell	Issue
7	IS	F15	Year 3 SG&A uses =F120.18 but Years 4-5 use =F12Assumptions! $B$ 12
8	CF	D22	Year 1 CapEx uses =D120.05 but Years 2-5 use =D12Assumptions! $B$ 15

Check 4: Off-by-One Ranges

#	Tab	Cell	Issue
9	IS	D20	=SUM(D15:D18) but D14 (R&D expense) should be included. Correct: =SUM(D14:D18)

Model Integrity Checks

Balance Sheet Balance:

Period	Assets	L + E	Difference	Status
Year 0	$450.0M	$450.0M	$0	Pass
Year 1	$502.3M	$502.3M	$0	Pass
Year 2	$558.1M	$555.8M	$2.3M	FAIL
Year 3	$618.4M	$613.9M	$4.5M	FAIL
Year 4	#REF!	#REF!	N/A	ERROR
Year 5	#REF!	#REF!	N/A	ERROR

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:

Period	CF Ending Cash	BS Cash	Difference	Status
Year 0	$80.0M	$80.0M	$0	Pass
Year 1	$95.2M	$95.2M	$0	Pass
Year 2	$112.8M	$110.5M	$2.3M	FAIL

The cash difference matches the BS imbalance — same root cause.Retained Earnings Roll-Forward:

Period	Prior RE + NI - Div	Ending RE	Difference
Year 1	$250 +$ 52.3 - $0	$302.3M	$0 — Pass
Year 2	$302.3 +$ 58.1 - $0	$358.1M	$2.3M gap — the NI feeding RE is wrong due to the SG&A hardcode

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 $50 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

Term	Definition	Why It Matters
Hardcoded Override	A 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 Error	Excel 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 Formula	A 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 Range	A 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 Reference	A formula that directly or indirectly references itself.	Can be intentional (interest calculations) or accidental. Accidental circulars produce incorrect results.
Balance Sheet Balance	Assets = 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-Out	CF 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 Convention	Blue 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

Scope	What It Covers
Selection	Just the currently selected range — quick formula checks
Sheet	The current active sheet — formula-level checks only
Model	The whole workbook — formula checks plus financial model integrity (BS balance, cash tie-out, roll-forwards, logic sanity)

Step 2: Formula-Level Checks (All Scopes)

Check	What 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 formulas	A formula that breaks the pattern of its neighbors
Off-by-one ranges	SUM/AVERAGE that misses the first or last row
Pasted-over formulas	Cell that looks like a formula but is actually hardcoded
Circular references	Intentional or accidental
Broken cross-sheet links	References to cells that moved or were deleted
Unit/scale mismatches	Thousands mixed with millions
Hidden rows/tabs	Could 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:

DCF 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

LBO Bugs

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

Merger Model Bugs

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

3-Statement Model Bugs

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:

Severity	Meaning
Critical	Wrong output — BS does not balance, formula broken, cash does not tie
Warning	Risky — hardcodes, inconsistent formulas, edge-case failures
Info	Style/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

1. Fixing symptoms instead of root causes

The mistake: The BS shows a

2.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.

2. Only checking formula cells and missing hardcodes

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.

3. Ignoring hidden tabs and rows

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.

4. Not testing edge cases

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.

5. Auditing formulas without checking logic

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.

6. Not verifying the circular reference handler

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.

7. Not checking cross-tab formula consistency

The mistake: The IS shows Net Income of

52.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 (

D

25 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.

8. Skipping the reasonableness check

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

30B 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

Scenario 1: Pre-IC Model Review

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:

Set scope to “model” (full workbook audit)
Check BS balance for all projection periods — this is the single highest-priority check
Check cash tie-out for all periods
Verify WACC inputs match current market data (risk-free rate, beta, ERP)
Check that the sensitivity table center cell equals the base case output
Scan for hardcoded overrides in the projection columns
Verify terminal value as a percentage of EV (flag if >75%)
Report findings with severity levels. Critical findings must be fixed before IC. Warnings can be noted and fixed after.

Scenario 2: New Analyst's First Model

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:

Full model audit: formula-level checks + integrity checks + logic checks
Pay special attention to: working capital signs (the #1 first-model error), RE roll-forward, and D&A consistency
Check that all inputs have cell comments citing their source
Verify that projection formulas reference the Assumptions tab (not hardcoded rates)
Test with scenario toggle: do all three scenarios produce reasonable outputs?
Run edge cases: 0% growth, negative EBITDA
Provide a marked-up list of findings for the analyst to fix, organized by priority
Re-audit after fixes to verify nothing was introduced during the repair

Scenario 3: Debugging a Model That 'Won't Balance'

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:

Check the BS balance row for every period. Identify the first period where the gap appears.
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.
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.
If cash ties, check PP&E: Beginning PP&E + CapEx - D&A = Ending PP&E.
If PP&E ties, check debt: Beginning Debt - Repayments + New Issuances = Ending Debt.
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:

BS balances in Years 0-2 but shows a $1.5M gap in Year 3
Two cells on the IS show #DIV/0! errors (Years 4-5 tax rate calculations)
The CF tab has a hidden row (Row 45) containing an old WACC calculation
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)
The sensitivity table shows the same value ($24.50) in all 25 cells
D&A on the CF is $12.0M but D&A on the IS is$ 14.5M

Your tasks:

Classify each finding as Critical, Warning, or Info
For the BS imbalance, describe the debugging process you would follow
For the #DIV/0! errors, identify the most likely cause and fix
For the sensitivity table, explain why all cells show the same value and how to fix it
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.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, 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

Install the Plugin

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

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

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.

Overview

Financial Analysis

Equity Research

Investment Banking

Private Equity

Wealth Management

LSEG

S&P Global

Audit XLS Skill

Audit XLS Skill

What is Excel Auditing?

Detailed Worked Example

Why It Matters

Key Concepts

How It Works

Step 1: Determine Scope

Step 2: Formula-Level Checks (All Scopes)

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

Step 4: Report

Common Mistakes

Daily Workflow

Practice Exercise

How to Add to Your Local Context

Best Practices

​Audit XLS Skill

​What is Excel Auditing?

​Detailed Worked Example

​Why It Matters

​Key Concepts

​How It Works

​Step 1: Determine Scope

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

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

​Step 4: Report

​Common Mistakes

​Daily Workflow

​Practice Exercise

​How to Add to Your Local Context

​Best Practices

Audit XLS Skill

What is Excel Auditing?

Detailed Worked Example

Why It Matters

Key Concepts

How It Works

Step 1: Determine Scope

Step 2: Formula-Level Checks (All Scopes)

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

Step 4: Report

Common Mistakes

Daily Workflow

Practice Exercise

How to Add to Your Local Context

Best Practices