如何在 Excel 中進行敏感度分析
在 Excel 中創建單變數和雙變數資料表,核對敏感度分析結果,找出可能改變財務決策的假設。
- 用單變數表測試銷量,再用雙變數表比較價格和銷量組合。
- 將矩陣中心值和一個角落的值與模型核對。
- 示例中單價為 100 美元時,損益兩平銷量為 875 件。
如果預算預計營業利益為 5,000 美元,能降價多少?銷量未達標時還有多少餘地?敏感度分析用不同假設重算同一個模型。本指南介紹如何在 Excel 建立單變數與雙變數表、對照基準核對結果,並找出損益兩平銷量。
甚麼是敏感度分析?
敏感度分析衡量指定輸入變化對模型輸出的影響。以基準情形為起點,調整要測試的輸入,將其餘假設固定。輸出差異反映結果對這些變化有多敏感。
例如,預算假設售價為每件 100 美元、銷量為 1,000 件。價格和銷量是輸入,營業利益是輸出。單變數測試觀察售價固定在 100 美元時銷量變化的影響。雙變數測試比較不同價格與銷量組合。
這個方法也適用於其他模型。現金預測可測試回款放慢對月末現金餘額的影響。估值模型可測試折現率對淨現值的影響。輸入範圍應服務於相應的決策。
敏感度分析與情境分析
敏感度分析觀察價格或銷量變動時輸出會變化多少。情境分析評估可能同時發生的一組假設,例如降價、預期銷量上升以及額外行銷費用。價格和銷量矩陣幫助拆解影響,促銷情境幫助評估整體方案。
季度結束後,差異分析 可以解釋實際利益為何偏離預算。已知目標結果時,Excel 的目標搜尋可反推一個輸入,例如找出固定價格下營業利益為零時的銷量。
如何選擇敏感度分析的輸入和範圍
先明確決策和可能改變決策的指標。決定是否核准折扣時,可以先看營業利益與損益兩平銷量。如果關心流動性,最低現金餘額可能更重要。
記錄基準值、測試範圍的上下界、範圍依據和確認人。價格可參考近期報價或已核准的折扣政策,銷量可參考可比期間、已接訂單或銷售預測。統一期間和計量單位。比率要區分相對變化和百分點。10% 加 2 個百分點為 12%,相對增加 2% 為 10.2%。
示例測試 95 至 105 美元的價格,以及 800 至 1,200 件的季度銷量。練習沿用基準成本假設。實際編制預算時,要覈實銷量是否跨過產能上限或供應商價格級距,從而改變成本。
如何在 Excel 中執行敏感度分析
在桌面版 Excel 的同一張工作表中輸入下列假設和利益公式,建立基準模型。
| 儲存格 | 模型項目 | 數值或公式 |
|---|---|---|
B2 | 單位售價 | 100 |
B3 | 季度銷量 | 1000 |
B4 | 單位變動成本 | 60 |
B5 | 季度固定成本 | 35000 |
B6 | 季度營業利益 | =(B2-B4)*B3-B5 |
B6 的基準結果應為 5,000 美元。兩張表都引用這個輸出儲存格。
創建單變數資料表
先測試銷量,保持售價為每件 100 美元。
- 在
=$B$6輸入E3。在800逐列填入1000、1200和D4:D6。 - 選取
D3:E6,打開 資料 → 模擬分析 → 資料表。 - 將 欄輸入儲存格 設為
$B$3,將 列輸入儲存格 留空,然後選擇 確定。
Excel 在 E4:E6 填入 −3,000 美元、5,000 美元和 13,000 美元。1,000 件的結果應與 5,000 美元的基準利益相同。以該價格,每多賣一件可貢獻 40 美元,用於覆蓋固定成本和形成利益。
創建雙變數資料表
把價格排在頂部,將銷量列在左側。
- 在
=$B$6輸入H3。在95填入100、105和I3:K3,並在800填入1000、1200和H4:H6。 - 選取
H3:K6,打開 資料 → 模擬分析 → 資料表。 - 將 列輸入儲存格 設為
$B$2,對應頂部的價格。將 欄輸入儲存格 設為$B$3,對應左側的銷量。選擇 確定。
不同價格和銷量下的季度營業利益.
| 季度銷量 | 每件 95 美元 | 每件 100 美元 | 每件 105 美元 |
|---|---|---|---|
| 800 | −$7,000 | −$3,000 | $1,000 |
| 1,000 | $0 | $5,000 | $10,000 |
| 1,200 | $7,000 | $13,000 | $19,000 |
把中心結果與 B6 核對,再手算一個角落。單價 95 美元、銷量 800 件時,每件貢獻 35 美元,扣除固定成本前合計 28,000 美元,扣除後為 −7,000 美元。核對 100 美元列與單變數表的結果。這三項核對有助於發現輸出引用錯誤、輸入儲存格對調或結果未更新。
如何解讀敏感度表
先看與決策最相關的數值。銷量 1,000 件時,價格從 100 美元降至 95 美元會耗盡 5,000 美元的基準利益。維持 100 美元單價時,銷量降至 875 件會損益兩平,等於 35,000 美元固定成本除以每件 40 美元的貢獻額。單價為 95 美元時,每件貢獻 35 美元,損益兩平銷量升至 1,000 件。
表格展示指定組合的結果。若預計降價能提高銷量,應覈實依據和團隊是否能承接額外銷量。按現有成本估算,以 95 美元出售時須銷售 至少 1,143 件才能保住 5,000 美元利益。促銷情境還可以納入新增費用和根據以往結果估算的銷量。
管理層簡報可以這樣寫。
單價 95 美元、銷量保持 1,000 件時,季度利益從 5,000 美元降至 0 美元。在當前成本假設下,要保住原有利益,至少需售出 1,143 件。核准折扣前,覈實歷史促銷能否支持該銷量,並查看初期訂單是否達到預期進度。
將活頁簿版本及測試範圍的來源與簡報一起保留。用 Kuse 將已核對的矩陣、假設說明和佐證檔案整理成決策簡報。簡報應列出基準情形、改變決策的臨界點和待補的證據。由模型負責人對照 Excel 覈實數字和表述,再將相同格式用於下一次預測。
常見問題
為甚麼 Excel 敏感度表的每格都顯示同一個值?
手動改變模型輸入,確認關聯輸出變化,再恢復基準值。如果模型輸出改變但矩陣不變,檢查輸出引用和表格實際使用的輸入。若 Excel 跳過資料表自動重算,選擇表格公式重新計算。
為甚麼敏感度表的基準結果與模型不一致?
先檢查表頭是否使用精確的基準值。四捨五入後顯示數字可能相同,Excel 使用的數值卻不同。隨後核對輸出引用、重算表格,並確認其他假設與基準一致。
如何檢查現有資料表使用的輸入儲存格?
選取矩陣中的計算結果,查看資料編輯列。桌面版 Excel 的資料表公式可按順序顯示行輸入和列輸入引用。沿著參照確認它們對應的假設與關聯輸出。
何時使用雙變數資料表或目標搜尋?
要看兩項假設的不同組合,例如價格和銷量下的利益,可用雙變數表。已知目標並希望 Excel 調整一個輸入時,可用目標搜尋,例如固定價格下求利益為零的銷量。
如何為敏感度分析選擇合理的輸入?
從已核准或當前基準出發,用歷史資料、合同條款、供應商報價或預測假設確定範圍。加入接近臨界點的數值,並標注用於探索的取值,便於復核證據。
如何用敏感度表找到損益兩平點?
尋找利益為零的儲存格,或利益正負號變化的相鄰結果。若平衡點在兩個測試值之間,用模型公式或目標搜尋計算。銷量有小數時,視需要向上取整,再核對模型中的營業利益。