エクセル（Excel）で損益分岐点売上高を計算する。グラフで変動費・固定費を視覚化！
本記事では、損益分岐点売上高を自動計算するエクセルシートを紹介します。
このエクセルシートの最大の特徴は、損益分岐点売上高をグラフで視覚化するだけでなく、目標とする経常利益を達成するために、あと何個販売すれば良いのか？を逆算出来るところです。
また、経費をいくら削減すれば、損益分岐点を下げることが出来るのか？など、分析結果を表示してくれる点がポイントになります。
数値を8つ入力するだけで、
【損益計算書を作成し、損益分岐点売上高がいくらなのか？】
が、一目で分かります。是非ご活用ください。
ファイルイメージ
①.損益計算書と変動損益計算書を自動作成
②.売上高の内訳（要素別分解）をグラフで視覚
グラフは２軸構成となっており、下記の通り分析種類が異なります。↓
③.損益分岐点売上高のグラフを自動作成
④.目標を達成するための分析結果を表示
⑤.全体イメージ図
セブン-イレブンのアイスコーヒーは美味しいですよね。
１杯120円で楽しめるんだから、毎日の一息に欠かせません。
さて、でも一体アイスコーヒーを何杯販売すれば、採算が取れるのでしょうか？
材料費や経費を差し引いて、利益が出る販売点数はいくつからでしょうか？
いくら売り上げると利益が出るのでしょうか？
目標とする利益100万円を達成するためには、あと何個販売すれば良いの？
日常生活においても、こんな疑問が浮かぶ場面が多いかと思います。本ファイルは、それらすべてを自動計算して視覚化してくれるエクセル（Excel）ファイルになります。
使用方法
必要とする８つの項目に数値を入力するだけです。
あとはすべて自動計算されます。
売上高：実際の売上高を入力してください。
変動費（売上原価）：売上に応じて金額が変わる経費のことです。具体的には、原材料費や仕入原価などが該当します。
変動費（販管費）：具体的には、外注費、販売手数料、梱包費、運送費などが該当します。
変動費（営業外費用）：具体的には、支払利息などが該当します。
固定費（売上原価）：売上にかかわらず発生する経費のことです。代表的な固定費には、事務所や店舗の賃料、減価償却費などがあります。
固定費（販管費）：代表的な固定費には、人件費、保険料、広告宣伝費などがあります。
1個販売当たり限界利益：商品1個の販売額を1,000円とし、原価を800円とします。この場合、商品が1個売れたとき、売上高と変動費（売上原価）の差額は、1,000円-800円＝200円です。この差額の200円を限界利益と言います。つまり、1個販売当たりの利益のことです。
目標とする経常利益：達成したい経常利益額を入力してください。
分析表の見方
まずは、「①損益分岐点数量を上回る販売数量を達成する」についてです。
販売数量が増加するということは、売上高が増加するということなので、当然利益額も増えます。
必要とする８つの項目の最後の項目、「目標とする経常利益」を入力頂きますと、損益分岐点販売量と必達販売数が計算されます。↓
次に、「②損益分岐点を下げる」についてです。
これには２つの方法があり、
「Ⅰ.変動費を削減する方法」と「Ⅱ.固定費を削減する方法」があります。
Ⅰ.変動費を削減する方法
変動費を削減することは、難しくても値上げをすることは出来ます。価格転嫁です。これにより、損益分岐点販売数量を何個削減できるかが計算されます。
Ⅱ.固定費を削減する方法
変動費の削減が難しい場合、家賃の減額など固定費の削減を検討します。これにより、損益分岐点販売数量を何個削減できるかが計算されます。
以上が、分析表の見方になります。
利益が出ている時の売上高と、損益分岐点トントンの時の売上高の比較
ここで、頭を整理するために利益が出ている場合と、損益分岐点トントンの場合で、グラフがどの様に異なるのかを見ていきましょう。
損益計算書は以下の様に変化します。↓
下記は、上段が利益が出ている場合で、下段が損益分岐点トントンの場合です。
このとき、損益分岐点売上高のグラフは下記の様になります。↓
トントンの場合は、損益分岐点売上高が大きく右にシフトしていることが見て取れます。
さらに、売上高の内訳（要素別分解）を見ていきましょう。↓
トントンの場合は、利益が0円となるため、売上高（青色）＝損益分岐点売上高（オレンジ色）となり、損益分岐点比率が100％（茶色）となっていることが見て取れます。
如何でしたでしょうか？
グラフの変化を見ることで、固定費・変動費の形状を思い浮かべやすくなったと思います。
ちなみに、赤字の場合は下記のようなグラフとなります。↓
損益分岐点売上高は遥か右斜め上となります。。。↓
また、売上高の内訳も下記のように悲惨な姿となります。↓
このように、とっつきにくい損益分岐点売上高も、当エクセルシートを使えば簡単で身近なものになります。
是非、中期経営計画や財務シミュレーションなどにお役立て下さい。
損益分岐点の数式一覧
損益分岐点売上高 ＝ 固定費÷限界利益率
損益分岐点売上高 ＝ 固定費÷｛1－（変動費÷売上高）｝
限界利益率 = 1 － 変動費 ÷ 売上高
固定費 ＝ 損益分岐点売上高×（1－変動費率）
利益 ＝ 売上高－変動費－固定費
損益分岐点販売量 ＝ 固定費÷1個あたり限界利益
損益分岐点比率 ＝ 損益分岐点売上高÷売上高
目標利益を達成できる売上高 ＝ （固定費＋目標利益）÷限界利益率
損益分岐点売上高＝ 固定費÷粗利益率
（応用編：売上原価≒変動費と仮定した場合のみ成り立つ）
ここから先は
¥ 1,620
Amazonギフトカード5,000円分が当たる
メールやTwitterでもお仕事依頼を受け付けています。 どなたでもお気軽にお問い合わせください。