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

2024年4月8日2024年4月9日

当ページのリンクには広告が含まれています。

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

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

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

あと、（簡易的な紹介ですが）、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の右側に「データ系列の書式設定」が出てきますので

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

に変更してください。

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

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

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

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

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

損益分岐点売上高＝固定費÷｛（売上高－変動費）÷売上高}

ちなみに、今回の場合だと損益分岐点売上高は「1,000,000÷｛（5,000,000-2,000,000)÷5,000,000｝＝1,666,667円」となります。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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