- 更新日 : 2024年8月21日
損益分岐点をエクセルで作成!計算方法とグラフの作り方
会社の経営状況を分析する方法に、損益分岐点を利用する方法があります。損益分岐点は、どのくらいの売上を上げていかなければならないか、売上とコストのバランスを見るのに便利です。
この記事では、損益分岐点とは何か、どのような算出方法があるのか。さらに、エクセルの関数やグラフを使った算出方法や損益分岐点の書き方、フォーマット作成、無料のテンプレートの活用まで、損益分岐点の基礎から作成までを解説します。
損益分岐点とは
企業活動は、モノやサービスの提供で売上を生み出しますが、一方で、モノやサービスを提供するための人件費や水道光熱費、広告宣伝費などの経費も発生します。このような、売上から経費を差し引いた額が企業にとっての“利益”(営業活動外も含めた経常的な利益は経常利益)です。
損益分岐点は、赤字にも、黒字にもならない、利益がちょうど“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になり、損益分岐点をクリアするには、どのくらいの商品を売れば良いかが反映されます。このように、細かく分析したいときは、エクセルのゴールシーク機能が便利です。
よくある質問
損益分岐点とは?
損益分岐点は、赤字にも、黒字にもならない、利益がちょうど“0”になる売上高のことです。詳しくはこちらをご覧ください。
損益分岐点の計算方法は?
「損益分岐点売上高 = 固定費 ÷{(売上高 - 変動費)÷ 売上高}」などで求められます。詳しくはこちらをご覧ください。
損益分岐点を簡単に、便利に算出する方法はある?
あります。エクセルの機能を使うと便利です。詳しくはこちらをご覧ください。
※ 掲載している情報は記事更新時点のものです。
会計の知識をさらに深めるなら
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容(テンプレートを含む)の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
売上原価とは?計算方法や業種ごとの違いを解説
一般に「原価」と聞くとピンとくる人も、「売上原価」と聞くと原価との違いに考え込む場合があります。 損益計算書で売上の次に表示される売上原価は、財務情報として非常に重要性が高く、いわゆる「コスト」の代表格です。 ここでは、「売上原価」の求め方…
詳しくみる「公益目的事業(公益事業)」に認定されるために知っておくべき3つのポイント
公益法人として会社を立ち上げるためには「公益目的事業(公益事業)」として認定される必要があります。 公益目的事業として認定されるためには、「公益社団法人及び公益財団法人の認定等に関する法律」(以下、認定法)別表各号に定められている23種類の…
詳しくみる直接原価計算とは?全部原価計算との違いや計算のポイント
直接原価計算とは製造費用を変動費と固定費に分け、変動費で原価計算をする方法です。一方で変動費・固定費ともに製造原価とする方法を、全部原価計算と呼びます。どちらも原価計算方法の手法ですが、内容についてあまり詳しくない方も多いでしょう。 本記事…
詳しくみる決算とは?時期や業務の流れ、必要書類について解説!
健全な経営を図るため、また、株主などのステークホルダーへの会計情報を正確に開示し、透明性を確保する責任を果たすために、会社は「決算」を行います。この記事では、なぜ決算が必要なのかという理由から、決算の時期、決算業務の流れ、決算に関わる書類に…
詳しくみる月次推移表とは?見方や書き方、活用方法を紹介
月次推移表とは、月ごとの業績や資産の状況について、他の月と比較して傾向や変化を把握できるものです。うまく活用することで将来に活かせるでしょう。 本記事では月次推移表の作り方や読み方を解説します。月次推移表について理解し、さっそく活用しましょ…
詳しくみる決算修正の方法と注意点 – 前年度修正損益は必ず申告
当年度の決算書を作成する際、すでに確定している過年度の決算書に誤りが見つかる場合があります。 この記事では、決算修正(過年度修正)の具体的なやり方と、決算修正の結果によって生じる可能性のある税務上の問題点と対処方法について解説します。過年度…
詳しくみる