エクセルでカンタンに損益分岐点 変動費と固定費はあえてざっくりと
おはようございます。こんにちは。いたをです。
今日で２月も終わりですね。
「二月は逃げる、三月は去る。」気がつけばもう四月になってしまっていた！とならないように一刻一刻を大切に過ごしたいものです。
さて、今日は損益分岐点のお話。
損益分岐点とは、企業経営を行うに当たって「売上＝費用」となる金額、すなわち「得も損もしない」金額（ポイント）。「売上＝費用」となる点（ポイント）では文字通り、売上 － 費用 ＝ 0円になります。
損益分岐点を求めることによって、様々なことが見えてきます。
例えばラーメン屋さんであれば、
１日にどのくらいのお客さんが来てくれれば黒字になるのか？
ラーメン１杯の値段をいくらに設定するとどのくらいの客数で黒字になるのか？
原価を下げるとどのくらい利益が出るのか？
家賃や人件費など経営を圧迫している費用はないか？
などなどいろいろなことを分析することができます。
何かしらの商いをしている人でしたら、これらの数字は避けては通れないので、きちんと現状の損益分岐点を知っておくことは大切です。
でも難しいんでしょ・・・
はい、否定はしません。
すこーしだけ腰を据えてお金の勉強をする必要があると思います。
でも、そんな時間が無い！
とりあえずざっくりとでいいから今の損益分岐点を知っておきたい。
来季以降の計画に大きな見通しを知っておきたい。
なんて時に、「売上高」と「経常利益」という２つの数字を用いて、
エクセルで！
ざっくりと損益分岐点を調べることができるんですよ。
今日はその方法をご紹介しましょう。
１．まずは損益分岐点についてお勉強です
１－１．売上・変動費・固定費
それぞれをグラフにまとめてみます
損益分岐点を求めるには、まず売上と変動費を基に考えていきましょう。
売上とは、商品1つあたりの販売価格✕販売数で求められます。ここではラーメン１杯の価格✕販売数になります。
変動費とは、商品１つあたりにかかる費用のことです。今回の場合はラーメン１杯を作るのに必要な材料などの原価になります。
例えば、ラーメン１杯の価格が900円で原価が450円と仮定します（解かりやすいように半分にしました）。
その場合売上と変動費のグラフは次のようになります。
ラーメンの販売数が増えれば増えるほど、売上と変動費が比例して増えていきます。これは簡単ですね。
次にラーメン店には食材などの変動費以外に、店舗の家賃やアルバイト・パートなどの人件費、ガス代や電気代、水道代などの水道光熱費がかかってきます。販売数に応じて変化しない一定でかかる費用として固定費というものがかかってきます。
先程のグラフに固定費を入れ込んでみましょう。今回はざっくりと１ヶ月に100万円かかっているとします。
最後に変動費を固定費の分だけ上にスライドさせます。
変動費を固定費分スライドさせた時に売上線とぶつかった点（オレンジの点で表したところ）が損益分岐点となります。損益分岐点は黒字と赤字の境目のことです。損益分岐点から右側の「売上」が上に来ている部分が利益が出ている（黒字）ということですね。
１ヶ月に3,000杯ラーメンを売り上げたとしましょう。
売上：900円✕3000杯＝2,700,000円
変動費（原価）：450円✕3,000杯＝1,350,000円
固定費：1,000,000円
利益は売上から変動費と固定費を差し引いたものです。
利益＝売上ー（変動費＋固定費）
利益：2,700,000円－(1,350,000円＋1,000,000円)＝350,000円
35万円の利益が出たことになります。
でも、今日のお題「損益分岐点」はどうやって求めるの？
１－２．損益分岐点を求めてみます
急に難しくなったなぁ・・・と思った人ももう少しお付き合いください。
実はそこまで難しくはないんですよ。
損益分岐点とは、企業経営を行うに当たって「売上＝費用」となる金額、すなわち「得も損もしない」金額（ポイント）。「売上＝費用」となる点（ポイント）では文字通り、売上 － 費用 ＝ 0円になります。
そう、利益は売上から変動費と固定費を差し引いたものですから、
利益＝売上ー(変動費＋固定費)＝0となるポイントが損益分岐点になるわけです。
もう一度３番めに登場したグラフをご覧ください。
固定費は、利益が出ていても、出ていなくてもかかるわけです。
すなわち、損益分岐点は
ようやくここから利益が出始めるポイント！！
というところなんです。
固定費を無視した場合、しっかり利益が残っているのか？というところに注視して、売上から変動費を引いた利益を「限界利益」と考えることにしましょう。
そして、限界利益を売上で割った値を「限界利益率」と考えます。ラーメンの例で例えれば、１杯あたりの限界利益と置き換えてもいいかもしれません。
先程のラーメン店で考えてみますと、
限界利益＝固定費＝1,000,000円
限界利益率＝限界利益÷売上高＝1,350,000円÷2,700,000円＝0.5
売上ー変動費が固定費とイコールになる売上高が損益分岐点での売上（損益分岐点売上高）ということになりますので。
利益が0となる点（損益分岐点）
限界利益＝固定費＝1,000,000円
売上高＝損益分岐点売上高
限界利益率＝（限界利益＝固定費）÷（売上高＝損益分岐点売上高）
ですから
限界利益率＝固定費÷損益分岐点売上高
に置き換えられます。
と、なると・・・、
損益分岐点売上高＝固定費÷限界利益率
と計算できます。
では数字を入れていきますよ。
損益分岐点売上高＝1,000,000円÷0.5
＝2,000,000円
つまり、ラーメン1杯900円で販売した場合2,223杯以上／月売らなければ、赤字になってしまうということになりますね。
お疲れさまでした。
ちょっと休憩しましょう。
２．エクセルで損益分岐点を調べることが！
その方法をご紹介
それでは、今日の本題に参りましょう。
エクセルのある機能を使うことで、損益分岐点を求めることができます。
その方法をご紹介しましょう。
今回ある企業の売上高と経常利益のデータを拝借して（もちろんダミーですよ！）エクセル表にまとめてみました。
えっ、これだけでいいの？
はい。、変動費、固定費をきちんと算出して・・というのが本質ですが、タイトルにも入れたとおり、今日はあえてざっくりと考えてしまいます。
ではどうやって、損益分岐点を求めていくの？
回帰分析を使って損益分岐点売上高を算出する方法です。
回帰分析とは、簡単に言えば過去の事象の中から法則性を見つけ数式化すること。つまり過去の業績（ファクト）から変動比率、固定費を算出するアプローチです。
まっ、難しいことは横に置いておいて、早速やってみましょう。
２－１．まずはグラフを描いていきます
まずは、変動費と固定費の合計であるコストを求めてみましょう。
これは簡単ですね。売上高から経常利益を引き算すればいいですね。
次に、売上高と、コストの行を選択したら、
［挿入］タブの［グラフ］グループから「散布図」を選択します。
そうすると下に散布図のグラフが描かれました。
ちょっと見やすいようにグラフを加工してみましょう。
おわかりとは思いますが、
このグラフの横軸は売上高、縦軸はコスト（固定費＋変動費）になっています。それぞれの月で数値がプロットされているわけですね。
パッと見、なにか相関関係がありそうな感じしませんか。
それでは、エクセルさんに計算してもらいましょう。
プロットされている点をどれでもいいのでクリックして選択します。続いて右クリックして出てくるメニューから「近似曲線の追加」を選択します。
近似曲線のオプションは、「線形近」にしておきます。
また、下の方で、
「グラフに数式を表示する」と「グラフにR-２乗値を表示する」の２箇所にチェックを入れます。
すると、こんなグラフに変わりますよ・・・。
ｙ＝0.6703x✕102498
という方程式が出てきました。
中学校の時に学んだことのある「１次関数」ってやつですね。
この計算式縦軸（y）はコスト、横軸（x）は売上高でした。
ここでの67％（0.6703）は変動費率になります。
また、1022498というのは固定費ということになります。
1億249万8千円ということですね。
ということは、
売上高に67％（0.6703）を掛けて、102498を足せば、固定費＋変動費のコストが求められますよ！ということです。
ほほー！！
ちなみに、R-２乗値の0.972とは何かといいますと、
先程導き出された方程式の確からしさを表します。97%の確率でほぼ正しい数字を出せますよ～ってことだと思ってもらえればいいです。
２－２．次に損益分岐点（売上高）を算出してみます
最初に準備した表の下変動費、変動費率、固定費・・・と次のような表を作成しました。予め計算式も入れておきましょう。
コスト総額：売上高（B7）に変動費率（B8）を掛けて、固定費（B9）を加えれば求められれます。
利益：売上高（B7）からコスト総額（B10）を引けば求められます。
さぁ、いよいよ最後です。
損益分岐点（売上高）を求めてみましょう。
２－３．ゴールシーク機能を使ってシュミレーションします
突然の機能紹介でびっくりしたかもいらっしゃるかもしれません。知名度は低いけれど、実は結構昔から装備されている機能なんです。今日使い方を覚えたら、鼻高々ですよ（笑）。
エクセルの中に入っている「ゴールシーク」機能。
初めて言葉を聞いたって方もいらっしゃるかもしれません。
皆さんのエクセルの中にもちゃんと入ってますよ（）。
ゴールシークとは指定した結果を導き出すために必要な値を求める機能です。今回は損益分岐点売上高（「売上＝費用」となる金額・利益が０である金額）を求めてみることしましょう。
利益を求める式が入っているB11セルを選択します。
次に［データ］タブの［予測］グループから「What-If分析」→「ゴールシーク」を選びます。
ゴールシークのダイアログボックスが表示されますので、
まずは「数式入力セル」がB11になっていることを確認しましょう。
次に「目標値」に0と入力します。これは利益が０になるということですね。ここまで読んできた皆さんは説明は要らないでしょう。
最後に「変化させるセル」欄をクリックします。
ここでは売上高（B7）セルをクリックします。セル番号は絶対参照で表示されます。
ここまで入力できましたら、「OK」ボタンをクリックしましょう。あとはエクセルが自動で計算してくれますよ。
利益のセルは「0」となり、売上高のセルは利益が「0」となるための数値が入ります。すなわち、月売上3億1,060万円を超えれば利益が出て、超えなければ損失が生じるという事がわかります。
３．まとめ
今日は、エクセルで損益分岐点（売上高）をざっくりと調べてみよう！ということで、回帰分析の手法やゴールシーク機能をご紹介しました。
前述していますが、きっちりと固定費、変動費を集計していくことでちゃんとした損益分岐点が算出できるのですが、費用によっては「固定」とも言えたり、時には「変動」とも言えたりするものがあるため厳密に分けるのは難しかったりします。
そんなこともあって、今回はざっくりと分析する手法をご紹介しました。
自分の働いている職場や関心のある企業がどのような常態か、数字を見る機会があればぜひ調べてみるといいですね。
最後に、変動費率とは売上に対する変動費の比率（割合）のことでしたが、この比率が小さいほど、効率よく利益が生まれます。たとえば変動費率が10%と30%の場合、10%の方が3倍の収益力があると言えます。
覚えておきましょう。
それではまた。
＊＊＊
いいなと思ったら応援しよう！
