【徹底図解】損益分岐点をEXCEL(エクセル)のグラフで表示する方法

EXCELで損益分岐点をグラフにする方法その他

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

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

そこで、今回の記事では損益分岐点をグラフで表示する方法を紹介します。

あと、(簡易的な紹介ですが)、EXCELで実践的に損益分岐点等のシミュレーションする機能も紹介しておきたいと思います。

【おさらい】損益分岐点とは

損益分岐点とは損益分岐点とは、かかった費用を収益でカバーして損益がゼロになる売上高の事。

計算式は以下の通りです。3つ並べていますが、全て同じ計算結果になりますよ。

損益分岐点の計算式
  • ①損益分岐点売上高=固定費÷{(売上高-変動費)÷売上高}
  • ②損益分岐点売上高=固定費÷(限界利益÷売上高)
  • ③損益分岐点売上高=固定費÷限界利益率

①の式を展開していくと②の式になり、③の式になります。

たとえば

・売上5,000万円
・変動費2,500万円-(売上高比率50%)
・限界利益2,500万円-(同比率50%)
・固定費2,000万円- (同比率40%)
・経常利益500万円- (同比率10%)

損益分岐点のイメージ図

このような事業状況だったとします。限界利益率は2,500万円÷5,000万円で0.5です。

よって損益分岐点売上高は2,000万円÷0.5=4,000万円となります。

損益分岐点を計算する際には「変動費」「固定費」「限界利益」などの定義を理解しておく必要がありますので、計算式や定義があやふやな方は下記記事を先に読んでからこちらの記事に戻ってきてください。

【具体例付】損益分岐点とは?計算方法と実務上の留意点を税理士が徹底解説!
損益分岐点とは売上高とそれにかかる費用が丁度イコールになる売上高(損益分岐点売上高と呼ぶ)又は販売数量(損益分岐点販売数量と呼ぶ)のことを言います。非常に古典的な経営分析の手法ですが、変動費・固定費の振分や正しい分析が出来れば、経営判断の重要指針となるでしょう。

 
なお、損益分岐点を把握することで以下のような分析が可能になりますよ。

  • 利益が出る売上高はいくらなのか?
  • いくらまでなら値引きをしても利益を出せるのか?
  • 設備投資等のために必要な資本を回収できるだけの売上高はいくらなのか?

税理士等の専門家のみならず、実際に経営を行う中小企業経営者や個人事業経営者も是非知っておきたい指標です!

損益分岐点のグラフをエクセルで作成する方法

損益分岐点のグラフを作成するには「A列:売上が最小値の場合の売上高・変動費含む総費用合計・固定費」、「B列:売上が最大値の場合の売上高・変動費含む総費用合計・固定費」の情報を入力します。

損益分岐点のグラフ(方眼あり)

実際に数字を見ながら、上記画像の中にどのような数字が入るのか見てみましょう。

前提条件

  • 売上高 5,000,000
  • 変動費 2,000,000
  • 固定費 1,000,000

損益分岐点グラフの実際の数値(方眼あり)

場所説明
A列×売上高セルここは売上の最小値となる金額、つまり0を手入力します。
A列×変動費含む総費用セル固定費+変動費の最小値の合計を入力。A列では売上0なので変動費も0になるため、固定費の金額を同じ金額が入ります
A列×固定費セル前提条件の固定費(1,000,000円)を入力
B列×売上高セル売上の最大値となる金額、ここでは前提条件の5,000,000円を入力
B列×変動費含む総費用セル固定費+売上の最大値に対応する変動費の最大値、ここでは2,000,000円を入力
B列×固定費セル前提条件の固定費(1,000,000円)を入力

で、こちらの表データの全てを選択した状態にして

EXCELツールバーの「挿入」⇒「グラフ」をクリック。

選択状態からの挿入⇒グラフ

そして、「すべてのグラフ」⇒「面」から下記画像のグラフを選択してください。

全てのグラフからの面

すると、こんな感じのグラフが出てきます。
面画像の話

このグラフは「A列⇒B列」にかけての数字部分が塗りつぶし状態になっていますので、少しグラフの書式を変える必要があります。

グラフの塗りつぶしている部分をクリックすると、EXCELの右側に「データ系列の書式設定」が出てきますので

データ系列の書式設定1

・「塗りつぶし」のところを塗りつぶしなし
・「枠線」のところを単色

に変更してください。

データ系列の書式設定だけ

注:適宜先の太さや色などは変更して、自分が見やすいように調整しましょう。

するとこんな感じになりますよ。

一つの要素だけ塗りつぶしなしにした状態

売上高部分は実戦のみになりました。

あとは「変動費含む総費用」「固定費」の2つについても同じように操作すれば、

損益分岐点グラフの作成

このように損益分岐点グラフが完成します。

損益分岐点売上高=固定費÷{(売上高-変動費)÷売上高
ちなみに、今回の場合だと損益分岐点売上高は「1,000,000÷{(5,000,000-2,000,000)÷5,000,000}=1,666,667円」となります。

EXCEL(エクセル)のゴールシーク機能を使って分析・調査する方法

EXCELのゴールシーク機能を使えば、もう少し精緻な分析を行うことが出来ます。

ゴールシークとは、ある数式の計算結果を利用することにより、求めたい数値を逆算してくれるEXCELの機能の事を言います。

従って、ゴールシークを利用すれば

  • 損益分岐点はどこにあるのか?
  • 目標利益を達成するためには「単価をいくらにする必要があるのか?」「販売数量はどの程度必要なのか?」

などを調査することが可能になります。

こんな感じで、売上については「単価×数量」、変動費についてはも「仕入単価+その他変動費」という形で要素を分けていきます。

ゴールシークの元データ

ちなみに、数式はこんな感じになっていますので、適宜参考にしてください。

数式が入っている元データ(セル名付)

では、ゴールシーク機能を使って損益分岐点販売数量を求めてみましょう。

ゴールシークはEXCELの「データ」⇒「What-If分析」⇒「ゴールシーク」で呼び出します。

ゴールシークの呼び出し方

するとこんなダイアログが出てきます。

ゴールシークの入力項目

入力する項目を説明すると、以下のような感じになります。

項目入力内容
数式入力セル
数式が入っているセルを選択する。ここで選んだセルを下の「目標値」にするためには「変化させるセル」の数字をいくらにしなければならないか?を算出するのが目的。
目標値
必ず手入力する。数式入力セルで選択したセルの目標金額を入力する。
変化させるセル
エクセル上でベタ打ちになっているセルを選択する。正しく参照できていれば自動で値が逆算されて、結果として「数式入力セル」の数値が「目標値」に変わります。

説明だけだと分かりにくいので、実際にやってみましょう。

【事例】経常利益がゼロになる販売数は何個なのか?

つまり、損益分岐点売上高を求めるってことですね。販売数が何個以上下がったら赤字になる可能性があるのか調べるということです。

ゴールシーク機能の例(販売個数1)

  • 数式入力セル⇒経常利益がゼロになる場合の販売数を知りたいのでF15を選択
  • 目標値⇒0を手入力
  • 変化させるセル⇒販売数がどれだけ変化したら経常利益がゼロになるか知りたいのでF5を選択
注:なお、ゴールシークではセルを選択すると絶対参照で入力されます。たとえばF5を選択すると「$F$5」となりますよ!

これで「OK」を押すと、計算が行われ、以下のように「セル○○の収束地を探索しています。」とダイアログが出て、結果が出ます。

ゴールシーク機能の例(販売個数2)

今回の場合は、エクセルグラフを作成したときと同じ結果になりましたね。販売数が333個を下回ると赤字になってしまうという結果が出来ました。

ちなみに、以下のように数式を組んでおけば、シミュレーションに対応したグラフもすぐに作成出来ますよ!

ゴールシークとEXCELグラフのあわせ技

ゴールシーク機能をもっと詳細に知りたい方は下記記事などを参考にどうぞ!

ゴールシークとは?機能を使って逆算してみよう
Excelで計算結果から、その結果を得るための数値を逆算する「ゴールシーク」機能についてご紹介!その使い方やしくみ、魅力に迫ります。

EXCELのアドイン「ソルバー」を使って更に詳細にシミュレーション

ゴールシークも非常に便利な機能ですし、通常個人事業主の方が自分で分析するのであればゴールシークだけでも十分かもしれません。

ただ、ゴールシークでは、複雑な条件を設定して数字を逆算するのが難しく、また求めることが出来る変数も1個だけです。(先ほどの例で言ったら販売個数の変動だけしか算出出来ていませんね。私が知らないだけかもしれませんが・・・)

複数の商品や複数の事業部があって、それらを横断的に分析したい!という場合には、ゴールシークだけでは物足りなくなるかもしれません。

そんな時はEXCELの「ソルバー」というアドインを使ってみて下さい。
詳細な条件を設定した上で目標数値を達成するための分析が可能ですよ。

やり方は、下記記事などを御覧くださいね。

Excelアドイン「ソルバー」を使って売上予測をしてみよう! | JMIRI Office部 | 処方情報分析サービスの医療情報総合研究所(JMIRI)
こんにちは、二回目の投稿となります甘党です。 最近はセ○ン○レブンのマカダミアクッキーをよく食べています。甘すぎずサクサクしておいしいので皆さんも良かったら食べてみてはいかがでしょうか? ...

まとめ

以上、おもに損益分岐点のグラフをEXCELで作成する方法を紹介しました。

また、合わせてシミュレーションをより詳細にするEXCELの機能を紹介しました。

なお、実際にはもう少し細かなら変動損益計算書(&EXCELの機能ソルバー)を利用して分析した方が経営には活かせると思います。

通常の決算書から変動損益計算書を作成するテンプレートなどもネット上では配布されているので、そういうのも利用して是非経営分析を行ってみて下さい。

タイトルとURLをコピーしました