Excelで損益分岐点分析グラフを作成する方法|ステップバイステップ完全ガイド

このデータセットには、販売数量(個)、単位あたりの販売価格、固定費の各項目、変動費の各項目が含まれています。これらのデータをもとに、損益分岐点(BEP)を計算し、わかりやすいグラフを作成していきましょう。

ステップ1:各種コスト要素を計算する

手順:

• C7セルを選択し、以下のように入力します。

• SUM関数をダブルクリックして選択するか、TABキーを押します。

• 固定費の範囲であるC8:C12を選択します。

• C7には次の数式を使用します。
• Enterキーを押します。

• 総変動費を求めるには、C8に以下の数式を入力します。

平均変動費(製品1単位あたりの変動費の平均値)は、総変動費を総生産数量で割ることで求められます。

• 以下の数式を使用します。

ステップ2:損益分岐販売量を計算する

手順:

• C10セルを選択し、以下の数式を入力します。
• Enterキーを押します。

• C11に移動し、以下の数式を入力します。
• Enterキーを押します。

6,071個を販売した時点で、会社は損益分岐点に到達します。このときの売上高は$30,357です。

ステップ3:コスト・売上・利益の一覧表を作成する

手順:

• B4:G14の範囲に表の基本構造を作成します。

• [Unit](数量)列に昇順で数量を入力します。

• C5に移動し、以下の数式を入力します。
• Enterキーを押します。

• C5セルの右下隅にあるフィルハンドル(プラス「+」マーク)を見つけます。
• それをダブルクリックします。

残りのセルは自動的に入力されます。

• D5を選択し、以下の数式を使用します。

ここでは、数量([Units])に平均変動費を掛けています。数式には絶対参照を使用しています。

• 変動費に固定費を加えて、E5に[Total Cost](総費用)を求めます。

• 売上高を求めるには、「Break Even」ワークシートのB5の数量にC5の単価を掛けます。
• F5に移動し、以下の数式を入力します。
• Enterキーを押します。

• G5に移動し、以下の数式を使用します。
• Enterキーを押します。

ステップ4:損益分岐点グラフを挿入する

手順:

• [Profit](利益)列を除いた表全体を選択します。
• [挿入]タブで[散布図(X、Y)またはバブルチャートの挿入]をクリックします。
• [平滑線付き散布図とマーカー]を選択します。

グラフが表示されます。

• 任意の系列の線上にあるマーカーを右クリックします。

コンテキストメニューで:

• [データ系列の書式設定...]をクリックします。

[データ系列の書式設定]ウィンドウで:

• [塗りつぶしと線]をクリックします。
• [マーカー]を選択します。
• マーカーのオプションを展開し、[なし]を選択します。
• [塗りつぶし]セクションを展開し、[塗りつぶしなし]を選択します。

他の系列についても同じ手順を繰り返します。

固定費と変動費の線を区別しやすくするために:

• 固定費の線を右クリックします。
• [データ系列の書式設定...]を選択します。

[データ系列の書式設定]で:

• [塗りつぶしと線]をクリックします。
• [線]セクションで[破線の種類]を[長鎖線]に設定します。

これが出力結果です。

ステップ5:グラフ上で損益分岐点を特定する

手順:

• プロットエリア内で右クリックします。
• [データの選択...]を選択します。

• [データソースの選択]ダイアログボックスで[追加]をクリックします。

[系列の編集]で:

• [系列名]にBEPと入力します。
• [Break Even]ワークシートに移動し、[系列Xの値]と[系列Yの値]にそれぞれC10とC11のセル参照を指定します。
• [OK]をクリックします。

• [OK]をクリックします。

• 新しい系列の[データ系列の書式設定]を開きます。
• [マーカー]オプションに移動します。
• [組み込み]を選択し、[種類]で円形マーカーを選びます。
• マーカーの[サイズ]を8に設定します。

これが出力結果です。

ステップ6:損益分岐線を引く

手順:

• プラス型の[グラフ要素]アイコンをクリックします。
• [誤差範囲]の右側にある矢印をクリックします。
• [その他のオプション...]を選択します。

[誤差範囲の書式設定]作業ウィンドウで:

• [誤差範囲のオプション]を選択します。
• [方向]で[負]、[端のスタイル]で[キャップなし]を選択します。
• [誤差量]で[パーセンテージ]を選択し、100%に設定します。

• [誤差範囲のオプション]のドロップダウン矢印をクリックします。
• [系列"BEP" Y誤差範囲]を選択し、縦方向の誤差範囲についても同じ手順を実行します。

これが出力結果です。

ゴールシーク機能を使って損益分岐点での販売価格を計算する方法

手順:

• [Revenue](売上高)を計算するには、C9に以下の数式を入力します。

• この数式を使って、C10に[Profit](利益)を求めます。

損益分岐点とは、企業や事業が利益を生み出し始める直前の状態を指します。

• [データ]タブに移動します。
• [予測]グループの[What-If分析]をクリックします。
• [ゴールシーク]を選択します。

• [ゴールシーク]ダイアログボックスで、以下のようにセル参照を入力し、[OK]をクリックします。

C10が0になるように、C5の値を変更します。
その結果、[Unit Selling Price](単価)は3.90と算出されました。

練習セクション

ここで実際に練習してみましょう。

練習用ワークブックのダウンロード

以下のExcelワークブックをダウンロードしてご利用ください。

関連記事

• Excelで損益分岐点を計算する方法
• Excelで複数製品の損益分岐分析を行う方法
• Excelでゴールシークを使った損益分岐分析を行う方法

<< 損益分岐分析 | Excel For Finance | Excel学習 に戻る

無料の高度なExcel演習問題(解答付き)を受け取ろう!