ZKIZ Archives


內審通識:善用Excel的What-if analysis

當審視管理層的決策是否合理時,我們可以採用假設分析 ( What-if analysis )。所謂假設分析,其實是一個模擬分析工具,首先計算出不同方案所產生的結果,然後再根據結果選擇最適合的方案。例如定期存款,選擇不同的年期和利率,得出的回報皆也有分別,究竟那個組合最佳最合適, 接下來將以Excel的What-if analysis作示範。

假設公司有一筆閒置現金,金額為100萬元,若用作定期存款,並選擇1年期,年利率為0.2%以及以複式計算,到期後得出的回報為2,000元。

好了,假設管理層希望1年後能得到3,000元,利率要多少才達標?Excel設定的步驟如下:

1. 首先建立以下的工作簿,Interest income ( 儲存格B6 ) 的計算程式是「=(+B2*(1+B4)^B3)-B2」。完成後,在「Data」中選取「What-if Analysis」,然後再選「Goal Seek」。



2. 將「Set cell」設定為B6 ( 即Interest income ) ;在「To value」中輸入「3,000」 ( 即是將B6轉換成3,000 );最後將「By changing cell」設定為B4 ( 即是將利率設為可變 )。




3. 這樣設定下,Excel將會鎖定Interest income 為3,000,並倒算出利率為0.30%。




另一問題,若然利率0.2%不變,存款要多少年才能獲取3,000元呢?步驟與上述一樣,唯一分別只是將「By changing cell」設定為B3 ( 即是將年期設為可變 ),得出的結果將是1.5年。



綜合而言,若管理層希望將利息回報由2,000元提升至3,000元,他們可以選擇為期1年、利率為0.3%的定期存款,或者為期1.5年、利率為0.2%的定期存款。

以目標來尋找變數,好處是快捷,但壞處是每次只能改變一個變數,若果要測試多個變數,每次輸入有點費時,若然希望一次過看到所有變數組合的結果,我們可以利用「Data table」功能。步驟如下:

1. 首先建立一個表格,在儲存格E8輸入「+B6」,然後在儲存格F8至I8分別輸入年期的變動值,這裡我們用1、1.5、2和3。接著在儲存格E9至E12分別輸入利率,這裡我們用0.2%、0.25%、0.3%和0.35%。

2. 完成輸入後,選取儲存格E8至I12。在「Data」中選取「What-if Analysis」,然後再選「Data table」。



3. 將「Row input cell」設定為年期 ( 即儲存格B3 ),以及將「Column input cell」設定為利率 ( 即儲存格B4 )。



4. 完成後Excel將自動列出所有變數組合的結果。



從上面的結果,我們可以輕鬆揀選組合,例如利息回報在6,000元左右,我們可選擇2年期和0.3%利率,或選擇3年期和0.2%利率。

最後一提,What-if Analysis中還有一個Scenario Manager功能,能夠處理最多32個變數,而且能把變數組合儲存、即時計算,以及列出摘要。此功能的操作詳情將於下篇再續。

原文刊於:Education post 2016-07-08

&&&&&&&&
內審 通識 善用 Excel What-if analysis
PermaLink: https://articles.zkiz.com/?id=204408


ZKIZ Archives @ 2019