如何在 Excel 中进行敏感性分析

在 Excel 中创建单变量和双变量数据表,核对敏感性分析结果,找出可能改变财务决策的假设。

Excel 价格与销量敏感性矩阵
要点
  • 用单变量表测试销量,再用双变量表比较价格和销量组合。
  • 将矩阵中心值和一个角落的值与模型核对。
  • 示例中单价为 100 美元时,盈亏平衡销量为 875 件。

如果预算预计营业利润为 5,000 美元,能降价多少?销量未达标时还有多少余地?敏感性分析用不同假设重算同一个模型。本指南介绍如何在 Excel 建立单变量与双变量表、对照基准核对结果,并找出盈亏平衡销量。

什么是敏感性分析?

敏感性分析衡量指定输入变化对模型输出的影响。以基准情形为起点,调整要测试的输入,将其余假设固定。输出差异反映结果对这些变化有多敏感。

例如,预算假设售价为每件 100 美元、销量为 1,000 件。价格和销量是输入,营业利润是输出。单变量测试观察售价固定在 100 美元时销量变化的影响。双变量测试比较不同价格与销量组合。

这个方法也适用于其他模型。现金预测可测试回款放慢对月末现金余额的影响。估值模型可测试折现率对净现值的影响。输入范围应服务于相应的决策。

敏感性分析与情景分析

敏感性分析观察价格或销量变动时输出会变化多少。情景分析评估可能同时发生的一组假设,例如降价、预期销量上升以及额外营销费用。价格和销量矩阵帮助拆解影响,促销情景帮助评估整体方案。

季度结束后,差异分析 可以解释实际利润为何偏离预算。已知目标结果时,Excel 的单变量求解可反推一个输入,例如找出固定价格下营业利润为零时的销量。

如何选择敏感性分析的输入和范围

先明确决策和可能改变决策的指标。决定是否批准折扣时,可以先看营业利润与盈亏平衡销量。如果关心流动性,最低现金余额可能更重要。

记录基准值、测试上下限、范围依据和确认人。价格可参考近期报价或获批的折扣政策,销量可参考可比期间、在手订单或销售预测。统一期间和计量单位。比率要区分相对变化和百分点。10% 加 2 个百分点为 12%,相对增加 2% 为 10.2%。

示例测试 95 至 105 美元的价格,以及 800 至 1,200 件的季度销量。练习沿用基准成本假设。实际编制预算时,要核实销量是否跨过产能上限或供应商价格档位,从而改变成本。

如何在 Excel 中执行敏感性分析

在桌面版 Excel 的同一张工作表中输入下列假设和利润公式,建立基准模型。

基准模型假设与 Excel 单元格引用
单元格模型项目数值或公式
B2单位售价100
B3季度销量1000
B4单位变动成本60
B5季度固定成本35000
B6季度营业利润=(B2-B4)*B3-B5

B6 的基准结果应为 5,000 美元。两张表都引用这个输出单元格。

创建单变量数据表

先测试销量,保持售价为每件 100 美元。

  1. 在 =$B$6 输入 E3。在 800 逐行填入 1000、1200 和 D4:D6。
  2. 选中 D3:E6,打开 数据 → 模拟分析 → 数据表。
  3. 将 输入引用列的单元格 设为 $B$3,将 输入引用行的单元格 留空,然后选择 确定。

Excel 在 E4:E6 填入 −3,000 美元、5,000 美元和 13,000 美元。1,000 件的结果应与 5,000 美元的基准利润相同。以该价格,每多卖一件可贡献 40 美元,用于覆盖固定成本和形成利润。

创建双变量数据表

把价格排在顶部,将销量列在左侧。

  1. 在 =$B$6 输入 H3。在 95 填入 100、105 和 I3:K3,并在 800 填入 1000、1200 和 H4:H6。
  2. 选中 H3:K6,打开 数据 → 模拟分析 → 数据表。
  3. 将 输入引用行的单元格 设为 $B$2,对应顶部的价格。将 输入引用列的单元格 设为 $B$3,对应左侧的销量。选择 确定。

不同价格和销量下的季度营业利润.

不同价格和销量下的季度营业利润
季度销量每件 95 美元每件 100 美元每件 105 美元
800−$7,000−$3,000$1,000
1,000$0$5,000$10,000
1,200$7,000$13,000$19,000

把中心结果与 B6 核对,再手算一个角落。单价 95 美元、销量 800 件时,每件贡献 35 美元,扣除固定成本前合计 28,000 美元,扣除后为 −7,000 美元。核对 100 美元列与单变量表的结果。这三项核对有助于发现输出引用错误、输入单元格对调或结果未刷新。

如何解读敏感性表

先看与决策最相关的数值。销量 1,000 件时,价格从 100 美元降至 95 美元会耗尽 5,000 美元的基准利润。维持 100 美元单价时,销量降至 875 件会盈亏平衡,等于 35,000 美元固定成本除以每件 40 美元的贡献额。单价为 95 美元时,每件贡献 35 美元,盈亏平衡销量升至 1,000 件。

表格展示指定组合的结果。若预计降价能提高销量,应核实依据和团队是否能承接额外销量。按现有成本估算,以 95 美元出售时须销售 至少 1,143 件才能保住 5,000 美元利润。促销情景还可以纳入新增费用和根据以往结果估算的销量。

管理层简报可以这样写。

单价 95 美元、销量保持 1,000 件时,季度利润从 5,000 美元降至 0 美元。在当前成本假设下,要保住原有利润,至少需售出 1,143 件。批准折扣前,核实历史促销能否支持该销量,并查看初期订单是否达到相应进度。

将工作簿版本及测试范围的来源随简报保存。用 Kuse 将已核对的矩阵、假设说明和支持文件整理成决策简报。简报应列出基准情形、改变决策的临界点和待补的证据。由模型负责人对照 Excel 核实数字和表述,再将相同格式用于下一次预测。

常见问题

为什么 Excel 敏感性表的每格都显示同一个值?

手动改变模型输入,确认关联输出变化,再恢复基准值。如果模型输出改变但矩阵不变,检查输出引用和表格实际使用的输入。若 Excel 跳过数据表自动重算,选择表格公式重新计算。

为什么敏感性表的基准结果与模型不一致?

先检查表头是否使用精确的基准值。四舍五入后显示数字可能相同,Excel 使用的数值却不同。随后核对输出引用、重算表格,并确认其他假设与基准一致。

如何检查现有数据表使用的输入单元格?

选中矩阵中的计算结果,查看公式栏。桌面版 Excel 的数据表公式可按顺序显示行输入和列输入引用。沿着参照确认它们对应的假设与关联输出。

何时使用双变量数据表或单变量求解?

要看两项假设的不同组合,例如价格和销量下的利润,可用双变量表。已知目标并希望 Excel 调整一个输入时,可用单变量求解,例如固定价格下求利润为零的销量。

如何为敏感性分析选择合理的输入?

从获批或当前基准出发,用历史数据、合同条款、供应商报价或预测假设确定范围。加入接近临界点的数值,并标注用于探索的取值,便于复核证据。

如何用敏感性表找到盈亏平衡点?

寻找利润为零的单元格,或利润正负号变化的相邻结果。若平衡点在两个测试值之间,用模型公式或单变量求解计算。销量有小数时,视需要向上取整,再核对模型中的利润。