> ## Documentation Index
> Fetch the complete documentation index at: https://financecontext.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Audit XLS Skill

> Learn why Excel auditing matters in finance, the common errors that plague financial models, and how to systematically audit spreadsheets from formula checks to full model integrity reviews.

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

<Steps>
  <Step title="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)
    ```
  </Step>

  <Step title="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 =F12*0.18 but Years 4-5 use =F12*Assumptions!$B$12 |
    | 8 | CF  | D22  | Year 1 CapEx uses =D12*0.05 but Years 2-5 use =D12*Assumptions!$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) |
  </Step>

  <Step title="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 |
  </Step>

  <Step title="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)
    ```
  </Step>
</Steps>

## 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 =B*C but row 7 uses =B*D, 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:**

<Accordion title="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
</Accordion>

<Accordion title="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
</Accordion>

<Accordion title="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
</Accordion>

<Accordion title="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
</Accordion>

### 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        |

<Warning>
  Claude reports findings but does not change anything without explicit permission. Report first, fix on request.
</Warning>

## Common Mistakes

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

## Daily Workflow

<Accordion title="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:**

  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.
</Accordion>

<Accordion title="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:**

  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
</Accordion>

<Accordion title="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:**

  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.
</Accordion>

## 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

<Tip>
  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.
</Tip>

## How to Add to Your Local Context

<Steps>
  <Step title="Install the Plugin">
    ```bash theme={null}
    claude plugin install financial-analysis@financial-services-plugins
    ```
  </Step>

  <Step title="Add Firm Audit Standards">
    Edit `skills/audit-xls/SKILL.md`:

    ```markdown theme={null}
    ## 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
    ```
  </Step>

  <Step title="Set Color Convention">
    ```markdown theme={null}
    ## 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
    ```
  </Step>
</Steps>

## Best Practices

<Warning>
  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.
</Warning>