词元之母TOK.MOM - 平台充值汇率 1:1 即 1 人民币充值到账 1 美元，支持一个 Key 调用近 600+ 海内外模型，限时特价模型低至 1 折，欢迎上岸！
"DCF 模型"
DCF 模型
Skill 元数据
| 来源 | 可选——通过 hermes skills install official/finance/dcf-model 安装 |
| 路径 | optional-skills/finance/dcf-model |
| 版本 | 1.0.0 |
| 作者 | Anthropic（由 Nous Research 改编） |
| 许可证 | Apache-2.0 |
| 平台 | linux, macos, windows |
| 标签 | finance, valuation, dcf, excel, openpyxl, modeling, investment-banking |
| 相关 skill | excel-author, pptx-author, comps-analysis, lbo-model, 3-statement-model |
参考：完整 SKILL.md
信息
环境
遵循
excel-author skill 关于单元格着色、公式、命名区域和敏感性表格的约定。交付前重新计算：
python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx。DCF 模型构建器
概述
工具
关键约束——请先阅读
ws["D20"] = "=D19*(1+$B$8)" 是正确的；ws["D20"] = calculated_revenue 是错误的#BDD7EE）+ 粗体字体，使基准情景立即可见。python recalc.py model.xlsx 30=IF($B$6=1,[Bear cell],IF($B$6=2,[Base cell],[Bull cell]))DCF 流程工作流
第 1 步：数据获取与验证
1.
2.
3.
第 2 步：历史分析（3-5 年）
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%第 3 步：构建收入预测
1.
2.
3.
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%)第 4 步：运营费用建模
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)第 5 步：自由现金流计算
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第 6 步：资本成本（WACC）研究
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)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 financialsMarket 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)第 7 步：折现率应用（5-10 年预测）
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第 8 步：终值计算
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 Value = Final Year EBITDA × Exit Multiple
Where Exit Multiple comes from:
- Industry comparable trading multiples
- Precedent transaction multiples
- Typical range: 8-15x EBITDAPV 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第 9 步：企业价值到股权价值桥接
(+) 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%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%第 10 步：敏感性分析
1.
2.
3.
情景块选择模式——遵循此方法
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%1.
2.
3.
4.
=INDEX(B10:D10, 1, $B$6)=IF($B$6=1,[Bear block cell],IF($B$6=2,[Base block cell],[Bull block cell]))正确的收入预测模式
=INDEX([Bear FY1 growth]:[Bull FY1 growth], 1, $B$6)Revenue Year 1: =D29*(1+$E$10)正确的 FCF 公式模式
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正确的单元格注释格式
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正确的假设表格结构
1.
2.
3.
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%,,,,正确的行规划流程
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
...,...正确的敏感性表格实现
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#BDD7EE）和粗体字体，以便基准情景在视觉上有明确锚点。axis_values = [base - 2*step, base - step, base, base + step, base + 2*step]——围绕基准对称，奇数个数保证有中心。$A88（8.0%）B$87（2.0%）=([SUM of PV FCFs using $A88 as discount rate] + [Terminal Value using B$87 as growth rate and $A88 as WACC] - [Net Debt]) / [Shares]错误：简化的敏感性表格近似或占位文本
// 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// 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"]"写 75+ 个公式感觉很复杂，所以我会留一个注释让用户手动完成。"
错误：缺少单元格注释
错误：公式行引用偏移
FCF 部分引用了错误的假设行：
D&A: =E29*$E$34 // Should be $E$21, but referencing wrong rowCapEx: =E29*$E$41 // Should be $E$22, but row shifted1.
2.
3.
4.
错误：每个情景中每个假设使用单行
Assumption,Bear,Base,Bull
Revenue Growth FY1,10%,13%,16%
Revenue Growth FY2,9%,12%,15%错误：无边框
错误：错误的字体颜色或无字体颜色区分
错误：运营费用基于毛利润
S&M: =E33*0.15 // E33 = Gross Profit (WRONG)S&M: =E29*0.15 // E29 = Revenue (CORRECT)前 5 大错误汇总
1.
2.
3.
4.
5.
WACC 计算错误
增长假设缺陷
终值错误
现金流预测错误
Excel 文件创建
xlsx skill 进行所有电子表格操作。 xlsx skill 提供：recalc.py 脚本自动重新计算公式质量评估标准
1.
2.
3.
4.
5.
6.
输入要求
最低必需输入
1.
2.
3.
Excel 模型结构
工作表架构
1.
2.
公式重新计算（必须执行）
excel-author skill 中的 recalc.py 脚本重新计算所有公式：{
"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
}{
"status": "errors_found",
"total_errors": 2,
"total_formulas": 42,
"error_summary": {
"#REF!": {
"count": 2,
"locations": ["DCF!B25", "DCF!C25"]
}
}
}格式标准
#1F4E79）背景，白色粗体文本#D9E1F2）背景，黑色粗体文本#F2F2F2）背景，蓝色字体——或者如果想要最大简洁性，白色背景配蓝色字体#BDD7EE）背景，黑色粗体字体边框标准（专业外观的必要要求）
0.0%（一位小数）$#,##0；每股用 $#,##0.00——始终在标题中指定单位（"Revenue ($mm)"）$#,##0;($#,##0);-）#,##0(#,##0)（不用负号）DCF 工作表详细结构
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")))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]<correct_patterns> 节中的"正确的假设表格结构"。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)，其中 10 是第 1 年增长的合并列=E29*(1+IF($B$6=1,$B$10,IF($B$6=2,$C$10,$D$10)))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]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 工作表结构
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]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)敏感性分析（DCF 工作表底部）
1.
2.
3.
1.
2.
3.
4.
5.
6.
情景选择器实现
熊市情景
基准情景
牛市情景
=INDEX(B10:D10, 1, $B$6)，其中 B10:D10 = 熊/基/牛值，1 = 行偏移，$B$6 = 情景选择器单元格（1、2 或 3）Revenue Year 1: =D29*(1+$E$10)，其中 10 是第 1 年增长的合并列值。交付物结构
[Ticker]_DCF_Model_[Date].xlsx1.
2.
最佳实践
模型构建
1.
2.
3.
4.
5.
文档
1.
2.
3.
4.
质量控制
1.
2.
3.
4.
常见变体
高增长科技公司
成熟/稳定公司
周期性公司
多业务段公司
故障排除
工作流集成
DCF 构建开始时
1.
2.
3.
模型构建期间
1.
2.
3.
交付模型前（必须执行）
1.
2.
python recalc.py model.xlsx 303.
status 为 "success" → 继续第 4 步status 为 "errors_found" → 检查 error_summary 并阅读 TROUBLESHOOTING.md 获取调试指导4.
5.
6.
可用数据来源
最终输出检查清单
python recalc.py model.xlsx 30 直到状态为"success"（零公式错误）[Ticker]_DCF_Model_[Date].xlsx数据来源——MCP 优先，网络备用
native-mcp skill），优先使用它获取时间点可比数据、先例交易和文件。https://www.sec.gov/cgi-bin/browse-edgar）使用 web_search / web_extract 获取美国文件browser_navigate 用于交互式数据门户[UNSOURCED] 并向用户说明。归属
excel-author skill 的约定面向无头 openpyxl。原始来源：https://github.com/anthropics/financial-services修改于 2026-05-31 00:25:01