損益分岐点をExcelの分析ツール［ゴールシーク］で求めてみよう

損益分岐点とは、売上と費用が等しくなるポイントのことです。

Excelには［ゴールシーク］という機能があり、ボタンをクリックするだけで、解を求めることができます。

赤字にならないためには、何個売れたらいいのか？単価は適切かしら？など悩んだら、Excelのゴールシークを使ってみてください。

ゴールシークは、最低販売数を求めたり、販売数が決まっているなら、商品の価格はいくらにすればよいかなどを算出できる機能です。

売上と費用と利益を求める表

サンプルとして以下のような表を作成しています。

［数式］タブの［ワークシート分析］グループにある［数式の表示］をクリックして、入力されている数式を確認します。

［売上］を求めるセル［D2］には、単価×販売数、［費用］を求めるセル［D5］には、原材料費と家賃と人件費の合計、［利益］を求めるセル［D9］には、売上から費用を引く数式が入力されています。

ゴールシークを使用する場合、セルに数式を入力して設定しておく必要があります。

再度［数式の表示］をクリックして、数式は非表示にします（元に戻します）。

ゴールシークの設定と実行

［データ］タブの［データツール］グループにある［What-If 分析］をクリックしてメニューの中から［ゴールシーク］をクリックします。

［ゴールシーク］ダイアログボックスが表示されます。各テキストボックスには、以下の内容を指定します。

• ［数式入力セル］には、目標値を求める数式が入力されているセルを指定します。
• ［目標値］には、目標値を直接入力します。
• ［変化させるセル］には、値を変化させるセルを指定します。

ここでは、［数式入力セル］には、セル［D9］を指定します。［数式入力セル］のテキストボックス内にカーソルを置いて、セル［D9］をクリックします。

絶対参照の$マークは、自動で表示されます。

［目標値］のテキストボックスには、［0］を直接入力します。

［変化させるセル］には、セル［D4］を指定します。［変化させるセル］のテキストボックスにカーソルを置いて、セル［D4］をクリックします。

［OK］ボタンをクリックします。

結果、以下のようになります。

セル［D4］の値が［120］、セル［D9］は［0］と表示されます。

赤字にならないためには、最低120個の販売数が必要という解が求められたわけです。

販売数を固定して単価を変化させる

販売数を固定して、単価を変化させるには、以下のように設定します。

［変化させるセル］にセル［D3］を指定します。

結果、以下のようになります。

単価は、2400円にする必要があるということですね。

目標額を設定して販売数を求めるには

損益分岐点ではなく、目標額を設定して販売数を求める場合の方法です。

目標額を100,000円にしたいのであれば、［ゴールシーク］ダイアログボックスの［目標値］に［100000］と入力します。

［数式入力セル］はセル［D9］、変化させるセルはセル［D4］を指定します。

以下のように求められます。

100,000円の利益をだすためには、170個の販売数が必要になるということですね。

ゴールシークの解が見つからない時は反復計算の設定で調整

数式によっては、ゴールシークは解答が見つからずに何度も計算を繰り返すことがあります。

反復計算の設定をしておくと、計算を中断させたりすることができます。

［Excelのオプション］ダイアログボックスの表示

設定するには、［ファイル］タブをクリックして［オプション］をクリックします。

［Excelのオプション］ダイアログボックスを表示するには、キーボードから［Alt］→［F］→［T］を押してもOKです。

計算方法の設定

［Excelのオプション］ダイアログボックスの［数式］タブにある［反復計算を行う］から設定します。

既定では、反復回数が100回に達した時か、誤差が0.001以内になると計算がストップします。

最大反復回数を大きくして、変化の最大値を小さくすると精度が上がりますが、精度を上げると計算に時間がかかることになりますので、少しずつ調整してみてください。

参考ゴールシークについては、以下の記事でも解説しています。

ゴールシーク（逆算シミュレーション）

また、［ゴールシーク］はExcelのバージョンによってボタンの位置が変わっています。

以下の記事を参考にしてください。

ゴールシークのボタンの位置

［What-If 分析］のメニューにある［シナリオの登録と管理］と［データテーブル］については、以下の記事で解説しています。

Excelには、ソルバーという分析ツールもあります。