- 更新日 : 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”になる売上高のことです。詳しくはこちらをご覧ください。
損益分岐点の計算方法は?
「損益分岐点売上高 = 固定費 ÷{(売上高 - 変動費)÷ 売上高}」などで求められます。詳しくはこちらをご覧ください。
損益分岐点を簡単に、便利に算出する方法はある?
あります。エクセルの機能を使うと便利です。詳しくはこちらをご覧ください。
※ 掲載している情報は記事更新時点のものです。
会計の知識をさらに深めるなら
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容(テンプレートを含む)の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
企業会計原則とは?基礎となる7つの一般原則や会計公準についても解説
企業会計原則とは、会計時に守らなければならい基準のこと。公正な会計処理を行うために作られおり、決算書(財務諸表)の監査をするときにも使われています。この記事では基礎となる7つの一般原則をわかりやすく解説しています。 企業会計原則とは 企業会…
詳しくみる資本金とは?企業の平均額やランキングを調査
新たに会社を設立する場合、必要となるお金が「資本金」です。「資本金」という言葉自体は、耳にしたことがある方も多いでしょう。しかし、細部にわたってきちんと理解している方は少ないかもしれません。そこでこの記事では、国内企業における平均額や上位ラ…
詳しくみる持株比率別に連結決算時の会計方法を解説|持分法適用会社との違いも紹介
連結決算では、連結子会社と関連会社の区別、それぞれの会計方法に関する正しい知識が不可欠です。 本記事では、連結決算時の会計方法を持株比率別にお伝えします。持分法を適用するメリットと連結会社と持分法適用会社の違いについても解説しております。会…
詳しくみる岩手県で法人決算を依頼できる税理士は?書類の提出先もあわせて解説
岩手での法人決算は、盛岡市を中心に県内各地の税理士事務所に依頼できます。法人決算は重要度が高くミスが許されない作業でもあるため、税務のプロである税理士に任せるのがおすすめです。 この記事では、岩手の企業が法人決算業務を地元の税理士に依頼する…
詳しくみる決算業務はサポートがあれば効率化できる!メリットや注意点を比較して解説
決算業務とは、法人の年間収益をまとめ、決算書類を作成する業務のことです。しかし「日常業務に加えて決算業務を行う時間の余裕がない」「知識がなくて難しい」という場合も多いのではないでしょうか。そこで、検討していただきたいのが、会計ソフトや専門家…
詳しくみる損益計算書と貸借対照表の違いと見方とは?作り方まで解説
損益計算書と貸借対照表は、決算において作成を求められる重要な財務諸表です。そのためには、2つの帳票の関係を正しく理解し、作成することが重要です。また、財務諸表をはじめとする帳簿や書類の保存については、電子帳簿保存法の改正にも注意が必要です。…
詳しくみる