ホームページのリニューアルや商品の仕入れなど、新しい設備に投資を行う際は必ず「どのくらい売れれば、利益になるのか」を明確にする損益分岐点を見極める必要があります。

経営者や管理職の方であれば、損益分岐点の算出が必要となる機会は多いでしょう。ただ、初めて取り組む場合、どのように算出すればいいかわかりにくいのではないでしょうか。

今回は、損益分岐点の算出方法と、Excelを使った損益分岐点グラフの作り方を解説します。損益分岐点を出すための基本的な考えからから解説するので、初めて損益分岐点を算出という方は参考にしてみてください。

損益分岐点とは

「損益分岐点(BEP : break-even point)」とは、商品やサービスの販売にかかる費用をカバーでき、売上高に比例した利益が発生する売上高を指します。

例えばレストランの場合、売上が少ない状態では、毎月の家賃や人件費がかかってしまい利益は発生しません。
ですが、費用をカバーするほどの売上があれば、店舗としては黒字状態になるでしょう。

損益分岐点を算出することで、事業や新しい設備への投資に対して「利益を出すためには○万円売れなくてはいけないけれど、その見込みはないから投資はやめるべき」といった判断が可能となります。そのため、経営者はもちろん会社の予算を管理している管理職の方は知っておくべき概念でしょう。

損益分岐点の算出方法

損益分岐点を算出するには、以下の数式を使って計算します。

損益分岐点売上高 = 固定費 ÷{(売上高−変動費)÷売上高 }

固定費・変動費とは

「固定費」とは、売上の変動にかかわらず発生する費用です。
例えば家賃や機械などの設備費、人件費が挙げられます。

一方で「変動費」は、売上の増減に従って変動する費用です。商品の仕入れ額や外注費など、商品やサービスを売った分金額が大きくなります。

飲食店を例に算出してみよう

では、飲食店を例に損益分岐点の出し方を考えてみましょう。
家賃10万円の店で、仕入原価500円のオムライスを1000円で販売している場合を想定します。
その場合、以下のような計算式となります。

損益分岐売上高=100,000÷ { (1,000-500)÷ 1,000 }

これを計算すると、損益分岐売上高は200,000円だということがわかります。

参考:
[損益分岐点|経営者なら絶対に理解しておくべき損益分岐点の仕組み | inQup] (http://inqup.com/profit-loss)
IT業界を読み解くための経営分析入門 - 損益分岐点分析(1)固定費と変動費を見極める|ITpro

Excel(エクセル)を使って損益分岐点をグラフにしよう

損益分岐点は計算式を使って算出することができます。
さらにグラフを用いることで、視覚的にもわかりやすく説明できます。

例えば、下記は計算式の説明の際に用いた飲食店の例をグラフにしたものです。
固定費と変動費を合わせた総費用を超えた収益が得られる損益分岐売上高が20万円になっていることがわかります。

また、売上高が30万円になった際には、固定費と変動費を合わせた総費用は25万円であり、5万円の利益が出ることがわかります。

スクリーンショット_2017-09-28_20.22.13.png

グラフにすることで、他の数値との関連が一目でわかるようになり、説明にも用いやすくなります。
では、こういったグラフはどのように作成すればいいのでしょうか。

Excelを利用したグラフ作成方法を解説します。

1.表を作成する

スクリーンショット_2017-09-28_20.49.34.png
上記のように表を作成します。Aはもっとも売上が少ない状態、Bはもっとも売上が多い状態の数値を表しています。青い文字の部分に想定する数値を入れてください。

なお、売上高の最大値は自由に決めて構いません。
例えば、上記の画像内にあるレストランの例えの場合、売上高の最大値は30万円に設定しています。

2.表を選択して面グラフを作成する

先ほどの表を選択した状態で上部のタブより「挿入」を選択し「グラフ」から「面」をクリックしましょう。

スクリーンショット_2017-09-28_20.51.54.png

以下のように面グラフが表示されました。

スクリーンショット_2017-09-28_20.56.18.png

3.列と行を入れ替え

2の状態からグラフに反映する列と行を入れ替えます。

 (1)上部のメニュー「グラフデータの選択」をクリック
 (2)「行/列の切り替え」をクリック

スクリーンショット_2017-09-28_20.57.50.png

下記のように、グラフが切り替わりました。売上高・固定費・変動費がそれぞれ表示されているのがわかります。

スクリーンショット_2017-09-28_21.02.06.png

4.面の塗りつぶしを透明に変更する

グラフの面が塗りつぶしになっている状態では、売上高と変動費・固定費がどこで交わっているのかよく見えません。
そのため、面の塗りつぶしを透明に変更しましょう。

グラフをクリックすると、下記のように右側に書式の設定画面が表示されます。
そこで塗りつぶしの設定を「塗りつぶしなし」枠線を「線(単色)」に変更します。

スクリーンショット_2017-09-28_21.08.07.png

これを売上高・変動費・固定費すべてのグラフで設定すると、線がわかりやすく見えるようになります。

5.完成

これでグラフは完成です。売上高と変動費の線が交わる部分が損益分岐点となります。

スクリーンショット_2017-09-28_21.11.11.png

まとめ

損益分岐点を出すには以下の数式で計算します。

損益分岐点売上高 = 固定費 ÷{(売上高−変動費)÷売上高 }

ただ、数式だけでは見た目にもわかりづらく、数値同士の関係性が見えません。
グラフを利用すれば収入と支出の関係性が一目でわかるので、上司への提案資料としても使いやすいでしょう。

今回紹介したのはExcelによるグラフ作成でしたが、Web上では数値を入力するだけで作成できるツールもあります。
手軽に算出したい方はそういったツールを使ってみてもいいでしょう。

参考:
[損益分岐点グラフ作成ツール|アフォード・ビジネス・コンサルティング 計算,固定費,変動費,無料,ツール] (http://www.afford.co.jp/sheet/sindan-sheet2.html)

Excel(エクセル)の効率的な運用を知る

Excelを使用するなら知っておきたい!便利な小技8選

Excelを使用するなら知っておきたい!便利な小技8選

今回は、Excelを使用する際に知っておくとより便利になる小技8選をご紹介します。 どの技も難しい操作はないので、まずは手順に従って試してみることをオススメします。

エクセル(Excel)の【IF関数】の使い方を極めよう!“伝わる”レポート作成の第一歩

エクセル(Excel)の【IF関数】の使い方を極めよう!“伝わる”レポート作成の第一歩

Microsoft Officeシリーズのエクセルは、代表的な表計算ソフトです。今回は、エクセル(Excel)を使った資料作成で絶対に押さえておきたい「IF関数」の使い方の基本から、上級者にステップアップするための応用Tipsを解説します。エクセルでIF関数を使うことで、条件に合致するデータのみ集計したり、除外したりすることができます。ぜひマスターしましょう。