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

&&&&&&&&