損益分岐点を計算したいけど、変動費と固定費ってどうわければいいの？
利益と損失の分かれ目「損益分岐点」を知りたい。でも、算式の変動費と固定費って…と、悩んだことはありませんか。
「給料」は固定費なのか、変動費なのか。基本給部分は固定費で、残業代は変動費？などなど。迷いだすとキリがありませんよね。
そこで。変動費と固定費の分類で迷わない、悩まない損益分岐点についておはなしします。
エクセルでカンタンに損益分岐点！変動費と固定費は自分で分けない
損益分岐点を求める算式は、「固定費 ÷（１－変動費 ÷ 売上高）」とされています。少し変形すると「固定費 ÷ （１－変動費率）」。
それはいいけれど。そもそも「変動費」と「固定費」はどうわければいいのか、という問題があります。いわゆる「固変分解」。
損益分岐点を計算するにあたり、固変分解は悩み深いものです。けれども、それを解決する方法があります。
直近12か月分の月次決算の実績値を用意して、あとはエクセルにまかせるという方法です。手順は次のとおり。
- 12か月分の「売上高」「コスト」の数字を集める
- エクセルで散布図をつくる
- エクセルで近似曲線・算式を表示する（固変分解）
- 損益分岐点を計算する
散布図？近似曲線？なんだそれ。
だいじょうぶです。むずかしいことはありません。エクセルが少しいじれれば。
12か月分の「売上高」「コスト」の数字を集めよう
まずは、「売上高」と「コスト」について、エクセルに数字を入力してみましょう。こんな感じです。
例として1月から12月までの12か月分。「黄色セル」に入力をした、というイメージです。
「黄色セル」以外の数値は算式が組んでありますが、「足し算」だけのカンタンなものです。「コスト」は「原価＋経費」の算式になっています。
原価･･･
商品仕入などの売上原価のこと。月次試算表などから「売上原価」の数字を拾いましょう。
経費…
「販売費及び一般管理費＋支払利息」で計算します。月次試算表などから「販売費及び一般管理費の合計額」「支払利息」の数字を拾いましょう。
「販売費及び一般管理費」とは、給料、福利厚生費、消耗品などの諸経費の総称です。
「支払利息」は「販売費及び一般管理費」には含まれず、「営業外費用」と総称される項目の１つです。
エクセルで散布図をつくろう
続いて、次の手順で「散布図」を作成してみます。
- 1月から12月の「売上」「コスト」の数字部分を選択
- 「挿入」タブをクリック
- グラフの「散布図」をクリック
- 小さいウィンドウから、左上の「散布図」をクリック
すると、次のようなグラフが表示されるはずです。これが「散布図」です。
縦軸がコスト、横軸が売上高。これに12か月分のデータがプロットされています。青い丸点がそれです。
ここで、「あること」に気が付きます。右肩上がりの直線状に並んでいますね。
これは「サンプルデータだから」ということはありますが、基本的には実際同じように「右肩上がりの直線状」に並びます。
実はこの直線が、損益分岐点分析で言われる「総コスト線（総費用線）」を表しています。
総コスト線、なんだそれ？という方は、とくに気にされなくてもいまは問題ありません。
ちなみに。この直線から「はみ出た点」があれば、その点が示す月は「間違っているかもしれない月次決算」だ、というおはなしを以前にしました。
エクセルで近似曲線・算式を表示しよう
それではいよいよ、変動費と固定費の問題に入ります。次の手順で「近似曲線」を追加します。
- プロットされている点のいずれかを「右」クリック
- 小さいウィンドウが表示されるので、「近似曲線の追加」をクリック
続けて操作します。
- 「近似曲線の書式設定」で「線形近似」にチェックが入っていることを確認
- 「グラフに数式を表示する」にチェックを入れる
すると、さきほどのグラフ上に次の「算式」が表示されます。
y = 0.6205x +2865.3
結論から言います。「0.6205」が変動費率、「2865.3」が月間固定費です。
これを解説するとすれば、むかしむかしに教わった「1次関数」のおはなしをすることになります。
カンタンに触れておくと。グラフ上の「右肩上がりの直線」を左端の「縦軸」にぶつかるまで伸ばしたとして。
縦軸とぶつかったところが「2865.3」、1次関数では「切片」とよばれていたものです。思い出しましたか？
「0.6205」は、この直線の「傾き」とよばれるもの。「y = ax + b」と公式を覚えていたアレです。
本筋から逸れますので、1次関数の話はこれくらいにしておきます。
損益分岐点を計算する
あとは、冒頭にも示した「損益分岐点の算式」にあてはめるだけです。やってみましょう。
損益分岐点売上高
＝固定費 ÷（１－変動費 ÷売上高）
＝固定費 ÷（１－変動費率）
＝2865.3 × 12 ÷（１－0.6205）
＝90,602.3715･･･
固定費「2865.3」に12を乗じているのは、「2865.3」が月間の固定費だったから。1年分の固定費に直した、ということです。
求められた「90,602」が、このサンプルデータにおける「年間の損益分岐点売上高」です。確かめてみましょう。
利益
＝売上高 －変動費－固定費
＝売上高－（売上高 × 変動費率）－固定費
＝90,602－（90,602 × 0.6205）－2865.3 × 12
＝－0.141
ということで利益は「ほぼゼロ」。つまり、90,602は損益分岐点売上高だということです。
おまけで、いわゆる「損益分岐点比率」を見てみると、
損益分岐点比率
＝損益分岐点 ÷ 実際売上高
＝90,602 ÷ 141,600 × 100
＝63.98･･･％
実際売上高の141,600からみて、損益分岐点90,602は約64％。いま現在は、損益分岐点に対して「かなりの余裕」がある事業だということがわかります。
補足とまとめ
エクセルで固変分解、どうでしたでしょうか。少し補足説明をしてから、はなしをまとめます。
エクセルで固変分解のメリット・デメリット
一般に、固変分解を勘定科目でおこなうという方法（勘定科目法）があります。これには２つのデメリットがあります。
- わける人の考え方しだいで、固変分解の結果が変わる
- わけるのに（迷う・悩むので）時間がかかる
今回おはなしした方法によれば、上記のデメリットを解決することができます。
ですが、今回おはなしした方法にもデメリットがないわけではありません。それは、
- データ元の月次決算がただしいものでなければいけない
- 固定費が「マイナス」として算出される可能性がある
途中でも触れましたが、「間違っているかもしれない月次決算」の月が多いと、「右肩上がりの直線」がうまく引けません。
結果として、ただしい変動費率、固定費額が計算できないことになります。ただしい月次決算を前提にする方法であることは理解しておきましょう。
また、ときおり固定費が「マイナス」になってしまうという現象も起こりえます。そうなってしまうと損益分岐点は計算できません。
あえて詳述は避けますが、そういうことがあり得ます。
この方法は、統計学の最小二乗法という方法によって、便宜的に「右肩上がりの直線」を導き出しているものであり、うまくいかないケースもあるということでご理解ください。
まとめ
事業を行ううえで、利益と損失の分かれ目（損益分岐点）を考えることはたいせつなことです。
同じ売上高であったとしても、損益分岐点はいくらかによって、事業の安全度は変わります。これが、先述した損益分岐点比率の話です。
自社の損益分岐点がどれくらいか、ということは知っていて損はありません。
また、損益分岐点を求めるだけでなく、「変動費型のビジネスか固定費型のビジネスか」など、事業分析にも展開できます。
そのあたりは機会をあらためるとして、まずは損益分岐点から。
決算が終わったとき、経営計画を立てるときなどは損益分岐点を把握するのに良いタイミングです。
エクセルで損益分岐点、ぜひ計算してみましょう！
＊＊＊＊＊＊＊＊＊＊＊＊
きょうの執筆後記
＊＊＊＊＊＊＊＊＊＊＊＊
昨日は、8月申告法人の決算業務。
その後にあたらしいセミナーの検討。あたらしいことを考えるのは楽しくてよいですが、時間がもっともっと欲しい今日この頃。
