dcf-model

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

DCF Model Builder

DCF模型构建器

Overview

概述

This skill creates institutional-quality DCF models for equity valuation following investment banking standards. Each analysis produces a detailed Excel model (with sensitivity analysis included at the bottom of the DCF sheet).

本技能遵循投资银行标准，创建机构级别的DCF模型用于股权估值。每次分析都会生成一个详细的Excel模型（DCF工作表底部包含敏感性分析）。

Tools

工具

Default to using all of the information provided by the user and MCP servers available for data sourcing.

默认使用用户提供的所有信息以及可用的MCP服务器进行数据获取。

Critical Constraints - Read These First

关键约束 - 请先阅读

These constraints apply throughout all DCF model building. Review before starting:

Environment: Office JS vs Python/openpyxl:

If running inside Excel (Office Add-in / Office JS environment): Use Office JS directly — do NOT use Python/openpyxl. Write formulas via
```
range.formulas = [["=D19*(1+$B$8)"]]
```
. No separate recalc step needed; Excel calculates natively. Use
```
range.format.*
```
for styling. The same formulas-over-hardcodes rule applies: set
```
.formulas
```
, never
```
.values
```
for derived cells.
If generating a standalone .xlsx file (no live Excel session): Use Python/openpyxl as described below, then run
```
recalc.py
```
before delivery.
The rest of this skill uses openpyxl examples — translate to Office JS API calls when in that environment, but all principles (formula strings, cell comments, section checkpoints, sensitivity table loops) apply identically.

⚠️ Office JS merged cell pitfall: When building section headers with merged cells, do NOT call

.merge()

then set

.values

on the merged range — Office JS still reports the range's original dimensions and will throw

InvalidArgument: The number of rows or columns in the input array doesn't match the size or dimensions of the range

. Instead, write the value to the top-left cell alone, then merge and format the full range:

// WRONG — throws InvalidArgument:
const hdr = ws.getRange("A7:H7");
hdr.merge();
hdr.values = [["MARKET DATA & KEY INPUTS"]];  // 1×1 array vs 1×8 range → fails

// CORRECT — value first on single cell, then merge + format the range:
ws.getRange("A7").values = [["MARKET DATA & KEY INPUTS"]];
const hdr = ws.getRange("A7:H7");
hdr.merge();
hdr.format.fill.color = "#1F4E79";
hdr.format.font.bold = true;
hdr.format.font.color = "#FFFFFF";

This applies to every merged section header in the DCF (market data, scenario blocks, cash flow projection, terminal value, valuation summary, sensitivity tables).

Formulas Over Hardcodes (NON-NEGOTIABLE):

Every projection, margin, discount factor, PV, and sensitivity cell MUST be a live Excel formula — never a value computed in Python and written as a number

When using openpyxl:

ws["D20"] = "=D19*(1+$B$8)"

is correct;

ws["D20"] = calculated_revenue

is WRONG

The only hardcoded numbers permitted are: (1) raw historical inputs, (2) assumption drivers (growth rates, WACC inputs, terminal g), (3) current market data (share price, debt balance)
If you catch yourself computing something in Python and writing the result — STOP. The model must flex when the user changes an assumption.

Verify Step-by-Step With the User (DO NOT build end-to-end):

After data retrieval → show the user the raw inputs block (revenue, margins, shares, net debt) and confirm before projecting
After revenue projections → show the projected top line and growth rates, confirm before building margin build
After FCF build → show the full FCF schedule, confirm logic before computing WACC
After WACC → show the calculation and inputs, confirm before discounting
After terminal value + PV → show the equity bridge (EV → equity value → per share), confirm before sensitivity tables
Catch errors at each stage — a wrong margin assumption discovered after sensitivity tables are built means rebuilding everything downstream

Sensitivity Tables:

Use an ODD number of rows and columns (standard: 5×5, sometimes 7×7) — this guarantees a true center cell
Center cell = base case. Build the axis values so the middle row header and middle column header exactly equal the model's actual assumptions (e.g., if base WACC = 9.0%, the middle row is 9.0%; if terminal g = 3.0%, the middle column is 3.0%). The center cell's output must therefore equal the model's actual implied share price — this is the sanity check that the table is built correctly.
Highlight the center cell with the medium-blue fill (
```
#BDD7EE
```
) + bold font so it's immediately visible which cell is the base case.
Populate ALL cells (typically 3 tables × 25 cells = 75) with full DCF recalculation formulas
Use openpyxl loops (or Office JS loops) to write formulas programmatically
NO placeholder text, NO linear approximations, NO manual steps required
Each cell must recalculate full DCF for that assumption combination

Cell Comments:

Add cell comments AS each hardcoded value is created
Format: "Source: [System/Document], [Date], [Reference], [URL if applicable]"
Every blue input must have a comment before moving to next section
Do not defer to end or write "TODO: add source"

Model Layout Planning:

Define ALL section row positions BEFORE writing any formulas
Write ALL headers and labels first
Write ALL section dividers and blank rows second
THEN write formulas using the locked row positions
Test formulas immediately after creation

Formula Recalculation:

Run
```
python recalc.py model.xlsx 30
```
before delivery
Fix ALL errors until status is "success"
Zero formula errors required (#REF!, #DIV/0!, #VALUE!, etc.)

Scenario Blocks:

Create separate blocks for Bear/Base/Bull cases
Show assumptions horizontally across projection years within each block

Use IF formulas:

=IF($B$6=1,[Bear cell],IF($B$6=2,[Base cell],[Bull cell]))

Verify formulas reference correct scenario block cells

这些约束适用于所有DCF模型构建流程。开始前请仔细阅读：

环境：Office JS vs Python/openpyxl：

如果在Excel内部运行（Office插件 / Office JS环境）：直接使用Office JS — 请勿使用Python/openpyxl。通过
```
range.formulas = [["=D19*(1+$B$8)"]]
```
写入公式。无需单独的重计算步骤；Excel会原生计算。使用
```
range.format.*
```
进行样式设置。同样遵循“公式优先于硬编码”规则：设置
```
.formulas
```
，切勿为派生单元格设置
```
.values
```
。
如果生成独立的.xlsx文件（无实时Excel会话）：使用下文描述的Python/openpyxl，然后在交付前运行
```
recalc.py
```
。
本技能其余部分使用openpyxl示例 — 在Office JS环境中时，请转换为Office JS API调用，但所有原则（公式字符串、单元格注释、章节检查点、敏感性表格循环）均完全适用。

⚠️ Office JS合并单元格陷阱：使用合并单元格构建章节标题时，请勿先调用

.merge()

再设置合并区域的

.values

— Office JS仍会报告区域的原始维度，并抛出

InvalidArgument: The number of rows or columns in the input array doesn't match the size or dimensions of the range

错误。相反，请仅将值写入左上角单元格，然后合并并格式化整个区域：

// WRONG — throws InvalidArgument:
const hdr = ws.getRange("A7:H7");
hdr.merge();
hdr.values = [["MARKET DATA & KEY INPUTS"]];  // 1×1 array vs 1×8 range → fails

// CORRECT — value first on single cell, then merge + format the range:
ws.getRange("A7").values = [["MARKET DATA & KEY INPUTS"]];
const hdr = ws.getRange("A7:H7");
hdr.merge();
hdr.format.fill.color = "#1F4E79";
hdr.format.font.bold = true;
hdr.format.font.color = "#FFFFFF";

这适用于DCF中的所有合并章节标题（市场数据、场景块、现金流预测、终值、估值摘要、敏感性表格）。

公式优先于硬编码（不可协商）：

所有预测值、利润率、折现因子、现值（PV）和敏感性单元格必须是实时Excel公式 — 绝不能是在Python中计算后写入的数值

使用openpyxl时：

ws["D20"] = "=D19*(1+$B$8)"

是正确的；

ws["D20"] = calculated_revenue

是错误的

仅允许以下硬编码数值：(1) 原始历史输入值，(2) 假设驱动因素（增长率、WACC输入值、终值增长率g），(3) 当前市场数据（股价、债务余额）
如果发现自己在Python中计算结果并写入单元格 — 请停止。模型必须能在用户修改假设时灵活调整。

逐步与用户确认（请勿端到端直接构建）：

数据获取完成后 → 向用户展示原始输入块（收入、利润率、股份数、净债务），确认后再进行预测
收入预测完成后 → 展示预测的顶线数据和增长率，确认后再构建利润率模块
自由现金流（FCF）构建完成后 → 展示完整的FCF时间表，确认逻辑后再计算WACC
WACC计算完成后 → 展示计算过程和输入值，确认后再进行折现
终值+现值计算完成后 → 展示股权转换过程（企业价值EV → 股权价值 → 每股价值），确认后再构建敏感性表格
在每个阶段捕捉错误 — 如果在敏感性表格构建完成后才发现错误的利润率假设，意味着需要重建下游所有内容

敏感性表格：

使用奇数行和列（标准为5×5，有时为7×7）— 这保证存在一个真正的中心单元格
中心单元格=基准场景。构建轴值时，确保中间行标题和中间列标题与模型的实际假设完全一致（例如，如果基准WACC=9.0%，中间行就是9.0%；如果终值增长率g=3.0%，中间列就是3.0%）。因此中心单元格的输出必须等于模型的实际隐含股价 — 这是验证表格构建正确与否的 sanity check。
高亮中心单元格：使用中等蓝色填充（
```
#BDD7EE
```
）+ 粗体字体，让用户能立即识别哪个单元格是基准场景。
填充所有单元格（通常为3个表格×25个单元格=75个），每个单元格包含完整的DCF重计算公式
使用openpyxl循环（或Office JS循环）以编程方式写入公式
禁止使用占位文本、线性近似或手动步骤
每个单元格必须针对该假设组合重新计算完整的DCF

单元格注释：

在创建每个硬编码值时添加单元格注释
格式："Source: [System/Document], [Date], [Reference], [URL if applicable]"
所有蓝色输入单元格在进入下一章节前必须添加注释
请勿延迟到最后添加或写入"TODO: add source"

模型布局规划：

在写入任何公式前，定义所有章节的行位置
先写入所有标题和标签
再写入所有章节分隔符和空白行
然后使用锁定的行位置写入公式
公式创建后立即测试

公式重计算：

交付前运行
```
python recalc.py model.xlsx 30
```
修复所有错误直到状态为"success"
要求零公式错误（#REF!, #DIV/0!, #VALUE!等）

场景块：

分别为熊市/基准/牛市场景创建独立的块
在每个块内，按预测年份横向展示假设

使用IF公式：

=IF($B$6=1,[Bear cell],IF($B$6=2,[Base cell],[Bull cell]))

验证公式引用了正确的场景块单元格

DCF Process Workflow

DCF流程工作流

Step 1: Data Retrieval and Validation

步骤1：数据获取与验证

Fetch data from MCP servers, user provided data, and the web.

Data Sources Priority:

MCP Servers (if configured) - Structured financial data from providers like Daloopa
User-Provided Data - Historical financials from their research
Web Search/Fetch - Current prices, beta, debt and cash when needed

Validation Checklist:

Verify net debt vs net cash (critical for valuation)
Confirm diluted shares outstanding (check for recent buybacks/issuances)
Validate historical margins are consistent with business model
Cross-check revenue growth rates with industry benchmarks
Verify tax rate is reasonable (typically 21-28%)

从MCP服务器、用户提供的数据和网络获取数据。

数据源优先级：

MCP服务器（若已配置）- 来自Daloopa等供应商的结构化财务数据
用户提供的数据 - 来自用户研究的历史财务数据
网络搜索/获取 - 必要时获取当前价格、贝塔（Beta）、债务和现金数据

验证清单：

验证净债务与净现金（对估值至关重要）
确认稀释后流通股数（检查近期回购/发行情况）
验证历史利润率与业务模式一致
将收入增长率与行业基准交叉核对
验证税率合理（通常为21-28%）

Step 2: Historical Analysis (3-5 years)

步骤2：历史分析（3-5年）

Analyze and document:

Revenue growth trends: Calculate CAGR, identify drivers
Margin progression: Track gross margin, EBIT margin, FCF margin
Capital intensity: D&A and CapEx as % of revenue
Working capital efficiency: NWC changes as % of revenue growth
Return metrics: ROIC, ROE trends

Create summary tables showing:

Historical Metrics (LTM):
Revenue: $X million
Revenue growth: X% CAGR
Gross margin: X%
EBIT margin: X%
D&A % of revenue: X%
CapEx % of revenue: X%
FCF margin: X%

分析并记录：

收入增长趋势：计算复合年增长率（CAGR），识别驱动因素
利润率变化：跟踪毛利率、EBIT利润率、FCF利润率
资本密集度：折旧与摊销（D&A）和资本支出（CapEx）占收入的比例
营运资金效率：净营运资金（NWC）变化占收入增长的比例
回报指标：投入资本回报率（ROIC）、净资产收益率（ROE）趋势

创建汇总表格，示例如下：

Historical Metrics (LTM):
Revenue: $X million
Revenue growth: X% CAGR
Gross margin: X%
EBIT margin: X%
D&A % of revenue: X%
CapEx % of revenue: X%
FCF margin: X%

Step 3: Build Revenue Projections

步骤3：构建收入预测

Methodology:

Start with latest actual revenue (LTM or most recent fiscal year)
Apply growth rates for each projection year
Show both dollar amounts AND calculated growth %

Growth Rate Framework:

Year 1-2: Higher growth reflecting near-term visibility
Year 3-4: Gradual moderation toward industry average
Year 5+: Approaching terminal growth rate

Formula structure:

Revenue(Year N) = Revenue(Year N-1) × (1 + Growth Rate)
Growth %(Year N) = Revenue(Year N) / Revenue(Year N-1) - 1

Three-scenario approach:

Bear Case: Conservative growth (e.g., 8-12%)
Base Case: Most likely scenario (e.g., 12-16%)
Bull Case: Optimistic growth (e.g., 16-20%)

方法：

以最新实际收入（LTM或最近财年）为起点
为每个预测年份应用增长率
同时展示美元金额和计算得出的增长率%

增长率框架：

第1-2年：较高增长率，反映短期可见性
第3-4年：逐步放缓至行业平均水平
第5年及以后：趋近终值增长率

公式结构：

收入(第N年) = 收入(第N-1年) × (1 + 增长率)
增长率%(第N年) = 收入(第N年) / 收入(第N-1年) - 1

三场景方法：

Bear Case: 保守增长（例如8-12%）
Base Case: 最可能场景（例如12-16%）
Bull Case: 乐观增长（例如16-20%）

Step 4: Operating Expense Modeling

步骤4：运营费用建模

Fixed/Variable Cost Analysis:

Operating expenses should model realistic operating leverage:

Sales & Marketing: Typically 15-40% of revenue depending on business model
Research & Development: Typically 10-30% for technology companies
General & Administrative: Typically 8-15% of revenue, shows leverage as company scales

Key principles:

ALL percentages based on REVENUE, not gross profit
Model operating leverage: % should decline as revenue scales
Maintain separate line items for S&M, R&D, G&A
Calculate EBIT = Gross Profit - Total OpEx

Margin expansion framework:

Current State → Target State (Year 5)
Gross Margin: X% → Y% (justify based on scale, efficiency)
EBIT Margin: X% → Y% (result of revenue growth + opex leverage)

固定/可变成本分析：

运营费用应模拟真实的经营杠杆：

销售与营销（S&M）：根据业务模式，通常占收入的15-40%
研发（R&D）：科技公司通常为10-30%
一般及行政（G&A）：通常占收入的8-15%，随着公司规模扩大呈现杠杆效应

关键原则：

所有百分比基于收入，而非毛利润
模拟经营杠杆：百分比应随收入规模扩大而下降
为S&M、R&D、G&A保留单独的行项目
计算EBIT = 毛利润 - 总运营费用

利润率扩张框架：

当前状态 → 目标状态（第5年）
毛利率: X% → Y%（基于规模、效率说明合理性）
EBIT利润率: X% → Y%（收入增长+运营杠杆的结果）

Step 5: Free Cash Flow Calculation

步骤5：自由现金流计算

Build FCF in proper sequence:

EBIT
(-) Taxes (EBIT × Tax Rate)
= NOPAT (Net Operating Profit After Tax)
(+) D&A (non-cash expense, % of revenue)
(-) CapEx (% of revenue, typically 4-8%)
(-) Δ NWC (change in working capital)
= Unlevered Free Cash Flow

Working Capital Modeling:

Calculate as % of revenue change (delta revenue)
Typical range: -2% to +2% of revenue change
Negative number = source of cash (working capital release)
Positive number = use of cash (working capital build)

Maintenance vs Growth CapEx:

Maintenance CapEx: Sustains current operations (~2-3% revenue)
Growth CapEx: Supports expansion (additional 2-5% revenue)
Total CapEx should align with company's growth strategy

按正确顺序构建FCF：

EBIT
(-) 税费（EBIT × 税率）
= NOPAT（税后净营业利润）
(+) D&A（非现金费用，占收入的比例）
(-) CapEx（占收入的比例，通常为4-8%）
(-) Δ NWC（营运资金变化）
= 无杠杆自由现金流

营运资金建模：

按收入变化（收入增量）的比例计算
典型范围：收入变化的-2%至+2%
负数=现金来源（营运资金释放）
正数=现金使用（营运资金占用）

维护性与增长性CapEx：

维护性CapEx：维持当前运营（约占收入的2-3%）
增长性CapEx：支持扩张（额外占收入的2-5%）
总CapEx应与公司的增长战略一致

Step 6: Cost of Capital (WACC) Research

步骤6：资本成本（WACC）研究

CAPM Methodology for Cost of Equity:

Cost of Equity = Risk-Free Rate + Beta × Equity Risk Premium

Where:
- Risk-Free Rate = Current 10-Year Treasury Yield
- Beta = 5-year monthly stock beta vs market index
- Equity Risk Premium = 5.0-6.0% (market standard)

Cost of Debt Calculation:

After-Tax Cost of Debt = Pre-Tax Cost of Debt × (1 - Tax Rate)

Determine Pre-Tax Cost of Debt from:
- Credit rating (if available)
- Current yield on company bonds
- Interest expense / Total Debt from financials

Capital Structure Weights:

Market Value Equity = Current Stock Price × Shares Outstanding
Net Debt = Total Debt - Cash & Equivalents
Enterprise Value = Market Cap + Net Debt

Equity Weight = Market Cap / Enterprise Value
Debt Weight = Net Debt / Enterprise Value

WACC = (Cost of Equity × Equity Weight) + (After-Tax Cost of Debt × Debt Weight)

Special Cases:

Net Cash Position: If Cash > Debt, Net Debt is NEGATIVE
- Debt Weight may be negative
- WACC calculation adjusts accordingly
No Debt: WACC = Cost of Equity

Typical WACC Ranges:

Large Cap, Stable: 7-9%
Growth Companies: 9-12%
High Growth/Risk: 12-15%

股权成本的CAPM方法：

股权成本 = 无风险利率 + Beta × 股权风险溢价

其中：
- 无风险利率 = 当前10年期国债收益率
- Beta = 5年月度股票相对于市场指数的贝塔值
- 股权风险溢价 = 5.0-6.0%（市场标准）

债务成本计算：

税后债务成本 = 税前债务成本 × (1 - 税率)

税前债务成本的确定方式：
- 信用评级（若可用）
- 公司债券当前收益率
- 财务报表中的利息费用 / 总债务

资本结构权重：

股权市值 = 当前股价 × 流通股数
净债务 = 总债务 - 现金及等价物
企业价值 = 市值 + 净债务

股权权重 = 市值 / 企业价值
债务权重 = 净债务 / 企业价值

WACC = (股权成本 × 股权权重) + (税后债务成本 × 债务权重)

特殊情况：

净现金头寸：若现金>债务，净债务为负数
- 债务权重可能为负数
- WACC计算相应调整
无债务：WACC = 股权成本

典型WACC范围：

大盘股、稳定型：7-9%
成长型公司：9-12%
高增长/高风险：12-15%

Step 7: Discount Rate Application (5-10 Year Forecast)

步骤7：折现率应用（5-10年预测）

Mid-Year Convention:

Cash flows assumed to occur mid-year
Discount Period: 0.5, 1.5, 2.5, 3.5, 4.5, etc.
Discount Factor = 1 / (1 + WACC)^Period

Present Value Calculation:

For each projection year:
PV of FCF = Unlevered FCF × Discount Factor

Example (Year 1):
FCF = $1,000
WACC = 10%
Period = 0.5
Discount Factor = 1 / (1.10)^0.5 = 0.9535
PV = $1,000 × 0.9535 = $954

Projection Period Selection:

5 years: Standard for most analyses
7-10 years: High growth companies with longer runway
3 years: Mature, stable businesses

年中惯例：

假设现金流在年中发生
折现期：0.5, 1.5, 2.5, 3.5, 4.5等
折现因子 = 1 / (1 + WACC)^期数

现值计算：

对于每个预测年份：
FCF现值 = 无杠杆FCF × 折现因子

示例（第1年）：
FCF = $1,000
WACC = 10%
期数 = 0.5
折现因子 = 1 / (1.10)^0.5 = 0.9535
现值 = $1,000 × 0.9535 = $954

预测期选择：

5年：大多数分析的标准
7-10年：具有更长增长周期的高增长公司
3年：成熟、稳定的企业

Step 8: Terminal Value Calculation

步骤8：终值计算

Perpetuity Growth Method (Preferred):

Terminal FCF = Final Year FCF × (1 + Terminal Growth Rate)
Terminal Value = Terminal FCF / (WACC - Terminal Growth Rate)

Critical Constraint: Terminal Growth < WACC (otherwise infinite value)

Terminal Growth Rate Selection:

Conservative: 2.0-2.5% (GDP growth rate)
Moderate: 2.5-3.5%
Aggressive: 3.5-5.0% (only for market leaders)

Do not exceed: Risk-free rate or long-term GDP growth

Exit Multiple Method (Alternative):

Terminal Value = Final Year EBITDA × Exit Multiple

Where Exit Multiple comes from:
- Industry comparable trading multiples
- Precedent transaction multiples
- Typical range: 8-15x EBITDA

Present Value of Terminal Value:

PV of Terminal Value = Terminal Value / (1 + WACC)^Final Period

Where Final Period accounts for timing:
5-year model with mid-year convention: Period = 4.5

Terminal Value Sanity Check:

Should represent 50-70% of Enterprise Value
If >75%, model may be over-reliant on terminal assumptions
If <40%, check if terminal assumptions are too conservative

永续增长法（首选）：

终期FCF = 最后一年FCF × (1 + 终值增长率)
终值 = 终期FCF / (WACC - 终值增长率)

关键约束：终值增长率 < WACC（否则价值无限大）

终值增长率选择：

保守型：2.0-2.5%（GDP增长率）
适中型：2.5-3.5%
激进型：3.5-5.0%（仅适用于市场领导者）

不得超过：无风险利率或长期GDP增长率

退出乘数法（替代方法）：

终值 = 最后一年EBITDA × 退出乘数

其中退出乘数来自：
- 行业可比交易乘数
- 先例交易乘数
- 典型范围：8-15x EBITDA

终值现值：

终值现值 = 终值 / (1 + WACC)^最后一期数

其中最后一期数考虑时间因素：
采用年中惯例的5年模型：期数=4.5

终值合理性检查：

应占企业价值的50-70%
若>75%，模型可能过度依赖终值假设
若<40%，检查终值假设是否过于保守

Step 9: Enterprise to Equity Value Bridge

步骤9：企业价值到股权价值的转换

Valuation Summary Structure:

(+) Sum of PV of Projected FCFs = $X million
(+) PV of Terminal Value = $Y million
= Enterprise Value = $Z million

(-) Net Debt [or + Net Cash if negative] = $A million
= Equity Value = $B million

÷ Diluted Shares Outstanding = C million shares
= Implied Price per Share = $XX.XX

Current Stock Price = $YY.YY
Implied Return = (Implied Price / Current Price) - 1 = XX%

Critical Adjustments:

Net Debt = Total Debt - Cash & Equivalents
- If positive: Subtract from EV (reduces equity value)
- If negative (Net Cash): Add to EV (increases equity value)
Use Diluted Shares: Includes options, RSUs, convertible securities
Other adjustments (if applicable):
- Minority interests
- Pension liabilities
- Operating lease obligations

Valuation Output Format:

csv

Valuation Component,Amount ($M)
PV Explicit FCFs,X.X
PV Terminal Value,Y.Y
Enterprise Value,Z.Z
(-) Net Debt,A.A
Equity Value,B.B
,,
Shares Outstanding (M),C.C
Implied Price per Share,$XX.XX
Current Share Price,$YY.YY
Implied Upside/(Downside),+XX%

估值摘要结构：

(+) 预测FCF现值之和 = $X million
(+) 终值现值 = $Y million
= 企业价值 = $Z million

(-) 净债务 [若为负则+净现金] = $A million
= 股权价值 = $B million

÷ 稀释后流通股数 = C million股
= 隐含每股价格 = $XX.XX

当前股价 = $YY.YY
隐含回报率 = (隐含价格 / 当前价格) - 1 = XX%

关键调整：

净债务 = 总债务 - 现金及等价物
- 若为正：从EV中减去（降低股权价值）
- 若为负（净现金）：加到EV中（提高股权价值）
使用稀释后股份数：包括期权、限制性股票单位（RSU）、可转换证券
其他调整（若适用）：
- 少数股东权益
- 养老金负债
- 经营租赁义务

估值输出格式：

csv

Valuation Component,Amount ($M)
PV Explicit FCFs,X.X
PV Terminal Value,Y.Y
Enterprise Value,Z.Z
(-) Net Debt,A.A
Equity Value,B.B
,,
Shares Outstanding (M),C.C
Implied Price per Share,$XX.XX
Current Share Price,$YY.YY
Implied Upside/(Downside),+XX%

Step 10: Sensitivity Analysis

步骤10：敏感性分析

Build three sensitivity tables at the bottom of the DCF sheet showing how valuation changes with different assumptions:

WACC vs Terminal Growth - Shows enterprise value sensitivity to discount rate and perpetuity growth
Revenue Growth vs EBIT Margin - Shows impact of top-line growth and operating leverage
Beta vs Risk-Free Rate - Shows sensitivity to cost of equity components

Implementation: These are simple 2D grids (NOT Excel's "Data Table" feature) with formulas in each cell. Each cell must contain a full DCF recalculation for that specific assumption combination. See Critical Constraints section for detailed requirements on populating all 75 cells programmatically using openpyxl.

<correct_patterns>

This section contains all the CORRECT patterns to follow when building DCF models.

在DCF工作表底部构建三个敏感性表格，展示估值随不同假设的变化：

WACC vs 终值增长率 - 展示企业价值对折现率和永续增长率的敏感性
收入增长率 vs EBIT利润率 - 展示顶线增长和经营杠杆的影响
Beta vs 无风险利率 - 展示对股权成本组成部分的敏感性

实现方式：这些是简单的二维网格（而非Excel的“数据表格”功能），每个单元格包含公式。每个单元格必须包含针对该特定假设组合的完整DCF重计算。有关使用openpyxl以编程方式填充所有75个单元格的详细要求，请参阅关键约束部分。

<correct_patterns>

本部分包含构建DCF模型时需遵循的所有正确模式。

Scenario Block Selection Pattern - Follow This Approach

场景块选择模式 - 遵循此方法

Assumptions are organized in separate blocks for each scenario:

CRITICAL STRUCTURE - Three rows per section header:

csv

BEAR CASE ASSUMPTIONS (section header, merge cells across)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),12%,10%,9%,8%,7%
EBIT Margin (%),45%,44%,43%,42%,41%

BASE CASE ASSUMPTIONS (section header, merge cells across)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),16%,14%,12%,10%,9%
EBIT Margin (%),48%,49%,50%,51%,52%

BULL CASE ASSUMPTIONS (section header, merge cells across)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),20%,18%,15%,13%,11%
EBIT Margin (%),50%,51%,52%,53%,54%

Each scenario block MUST have a column header row showing the projection years (FY2025E, FY2026E, etc.) immediately below the section title. Without this, users cannot tell which assumption value corresponds to which year.

How to reference assumptions - Create a consolidation column:

Case selector cell (e.g., B6) contains 1=Bear, 2=Base, or 3=Bull
Create a consolidation column with INDEX or OFFSET formulas to pull from the correct scenario block
Projection formulas reference the consolidation column (clean cell references)
Each scenario block contains full set of DCF assumptions across projection years

Recommended consolidation column pattern (using INDEX):

=INDEX(B10:D10, 1, $B$6)

NOT this - scattered IF statements throughout:

=IF($B$6=1,[Bear block cell],IF($B$6=2,[Base block cell],[Bull block cell]))

The consolidation column approach centralizes logic and makes the model easier to audit.

假设按每个场景组织在独立的块中：

关键结构 - 每个章节标题占三行：

csv

BEAR CASE ASSUMPTIONS（章节标题，跨列合并单元格）
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),12%,10%,9%,8%,7%
EBIT Margin (%),45%,44%,43%,42%,41%

BASE CASE ASSUMPTIONS（章节标题，跨列合并单元格）
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),16%,14%,12%,10%,9%
EBIT Margin (%),48%,49%,50%,51%,52%

BULL CASE ASSUMPTIONS（章节标题，跨列合并单元格）
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),20%,18%,15%,13%,11%
EBIT Margin (%),50%,51%,52%,53%,54%

每个场景块必须包含列标题行，显示预测年份（FY2025E、FY2026E等），紧跟在章节标题下方。没有此行，用户无法区分哪个假设值对应哪一年。

如何引用假设 - 创建合并列：

场景选择单元格（例如B6）包含1=熊市、2=基准、3=牛市
创建一个合并列，使用INDEX或OFFSET公式从正确的场景块中提取数据
预测公式引用合并列（清晰的单元格引用）
每个场景块包含完整的DCF假设，覆盖所有预测年份

推荐的合并列模式（使用INDEX）：

=INDEX(B10:D10, 1, $B$6)

请勿使用这种方式 - 分散的IF语句：

=IF($B$6=1,[Bear block cell],IF($B$6=2,[Base block cell],[Bull block cell]))

合并列方法集中了逻辑，使模型更易于审计。

Correct Revenue Projection Pattern

正确的收入预测模式

Create a consolidation column with INDEX formulas, then reference it in projections:

Step 1 - Consolidation column for FY1 growth:

=INDEX([Bear FY1 growth]:[Bull FY1 growth], 1, $B$6)

Step 2 - Revenue projection references the consolidation column:

Revenue Year 1: =D29*(1+$E$10)

Where:

D29 = Prior year revenue
$E$10 = Consolidation column cell for FY1 growth (contains INDEX formula)
$B$6 = Case selector (1=Bear, 2=Base, 3=Bull)

This approach is cleaner than embedding IF statements in every projection formula and makes it much easier to audit which scenario assumptions are being used.

创建包含INDEX公式的合并列，然后在预测中引用它：

步骤1 - FY1增长率的合并列：

=INDEX([Bear FY1 growth]:[Bull FY1 growth], 1, $B$6)

步骤2 - 收入预测引用合并列：

Revenue Year 1: =D29*(1+$E$10)

其中：

D29 = 上一年收入
$E$10 = FY1增长率的合并列单元格（包含INDEX公式）
$B$6 = 场景选择器（1=熊市、2=基准、3=牛市）

这种方法比在每个预测公式中嵌入IF语句更简洁，并且能更轻松地审计正在使用的场景假设。

Correct FCF Formula Pattern

正确的FCF公式模式

Use consolidation columns with INDEX formulas, then reference them in FCF calculations:

Consolidation column approach:

csv

Item,Formula,Reference
D&A,=E29*$E$21,$E$21 = consolidation column for D&A %
CapEx,=E29*$E$22,$E$22 = consolidation column for CapEx %
Δ NWC,=(E29-D29)*$E$23,$E$23 = consolidation column for NWC %
Unlevered FCF,=E57+E58-E60-E62,E57=NOPAT E58=D&A E60=CapEx E62=Δ NWC

Each consolidation column cell contains an INDEX formula that pulls from the appropriate scenario block based on case selector. This keeps projection formulas clean and auditable.

Before writing formulas, confirm scenario block row locations and set up consolidation columns.

使用包含INDEX公式的合并列，然后在FCF计算中引用它们：

合并列方法：

csv

Item,Formula,Reference
D&A,=E29*$E$21,$E$21 = D&A比例的合并列
CapEx,=E29*$E$22,$E$22 = CapEx比例的合并列
Δ NWC,=(E29-D29)*$E$23,$E$23 = NWC比例的合并列
Unlevered FCF,=E57+E58-E60-E62,E57=NOPAT E58=D&A E60=CapEx E62=Δ NWC

每个合并列单元格包含一个INDEX公式，根据场景选择器从相应的场景块中提取数据。这使预测公式保持清晰且易于审计。

在写入公式前，确认场景块的行位置并设置合并列。

Correct Cell Comment Format

正确的单元格注释格式

Every hardcoded value needs this format:

"Source: [System/Document], [Date], [Reference], [URL if applicable]"

Examples:

csv

Item,Source Comment
Stock price,Source: Market data script 2025-10-12 Close price
Shares outstanding,Source: 10-K FY2024 Page 45 Note 12
Historical revenue,Source: 10-K FY2024 Page 32 Consolidated Statements
Beta,Source: Market data script 2025-10-12 5-year monthly beta
Consensus estimates,Source: Management guidance Q3 2024 earnings call

每个硬编码值需要以下格式：

"Source: [System/Document], [Date], [Reference], [URL if applicable]"

示例：

csv

Item,Source Comment
Stock price,Source: Market data script 2025-10-12 Close price
Shares outstanding,Source: 10-K FY2024 Page 45 Note 12
Historical revenue,Source: 10-K FY2024 Page 32 Consolidated Statements
Beta,Source: Market data script 2025-10-12 5-year monthly beta
Consensus estimates,Source: Management guidance Q3 2024 earnings call

Correct Assumption Table Structure

正确的假设表格结构

CRITICAL: Each scenario block requires THREE structural elements:

Section header row (merged cells): e.g., "BEAR CASE ASSUMPTIONS"
Column header row showing years - THIS IS REQUIRED, DO NOT SKIP
Data rows with assumption values

Structure:

csv

BEAR CASE ASSUMPTIONS (section header - merge across columns A:G)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

BASE CASE ASSUMPTIONS (section header - merge across columns A:G)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

BULL CASE ASSUMPTIONS (section header - merge across columns A:G)
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

WITHOUT the column header row showing projection years (FY2025E, FY2026E, etc.), users cannot tell which assumption value corresponds to which year. This row is MANDATORY.

Then create a consolidation column (typically the next column to the right) that uses INDEX formulas to pull from the selected scenario block based on the case selector. This consolidation column is what your projection formulas reference.

关键要求：每个场景块需要三个结构元素：

章节标题行（合并单元格）：例如"BEAR CASE ASSUMPTIONS"
显示年份的列标题行 - 这是必填项，请勿跳过
假设值的数据行

结构：

csv

BEAR CASE ASSUMPTIONS（章节标题 - 跨A:G列合并）
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

BASE CASE ASSUMPTIONS（章节标题 - 跨A:G列合并）
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

BULL CASE ASSUMPTIONS（章节标题 - 跨A:G列合并）
Assumption,FY1,FY2,FY3,FY4,FY5
Revenue Growth (%),X%,X%,X%,X%,X%
EBIT Margin (%),X%,X%,X%,X%,X%
Terminal Growth,X%,,,,
WACC,X%,,,,

如果没有显示预测年份（FY2025E、FY2026E等）的列标题行，用户无法区分哪个假设值对应哪一年。此行是强制性的。

然后创建一个合并列（通常在右侧下一列），使用INDEX公式根据场景选择器从选定的场景块中提取数据。你的预测公式将引用这个合并列。

Correct Row Planning Process

正确的行规划流程

1. Write ALL headers and labels FIRST:

csv

Row,Content
1,[Company Name] DCF Model
2,Ticker | Date | Year End
4,Case Selector
7,KEY ASSUMPTIONS
26,Assumption headers
27-31,Growth assumptions
...,...

2. Write ALL section dividers and blank rows

3. THEN write formulas using the locked row positions

4. Test formulas immediately after creation

Think of it like construction:

Good: Pour foundation, then build walls (stable structure)
Bad: Build walls, then pour foundation (walls collapse)

Excel version:

Good: Add headers, then write formulas (formulas stable)
Bad: Write formulas, then add headers (formulas break)

1. 先写入所有标题和标签：

csv

Row,Content
1,[Company Name] DCF Model
2,Ticker | Date | Year End
4,Case Selector
7,KEY ASSUMPTIONS
26,Assumption headers
27-31,Growth assumptions
...,...

2. 写入所有章节分隔符和空白行

3. 然后使用锁定的行位置写入公式

4. 公式创建后立即测试

可以想象成建筑施工：

正确顺序：先打地基，再建墙（结构稳定）
错误顺序：先建墙，再打地基（墙会倒塌）

Excel版本：

正确顺序：添加标题，再写入公式（公式稳定）
错误顺序：写入公式，再添加标题（公式会出错）

Correct Sensitivity Table Implementation

正确的敏感性表格实现

IMPORTANT: These are NOT Excel's "Data Table" feature. These are simple grids where you write regular formulas using openpyxl. Yes, this means ~75 formulas total (3 tables × 25 cells each), but this is straightforward and required.

Programmatic Population with Formulas:

Each sensitivity table must be fully populated with formulas that recalculate the implied share price for each combination of assumptions. Do not use Excel's Data Table feature (it requires manual intervention and cannot be automated via openpyxl).

Implementation approach - CONCRETE EXAMPLE:

Table Structure — 5×5 grid (ODD dimensions, base case centered):

If the model's base WACC = 9.0% and base terminal growth = 3.0%, build the axes symmetrically around those values:

csv

WACC vs Terminal Growth,  2.0%,  2.5%,  3.0%,  3.5%,  4.0%
              8.0%,       [fml], [fml], [fml], [fml], [fml]
              8.5%,       [fml], [fml], [fml], [fml], [fml]
              9.0%,       [fml], [fml], [★  ], [fml], [fml]   ← middle row = base WACC
              9.5%,       [fml], [fml], [fml], [fml], [fml]
             10.0%,       [fml], [fml], [fml], [fml], [fml]
                                   ↑
                          middle col = base terminal g

★ = the center cell. Its formula output MUST equal the model's actual implied share price (from the valuation summary). Apply the medium-blue fill (

#BDD7EE

) and bold font to this cell so the base case is visually anchored.

Rule for axis values:

axis_values = [base - 2*step, base - step, base, base + step, base + 2*step]

— symmetric around the base, odd count guarantees a center.

Formula Pattern - Cell B88 (WACC=8.0%, Terminal Growth=2.0%):

The formula in B88 should recalculate the implied price using:

WACC from row header:
```
$A88
```
(8.0%)
Terminal Growth from column header:
```
B$87
```
(2.0%)

Recommended approach: Reference the main DCF calculation but substitute these values.

Example formula structure:

=([SUM of PV FCFs using $A88 as discount rate] + [Terminal Value using B$87 as growth rate and $A88 as WACC] - [Net Debt]) / [Shares]

CRITICAL - Write a formula for EVERY cell in the 5x5 grid (25 cells per table, 75 cells total). Use openpyxl to write these formulas programmatically in a loop. Do NOT skip this step or leave placeholder text.

Python implementation pattern:

python

undefined

重要提示：这些不是Excel的“数据表格”功能。这些是简单的网格，你使用openpyxl写入常规公式。是的，这意味着总共约75个公式（3个表格×每个25个单元格），但这是直接且必需的。

使用公式以编程方式填充：

每个敏感性表格必须完全填充公式，针对每个假设组合重新计算隐含股价。请勿使用Excel的数据表格功能（它需要手动干预，无法通过openpyxl自动化）。

实现方法 - 具体示例：

表格结构 — 5×5网格（奇数维度，基准场景居中）：

如果模型的基准WACC=9.0%，基准终值增长率=3.0%，围绕这些值对称构建轴：

csv

WACC vs Terminal Growth,  2.0%,  2.5%,  3.0%,  3.5%,  4.0%
              8.0%,       [fml], [fml], [fml], [fml], [fml]
              8.5%,       [fml], [fml], [fml], [fml], [fml]
              9.0%,       [fml], [fml], [★  ], [fml], [fml]   ← 中间行=基准WACC
              9.5%,       [fml], [fml], [fml], [fml], [fml]
             10.0%,       [fml], [fml], [fml], [fml], [fml]
                                   ↑
                          中间列=基准终值增长率

★ = 中心单元格。其公式输出必须等于模型的实际隐含股价（来自估值摘要）。对该单元格应用中等蓝色填充（

#BDD7EE

）和粗体字体，使基准场景在视觉上突出。

轴值规则：

axis_values = [base - 2*step, base - step, base, base + step, base + 2*step]

— 围绕基准对称，奇数数量保证存在中心。

公式模式 - B88单元格（WACC=8.0%，终值增长率=2.0%）：

B88中的公式应使用以下值重新计算隐含价格：

行标题中的WACC：
```
$A88
```
（8.0%）
列标题中的终值增长率：
```
B$87
```
（2.0%）

推荐方法：引用主DCF计算，但替换这些值。

公式结构示例：

=([使用$A88作为折现率的FCF现值之和] + [使用B$87作为增长率和$A88作为WACC的终值] - [净债务]) / [股份数]

关键要求 - 为5x5网格中的每个单元格编写公式（每个表格25个单元格，总共75个）。使用openpyxl通过循环以编程方式写入这些公式。请勿跳过此步骤或留下占位文本。

Python实现模式：

python

undefined

Pseudocode for populating sensitivity table

填充敏感性表格的伪代码

for row_idx, wacc_value in enumerate(wacc_range): for col_idx, term_growth_value in enumerate(term_growth_range): # Build formula that uses wacc_value and term_growth_value formula = f"=<DCF recalc using {wacc_value} and {term_growth_value}>" ws.cell(row=start_row+row_idx, column=start_col+col_idx).value = formula


**The sensitivity tables must work immediately when the model is opened, with no manual steps required from the user.**

</correct_patterns>

<common_mistakes>

This section contains all the WRONG patterns to avoid when building DCF models.

for row_idx, wacc_value in enumerate(wacc_range): for col_idx, term_growth_value in enumerate(term_growth_range): # 构建使用wacc_value和term_growth_value的公式 formula = f"=<DCF recalc using {wacc_value} and {term_growth_value}>" ws.cell(row=start_row+row_idx, column=start_col+col_idx).value = formula


**敏感性表格必须在模型打开时立即可用，无需用户执行任何手动步骤。**

</correct_patterns>

<common_mistakes>

本部分包含构建DCF模型时需避免的所有错误模式。

WRONG: Simplified Sensitivity Table Approximations or Placeholder Text

错误：简化的敏感性表格近似或占位文本

Don't use linear approximations:

// WRONG - Linear approximation
B97: =B88*(1+(0.096-0.116))    // Assumes linear relationship

// WRONG - Division shortcut
B105: =B88/(1+(E48-0.07))      // Doesn't recalculate full DCF

Don't leave placeholder text:

// WRONG - Placeholder note
"Note: Use Excel Data Table feature (Data → What-If Analysis → Data Table) to populate sensitivity tables."

// WRONG - Empty cells
[leaving cells blank because "this is complex"]

Don't confuse terminology:

❌ "Sensitivity tables need Excel's Data Table feature" (NO - that's a specific Excel tool we can't use)
✅ "Sensitivity tables are simple grids with formulas in each cell" (YES - this is what we build)

Why these shortcuts are wrong:

Linear approximation formulas don't actually recalculate the DCF - they just apply simple math adjustments
The relationships are not linear, so the results will be inaccurate
Placeholder text requires manual user intervention
Model is not immediately usable when delivered
Not professional or client-ready
Empty cells = incomplete deliverable

Common rationalization to REJECT: "Writing 75+ formulas feels complex, so I'll leave a note for the user to complete it manually."

Reality: Writing 75 formulas is straightforward when you use a loop in Python with openpyxl. Each formula follows the same pattern - just substitute the row/column values. This is a required part of the deliverable.

Instead: Populate every sensitivity cell with formulas that recalculate the full DCF for that specific combination of assumptions

请勿使用线性近似：

// WRONG - Linear approximation
B97: =B88*(1+(0.096-0.116))    // 假设线性关系

// WRONG - Division shortcut
B105: =B88/(1+(E48-0.07))      // 未重新计算完整DCF

请勿留下占位文本：

// WRONG - Placeholder note
"Note: Use Excel Data Table feature (Data → What-If Analysis → Data Table) to populate sensitivity tables."

// WRONG - Empty cells
[leaving cells blank because "this is complex"]

请勿混淆术语：

❌ "敏感性表格需要Excel的数据表格功能"（不可以 - 这是我们无法使用的特定Excel工具）
✅ "敏感性表格是每个单元格都包含公式的简单网格"（正确 - 这是我们要构建的）

这些捷径错误的原因：

线性近似公式并未实际重新计算DCF - 只是应用简单的数学调整
实际关系并非线性，因此结果会不准确
占位文本需要用户手动干预
模型交付后无法立即使用
不专业，不符合客户交付标准
空单元格=不完整的交付物

需拒绝的常见合理化借口： "编写75+个公式感觉很复杂，所以我会给用户留个注释让他们手动完成。"

实际情况：使用Python的openpyxl循环编写75个公式非常简单。每个公式遵循相同的模式 - 只需替换行/列的值。这是交付物的必要部分。

正确做法：为每个敏感性单元格填充公式，针对该特定假设组合重新计算完整的DCF

WRONG: Missing Cell Comments

错误：缺少单元格注释

Don't do this:

Create all hardcoded inputs without comments
Think "I'll add them later"
Write "TODO: add source"
Leave blue inputs without documentation

Why it's wrong:

Can't verify where data came from
Fails xlsx skill requirements
Not audit-ready
Wastes time fixing later

Instead: Add cell comment AS EACH hardcoded value is created

请勿这样做：

创建所有硬编码输入但不添加注释
想着"我稍后再添加"
写入"TODO: add source"
留下蓝色输入单元格而不添加文档

错误原因：

无法验证数据来源
不符合xlsx技能要求
不具备审计就绪性
以后修复会浪费时间

正确做法：在创建每个硬编码值时添加单元格注释

WRONG: Formula Row References Off

错误：公式行引用错误

Symptom: The FCF section references wrong assumption rows:

D&A:  =E29*$E$34    // Should be $E$21, but referencing wrong row

CapEx: =E29*$E$41   // Should be $E$22, but row shifted

Why this happens:

Formulas written first
Then headers inserted
All row references shifted
Now formulas point to wrong cells → #REF! errors

Instead: Lock row layout FIRST, then write formulas

症状： FCF部分引用了错误的假设行：

D&A:  =E29*$E$34    // 应为$E$21，但引用了错误的行

CapEx: =E29*$E$41   // 应为$E$22，但行偏移了

原因：

先写入公式
然后插入标题
所有行引用偏移
现在公式指向错误的单元格 → #REF!错误

正确做法：先锁定行布局，再写入公式

WRONG: Single Row for Each Assumption Across Scenarios

错误：每个假设在场景中仅占一行

Don't structure assumptions like this:

csv

Assumption,Bear,Base,Bull
Revenue Growth FY1,10%,13%,16%
Revenue Growth FY2,9%,12%,15%

This vertical layout makes it hard to see the progression across years within each scenario.

Why it's wrong:

Makes it difficult to see assumptions evolving across years within each scenario
Harder to compare scenario assumptions across full projection period
Less intuitive for reviewing scenario logic

Instead:

Create separate blocks for each scenario (Bear, Base, Bull)
Within each block, show assumptions horizontally across projection years
This makes each scenario's assumptions easier to review as a cohesive set

请勿这样组织假设：

csv

Assumption,Bear,Base,Bull
Revenue Growth FY1,10%,13%,16%
Revenue Growth FY2,9%,12%,15%

这种垂直布局难以查看每个场景中跨年份的假设变化。

错误原因：

难以查看每个场景中跨年份的假设演变
难以比较整个预测期内的场景假设
审查场景逻辑时不够直观

正确做法：

为每个场景（熊市、基准、牛市）创建独立的块
在每个块内，按预测年份横向展示假设
这使每个场景的假设作为一个连贯的集合更易于审查

WRONG: No Borders

错误：无边框

Don't deliver a model without borders:

No section delineation
All cells blend together
Hard to read and unprofessional

Why it's wrong:

Not client-ready
Difficult to navigate
Looks amateur

Instead: Add borders around all major sections

请勿交付没有边框的模型：

没有章节划分
所有单元格混在一起
难以阅读且不专业

错误原因：

不符合客户交付标准
难以导航
看起来业余

正确做法：为所有主要章节添加边框

WRONG: Wrong Font Colors or No Font Color Distinction

错误：字体颜色错误或无字体颜色区分

Don't do this:

All text is black
Only use fill colors (no font color changes)
Mix up which cells are blue vs black

Why it's wrong:

Can't distinguish inputs from formulas
Auditing becomes impossible
Violates xlsx skill requirements

Instead: Blue text for ALL hardcoded inputs, black text for ALL formulas, green for sheet links

请勿这样做：

所有文本都是黑色
仅使用填充颜色（不更改字体颜色）
混淆蓝色和黑色单元格

错误原因：

无法区分输入和公式
审计变得不可能
违反xlsx技能要求

正确做法：所有硬编码输入使用蓝色文本，所有公式使用黑色文本，工作表链接使用绿色文本

WRONG: Operating Expenses Based on Gross Profit

错误：基于毛利润计算运营费用

Don't do this:

S&M: =E33*0.15    // E33 = Gross Profit (WRONG)

Why it's wrong:

Operating expenses scale with revenue, not gross profit
Produces unrealistic margin progression
Not how businesses actually operate

Instead:

S&M: =E29*0.15    // E29 = Revenue (CORRECT)

请勿这样做：

S&M: =E33*0.15    // E33 = 毛利润（错误）

错误原因：

运营费用随收入扩展，而非毛利润
产生不切实际的利润率变化
不符合企业实际运营方式

正确做法：

S&M: =E29*0.15    // E29 = 收入（正确）

TOP 5 ERRORS SUMMARY

五大错误总结

Formula row references off → Define ALL row positions BEFORE writing formulas
Missing cell comments → Add comments AS cells are created, not at end
Simplified sensitivity tables → Populate all cells with full DCF recalc formulas, not approximations
Scenario block references wrong → Ensure IF formulas pull from correct Bear/Base/Bull blocks
No borders → Add professional section borders for client-ready appearance

In addition, be aware of these errors:

公式行引用错误 → 写入公式前定义所有行位置
缺少单元格注释 → 创建单元格时添加注释，而非最后添加
简化的敏感性表格 → 为所有单元格填充完整的DCF重计算公式，而非近似值
场景块引用错误 → 确保IF公式从正确的熊市/基准/牛市块中提取数据
无边框 → 添加专业的章节边框，符合客户交付外观

此外，请注意以下错误：

WACC Calculation Errors

WACC计算错误

Mixing book and market values in capital structure
Using equity beta instead of asset/unlevered beta incorrectly
Wrong tax rate application to cost of debt
Incorrect risk-free rate (must use current 10Y Treasury)
Failure to adjust for net debt vs net cash position

在资本结构中混合使用账面价值和市场价值
错误地使用股权贝塔而非资产/无杠杆贝塔
债务成本应用错误的税率
使用错误的无风险利率（必须使用当前10年期国债收益率）
未针对净债务与净现金头寸进行调整

Growth Assumption Flaws

增长假设缺陷

Terminal growth > WACC (creates infinite value)
Projection growth rates inconsistent with historical performance
Ignoring industry growth constraints
Revenue growth not aligned with unit economics
Margin expansion without operational justification

终值增长率 > WACC（产生无限价值）
预测增长率与历史表现不一致
忽略行业增长限制
收入增长与单位经济不匹配
利润率扩张但无运营层面的合理性

Terminal Value Mistakes

终值错误

Using wrong growth method (perpetuity vs exit multiple)
Terminal value >80% of enterprise value (suggests over-reliance)
Inconsistent terminal margins with steady state assumptions
Wrong discount period for terminal value

使用错误的增长方法（永续法vs退出乘数法）
终值 >企业价值的80%（表明过度依赖）
终值利润率与稳态假设不一致
终值使用错误的折现期

Cash Flow Projection Errors

现金流预测错误

Operating expenses based on gross profit instead of revenue
D&A/CapEx percentages misaligned with business model
Working capital changes not properly calculated
Tax rate inconsistency between years
NOPAT calculation errors

These errors are the most common. Re-read this section before starting any DCF build.

</common_mistakes>

运营费用基于毛利润而非收入
D&A/CapEx比例与业务模式不一致
营运资金变化计算不正确
各年份税率不一致
NOPAT计算错误

这些是最常见的错误。开始任何DCF构建前，请重新阅读本部分。

</common_mistakes>

Excel File Creation

Excel文件创建

This skill uses the
xlsx
skill for all spreadsheet operations. The xlsx skill provides:

Standardized formula construction rules
Number formatting conventions
Automated formula recalculation via
```
recalc.py
```
script
Comprehensive error checking and validation

All Excel files created by this skill must follow xlsx skill requirements, including zero formula errors and proper recalculation.

本技能使用
xlsx
技能进行所有电子表格操作。 xlsx技能提供：

标准化公式构建规则
数字格式约定
通过
```
recalc.py
```
脚本自动进行公式重计算
全面的错误检查和验证

本技能创建的所有Excel文件必须遵循xlsx技能要求，包括零公式错误和正确的重计算。

Quality Rubric

质量评估标准

Every DCF model must maximize for:

Realistic revenue and margin assumptions based on historical performance
Appropriate cost of capital calculation with proper CAPM methodology
Comprehensive sensitivity analysis showing valuation ranges
Clear terminal value calculation with supporting rationale
Professional model structure enabling scenario analysis
Transparent documentation of all key assumptions

每个DCF模型必须最大化满足以下要求：

基于历史表现的真实收入和利润率假设
使用正确CAPM方法的合理资本成本计算
展示估值范围的全面敏感性分析
带有支持依据的清晰终值计算
支持场景分析的专业模型结构
所有关键假设的透明文档

Input Requirements

输入要求

Minimum Required Inputs

最低要求输入

Company identifier: Ticker symbol or company name
Growth assumptions: Revenue growth rates for projection period (or "use consensus")
Optional parameters:
- Projection period (default: 5 years)
- Scenario cases (Bear/Base/Bull growth and margin assumptions)
- Terminal growth rate (default: 2.5-3.0%)
- Specific WACC inputs if not using CAPM

公司标识符：股票代码或公司名称
增长假设：预测期的收入增长率（或"使用共识预测"）
可选参数：
- 预测期（默认：5年）
- 场景案例（熊市/基准/牛市的增长和利润率假设）
- 终值增长率（默认：2.5-3.0%）
- 若不使用CAPM，需提供特定的WACC输入值

Excel Model Structure

Excel模型结构

Sheet Architecture

工作表架构

Create two sheets:

DCF - Main valuation model with sensitivity analysis at bottom
WACC - Cost of capital calculation

CRITICAL: Sensitivity tables go at the BOTTOM of the DCF sheet (not on a separate sheet). This keeps all valuation outputs together.

创建两个工作表：

DCF - 主要估值模型，底部包含敏感性分析
WACC - 资本成本计算

关键要求：敏感性表格放在DCF工作表的底部（而非单独的工作表）。这使所有估值输出集中在一起。

Formula Recalculation (MANDATORY)

公式重计算（强制性）

After creating or modifying the Excel model, recalculate all formulas using the recalc.py script from the xlsx skill:

bash

python recalc.py [path_to_excel_file] [timeout_seconds]

Example:

bash

python recalc.py AAPL_DCF_Model_2025-10-12.xlsx 30

The script will:

Recalculate all formulas in all sheets using LibreOffice
Scan ALL cells for Excel errors (#REF!, #DIV/0!, #VALUE!, #NAME?, #NULL!, #NUM!, #N/A)
Return detailed JSON with error locations and counts

Expected output format:

json

{
  "status": "success",           // or "errors_found"
  "total_errors": 0,              // Total error count
  "total_formulas": 42,           // Number of formulas in file
  "error_summary": {}             // Only present if errors found
}

If errors are found, the output will include details:

json

{
  "status": "errors_found",
  "total_errors": 2,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {
      "count": 2,
      "locations": ["DCF!B25", "DCF!C25"]
    }
  }
}

Fix all errors and re-run recalc.py until status is "success" before delivering the model.

创建或修改Excel模型后，使用xlsx技能的recalc.py脚本重新计算所有公式：

bash

python recalc.py [path_to_excel_file] [timeout_seconds]

示例：

bash

python recalc.py AAPL_DCF_Model_2025-10-12.xlsx 30

该脚本将：

使用LibreOffice重新计算所有工作表中的所有公式
扫描所有单元格查找Excel错误（#REF!, #DIV/0!, #VALUE!, #NAME?, #NULL!, #NUM!, #N/A）
返回包含错误位置和数量的详细JSON

预期输出格式：

json

{
  "status": "success",           // 或 "errors_found"
  "total_errors": 0,              // 总错误数
  "total_formulas": 42,           // 文件中的公式数量
  "error_summary": {}             // 仅在发现错误时存在
}

如果发现错误，输出将包含详细信息：

json

{
  "status": "errors_found",
  "total_errors": 2,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {
      "count": 2,
      "locations": ["DCF!B25", "DCF!C25"]
    }
  }
}

修复所有错误，并重新运行recalc.py直到状态为"success"，然后再交付模型。

Formatting Standards

格式标准

IMPORTANT: Follow the xlsx skill for formula construction rules and number formatting conventions. The DCF skill adds specific visual presentation standards.

Color Scheme - Two Layers:

Layer 1: Font Colors (MANDATORY from xlsx skill)

Blue text (RGB: 0,0,255): ALL hardcoded inputs (stock price, shares, historical data, assumptions)
Black text (RGB: 0,0,0): ALL formulas and calculations
Green text (RGB: 0,128,0): Links to other sheets (WACC sheet references)

Layer 2: Fill Colors — Professional Blue/Grey Palette (Default unless user specifies otherwise)

Keep it minimal — use only blues and greys for fills. Do NOT introduce greens, yellows, oranges, or multiple accent colors. A model with too many colors looks amateurish.
Default fill palette:
- Section headers: Dark blue (RGB: 31,78,121 /
```
#1F4E79
```
  ) background with white bold text
- Sub-headers/column headers: Light blue (RGB: 217,225,242 /
```
#D9E1F2
```
  ) background with black bold text
- Input cells: Light grey (RGB: 242,242,242 /
```
#F2F2F2
```
  ) background with blue font — or just white with blue font if you want maximum minimalism
- Calculated cells: White background with black font
- Output/summary rows (per-share value, EV, etc.): Medium blue (RGB: 189,215,238 /
```
#BDD7EE
```
  ) background with black bold font
That's it — 3 blues + 1 grey + white. Resist the urge to add more.
User-provided templates or explicit color preferences ALWAYS override these defaults.

How the layers work together:

Input cell: Blue font + light grey fill = "Hardcoded input"
Formula cell: Black font + white background = "Calculated value"
Sheet link: Green font + white background = "Reference from another sheet"
Key output: Black bold font + medium blue fill = "This is the answer"

Font color tells you WHAT it is (input/formula/link). Fill color tells you WHERE you are (header/data/output).

重要提示：遵循xlsx技能的公式构建规则和数字格式约定。DCF技能添加了特定的视觉呈现标准。

配色方案 - 两层：

第一层：字体颜色（xlsx技能强制性要求）

蓝色文本（RGB: 0,0,255）：所有硬编码输入（股价、股份数、历史数据、假设）
黑色文本（RGB: 0,0,0）：所有公式和计算结果
绿色文本（RGB: 0,128,0）：指向其他工作表的链接（WACC工作表引用）

第二层：填充颜色 — 专业蓝灰调色板（默认，除非用户指定）

保持简洁 — 仅使用蓝色和灰色填充。请勿引入绿色、黄色、橙色或多种强调色。颜色过多的模型看起来很业余。
默认填充调色板：
- 章节标题：深蓝色（RGB: 31,78,121 /
```
#1F4E79
```
  ）背景，白色粗体文本
- 子标题/列标题：浅蓝色（RGB: 217,225,242 /
```
#D9E1F2
```
  ）背景，黑色粗体文本
- 输入单元格：浅灰色（RGB: 242,242,242 /
```
#F2F2F2
```
  ）背景，蓝色字体 — 若追求极简风格，也可使用白色背景+蓝色字体
- 计算单元格：白色背景，黑色字体
- 输出/汇总行（每股价值、EV等）：中等蓝色（RGB: 189,215,238 /
```
#BDD7EE
```
  ）背景，黑色粗体文本
仅此而已 — 3种蓝色 + 1种灰色 + 白色。请勿添加更多颜色。
用户提供的模板或明确的颜色偏好始终优先于这些默认设置。

两层的配合方式：

输入单元格：蓝色字体 + 浅灰色填充 = "硬编码输入"
公式单元格：黑色字体 + 白色背景 = "计算值"
工作表链接：绿色字体 + 白色背景 = "引用自其他工作表"
关键输出：黑色粗体字体 + 中等蓝色填充 = "这是结果"

字体颜色告诉你它是什么（输入/公式/链接）。填充颜色告诉你它在哪里（标题/数据/输出）。

Border Standards (REQUIRED for Professional Appearance)

边框标准（专业外观必需）

Thick borders (1.5pt) around major sections:

KEY INPUTS section
PROJECTION ASSUMPTIONS section
5-YEAR CASH FLOW PROJECTION section
TERMINAL VALUE section
VALUATION SUMMARY section
Each SENSITIVITY ANALYSIS table

Medium borders (1pt) between sub-sections:

Company Details vs Historical Performance
Growth Assumptions vs EBIT Margin vs FCF Parameters

Thin borders (0.5pt) around data tables:

Scenario assumption tables (Bear | Base | Bull | Selected)
Historical vs projected financials matrix

No borders: Individual cells within tables (keep clean, scannable)

Borders are mandatory - models without professional borders are not client-ready.

Number Formats (follows xlsx skill standards):

Years: Format as text strings (e.g., "2024" not "2,024")
Percentages:
```
0.0%
```
(one decimal place)
Currency:
```
$#,##0
```
for millions;
```
$#,##0.00
```
for per-share - ALWAYS specify units in headers ("Revenue ($mm)")
Zeros: Use number formatting to make all zeros "-" (e.g.,
```
$#,##0;($#,##0);-
```
)
Large numbers:
```
#,##0
```
with thousands separator
Negative numbers:
```
(#,##0)
```
in parentheses (NOT minus sign)

Cell Comments (MANDATORY for all hardcoded inputs):

Per the xlsx skill, ALL hardcoded values must have cell comments documenting the source. Format: "Source: [System/Document], [Date], [Reference], [URL if applicable]"

CRITICAL: Add comments AS CELLS ARE CREATED. Do not defer to the end.

粗边框（1.5pt）围绕主要章节：

KEY INPUTS章节
PROJECTION ASSUMPTIONS章节
5-YEAR CASH FLOW PROJECTION章节
TERMINAL VALUE章节
VALUATION SUMMARY章节
每个SENSITIVITY ANALYSIS表格

中等边框（1pt）用于子章节之间：

公司详情 vs 历史表现
增长假设 vs EBIT利润率 vs FCF参数

细边框（0.5pt）围绕数据表格：

场景假设表格（熊市 | 基准 | 牛市 | 选定）
历史 vs 预测财务数据矩阵

无边框：表格内的单个单元格（保持整洁、易读）

边框是强制性的 - 没有专业边框的模型不符合客户交付标准。

数字格式（遵循xlsx技能标准）：

年份：格式为文本字符串（例如"2024"而非"2,024"）
百分比：
```
0.0%
```
（一位小数）
货币：百万单位使用
```
$#,##0
```
；每股使用
```
$#,##0.00
```
— 始终在标题中指定单位（"Revenue ($mm)"）
零值：使用数字格式将所有零显示为"-"（例如
```
$#,##0;($#,##0);-
```
）
大数：使用千位分隔符的
```
#,##0
```
负数：用括号表示（
```
(#,##0)
```
，而非减号）

单元格注释（所有硬编码输入必需）：

根据xlsx技能要求，所有硬编码值必须有单元格注释记录来源。格式："Source: [System/Document], [Date], [Reference], [URL if applicable]"

关键要求：创建单元格时添加注释。请勿延迟到最后添加。

DCF Sheet Detailed Structure

DCF工作表详细结构

Section 1: Header

csv

Row,Content
1,[Company Name] DCF Model
2,Ticker: [XXX] | Date: [Date] | Year End: [FYE]
3,Blank
4,Case Selector Cell (1=Bear 2=Base 3=Bull)
5,Case Name Display (formula: =IF([Selector]=1"Bear"IF([Selector]=2"Base""Bull")))

Section 2: Market Data (NOT case dependent)

csv

Item,Value
Current Stock Price,$XX.XX
Shares Outstanding (M),XX.X
Market Cap ($M),[Formula]
Net Debt ($M),XXX [or Net Cash if negative]

Section 3: DCF Scenario Assumptions

Create separate assumption blocks for each scenario (Bear, Base, Bull) with DCF-specific assumptions (Revenue Growth %, EBIT Margin %, Tax Rate %, D&A % of Revenue, CapEx % of Revenue, NWC Change % of ΔRev, Terminal Growth Rate, WACC) laid out horizontally across projection years. Each block must include section header, column header row showing the projection years (FY1, FY2, etc.), and data rows. See

<correct_patterns>

section "Correct Assumption Table Structure" for the exact layout.

Section 4: Historical & Projected Financials

Reference a consolidation column (e.g., "Selected Case") that pulls from scenario blocks, not scattered IF formulas in every projection row.

csv

Income Statement ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
Revenue,XXX,XXX,XXX,XXX,[=E29*(1+$E$10)],[=F29*(1+$E$11)],[=G29*(1+$E$12)]
  % growth,XX%,XX%,XX%,XX%,[=E29/D29-1],[=F29/E29-1],[=G29/F29-1]
,,,,,,
Gross Profit,XXX,XXX,XXX,XXX,[=E29*E33],[=F29*F33],[=G29*G33]
  % margin,XX%,XX%,XX%,XX%,[=E33/E29],[=F33/F29],[=G33/G29]
,,,,,,
Operating Expenses:,,,,,,,
  S&M,XXX,XXX,XXX,XXX,[=E29*0.15],[=F29*0.14],[=G29*0.13]
  R&D,XXX,XXX,XXX,XXX,[=E29*0.12],[=F29*0.11],[=G29*0.10]
  G&A,XXX,XXX,XXX,XXX,[=E29*0.08],[=F29*0.07],[=G29*0.07]
  Total OpEx,XXX,XXX,XXX,XXX,[=E36+E37+E38],[=F36+F37+F38],[=G36+G37+G38]
,,,,,,
EBIT,XXX,XXX,XXX,XXX,[=E33-E39],[=F33-F39],[=G33-G39]
  % margin,XX%,XX%,XX%,XX%,[=E41/E29],[=F41/F29],[=G41/G29]
,,,,,,
Taxes,(XX),(XX),(XX),(XX),[=E41*$E$24],[=F41*$E$24],[=G41*$E$24]
  Tax rate,XX%,XX%,XX%,XX%,[=E43/E41],[=F43/F41],[=G43/G41]
,,,,,,
NOPAT,XXX,XXX,XXX,XXX,[=E41-E43],[=F41-F43],[=G41-G43]

Key Formula Pattern:

Revenue growth:
```
=E29*(1+$E$10)
```
where $E$10 is consolidation column for Year 1 growth

NOT:

=E29*(1+IF($B$6=1,$B$10,IF($B$6=2,$C$10,$D$10)))

This approach is cleaner, easier to audit, and prevents formula errors by centralizing the scenario logic.

Section 5: Free Cash Flow Build

CRITICAL: Verify row references point to the CORRECT assumption rows. Test formulas immediately after creation.

csv

Cash Flow ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
NOPAT,XXX,XXX,XXX,XXX,[=E45],[=F45],[=G45]
(+) D&A,XXX,XXX,XXX,XXX,[=E29*$E$21],[=F29*$E$21],[=G29*$E$21]
    % of Rev,XX%,XX%,XX%,XX%,[=E58/E29],[=F58/F29],[=G58/G29]
(-) CapEx,(XX),(XX),(XX),(XX),[=E29*$E$22],[=F29*$E$22],[=G29*$E$22]
    % of Rev,XX%,XX%,XX%,XX%,[=E60/E29],[=F60/F29],[=G60/G29]
(-) Δ NWC,(XX),(XX),(XX),(XX),[=(E29-D29)*$E$23],[=(F29-E29)*$E$23],[=(G29-F29)*$E$23]
    % of Δ Rev,XX%,XX%,XX%,XX%,[=E62/(E29-D29)],[=F62/(F29-E29)],[=G62/(G29-F29)]
,,,,,,
Unlevered FCF,XXX,XXX,XXX,XXX,[=E57+E58-E60-E62],[=F57+F58-F60-F62],[=G57+G58-G60-G62]

Row reference examples (based on layout planning):

$E$21 = D&A % assumption (consolidation column, row 21)
$E$22 = CapEx % assumption (consolidation column, row 22)
$E$23 = NWC % assumption (consolidation column, row 23)
E29 = Revenue for year (row 29)
E45 = NOPAT for year (row 45)

Before writing formulas: Confirm these row numbers match the actual layout. Test one column, then copy across.

Section 6: Discounting & Valuation

csv

DCF Valuation,2024E,2025E,2026E,2027E,2028E,Terminal
Unlevered FCF ($M),XXX,XXX,XXX,XXX,XXX,
Period,0.5,1.5,2.5,3.5,4.5,
Discount Factor,0.XX,0.XX,0.XX,0.XX,0.XX,
PV of FCF ($M),XXX,XXX,XXX,XXX,XXX,
,,,,,,
Terminal FCF ($M),,,,,,,XXX
Terminal Value ($M),,,,,,,XXX
PV Terminal Value ($M),,,,,,,XXX
,,,,,,
Valuation Summary ($M),,,,,,
Sum of PV FCFs,XXX,,,,,
PV Terminal Value,XXX,,,,,
Enterprise Value,XXX,,,,,
(-) Net Debt,(XX),,,,,
Equity Value,XXX,,,,,
,,,,,,
Shares Outstanding (M),XX.X,,,,,
IMPLIED PRICE PER SHARE,$XX.XX,,,,,
Current Stock Price,$XX.XX,,,,,
Implied Upside/(Downside),XX%,,,,,

章节1：页眉

csv

Row,Content
1,[Company Name] DCF Model
2,Ticker: [XXX] | Date: [Date] | Year End: [FYE]
3,Blank
4,Case Selector Cell (1=Bear 2=Base 3=Bull)
5,Case Name Display (formula: =IF([Selector]=1"Bear"IF([Selector]=2"Base""Bull")))

章节2：市场数据（与场景无关）

csv

Item,Value
Current Stock Price,$XX.XX
Shares Outstanding (M),XX.X
Market Cap ($M),[Formula]
Net Debt ($M),XXX [或Net Cash（若为负）]

章节3：DCF场景假设

为每个场景（熊市、基准、牛市）创建独立的假设块，包含DCF特定假设（收入增长率%、EBIT利润率%、税率%、D&A占收入比例、CapEx占收入比例、NWC变化占收入增量比例、终值增长率、WACC），按预测年份横向排列。每个块必须包含章节标题、显示预测年份（FY1、FY2等）的列标题行和数据行。请参阅<correct_patterns>部分的“正确的假设表格结构”获取确切布局。

章节4：历史与预测财务数据

引用合并列（例如"Selected Case"）从场景块中提取数据，而非在每个预测行中使用分散的IF公式。

csv

Income Statement ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
Revenue,XXX,XXX,XXX,XXX,[=E29*(1+$E$10)],[=F29*(1+$E$11)],[=G29*(1+$E$12)]
  % growth,XX%,XX%,XX%,XX%,[=E29/D29-1],[=F29/E29-1],[=G29/F29-1]
,,,,,,
Gross Profit,XXX,XXX,XXX,XXX,[=E29*E33],[=F29*F33],[=G29*G33]
  % margin,XX%,XX%,XX%,XX%,[=E33/E29],[=F33/F29],[=G33/G29]
,,,,,,
Operating Expenses:,,,,,,,
  S&M,XXX,XXX,XXX,XXX,[=E29*0.15],[=F29*0.14],[=G29*0.13]
  R&D,XXX,XXX,XXX,XXX,[=E29*0.12],[=F29*0.11],[=G29*0.10]
  G&A,XXX,XXX,XXX,XXX,[=E29*0.08],[=F29*0.07],[=G29*0.07]
  Total OpEx,XXX,XXX,XXX,XXX,[=E36+E37+E38],[=F36+F37+F38],[=G36+G37+G38]
,,,,,,
EBIT,XXX,XXX,XXX,XXX,[=E33-E39],[=F33-F39],[=G33-G39]
  % margin,XX%,XX%,XX%,XX%,[=E41/E29],[=F41/F29],[=G41/G29]
,,,,,,
Taxes,(XX),(XX),(XX),(XX),[=E41*$E$24],[=F41*$E$24],[=G41*$E$24]
  Tax rate,XX%,XX%,XX%,XX%,[=E43/E41],[=F43/F41],[=G43/G41]
,,,,,,
NOPAT,XXX,XXX,XXX,XXX,[=E41-E43],[=F41-F43],[=G41-G43]

关键公式模式：

收入增长：
```
=E29*(1+$E$10)
```
，其中$E$10是第1年增长率的合并列

请勿使用：

=E29*(1+IF($B$6=1,$B$10,IF($B$6=2,$C$10,$D$10)))

这种方法更简洁、更易于审计，通过集中场景逻辑防止公式错误。

章节5：自由现金流构建

关键要求：验证行引用指向正确的假设行。公式创建后立即测试。

csv

Cash Flow ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
NOPAT,XXX,XXX,XXX,XXX,[=E45],[=F45],[=G45]
(+) D&A,XXX,XXX,XXX,XXX,[=E29*$E$21],[=F29*$E$21],[=G29*$E$21]
    % of Rev,XX%,XX%,XX%,XX%,[=E58/E29],[=F58/F29],[=G58/G29]
(-) CapEx,(XX),(XX),(XX),(XX),[=E29*$E$22],[=F29*$E$22],[=G29*$E$22]
    % of Rev,XX%,XX%,XX%,XX%,[=E60/E29],[=F60/F29],[=G60/G29]
(-) Δ NWC,(XX),(XX),(XX),(XX),[=(E29-D29)*$E$23],[=(F29-E29)*$E$23],[=(G29-F29)*$E$23]
    % of Δ Rev,XX%,XX%,XX%,XX%,[=E62/(E29-D29)],[=F62/(F29-E29)],[=G62/(G29-F29)]
,,,,,,
Unlevered FCF,XXX,XXX,XXX,XXX,[=E57+E58-E60-E62],[=F57+F58-F60-F62],[=G57+G58-G60-G62]

行引用示例（基于布局规划）：

$E$21 = D&A比例假设（合并列，第21行）
$E$22 = CapEx比例假设（合并列，第22行）
$E$23 = NWC比例假设（合并列，第23行）
E29 = 当年收入（第29行）
E45 = 当年NOPAT（第45行）

写入公式前：确认这些行号与实际布局匹配。测试一列，然后复制到其他列。

章节6：折现与估值

csv

DCF Valuation,2024E,2025E,2026E,2027E,2028E,Terminal
Unlevered FCF ($M),XXX,XXX,XXX,XXX,XXX,
Period,0.5,1.5,2.5,3.5,4.5,
Discount Factor,0.XX,0.XX,0.XX,0.XX,0.XX,
PV of FCF ($M),XXX,XXX,XXX,XXX,XXX,
,,,,,,
Terminal FCF ($M),,,,,,,XXX
Terminal Value ($M),,,,,,,XXX
PV Terminal Value ($M),,,,,,,XXX
,,,,,,
Valuation Summary ($M),,,,,,
Sum of PV FCFs,XXX,,,,,
PV Terminal Value,XXX,,,,,
Enterprise Value,XXX,,,,,
(-) Net Debt,(XX),,,,,
Equity Value,XXX,,,,,
,,,,,,
Shares Outstanding (M),XX.X,,,,,
IMPLIED PRICE PER SHARE,$XX.XX,,,,,
Current Stock Price,$XX.XX,,,,,
Implied Upside/(Downside),XX%,,,,,

WACC Sheet Structure

WACC工作表结构

csv

COST OF EQUITY CALCULATION,,
Risk-Free Rate (10Y Treasury),X.XX%,[Yellow input]
Beta (5Y monthly),X.XX,[Yellow input]
Equity Risk Premium,X.XX%,[Yellow input]
Cost of Equity,X.XX%,[Calculated blue]
,,
COST OF DEBT CALCULATION,,
Credit Rating,AA-,[Yellow input]
Pre-Tax Cost of Debt,X.XX%,[Yellow input]
Tax Rate,XX.X%,[Link to DCF sheet]
After-Tax Cost of Debt,X.XX%,[Calculated blue]
,,
CAPITAL STRUCTURE,,
Current Stock Price,$XX.XX,[Link to DCF]
Shares Outstanding (M),XX.X,[Link to DCF]
Market Capitalization ($M),"X,XXX",[Calculated]
,,
Total Debt ($M),XXX,[Yellow input]
Cash & Equivalents ($M),XXX,[Yellow input]
Net Debt ($M),XXX,[Calculated]
,,
Enterprise Value ($M),"X,XXX",[Calculated]
,,
WACC CALCULATION,Weight,Cost,Contribution
Equity,XX.X%,X.X%,X.XX%
Debt,XX.X%,X.X%,X.XX%
,,
WEIGHTED AVERAGE COST OF CAPITAL,X.XX%,[Green output]

Key WACC Formulas:

Market Cap = Price × Shares
Net Debt = Total Debt - Cash
Enterprise Value = Market Cap + Net Debt
Equity Weight = Market Cap / EV
Debt Weight = Net Debt / EV
WACC = (Cost of Equity × Equity Weight) + (After-tax Cost of Debt × Debt Weight)

csv

COST OF EQUITY CALCULATION,,
Risk-Free Rate (10Y Treasury),X.XX%,[Yellow input]
Beta (5Y monthly),X.XX,[Yellow input]
Equity Risk Premium,X.XX%,[Yellow input]
Cost of Equity,X.XX%,[Calculated blue]
,,
COST OF DEBT CALCULATION,,
Credit Rating,AA-,[Yellow input]
Pre-Tax Cost of Debt,X.XX%,[Yellow input]
Tax Rate,XX.X%,[Link to DCF sheet]
After-Tax Cost of Debt,X.XX%,[Calculated blue]
,,
CAPITAL STRUCTURE,,
Current Stock Price,$XX.XX,[Link to DCF]
Shares Outstanding (M),XX.X,[Link to DCF]
Market Capitalization ($M),"X,XXX",[Calculated]
,,
Total Debt ($M),XXX,[Yellow input]
Cash & Equivalents ($M),XXX,[Yellow input]
Net Debt ($M),XXX,[Calculated]
,,
Enterprise Value ($M),"X,XXX",[Calculated]
,,
WACC CALCULATION,Weight,Cost,Contribution
Equity,XX.X%,X.X%,X.XX%
Debt,XX.X%,X.X%,X.XX%
,,
WEIGHTED AVERAGE COST OF CAPITAL,X.XX%,[Green output]

关键WACC公式：

Market Cap = Price × Shares
Net Debt = Total Debt - Cash
Enterprise Value = Market Cap + Net Debt
Equity Weight = Market Cap / EV
Debt Weight = Net Debt / EV
WACC = (Cost of Equity × Equity Weight) + (After-tax Cost of Debt × Debt Weight)

Sensitivity Analysis (Bottom of DCF Sheet)

敏感性分析（DCF工作表底部）

TERMINOLOGY REMINDER: "Sensitivity tables" = simple 2D grids with row headers, column headers, and formulas in each data cell. NOT Excel's "Data Table" feature (Data → What-If Analysis → Data Table). You will use openpyxl to write regular Excel formulas into each cell.

Location: Rows 87+ on DCF sheet (NOT a separate sheet)

Three sensitivity tables, vertically stacked:

WACC vs Terminal Growth (rows 87-100) - 5x5 grid = 25 cells with formulas
Revenue Growth vs EBIT Margin (rows 102-115) - 5x5 grid = 25 cells with formulas
Beta vs Risk-Free Rate (rows 117-130) - 5x5 grid = 25 cells with formulas

Total formulas to write: 75 (this is required, not optional)

CRITICAL: All sensitivity table cells must be populated programmatically with formulas using openpyxl. DO NOT use linear approximation shortcuts. DO NOT leave placeholder text or notes about manual steps. DO NOT rationalize leaving cells empty because "it's complex" - use a Python loop to generate the formulas.

Table Setup:

Create table structure with row/column headers (the assumption values to test)
Populate EVERY data cell with a formula that:
- Uses the row header value (e.g., WACC = 9.0%)
- Uses the column header value (e.g., Terminal Growth = 3.0%)
- Recalculates the full DCF with those specific assumptions
- Returns the implied share price for that scenario
All cells must contain working formulas when delivered
Format cells with conditional formatting: Green scale for higher values, red scale for lower values
Bold the base case cell
Leave 1-2 blank rows between tables

No manual intervention required - the sensitivity tables must be fully functional when the user opens the file.

术语提醒：“敏感性表格”=包含行标题、列标题和每个数据单元格都有公式的简单二维网格。不是Excel的“数据表格”功能（Data → What-If Analysis → Data Table）。你将使用openpyxl在每个单元格中写入常规Excel公式。

位置：DCF工作表的第87行及以下（而非单独工作表）

三个敏感性表格，垂直堆叠：

WACC vs 终值增长率（第87-100行）- 5x5网格=25个带公式的单元格
收入增长率 vs EBIT利润率（第102-115行）- 5x5网格=25个带公式的单元格
Beta vs 无风险利率（第117-130行）- 5x5网格=25个带公式的单元格

需写入的公式总数：75个（这是必需的，而非可选）

关键要求：所有敏感性表格单元格必须使用openpyxl以编程方式填充公式。请勿使用线性近似捷径。请勿留下占位文本或关于手动步骤的注释。请勿以“太复杂”为理由留空单元格 - 使用Python循环生成公式。

表格设置：

创建带有行/列标题（要测试的假设值）的表格结构
为每个数据单元格填充公式，该公式：
- 使用行标题值（例如WACC=9.0%）
- 使用列标题值（例如终值增长率=3.0%）
- 使用这些特定假设重新计算完整的DCF
- 返回该场景的隐含股价
交付时所有单元格必须包含可用公式
使用条件格式设置单元格：高值使用绿色渐变，低值使用红色渐变
基准场景单元格加粗
表格之间留1-2个空白行

无需手动干预 - 用户打开文件时敏感性表格必须完全可用。

Case Selector Implementation

场景选择器实现

Three-Case Framework:

三场景框架：

Bear Case

熊市场景

Conservative revenue growth (low end of historical range)
Margin compression or no expansion
Higher WACC (risk premium increase)
Lower terminal growth rate
Higher CapEx assumptions

保守的收入增长率（历史范围下限）
利润率压缩或无扩张
更高的WACC（风险溢价增加）
更低的终值增长率
更高的CapEx假设

Base Case

基准场景

Consensus or management guidance revenue growth
Moderate margin expansion based on operating leverage
Current market-implied WACC
GDP-aligned terminal growth (2.5-3.0%)
Standard CapEx assumptions

共识或管理层指引的收入增长率
基于经营杠杆的适度利润率扩张
当前市场隐含的WACC
与GDP一致的终值增长率（2.5-3.0%）
标准CapEx假设

Bull Case

牛市场景

Optimistic revenue growth (high end of projections)
Significant margin expansion
Lower WACC (reduced risk premium)
Higher terminal growth (3.5-5.0%)
Reduced CapEx intensity

Formula Implementation:

DO NOT use nested IF formulas scattered throughout. Instead, create a consolidation column that uses INDEX or OFFSET formulas to pull from the appropriate scenario block.

Recommended pattern (using INDEX):

=INDEX(B10:D10, 1, $B$6)

where

B10:D10

= Bear/Base/Bull values,

= row offset,

$B$6

= case selector cell (1, 2, or 3)

Then reference the consolidation column in all projections:

Revenue Year 1: =D29*(1+$E$10)

where $E$10 is the consolidation column value for Year 1 growth.

This approach centralizes scenario logic, making the model easier to audit and maintain.

乐观的收入增长率（预测范围上限）
显著的利润率扩张
更低的WACC（风险溢价降低）
更高的终值增长率（3.5-5.0%）
更低的CapEx密集度

公式实现：

请勿在各处使用嵌套IF公式。相反，创建一个合并列，使用INDEX或OFFSET公式从相应的场景块中提取数据。

推荐模式（使用INDEX）：

=INDEX(B10:D10, 1, $B$6)

，其中

B10:D10

=熊市/基准/牛市的值，

=行偏移，

$B$6

=场景选择单元格（1、2或3）

然后引用合并列进行所有预测：

Revenue Year 1: =D29*(1+$E$10)

，其中$E$10是第1年增长率的合并列值。

这种方法集中了场景逻辑，使模型更易于审计和维护。

Deliverables Structure

交付物结构

File naming:

[Ticker]_DCF_Model_[Date].xlsx

Two sheets:

DCF - Complete model with Bear/Base/Bull cases + three sensitivity tables at bottom (WACC vs Terminal Growth, Revenue Growth vs EBIT Margin, Beta vs Risk-Free Rate)
WACC - Cost of capital calculation

Key features: Case selector (1/2/3), consolidation column with INDEX/OFFSET formulas, color-coded cells, cell comments on all inputs, professional borders

文件命名：

[Ticker]_DCF_Model_[Date].xlsx

两个工作表：

DCF - 完整模型，包含熊市/基准/牛市场景 + 底部三个敏感性表格（WACC vs 终值增长率、收入增长率 vs EBIT利润率、Beta vs 无风险利率）
WACC - 资本成本计算

关键特性：场景选择器（1/2/3）、带有INDEX/OFFSET公式的合并列、颜色编码单元格、所有输入的单元格注释、专业边框

Best Practices

最佳实践

Model Construction

模型构建

Build incrementally: Complete each section before moving to next
Test as building: Enter sample numbers to verify formulas
Use consistent structure: Similar calculations follow similar patterns
Comment complex formulas: Add notes for unusual calculations
Build in checks: Sum checks and balance checks where applicable

增量构建：完成每个章节后再进入下一章节
边构建边测试：输入示例数据验证公式
使用一致结构：类似计算遵循类似模式
为复杂公式添加注释：为不寻常的计算添加说明
内置检查：适用时添加求和检查和平衡检查

Documentation

文档

Document all assumptions: Explain reasoning behind key inputs
Cite data sources: Note where each data point came from
Explain methodology: Describe any non-standard approaches
Flag uncertainties: Highlight areas with limited visibility

记录所有假设：解释关键输入的理由
引用数据源：记录每个数据点的来源
解释方法：描述任何非标准方法
标记不确定性：突出显示可见性有限的领域

Quality Control

质量控制

Cross-check calculations: Verify math in multiple ways
Stress test assumptions: Run sensitivity to ensure model is robust
Peer review: Have someone else check formulas
Version control: Save versions as work progresses

交叉核对计算：通过多种方式验证数学计算
压力测试假设：运行敏感性分析确保模型稳健
同行评审：让他人检查公式
版本控制：保存工作进展的版本

Common Variations

常见变体

High-Growth Technology Companies

高增长科技公司

Longer projection period (7-10 years)
Higher initial growth rates (20-30%)
Significant margin expansion over time
Higher WACC (12-15%)
Model unit economics (users, ARPU, etc.)

更长的预测期（7-10年）
更高的初始增长率（20-30%）
随时间显著的利润率扩张
更高的WACC（12-15%）
建模单位经济（用户、ARPU等）

Mature/Stable Companies

成熟/稳定公司

Shorter projection period (3-5 years)
Modest growth rates (GDP +1-3%)
Stable margins
Lower WACC (7-9%)
Focus on cash generation and capital allocation

更短的预测期（3-5年）
适度的增长率（GDP+1-3%）
稳定的利润率
更低的WACC（7-9%）
关注现金生成和资本配置

Cyclical Companies

周期性公司

Model through economic cycle
Normalize margins at mid-cycle
Consider trough and peak scenarios
Adjust beta for cyclicality

跨经济周期建模
将利润率归一化至周期中期
考虑低谷和高峰场景
针对周期性调整Beta

Multi-Segment Companies

多业务板块公司

Separate DCFs for each business unit
Different growth rates and margins by segment
Sum-of-parts valuation
Consider synergies

为每个业务单元创建单独的DCF
不同板块使用不同的增长率和利润率
分部加总估值
考虑协同效应

Troubleshooting

故障排除

If you encounter errors or unreasonable results, read TROUBLESHOOTING.md for detailed debugging guidance.

如果遇到错误或不合理的结果，请阅读TROUBLESHOOTING.md获取详细的调试指导。

Workflow Integration

工作流集成

At Start of DCF Build

DCF构建开始时

Gather market data:
- Check for available MCP servers for current market data
- Use web search/fetch for stock prices, beta, and other market metrics
- Request from user if specific data is needed
Gather historical financials:
- Check for available MCP servers (Daloopa, etc.)
- Request from user if not available via MCP
- Manual extraction from 10-Ks if necessary
Begin model construction using the DCF methodology detailed in this skill

收集市场数据：
- 检查可用的MCP服务器获取当前市场数据
- 使用网络搜索/获取股价、Beta和其他市场指标
- 若需要特定数据，向用户请求
收集历史财务数据：
- 检查可用的MCP服务器（如Daloopa）
- 若MCP无法获取，向用户请求
- 必要时手动从10-K文件中提取
开始模型构建，使用本技能详细描述的DCF方法

During Model Construction

模型构建过程中

Build Excel model using openpyxl with formulas (not hardcoded values)
Follow xlsx skill conventions for formula construction and formatting
Apply fill colors only if requested by user or if specific brand guidelines are provided

使用openpyxl构建Excel模型，使用公式（而非硬编码值）
遵循xlsx技能约定进行公式构建和格式设置
仅在用户要求时应用填充颜色，或遵循特定品牌指南

Before Delivering Model (MANDATORY)

交付模型前（强制性）

Verify structure:
- Scenario blocks for Bear/Base/Bull with assumptions across projection years
- Case selector functional with formulas referencing correct scenario blocks
- Sensitivity tables at bottom of DCF sheet (not separate sheet)
- Font colors: Blue inputs, black formulas, green sheet links
- Cell comments on ALL hardcoded inputs
- Professional borders around major sections
Recalculate formulas: Run
```
python recalc.py model.xlsx 30
```
Check output:
- If
```
status
```
  is
```
"success"
```
  → Continue to step 4
- If
```
status
```
  is
```
"errors_found"
```
  → Check
```
error_summary
```
  and read TROUBLESHOOTING.md for debugging guidance
Fix errors and re-run recalc.py until status is "success"
Spot-check formulas:
- Test one FCF formula - does it reference the correct assumption rows?
- Change case selector - does the consolidation column update properly?
- Verify revenue formulas reference consolidation column (not nested IF formulas)
Deliver model

验证结构：
- 包含熊市/基准/牛市场景块，假设覆盖所有预测年份
- 场景选择器可用，公式引用正确的场景块
- 敏感性表格在DCF工作表底部（而非单独工作表）
- 字体颜色：蓝色=输入，黑色=公式，绿色=工作表链接
- 所有硬编码输入都有单元格注释
- 主要章节周围有专业边框
重新计算公式：运行
```
python recalc.py model.xlsx 30
```
检查输出：
- 如果
```
status
```
  为
```
"success"
```
  → 继续步骤4
- 如果
```
status
```
  为
```
"errors_found"
```
  → 查看
```
error_summary
```
  并阅读TROUBLESHOOTING.md获取调试指导
修复错误并重新运行recalc.py直到状态为"success"
抽查公式：
- 测试一个FCF公式 - 它是否引用了正确的假设行？
- 更改场景选择器 - 合并列是否正确更新？
- 验证收入公式引用合并列（而非嵌套IF公式）
交付模型

Available Data Sources

可用数据源

MCP servers: If configured (Daloopa for historical financials)
Web search/fetch: For current stock prices, beta, and market data
User-provided data: Historical financials, consensus estimates
Manual extraction: SEC EDGAR filings as fallback

MCP服务器：若已配置（Daloopa用于历史财务数据）
网络搜索/获取：用于当前股价、Beta和市场数据
用户提供的数据：历史财务数据、共识预测
手动提取：SEC EDGAR文件作为备选

Final Output Checklist

最终输出检查清单

Before delivering DCF model:

Required:

Run
```
python recalc.py model.xlsx 30
```
until status is "success" (zero formula errors)
Two sheets: DCF (with sensitivity at bottom), WACC
Font colors: Blue=inputs, Black=formulas, Green=sheet links
Cell comments on ALL hardcoded inputs
Sensitivity tables fully populated with formulas
Professional borders around major sections

Validation:

OpEx based on revenue (not gross profit)
Terminal value 50-70% of EV
Terminal growth < WACC
Tax rate 21-28%
File naming:
```
[Ticker]_DCF_Model_[Date].xlsx
```

交付DCF模型前：

必需项：

运行
```
python recalc.py model.xlsx 30
```
直到状态为"success"（零公式错误）
两个工作表：DCF（底部带敏感性分析）、WACC
字体颜色：蓝色=输入，黑色=公式，绿色=工作表链接
所有硬编码输入都有单元格注释
敏感性表格完全填充公式
主要章节周围有专业边框

验证项：

运营费用基于收入（而非毛利润）
终值占EV的50-70%
终值增长率 < WACC
税率为21-28%
文件命名：
```
[Ticker]_DCF_Model_[Date].xlsx
```