Excel で損益分岐点分析を実行する方法 (例あり)

損益分岐点分析は、企業が損益分岐点、つまり正確にゼロドルの利益を出すために特定の製品を何個販売する必要があるかを示す計算です。

この期間を過ぎると、追加のユニットが販売され、プラスの利益が得られます。

損益分岐点分析を実行するには、次の簡単な式を使用できます。

損益分岐点 = 固定費 / (1 個あたりの販売価格 – 1 個あたりの原価)

次の例は、この数式を使用して Excel で損益分岐点分析を実行する方法を示しています。

例: Excel で損益分岐点分析を実行する方法

Ty がクッキー ストアのオープンを検討しているとします。

彼の固定費には、購入する必要がある機器とクッキーの材料が含まれており、合計1,000 ドルになります。

クッキーの製造コストは 1 つあたり1 ドルですが、彼はそれらを 1 つあたり5 ドルで販売する予定です。

損益分岐点分析を実行して、損益分岐点までに販売する必要のある Cookie の数を判断したいとします。

これを行うには、固定費、販売単価、単価を Excel に入力します。

次に、次の数式をセルB5に入力して、損益分岐点までに販売する必要があるユニット数を計算します。

 = B1 /( B2 - B3 )

次のスクリーンショットは、この式を実際に使用する方法を示しています。

損益分岐点、つまりちょうどゼロドルの利益を出すには、 250個販売する必要があります。

必要に応じて、次のセルに次の数式を入力して、Ty がこの数のユニットを販売することで得られる総収益、総コスト、および総利益を計算することもできます。

• B6: =B5*B2
• B7: =B1+(B5*B3)
• B8: =B6-B7

次のスクリーンショットは、これらの数式を実際に使用する方法を示しています。

総収益は$1,250 、総コストは$1,250 、総利益は$0であることがわかります。

これらの数式をすべて設定したら、セルB2の単位あたりの販売価格を変更して、価格の違いが損益分岐点までに販売しなければならない単位数にどのような影響を与えるかを確認することもできます。

たとえば、ユニットあたりの販売価格を$6に変更するとします。

損益分岐点までに販売しなければならないユニット数が200に減少することがわかります。

これには意味があるはずです。販売単価が高くなるほど、クッキー 1 枚あたりの利益が大きくなり、損益分岐点までに販売しなければならないクッキーの数が減ります。

セルB1 、 B2 、およびB3の値を自由に操作して、さまざまな価格を変更すると損益分岐点がどのように変化するかを確認してください。

追加リソース

次のチュートリアルでは、Excel で他の一般的な操作を実行する方法について説明します。

Excel で売上の伸びを計算する方法
Excel で売上予測を作成する方法
Excelで四半期データを年間データに変換する方法