Excel。ゴールシークを使って損益分岐点を算出してみよう。
＜ゴールシーク＞
損益分岐点は、ある一定以上の売上を計上しないと赤字になってしまうので、赤字から黒字に切り替わる、利益ゼロの売上高や販売数のことですが、損益分岐点を算出するのに、よく損益分岐点のグラフを作ってみたりします。しかし、ExcelのWhat-IF分析にある『ゴールシーク』を使うことで、ビックリするぐらい、簡単に算出することができます。
次のような表を用意します。
本当は、固定費や変動費はもっと細かくするのですが…。
E2:F6の表は、最低いくつ販売しないと赤字になっちゃうのかを確認する表です。
H2:I6の表は、最低いくらで販売しないと赤字になっちゃうのかを確認する表です。
今回、利益は、売上高-(固定費+変動費×販売数)で算出することができますので、
F6には、=F5-(C3+C4*F3)
I6には、=I5-(C3+C4*I3)
という数式が設定してあります。
ゴールシークを使う場合、数式が設定されているセルが必要になります。
【ゴールシークを設定しよう】
データタブのWhat-IF分析にある「ゴールシーク」をクリックします。
ゴールシークダイアログボックスが表示されます。
数式入力セルは、利益を算出している、F6です。F6をクリックすると、絶対参照がついた$F$6と入力されます。
目標値は、先程設定した、「数式入力セル」の値の目標値です。
今回は損益分岐点を算出したいわけですから、利益が0（ゼロ）になる数値を算出するので、0（ゼロ）を入力します。
変化させるセルですが、最低いくつ販売したらいいのかを知りたいわけですね。
今回は、販売数ですから、F3をクリックすると、絶対参照が設定された、$F$3と入力されます。
あとは、OKボタンをクリックしましょう。
一生懸命、Excelが計算を繰り返して、適切な値を算出してくれます。
無事に解答が見つかったとメッセージが表示されますので、OKボタンをクリックしましょう。
結果、最低販売数は100を満たさないと、赤字になってしまうことがわかったわけですね。
いちいち、自分で何度も数値を当てはめて算出するよりもゴールシークを使うことで簡単に算出することができました。
同様に、今度は、販売単価を変えるとしたらいくらにすればいいのかを、ゴールシークを使って算出してみましょう。
今回は、ゴールシークダイアログボックスの設定は、販売価格なので、変化させるセルが、販売価格のI4をクリックすればいいだけで、他のボックスは大きく変更はありません。
では、OKボタンをクリックしましょう。
結果、販売単価は、販売数を1000とした場合は、2050円を下回ると、赤字になってしまうことがわかりました。
このように、難しそうな損益分岐点をゴールシークという機能を使うことで、比較的簡単に算出することができます。
