內審通識：善用Excel的情景分析

上一篇示範如何善用「What-if Analysis」的「Goal Seek」和「Data Table」功能，這篇將分享另一個功能，「Scenario Manager」。

很多時候，我們都需要進行情景分析 ( Scenario analysis )，例如盈利預算、投資回報預測、壓力測試和風險評估等。此文將以企業的盈利預算做例子，示範如何利用Excel的「Scenario Manager」進行情景分析。

在這個例子中，我們以2015年的實際表現去預測2016年的表現。當中影響「稅前盈利」的三個要素包括營業額增長，銷售成本增長，和經營開支增長。按管理層預測，未來一年的盈利有三個可能，包括「最好」，「最有可能」，以及「最差」，各情況的三個增長率皆有分別，例如營業額增長率分別是15%、10%和5%。

1. 首先，我們以2015年的實際數字編制報表如下，並且以「最有可能」情況的增長率來計算2016年的預測。

2. 完成報表後，我們需要為四個儲存格輸入名稱，包括C2 ( 營業額增長 )、C3 ( 銷售成本增長 )、C5 ( 經營開支增長 ) 和 D6 ( 稅前盈利 )。以C2為例，先選取C2，然後按滑鼠右鍵並點選「Define name」。

3. 之後在「Name」中輸入「營業額增長」，然後按「OK」。

4. 重覆步驟3，為其餘三個儲存格輸入名稱。即是將C3設定為「銷售成本增長」、C5為「經營開支增長」，以及D6為「稅前盈利」。

5. 接著在「Data」的「What-if Analysis」中揀選「Scenario Manager」，然後按「Add…」加入三個情景。以「最可能」為例，在「Scenario name」中輸入「Likely」，然後將「Changing cells」設定為C2、C3和C5，並且點選「Prevent changes」。

6. 按「OK」後會出現一個輸入窗口，將「最可能」的增長數值輸入各欄。

7. 重覆步驟5和6，完成後在「Scenario Manager」中會看到三個已輸入的情景。

8. 只要揀選任何一個情景然後按「Show」，報表就會自動計算稅前盈利 ( 即儲存格D6 )。例如揀選「Worst」然後按「Show」，稅前盈利即時計算出510,000。

9. 若然希望一次過看見所有情景，只要按「Summary…」，各情景的變數連同結果就會形成，並以工作表形式顯示，用家也可以選擇以Pivot table顯示。

最後一提，此功能可以處理最多32個變數，輸入了的情景可以隨時增加、刪除和修改，更可以與其他工作表的情景合併，非常好用。

原文刊於：Education post 2016-07-22

&&&&&&&&