3-statement-model

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

3-Statement Financial Model Template Completion

三表财务模型模板补全

Complete and populate integrated financial model templates with proper linkages between Income Statement, Balance Sheet, and Cash Flow Statement.

完成并填充整合的财务模型模板，确保Income Statement、Balance Sheet和Cash Flow Statement之间的关联正确。

⚠️ CRITICAL PRINCIPLES — Read Before Populating Any Template

⚠️ 关键原则 — 填充任何模板前请阅读

Environment — Office JS vs Python:

If running inside Excel (Office Add-in / Office JS): Use Office JS directly. Write formulas via
```
range.formulas = [["=D14*(1+Assumptions!$B$5)"]]
```
— never
```
range.values
```
for derived cells. No separate recalc; Excel computes natively. Use
```
context.workbook.worksheets.getItem(...)
```
to navigate tabs.
If generating a standalone .xlsx file: Use Python/openpyxl. Write
```
ws["D15"] = "=D14*(1+Assumptions!$B$5)"
```
, then run
```
recalc.py
```
before delivery.
Office JS merged cell pitfall: Do NOT call
```
.merge()
```
then set
```
.values
```
on the merged range — throws
```
InvalidArgument
```
because the range still reports its pre-merge dimensions. Instead write value to top-left cell alone, then merge + format the full range:
```
ws.getRange("A1").values = [["INCOME STATEMENT"]]; const h = ws.getRange("A1:G1"); h.merge(); h.format.fill.color = "#1F4E79";
```
All principles below apply identically in either environment.

Formulas over hardcodes (non-negotiable):

Every projection cell, roll-forward, linkage, and subtotal MUST be an Excel formula — never a pre-computed value
When using Python/openpyxl: write formula strings (
```
ws["D15"] = "=D14*(1+Assumptions!$B$5)"
```
), NOT computed results (
```
ws["D15"] = 12500
```
)
The ONLY cells that should contain hardcoded numbers are: (1) historical actuals, (2) assumption drivers in the Assumptions tab
If you find yourself computing a value in Python and writing the result to a cell — STOP. Write the formula instead.
Why: the model must flex when scenarios toggle or assumptions change. Hardcodes break every downstream integrity check silently.

Verify step-by-step with the user:

After mapping the template → show the user which tabs/sections you've identified and confirm before touching any cells
After populating historicals → show the user the historical block and confirm values/periods match source data
After building IS projections → run the subtotal checks, show the user the projected IS, confirm before moving to BS
After building BS → show the user the balance check (Assets = L+E) for every period, confirm before moving to CF
After building CF → show the user the cash tie-out (CF ending cash = BS cash), confirm before finalizing
Do NOT populate the entire model end-to-end and present it complete — break at each statement, show the work, catch errors early

环境选择 — Office JS vs Python:

如果在Excel内运行（Office 插件 / Office JS）： 直接使用Office JS。通过
```
range.formulas = [["=D14*(1+Assumptions!$B$5)"]]
```
编写公式——推导单元格绝不能使用
```
range.values
```
。无需单独重新计算；Excel会自动计算。使用
```
context.workbook.worksheets.getItem(...)
```
切换工作表。
如果生成独立.xlsx文件： 使用Python/openpyxl。编写
```
ws["D15"] = "=D14*(1+Assumptions!$B$5)"
```
，交付前运行
```
recalc.py
```
。
Office JS合并单元格陷阱： 不要先调用
```
.merge()
```
再为合并区域设置
```
.values
```
——这会抛出
```
InvalidArgument
```
错误，因为区域仍会报告合并前的尺寸。应先仅向左上单元格写入值，再合并并格式化整个区域：
```
ws.getRange("A1").values = [["INCOME STATEMENT"]]; const h = ws.getRange("A1:G1"); h.merge(); h.format.fill.color = "#1F4E79";
```
以下所有原则在两种环境下均适用。

优先使用公式而非硬编码（不可协商）：

每个预测单元格、滚动结转、关联和小计都必须是Excel公式——绝不能是预先计算的值
使用Python/openpyxl时：编写公式字符串（
```
ws["D15"] = "=D14*(1+Assumptions!$B$5)"
```
），而非计算结果（
```
ws["D15"] = 12500
```
）
唯一应包含硬编码数字的单元格是：(1) 历史实际值，(2) Assumptions工作表中的假设驱动因素
如果您正在Python中计算值并将结果写入单元格——请停止。改用公式。
原因：当场景切换或假设变更时，模型必须能灵活调整。硬编码会静默破坏所有下游完整性检查。

与用户逐步验证：

完成模板映射后 → 向用户展示您识别出的工作表/章节，确认后再操作任何单元格
填充历史数据后 → 向用户展示历史数据块，确认数值/期间与源数据匹配
构建IS预测后 → 运行小计检查，向用户展示预测的IS，确认后再转向BS
构建BS后 → 向用户展示每个期间的平衡检查（资产=负债+权益），确认后再转向CF
构建CF后 → 向用户展示现金核对（CF期末现金=BS现金），确认后再定稿
不要一次性填充整个模型并直接展示完成版本 —— 在每个报表阶段暂停，展示工作内容，尽早发现错误

Formatting — Professional Blue/Grey Palette (Default unless template/user specifies otherwise)

格式设置 — 专业蓝灰调色板（默认，除非模板/用户另有指定）

Keep colors minimal. Use only blues and greys for cell fills. Do NOT introduce greens, yellows, oranges, or multiple accent colors — a clean model uses restraint.

Element	Fill	Font
Section headers (IS / BS / CF titles)	Dark blue `#1F4E79`	White bold
Column headers (FY2024A, FY2025E, etc.)	Light blue `#D9E1F2`	Black bold
Input cells (historicals, assumption drivers)	Light grey `#F2F2F2` or white	Blue `#0000FF`
Formula cells	White	Black
Cross-tab links	White	Green `#008000`
Check rows / key totals	Medium blue `#BDD7EE`	Black bold

That's 3 blues + 1 grey + white. If the template has its own color scheme, follow the template instead.

Font color signals what a cell is (input/formula/link). Fill color signals where you are (header/data/check).

尽量减少颜色使用。仅使用蓝色和灰色填充单元格。不要使用绿色、黄色、橙色或多种强调色——简洁的模型需克制用色。

元素	填充色	字体
章节标题（IS/BS/CF标题）	深蓝色 `#1F4E79`	白色粗体
列标题（FY2024A、FY2025E等）	浅蓝色 `#D9E1F2`	黑色粗体
输入单元格（历史数据、假设驱动因素）	浅灰色 `#F2F2F2` 或白色	蓝色 `#0000FF`
公式单元格	白色	黑色
跨工作表关联	白色	绿色 `#008000`
检查行/关键总计	中蓝色 `#BDD7EE`	黑色粗体

即3种蓝色+1种灰色+白色。如果模板有自己的配色方案，请遵循模板设置。

字体颜色表示单元格类型（输入/公式/关联）。填充颜色表示所在位置（标题/数据/检查）。

Model Structure

模型结构

Identifying Template Tab Organization

识别模板工作表组织

Templates vary in their tab naming conventions and organization. Before populating, review all tabs to understand the template's structure. Below are common tab names and their typical contents:

Common Tab Names	Contents to Look For
IS, P&L, Income Statement	Income Statement
BS, Balance Sheet	Balance Sheet
CF, CFS, Cash Flow	Cash Flow Statement
WC, Working Capital	Working Capital Schedule
DA, D&A, Depreciation, PP&E	Depreciation & Amortization Schedule
Debt, Debt Schedule	Debt Schedule
NOL, Tax, DTA	Net Operating Loss Schedule
Assumptions, Inputs, Drivers	Driver assumptions and inputs
Checks, Audit, Validation	Error-checking dashboard

Template Review Checklist

Identify which tabs exist in the template (not all templates include every schedule)
Note any template-specific tabs not listed above
Understand tab dependencies (e.g., which schedules feed into the main statements)
Locate input cells vs. formula cells on each tab

模板的工作表命名规则和组织方式各不相同。填充前，请查看所有工作表以了解模板结构。以下是常见的工作表名称及其典型内容：

常见工作表名称	需查找的内容
IS、P&L、Income Statement	Income Statement
BS、Balance Sheet	Balance Sheet
CF、CFS、Cash Flow	Cash Flow Statement
WC、Working Capital	营运资金明细表
DA、D&A、Depreciation、PP&E	折旧与摊销明细表
Debt、Debt Schedule	债务明细表
NOL、Tax、DTA	净营业亏损明细表
Assumptions、Inputs、Drivers	驱动因素假设和输入值
Checks、Audit、Validation	错误检查仪表板

模板检查清单

识别模板中存在的工作表（并非所有模板都包含所有明细表）
记录上述未列出的模板专属工作表
了解工作表依赖关系（例如哪些明细表为主要报表提供数据）
定位每个工作表上的输入单元格与公式单元格

Understanding Template Structure

理解模板结构

Before populating a template, familiarize yourself with its existing layout to ensure data is entered in the correct locations and formulas remain intact.

Identifying Row Structure

Locate the model title at top of each tab
Identify section headers and their visual separation
Find the units row indicating $ millions, %, x, etc.
Note column headers distinguishing Actuals vs. Estimates periods
Confirm period labels (e.g., FY2024A, FY2025E)
Identify input cells vs. formula cells (typically distinguished by font color)

Identifying Column Structure

Confirm line item labels in leftmost column
Verify historical years precede projection years
Note the visual border separating historical from projected periods
Check for consistent column order across all tabs

Working with Named Ranges Templates often use named ranges for key inputs and outputs. Before entering data:

Review existing named ranges in the template (Formulas → Name Manager in Excel)
Common named ranges include: Revenue growth rates, cost percentages, key outputs (Net Income, EBITDA, Total Debt, Cash), scenario selector cell
Ensure inputs are entered in cells that feed into these named ranges

填充模板前，请熟悉其现有布局，确保数据输入到正确位置且公式保持完整。

识别行结构

定位每个工作表顶部的模型标题
识别章节标题及其视觉分隔方式
找到表示单位的行（如百万美元、%、倍数等）
注意区分实际值与预测值期间的列标题
确认期间标签（如FY2024A、FY2025E）
识别输入单元格与公式单元格（通常通过字体颜色区分）

识别列结构

确认最左侧列的行项目标签
验证历史年份在预测年份之前
注意分隔历史与预测期间的视觉边框
检查所有工作表的列顺序是否一致

处理命名区域 模板通常为关键输入和输出使用命名区域。输入数据前：

查看模板中的现有命名区域（Excel中依次点击「公式」→「名称管理器」）
常见命名区域包括：收入增长率、成本百分比、关键输出（净利润、EBITDA、总债务、现金）、场景选择器单元格
确保输入值输入到为这些命名区域提供数据的单元格中

Projection Period

预测期间

Templates typically project 5 years forward from last historical year
Verify historical (A) vs. projected (E) columns are clearly separated
Confirm columns use fiscal year notation (e.g., FY2024A, FY2025E)

模板通常从最后一个历史年份起向前预测5年
验证历史（A）与预测（E）列已明确分隔
确认列使用财年标记（如FY2024A、FY2025E）

Margin Analysis

利润率分析

Note: The following margin analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

Calculate and display profitability margins on the Income Statement (IS) tab to track operational efficiency and enable peer comparison.

注意：仅当用户要求或模板明确需要时，才执行以下利润率分析。若无要求，请跳过本节。

在Income Statement工作表上计算并显示利润率，以跟踪运营效率并支持同业比较。

Core Margins to Include

需包含的核心利润率

Margin	Formula	What It Measures
Gross Margin	Gross Profit / Revenue	Pricing power, production efficiency
EBITDA Margin	EBITDA / Revenue	Core operating profitability
EBIT Margin	EBIT / Revenue	Operating profitability after D&A
Net Income Margin	Net Income / Revenue	Bottom-line profitability

利润率	公式	衡量指标
毛利率	毛利润 / 收入	定价能力、生产效率
EBITDA利润率	EBITDA / 收入	核心运营盈利能力
EBIT利润率	EBIT / 收入	扣除折旧摊销后的运营盈利能力
净利润率	净利润 / 收入	最终盈利能力

Income Statement Layout with Margins

包含利润率的Income Statement布局

Display margin percentages directly below each profit line item:

Gross Margin % below Gross Profit
EBIT Margin % below EBIT
EBITDA Margin % below EBITDA
Net Income Margin % below Net Income

在每个利润行项目下方直接显示利润率百分比：

毛利润下方显示毛利率%
EBIT下方显示EBIT利润率%
EBITDA下方显示EBITDA利润率%
净利润下方显示净利润率%

Credit Metrics

信用指标

Note: The following Credit analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

Calculate and display credit/leverage metrics on the Balance Sheet (BS) tab to assess financial health, debt capacity, and covenant compliance.

注意：仅当用户要求或模板明确需要时，才执行以下信用分析。若无要求，请跳过本节。

在Balance Sheet工作表上计算并显示信用/杠杆指标，以评估财务健康状况、债务能力和契约合规性。

Core Credit Metrics to Include

需包含的核心信用指标

Metric	Formula	What It Measures
Total Debt / EBITDA	Total Debt / LTM EBITDA	Leverage multiple
Net Debt / EBITDA	(Total Debt - Cash) / LTM EBITDA	Leverage net of cash
Interest Coverage	EBITDA / Interest Expense	Ability to service debt
Debt / Total Cap	Total Debt / (Total Debt + Equity)	Capital structure
Debt / Equity	Total Debt / Total Equity	Financial leverage
Current Ratio	Current Assets / Current Liabilities	Short-term liquidity
Quick Ratio	(Current Assets - Inventory) / Current Liabilities	Immediate liquidity

指标	公式	衡量指标
总债务/EBITDA	总债务 / LTM EBITDA	杠杆倍数
净债务/EBITDA	(总债务 - 现金) / LTM EBITDA	扣除现金后的杠杆倍数
利息保障倍数	EBITDA / 利息支出	偿债能力
债务/总资本	总债务 / (总债务 + 权益)	资本结构
债务/权益	总债务 / 总权益	财务杠杆
流动比率	流动资产 / 流动负债	短期流动性
速动比率	(流动资产 - 存货) / 流动负债	即时流动性

Credit Metric Hierarchy Checks

信用指标层级检查

Validate that Upside shows strongest credit profile:

Leverage: Upside < Base < Downside (lower is better)
Coverage: Upside > Base > Downside (higher is better)
Liquidity: Upside > Base > Downside (higher is better)

验证乐观场景显示最强信用状况：

杠杆率：乐观场景 < 基准场景 < 悲观场景（越低越好）
保障倍数：乐观场景 > 基准场景 > 悲观场景（越高越好）
流动性：乐观场景 > 基准场景 > 悲观场景（越高越好）

Covenant Compliance Tracking

契约合规跟踪

If debt covenants are known, add explicit compliance checks comparing actual metrics to covenant thresholds.

若已知债务契约，添加明确的合规检查，对比实际指标与契约阈值。

Scenario Analysis (Base / Upside / Downside)

场景分析（基准/乐观/悲观）

Use a scenario toggle (dropdown) in the Assumptions tab with CHOOSE or INDEX/MATCH formulas.

Scenario	Description
Base Case	Management guidance or consensus estimates
Upside Case	Above-guidance growth, margin expansion
Downside Case	Below-trend growth, margin compression

Key Drivers to Sensitize: Revenue growth, Gross margin, SG&A %, DSO/DIO/DPO, CapEx %, Interest rate, Tax rate.

Scenario Audit Checks: Toggle switches all statements, BS balances in all scenarios, Cash ties out, Hierarchy holds (Upside > Base > Downside for NI, EBITDA, FCF, margins).

在Assumptions工作表中使用场景切换器（下拉菜单），搭配CHOOSE或INDEX/MATCH公式。

场景	描述
基准场景	管理层指引或市场一致预期
乐观场景	超指引增长、利润率扩张
悲观场景	低于趋势增长、利润率压缩

需敏感性分析的关键驱动因素：收入增长率、毛利率、销售及管理费用率、DSO/DIO/DPO、资本支出率、利率、税率。

场景审计检查：切换开关可作用于所有报表、所有场景下BS平衡、现金核对无误、层级关系成立（净利润、EBITDA、自由现金流、利润率：乐观场景 > 基准场景 > 悲观场景）。

SEC Filings Data Extraction

SEC申报数据提取

If the template specifically requires pulling data from SEC filings (10-K, 10-Q), see references/sec-filings.md for detailed extraction guidance. This reference is only needed when populating templates with public company data from regulatory filings.

若模板明确要求从SEC申报文件（10-K、10-Q）提取数据，请参阅references/sec-filings.md获取详细提取指南。仅当使用监管申报文件中的上市公司数据填充模板时，才需参考此文档。

Completing Model Templates

完成模型模板

This section provides general guidance for completing any 3-statement financial model template while preserving existing formulas and ensuring data integrity.

本节提供完善任何三表财务模型模板的通用指南，同时保留现有公式并确保数据完整性。

Step 1: Analyze the Template Structure

步骤1：分析模板结构

Before entering any data, thoroughly review the template to understand its architecture:

Identify Input vs. Formula Cells

Look for visual cues (font color, cell shading) that distinguish input cells from formula cells
Common conventions: Blue font = inputs, Black font = formulas, Green font = links to other sheets
Use Excel's Trace Precedents/Dependents (Formulas → Trace Precedents) to understand cell relationships
Check for named ranges that may control key inputs (Formulas → Name Manager)

Map the Template's Flow

Identify which tabs feed into others (e.g., Assumptions → IS → BS → CF)
Note any supporting schedules and their linkages to main statements
Document the template's specific line items and structure before populating

输入任何数据前，请全面审查模板以了解其架构：

识别输入单元格与公式单元格

寻找区分输入单元格与公式单元格的视觉线索（字体颜色、单元格底纹）
常见惯例：蓝色字体=输入值，黑色字体=公式，绿色字体=关联至其他工作表
使用Excel的「追踪引用单元格/从属单元格」（依次点击「公式」→「追踪引用单元格」）了解单元格关系
检查可能控制关键输入的命名区域（依次点击「公式」→「名称管理器」）

梳理模板数据流

识别哪些工作表为其他工作表提供数据（例如Assumptions → IS → BS → CF）
记录任何支持性明细表及其与主报表的关联
填充前记录模板的特定行项目和结构

Step 2: Filling in Data Without Breaking Formulas

步骤2：填充数据且不破坏公式

Golden Rules for Data Entry

Rule	Description
Only edit input cells	Never overwrite cells containing formulas unless intentionally replacing the formula
Preserve cell references	When copying data, use Paste Values (Ctrl+Shift+V) to avoid overwriting formulas with source formatting
Match the template's units	Verify if template uses thousands, millions, or actual values before entering data
Respect sign conventions	Follow the template's existing sign convention (e.g., expenses as positive or negative)
Check for circular references	If the template uses iterative calculations, ensure Enable Iterative Calculation is turned on

Safe Data Entry Process

Identify the exact cells designated for input (usually highlighted or labeled)
Enter historical data first, then verify formulas are calculating correctly for those periods
Enter assumption drivers that feed forecast calculations
Review calculated outputs to confirm formulas are working as intended
If a formula cell must be modified, document the original formula before making changes

Handling Pre-Built Formulas

If formulas reference cells you haven't populated yet, expect temporary errors (#REF!, #DIV/0!) until all inputs are complete
When formulas produce unexpected results, trace precedents to identify missing or incorrect inputs
Never delete rows/columns without checking for formula dependencies across all tabs

数据输入黄金法则

规则	描述
仅编辑输入单元格	除非有意替换公式，否则绝不要覆盖包含公式的单元格
保留单元格引用	复制数据时，使用「粘贴值」（Ctrl+Shift+V）避免用源格式覆盖公式
匹配模板单位	输入数据前验证模板使用千、百万还是实际值
遵循符号惯例	遵循模板现有符号惯例（例如费用为正或负）
检查循环引用	若模板使用迭代计算，请确保已开启「启用迭代计算」

安全数据输入流程

确定指定用于输入的精确单元格（通常带有高亮或标签）
先输入历史数据，然后验证公式在这些期间的计算是否正确
输入为预测计算提供数据的假设驱动因素
查看计算输出以确认公式按预期运行
若必须修改公式单元格，修改前记录原始公式

处理预构建公式

若公式引用尚未填充的单元格，在完成所有输入前可能会出现临时错误（#REF!、#DIV/0!）
当公式产生意外结果时，追踪引用单元格以识别缺失或错误的输入
删除行/列前务必检查所有工作表的公式依赖关系

Step 3: Validating Formulas

步骤3：验证公式

Formula Integrity Checks

Before relying on template outputs, validate that formulas are functioning correctly:

Check Type	Method
Trace precedents	Select a formula cell → Formulas → Trace Precedents to verify it references correct inputs
Trace dependents	Verify key inputs flow to expected output cells
Evaluate formula	Use Formulas → Evaluate Formula to step through complex calculations
Check for hardcodes	Projection formulas should reference assumptions, not contain hardcoded values
Test with known values	Input simple test values to verify formulas produce expected results
Cross-tab consistency	Ensure the same formula logic applies across all projection periods

Common Formula Issues to Watch For

Mixed absolute/relative references causing incorrect results when copied across periods
Broken links to external files or deleted ranges (#REF! errors)
Division by zero in early periods before revenue ramps (#DIV/0! errors)
Circular reference warnings (may be intentional for interest calculations)
Inconsistent formulas across projection columns (use Ctrl+\ to find differences)

Validating Cross-Tab Linkages

Confirm values that appear on multiple tabs are linked (not duplicated)
Verify schedule totals tie to corresponding line items on main statements
Check that period labels align across all tabs

公式完整性检查

依赖模板输出前，验证公式是否正常运行：

检查类型	方法
追踪引用单元格	选择公式单元格→「公式」→「追踪引用单元格」以验证其引用了正确的输入
追踪从属单元格	验证关键输入流向预期的输出单元格
公式求值	使用「公式」→「公式求值」逐步查看复杂计算过程
检查硬编码	预测公式应引用假设，而非包含硬编码值
使用已知值测试	输入简单测试值以验证公式产生预期结果
跨工作表一致性	确保所有预测期间应用相同的公式逻辑

需注意的常见公式问题

混合绝对/相对引用导致跨期间复制时结果错误
链接至外部文件或已删除区域的链接断裂（#REF!错误）
收入增长前早期期间的除零错误（#DIV/0!错误）
循环引用警告（可能是利息计算的有意设置）
预测列间公式不一致（使用Ctrl+\查找差异）

验证跨工作表关联

确认出现在多个工作表上的值是关联的（而非重复输入）
验证明细表总计与主报表的对应行项目一致
检查所有工作表的期间标签是否对齐

Step 4: Quality Checks by Sheet

步骤4：按工作表执行质量检查

Perform these validation checks on each sheet after populating the template:

Income Statement (IS) Quality Checks

Revenue figures match source data for historical periods
All expense line items sum to reported totals
Subtotals (Gross Profit, EBIT, EBT, Net Income) calculate correctly
Tax calculation logic is appropriate (handles losses correctly)
Forecast drivers reference assumptions tab (no hardcodes)
Period-over-period changes are directionally reasonable

Balance Sheet (BS) Quality Checks

Assets = Liabilities + Equity for every period (primary check)
Cash balance matches Cash Flow Statement ending cash
Working capital accounts tie to supporting schedules (if applicable)
Retained Earnings rolls forward correctly: Prior RE + Net Income - Dividends +/- Adjustments = Ending RE
Debt balances tie to debt schedule (if applicable)
All balance sheet items have appropriate signs (assets positive, most liabilities positive)

Cash Flow Statement (CF) Quality Checks

Net Income at top of CFO matches Income Statement Net Income
Non-cash add-backs (D&A, SBC, etc.) tie to their source schedules/statements
Working capital changes have correct signs (increase in asset = use of cash = negative)
CapEx ties to PP&E schedule or fixed asset roll-forward
Financing activities tie to changes in debt and equity accounts on BS
Ending Cash matches Balance Sheet Cash
Beginning Cash equals prior period Ending Cash

Supporting Schedule Quality Checks

Opening balances equal prior period closing balances
Roll-forward logic is complete (Beginning + Additions - Deductions = Ending)
Schedule totals tie to main statement line items
Assumptions used in calculations match Assumptions tab

填充模板后，对每个工作表执行以下验证检查：

Income Statement（IS）质量检查

历史期间的收入数据与源数据匹配
所有费用行项目合计与报告总计一致
小计（毛利润、EBIT、税前利润、净利润）计算正确
税金计算逻辑恰当（正确处理亏损）
预测驱动因素引用Assumptions工作表（无硬编码）
期间环比变化符合合理趋势

Balance Sheet（BS）质量检查

每个期间的资产=负债+权益（核心检查）
现金余额与Cash Flow Statement期末现金匹配
营运资金账户与支持性明细表一致（若适用）
留存收益滚动结转正确：期初留存收益+净利润-股息+/-调整=期末留存收益
债务余额与债务明细表一致（若适用）
所有资产负债表项目符号恰当（资产为正，大多数负债为正）

Cash Flow Statement（CF）质量检查

CFO顶部的净利润与Income Statement净利润匹配
非现金加回项（折旧摊销、基于股份的薪酬等）与源明细表/报表一致
营运资金变化符号正确（资产增加=现金使用=负数）
资本支出与PP&E明细表或固定资产滚动结转一致
融资活动与BS上的债务和权益账户变化一致
期末现金与Balance Sheet现金匹配
期初现金等于上期期末现金

支持性明细表质量检查

期初余额等于上期期末余额
滚动结转逻辑完整（期初+增加-减少=期末）
明细表总计与主报表行项目一致
计算中使用的假设与Assumptions工作表匹配

Step 5: Cross-Statement Integrity Checks

步骤5：跨报表完整性检查

After validating individual sheets, confirm the three statements are properly integrated:

Check	Formula	Expected Result
Balance Sheet Balance	Assets - Liabilities - Equity	= 0
Cash Tie-Out	CF Ending Cash - BS Cash	= 0
Net Income Link	IS Net Income - CF Starting Net Income	= 0
Retained Earnings	Prior RE + NI - Dividends - BS Ending RE	= 0 (adjust for SBC/other items as needed)

验证单个工作表后，确认三张报表已正确整合：

检查项	公式	预期结果
资产负债表平衡	资产 - 负债 - 权益	= 0
现金核对	CF期末现金 - BS现金	= 0
净利润关联	IS净利润 - CF期初净利润	= 0
留存收益	期初留存收益 + 净利润 - 股息 - BS期末留存收益	= 0（根据股份支付/其他项目调整）

Step 6: Final Review

步骤6：最终审核

Before considering the model complete:

Toggle through all scenarios (if applicable) to verify checks pass in each case
Review all #REF!, #DIV/0!, #VALUE!, and #NAME? errors and resolve or document
Confirm all input cells have been populated (search for placeholder values)
Verify units are consistent across all tabs
Save a clean version before making any additional modifications

确认模型完成前：

切换所有场景（若适用）以验证每个场景下检查均通过
查看所有#REF!、#DIV/0!、#VALUE!和#NAME?错误并解决或记录
确认所有输入单元格已填充（搜索占位符值）
验证所有工作表的单位一致
进行任何额外修改前保存干净版本

Model Validation and Audit

模型验证与审计

This section consolidates all validation checks and audit procedures for completed templates.

本节整合了已完成模板的所有验证检查和审计流程。

Core Linkages (Must Always Hold)

核心关联（必须始终成立）

See references/formulas.md for all formula details.

Check	Formula	Expected Result
Balance Sheet Balance	Assets - Liabilities - Equity	= 0
Cash Tie-Out	CF Ending Cash - BS Cash	= 0
Cash Monthly vs Annual	Closing Cash (Monthly) - Closing Cash (Annual)	= 0
Net Income Link	IS Net Income - CF Starting Net Income	= 0
Retained Earnings	Prior RE + NI + SBC - Dividends - BS Ending RE	= 0
Equity Financing	ΔCommon Stock/APIC (BS) - Equity Issuance (CFF)	= 0
Year 0 Equity	Equity Raised (Year 0) - Beginning Equity Capital (Year 1)	= 0

请参阅references/formulas.md获取所有公式详情。

检查项	公式	预期结果
资产负债表平衡	资产 - 负债 - 权益	= 0
现金核对	CF期末现金 - BS现金	= 0
月度与年度现金	月度期末现金 - 年度期末现金	= 0
净利润关联	IS净利润 - CF期初净利润	= 0
留存收益	期初留存收益 + 净利润 + 股份支付 - 股息 - BS期末留存收益	= 0
股权融资	BS普通股/资本公积变化 - CFF股权发行	= 0
第0年权益	第0年筹集的权益 - 第1年期初权益资本	= 0

Sign Convention Reference

符号惯例参考

Statement	Item	Sign Convention
CFO	D&A, SBC	Positive (add-back)
CFO	ΔAR (increase)	Negative (use of cash)
CFO	ΔAP (increase)	Positive (source of cash)
CFI	CapEx	Negative
CFF	Debt issuance	Positive
CFF	Debt repayments	Negative
CFF	Dividends	Negative

报表	项目	符号惯例
CFO	折旧摊销、股份支付	正（加回）
CFO	应收账款变化（增加）	负（现金使用）
CFO	应付账款变化（增加）	正（现金来源）
CFI	资本支出	负
CFF	债务发行	正
CFF	债务偿还	负
CFF	股息	负

Circular Reference Handling

循环引用处理

Interest expense creates circularity: Interest → Net Income → Cash → Debt Balance → Interest

Enable iterative calculation in Excel: File → Options → Formulas → Enable iterative calculation. Set maximum iterations to 100, maximum change to 0.001. Add a circuit breaker toggle in Assumptions tab.

利息支出会导致循环：利息→净利润→现金→债务余额→利息

在Excel中启用迭代计算：文件→选项→公式→启用迭代计算。设置最大迭代次数为100，最大变化值为0.001。在Assumptions工作表中添加断路器切换开关。

Check Categories

检查类别

Section 1: Currency Consistency

Currency identified and documented in Assumptions
All tabs use consistent currency symbol and scale
Units row matches model currency

Section 2: Balance Sheet Integrity

Assets = Liabilities + Equity (for each period)
Formula: Assets - Liabilities - Equity (must = 0)

Section 3: Cash Flow Integrity

Cash ties to BS (CF Ending Cash = BS Cash)
Cash Monthly vs Annual: Closing Cash (Monthly) = Closing Cash (Annual)
NI ties to IS (CF Net Income = IS Net Income)
D&A ties to schedule
SBC ties to IS
ΔAR, ΔInventory, ΔAP tie to WC schedule
CapEx ties to DA schedule

Section 4: Retained Earnings

RE roll-forward check: Prior RE + NI + SBC - Dividends = Ending RE
Show component breakdown for debugging

Section 5: Working Capital

AR, Inventory, AP tie to BS
DSO, DIO, DPO reasonability checks (flag if outside normal ranges)

Section 6: Debt Schedule

Total Debt ties to BS (Current + LT Debt)
Interest calculation ties to IS

Section 6b: Equity Financing

Equity issuance proceeds tie to BS Common Stock/APIC increase
Cash increase from equity = Equity account increase (must balance)
Equity Raise Tie-Out: ΔCommon Stock/APIC (BS) = Equity Issuance (CFF) (must = 0)
Year 0 Equity Tie-Out: Equity Raised (Year 0) = Beginning Equity Capital (Year 1)

Section 6c: NOL Schedule

Beginning NOL (Year 1 / Formation) = 0 (new business starts with zero NOL)
NOL increases only when EBT < 0 (losses must be realized to generate NOL)
DTA ties to BS (NOL Schedule DTA = BS Deferred Tax Asset)
NOL utilization ≤ 80% of EBT (post-2017 federal limitation)
NOL balance is non-negative (cannot utilize more than available)
NOL generated only when EBT < 0
Tax expense = 0 when taxable income ≤ 0

Section 7: Scenario Hierarchy

Absolute metrics: Upside > Base > Downside (NI, EBITDA, FCF)
Margins: Upside > Base > Downside (GM%, EBITDA%, NI%)
Credit metrics: Upside < Base < Downside for leverage (inverted)

Section 8: Formula Integrity

COGS, S&M, G&A, R&D, SBC driven by % of Revenue (no hardcodes)
Consistent formulas across projection years
No #REF!, #DIV/0!, #VALUE! errors

Section 9: Credit Metric Thresholds

Flag metrics as Green/Yellow/Red based on covenant thresholds
Summary of any red flags

第1节：货币一致性

在Assumptions中识别并记录货币
所有工作表使用一致的货币符号和规模
单位行与模型货币匹配

第2节：资产负债表完整性

每个期间的资产=负债+权益
公式：资产 - 负债 - 权益（必须=0）

第3节：现金流量表完整性

现金与BS匹配（CF期末现金=BS现金）
月度与年度现金：月度期末现金=年度期末现金
净利润与IS匹配（CF净利润=IS净利润）
折旧摊销与明细表匹配
股份支付与IS匹配
应收账款、存货、应付账款变化与营运资金明细表匹配
资本支出与折旧摊销明细表匹配

第4节：留存收益

留存收益滚动结转检查：期初留存收益+净利润+股份支付-股息=期末留存收益
显示组件明细以便调试

第5节：营运资金

应收账款、存货、应付账款与BS匹配
DSO、DIO、DPO合理性检查（超出正常范围时标记）

第6节：债务明细表

总债务与BS匹配（流动+长期债务）
利息计算与IS匹配

第6b节：股权融资

股权发行所得与BS普通股/资本公积增加额匹配
股权带来的现金增加=权益账户增加额（必须平衡）
股权融资核对：BS普通股/资本公积变化 - CFF股权发行（必须=0）
第0年权益核对：第0年筹集的权益=第1年期初权益资本

第6c节：净营业亏损（NOL）明细表

期初NOL（第1年/成立时）=0（新企业初始NOL为0）
仅当税前利润<0时NOL增加（必须实现亏损才能产生NOL）
递延所得税资产（DTA）与BS匹配（NOL明细表DTA=BS递延所得税资产）
NOL使用≤税前利润的80%（2017年后联邦限制）
NOL余额非负（使用额不得超过可用额度）
仅当税前利润<0时产生NOL
应纳税所得额≤0时税金支出=0

第7节：场景层级

绝对指标：乐观场景>基准场景>悲观场景（净利润、EBITDA、自由现金流）
利润率：乐观场景>基准场景>悲观场景（毛利率、EBITDA率、净利润率）
信用指标：杠杆率乐观场景<基准场景<悲观场景（反向）

第8节：公式完整性

销货成本、销售与营销费用、管理费用、研发费用、股份支付由收入百分比驱动（无硬编码）
所有预测年份公式一致
无#REF!、#DIV/0!、#VALUE!错误

第9节：信用指标阈值

根据契约阈值将指标标记为绿/黄/红
汇总任何红色预警

Master Check Formula

主检查公式

Aggregate all section statuses into a single master check:

If all sections pass → "✓ ALL CHECKS PASS"
If any section fails → "✗ ERRORS DETECTED - REVIEW BELOW"

将所有章节状态汇总为单个主检查：

若所有章节通过→"✓ 所有检查通过"
若任何章节失败→"✗ 检测到错误 - 请查看下方"

Quick Debug Workflow

快速调试流程

When Master Status shows errors:

Scroll to find red-highlighted sections
Identify which check category has failures
Navigate to source tab to investigate
Fix the underlying issue
Return to Checks tab to verify resolution

当主状态显示错误时：

滚动查找红色高亮的章节
识别哪个检查类别存在失败项
导航至源工作表调查
修复根本问题
返回检查工作表验证问题已解决