【Excel】敏感性分析 (Sensitivity Analysis) 財務、商業分析教學

---

今天的文章要教大家如何用 Excel 做敏感性分析(Sensitivity Analysis)。

敏感性分析時常應用於財務分析，不過也可以用於商業上的試算。當你希望通過控制 X, Y 兩個變數，求出對應結果，模擬分析就可以派上用場了！

💡 Excel 範例檔案下載：Excel Sensitivity Analysis

---

運算列表邏輯

在教學「敏感性分析」之前，要先跟大家講解 Excel 中模擬分析 > 運算列表的邏輯。

以三角型面積為例，三角形面積 = 底*高/2，故 D3 的面積 = 10*20/2 = 100，如下圖所示：

當底從 10 變成 20, 30…..50 、高從 20 變成 25,30,….45，面積會變成多少呢？我們將「底」跟「高」分別放在「列」與「欄」，希望圖表中的數字放入對應面積。

舉例來說，H4 會由底(10)、高(20) 求出對應的三角形面積，以此類推。

這裡有兩個方法可以完成：

1. 在儲存格中下公式
2. 使用模擬分析 > 運算列表的功能

下面我們會介紹第二個方法。

---

運算列表教學與範例

第一步，在 G3 儲存格內下三角形面積公式。

這邊我們填入 = D3，也就是先前我們已經寫好的三角形面積。

第二步，我們將 G3~L9 處的面積框起來，點選：資料 > 模擬分析 > 運算列表

第三步，在跳出的視窗中，將「底」填入「欄變數儲存格」，「高」填入「列變數儲存格」。

就可以得到陣列中的三角形面積啦！

這邊再帶大家看一個範例，我們希望求出對應 a+b^2 對應的值。

需要特別留意的是：由於我們將 b 放在圖表中的「列」、a 放在「欄」，所以操作時視窗中的「欄變數儲存格」與「列變數儲存格」跟剛才不一樣喔。

---

敏感性分析教學與範例

假設你是一位賣桌子的商人，你想知道每個月應該賣幾張桌子，以及每張桌子定價多少，所對應的稅前利潤(Earning before tax)。

首先，我們根據藍色區域的已知資訊，填入綠色損益表中對應的數值，大家可以參考 D 欄公式。

接下來，我們要準備敏感性分析的表格：

1. 在 H3~L3 列填入銷量(Sold)，這裡我們填入 400~800 範圍的數值。
2. 在 G4~H9 欄填入售價(Price)，這裡我們以 $200 為級距填入 $1000~$2000 的售價範圍。
3. 在 G3 儲存格中，輸入=C13。這裡輸入的值是我們通過敏感性分析想得到的目標數值。

選擇範圍 G3~L9，點選資料 > 模擬分析 > 運算列表。在跳出的視窗內：

• 列變數儲存格：點選 C2
• 欄變數儲存格：點選 C3

接著點選確定，敏感性分析就完成啦！

這張敏感性分析(Sensitivity Analysis) 圖表可以解讀為：

• 當桌子定價為 $1,000，每月至少要銷售 600 張桌子才不會虧錢
• 當桌子定價為 $1200 或更高，每月銷售 400 張也不會虧錢，售出越多張桌子可以賺越多

平時工作中墨菲本人使用 Google Sheet 試算表較多，不過這次教學的敏感性分析只適用於 Excel，目前看起來試算表還沒辦法支援。如果大家有其他問題歡迎留言或是來信跟我們討論，下期文章見囉！