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

  • Post author:
Home » 莫非工具 » Excel » 【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,目前看起來試算表還沒辦法支援。如果大家有其他問題歡迎留言或是來信跟我們討論,下期文章見囉!

Murphy

我是Murphy,是一名職場人。這裡主要分享職場問題、職場話題、求職資訊,還有那些我所熱愛的事物。

歡迎留言,與我們分享你的看法!