如何在 Excel 中进行敏感性分析：分步指南

要在 Excel 中进行敏感性分析，请将模型的每个输入项放在独立的单元格中。然后更改这些输入并记录结果的变化。对于单个输入使用单变量数据表，对于两个输入使用双变量数据表，对于完整案例则使用方案管理器。最后以龙卷风图结束，按影响程度对输入项进行排序。

本指南解释了什么是敏感性分析、如何构建模型以及如何使用每种 Excel 工具。它包含一个带有真实公式的实操案例、一种使用 AI 的更快捷方法，以及应避免的错误。

什么是敏感性分析

敏感性分析显示当一个假设发生变化时，结果会发生多大变化。它回答诸如“如果价格下降 10%，利润会发生什么变化？”之类的问题。

大多数商业模型都建立在猜测之上。销售量、价格、成本和增长率都是估计值。敏感性分析测试这些猜测中哪些最为重要。

Excel 将这些工具归类在“模拟分析”下。Microsoft 将其定义为“更改单元格中的值以查看公式结果如何变化的过程”。

Microsoft 列出了三种内置工具：“方案、目标搜寻和数据表”。每种工具都有不同的用途：

• 数据表显示一个结果在单变量或双变量的多个值下的变化。
• 方案管理器保存完整的输入集，例如最坏情况和最佳情况。
• 目标搜寻进行逆向运算。它寻找达到目标结果所需的输入值。

敏感性分析通常使用前两种工具。目标搜寻有助于解决相关问题，例如盈亏平衡价格。

开始前先设置模型

只有在模型构建整洁的情况下，Excel 的工具才能发挥作用。请先检查以下几点。

将每个输入项放在独立的单元格中。单位、价格、成本和费率各需要一个单元格。不要在公式内部输入数字。

将结果与输入项链接。结果单元格（如利润）必须是引用输入单元格的公式。如果不这样做，当 Excel 替换新值时，任何内容都不会改变。

将输入项集中在一起。将它们分组放在工作表顶部的同一个区块中。标记每一个输入项，并为输入单元格着色以使其突出。

选择一个结果进行测试。当敏感性分析跟踪一个输出（如利润、净现值或年末现金）时，更容易阅读。

记录您的基准案例。写下起始值。分析中的每个结果都与此基准进行比较。

这是一个可以跟随操作的简单利润模型。这是一家虚构的自行车店，Northgate Cycles：

单元格	标签	数值
B1	销售单位	2,400
B2	单位价格	$850
B3	单位变动成本	$510
B4	固定成本	$520,000
B5	利润	=B1*(B2-B3)-B4

在这些基准值下，利润为 $296,000。

如何使用数据表进行单变量敏感性分析

单变量数据表测试一个输入项的一系列值。Microsoft 表示它显示“一个或多个公式中一个变量的不同值将如何改变结果”。

以下是在示例模型中测试价格的方法：

1. 在 D2 到 D6 中，输入要测试的价格：$800、$825、$850、$875 和 $900。
2. 在 E1（第一个价格上方一行且向右一列）中，输入 =B5。
3. 选择 D1:E6。
4. 在“数据”选项卡上，选择“模拟分析”>“数据表”。
5. 在“列引用单元格”中，输入 B2（价格单元格）。将“行引用单元格”留空。
6. 点击“确定”。

Excel 会在 E2:E6 中填入每个价格对应的利润。公式的位置很重要。Microsoft 表示要将其输入在“数值列上方一行且向右一格”的位置。

Northgate Cycles 的结果如下：

价格	利润
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

价格每变动 $25，利润就会变动 $60,000。这就是利润对价格的敏感性。

如何进行双变量敏感性分析

双变量数据表同时测试两个输入项。Microsoft 表示它显示“一个公式中两个变量的不同值将如何改变该公式的结果”。

以下是同时测试价格和单位的方法：

1. 在空白单元格（如 D10）中，输入 =B5。
2. 在其下方，在 D11 到 D13 中，输入单位销量：2,000、2,400 和 2,800。
3. 在其右侧，在 E10 到 G10 中，输入价格：$800、$850 和 $900。
4. 选择 D10:G13。
5. 选择“模拟分析”>“数据表”。
6. 在“行引用单元格”中，输入 B2。在“列引用单元格”中，输入 B1。
7. 点击“确定”。

结果是一个利润网格：

单位 \ 价格	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

网格显示，在测试的每种组合中，商店都保持盈利。最薄弱的角落（以 $800 销售 2,000 个单位）仍能赚取 $60,000。

有一个硬性限制。Microsoft 表示“数据表不能容纳超过两个变量”。对于更多输入项，请使用方案管理器。

数据表在工作表重新计算时也会重新计算。在大型工作簿中，这可能会降低 Excel 的速度。Microsoft 建议将计算设置为仅在按下 F9 时重新计算数据表。

如何使用方案管理器比较完整案例

方案管理器保存完整的输入集并进行替换。Microsoft 将方案描述为“Excel 保存并可以在工作表上自动替换的一组值”。

以下是设置三个案例的方法：

1. 在“数据”选项卡上，选择“模拟分析”>“方案管理器”。
2. 点击“添加”，将方案命名为“基准”，并输入 B1:B4 作为可变单元格。保留当前值。
3. 添加“最坏情况”：2,000 个单位、$825 价格、$530 变动成本和 $540,000 固定成本。
4. 添加“最佳情况”：2,800 个单位、$875 价格、$495 变动成本和 $510,000 固定成本。
5. 选择一个方案并点击“显示”以加载其数值。
6. 点击“总结”并选择 B5 作为结果单元格以生成报告。

对于 Northgate Cycles，总结显示了三种利润。最坏情况赚取 $50,000，基准案例为 $296,000，最佳情况为 $554,000。

Microsoft 指出了一些限制。每个方案最多可包含 32 个可变值。总结报告不是自动的：“方案报告不会自动重新计算”。如果您更改了方案，请创建一个新的总结。

如何使用目标搜寻找到盈亏平衡点

敏感性分析显示结果变动了多少。目标搜寻回答相反的问题：什么样的输入值能产生目标结果？

Microsoft 表示目标搜寻“获取一个结果并确定产生该结果的可能输入值”。其示例是寻找贷款支付所需的利率。

以下是寻找 Northgate Cycles 盈亏平衡价格的方法：

1. 在“数据”选项卡上，选择“模拟分析”>“目标搜寻”。
2. 在“目标单元格”中，输入 B5（利润单元格）。
3. 在“目标值”中，输入 0。
4. 在“可变单元格”中，输入 B2（价格单元格）。
5. 点击“确定”。

Excel 会更改价格，直到利润达到零。在 2,400 个单位和基准成本下，盈亏平衡价格约为 $726.67。这比当前 $850 的价格低约 15%。

目标搜寻有一个限制。Microsoft 表示它“仅适用于一个变量输入值”。对于多个输入项，Microsoft 建议使用 Solver 加载项。

如何构建龙卷风图

龙卷风图按输入项对结果的影响程度进行排序。这是展示敏感性分析最清晰的方式。

以下是构建方法：

1. 选择一个测试范围，例如每个输入项分别降低 10% 和升高 10%。
2. 一次更改一个输入项，并记录低结果和高结果。保持其他输入项为基准值。
3. 将结果放入表格中，每个输入项占一行。
4. 对行进行排序，使变动范围最大的输入项位于顶部。
5. 插入低结果和高结果的条形图。

对于 Northgate Cycles，每个输入项上下浮动 10%：

输入项	-10% 时的利润	+10% 时的利润	范围
价格	$92,000	$500,000	$408,000
变动成本	$418,400	$173,600	$244,800
销售单位	$214,400	$377,600	$163,200
固定成本	$348,000	$244,000	$104,000

价格的影响最大，且差距明显。价格下降 10% 会使利润从 $296,000 削减至 $92,000。固定成本的影响最小。这告诉所有者应该把时间花在检查哪些假设上。

Excel 中的条形图从左向右绘制。为了获得龙卷风形状，请绘制每个输入项相对于基准利润的变化量，而不是原始数值。这样，低情况条形指向左侧，高情况条形指向右侧。

如何使用 AI 进行敏感性分析

为大型模型构建数据表、方案和龙卷风图非常耗时。AI 工具可以直接从您的工作簿运行相同的测试。

以下是使用 Powerdrill Bloom 的三个步骤。

第 1 步：上传您的模型

从包含您模型的电子表格开始。AI Sensitivity Analysis Generator 会要求您上传“DCF、商业案例、运营计划或任何电子表格模型”。

确保输入项和结果单元格都有清晰的标签。该工具需要知道哪些数字是假设。

第 2 步：使用自然语言选择敏感性视图

描述您想要的测试。例如：“显示利润的龙卷风图，每个输入项上下浮动 10%。添加价格和销售单位的双向表。”

工具页面列出了四种视图：“龙卷风图、双向表、蒙特卡罗模拟或盈亏平衡”。它将龙卷风图输出描述为“对影响答案最大的输入项进行排序”的图表。它还提供“针对两个重要假设的敏感性表”。

第 3 步：检查结果并导出分析

在分享之前，将一两个结果与您自己的数据表进行比较。基准案例应与您的模型完全匹配。

然后导出。工具页面列出了“Excel 工作簿、品牌 PDF 或画布链接”。对于演示文稿，Excel to PPT 工具可以将相同的文件转换为幻灯片。AI Financial Modeling 页面涵盖了构建模型本身的内容。

如何阅读和展示结果

只有当人们根据敏感性分析采取行动时，它才有用。以下习惯会有所帮助。

以排名开场。以龙卷风图开始。它通过一张图片展示了哪些假设驱动了结果。

将范围转化为决策。“如果价格下降 10%，利润将降至 $92,000”是一个事实。“我们应该在签署租约前测试价格”是一个决策。

显示盈亏平衡点。将分析与结果降至零时的数值配对。对于 Northgate Cycles，在基准价格和成本下，盈亏平衡点约为 1,530 个单位。我们的 Excel 盈亏平衡分析指南涵盖了该计算。

使用现实的范围。10% 的变动适用于稳定的输入项。新产品的销量可能需要更广泛的范围。

说明哪些是保持不变的。每次单项测试都保持其他输入项为基准值。在幻灯片或脚注中说明这一点。读者就会知道，10% 的价格变化假设销量、成本和固定支出保持不变。这一行说明可以防止大多数关于方法的后续问题。

在结果出来后重新审视。一旦实际数据到位，请将其与您的范围进行比较。预算与实际报告是自然的后续行动。

常见的敏感性分析错误

这些错误很常见且容易避免。

公式中存在硬编码数字。如果公式包含 850 而不是 B2，数据表就无法更改它。请将每个输入项保留在单元格中。

一次测试过多的输入项。数据表处理一两个。对于更多输入项，请改用方案管理器或龙卷风图。

对每个输入项使用相同的范围。价格和销量的确定性很少相同。选择能反映您实际认知情况的范围。

忽略输入项之间的联系。较低的价格可能会提高销量。单项测试会忽略这一点，因此请添加一个同时改变两者的方案。

过时的方案总结。方案报告不会自行更新。在任何更改后请重新构建它们。

忘记基准案例。每个表格都应显示基准结果，以便读者看到每次变化的幅度。

当您准备对下一个模型进行压力测试时，请使用您用于规划的工作簿尝试 Powerdrill Bloom。

常见问题解答

什么是 Excel 中的敏感性分析？

这是一种测试当输入项发生变化时结果如何变化的方法。在 Excel 中，您可以使用模拟分析工具（主要是数据表和方案管理器）来替换新的输入值并记录结果。

如何在 Excel 中进行单变量敏感性分析？

在列中列出输入值。在上方一行且向右一列的位置放入引用结果的公式。选择该范围，选择“模拟分析”>“数据表”，并将输入单元格输入为“列引用单元格”。

数据表和方案管理器有什么区别？

数据表在多个数值上测试一两个变量，并在一个网格中显示所有结果。方案管理器保存完整的输入集（每个最多 32 个可变值），并一次替换一个方案。

如何在 Excel 中制作龙卷风图？

一次一个地将每个输入项上下移动相同的量，并记录结果。按范围大小对输入项进行排序，然后将每个输入项相对于基准结果的变化绘制为水平条形图。

AI 可以做敏感性分析吗？

是的。AI 工具可以读取您的电子表格模型，运行龙卷风图和双向测试，并将结果导出为工作簿或报告。您仍然需要检查基准案例并选择现实的范围。

来源： Microsoft 支持，模拟分析简介 · Microsoft 支持，使用数据表计算多个结果 · Microsoft 支持，使用方案在各种数值集之间切换。