Excel 靈敏度分析實用指南：從手動到智能演進

在試算表中設計模型時，我們常常想知道改變一個變數的值會如何影響最終結果。這個過程可以透過在 Excel 中執行敏感度分析來完成。

如果你是從事財務建模或決策流程的數據專業人士，那麼了解如何執行敏感度分析至關重要。

在本文中，我們將深入探討敏感度分析。我們將涵蓋這個有用的「假設」技術的基礎知識、如何在 Excel 中執行和解讀基礎與高階敏感度分析的結果，最後，我們將討論 AI 和機器學習在實現更強大敏感度分析中的作用，以及最佳實踐和常見陷阱。

什麼是 Excel 中的敏感度分析？

敏感度分析最常見的方法是檢查一個變數的變化如何影響最終結果，同時保持所有其他因素不變。這個過程會對所有考慮的變數重複進行。它有助於識別模型中影響最大的驅動因素，讓企業能夠專注於影響其績效的關鍵因素。

敏感度分析透過識別哪些因素對績效影響最大，幫助企業優先處理關鍵風險和機會。鑑於其性質，敏感度分析在金融、商業和專案管理中很常見，例如，用於計算利率隨時間的可能變化，或了解不同的成長趨勢。

此外，敏感度分析通常與情境分析結合使用，後者通常同時分析多個變數的組合。這兩種假設分析都研究應變數如何對某些輸入做出反應。然而，情境分析是針對特定的「情境」，而敏感度分析則更為開放，因為它提供了一系列的輸入和值。

敏感度分析的目標是了解應變數如何對一系列輸入值（稱為自變數）做出反應。

透過將此技術納入決策過程，組織可以主動調整策略並增強對不確定性的韌性。

通常，敏感度分析以帶有條件格式化的敏感度分析表呈現，突顯從最高到最低的值。

以下是價格對供需敏感度的範例。供給和需求是座標軸上的自變數，價格是應變數，佔據表格內的值。

在接下來的部分中，我們將使用類似的 Excel 敏感度分析表。

開始使用 Excel 進行敏感度分析

讓我們來看看如何在 Excel 中執行敏感度分析，遵循流程的每個步驟。

Excel 中的傳統工具和功能

Excel 是建立數學模型的便利工具，包括假設分析。假設分析本質上是關於建立公式來探索像敏感度分析這樣的問題。

大多數較新版本的 Excel 都帶有一個 資料 索引標籤，提供了許多處理資料的工具和可能性。這包括功能區中的 假設分析 按鈕。它包含三個工具。資料表 按鈕專門用於建立敏感度分析表。

要執行情境分析，只需點擊 情境管理員 按鈕。

我們還將使用 規劃求解，這是一個可以從 開發人員 索引標籤啟用的增益集。

規劃求解用於假設分析，以在限制條件下，為一個儲存格（稱為目標儲存格）中的公式找到最佳（最大或最小）值，這些限制是工作表上其他公式儲存格值的約束。

在啟用規劃求解之前，您必須存取 開發人員 索引標籤，該索引標籤預設不顯示。要顯示此索引標籤：

• 前往 檔案 > 選項
• 從左側面板中選擇 自訂功能區
• 在右側欄的「主要索引標籤」下，勾選 開發人員 的方塊

執行單變數敏感度分析

現在我們的工具準備好了，讓我們從在 Excel 中建立一個簡單的單變數敏感度分析開始。

傳統方法：設定您的模型

在 Excel 中執行敏感度分析的第一步是識別模型所依據的輸入和輸出。

在本教學中，我們將使用敏感度分析來研究，如果我們改變一些輸入變數的值（例如銷售的吉他數量、每把吉他的價格或生產成本），一家吉他銷售公司的淨利潤將如何變化。

下面，您可以看到包含輸入和輸出變數的表格。

使用資料表進行敏感度分析時，將輸出儲存格連結到輸入變數非常重要，這樣 Excel 才能理解模型變數之間的關係。這個過程是透過公式完成的。在我們的案例中：

• 營收是透過單價乘以銷售數量計算得出 (=B2*B4)
• 銷售成本是透過單位生產成本乘以銷售數量計算得出 (=B3*B4)
• 利潤是透過營收減去銷售成本計算得出 (=B7-B8)

傳統方法：建立單向資料表

您可能想知道，如果我們在保持生產成本和銷售數量不變的情況下提高或降低單價，利潤會如何變化。當我們分析一個輸出變數如何隨著一個輸入變數值的變化而變化時，我們需要一個所謂的單向資料表。

下面的 GIF 展示了如何在 Excel 中建立單向資料表。如您所見，該表會自動重新計算每個銷售單位的利潤。當然，銷售 250 單位時的利潤與原始參數相同（即 €50,000）。

更聰明的方法：使用 Excelmatic 進行單變數分析

儘管資料表功能很強大，但設定過程需要精確的儲存格參照和多個步驟。對於更複雜或動態的模型，這可能會變得繁瑣且容易出錯。

Excelmatic 提供了一個更直觀的替代方案。作為一個 Excel AI 代理，它允許您使用自然語言執行分析。

要實現相同的目標，您只需：

1. 將包含模型的 Excel 檔案上傳到 Excelmatic。
2. 用簡單的語言陳述您的要求：「建立一個敏感度分析表，顯示當銷售數量（儲存格 B4）從 150 到 500 以 50 為增量變化時，利潤（儲存格 B9）如何變化。」

Excelmatic 立即理解您的模型結構，並自動生成與手動建立的單向資料表完全相同的結果，無需您手動設定表格、連結儲存格或導航任何選單。這種方法的優勢在於其速度和簡單性，讓您可以專注於分析結果，而不是操作工具。

您可能還想檢查不同的輸出變數如何隨單位銷售量變化。在傳統方法中，您需要調整資料表的設定。

使用 Excelmatic，您只需稍微修改您的請求：「顯示當銷售數量（B4）從 150 到 500 變化時，利潤（B9）和銷售成本（B8）如何變化。」 AI 會為您處理所有細節。

進行雙變數敏感度分析

傳統方法：建立雙向資料表

除了分析一個輸出如何受一個輸入影響外，您還可以建立一個雙變數表來分析兩個輸入變數的影響。假設您想知道，如果我們同時修改銷售的吉他數量和每把吉他的價格，利潤會如何變化。

首先，您需要建立一個二維表格，其中行是單位價格的範圍，列是單位銷售量的範圍。然後，在表格的左上角，選擇您要研究的變數。最後，在輸入變數表中，選擇自變數（即單位價格和銷售數量）的儲存格參照。

如您所見，一旦您建立了表格，您可以輕鬆地將左上角的輸出變數更改為另一個變數，Excel 將重新計算所有值。

更聰明的方法：使用 Excelmatic 進行雙變數分析

設定雙向資料表比單向表更複雜，需要正確參照行、列和角落儲存格中的輸入和輸出。

使用 Excelmatic，這個過程也被簡化為一個簡單的指令。上傳檔案後，您可以直接詢問：

「生成一個雙向敏感度表，顯示利潤如何基於銷售數量（從 150 到 500）和單位價格（從 €150 到 €400）變化。」

Excelmatic 為您建立整個二維表格，填入所有計算值，從而消除了手動設定可能導致的參照錯誤。如果您想分析另一個輸出，例如「營收」，只需提出一個新問題：「現在，顯示相同表格但針對營收。」 這種對話式工作流程讓複雜的分析變得輕而易舉。

解讀 Excel 敏感度分析結果

現在您有了資料表，最後一步是解讀結果，以獲得關於輸入變化如何影響輸出的洞察。

分析資料表輸出

基於此敏感度分析，我們可以就利潤做出明智的決策。以下是一些洞察：

• 如果我們將吉他價格提高到 €350，我們只需要銷售 200 把吉他就能達到目前的 €50,000 利潤。
• 如果我們將吉他價格降低到 €200，我們需要銷售 500 單位才能達到目前的利潤。
• 如果我們將銷售的吉他數量減少到 150，除非我們將吉他價格提高到 €400 以上，否則無法達到目前的利潤。

分析的解讀將取決於您公司的具體需求。提出諸如您可以生產多少吉他、您需要維持的最低利潤水平以保持公司健康，以及您可以採取哪些措施來降低吉他生產成本等問題，對於從敏感度分析中提取有意義的洞察至關重要。

傳統方法：使用規劃求解進行最佳化和敏感度分析

當您執行敏感度分析時，您會根據模型輸入變數的變化返回一系列潛在輸出。然而，您可能想了解更多關於模型內部運作的資訊。

幸運的是，Excel 配備了一個名為「規劃求解」的強大工具，可以幫助您完成這項任務。如前所述，規劃求解用於假設分析，以在限制條件下，為一個公式找到最佳（最大或最小）值，這些限制是工作表上其他公式儲存格值的約束。

規劃求解不僅僅是目標搜尋的升級版。首先，規劃求解允許您根據多個變數計算最佳結果，並允許您在模型中包含限制條件。更重要的是，規劃求解還帶有一個可選的敏感度功能，讓您可以看到當您更改模型的係數時，最佳解如何變化。

為了說明規劃求解的強大功能，讓我們回到我們的吉他公司。想像一下，該公司生產兩種吉他型號（型號 A 和型號 B），每種型號都需要一定量的桃花心木和雪松木。每個型號有不同的價格。

該公司想知道，考慮到目前桃花心木和雪松木的可用性，每種型號應生產多少單位才能最大化其利潤。

在下面的 GIF 中，我們展示了如何使用規劃求解來找到最佳單位值，以在木材可用性限制下最大化營收，並確保單位為整數。

一旦我們添加了所有參數值，規劃求解估計生產 48 單位的型號 A 和 9 單位的型號 B 是最大化利潤（€17,591）的最佳組合。我們可以在關閉規劃求解對話方塊之前點擊 敏感度，Excel 將建立一個帶有敏感度報告的新工作表。

敏感度報告為我們提供了寶貴的洞察，以估計如果我們改變輸入變數，利潤將如何變化。陰影價格 表示如果您增加一單位受限制的資源，利潤將增加多少。在這個例子中，每增加一平方公尺的桃花心木會使利潤增加 €98，而雪松則使利潤增加 €103，突顯出雪松具有更大的潛在投資回報。

更聰明的方法：使用 Excelmatic 進行高階最佳化

規劃求解的功能很強大，但其介面和設定對許多使用者來說可能複雜且不直觀。您需要精確定義目標儲存格、變數儲存格和限制條件。

Excelmatic 也將這個複雜的最佳化問題轉變為簡單的對話。您只需描述您的目標：

「透過改變型號 A（G4）和型號 B（G5）的單位數來最大化利潤（儲存格 G10），限制條件是使用的桃花心木總量（D14）不能超過 2000，使用的雪松總量（E14）不能超過 3000，且單位必須為整數。然後，生成一份敏感度報告。」

Excelmatic 解析您的請求，執行最佳化演算法，提供最佳生產組合，並生成一份易於理解的敏感度報告。這不僅節省時間，還降低了因規劃求解參數設定錯誤而導致分析不準確的風險。

使用條件格式化視覺化資料

傳統方法

如果您建立了單向和雙向敏感度分析表，格式化結果可以改變遊戲規則，幫助您快速發現相關資訊並增強決策。條件格式化是建立重要閾值和引人注目的熱力圖的關鍵。

讓我們回到我們的雙向表。我們可以使用條件格式化來建立多色熱力圖或雙色刻度，並設定自訂閾值，如下面的 GIF 所示。

更聰明的方法：使用 Excelmatic 一鍵視覺化

手動設定條件格式化規則（尤其是複雜的閾值）可能非常耗時。使用 Excelmatic，視覺化 也只是一個簡單的請求。生成敏感度分析表後，您可以接著說：

「對這個表格套用條件格式化。使用綠到紅的色階，其中較高的利潤值為綠色。」

Excelmatic 將立即為您套用格式化，產生一個清晰、直觀的熱力圖，讓您一目了然地看到哪些變數組合產生最佳和最差的結果。

整合 AI 和機器學習

AI 增強的敏感度分析

到目前為止，我們已經看到了如何手動執行敏感度分析。然而，隨著模型複雜度的增加，進行和解讀敏感度分析的結果可能變得非常耗時。

這就是 AI 的用武之地。像 Excelmatic 這樣的 AI 工具是「AI 增強敏感度分析」的實際應用。它自動化了手動計算、設定和視覺化的過程，讓您能即時獲得答案和洞察。您不需要自己建立複雜的 AI 流程；Excelmatic 為您提供了一個即用、強大的分析引擎。

蒙地卡羅模擬

蒙地卡羅模擬是一種數學技術，用於模擬因隨機變數介入而難以預測的過程中不同結果的機率。

您可以利用這項強大的技術來增強您的敏感度分析，找出哪些變數對輸出影響最大。在我們的吉他範例中，我們可以保持成本價和銷售數量不變，並改變單位價格以了解估計值的變化。然後，對其餘兩個變數重複相同的過程，一次一個。

使用 Excelmatic，執行蒙地卡羅模擬也變得更容易。您可以上傳您的模型並請求：「對利潤執行一個 10,000 次試驗的蒙地卡羅模擬，假設單位價格服從平均值為 €300、標準差為 €20 的常態分佈。」 AI 會處理複雜的隨機抽樣和計算，並向您展示結果分佈。

最佳實踐和常見陷阱

雖然 Excel 讓敏感度分析變得極其簡單和易於使用，但仍有一些常見的陷阱需要注意。

設計穩健的模型

正如您在本教學中所見，為了讓敏感度分析發揮作用，Excel 必須知道輸入和輸出變數之間的關係。這要求您明智地使用公式，具有明確定義的儲存格參照和範圍。使用像 Excelmatic 這樣的 AI 工具可以幫助減少因手動公式錯誤引起的問題，因為它直接理解模型邏輯。

減少錯誤並確保準確性

即使您的敏感度分析定義良好，您仍然需要仔細驗證結果。手動測試和圖表是視覺化結果並檢查輸出中異常值或異常的好方法。

然而，重要的是要記住，敏感度分析可能不適用於您的使用案例。這種分析基於一些簡單的假設，例如輸入變數的獨立性、線性關係的存在以及輸入變數的靜態性質。然而，您的模型可能更複雜，並且可能受到敏感度分析固有忽略的外部因素的影響。

結論

恭喜您完成本教學。敏感度分析是一種強大的假