【EXCEL・エクセル㊹】簡単!5分でわかるゴールシーク分析

スポンサードリンク
EXCELでできることについて説明します。今回は、データ分析について説明します。

第44回:データ分析について⑨

 

損益分岐点を見つける手法「ゴールシーク」について学びましょう!

 

12.ゴールシークで損益分岐点を求めたい

 
前回はキャンペーンの売上予測をしましたが、今回は、売上と損益の関係、「損益分岐点」について説明していきます。
 
33-1
 
 
キャンペーンに限らず、商品の販売を事業として行う際は、人件費(給与、保険、年金etc)やテナント費などの、商品の販売数量の有無や大小にかかわらずに一定額かかる『固定費』と、仕入費用や外注費など、販売数量に応じて変動して費用のかかる『変動費』がかかります。
変動費は商品が1個も売れなければゼロですが、それでは固定費分をカバーすることはできません。
商品がある一定数以上販売できれば赤字から黒字に転換するという、利益ゼロの時点の売上高や販売数量のことを「損益分岐点」といいます。
今回は、この損益分岐点について、「ゴールシーク」という手法にて求めていきます。
 
 
まず、『固定費』と『変動費』と『販売数量』、『販売単価』、『売上高』、『利益』の最低6項目の含まれた表(セル)を準備します。
利益のセルには、売上高-(固定費-変動費×販売数量)を意味する計算式を入れておきます。
損益分岐点を求めるため、利益のセルを選択した状態で、ゴールシークを実行します。
データタブにある『What-If分析』をクリックし、「ゴールシーク(G)」をクリックします。
 
33-2
 
 
損益分岐点を求めますので、『目標値(V)』を"0"、『変化させるセル(C)』を"販売数量のセル"にし、OKをクリックします。
 
33-3
 
 
Sponsored Links

ゴールシークウィンドウから"解答が見つかりました"というメッセージが表示されれば成功です。
固定費"20,250"で変動費が"3,000"で単価"5,000"の商品を販売する場合の損益分岐点は、『販売数量10、売上高50,625』という結果になりました。
 
33-4
 
 
とりあえず10個以上売ればよいということがわかりました。
今度は、30個売った場合、単価をいくら以上にすれば黒字になるかについて、同じ『ゴールシーク』を使って検証してみましょう。
 
利益のセルを選択した状態で、ゴールシークを実行します。
今度は『変化をさせるセル(C)』を単価のセルに設定して、OKをクリックします。
 
33-5
 
 
同じくゴールシークウィンドウから、"解答が見つかりました"というメッセージが表示されれば成功です。
30個を販売する場合の損益分岐点となる単価は"3,675"で、その時の売上高が"110,250"となることがわかりました。
 
33-6
 
 
今回でデータ分析の説明は終わりになります。
 
エクセルの基礎はこれでひととおり説明しましたが、次回より便利なおまけ機能についていくつかご紹介して、それで終了にしたいと思います。
もうしばらく、EXCEL基礎編をよろしくお願いします。
それでは、また次回に。
 
スポンサードリンク
PREV:【EXCEL・エクセル㊸】簡単!5分でわかるデータの回帰分析
NEXT:出社前に遠出して遊べるかチャレンジしてみました!大人の朝活!

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です