損益分岐点をエクセルで作成！計算方法とグラフの作り方
会社の経営状況を分析する方法に、損益分岐点を利用する方法があります。損益分岐点は、どのくらいの売上を上げていかなければならないか、売上とコストのバランスを見るのに便利です。
この記事では、損益分岐点とは何か、どのような算出方法があるのか。さらに、エクセルの関数やグラフを使った算出方法や損益分岐点の書き方、フォーマット作成、無料のテンプレートの活用まで、損益分岐点の基礎から作成までを解説します。
損益分岐点とは
企業活動は、モノやサービスの提供で売上を生み出しますが、一方で、モノやサービスを提供するための人件費や水道光熱費、広告宣伝費などの経費も発生します。このような、売上から経費を差し引いた額が企業にとっての“利益”（営業活動外も含めた経常的な利益は経常利益）です。
損益分岐点は、赤字にも、黒字にもならない、利益がちょうど“0”になる売上高のこと。どれくらいの売上高であれば利益ができるのか、あるいは損失にならないのかの分析に使われるものです。
損益分岐点の計算式
損益分岐点となる売上高を求めたい場合、以下のような計算式を使います。
2の計算式で使う「限界利益」は、「売上高–変動費」の額です。
3の計算式にある限界利益率は、「限界利益 ÷ 売上高」、つまり売上高に対する限界利益の割合です。
このように、求め方としては3つあるように見えますが、
｛（売上高 － 変動費）÷ 売上高｝＝（限界利益 ÷ 売上高）＝ 限界利益率
となり、いずれも限界利益率を示した式になりますので、計算のやり方はどれも同じと言えます。
固定費5000円、変動費70000円、売上高200000円の場合を事例に、1の計算式に当てはめて計算してみましょう。
50000円 ÷｛（200000円－70000円）÷ 200000円｝＝約77000円
この場合、損益分岐点売上高は約77000円です。
なお、（200000円－70000円）÷200000円＝0.65が限界利益率となります。
1の計算式は数字を上記のように数字を当てはめれば簡単に損益分岐点売上高を出せますが、計算式としてはやや長いので覚えにくいです。覚え方としては、「固定費÷限界利益率」で、限界利益率はどうやって出せばよかった？といった流れで覚えた方が簡単かもしれません。
このように損益分岐点売上高の計算で、売上高は明確に分かるかと思います。注意点は、計算で必要な固定費や変動費をどのように分けるかです。固定費や変動費は、いずれも売上高に対する費用で、以下のような考えで区分します。
| 固定費 | 地代家賃や人件費など、売上の増減にかかわらず発生する費用 |
| 変動費 | 原料費や販売手数料など、売上に比例して増加する費用 |
エクセルを使った損益分岐点グラフの作り方
損益分岐点は、ここまで説明してきたように、計算式を使って導くことができます。難しい式ではないので、簡単に損益分岐点を知ることはできますが、その都度計算しなくてはならないのが不便です。
損益分岐点を簡単に、便利に算出したい時は、エクセルの機能を使って損益分岐点を作るのが便利です。この項では、エクセルを使った損益分岐点の書き方（グラフ作成の仕方）、より細かく損益分岐点を分析したい時に使えるエクセルのゴールシーク機能（回帰分析）を使った損益分岐点の出し方について解説します。
1.グラフのもとになるデータを作成する
- エクセルで売上高の最小値と売上高の最大値の表を作ります。
- 赤枠の中に数字を入れていきます。
- 売上が最小値（0）の時、変動費は発生しないと考えられますので、固定費の額をそのまま「変動費＋固定費」の額に持っていきます。
- 売上の最大値について例えば1年を基準に売上が一番高い月を選択。
- 売上の最も高い月を参考に、変動費をプラスします。（固定費はそのままです。）
売上の最小値と売り上げの最大値を入れれば、損益分岐点のグラフの準備は完了です。
2.グラフを選択する
- グラフにする表の範囲を指定します。（この場合B3:D6が範囲です。）
- エクセル上部のタブ［挿入］をクリック。
- 真ん中あたりにある［グラフ］の右下の矢印をクリック。
- グラフの挿入画面が拡大で表示されます。
- 上部のタブから［すべてのグラフ］をクリックし、上から5番目の［面］をクリック
- すべての数値が積み重なるように表示される、画面上では右側のグラフを選択します。
3.グラフを調整する
- グラフの作成をクリックするとエクセルにグラフが挿入されます
- そのままでは見にくいので調整していきましょう。（今回は、売上高と変動費＋固定の塗りつぶしを透明化、固定費をパターンの塗りつぶしに調整し、グラフタイトルを「損益分岐点」に変更しました。）
好みに合わせてグラフを調整すれば、損益分岐点のグラフの完成です。一度作成してしまえば、表の値を変えることでグラフに反映されるようになるので、フォーマット代わりにもなります。
損益計算書を利用して、変動費や固定費を自動で反映するようにしたい場合は、データをエクセルの別シートに入れて、画像の表部分にデータをもってくるように関数を入れることで自動的に反映させることも可能です。グラフの作成がうまくいかない場合は、無料のテンプレートなどもありますので、活用してみるのも良いでしょう。
なお、上記のような損益分岐点のグラフの作成は、グラフ機能の備わった表計算ソフトであれば基本的に作成できます。エクセルがない場合は、グラフ機能のあるほかの表計算ソフトで代用しても問題ありません。
ゴールシークを使った損益分岐点の求め方
売上や変動費を単価や販売数量ごとに細かく分析したい場合は、エクセルのゴールシーク（回帰分析）が便利です。
- 分析したい商品の単価や仕入単価などを入力していく。
- 上記で計算式の入っている売上高・変動費・限界利益・経常利益には関数を入れておく。（数字が変わっても自動で計算してくれます。）
- 上部のタブの［データ］をクリックし、右の方にある[予測]の［What-If分析］の下矢印にカーソルを合わせます。
- カーソルを合わせると［ゴールシーク］が出てくるのでクリック。
- 今回は損益分岐点を知りたいので、数式入力セルに経常利益のセルを入力。
- 売上は販売数量で変わるので、変化させるセルに販売数の数字が入っているセルを選択します。
- 損益分岐点を知りたいので目標値は0に設定しましょう。
［OK］ボタンをクリックすると、経常利益が0になり、損益分岐点をクリアするには、どのくらいの商品を売れば良いかが反映されます。このように、細かく分析したいときは、エクセルのゴールシーク機能が便利です。
※ 掲載している情報は記事更新時点のものです。
