如何在 Excel 中製作損益兩平分析：完整指南

損益兩平分析旨在找出總收入等於總成本的銷售水準，此時企業既不獲利也不虧損。若要在 Excel 中進行此分析，請輸入您的固定成本、單價和每單位變動成本。將固定成本除以價格與變動成本之間的差額，然後繪製收入與總成本的對照圖表。

本指南涵蓋了相關公式、您所需的輸入資料、三個 Excel 步驟以及一個實際範例。此外，還將展示如何使用「目標搜尋」和「資料表」來測試當價格或成本變動時會產生什麼變化。

什麼是損益兩平分析

美國中小企業管理局（SBA）將損益兩平點定義為「總成本與總收入相等的點」。低於該點，每次銷售仍不足以彌補企業的成本；高於該點，每次銷售則能增加利潤。

SBA 將損益兩平分析列為計算創業成本的原因之一，此外還包括估算利潤和取得貸款。貸款機構和投資人通常會要求提供此分析，因為它能顯示企業在停止虧損前必須達到多少銷售量。

損益兩平分析可以回答三個實際問題：我們需要銷售多少單位？這代表多少收入？以及該結果對價格和成本變動有多敏感？

它也可以作為新想法的快速測試。在您投入新產品或新據點之前，請先估算這三項輸入資料。然後評估所需的銷售量對於您所服務的市場而言是否切合實際。

損益兩平公式

SBA 提供了兩種版本的損益兩平公式。

以單位計算：

損益兩平點（單位）= 固定成本 /（每單位售價 - 每單位變動成本）

以銷售金額計算：

損益兩平點（銷售金額）= 固定成本 / 邊際貢獻率

SBA 將第二個項目解釋為「產品價格與製造該產品成本之間的差額」。對於銷售金額公式，它將該邊際貢獻計算為一個比率：價格減去變動成本，再除以價格。

這種區別在試算表中非常重要。每單位邊際貢獻是一個金額，例如 5 美元產品的邊際貢獻為 3 美元。而邊際貢獻率是一個百分比，例如 60%。在單位公式中使用金額數值，在銷售金額公式中使用比率。

SBA 還設定了一個界限：「此損益兩平分析是建立在單一產品或服務的基礎上。」後續章節將介紹如何處理多種產品的情況。

開始之前您需要準備的資料

每項損益兩平分析都由三種輸入資料驅動。正確拆分成本比做好 Excel 操作更為重要。

輸入資料	代表意義	範例
固定成本	無論銷售量多少都保持不變的成本	租金、薪資、保險、軟體訂閱費
每單位變動成本	隨著每售出一個單位而增加的成本	原材料、包裝、付款手續費、運費
單價	客戶為一個單位支付的金額	定價，或折扣後的平均售價

每個輸入資料請使用相同的時間週期。如果租金是按月計算，得出的結果就是每月的損益兩平點。將年薪與月租金混在一起計算，得出的數字將毫無意義。

每單位變動成本是人們最常憑空猜測的輸入資料。相反地，請根據歷史數據進行估算。將上一季的總變動成本除以同一季售出的單位數。如果各季之間的結果波動很大，請使用多個週期的平均值。

某些成本是混合的。例如包含基本費和使用費的電話方案，就同時有固定部分和變動部分。請將它們拆分，而不是猜測它們屬於哪一類。

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

以下三個步驟將建立一個可運作的損益兩平模型和圖表。它們使用的是同樣適用於 Google Sheets 的標準公式。

步驟 1：設定輸入資料

開啟一張空白工作表，將儲存格 A1 到 A3 分別標記為「固定成本」、「單價」和「每單位變動成本」。在 B1 到 B3 中輸入對應的數值。

請將輸入資料保留在各自的儲存格中，切勿直接輸入到公式中。這樣一來，當輸入資料變更時，後續的每個計算都會自動更新，您也可以透過編輯單一儲存格來測試不同的情境。

用淺色填滿格式化這些輸入儲存格。這能向任何開啟該檔案的人提示哪些數字是可以修改的。

步驟 2：計算損益兩平點

在第 5 到 8 列中新增計算公式。

• 在 A5 中輸入「每單位邊際貢獻」，並在 B5 中輸入 =B2-B3。
• 在 A6 中輸入「損益兩平單位數」，並在 B6 中輸入 =ROUNDUP(B1/B5,0)。
• 在 A7 中輸入「邊際貢獻率」，並在 B7 中輸入 =B5/B2。
• 在 A8 中輸入「損益兩平銷售額」，並在 B8 中輸入 =B1/B7。

在 B6 中使用 ROUNDUP（無條件進位）非常重要。您無法銷售不足一單位的零碎產品，而無條件捨去則會使您差一點才能達到損益兩平。

檢查 B8 是否大致等於 B6 乘以價格。如果不是，則表示其中一個輸入資料放錯了儲存格。

步驟 3：建立損益兩平圖表

在計算結果下方建立一個小表格。在 D 欄中，以均等間距列出從 0 開始遞增的單位銷售量，例如 0、500 和 1,000。請繼續列出超過您損益兩平點的數值。在 E 欄中，使用 =D11*$B$2 計算收入。在 F 欄中，使用 =$B$1+D11*$B$3 計算總成本。

選取 D 到 F 欄並插入圖表。帶有平滑線的散佈圖效果最好，因為它會將單位欄視為真正的數值軸。

收入線從零開始並陡峭上升。總成本線從固定成本水準開始，上升速度較慢。兩條線相交的地方就是損益兩平點。在該處新增資料標籤，這樣讀者就不用自行估算。將交叉點右側的區域塗上淺色，以顯示獲利區域。

實際範例

一輛咖啡餐車每月的固定成本為 6,000 美元。咖啡每杯售價為 5.00 美元，每杯咖啡在咖啡豆、牛奶、紙杯和刷卡手續費方面的成本為 2.00 美元。

計算項目	結果
每單位邊際貢獻	$5.00 - $2.00 = $3.00
損益兩平單位數	$6,000 / $3.00 = 2,000 杯
邊際貢獻率	$3.00 / $5.00 = 60%
損益兩平銷售額	$6,000 / 0.60 = $10,000

該餐車每月需要售出 2,000 杯咖啡，或達到 10,000 美元的收入，才能彌補其成本。此後每多賣一杯，就能增加 3.00 美元的利潤。

目標利潤也使用相同的邏輯。若要每月賺取 3,000 美元，請將其加入固定成本：9,000 美元除以 3.00 美元等於 3,000 杯。

安全邊際顯示了緩衝空間有多大。如果該餐車預期售出 2,600 杯，那麼在開始虧損之前，它可以少賣 600 杯。這大約是預期銷售量的 23%。

使用目標搜尋尋找損益兩平價格

有時問題的方向正好相反。您知道自己的銷售量，並想知道達到損益兩平的價格是多少。

Microsoft 的支援頁面簡單地描述了這個使用情境：您知道自己想要從公式中得到什麼結果，但不知道產生該結果的輸入值。目標搜尋可以透過調整單一儲存格來找到該輸入值。

新增一個利潤儲存格（例如 B9），輸入 =B2*C1-B1-B3*C1，其中 C1 為您的預期銷售量。然後按照 Microsoft 的步驟操作：在「資料」索引標籤的「預測」群組中，選取「模擬分析」，然後選取「目標搜尋」。將儲存格 B9 的目標值設定為 0，並藉由變更儲存格 B2 來達成。

Excel 會調整價格，直到利潤剛好為零。以每月 1,500 杯計算，咖啡餐車需要將價格定為 6.00 美元才能達到損益兩平。

Microsoft 指出了一個限制：「目標搜尋只能處理一個變數輸入值。」若要同時求解多個輸入值，它建議使用 Solver 增益集。

敏感度分析：如果價格或成本變動會怎樣？

單一的損益兩平數字隱藏了其脆弱性。敏感度分析表可以顯示在一系列不同輸入資料下的結果。

以咖啡餐車為例，價格或成本的微小變化會顯著影響損益兩平點：

情境	邊際貢獻	損益兩平單位數
基本情況：價格 5.00 美元，成本 2.00 美元	$3.00	2,000
價格上漲至 $5.50	$3.50	1,715
變動成本上漲至 $2.50	$2.50	2,400
固定成本上漲至 $7,500	$3.00	2,500

Excel 中「模擬分析」下的「資料表」功能可以自動建立像這樣的網格。將一系列價格列在某一欄，並將一系列變動成本列在某一列。然後將該資料表指向損益兩平單位數的儲存格。

比較相同幅度的變化，看看哪個輸入資料的影響最大。在這個範例中，50 美分的變動如果發生在變動成本上，會使損益兩平點增加 400 杯；但如果反映在價格上，則只會減少 285 杯。這就是為什麼變動成本通常是最值得優先談判的成本。

多種產品的損益兩平分析

SBA 的公式假設為單一產品。大多數企業銷售多種產品，且每種產品都有自己的邊際貢獻。

通常的做法是計算加權平均邊際貢獻。估算每種產品佔銷售額的比例，將每種產品的邊際貢獻乘以其比例，然後相加。將固定成本除以該加權數值，即可得出整個產品組合的損益兩平單位數。

這裡有一個簡短的範例。產品 A 的邊際貢獻為 3 美元，佔單位銷售量的 60%。產品 B 的邊際貢獻為 5 美元，佔 40%。加權邊際貢獻為 3 美元乘以 0.6 加上 5 美元乘以 0.4，即 3.80 美元。在固定成本為 7,600 美元的情況下，損益兩平點為 2,000 個單位：其中產品 A 為 1,200 個，產品 B 為 800 個。

SBA 指出，如果每月的銷售額有所波動，您可能還需要單獨為每種產品進行計算。它還提出了一個值得銘記的警告：損益兩平點是供規劃和評估貸款機構可行性使用的估算值，並非旨在取代詳細的會計帳目。

如果產品組合發生變化，損益兩平點也會隨之改變。即使總銷售額保持穩定，向低利潤產品的轉移也會提高損益兩平點。

如何呈現損益兩平分析

大多數讀者需要三個數字和一張圖表。首先呈現損益兩平單位數、損益兩平銷售額和安全邊際。將圖表直接放在下方，並標註交叉點。

接著展示敏感度分析表。它回答了每位貸款機構人員和經理接下來會問的問題：如果成本上升或銷售未達預期會怎樣？

保持輸入資料的可見性。一份簡短的固定成本清單、價格和每單位變動成本，能讓讀者在一分鐘內驗證您的邏輯。SBA 指出，損益兩平點是商業計畫書中的一項重要計算，因此請做好它會被仔細審閱的準備。

常見錯誤

• 混淆時間週期。 將月租金與年薪混在一起計算會得出毫無意義的結果。請將所有項目換算為同一個週期。
• 在客戶支付較少金額時使用定價。 如果折扣很常見，請使用實際收到的平均價格。
• 遺漏微小的變動成本。 刷卡手續費、包裝費和運費在每單位累計下來會改變最終結果。
• 忽略產能限制。 固定成本通常會在達到特定銷售量時跳升，例如需要增加第二班制或更大的空間。請在每個階段重新計算。
• 無條件捨去。 出現小數點的結果意味著您需要下一個完整的單位，而不是前一個。

利用 AI 更快完成

一旦成本拆分明確，試算表方法就能很好地發揮作用。然而，最耗時的部分通常是整理資料，因為成本往往散落在總分類帳匯出檔或一堆發票中。

AI 工作空間可以協助進行此類分類。將匯出的成本資料上傳至 Powerdrill Bloom，並要求它將每一行分類為固定或變動成本，然後計算損益兩平單位數和銷售額。您也可以在同一個需求中要求產生敏感度分析表和圖表。

在信任結果之前，請先檢視分類。混合成本（例如水電費）通常需要由您親自做出判斷。

若要了解更廣泛的規劃藍圖，sensitivity analysis generator 和 AI financial modeling 頁面涵蓋了相關模型。我們的預算與實際執行狀況報告指南展示了如何追蹤實際結果是高於還是低於計畫。若要關注時間點而非總額，現金流量報告則能顯示資金實際入帳的時間。

如果您已經準備好匯出的成本資料，可以嘗試使用 Powerdrill Bloom，並將其損益兩平結果與您自己的試算表進行比較。

常見問題

什麼是損益兩平分析？

損益兩平分析旨在計算總收入等於總成本的銷售水準。在此時，企業既不獲利也不虧損。它常用於商業計畫書和貸款申請中。

如何計算以單位表示的損益兩平點？

將固定成本除以每單位邊際貢獻（即銷售價格減去每單位變動成本）。在固定成本為 6,000 美元且每單位邊際貢獻為 3 美元的情況下，損益兩平點為 2,000 個單位。

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

在不同的儲存格中輸入固定成本、價格和變動成本。將邊際貢獻計算為價格減去變動成本，然後用固定成本除以該數值。繪製收入和總成本對照單位數的圖表，並讀取交叉點。

邊際貢獻與邊際貢獻率有何不同？

每單位邊際貢獻是一個金額：價格減去變動成本。而邊際貢獻率是該金額除以價格，以百分比表示。在計算損益兩平單位數時使用金額數值，在計算損益兩平銷售額時使用比率。

為什麼損益兩平分析很重要？

它能顯示企業在停止虧損前必須達到多少銷售量。它還能揭示哪個輸入資料（例如價格、變動成本或租金）對該門檻的影響最大。SBA 將其列為計算創業成本的原因之一。

資料來源： 美國中小企業管理局，計算您的創業成本 · Microsoft 支援，使用目標搜尋。本實際範例所使用之數據僅供說明之用。