全999文字
PR

 営業管理では、条件に合った目標販売数を計算したいことがある。こうしたニーズにはExcelの「ゴールシーク」が応えてくれる。

条件に合致する値を探す

 単価500円の商品がある。売り上げから原価を引いた総利益が100万円になるよう目指したい。総利益率が25%だとすると、商品を何個売れば目標を達成できるだろう。

 計算に慣れた人ならば即答できるかもしれないが、普通は「はてな?」と考えてしまうのではないか。ゴールシークを利用して計算してみよう。そのために作成したのが下図の表だ。

「販売数」にあたるB2が空白になっている。ゴールシークを用いてここに目標販売数を計算する
「販売数」にあたるB2が空白になっている。ゴールシークを用いてここに目標販売数を計算する
[画像のクリックで拡大表示]

 B1には「単価」の「¥500」が直接入力してある。B2の「販売数」は空白だ。「売上」のB3は「=B1*B2」で、B2が空白のため、現在は「¥0」となっている。B4の「総利益率」には「25%」と直接入力した。「総利益」のB5は、売り上げに総利益率を掛け算したら計算できる。よって計算式は「=B3*B4」になっている。

 では、ゴールシークを実行しよう。目当ては空白セルB2の「販売数」で、この値をゴールシークで計算する。

 まず、「総利益」にあたるB5を選択したら、「データ」タブの「What-If分析」ボタンから「ゴールシーク」を選んでダイアログを表示する。

 「数式入力セル」には「B5」が入っている。「目標値」には目標総利益である「1000000」を入力する。「変化させるセル」は「販売数」の「B2」を指定する。

「ゴールシーク」ダイアログで、「目標値」には「1000000」、「変化させるセル」には「販売数」のB2を指定する
「ゴールシーク」ダイアログで、「目標値」には「1000000」、「変化させるセル」には「販売数」のB2を指定する
[画像のクリックで拡大表示]

 以上の設定ができたら「OK」を押してみよう。計算が始まり、結果、目標販売数は「8,000」個で、目標売上高は「4,000,000」円になることが分かる。

「ゴールシーク」による結果が出た。目標販売数は「8,000」個、目標売上高は「4,000,000」円になった
「ゴールシーク」による結果が出た。目標販売数は「8,000」個、目標売上高は「4,000,000」円になった
[画像のクリックで拡大表示]

 ちなみに、上記の例の場合、目標とする100万円を総利益率の25%で割れば、目標売上高を算出できるし、そこから目標販売数も計算できる。