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.

完成并填充整合式财务模型模板，确保利润表（IS）、资产负债表（BS）和现金流量表（CF）之间的关联正确。

⚠️ 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 与 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) 假设表中的驱动假设
若你正通过Python计算值并写入单元格，请立即停止。改用公式。
原因：模型需在场景切换或假设变更时灵活调整。硬编码会悄无声息地破坏所有下游完整性检查。

与用户逐步验证：

分析模板结构后 → 向用户展示你识别出的工作表/板块，确认无误后再操作单元格
填充历史数据后 → 向用户展示历史数据块，确认数值/期间与源数据匹配
构建利润表预测后 → 运行小计检查，向用户展示预测利润表，确认无误后再处理资产负债表
构建资产负债表后 → 向用户展示各期间的平衡检查（资产=负债+权益），确认无误后再处理现金流量表
构建现金流量表后 → 向用户展示现金核对结果（现金流量表期末现金=资产负债表现金），确认无误后再定稿
切勿一次性完成整个模型再交付 —— 拆分到每张报表，分步展示工作，尽早发现错误

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

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

元素	填充色	字体
板块标题（利润表/资产负债表/现金流量表标题）	深蓝色 `#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	利润表
BS、Balance Sheet	资产负债表
CF、CFS、Cash Flow	现金流量表
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.

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

在利润表（IS）工作表中计算并展示盈利能力指标，以跟踪运营效率并支持同业对比。

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

带利润率的利润表布局

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.

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

在资产负债表（BS）工作表中计算并展示信用/杠杆指标，以评估财务健康状况、债务容量和契约合规性。

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	总债务/过去12个月EBITDA	杠杆倍数
净债务/EBITDA	(总债务-现金)/过去12个月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).

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

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

需敏感度分析的核心驱动因素：收入增长率、毛利率、销售及管理费用率、应收账款周转天数/存货周转天数/应付账款周转天数、资本支出率、利率、税率。

场景审计检查：切换场景后所有报表同步更新、各场景下资产负债表平衡、现金核对一致、层级关系成立（净利润、EBITDA、自由现金流、利润率：乐观场景>基准场景>悲观场景）。

SEC Filings Data Extraction

SEC filings数据提取

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 filings（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的「追踪引用单元格/从属单元格」功能（「公式」→「追踪引用单元格」）理解单元格关系
检查可能控制关键输入的命名区域（「公式」→「名称管理器」）

梳理模板数据流

识别工作表间的依赖关系（例如：假设表→利润表→资产负债表→现金流量表）
记录所有支持明细表及其与主报表的关联
填充前先记录模板的特定行项目和结构

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

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

利润表（IS）质量检查

历史期间收入数据与源数据匹配
所有费用行项目求和等于报告总计
小计（毛利润、EBIT、EBT、净利润）计算正确
税金计算逻辑合理（正确处理亏损）
预测驱动因素引用假设表（无硬编码）
期间变动方向合理

资产负债表（BS）质量检查

各期间资产=负债+权益（核心检查）
现金余额与现金流量表期末现金匹配
营运资金项目与支持明细表匹配（若有）
留存收益结转正确：期初留存收益+净利润-股利+/-调整=期末留存收益
债务余额与债务明细表匹配（若有）
所有资产负债表项目符号正确（资产为正，多数负债为正）

现金流量表（CF）质量检查

经营活动现金流量顶部的净利润与利润表净利润匹配
非现金加回项（折旧摊销、股份支付等）与源明细表/报表匹配
营运资金变动符号正确（资产增加=现金流出=负数）
资本支出与固定资产明细表/固定资产结转表匹配
筹资活动与资产负债表债务和权益项目变动匹配
期末现金与资产负债表现金匹配
期初现金等于上期期末现金

支持明细表质量检查

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

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
现金核对	现金流量表期末现金-资产负债表现金	= 0
净利润关联	利润表净利润-现金流量表初始净利润	= 0
留存收益	期初留存收益+净利润-股利-资产负债表期末留存收益	= 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
现金核对	现金流量表期末现金-资产负债表现金	= 0
月度与年度现金一致	月度期末现金-年度期末现金	= 0
净利润关联	利润表净利润-现金流量表初始净利润	= 0
留存收益	期初留存收益+净利润+股份支付-股利-资产负债表期末留存收益	= 0
权益融资	资产负债表普通股/资本公积变动-筹资活动权益发行	= 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

报表	项目	符号约定
经营活动现金流量	折旧摊销、股份支付	正（加回项）
经营活动现金流量	应收账款增加	负（现金流出）
经营活动现金流量	应付账款增加	正（现金流入）
投资活动现金流量	资本支出	负
筹资活动现金流量	债务发行	正
筹资活动现金流量	债务偿还	负
筹资活动现金流量	股利	负

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。在假设表中添加循环中断切换器。

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：货币一致性

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

板块2：资产负债表完整性

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

板块3：现金流量表完整性

现金与资产负债表匹配（现金流量表期末现金=资产负债表现金）
月度与年度现金一致：月度期末现金=年度期末现金
净利润与利润表匹配（现金流量表净利润=利润表净利润）
折旧摊销与明细表匹配
股份支付与利润表匹配
应收账款、存货、应付账款变动与营运资金明细表匹配
资本支出与折旧摊销明细表匹配

板块4：留存收益

留存收益结转检查：期初留存收益+净利润+股份支付-股利=期末留存收益
展示成分明细以便调试

板块5：营运资金

应收账款、存货、应付账款与资产负债表匹配
应收账款周转天数/存货周转天数/应付账款周转天数合理性检查（超出正常范围则标记）

板块6：债务明细表

总债务与资产负债表匹配（流动负债+长期债务）
利息计算与利润表匹配

板块6b：权益融资

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

板块6c：净营业亏损明细表

第1年/成立期初净营业亏损=0（新企业初始净营业亏损为0）
仅当税前利润<0时，净营业亏损才会增加（需实现亏损才能产生净营业亏损）
递延所得税资产与资产负债表匹配（净营业亏损明细表递延所得税资产=资产负债表递延所得税资产）
净营业亏损使用≤税前利润的80%（2017年后联邦限制）
净营业亏损余额非负（不可使用超过可用额度）
仅当税前利润<0时才产生净营业亏损
应纳税所得额≤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"

将所有板块状态汇总为单个总检查：

若所有板块通过 → "✓ ALL CHECKS PASS"
若任意板块失败 → "✗ 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

当总状态显示错误时：

滚动查找红色高亮的板块
识别哪个检查分类出现失败
导航到源工作表调查
修复根本问题
返回检查工作表验证问题已解决