Back to Details

audit-xls

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

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

步骤1：确定审计范围

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.
如果用户已指定范围，则直接使用；否则询问用户：
你希望我按照什么范围进行审计？
  • selection — 仅当前选定区域
  • sheet — 仅当前活动工作表
  • model — 整个工作簿，包括财务模型完整性检查（BS平衡、cash tie-out、数据结转、逻辑合理性）
model范围是最全面的——适用于DCF、LBO、3-statement、merger、comps或任何集成财务模型，在发送给客户或内部人员前使用。

Step 2: Formula-level checks (ALL scopes)

步骤2：公式级检查（所有范围通用）

Run these regardless of scope:
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 in a row/column
Off-by-one ranges
SUM
/
AVERAGE
that misses the first or last row
Pasted-over formulasCell that looks like a formula but is actually a hardcoded value
Circular referencesIntentional or accidental
Broken cross-sheet linksReferences to cells that moved or were deleted
Unit/scale mismatchesThousands mixed with millions, % stored as whole numbers
Hidden rows/tabsCould contain overrides or stale calculations
无论选择哪种范围，都需执行以下检查：
检查项检查内容
公式错误
#REF!
, 
#VALUE!
, 
#N/A
, 
#DIV/0!
, 
#NAME?
公式中的硬编码
=A1*1.05
— 其中
1.05
应替换为单元格引用
公式不一致某单元格公式打破了所在行/列的公式模式
范围偏移错误
SUM
/
AVERAGE
函数遗漏首行或末行
被覆盖的公式看似公式但实际为硬编码值的单元格
循环引用有意或无意产生的循环引用
跨表链接失效引用已移动或删除的单元格
单位/规模不匹配千位与百万位混用、百分比以整数形式存储
隐藏行/工作表可能包含覆盖值或过时计算

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

步骤3：模型完整性检查（仅MODEL范围）

If scope is model, identify the model type (DCF / LBO / 3-statement / merger / comps / custom) and run the appropriate integrity checks below.
若范围为model，先识别模型类型（DCF / LBO / 3-statement / merger / comps / 自定义），再执行相应的完整性检查。

3a. Structural review

3a. 结构审查

CheckWhat to look for
Input/formula separationAre inputs clearly separated from calculations?
Color conventionBlue=input, black=formula, green=link — or whatever the model uses, applied consistently?
Tab flowLogical order (Assumptions → IS → BS → CF → Valuation)?
Date headersConsistent across all tabs?
UnitsConsistent (thousands vs millions vs actuals)?
检查项检查内容
输入与公式分离输入项是否与计算项明确分离？
颜色规范是否遵循模型约定的颜色规则（如蓝色=输入、黑色=公式、绿色=链接）且应用一致？
工作表顺序是否符合逻辑顺序（假设表 → IS → BS → CF → 估值表）？
日期表头所有工作表的日期表头是否一致？
单位单位是否统一（千位/百万位/实际值）？

3b. Balance Sheet

3b. 资产负债表（BS）

CheckTest
BS balancesTotal Assets = Total Liabilities + Equity (every period)
RE rollforwardPrior RE + Net Income − Dividends = Current RE
Goodwill/intangiblesFlow 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.
检查项验证方式
BS平衡各期总资产 = 总负债 + 所有者权益
留存收益结转上期留存收益 + 净利润 − 股息 = 本期留存收益
商誉/无形资产若涉及并购，是否与收购假设一致？
若BS不平衡，量化各期差异并追踪断点——此问题修复前，其他检查均无意义。

3c. Cash Flow Statement

3c. 现金流量表（CF）

CheckTest
Cash tie-outCF Ending Cash = BS Cash (every period)
CF sumsCFO + CFI + CFF = Δ Cash
D&A matchD&A on CF = D&A on IS
CapEx matchCapEx on CF matches PP&E rollforward on BS
WC changesSigns match BS movements (ΔAR, ΔAP, ΔInventory)
检查项验证方式
Cash tie-outCF期末现金 = BS现金（各期一致）
CF求和经营活动现金流 + 投资活动现金流 + 筹资活动现金流 = 现金变动额
折旧摊销匹配CF中的折旧摊销 = IS中的折旧摊销
资本支出匹配CF中的资本支出与BS中的PP&E结转一致
营运资本变动符号与BS变动一致（应收账款变动、应付账款变动、存货变动）

3d. Income Statement

3d. 利润表（IS）

CheckTest
Revenue buildTies to segment/product detail
TaxTax expense = Pre-tax income × tax rate (allow for deferred tax adj)
Share countTies to dilution schedule (options, converts, buybacks)
检查项验证方式
收入构成与细分业务/产品明细匹配
税费税费支出 = 税前利润 × 税率（允许递延税调整）
股份数量与稀释计划表（期权、可转换债券、回购）一致

3e. Circular references

3e. 循环引用

  • 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
  • 利息 → 债务余额 → 现金 → 利息是LBO/三表模型中常见的有意循环引用
  • 若为有意循环：确认迭代开关已开启且正常工作
  • 若为无意循环：追踪循环路径并标记修复方法

3f. Logic & reasonableness

3f. 逻辑与合理性

CheckFlag if
Growth rates>100% revenue growth without explanation
MarginsOutside industry norms
Terminal value dominanceTV > ~75% of DCF EV (yellow flag)
Hockey-stickProjections ramp unrealistically in out-years
CompoundingEBITDA compounds to absurd $ by Year 10
Edge casesModel breaks at 0% or negative growth, negative EBITDA, leverage goes negative
检查项需标记的情况
增长率收入增长率>100%且无合理解释
利润率超出行业正常范围
终值占比过高终值占DCF企业价值的比例>约75%（黄色预警）
突增式增长远期预测呈不切实际的爆发式增长
复利异常第10年EBITDA复利增长至不合理数值
极端场景模型在0%/负增长、负EBITDA、杠杆为负时失效

3g. Model-type-specific bugs

3g. 特定模型类型的问题

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
DCF：
  • 折现率应用于错误期间（年中 vs 年末）
  • 终值未折现
  • WACC使用账面价值而非市场价值
  • 自由现金流包含利息支出（应为无杠杆现金流）
  • 税盾重复计算
LBO：
  • 债务偿还与现金扫尾机制不一致
  • PIK利息未计入本金
  • 管理层滚动投资未体现在回报中
  • 退出倍数应用于错误的EBITDA（LTM vs NTM）
  • 费用未从首日权益中扣除
Merger：
  • 摊薄/增厚计算使用错误的股份数量（交易前 vs 交易后）
  • 协同效应未分阶段实现
  • 购买价格分配不平衡
  • 未包含现金的机会成本
  • 交易费用未纳入资金来源与用途
3-statement：
  • 营运资本变动符号错误
  • 折旧与PP&E计划表不一致
  • 债务到期时间表与本金偿还不一致
  • 股息超过净利润且无合理解释

Step 4: Report

步骤4：生成报告

Output a findings table:
#SheetCell/RangeSeverityCategoryIssueSuggested 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.
输出问题发现表格：
序号工作表单元格/区域严重程度类别问题描述建议修复方案
严重程度定义：
  • Critical（严重） — 输出错误（BS不平衡、公式失效、现金不匹配）
  • Warning（警告） — 存在风险（硬编码、公式不一致、极端场景失效）
  • Info（提示） — 风格/最佳实践问题（颜色编码、布局、命名）
若为model范围，需在报告前添加摘要：
模型类型：[DCF/LBO/三表/...] — 整体状态：[无问题 / 轻微问题 / 重大问题] — [N]个严重问题，[N]个警告，[N]个提示
未经询问不得修改任何内容——先提交报告，按需进行修复。

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
  • 优先检查BS平衡 — 若BS不平衡，后续所有结果均不可信
  • 硬编码覆盖是静默错误的首要来源 — 需重点排查
  • 符号约定错误（现金流出的正负标记）极为常见
  • 若模型使用VBA宏，需标记无法仅通过公式审计的宏驱动计算