如何在Excel中進(jìn)行收支平衡分析？

假設(shè)您要銷售一種新產(chǎn)品，并且知道每單位的可變成本和總固定成本。 現(xiàn)在，您將預(yù)測可能的銷售量，并根據(jù)這些價格為產(chǎn)品定價。

1

。 制作一個簡單的表，并在表中填充給定數(shù)據(jù)。 見下面的截圖：

2

。 輸入適當(dāng)?shù)墓揭杂嬎闶杖耄勺兂杀竞屠麧櫋?見上面的截圖：

收入

=單價x已售單位

可變成本

=單位成本x售出單位

利潤 =收入–可變成本–固定成本

3。 點擊 Data > 假設(shè)分析 > 目標(biāo)尋求

.

4

。 在打開的“目標(biāo)搜索”對話框中，請執(zhí)行以下操作（請參見上面的屏幕截圖）：

（1）指定 設(shè)置單元格

作為利潤單元，在我們的例子中是單元格B7；

（2）指定 重視 as 0

;

（3）指定 通過改變細(xì)胞

作為單價單元格，在我們的例子中是單元格B1。

（4）點擊 OK 按鈕。

5。 然后彈出“目標(biāo)搜索狀態(tài)”對話框。 請點擊 OK

按鈕以應(yīng)用它。

現(xiàn)在，它將單價從40更改為31.579，并且凈利潤更改為0。因此，如果您預(yù)測銷量為50，并且單價不能小于31.579，否則會發(fā)生損失。

下載復(fù)雜的收支平衡模板

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

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

演示：使用Excel中的“目標(biāo)搜索”功能進(jìn)行收支平衡分析

輕松地將多個工作表/工作簿/ CSV文件組合到一個工作表/工作簿中

將來自不同工作簿的數(shù)十張工作表合并到一張工作表中可能會很乏味。 但是隨著 Kutools for Excel“ 合并（工作表和工作簿） 實用程序，只需單擊幾下就可以完成！

與“目標(biāo)尋求”功能相比，我們還可以應(yīng)用公式輕松地在Excel中進(jìn)行收支平衡分析。

1。 制作一個簡單的表，并在表中填充給定數(shù)據(jù)。 在這種方法中，我們假設(shè)利潤為0，并且我們已經(jīng)預(yù)測了已售出的單位，單位成本和固定成本。 見下面的截圖：

2。 在表格中，輸入公式 = B6 / B2 + B4 進(jìn)入單元格B1中以計算單價，鍵入公式 = B1 * B2 進(jìn)入單元格B3中以計算收入，然后鍵入公式 = B2 * B4

進(jìn)入B5單元以獲取可變成本。 見下面的截圖：

然后，當(dāng)您更改預(yù)測的已售單價，每單位成本或固定成本的一個值時，單價的值將自動更改。 見上面的截圖：

如果已經(jīng)記錄了銷售數(shù)據(jù)，則還可以在Excel中使用圖表進(jìn)行收支平衡分析。 此方法將指導(dǎo)您輕松創(chuàng)建收支平衡圖。

1

。 準(zhǔn)備一個銷售表，如下圖所示。 在我們的案例中，我們假設(shè)已售出的單位，每單位成本和固定成本是固定的，并且需要按單價進(jìn)行收支平衡分析。

2

。 完成如下所示的表：

（1）在單元格E2中，鍵入公式 = D2 * $ B $ 1，并將其自動填充手柄向下拖動到RangeE2：E13;

（2）在單元格F2中，鍵入公式 = D2 * $ B $ 1 + $ B $ 3，并將其自動填充手柄向下拖動到范圍F2：F13；

（3）在單元格G2中，鍵入公式 = E2-F2，并將其自動填充手柄向下拖動到范圍G2：G13。

到目前為止，我們已經(jīng)完成了稍后將創(chuàng)建的盈虧平衡圖的源數(shù)據(jù)。 見下面的截圖：

3。 在表中，請同時選擇收入列，成本列和利潤列，然后單擊 插頁 > 插入折線圖或面積圖 > Line

。 看截圖：

4

。 現(xiàn)在創(chuàng)建了一個折線圖。 請右鍵單擊圖表，然后從上下文菜單中選擇“選擇數(shù)據(jù)”。 見下面的截圖：

5.在“選擇數(shù)據(jù)源”對話框中，請：

（1）在 圖例條目（系列） 部分，根據(jù)需要選擇系列之一。 在我的示例中，我選擇了 收入

系列;

（2）點擊 編輯 按鈕在 水平（類別）軸標(biāo)簽

部分;

（3）在彈出的“軸標(biāo)簽”對話框中，請指定“單價”列（列名稱除外）作為軸標(biāo)簽范圍；

（4）點擊 OK > OK

保存更改。

現(xiàn)在在收支平衡圖中，當(dāng)價格等于36時，您將看到收支平衡點，如下圖所示：

同樣，您也可以創(chuàng)建一個盈虧平衡圖，按已售出的單位來分析盈虧平衡點，如下圖所示：

演示：使用Excel中的圖表進(jìn)行收支平衡分析

在Excel中輕松導(dǎo)出/保存選擇為單獨(dú)的PDF / CSV / TEXT / HTML文件

例如，當(dāng)您在 Excel 中完成收支平衡分析時，您可能希望使用此收支平衡分析進(jìn)行演示。 在這種情況下，您可以申請 Kutools for Excel“ 將范圍導(dǎo)出到文件 實用程序可快速將選區(qū)導(dǎo)出為單獨(dú)的PDF文件，HTML文件，CSV文件，文本文件等。

最佳辦公生產(chǎn)力工具

在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發(fā)布者，Access，Visio和Project。 在同一窗口的新選項卡中而不是在新窗口中打開并創(chuàng)建多個文檔。 每天將您的工作效率提高50％，并減少數(shù)百次鼠標(biāo)單擊！