audit-xls

Anthropic ｜ 金融與投資 ｜ 原文 ｜ Apache-2.0

稽核試算表的公式錯誤，並檢查財務模型的平衡與勾稽

---
name: audit-xls
description: 稽核試算表的公式正確性、錯誤和常見失誤。範圍可以是選取的範圍、單一工作表，或整個模型（包含財務模型的完整性檢查，例如 BS 平衡、現金勾稽和邏輯合理性）。觸發詞包含「audit this sheet」、「check my formulas」、「find formula errors」、「QA this spreadsheet」、「sanity check this」、「debug model」、「model check」、「model won't balance」、「something's off in my model」、「model review」。
---

# 稽核試算表

稽核公式和資料的正確性與失誤。範圍決定深度：從對選取範圍的快速公式檢查，到完整的財務模型完整性稽核都有。

## 步驟 1：決定範圍

如果使用者已經給了範圍，就用它。否則**問他們**：

> 你想要我稽核哪個範圍？
> - **selection**：只稽核目前選取的範圍
> - **sheet**：只稽核目前的作用中工作表
> - **model**：整個活頁簿，包含財務模型的完整性檢查（BS 平衡、現金勾稽、滾動結轉、邏輯合理性）

**model** 範圍最深，在把 DCF、LBO、三表、併購、可比公司或任何整合式財務模型送給客戶或投資委員會（IC）之前使用。

---

## 步驟 2：公式層級的檢查（所有範圍）

不管範圍是什麼，都要跑這些：

| 檢查 | 要找什麼 |
|---|---|
| 公式錯誤 | `#REF!`、`#VALUE!`、`#N/A`、`#DIV/0!`、`#NAME?` |
| 公式裡寫死的值 | `=A1*1.05`，其中的 `1.05` 應該改成儲存格參照 |
| 不一致的公式 | 在同一列/欄裡，打破相鄰儲存格規律的公式 |
| 範圍差一格（off-by-one） | `SUM`/`AVERAGE` 漏掉第一列或最後一列 |
| 被貼上覆蓋的公式 | 看起來像公式，其實是寫死的值的儲存格 |
| 循環參照 | 有意或無意的都要看 |
| 壞掉的跨工作表連結 | 參照到已經移動或被刪除的儲存格 |
| 單位/尺度不一致 | 千和百萬混用、百分比存成整數 |
| 隱藏的列/分頁 | 可能藏著覆蓋用的值或過時的計算 |

---

## 步驟 3：模型完整性檢查（只限 MODEL 範圍）

如果範圍是 **model**，先辨識模型類型（DCF / LBO / 三表 / 併購 / 可比公司 / 自訂），再跑下面對應的完整性檢查。

### 3a. 結構檢視

| 檢查 | 要找什麼 |
|---|---|
| 輸入/公式分離 | 輸入和計算有明確分開嗎？ |
| 顏色慣例 | 藍色=輸入、黑色=公式、綠色=連結，或模型自己用的那套，有一致套用嗎？ |
| 分頁流向 | 順序合乎邏輯嗎（Assumptions → IS → BS → CF → Valuation）？ |
| 日期標題 | 所有分頁都一致嗎？ |
| 單位 | 一致嗎（千 vs 百萬 vs 實際數）？ |

### 3b. 資產負債表

| 檢查 | 測試 |
|---|---|
| BS 平衡 | Total Assets = Total Liabilities + Equity（每一期） |
| RE 滾動結轉 | 前期 RE + Net Income − 股利 = 本期 RE |
| 商譽/無形資產 | 由收購假設帶出（如果有併購） |

如果 BS 不平衡，**量化每一期的差額，並追蹤是在哪裡斷掉的**，這個沒修好之前，其他都不重要。

### 3c. 現金流量表

| 檢查 | 測試 |
|---|---|
| 現金勾稽 | CF 期末現金 = BS 現金（每一期） |
| CF 加總 | CFO + CFI + CFF = Δ 現金 |
| D&A 一致 | CF 上的 D&A = IS 上的 D&A |
| CapEx 一致 | CF 上的 CapEx 跟 BS 上的 PP&E 滾動結轉一致 |
| 營運資金變動 | 正負號跟 BS 的變動一致（ΔAR、ΔAP、ΔInventory） |

### 3d. 損益表

| 檢查 | 測試 |
|---|---|
| 營收組成 | 跟各部門/產品的明細勾稽得上 |
| 稅 | 稅額費用 = 稅前利益 × 稅率（允許遞延稅項調整） |
| 股數 | 跟稀釋明細表勾稽得上（選擇權、可轉債、庫藏股） |

### 3e. 循環參照

- 利息 → 債務餘額 → 現金 → 利息，是 LBO/三表模型裡常見的刻意循環
- 如果是刻意的：確認有迭代開關，而且能正常運作
- 如果不是刻意的：追蹤這個迴圈，並標出該怎麼打斷它

### 3f. 邏輯與合理性

| 檢查 | 出現以下情況就標記 |
|---|---|
| 成長率 | 營收成長 >100% 卻沒有說明 |
| 利潤率 | 超出產業常態 |
| 終值（Terminal Value）占比過高 | TV > DCF EV 的約 75%（黃旗） |
| 曲棍球棒形 | 預測在後面幾年不切實際地暴增 |
| 複利效應 | EBITDA 複利到第 10 年變成荒謬的金額 |
| 邊界情況 | 模型在 0% 或負成長、EBITDA 為負時壞掉，或槓桿變成負值 |

### 3g. 各模型類型特有的 bug

**DCF：**
- 折現率套用在錯誤的期間（年中 vs 年底）
- 終值沒有折現回來
- WACC 用帳面價值而不是市場價值
- FCF 包含利息費用（應該是無槓桿的）
- 稅盾被重複計算

**LBO：**
- 還債金額跟現金掃款（cash sweep）機制對不上
- PIK 利息沒有計入本金
- 管理層的 rollover 沒有反映在報酬裡
- 出場倍數套在錯誤的 EBITDA 上（LTM vs NTM）
- 費用/開支沒有從第 1 天的權益扣掉

**併購：**
- 增益/稀釋用了錯誤的股數（交易前 vs 交易後）
- 綜效沒有分階段計入
- 購買價格分攤（PPA）不平衡
- 沒有計入放棄的現金利息
- 交易費用沒有放進資金來源與用途（sources & uses）

**三表：**
- 營運資金變動的正負號錯誤
- 折舊跟 PP&E 明細表對不上
- 債務到期明細跟本金償還對不上
- 股利超過淨利卻沒有說明

---

## 步驟 4：報告

輸出一張發現事項表：

| # | 工作表 | 儲存格/範圍 | 嚴重度 | 類別 | 問題 | 建議修正 |
|---|---|---|---|---|---|---|

**嚴重度：**
- **Critical**：輸出是錯的（BS 不平衡、公式壞掉、現金勾稽不上）
- **Warning**：有風險（寫死的值、不一致的公式、邊界情況會失敗）
- **Info**：風格/最佳實務（顏色標示、版面、命名）

對 **model** 範圍，在最前面加一行摘要：

> Model type: [DCF/LBO/3-stmt/...] — Overall: [Clean / Minor Issues / Major Issues] — [N] critical, [N] warnings, [N] info

**沒有先問過，不要改任何東西**，先報告，使用者要求才修。

---

## 備註

- **先看 BS 平不平衡**：如果不平衡，所有下游的東西都值得懷疑
- **寫死的覆蓋值是悄悄出現的 bug 的頭號來源**：要積極搜尋
- **正負號慣例的錯誤**（現金流出用正還是負）非常常見
- 如果模型用了 VBA macro，要註明哪些由 macro 驅動的計算，無法單靠公式來稽核

---
name: audit-xls
description: Audit a spreadsheet for formula accuracy, errors, and common mistakes. Scopes to a selected range, a single sheet, or the entire model (including financial-model integrity checks like BS balance, cash tie-out, and logic sanity). Triggers on "audit this sheet", "check my formulas", "find formula errors", "QA this spreadsheet", "sanity check this", "debug model", "model check", "model won't balance", "something's off in my model", "model review".
---

# Audit Spreadsheet

Audit formulas and data for accuracy and mistakes. Scope determines depth — from quick formula checks on a selection up to full financial-model integrity audits.

## Step 1: Determine scope

If the user already gave a scope, use it. Otherwise **ask them**:

> What scope do you want me to audit?
> - **selection** — just the currently selected range
> - **sheet** — the current active sheet only
> - **model** — the whole workbook, including financial-model integrity checks (BS balance, cash tie-out, roll-forwards, logic sanity)

The **model** scope is the deepest — use it for DCF, LBO, 3-statement, merger, comps, or any integrated financial model before sending to a client or IC.

---

## Step 2: Formula-level checks (ALL scopes)

Run these regardless of scope:

| 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 in a row/column |
| 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 a hardcoded value |
| Circular references | Intentional or accidental |
| Broken cross-sheet links | References to cells that moved or were deleted |
| Unit/scale mismatches | Thousands mixed with millions, % stored as whole numbers |
| Hidden rows/tabs | Could contain overrides or stale calculations |

---

## Step 3: Model-integrity checks (MODEL scope only)

If scope is **model**, identify the model type (DCF / LBO / 3-statement / merger / comps / custom) and run the appropriate integrity checks below.

### 3a. Structural review

| Check | What to look for |
|---|---|
| Input/formula separation | Are inputs clearly separated from calculations? |
| Color convention | Blue=input, black=formula, green=link — or whatever the model uses, applied consistently? |
| Tab flow | Logical order (Assumptions → IS → BS → CF → Valuation)? |
| Date headers | Consistent across all tabs? |
| Units | Consistent (thousands vs millions vs actuals)? |

### 3b. Balance Sheet

| Check | Test |
|---|---|
| BS balances | Total Assets = Total Liabilities + Equity (every period) |
| RE rollforward | Prior RE + Net Income − Dividends = Current RE |
| Goodwill/intangibles | Flow from acquisition assumptions (if M&A) |

If BS doesn't balance, **quantify the gap per period and trace where it breaks** — nothing else matters until this is fixed.

### 3c. Cash Flow Statement

| Check | Test |
|---|---|
| Cash tie-out | CF Ending Cash = BS Cash (every period) |
| CF sums | CFO + CFI + CFF = Δ Cash |
| D&A match | D&A on CF = D&A on IS |
| CapEx match | CapEx on CF matches PP&E rollforward on BS |
| WC changes | Signs match BS movements (ΔAR, ΔAP, ΔInventory) |

### 3d. Income Statement

| Check | Test |
|---|---|
| Revenue build | Ties to segment/product detail |
| Tax | Tax expense = Pre-tax income × tax rate (allow for deferred tax adj) |
| Share count | Ties to dilution schedule (options, converts, buybacks) |

### 3e. Circular references

- Interest → debt balance → cash → interest is a common intentional circ in LBO/3-stmt models
- If intentional: verify iteration toggle exists and works
- If unintentional: trace the loop and flag how to break it

### 3f. Logic & reasonableness

| Check | Flag if |
|---|---|
| Growth rates | >100% revenue growth without explanation |
| Margins | Outside industry norms |
| Terminal value dominance | TV > ~75% of DCF EV (yellow flag) |
| Hockey-stick | Projections ramp unrealistically in out-years |
| Compounding | EBITDA compounds to absurd $ by Year 10 |
| Edge cases | Model breaks at 0% or negative growth, negative EBITDA, leverage goes negative |

### 3g. Model-type-specific bugs

**DCF:**
- Discount rate applied to wrong period (mid-year vs end-of-year)
- Terminal value not discounted back
- WACC uses book values instead of market values
- FCF includes interest expense (should be unlevered)
- Tax shield double-counted

**LBO:**
- Debt paydown doesn't match cash sweep mechanics
- PIK interest not accruing to principal
- Management rollover not reflected in returns
- Exit multiple applied to wrong EBITDA (LTM vs NTM)
- Fees/expenses not deducted from Day 1 equity

**Merger:**
- Accretion/dilution uses wrong share count (pre- vs post-deal)
- Synergies not phased in
- Purchase price allocation doesn't balance
- Foregone interest on cash not included
- Transaction fees not in sources & uses

**3-statement:**
- Working capital changes have wrong sign
- Depreciation doesn't match PP&E schedule
- Debt maturity schedule doesn't match principal payments
- Dividends exceed net income without explanation

---

## Step 4: Report

Output a findings table:

| # | Sheet | Cell/Range | Severity | Category | Issue | Suggested Fix |
|---|---|---|---|---|---|---|

**Severity:**
- **Critical** — wrong output (BS doesn't balance, formula broken, cash doesn't tie)
- **Warning** — risky (hardcodes, inconsistent formulas, edge-case failures)
- **Info** — style/best-practice (color coding, layout, naming)

For **model** scope, prepend a summary line:

> Model type: [DCF/LBO/3-stmt/...] — Overall: [Clean / Minor Issues / Major Issues] — [N] critical, [N] warnings, [N] info

**Don't change anything without asking** — report first, fix on request.

---

## Notes

- **BS balance first** — if it doesn't balance, everything downstream is suspect
- **Hardcoded overrides are the #1 source of silent bugs** — search aggressively
- **Sign convention errors** (positive vs negative for cash outflows) are extremely common
- If the model uses VBA macros, note any macro-driven calculations that can't be audited from formulas alone