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

新規事業を始める際に利用するポピュラーな指標として「損益分岐点」分析があります。

計算式自体は非常に簡単ですが、報告する際にはグラフ化したり、前提条件を変えて分析を行えるように下準備をしておく必要があります。

損益分岐点をグラフで表示する方法を紹介します。

Excelで損益分岐点算出

固定費・変動費

固定費は、店舗費・人件費など、変動しないものをいいます。

変動費は、材料費等です。売れれば変動します。

固定費と変動費を組合せたらこの様になります。

この様に固定費と変動費の線と売上の線の交差した場所を損益分岐点と言います。
利益がプラスマイナス0という意味です。

Excelのゴールシークを使って損益分岐点を算出

さっきの固定費と変動費をこの様な表にしました。
変動費は材料費・固定費は店舗費と人件費

ここでは販売単価が600円としておきます。

1日60個販売したとします。

売上高は販売単価×販売量（1日分）

費用は変動費合計+固定費合計×販売量（1日分）

費用は、変動費×販売量（1日）+固定費

売上高-費用で利益が算出出来ます。

では利益が0になる予測をゴールシークで立てていきます。

① 黄色のセルをアクティブセルにしてください。
② 【データタブ】をクリックしてください。
③ IF分析の▼からゴールシークをクリックしてください。

ゴールシークのボックスが出てきますので上記の様に入力してください。
OKボタンを押します。

販売量を41.5にすれば利益が0という基準が解りました。

損益分岐点グラフ作成

では、販売量が0の時、60の時の損益分岐点のグラフを作成します。

① 表をドラックしてください。
② 【挿入タブ】をクリックしてください。
③ グラフの配布図の▼から配布図直線をクリックしてください。

するとこの様なグラフが作成されます。

グラフを選択して、行と列の切り替えを押して下さい。

するとこの様な損益分岐点のグラフが完成しました。