会計の基礎知識

エクセルで損益分岐点を計算する方法

損益分岐点は会社を経営していくうえで、とても重要な経営指標となります。そこで今回は、エクセルを使って損益分岐点を計算する方法について解説します。

エクセルで損益分岐点を計算する方法

目次

損益分岐点とは

損益分岐点とは、売上高と費用が等しくなり損益がプラスマイナスゼロとなる売上高のことです。つまり、売上があがることで費用を賄うことができる売上高のことになります。売上高が損益分岐点を超えてくれば利益がでることになり、損益分岐点の満たないと利益がでるどころか損失をだすことになります。

売上高が損益分岐点未満であれば損失が生じることになり、損益分岐点を超えることで利益が生じることから採算点ともいわれています。

損益分岐点の売上高を算出するには以下の計算式を使います。
損益分岐点売上高=固定費÷{1-(変動費÷売上高)}

費用は固定費と変動費に分けることができます。固定費とは売上高の変動に関わらずかかってくる費用となります。例えば事業所の家賃や水道光熱費、また人件費や広告宣伝費、機械設備の費用、また減価償却費などがあたります。

変動費は売上の増減によって変動する費用のことです。商品の仕入や、外注費、また歩合給などがあります。一般的には売上原価として、売上高が増加するごとに変動費は増加することになります。

売上高から変動費を差し引いた金額は「限界利益」といい、売上高の占める限界利益の割合を「限界利益率」といいます。
上記の損益分岐点の算出に、限界利益率を使う計算式は以下の通りです。

損益分岐点売上高=固定費÷限界利益率

損益分岐点売上高を超える売上高があることで利益が発生します。そのため、まずは損益分岐点売上高を最低限の売上目標とすることになります。限界利益がゼロまたはマイナスとなっている場合、どれだけ売上高を増やしても赤字が大きくなるだけとなります。

損益分岐点売上高を算出する前に、限界利益率をしっかりと確認する必要があります。そのうえで、損益分岐点売上高を下げていくために、限界利益率をあげるために変動費を落としていくようにし、また固定費も見直していく必要があります。

エクセルで損益分岐点を計算する

会社の損益分岐点売上高をエクセルで計算してみます。ますは会社における1年間の業績を元に算出してみます。以下の金額は暫定的にしています。

【収益】

売上高 36,000,000

【費用】

仕入 19,800,00
役員報酬 6,000,000
給料 9,969,000
交通費 348,000
通信費 268,000
交際費 185,000
減価償却費 548,000
賃借料 800,000
保険料 148000
修繕費 228,000
光熱費 235,000
消耗品費 228,000
雑費 98,000
支払利息 225,000
費用合計 39,080,000
変動費 19,800,000
固定費 19,280,000

便宜的に費用は仕入が変動費用として、その他は全て固定費とします。
上記の業績では、費用合計39,080,000円、変動費19,800,000円、変動費19,280,000円となります。費用合計に対して売上高が少ないため、前期の1年間の業績は赤字となっています。これを黒字転換するために、損益分岐点売上高を把握しておかなければなりません。

損益分岐点売上高=固定費÷限界利益率

となるため、まずは限界利益率を求めていきます。

売上高 36,000,000
変動費 19,800,000
限界利益 16,200,000
限界利益率 0.45

損益分岐点売上高=固定費19,280,000÷限界利益率0.45

固定費 19,280,000
限界利益率 0.45
損益分岐点売上高 42,844,444

上記の計算では損益分岐点売上高は42,844,444円となります。前期の業績で固定費が変わらず、変動費率、つまり限界利益率が変わらないという条件がつきますが、損益分岐点売上高が確認することができます。

エクセルで損益分岐点のグラフを作る

上記のグラフは一番下の直線が変動費となり、そのうえの真ん中の線が変動費に固定費を加算したものです。左下から損益分岐点を超え一番右上まで伸びている直線が売上高となります。一定の売上高になるまでは、利益ではなく損失が発生している状態となります。損益分岐点で利益損失はプラスマイナスゼロとなり、損益分岐点を超えることで利益が発生します。

限界利益は売上から変動費を差し引いた金額となります。限界利益は売上があればプラスとなりますが、損益分岐点まで売上がいかないと利益はでずに赤字となってしまいます。また損益分岐点を超えてくると、限界利益の増加分は全て利益となります。

企業の営業活動で必要な費用は変動費と固定費に分けることができるとされていますが、人件費であっても必ず固定費になるというわけではありません。

基本的に従業員の給与は固定費となりますが、売上が増加することで既存の従業員では業務がまわらず、臨時のパートやアルバイトを雇う場合もあります。そのため、変動費として仕入や一部の販売管理費だけでなく、固定費とも変動費ともどちらとも判断しがたい費用が発生します。

また営業社員で歩合給をつけている場合には、従業員の給与であっても固定費ではなく変動費扱いとなるのが一般的です。

まとめ

エクセルで損益分岐点を計算することはそれほど難しいことではありません。固定費と変動費の金額が確認できれば、損益分岐点売上高をだすことができます。ただし、損益分岐点売上高を算出することは目的ではありません。
会社が赤字であれば、売上高を増やし変動費と固定費を下げて黒字転換にする、黒字であればどの程度の余裕があるのか見極めることが大切です。

経理をもっとラクにするために、会計freeeを活用しましょう

会計freeeを使えば、日々の経理業務に使う時間を大幅に短縮できます。

クラウド会計ソフト freeeサイト

「自動で経理」で日々の帳簿付けを手軽に

銀行口座やクレジットカードを同期することにより、利用履歴を取り込めます。

取り込んだ明細は、「自動で経理」という機能を使ってカンタンに帳簿付けできます。収入・支出の登録はもちろん、売掛金や買掛金の消し込み、資金の移動なども記帳できます。

経営層にもわかりやすく伝わるレポートもかんたんに

「資金繰りレポート」では、今後のお金の出入りを踏まえた上で、資金ショートを起こさないか確認できます。直接法のキャッシュ・フロー計算書作成にも活用できます。

会計freeeの資金繰りレポート機能

「損益レポート」を使えば、

  • ある部門の損益状況を時系列で確認する
  • 費用・収益の発生状況をグラフから確認する
  • 利益水準が高い月は何月だったのか
などを確認できます。

会計freeeの損益レポート機能

決算関連の書類作成にも対応

損益計算書と貸借対照表が即座に作成できます。

会計freeeの貸借対照表・損益計算書出力イメージ

ほかにも効率的な経理を実現するために数多くの機能があります。

  • 仕訳帳・総勘定元帳のCSV/PDF出力
  • 見積書/請求書/納品書の発行
  • 入金確認や消込、帳簿への反映
  • 支払管理や振込ファイルの自動作成
  • 証憑管理(電子帳簿保存対応)etc...


今すぐ会計freeeを使ってみたい方は、会計freeeアカウントの新規作成(無料)ページからお試しください。

クラウド会計ソフト freee

決算書は初めてでも簡単に作成できます。自動化させると日々の経理にかける時間は大きく削減されます。

バックオフィス基礎知識