2016年7月25日星期一

內審通識:善用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

&&&&&&&&

2 則留言:

匿名 說...

和 macro in ms excel 有咩分別
重要是可否 download data 作分析
若要 input data 應轉用主機 SQL

Bitter Melon 說...

Hi 匿名君

這裡講的是如何利用excel建構一下Scenario analysis, 而讀者目標群是不懂SQL的朋友.

LinkWithin

Blog Widget by LinkWithin