Stock Valuation Report Engine (decision-grade complete workbook)
When the user asks to "value this stock" / "is it worth buying" / "compare A and B" / "write an investment memo", what you generate is amulti-sheet Excel workbook containing a complete valuation logic chain — from data validation to the final investment conclusion, every step auditable and parameter-adjustable.
This Skill is used in conjunction with financial-modeling :financial-modeling is responsible for the concrete formula implementation of DCF/P&L; this Skill is responsible for thevaluation decision framework and report structure。
Workbook structure (7 sheets)
| # | Sheet name | Core content |
|---|---|---|
| 1 | Data Overview | Data source / date / quality score / confidence level |
| 2 | Core Assumptions | Bull/Base/Bear three-scenario parameter panel |
| 3 | Relative Valuation | P/E / P/B / EV/EBITDA peer comparison table |
| 4 | DCF Model | FCF forecast + discounting + terminal value (using the financial-modeling specification) |
| 5 | Quality Assessment | 6-dimension enterprise quality scoring matrix |
| 6 | Risk Matrix | Risk event × probability × impact × monitoring trigger |
| 7 | Valuation Conclusion | Triangulation + margin of safety + investment conclusion dashboard |
Sheet 1: Data Overview and Quality Gating
This is the model's "entry check" — validate data quality first, then do the valuation:
// 数据质量评分（公式驱动）
// 完整性得分 = 已填字段数 / 总必填字段数
ws.Range("C8").Formula = "=COUNTA(C12:C25)/14";
ws.Range("C8").NumberFormat = "0%";
// 时效性标记
ws.Range("C9").Value2 = "2024-12-31"; // 最新财报期（蓝色可编辑）
ws.Range("D9").Formula = '=IF(TODAY()-DATEVALUE(C9)>180,"⚠ 超过6个月","✓ 近期")';
// 置信度等级（自动判断）
ws.Range("C10").Formula = '=IF(C8>=0.85,"High",IF(C8>=0.6,"Medium","Low"))';
// High = 可做完整估值三角验证
// Medium = 可估值但需加宽区间
// Low = 仅定性方向判断，跳过精确 DCF
// 必填数据清单（缺失项红色标记）
var fields = ["收入(TTM)","净利润","EBITDA","总资产","总负债",
"股东权益","经营现金流","CapEx","总股数","当前股价",
"Beta","行业","同行公司","最近分红"];
Key rule: If the confidence level is Low, automatically skip the DCF Sheet and generate only the relative valuation + quality assessment + qualitative conclusion.
Sheet 3: Relative Valuation Table
// 表头
var headers = ["公司","P/E(TTM)","P/E(Fwd)","P/B","EV/EBITDA","EV/Sales","ROE","股息率"];
// 目标公司行（从数据总览引用）
// 同行公司行（用户提供或AI建议3-5家）
// 行业中位数行 = MEDIAN 公式
// 隐含价值列（每个倍数一列）
ws.Range("I5").Formula = "=MEDIAN(B6:B10)*'数据总览'!$C$17"; // 隐含P/E价值
// 折溢价标记（条件格式）
// 低于中位数 10%+ → 绿色 | 高于中位数 10%+ → 红色 | 区间内 → 无色
// 估值评分
ws.Range("I12").Formula = '=COUNTIF(I5:I9,">"&\'数据总览\'!$C$20)/5';
// 高于当前股价的方法数占比 → 越高越"便宜"
Sheet 5: Quality Assessment Matrix (6 dimensions)
var dimensions = [
{ name: "护城河与定价权", weight: 0.20 },
{ name: "治理与资本配置", weight: 0.15 },
{ name: "盈利质量(现金转化)", weight: 0.20 },
{ name: "资产负债风险", weight: 0.15 },
{ name: "周期性与外部依赖", weight: 0.15 },
{ name: "执行力与历史兑现", weight: 0.15 }
];
// 每维度: 评分(1-5) + 证据描述 + 权重
// 综合质量分 = 加权平均
ws.Range("E12").Formula = "=SUMPRODUCT(C5:C10,D5:D10)";
// 1-2 = Weak | 3 = Neutral | 4-5 = Strong
// 评分为蓝色可编辑（Font.Color = 0xFF0000）
// 权重也可调
Sheet 6: Risk Matrix
// 结构: 风险事件 | 类别 | 概率(1-5) | 影响(1-5) | 风险分 | 监控触发器 | 状态
// 风险分 = 概率 × 影响（公式）
ws.Range("E5").Formula = "=C5*D5";
// 前置填充常见风险类别
var riskCategories = ["宏观/政策","行业竞争","执行/管理","财务/流动性","估值/市场情绪","ESG/合规"];
// 风险热力图配色（条件格式）
// 风险分 >= 15 → 红色 | 10-14 → 橙色 | 5-9 → 黄色 | < 5 → 绿色
Sheet 7: Valuation Conclusion Dashboard
This is the "one-page summary" of the entire workbook:
// ── 区域 A：公允价值三角验证 ──
ws.Range("C5").Formula = "='DCF 模型'!C27"; // DCF 价值
ws.Range("C6").Formula = "='相对估值'!I13"; // 相对估值中位数
ws.Range("C7").Formula = "='核心假设'!C30"; // 情景加权价值
// 综合公允价值（权重可调，蓝色）
ws.Range("C9").Formula = "=D5*C5+D6*C6+D7*C7";
ws.Range("C9").Font.Size = 18;
ws.Range("C9").Font.Bold = true;
// ── 区域 B：安全边际 ──
ws.Range("C12").Value2 = 0.20; // 安全边际折扣（蓝色可调）
ws.Range("C13").Formula = "=C9*(1-C12)"; // 安全买入价
ws.Range("C13").Interior.Color = 0x90EE90;
ws.Range("C14").Formula = "='数据总览'!C20"; // 当前股价
ws.Range("C15").Formula = "=C14/C9-1"; // 折溢价率
// ── 区域 C：投资结论 ──
ws.Range("C17").Formula = '=IF(C14<=C13,"Attractive ✓",IF(C14<=C9,"Watchlist","Caution ⚠"))';
ws.Range("C17").Font.Size = 16;
// ── 区域 D：数据仪表盘 ──
ws.Range("C20").Formula = "='数据总览'!C10"; // 置信度
ws.Range("C21").Formula = "='质量评估'!E12"; // 质量评分
ws.Range("C22").Formula = "=MAX('风险矩阵'!E5:E15)"; // 最高风险分
// ── 区域 E：触发器 ──
ws.Range("A25").Value2 = "增仓触发"; // 什么条件下增加仓位
ws.Range("A26").Value2 = "减仓触发"; // 什么条件下减少仓位
ws.Range("A27").Value2 = "论点失效"; // 什么情况下整个投资逻辑作废
ws.Range("A28").Value2 = "投资期限";
// 这四行 B 列为蓝色可编辑文本
Decision label definitions
| Label | Meaning | Condition |
|---|---|---|
| Attractive | Valuation discount + acceptable quality + controllable risk | Current price ≤ safe buy price |
| Watchlist | Mixed signals, waiting for a trigger | Safe buy price < current price ≤ fair value |
| Caution | Valuation is high or risk is too great | Current price > fair value |
Collaboration with other Skills
- financial-modeling: Concrete formula implementation of DCF calculations (this Skill handles the decision framework; financial-modeling handles the financial engine)
- data-analysis: When the user first wants to analyze historical data and then do a valuation, data-analysis handles the statistics and this Skill handles the valuation
- chart-creation: Charts in the valuation conclusion dashboard (valuation range chart, risk heatmap)
Behavior in Ask mode
In Ask mode (read-only analysis), no Excel model is generated; instead, the valuation analysis is output asstructured text:
- Executive summary (one paragraph)
- Data sources and quality
- Core thesis (Bull/Base/Bear)
- Valuation data (with figures cited)
- Quality assessment (table)
- Risk list
- Fair value range and margin of safety
- Conclusion label + confidence level
Formatting specifications
Inherit all formatting specifications of financial-modeling, with the following additions:
- Quality score 4-5 → green background | 3 → no color | 1-2 → red background
- Risk score ≥ 15 → red | 10-14 → orange | < 10 → green
- Conclusion label font size 16px, Attractive=green text, Watchlist=orange text, Caution=red text
- Dashboard areas separated by bold borders