- 更新日 : 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”になる売上高のことです。詳しくはこちらをご覧ください。
損益分岐点の計算方法は?
「損益分岐点売上高 = 固定費 ÷{(売上高 - 変動費)÷ 売上高}」などで求められます。詳しくはこちらをご覧ください。
損益分岐点を簡単に、便利に算出する方法はある?
あります。エクセルの機能を使うと便利です。詳しくはこちらをご覧ください。
※ 掲載している情報は記事更新時点のものです。
会計の知識をさらに深めるなら
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容(テンプレートを含む)の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
ROEとROAとは?違いや目安、分析方法の解説
財務指標の中で、似ているものにROEとROAがあります。どちらも投資家が注目する指標で、企業分析に役立ちます。しかし、計算方法や分析の方法は異なるため、両者の概要と違いを理解しておくといいでしょう。この記事では、ROEとROAの概要や目安と…
詳しくみる決算申告のみを税理士に依頼するメリットは?相場や依頼方法、注意点を解説
決算申告には専門知識が必要であり、税理士に依頼すべきか迷う方も多いのではないでしょうか。税理士への依頼は顧問契約と決算申告のみの2種類があり、決算申告だけの依頼であればコストを抑えられます。 本記事では、決算申告のみを税理士に依頼する場合の…
詳しくみる決算とは?時期や業務の流れ、必要書類について解説!
健全な経営を図るため、また、株主などのステークホルダーへの会計情報を正確に開示し、透明性を確保する責任を果たすために、会社は「決算」を行います。この記事では、なぜ決算が必要なのかという理由から、決算の時期、決算業務の流れ、決算に関わる書類に…
詳しくみる純資産とは?総資産との違いや内訳、見るべきポイントを解説
「純資産」とは、企業が所有する資産の総額から負債の総額を差し引いたものです。組織の実質的な経済的価値を表しており、財務状態の健全性を評価するのに用いられます。本記事では純資産と総資産の違いや内訳、決算書において見るべきポイントをわかりやすく…
詳しくみる決算申告とは?期限や必要書類、作成手順などをわかりやすく解説
一般に、法人は年に一度決算を確定させて、その決算に基づいて確定申告をします。決算書を作成し、確定申告さえすればよいのではなく、法に基づいて決算書類を作成し、取締役会や株主総会で承認を得た上で、法人税をはじめとする申告及び納税をすることになり…
詳しくみる連結決算で親子会社間に決算期のずれがある場合は?わかりやすく解説
連結決算において親・子会社間で決算期がずれている場合、一定の期日を境に対応が異なることをご存知ない企業の方も多いのではないでしょうか。原則として決算日は統一することが推奨されますが、ずれている期間によっては調整しなくても良いケースもあります…
詳しくみる