Excelで損益分岐点分析を行う方法

新製品を販売する予定で、単位あたりの変動費と総固定費を知っていると仮定します。これから予測される販売量に基づいて製品の価格を設定します。

1簡単な表を作成し、与えられたデータで項目を表に入力します。以下のスクリーンショットをご覧ください。

2収益、変動費、利益を計算するために適切な数式を入力します。上記のスクリーンショットをご覧ください。
収益 = 単価 x 販売単位数
変動費 = 単位あたりの費用 x 販売単位数
利益 =収益 –変動費 – 固定費

3クリックします データ > What-If分析 > ゴールシーク.

4開いたゴールシークダイアログボックスで、以下の手順を行います（上記のスクリーンショットを参照）：
(1) 指定します 設定セル を利益セルとして指定します。今回の場合、セルB7です。
(2) 指定します 目標値 として 0;
(3) 指定します 変更するセル を単価セルとして指定します。今回の場合、セルB1です。
(4)クリックします OK ボタン。

5そして、ゴールシークステータスダイアログボックスが表示されます。 OK ボタンをクリックして適用します。

これで単価が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に変動費を計算するための数式を入力します。以下のスクリーンショットをご覧ください。

そして、予測された販売単位数、単位あたりの費用、または固定費のいずれかの値を変更すると、単価の値が自動的に変わります。上記のスクリーンショットをご覧ください。

ヒント: セルの書式と数式を保持して範囲をAutoTextエントリとして保存し、将来再利用する
損益分岐点分析を行うたびにセルを参照し、複数の数式を適用するのは非常に面倒です。Kutools for Excelは、範囲をAutoTextエントリとして保存するための便利な方法を提供します。 AutoText ユーティリティを使用して範囲をAutoTextエントリとして保存し、範囲内のセルの書式と数式を保持できます。そして、任意のワークブックでワンクリックでこの範囲を再利用できます。

今すぐ 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) 系列名の範囲 セクションで、必要に応じて系列の1つを選択します。私の例では、 収益 系列を選択します。
(2) 編集 ボタンを 水平（カテゴリ）軸ラベル セクションでクリックします。
(3) ポップアップする軸ラベルダイアログボックスで、軸ラベル範囲として単価列（列名を除く）を指定します。
(4)クリックします OK > OK 変更を保存します。

損益分岐点チャートでは、価格が36に等しいときに損益分岐点が発生することがわかります。以下のスクリーンショットをご覧ください。

同様に、販売単位数による損益分岐点を分析するための損益分岐点チャートを作成することもできます。以下のスクリーンショットをご覧ください。

Excelで選択範囲を簡単にPDF/CSV/TEXT/HTMLファイルとしてエクスポート/保存する

例えば、Excelで損益分岐点分析を完了した後、この損益分岐点分析を使用してプレゼンテーションを行いたい場合があります。この状況では、Kutools for Excelの 範囲をファイルにエクスポート ユーティリティを適用して、選択範囲を別のPDFファイル、HTMLファイル、CSVファイル、テキストファイルなどとして迅速にエクスポートできます。