【表格】AI 搭建财务模型与敏感性分析

摘要：用 AI 辅助搭一个简易财务模型，并用数据表做敏感性分析。

1. 痛点引入

做估值、做预算，最头疼的不是"算"，而是"搭"。

你大概也经历过：领导一句"给这个公司估个值"，你就得从空白表格开始，手工写五年自由现金流、算贴现、算终值，最后再加 sensitivities（敏感性分析，说人话就是"看看关键假设变一变，结果会差多少"）。一套下来几十个公式，错一个单元格，全盘重来。更崩溃的是领导又问："那如果折现率涨两个点到 12% 呢？增长率砍到 2% 呢？"——你只能一个个改、一个个抄结果。

于是很多人把希望寄托在 AI 上，心想："让 Copilot 直接给我建个模型不就完了？"

这里先给你泼盆冷水，也定个调：M365 Copilot 在 Excel 里是"副驾驶"，不是"自动驾驶"。它能帮你写公式、解释公式、给排版和建议，但它不会替你把整个财务模型的结构、假设、勾稽关系（勾稽关系，说人话就是"表与表之间对得上的逻辑"）想清楚——这些"脑子里的活"，还是得你来做。本文要做的，是把 AI 用在它真正擅长的地方，再用手工但可靠的操作把模型钉死，最后用 Excel 原生的「数据表（Data Table）」一键把敏感性分析跑出来。

2. 目标产出

学完这篇，你会拿到一个能跑的简易 DCF（DCF，Discounted Cash Flow，现金流折现估值，说人话就是"把公司未来能赚的钱按风险打折加到一起"）估值模型，具体长这样：

  • 一组用「名称管理器（Name Manager，Excel 里给单元格起别名的工具）」命名的假设单元格（折现率、永续增长率、基期自由现金流等），公式读起来像人话：=FCF1/(1+DiscountRate)^1，而不是 =$C$10/(1+$B$2)^1。
  • 五年自由现金流预测 + 终值（Terminal Value，终值，说人话就是"第 5 年之后公司还值多少"）+ 企业价值（Enterprise Value，企业价值，说人话就是"把前面这些折完现的钱加总"）的完整计算链。
  • 一张二维数据表：横轴是增长率、纵轴是折现率，表里每个格子自动算出对应的估值——领导问"12% 折现率、2% 增长"时，你直接指给他看格子里的数。

配套能力：你会清楚知道哪些事该交给 Copilot，哪些事必须自己点。

说明：本文以"简易 DCF 估值"为主案例。如果你要的是利润表/资产负债表/现金流量表三张主表联动的模型，思路完全一样——把"假设"用名称管理器固化、把计算链写清楚，最后用数据表做敏感性。三表模型只是科目更多，骨架不变。

3. 案例实战

下面咱一步一步来。先把模型骨架画出来，再让 Copilot 帮写公式，最后用数据表跑敏感性。

第一步：先把"假设"和"结果位"框出来（手工，约 2 分钟）

打开一个空白工作表，在左侧放假设区（这一步不用 AI，纯手填，确保锚点清晰）：

单元格 内容 值
B2 折现率 DiscountRate 10%
B3 永续增长率 GrowthRate 3%
B4 基期自由现金流 BaseFCF 100
B5 预测期现金流增速 FCFGrowth 15%
B6 预测年数 NYears 5

第二步：用「名称管理器」给这些单元格起名字（手工，关键一步）

名称管理器（Name Manager） 是 Excel 里一个"给单元格起别名"的工具——说人话就是：你把 B2 叫成 DiscountRate，以后公式里写 DiscountRate 就等于写 B2，但可读十倍，改位置时也不容易断引用。

操作路径：公式（Formulas）选项卡 → 名称管理器（Name Manager）→ 新建（New）。逐个把上面五个单元格定义成名称（引用方式用绝对地址）：

  • DiscountRate = =Sheet1!$B$2
  • GrowthRate = =Sheet1!$B$3
  • BaseFCF = =Sheet1!$B$4
  • FCFGrowth = =Sheet1!$B$5
  • NYears = =Sheet1!$B$6

关键点：名称最好用"工作表级"或"工作簿级"都行；起名别带空格，用驼峰或下划线。定义完，在任意空白格输入 =DiscountRate 回车，应该显示 0.1，说明绑定成功。

第三步：让 Copilot 帮你想/写计算列（AI 辅助，但要核对）

现在轮到 M365 Copilot 出场。打开 Excel 右侧的 Copilot 面板，你可以这么问（注意：把真实单元格地址写进提示词，越具体越好）：

提示词示例 1（生成预测现金流）： "在 C10 到 C14 生成未来 5 年自由现金流。C10 等于 BaseFCF，之后每年 = 上一年 ×(1+FCFGrowth)。用名称管理器里的名称，不要硬编码。"

提示词示例 2（解释公式，帮你看懂）： "解释一下 D10 这个贴现因子公式 =1/(1+DiscountRate)^B10 在算什么，用一句话。"

提示词示例 3（检查勾稽）： "检查 C10:C14 和 E10:E14，确认每一年的现值 = 现金流 × 贴现因子，并指出哪里可能写错。"

Copilot 能力边界（务必看）： 在我写作时，Copilot 在 Excel 里最稳的用法是——（a）在已格式化为「表格（Table，说人话就是把一片数据按 Ctrl+T 变成带筛选头的智能表）」的数据上"新增一列 / 建议公式"——注意：数据必须先 Ctrl+T 成表，这是它能加列、写公式的硬前提，纯普通网格它基本干不了这活；（b）在右侧面板用自然语言解释或改写你选中的公式；（c）回答关于表格的问题。它不适合、也做不到的是：替你从零设计模型结构、自动建好数据表、或自动建名称管理器。更关键的是——Copilot 生成的公式必须你人工核对，它偶尔会把符号、引用的单元格搞错（比如把 ^ 写成 *，或引用到错误的行）。把它当"写公式的实习生"，别当"审账的合伙人"。

不确定提示：上面"提示词示例"里按钮级的操作会因你账号的 Copilot 版本 / 授权而不同——比如它是否一定能直接把公式写进 C10:C14，还是只给建议让你点"插入"。但有一点可以确定：Copilot 在 Excel 里加列、写公式的硬前提是数据已经按 Ctrl+T 变成「表格（Table）」；在纯普通网格（没建成 Table）上它基本无法加列、也写不出公式。所以本文第三步的前提一定是"数据先成表"。如果它只给建议不改表，你就照它给的公式手动填——结果一样。

第四步：把计算链补全（手工，对照着填）

不管 Copilot 帮了多少，最终这几行公式你要在工作表上确认存在（假设预测放在第 10–14 行，年份在 B 列）：

B10: 1          C10: =BaseFCF                       D10: =1/(1+DiscountRate)^B10      E10: =C10*D10
B11: 2          C11: =C10*(1+FCFGrowth)             D11: =1/(1+DiscountRate)^B11      E11: =C11*D11
B12: 3          C12: =C11*(1+FCFGrowth)             D12: =1/(1+DiscountRate)^B12      E12: =C12*D12
B13: 4          C13: =C12*(1+FCFGrowth)             D13: =1/(1+DiscountRate)^B13      E13: =C13*D13
B14: 5          C14: =C13*(1+FCFGrowth)             D14: =1/(1+DiscountRate)^B14      E14: =C14*D14

终值与企业价值（放在第 16、18 行附近）：

C16 (终值 TV):    =C14*(1+GrowthRate)/(DiscountRate-GrowthRate)
E16 (终值现值):   =C16/(1+DiscountRate)^NYears
C18 (企业价值 EV): =SUM(E10:E14)+E16

关键点：终值用的是戈登永续增长模型（Gordon Growth Model）——说人话就是"假设第 5 年之后公司自由现金流按固定增长率永远涨下去，用一个分式把'永远'压成一个现值"。这个分式 TV = FCF₅×(1+g)/(r−g) 成立的前提是 r 必须大于 g，否则分母非正，估值会爆成负数或天文数字。

第五步：用「数据表（Data Table）」一键跑敏感性（手工，本文硬核点）

数据表（Data Table） 是 Excel 自带的"What-If（What-If，模拟分析，说人话就是'如果某个数变成 X 会怎样'）"工具——说人话：你给它一串"如果折现率变成 X、增长率变成 Y"的清单，它自动把每个组合代进模型、把结果吐成一张矩阵表。它分"单变量"和"双变量"两种，咱们用双变量（横轴增长率、纵轴折现率）。

布局（重要，照着放）：

  • 在 G9 输入锚点公式：=C18（也就是引用企业价值 EV 这个结果）。G9 是整张表的左上角。
  • 在 H9:L9（G9 右边那一行）横着写增长率场景：0.01, 0.02, 0.03, 0.04, 0.05。
  • 在 G10:G14（G9 下面那一列）竖着写折现率场景：0.08, 0.09, 0.10, 0.11, 0.12。

操作：

  1. 用鼠标框选整个区域 G9:L14（必须包含左上角的 =C18 锚点、顶部增长率、左侧折现率、以及中间空白的 5×5 身体）。
  2. 点 数据（Data）选项卡 → 模拟分析（What-If Analysis）→ 数据表（Data Table）。
  3. 在弹出的对话框里：
    • 行输入单元格（Row input cell）= B3（因为 Excel 规定"顶部那一行"的值从 Row input cell 代入；本例顶行写的是增长率，对应模型里的 GrowthRate 单元格 B3）。
    • 列输入单元格（Column input cell）= B2（因为 Excel 规定"左侧那一列"的值从 Column input cell 代入；本例左列写的是折现率，对应模型里的 DiscountRate 单元格 B2）。
  4. 确定。Excel 会把每个组合代进去重算，G10:L14 瞬间填满不同估值。

关键点 & 易错：行/列输入单元格很容易填反。先记清 Excel 的规则：数据表顶部那一行的值 → Row input cell；左侧那一列的值 → Column input cell。本例顶行是增长率（B3）、左列是折现率（B2），所以 Row 填 B3、Column 填 B2。填反了表不会报错，但出来的是"镜像错版"，你拿去汇报就丢人了。

确定规则（必看）：数据表的「行输入 / 列输入单元格」必须与数据表本身在同一张工作表——也就是 B2、B3 要和 G9:L14 处在同一个 Sheet。如果输入单元格在别的表，Excel 会直接报错"Input cell reference is not valid（输入单元格引用无效）"，数据表根本建不出来。这不是版本差异，而是硬约束。本文把模型和数据表都放在 Sheet1，正是为了满足它，照做即可。

4. 原理小结

数据表背后是 Excel 的 TABLE() 数组函数：你选定区域后，Excel 记住"哪个格子是结果锚点、哪两个格子是输入"，然后对每个场景值，临时把输入值塞进对应单元格、重算一遍、把结果写回矩阵对应格子——所以你改 B2、B3，整张敏感性表会跟着刷新，但它本身是一整块数组，不能单独改其中一个小格子。名称管理器则是另一回事：它只是给 B2 这类地址发了一张"别名名片"，让公式脱离具体行列号、可读也更易维护。至于 Copilot，它在这套流程里扮演的是"公式起草与解释助手"，模型的结构、假设和勾稽关系仍由你定义——AI 加速的是"写"，不是"想"。

5. 避坑指南

  1. 数据表左上角必须是公式，且要引用结果单元格。 很多人直接把 G9 留空或写文字，结果整张表空白。G9 一定要是 =C18 这种"指向你要看的那个结果"的公式。
  2. 行/列输入单元格填反 = 出一张镜像错表。 Excel 规则：顶行的值进 Row input cell，左列的值进 Column input cell。本例顶行是增长率 B3、左列是折现率 B2，所以 Row=B3、Column=B2。填完先对照正文核对一遍别接反。
  3. 折现率必须大于永续增长率。 TV = FCF₅×(1+g)/(r−g) 里 r−g 一旦 ≤0，终值直接爆掉（负数或无穷），整张估值表跟着崩。做敏感性时，增长率场景别设到超过折现率。
  4. 数据表是"易失性"的，别在巨表上滥用。 它每次重算都会重新跑所有场景，表格上万行时会让文件变卡。敏感性矩阵保持小（5×5、10×10 足够），原始大数据另放。
  5. Copilot 给的公式必须人工核对再上线。 它偶尔符号写错、引用错行，或把你的假设悄悄改掉。把它当实习生：出活你看一眼，确认对了再贴进模型。

6. 进阶延展

  • 回指基础篇 B1：如果你对"怎么把区域转成表格（Table）"还不熟，先回 B1 把超级表（Ctrl+T）玩法打牢——本文第三步的 AI 辅助严重依赖"数据先变成 Table"这个前提。
  • 更进阶的方向：
    • 把单模型升级成三表联动（利润表→现金流量表→资产负债表，靠留存收益/现金余额勾稽），再用数据表对"营收增速 vs 净利率"做敏感性。
    • 想要更"随机"的敏感性？上 蒙特卡洛模拟（Monte Carlo，说人话就是"让关键假设随机抖动几千次、看估值的概率分布"）——用 Excel 的 RAND() 配合数据表反复抽样，看估值落在哪个区间，而不是只盯几个固定场景。
    • 原始财务数据别再手工粘：用 Power Query（Excel 的"数据清洗 ETL 工具"，说人话就是"自动从文件/数据库抓数并清洗的管道"）把报表拉进来；更重的多维建模可以上 Power Pivot + DAX（DAX 是 Power Pivot 里专门写"度量值"的公式语言，适合做跨期、占比、同环比这类聚合计算）。
    • 如果你嫌"每次手点数据表"麻烦，可以看本系列 VBA 相关篇章，用宏（macro，说人话就是"Excel 里录下来能反复自动跑的一串操作"）把"改假设→刷新数据表→导出结果"一键串起来。