エクセルで損益分岐表を作成する方法

損益分岐点分析は、ビジネスモデルの利益の可能性を評価し、様々な価格戦略を評価するためのツールです。エクセルで固定費、変動費、価格オプションを簡単に集計し、製品の損益分岐点を決定することができます。これは、収支を均衡させるために設定した価格で販売する必要のある個数です。

パート1

変動費テーブルの追加

1. エクセルを開き、空白のワークブックを新規作成します。このワークブックには、すべてのコストを追跡するために複数のシートを作成します。
2. 画面下の "Sheet1 "の横にある "+"ボタンをクリックします。これで、新しい空白のワークシートが作成されます。
3. 新しいシートの名前を "VariableCosts "に変更します。このシートには、送料、手数料、その他のコストなど、商品のすべての変動費を追跡するテーブルが入ります。
4. 新しいシートのヘッダー・ラベルを作成します。基本的な変動費表を作成するには、A1に "Description"、B1に "Amount "を入力する。
5. Description "ヘッダーの下には、あなたのビジネスで扱う変動費の種類を記入する。
6. B列（「金額」）は、今は空欄にしておきます。後で実際のコストを記入することになります。
7. 入力したデータから表を作成します。データを表にすることで、後で数式に差し込むのが簡単になります：

   • すべてのセルをマウスでクリックしてドラッグし、ヘッダー行と空白の金額を含むすべてのデータを選択します。
   • 表として書式設定」ボタンをクリックする。これは「ホーム」タブにあります。Mac版エクセルを使用している場合は、「表」タブをクリックし、「新規」ボタンをクリックし、「ヘッダー付き表の挿入」を選択します。
   • My table has headers" ボックスにチェックを入れる。これにより、最初の行のラベルがヘッダー・ラベルとして保持されます。
   • 右上の "表名 "フィールドをクリックして、"VariableCosts "と名づける。

パート 2

固定費テーブルの追加

1. 画面下の "VariableCosts "の横の "+"ボタンをクリックする。これで、別の空白のワークシートが作成されます。
2. 新しいシートの名前を "FixedCosts "に変更する。このシートには、家賃、保険料、その他の変わらないコストなど、商品の固定費をすべて入れます。
3. ヘッダー・ラベルを作成します。変動費シートと同様に、セルA1に「説明」ラベル、セルB1に「金額」ラベルを作成します。
4. 最初の列には、"家賃 "のように固定費の説明を記入する。
5. B列（「金額」）は今は空欄にしておく。これらの費用は、スプレッドシートの残りの部分を作成した後に記入します。
6. 入力したデータから表を作成する。ヘッダーを含め、このシートで作成したすべてを選択します：

   • ホーム」タブの「表として書式設定」ボタンをクリックする。
   • My table has headers」にチェックを入れて、行1を表のヘッダーにする。
   • 表名」フィールドをクリックし、表の名前を "FixedCosts" とする。

パート 3

損益分岐点シートの作成

1. Sheet1の名前を "BEP "に変更し、選択する。このシートにメインのBEP (Break Even Point)チャートが入ります。BEP "にリネームする必要はありませんが、リネームした方がワークブックが見やすくなります。
2. ブレーク・イーブン・シートのレイアウトを作成します。この例では、以下のレイアウトでシートを作成します：

   • A1: 売上 - これは、スプレッドシートの売上セクションのラベルです。
   • B2: 単価 - これは、販売する商品ごとに請求する価格です。
   • B3: 販売個数 - 指定した期間内に、指定した価格で販売した個数です。
   • A4: 費用 - スプレッドシートの「費用」セクションのラベルです。
   • B5: 変動費 - あなたがコントロールできる商品のコストです（送料、手数料など）。
   • B6: 固定費 - あなたがコントロールできない製品のコストです（施設賃貸料、保険料など）。
   • A7: 売上高 - コストを考慮する前に、製品を販売することで生み出される金額です。
   • B8: 単位利益率 - コストを考慮した後の、1単位あたりの利益額です。
   • B9: 売上総利益率 - これは、コスト考慮後に販売されたすべてのユニットに対して得られる金額の合計です。
   • A10: BEP - これは、スプレッドシートの損益分岐点セクションのラベルです。
   • B11: 単位 - 費用に見合う販売個数です。
3. 出力セルと入力セルの数値フォーマットを変更します。データを正しく表示するために、特定のセルの数値書式を変更する必要があります：

   • C2、C5、C6、C8、C9をハイライトする。ホーム」タブの「数値」セクションのドロップダウン・メニューをクリックし、「通貨」を選択する。
   • C3とC11をハイライトする。ドロップダウンメニューをクリックし、"その他の数値形式 "を選択する。Number "を選択し、"Decimal places "を "0 "に設定する。
4. 数式で使用する範囲を作成する。数式を機能させるために、以下の範囲を選択して作成する。これにより、数式に挿入できる変数が作成され、これらの値を簡単に参照・更新できるようになります。

   • B2:C3を選択し、「数式」タブをクリックする。選択範囲から作成」をクリックし、「OK」をクリックする。
   • B5:C6を選択し、「数式」タブをクリックする。選択範囲から作成」をクリックし、「OK」をクリックする。
   • B8:C9を選択し、「数式」タブをクリックする。選択範囲から作成」をクリックし、「OK」をクリックする。
   • B11:C11を選択し、「数式」タブをクリックする。選択範囲から作成」をクリックし、「OK」をクリックする。

パート4

計算式の入力

1. 変動費の計算式を入力する。これは、販売する商品数に対する変動費の合計を計算します。C5 をクリックし、次の数式を入力する：

   • =SUM(変動費)*販売個数
2. 固定費の計算式を入力します。これで、商品の固定費合計が計算されます。C6 をクリックし、次の数式を入力します：

   • =SUM(固定費)
3. ユニット・マージンの計算式を入力します。これは変動費を考慮した後のマージンを計算します。C8をクリックし、以下の数式を入力します：

   • =単価-SUM(変動費)
4. 粗利率の計算式を入力します。これは、変動費を考慮した後の、あなたが販売する全ユニットの総利益を決定します。C9 をクリックし、以下の数式を入力します：

   • =Unit_Margin*Units_Sold
5. BEP計算式を入力します。これは固定費とマージンを比較し、収支を均衡させるために必要な販売個数を示します。C11をクリックし、以下の数式を入力します：

   • =IFERROR(Fixed_Costs/Unit_Margin,0)

パート5

損益分岐点を見つける

1. あなたのビジネスの変動費を入力します。VariableCosts テーブルに戻り、製品に関連するすべてのコストを記入します。ここで正確であればあるほど、BEP の計算はより正確になります。

   • VariableCosts 表の各コストは、販売単位当たりでなければなりません。
2. 事業の固定費を入力します。これらのコストを固定費表に入力します。これらはビジネスを運営するためのコストで、すべて同じ間隔に設定する必要があります (たとえば、月次コスト)。
3. 単価を入力します。BEPシートで、最初の見積単価を入力します。これは、計算を実行しながら調整できます。
4. 販売するユニット数を入力します。これは、固定費と同じ時間枠で販売する予定のユニット数です。例えば、固定費に月々の家賃と保険料が含まれている場合、「Units Sold」は同じ時間枠で販売するユニット数となります。
5. Units」出力を読む。Units」出力のセル（C11）には、収支を均衡させるためにタイムフレーム内で販売する必要のあるユニット数が表示されます。この数字は、「Price Per Unit」、「Variable Costs」、「Fixed Costs」のテーブルによって変わります。
6. 価格とコストを調整してください。単価を変更すると、収支を均衡させるために必要なユニット数が変わります。価格を変更し、BEP値がどうなるか試してみてください。

この記事は、CC BY-NC-SAの下で公開された " How to Do a Break Even Chart in Excel " を修正して作成されました。特に断りのない限り、CC BY-NC-SAの下で利用可能です。