【Excel】ゴールシークを使って損益分岐点を求める　ーどこまで値下げができるか

次のような自動車の売上計画があったとします。

競合のB社やC社が自動車価格の値下げを行っており、A社も値下げをしないと対抗しきれない状況となってきました。ただし、利益がゼロより小さくなると事業が立ち行かないので、まず、利益がゼロになる価格を求めたいと思います。

このような利益がゼロになるポイントを損益分岐点と言います。今回は、価格を調整し、損益分岐点をExcelで求める方法を紹介したいと思います。

売上計画の準備

まず、損益分岐点を求めるための売上計画を準備します。

利益については計算式で記述しないとExcelで求めることができないので、注意してください。今回の売上計画の表は下記の計算式で作成しています。

項目	計算式
売上	価格×販売台数（=F6*F7）
価格	（ベタ打ち）
販売台数	（ベタ打ち）
材料費	1台当たり材料費×販売台数（=F9*F7）
1台当たり材料費	（ベタ打ち）
固定費	（ベタ打ち）
利益	売上-材料費-固定費（=F5-F8-F10）

ゴールシークを使用する

損益分岐点を求めたい場合、Excelのゴールシークという機能を用います。

「データ」タブ⇒「What-If分析」⇒「ゴールシーク」をクリックします。

すると下図のウィンドウが表示されます。

入力する内容は次の通りです。

入力項目	選択セル	今回の入力内容
数式入力セル	目標となる数値の数式が入力されているセル	今回は利益を0にしたいので、”F11″を選択
目標値	目標となる数値	今回は利益を0にしたいので、0を入力
変化させるセル	目標となる数値になるように変化させるセル	今回は価格を調整するので、”F6″を選択

これで”OK”を押すと下図のように価格と利益が変化し、利益がゼロになる価格が1,500千円ということが分かります。

別の数字を変化させる

今度はこの状態で別の数字を変化させてみたいと思います。

利益がゼロではさすがに厳しいので、利益が100,000千円になるため「1台当たり材料費」をどこまで下げれば良いかを求めてみたいと思います。

基本的なやり方は同じですが、入力する内容が少し異なります。

入力項目	選択セル	今回の入力内容
数式入力セル	目標となる数値の数式が入力されているセル	今回は利益を100,000にしたいので、”F11″を選択
目標値	目標となる数値	今回は利益を100,000にしたいので、100000を入力
変化させるセル	目標となる数値になるように変化させるセル	今回は価格を調整するので、”F9″を選択

これで”OK”を押すと下図のように1台当たり材料費と利益が変化し、利益が100,000千円になる1台当たり材料費が400千円ということが分かります。

注意点

非常に便利なゴールシーク機能ですが、以下の点を注意する必要があります。

• 目標値を求めるセルは数式（数式は変化させるセルを直接または間接的に参照している）
• 変化させるセルはベタ打ち