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　一眼就能知悉正在分析的情景

情景分析說明完畢，本節的重點是通過切換索引值改變價值驅動因素，隨之將結果反映在盈利預測模型中，這樣做大大降低了計算錯誤的發生概率。