如何在 Excel 中進行損益平衡分析？

假設您即將銷售一款新產品，並且您知道每單位的可變成本和總固定成本。現在您將預測可能的銷售量，並根據這些數據為產品定價。

1. 創建一個簡單的表格，並將給定的數據填入表格中。請參見下面的截圖：

2. 輸入適當的公式來計算收入、可變成本和利潤。請參見上面的截圖：
收入 = 單價 x 銷售單位數
可變成本 = 每單位成本 x 銷售單位數
利潤 = 收入 – 可變成本 – 固定成本

3. 點擊資料 > 假設分析 > 目標搜尋。

4. 在彈出的目標搜尋對話框中，請執行以下操作（請參見上面的截圖）：
(1) 將設定單元格指定為利潤單元格，在我們的情況下是單元格 B7；
(2) 將目標值指定為0；
(3) 將變動單元格指定為單價單元格，在我們的情況下是單元格 B1。
(4) 點擊確定按鈕。

5. 然後會彈出目標搜尋狀態對話框。請點擊確定按鈕以應用它。

現在它將單價從 40 變為 31.579，淨利潤變為 0。因此，如果您預測銷售量為 50，則單價不能低於 31.579，否則會出現虧損。

下載複雜的損益平衡模板

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

與目標搜尋功能相比，我們也可以應用公式輕鬆地在 Excel 中進行損益平衡分析。

1. 創建一個簡單的表格，並將給定的數據填入表格中。在此方法中，我們假設利潤為 0，並且已經預測了銷售單位數、每單位成本和固定成本。請參見下面的截圖：

2. 在表格中，將公式 =B6/B2+B4 輸入到單元格 B1 中以計算單價，將公式 =B1*B2 輸入到單元格 B3 中以計算收入，將公式 =B2*B4 輸入到單元格 B5 中以計算可變成本。請參見下面的截圖：

然後當您更改預測銷售單位數、每單位成本或固定成本中的任何一個值時，單價的值將自動更改。請參見上面的截圖：

提示： 將範圍保存為自動文字條目（保留單元格格式和公式）以便未來重用
每次都引用單元格並應用多個公式進行損益平衡分析一定非常繁瑣。Kutools for Excel 提供了一個可愛的自動文字工具解決方案，將範圍保存為自動文字條目，該條目可以保留範圍內的單元格格式和公式。然後您可以通過一次點擊在任何工作簿中重用此範圍。

立即獲取 30-天全功能免費試用！

如果您已經記錄了銷售數據，您還可以在 Excel 中使用圖表進行損益平衡分析。這種方法將指導您輕鬆創建損益平衡圖表。

1. 準備一個如下截圖所示的銷售表格。在我們的情況下，我們假設銷售單位數、每單位成本和固定成本是固定的，我們需要通過單價進行損益平衡分析。

2. 完成如下所示的表格：
(1) 在單元格 E2 中，輸入公式 =D2*$B$1，並向下拖動其自動填充柄至範圍 E2:E13；
(2) 在單元格 F2 中，輸入公式 =D2*$B$1+$B$3，並向下拖動其自動填充柄至範圍 F2:F13；
(3) 在單元格 G2 中，輸入公式 =E2-F2，並向下拖動其自動填充柄至範圍 G2:G13。

到目前為止，我們已經完成了稍後將創建的損益平衡圖表的源數據。請參見下面的截圖：

3. 在表格中，請同時選擇收入列、成本列和利潤列，然後點擊插入 > 插入折線圖或面積圖 > 折線圖。請參見截圖：

4. 現在已創建了一個折線圖。請右鍵單擊圖表，並從上下文菜單中選擇選擇數據。請參見下面的截圖：

5. 在選擇數據源對話框中，請：
(1) 在系列名稱（系列）部分，選擇所需的系列之一。在我的示例中，我選擇了收入系列；
(2) 點擊水平（分類）軸標籤部分中的 編輯按鈕；
(3) 在彈出的軸標籤對話框中，請指定單價列（不包括列名）作為軸標籤區域；
(4) 點擊確定 > 確定以保存更改。

現在在損益平衡圖表中，您會看到當價格等於 36 時發生損益平衡點，如下截圖所示：

同樣，您也可以創建一個損益平衡圖表來分析銷售單位數的損益平衡點，如下截圖所示：

在 Excel 中輕鬆導出/保存選定範圍為單獨的 PDF/CSV/TEXT/HTML 文件

例如，當您完成在 Excel 中的損益平衡分析時，您可能希望使用此損益平衡分析進行演示。在這種情況下，您可以應用 Kutools for Excel 的導出範圍至文件工具快速將選定範圍導出為單獨的 PDF 文件、HTML 文件、CSV 文件、文本文件等。