如何在 Excel 中進行敏感度分析：逐步教學
要在 Excel 中進行敏感度分析，請將模型的每個輸入項放在獨立的儲存格中。接著更改這些輸入項並記錄結果的變動情況。針對單一輸入項使用單變數數據表，針對兩個輸入項使用雙變數數據表，而完整案例則使用分析藍本管理員。最後以龍捲風圖結束，依影響程度對輸入項進行排序。
本指南將解釋什麼是敏感度分析、如何設定模型，以及如何使用各項 Excel 工具。內容包含一個帶有實際公式的實作範例、一種使用 AI 的更快速方法，以及應避免的錯誤。
什麼是敏感度分析
敏感度分析顯示當一個假設改變時，結果會產生多大的變化。它回答了諸如「如果價格下降 10%，利潤會發生什麼變化？」之類的問題。
大多數商業模型都建立在猜測之上。銷售量、價格、成本和增長率都是估計值。敏感度分析測試這些猜測中哪些最為重要。
Excel 將這些工具歸類在「模擬分析」下。Microsoft 將其定義為「更改儲存格中的值」以查看公式結果如何變化的過程。
Microsoft 列出了三種內建工具：「分析藍本、目標搜尋和數據表」。每種工具都有不同的用途：
- 數據表顯示一個結果在一個或兩個輸入項的多個值下的變化。
- 分析藍本管理員儲存完整的輸入項組合，例如最差情況和最佳情況。
- 目標搜尋則是反向操作。它找出達到目標結果所需的輸入值。
敏感度分析通常使用前兩者。目標搜尋則有助於解決相關問題，例如損益平衡價格。
在開始之前設定模型
Excel 的工具只有在模型建構清晰的情況下才能發揮作用。請先檢查以下幾點。
將每個輸入項放在獨立的儲存格中。單位、價格、成本和比率各需要一個儲存格。不要在公式中直接輸入數字。
將結果與輸入項連結。結果儲存格（如利潤）必須是一個引用輸入儲存格的公式。如果不這樣做，當 Excel 替換新值時，結果將不會有任何變化。
將輸入項集中在一起。將它們分組放在工作表頂部的一個區塊中。為每個項目加上標籤，並為輸入儲存格上色以便區分。
選擇一個結果進行測試。當敏感度分析追蹤單一輸出項（如利潤、淨現值或年底現金）時，會更容易閱讀。
記錄您的基準案例。寫下起始值。分析中的每個結果都會與此基準進行比較。
這是一個可以跟著操作的簡單利潤模型。這是一家虛構的自行車店 Northgate Cycles：
| 儲存格 | 標籤 | 數值 |
|---|---|---|
| B1 | 銷售單位 | 2,400 |
| B2 | 單位售價 | $850 |
| B3 | 單位變動成本 | $510 |
| B4 | 固定成本 | $520,000 |
| B5 | 利潤 | =B1*(B2-B3)-B4 |
在這些基準值下，利潤為 $296,000。
如何使用數據表進行單變數敏感度分析
單變數數據表測試單一輸入項的一系列數值。Microsoft 表示它顯示了「一個或多個公式中某個變數的不同值將如何改變結果」。
以下是在範例模型中測試價格的方法：
- 在 D2 到 D6 中，輸入要測試的價格：$800、$825、$850、$875 和 $900。
- 在 E1（第一個價格上方一列、右側一欄）輸入 =B5。
- 選取 D1:E6。
- 在「資料」索引標籤上，選取「模擬分析」>「數據表」。
- 在「欄輸入儲存格」中，輸入 B2（價格儲存格）。將「列輸入儲存格」留白。
- 按一下「確定」。
Excel 會在 E2:E6 中填入每個價格對應的利潤。公式的放置位置至關重要。Microsoft 說明應將其輸入在「數值欄上方一列且右側一格」的位置。
Northgate Cycles 的結果如下：
| 價格 | 利潤 |
|---|---|
| $800 | $176,000 |
| $825 | $236,000 |
| $850 | $296,000 |
| $875 | $356,000 |
| $900 | $416,000 |
價格每變動 $25，利潤就會變動 $60,000。這就是利潤對價格的敏感度。
如何進行雙變數敏感度分析
雙變數數據表同時測試兩個輸入項。Microsoft 表示它顯示了「一個公式中兩個變數的不同值將如何改變該公式的結果」。
以下是同時測試價格和單位的方法：
- 在一個空白儲存格（如 D10）中輸入 =B5。
- 在其下方 D11 到 D13 中，輸入單位銷量：2,000、2,400 和 2,800。
- 在其右側 E10 到 G10 中，輸入價格：$800、$850 和 $900。
- 選取 D10:G13。
- 選取「模擬分析」>「數據表」。
- 在「列輸入儲存格」中輸入 B2。在「欄輸入儲存格」中輸入 B1。
- 按一下「確定」。
結果是一個利潤網格：
| 單位 \ 價格 | $800 | $850 | $900 |
|---|---|---|---|
| 2,000 | $60,000 | $160,000 | $260,000 |
| 2,400 | $176,000 | $296,000 | $416,000 |
| 2,800 | $292,000 | $432,000 | $572,000 |
網格顯示該店在測試的每種組合中都能保持獲利。最弱的角落（以 $800 售出 2,000 單位）仍有 $60,000 的利潤。
這有一個硬性限制。Microsoft 表示「數據表不能容納超過兩個變數」。若要處理更多輸入項，請使用分析藍本管理員。
數據表也會在工作表重新計算時跟著重新計算。在大型活頁簿中，這可能會拖慢 Excel 的速度。Microsoft 建議將計算設定為僅在按下 F9 時才重新計算數據表。
如何使用分析藍本管理員比較完整案例
分析藍本管理員儲存完整的輸入項組合並進行切換。Microsoft 將分析藍本描述為「Excel 儲存的一組值，並可在您的工作表上自動替換」。
以下是設定三個案例的方法：
- 在「資料」索引標籤上，選取「模擬分析」>「分析藍本管理員」。
- 按一下「新增」，將分析藍本命名為「基準」，並輸入 B1:B4 作為變更儲存格。保留目前的值。
- 新增一個「最差情況」：2,000 單位、$825 價格、$530 單位變動成本以及 $540,000 固定成本。
- 新增一個「最佳情況」：2,800 單位、$875 價格、$495 單位變動成本以及 $510,000 固定成本。
- 選取一個分析藍本並按一下「顯示」以載入其數值。
- 按一下「摘要」並選擇 B5 作為結果儲存格以建立報告。
對於 Northgate Cycles，摘要顯示了三種利潤。最差情況獲利 $50,000，基準案例為 $296,000，最佳情況則為 $554,000。
Microsoft 提到了一些限制。每個分析藍本最多可包含 32 個變更值。摘要報告不是自動的：「分析藍本報告不會自動重新計算」。如果您更改了分析藍本，請建立新的摘要。
如何使用目標搜尋找出損益平衡點
敏感度分析顯示結果變動的幅度。目標搜尋則回答相反的問題：什麼樣的輸入值會產生目標結果？
Microsoft 表示目標搜尋「獲取一個結果並確定產生該結果的可能輸入值」。其範例是找出貸款還款所需的利率。
以下是找出 Northgate Cycles 損益平衡價格的方法：
- 在「資料」索引標籤上，選取「模擬分析」>「目標搜尋」。
- 在「目標儲存格」中，輸入 B5（利潤儲存格）。
- 在「目標值」中，輸入 0。
- 在「變數儲存格」中，輸入 B2（價格儲存格）。
- 按一下「確定」。
Excel 會更改價格直到利潤歸零。在 2,400 單位和基準成本下，損益平衡價格約為 $726.67。這比目前的 $850 價格低了約 15%。
目標搜尋有一個限制。Microsoft 表示它「僅適用於單一變數輸入值」。對於多個輸入項，Microsoft 建議使用「規劃求解」增益集。
如何建立龍捲風圖
龍捲風圖依輸入項對結果的影響程度進行排序。這是呈現敏感度分析最清晰的方式。
以下是建立方法：
- 選擇一個測試範圍，例如每個輸入項分別降低 10% 和提高 10%。
- 一次更改一個輸入項，並記錄低結果和高結果。保持其他輸入項為基準值。
- 將結果放入表格中，每個輸入項佔一行。
- 對行進行排序，使範圍最寬的輸入項位於頂部。
- 插入低結果和高結果的橫條圖。
對於 Northgate Cycles，每個輸入項上下變動 10% 的結果如下：
| 輸入項 | −10% 時的利潤 | +10% 時的利潤 | 範圍 |
|---|---|---|---|
| 價格 | $92,000 | $500,000 | $408,000 |
| 變動成本 | $418,400 | $173,600 | $244,800 |
| 銷售單位 | $214,400 | $377,600 | $163,200 |
| 固定成本 | $348,000 | $244,000 | $104,000 |
價格的影響最大，且差距顯著。價格下降 10% 會使利潤從 $296,000 削減至 $92,000。固定成本的影響最小。這告訴業主應該花時間檢查哪些假設。
Excel 中的橫條圖是從左向右繪製的。為了獲得龍捲風形狀，請繪製每個輸入項相對於基準利潤的變化量，而不是原始數值。這樣，低情況的橫條會指向左側，而高情況的橫條會指向右側。
如何使用 AI 進行敏感度分析
為大型模型建立數據表、分析藍本和龍捲風圖非常耗時。AI 工具可以直接從您的活頁簿執行相同的測試。
以下是使用 Powerdrill Bloom 的三個步驟。
步驟 1：上傳您的模型
從包含您模型的試算表開始。AI 敏感度分析產生器會要求您上傳「DCF、商業案例、營運計畫或任何試算表模型」。
確保輸入項和結果儲存格都有清晰的標籤。該工具需要知道哪些數字是假設值。
步驟 2：使用自然語言選擇敏感度視圖
描述您想要的測試。例如：「顯示利潤的龍捲風圖，每個輸入項上下變動 10%。並增加一個價格和銷售單位的雙向表。」
工具頁面列出了四種視圖：「龍捲風圖、雙向表、蒙地卡羅或損益平衡」。它將龍捲風圖輸出描述為「對影響答案最大的輸入項進行排序」的圖表。它還提供「針對兩個重要假設的敏感度表」。
步驟 3：檢查結果並匯出分析
在分享之前，將一兩個結果與您自己的數據表進行比較。基準案例應與您的模型完全一致。
然後匯出。工具頁面列出了「Excel 活頁簿、品牌 PDF 或畫布連結」。對於簡報，Excel 轉 PPT 工具可將相同檔案轉換為投影片。AI 財務建模頁面則涵蓋了建構模型本身的內容。
如何閱讀與呈現結果
敏感度分析只有在人們根據它採取行動時才有用。這些習慣會有所幫助。
以排序開場。先展示龍捲風圖。它透過一張圖片顯示哪些假設驅動了結果。
將範圍轉化為決策。「如果價格下降 10%，利潤將降至 $92,000」是一個事實。「我們應該在簽署租約前測試價格」則是一個決策。
顯示損益平衡點。將分析與結果歸零時的數值配對。對於 Northgate Cycles，在基準價格和成本下，損益平衡點約為 1,530 單位。我們的 Excel 損益平衡分析指南涵蓋了該計算方法。
使用現實的範圍。10% 的變動適合穩定的輸入項。新產品的銷量可能需要更寬的範圍。
說明哪些是保持不變的。每次單一測試都會保持其他輸入項為基準值。請在投影片或腳註中說明。讀者就會知道 10% 的價格變化是假設銷量、成本和固定支出不變。這一行說明可以避免大多數關於方法的後續問題。
在結果出來後重新審視。一旦實際數字出現，請將其與您的範圍進行比較。預算 vs. 實際報告是自然的後續行動。
常見的敏感度分析錯誤
這些錯誤很常見且容易避免。
在公式中寫死數字。如果公式包含 850 而不是 B2，數據表就無法更改它。請將每個輸入項保留在儲存格中。
一次測試太多輸入項。數據表只能處理一到兩個。若要處理更多，請改用分析藍本管理員或龍捲風圖。
對每個輸入項使用相同的範圍。價格和銷量很少具有相同的抗不確定性。請選擇反映您實際認知情況的範圍。
忽略輸入項之間的關聯。較低的價格可能會提高銷量。單一測試會遺漏這一點，因此請增加一個同時變動兩者的分析藍本。
過時的分析藍本摘要。分析藍本報告不會自行更新。在任何更改後請重新建立它們。
忘記基準案例。每個表格都應顯示基準結果，以便讀者看到每次變化的幅度。
當您準備好對下一個模型進行壓力測試時，請搭配您用於規劃的活頁簿嘗試 Powerdrill Bloom。
常見問題
什麼是 Excel 中的敏感度分析？
這是一種測試當輸入項改變時，結果如何變化的方法。在 Excel 中，您可以使用模擬分析工具（主要是數據表和分析藍本管理員）來替換新的輸入值並記錄結果。
如何在 Excel 中進行單變數敏感度分析？
在一欄中列出輸入值。在上方一列、右側一欄的位置放入引用結果的公式。選取該範圍，選擇「模擬分析」>「數據表」，並將輸入儲存格輸入為「欄輸入儲存格」。
數據表和分析藍本管理員有什麼區別？
數據表在多個數值下測試一到兩個變數，並在一個網格中顯示所有結果。分析藍本管理員則儲存完整的輸入項組合（每個最多 32 個變更值），並一次替換一組。
如何在 Excel 中製作龍捲風圖？
一次一個，將每個輸入項上下移動相同的量，並記錄結果。依範圍大小對輸入項進行排序，然後將每個輸入項相對於基準結果的變化繪製為水平橫條。
AI 可以做敏感度分析嗎？
是的。AI 工具可以讀取您的試算表模型，執行龍捲風圖和雙向測試，並將結果匯出為活頁簿或報告。您仍然需要檢查基準案例並選擇現實的範圍。
資料來源： Microsoft 支援，模擬分析簡介 · Microsoft 支援，使用數據表計算多個結果 · Microsoft 支援，使用分析藍本在各種數值組合之間切換。