7　情景分析2　CHOOSE函数

接下来，以图2-38中的盈利预测模型为起点，我们做3种情景分析：悲观情况、中性情况和乐观情况。

图2-38　以盈利预测模型为起点，做3种情景分析

大多数人最先想到的办法一定是将此表复制3份，依次修改价值驱动因素的数字，制作出3种情景下的盈利预测模型（见图2-39）。但是，绝对禁止这样做。

图2-39　禁止将表格复制3份

为什么呢？假设复制了3份表格后发现某个公式错了，自然需要修正，但此时需要修改的错误也变成了3个，遗漏修改的风险大大增加了。

也就是说，如果把表格复制3份，公式数量就会增加2倍，发生计算错误的概率也会显著增加。那如何用一张表完成3种情景假设呢？接下来详细说明。

如前所述，盈利预测模型计算的起点是价值驱动因素（彩色数字）和公式计算出的数据（黑色数字）。在不同情景分析下，价值驱动因素的数值是不同的。

因此，找到不同情景分析下的通用公式，通过切换索引值就能调整对应的价值驱动因素数值（前提条件），最终可以实现在不复制公式的情况下完成3种情景分析（见图2-40）。

图2-40　制作3种情景分析下的盈利预测模型

说了这么多，你可能依然有“不懂什么意思”的困惑。我继续介绍计算方法。

这次，分3种情况分析价值驱动因素之一：“销售单价”（见图2-41）。

（1）单元格A1中输入“1”。这是索引值。

（2）第6～8行分别输入价值驱动因素（前提条件）之一的“销售单价”在悲观情况、中性情况和乐观情况下的数据。

（3）第9行显示索引值“1”对应的“悲观情况”下的销售单价。

（4）第18行盈利预测的销售单价引用第9行中悲观情况下销售单价的数值。

图2-41　索引值（单元格A1）=1时，销售单价为“悲观情况”下的数值

接下来，索引值切换为“2”。第9行的销售单价就变成了“中性情况”下的数值。与此同时，第18行盈利预测的销售单价也随之调整（见图2-42）。

图2-42　索引值（单元格A1）=2时，销售单价为“中性情况”下的数值

随后，将索引值切换为“3”，第9行就变成了乐观情况下的销售单价，第18行也随之调整（见图2-43）。

图2-43　索引值（单元格A1）=3时，销售单价为“乐观情况”下的数值

接下来，分步骤说明“情景分析”。

图2-44中已输入了每种情况下的销售单价（第6行至第8行），但索引值（单元格A1）和第9行的销售单价是空的。

步骤1：输入索引值

首先，完成索引值的设置（见图2-45）。在图2-46中的单元格A1输入“1”，即索引值。

图2-44　输入索引值（单元格A1）和销售单价（第9行）的数字，开始计算

将A1作为索引值有以下两点理由。

（1）将索引值放在工作表最显眼的单元格A1（左上角）中，能立刻知道目前所分析的情景。

（2）按“Ctrl”+“Home”键，可快速选定单元格A1。在进行模拟测算时需经常切换索引值，这样操作更便捷。

图2-45　情景分析（设置索引值）

图2-46　设置索引值=在单元格A1中输入“1”

步骤2：计算出所选索引值对应的情景分析结果

其次，创建显示所选索引值的公式（见图2-47）。

在单元格C9中输入公式“=CHOOSE（A1,C6,C7,C8）”（见图2-48），这是本书中唯一的函数。该函数可解释为=CHOOSE（索引值，悲观情况，中性情况，乐观情况）。

图2-47　情景分析（选择不同的索引值）

图2-48　CHOOSE（索引值，悲观情况，中性情况，乐观情况）

CHOOSE函数代表的含义：=CHOOSE（索引值，值1，值2，…）。

首先，索引值必须是数字。当索引值为“1”，显示“值1”；当索引值为“2”，显示“值2”。单元格C9的CHOOSE函数意味着：

（1）图2-49：索引值为“1”时→显示单元格C6的“悲观情况”。

（2）图2-50：索引值为“2”时→显示单元格C7的“中性情况”。

（3）图2-51：索引值为“3”时→显示单元格C8的“乐观情况”。

图2-49　索引值（单元格A1）=“1”时，显示悲观情况

图2-50　索引值（单元格A1）=“2”时，显示中性情况

图2-51　索引值（单元格A1）=“3”时，显示乐观情况

随后，将CHOOSE函数复制到“本月～第三个月”的单元格（F9～H9）中（见图2-52）。

图2-52　复制CHOOSE函数的公式

复制之前，将公式中的索引值调整为“绝对引用”。

修正前：=CHOOSE(A1,C6,C7,C8)。

修正后：=CHOOSE($A$1,C6,C7,C8)。

修正后，单元格A1用“$”标记，表示“绝对引用”（见图2-53），即复制带有“$”的行和列至表格中的任何区域，都不会改变引用的索引值（A1）。

在使用绝对引用时，可以选中该单元格并按“F4”键，当然，手动输入“$”也可以。

步骤3：将索引值对应的情况反映在盈利预测模型中

最后，将索引值对应情况下的价值驱动因素反映到盈利预测模型中（见图2-54）。

图2-53　绝对引用单元格A1，即使复制公式到表格中的任何区域也不会改变引用的值

图2-54　情景分析（设置计算公式）

如图2-55所示，盈利预测模型中的销售单价（第18行）引用选定情况下的销售单价（第9行）。

这样，公式就设置好了。在切换索引值（单元格A1）时，盈利预测模型中的销售单价也会随之改变。

图2-55　索引值选定情况下的销售单价（单元格F9）同步显示在盈利预测模型中

随后，如图2-56所示，索引值的右侧（单元格B1）和盈利预测模型的标题区域（单元格B13），引用选定的情景名称（单元格C9）。如此，只要看一眼盈利预测模型（见图2-57），就能立刻知悉正在分析的情景了。

图2-56　引用索引值所对应的情景名称

图2-57　一眼就能知悉正在分析的情景

情景分析说明完毕，本节的重点是通过切换索引值改变价值驱动因素，随之将结果反映在盈利预测模型中，这样做大大降低了计算错误的发生概率。