Excelで損益分岐点を算出しグラフで可視化する方法

Excelで損益分岐点を算出したいという方もいるのではないだろうか。事業を行う場合、売上額の損益分岐点などを求めることで、固定費などをいくらまで掛けても良いか検討するのは、重要なことである。

本記事ではExcelで損益分岐点を算出する方法について解説する。また、Excelで損益分岐点をグラフで表す方法についてもまとめた。

損益分岐点とは？

損益分岐点とは、利益も損失も『ゼロ』になる地点のことである。たとえば、商品を販売する場合であるならば、店舗の利益も損失もゼロになる販売個数や売上金額を指す。

店舗で商品を販売するには、光熱費や人件費などの固定費がまず発生する。また、商品を製造するには、材料費などの変動費もかかってくる。固定費と材料費の合計が売上金額と一致するための商品の売上金額が、損益分岐点ということになる。

たとえば、1ヶ月の固定費が100万円かかる。1つの商品を作るのに変動費が200円かかる。そして、商品の値段は500円。この場合「1ヶ月にどの程度売り上げれば、利益も損失もゼロになるだろうか」。これを求めるのが、損益分岐点を求めるという意味である。

■損益分岐点を求める方法

商品の売上金額の損益分岐点を求めるには、固定費と変動費の情報が必要である。それらの情報が揃ったら、以下の計算式によって損益分岐点を求められる。

• 固定費 ÷ (1 – 変動費 ÷ 商品の値段)

たとえば、1ヶ月の固定費が100万円、1つ商品を作るのにかかる変動費が200円、商品の値段が500円だった場合、

• 100万円 ÷ (1 – 200円 ÷ 500円) ≒ 166万円

という計算になる。この場合の損益分岐点は約166万円。つまり、166万円以上売り上げることができれば、損失を発生させなくて済むということだ。

また、166万円 ÷ 500円 = 3320なので、販売個数の損益分岐点は約3,320個になる。

3,320個も売り上げることが難しいと想定される場合、損失を発生させないためには、固定費などを削減できないか検討する必要がある。たとえば、固定費を80万円に下げられれば、損益分岐点は約133万円（約2,660個）になり、だいぶハードルが下がることになる。

このようにして、損益分岐点を店舗運営に活かしていく。

Excelで損益分岐点を算出する方法

Excelで損益分岐点を算出する方法について解説する。

損益分岐点を算出する手順は次のとおり。

1. 表を作成する
2. ゴールシークの画面を表示させる
3. 損益分岐点を算出する

ひとつひとつの手順について詳しくみていこう。

■1.表を作成する

損益分岐点を算出するには、固定費や変動費がいくらかなのかのデータが必要だ。まずは、費用表（固定費、変動費）を以下のように作成しよう。

続いて、費用表を元に、売上表（商品の値段、販売個数、売上高、費用、利益）を作成する。ここでは商品の値段は「500円」と設定する。販売個数はひとまず「5000」としよう。

商品の値段と販売個数が分かれば、「値段*販売個数」によって売上高は算出できる。

利益に関しては、「売上高-費用」によって算出できる。

■2.ゴールシークの画面を表示させる

Excelで損益分岐点を算出するには、ゴールシークの画面を表示させる必要がある。

「データ」タブの「What-IF 分析」から「ゴールシーク」をクリックしよう。

■3.損益分岐点を算出する

ゴールシークの画面を表示させたら、3つの入力項目があることを確認する。以下のように入力しよう。

• 数式入力セル：$F$7（利益）
• 目標値：0
• 変化させるセル：$F$4（販売個数）

「OK」をクリックすると、表の販売個数や売上高の値などが自動的に動く。そして「利益」が「0」の場合の、販売個数や売上高を算出してくれる。

Excelで損益分岐点をグラフで表す方法

続いて、Excelで損益分岐点をグラフで表す方法について解説する。

手順は次のとおり。

1. グラフの元となる表を作成する
2. グラフを作成する
3. 損益分岐点を表す

ひとつひとつの手順について詳しくみていこう。

■1.グラフの元となる表を作成する

まず、グラフの元となる表を作成する必要がある。

販売個数と売上高、費用が書かれた、以下のような表を作成しよう。表には、販売個数が0個の場合と、5000個の場合のデータを挿入しておく。

■2.グラフを作成する

作成した表全体を選択し、「挿入」タブから「散布図」をクリック

一番最後の散布図（直線）をクリックすると、グラフが作成される。

以下のようなグラフが作成されたことが分かる。

関連記事：Excelで散布図を作成し編集する方法【図解】
関連記事：Excelの円グラフ、折れ線グラフ、棒グラフ、散布図の作り方と編集方法

■3.損益分岐点を表す

グラフを選択した状態で、「グラフのデザイン」⇒「行/列の切り替え」をクリック。

すると、2つの直線が交わった状態になることが分かる。2つの直線が交わった地点が、損益分岐点ということになる。

グラフを見れば、売上高の損益分岐点は約166万円、販売個数の損益分岐点は約3,320個であることが、すぐに分かるだろう。

まとめ

本記事ではExcelで損益分岐点を算出する方法について解説した。最後に、店舗の固定費・変動費の情報から、損益分岐点を算出する手順をおさらいする。

1. 表を作成する
   1. 費用表（固定費、変動費）を作成する
   2. 売上表（商品の値段、販売個数、売上高、費用、利益）を作成する
2. ゴールシークの画面を表示させる
   1. 「データ」⇒「What-IF 分析」⇒「ゴールシーク」をクリック
3. 損益分岐点を算出する
   1. 数式入力セルに「利益」を入力
   2. 目標値を「0」に指定
   3. 変化させるセルに「販売個数」を入力
   4. 「OK」をクリックする

損益分岐点を算出すれば、固定費や変動費をかけすぎていることに気がつくこともある。固定費・変動費さえ分かれば、Excelで簡単に損益分岐点を算出できるので、やってみよう。

構成／編集部