経理や経営に関わる中でよく出るキーワード『損益分岐点』
今回はエクセルを使用し、
売上と利益情報のみで損益分岐点分析を
簡便的に行う 方法をご紹介いたします！
損益分岐点とは、簡単言えば、
自社の売上高がいくらまで下がっても利益がでるの？
という疑問に答える手法と言っていいでしょう
ですが、この損益分岐点の作成が以外と厄介・・・
会社の費用を固定費と変動費に分類する必要があるのですが、
どこまでが固定費？ 固定費なのに毎月増減する！ 変動費率って何よ！？
といった悩みに直面する人もいるのではないでしょうか？
当然、精度の高い分析は会社の強みになるのですが、
今すぐ知りたい！ なるべく時間をかけたくない！ という方必見！
今回は、エクセルの機能を使って、
過去の売上と利益から逆引きでサクっと簡単に
損益分岐点を求める方法をご紹介します！
具体的には、売上高と利益の過去データをコピペすれば、
下図のような損益分岐点分析を行える方法のご紹介です
結論だけ読みたい方は目次より、
「エクセルで損益分岐点を簡単に分析する方法」 へ飛んでください！
損益分岐点について簡単におさらい
このページに辿り着いた方は恐らく
損益分岐点を、ご存知の方が多いと思いますので
ここでは簡単にイメージをつける程度のご説明をさせていただきます
損益分岐点といえば下図をよく見かけるのではないでしょうか
そもそも利益を出すには
売上を増やすか、費用を減らせばよいわけですが、
売上が増えれば利益も増えるが、相応の費用も発生します
では、赤字に転じるデッドラインはどこでしょうか？
それは、売上高＝費用 の状態です
すなわち損益分岐点ですね
ここで厄介なのが費用です
費用には大きく分けて、
変動費と固定費があります
変動費は、売上が増えると増加する費用
固定費は、売上に関係なく一定額かかる費用
本来、固定費と変動費を分けるのが一仕事です
きっちり分類しても実際の費用には山谷があり、
図のようには、なかなかいきません。
そこで費用の分析は、置いといて
ざっくりと損益分岐点を計算するのが今回の主旨です！
中学校で習う方程式 ｙ＝ax+b
え！？ 急に方程式ってなんですのん？
と思われるかもしれませんが、
まず下図の方程式をご覧ください
『 y=ax+b 』 という方程式ですね
y=ax+b詳しい説明は下記をご参照ください
この方程式の意味について語る前に視覚的に
損益分岐点と比較してみてください
どうでしょう？ なにか似てませんか？
ええ、同じですね。私には全く同じにしか見えないです
つまり、y=ax+b 使用することで、
損益分岐点が求まるんですよ
なんか、中学で習った方程式がビジネスの現場でも
活用できるって面白いと思うのは私だけでしょうか
y=ax+b について簡単にふれてみましょう
まずは用語ですが、
a を傾き、b を切片と言います
上図では、y=1/2x+1 となりますね
a(傾き) は、x軸方向に２マス行くと、y軸方向に１マスあがります
b(切片)は、x軸が０のとき、＋１マス目がスタート地点となります
上記のルールで直線を引いたとき、
図のオレンジ色になるわけですね
あとは、ｘに適当な数字を代入した際に、
ｙはいくつになるか、という話でいいでしょう
では、損益分岐点に置き換えてみるとどうなるか見てみましょう
a(傾き) ＝変動費（率）、b(切片)＝固定費となります
（なお、売上や、費用にマイナスはないのでマイナス座標は非表示にします）
つまり、傾き（変動費率）と切片（固定費）を
計算できれば、おのずと損益分岐点が分かるということになります
なお後述しますが、計算方法はエクセルが行ってくれるので
関数の使い方を覚えるだけでOKですよ
エクセルを使った回帰分析
私が思うエクセルの素晴らしい機能の一つに、
この回帰分析が簡単にできる点をあげたいです
ですが、話すと長くなるのでココでは、
イメージだけ掴めるような、まとめ方をしてみます
余力があれば、回帰分析についての記事も書きたいです！
まず、誤解を恐れずに言うならば、
回帰分析は、y=ax+bの方程式を求めるための手法です
例えば下記のような売上と費用の実績があったとします
これをグラフにすると次のようになります
結構ばらつきがありますね
この中心に線を引くとすると、こんな感じです
この点線部分（近似直線）を求めるのが、回帰分析です
・・・厳密には少し違いますが
今回、損益分岐点を求める方法では、
今ある実績から近似直線を求めるための手法という認識で話を進めます
エクセルで損益分岐点を簡単に分析する方法
まず始めに分析の手順をご説明します
１．売上と利益の情報を一定期間分並べる
２．売上-利益より、費用を求める
３．傾きと切片を求める
４．損益分岐点が分かる
５．おまけでグラフも作成する
以上です
ここから先は下記のサンプルファイルの内容に沿って進めます
１．売上と利益の情報を一定期間分並べる
図のように、自社の売上と利益を並べてください
→ B列とC列です
A列 月は蛇足ですので、未記入でも構いません
２．売上-利益より、費用を求める
サンプルファイルでは自動計算にしてますが、
売上高-利益＝費用ですね
３．傾きと切片を求める
傾きは変動費率、切片は固定費です
こちらも自動計算にしてますが、
使用している関数を簡単にご紹介します
傾きは、SLOPE関数を用います
関数の中身は、（Y軸の範囲,X軸の範囲）を選択します
X軸は、売上高。 Y軸は費用です。
切片は、INTERCEPT関数を用います
関数の中身は、こちらも（Y軸の範囲,X軸の範囲）です
４．損益分岐点が分かる
５．おまけでグラフも作成する
はい、こんな感じです
と、サンプルファイルでは勝手に計算してますので
計算過程の考え方をご紹介いたします
損益分岐点売上高とは、売上高＝費用ですので
y=ax+b の式で見てみると、
x=売上高、y=費用、a=変動費率、b=固定費 ですから、
売上高＝a✕売上高＋b と置き換えれます
よって、売上高＝b÷（１-a） となります
余談ですが、損益分岐点と一緒に分析しておきたい
安全余裕率についてもサンプルファイルで計算しておきました
実際には、売上高と利益をコピペすれば、
完了ですので、サクっと知りたい方はお試しください！
コメント