宣伝費をいくらかけたら売り上げがどのくらいになるか？常識的に考えれば宣伝費をかければかけるほど売り上げは伸びます。このとき、売上金額を500万円にしたいときに、宣伝費をいくらに設定すればいいのか、ということが知りたくなります。
その時には逆算が必要になります。逆算の為には、算数や数学の知識が必要になりますが、数字に慣れていなければ、間違えやすく、大げさではなく、危険な行動だと思います。
逆算は、数学的に行わなくても、ある値で計算してみて、それで少なければもっと大きい値で計算してみる、といったことを繰り返すことで目的の値を見つけることができますね。でもそれには途方もない時間が必要です。
そこをなんとかExcelでやりたいと思います。
係数と切片
例えば宣伝費を200円増やせば売り上げが10000円上がれば次のような計算式が成り立ちます。
売上金額=宣伝費×50
この「50」は「係数」と言って、10000円÷200円で計算した、宣伝費1円上がれば売上金額が50円上がるという数値になります。
しかし、宣伝をしなくても店頭に並んでいるだけで売れる個数があります。宣伝費が0円でも発生している売上金額があるのです。
その宣伝費が0円でも10000円の売上がある場合の計算式は次のとおりです。
売上金額=宣伝費×50+10000
この0のときでも発生している「10000」の数値を「切片」と呼びます。
算数の逆算
これで売上金額を求めることができるようになりました。
しかし、今回は宣伝費を求めたいのです。
その場合の計算式を組み立て直します。
売上金額には余計な10000円が組み付いているので、売上金額からそれを引きます。売上金額から10000円（切片）を引いた残りを、宣伝費1円当たりの売上金額である50（係数）で割ります。これが考え方です。
宣伝費=(売上金額-10000[切片])÷50[係数]
これで宣伝費を求めることができます。
この方法が算数での逆算する仕組みです。でもやっぱりこの式を組み立てるのには数学と国語の両方のスキルが必要だと思います。
では、今度は切片の求め方を。。。やめておきましょう。
逆算のための実績の評価
逆算を行うためには、その元となる計算式を調べ、係数と切片を求めなければいけません。その前に、数式にできるような法則があるのかないのかを分析しておかないといけないでしょう。
常識的には宣伝費をかければ売り上げは上がりますが、絶対にそうだとは言い切れません。
そこで、「実績」のデータからまず調査していかなければなりません。
ちなみに実績のないものでも逆算はできます。130円のものを購入しお釣り50円をもらうためにいくら払えばいいか、水は比重1なので1000ccあたり1kgですが100gの水を得るのに何ccの容器いっぱいの水になるか、といった、常識的なものや物理計算のものは数式があらかじめ決まっているので、その数式を当てはめればよいのです。
一方、宣伝費と売上金額は、一度その行動をしてみて、その結果を記録しないとどのくらいの係数でどのくらいの切片なのかがわかりません。このようにやってみないとわからないものは実績のデータが必要です。
単純な逆算を行うためには、一方が上がったらそれに合わせてもう一方が上がる、または下がるということが必要です。それがバラバラであれば法則がないということになるので分析はできません。法則がない場合、法則がないことにももしかしたら原因があるので、それを突き止め解決します。
今回記録した宣伝費と売上金額は次の表の通りです。
それぞれの月でかけられる宣伝費はそれぞれの月に予算があるので違います。その宣伝費での売上金額があります。
宣伝費と売上金額の関係をグラフで表すと次のようになります。
宣伝費が上がると売上金額も上がります。よく見ると完全な直線ではありませんが、まあまあ直線です。このレベルでも数式を分析することができます。
もし、記録した結果が次のように宣伝費が上がっても売り上げがばらついている場合は、逆算は無理でしょう。
このばらつきを確認します。
係数と切片を求める
係数と切片はExcelの関数で求めることができます。
係数はSLOPE関数で求めることができ、SLOPE関数の書式は次のとおりです。
=SLOPE(既知のY,既知のX)
既知というのは実際に記録したデータという意味で捉えます。Yは結果データ、Xは原因データです。宣伝費と売上金額であれば、宣伝費があって売上金額が発生したので、原因は宣伝費、結果は売上金額です。
切片はINTERCEPT関数で求めることができ、INTERCEPT関数の書式は次のとおりです。
=INTERCEPT(既知のY,既知のX)
既知のY、既知のXはSLOPE関数と同じです。
では、係数を求めてみましょう。
既知のYは売上金額の値の範囲C2:C8、既知のXは宣伝費の値の範囲B2:B8なので次の計算式になります。
=SLOPE(C2:C8,B2:B8)
係数の結果は「97.35714」です。
今度は切片を求めてみましょう。
既知のY、既知のXはSLOPE関数と同じなので次の計算式になります。
=SLOPE(C2:C8,B2:B8)
切片の結果は「13714.29」です。
これで売り上げを求める計算式を作ることができます。
売上金額=宣伝費×係数（97.35714）＋切片（13714.29）
Excelのセルを使えば、売上金額を求めたい宣伝費をセルB12に入力、売上金額B13に求めるとして、セルB13に入力する計算式は次の計算式になります。
=B12*B10+B11
では、宣伝費を880円かけた時の売上金額を求めましょう。セルB12に「880」と入力します。
売上金額は「99,389」円と求まります。
もちろんこの方法で求めたことは予測なので外れることもあります。
逆算機能ゴールシーク
売上金額が「100,000」円ぴったりになるための宣伝費はいくらになるでしょうか。それは逆算することになります。逆算の算数をすれば求まりますが、面倒です。そうではなく、売上金額が100,000円になるように宣伝費をずらしていって、100,000円ぴったりの宣伝費を見つけましょう。でもそれは算数的に逆算するよりもっと面倒です。これをExcelにやらせることができます。その逆算で値を見つける機能が「ゴールシーク」です。「ゴール」を「シーク（見つけるまで探す）」機能です。
ゴールシークはデータタブの中のWhat-If分析の中にあります。
このようなゴールシークダイアログボックスが表示されます。
「数式入力セル」は結果の計算式を入力しているセルです。今回は売上金額を求めるのが結果ですので、売上金額の計算式が入力されているセル「B13」を指定します。
「目標値」は結果がいくつになればいいのかを指定します。今回は売上金額の結果が「100000」になるようにゴールを決めますので、「100000」とします。
「変化させるセル」は原因になる値を設定するセルです。今回は宣伝費が原因となりますので、セル「B12」とします。変化させるセルは逆算で知りたい値でもあります。
「OK」ボタンをクリックすると、宣伝費の値をどんどん入れ替えて、一瞬で答えを出します。
ここで結果を確認して「OK」ボタンをクリックし、完了します。
宣伝費は切り上げて「887」円かければ売り上げは100,000円ぴったりになる、と言えます。
まとめ
この記事では、Excelを使って「逆算」と呼ばれる方法について詳しく説明しました。逆算は、ある目標を達成するために必要な条件を見つける方法です。Excelを使うことで、数式や計算を手軽に行うことができます。
ゴールシークは、Excelの強力な機能の1つで、特定の結果を得るための逆算を行う手法です。例えば、ある売上金額を達成するために必要な宣伝費を計算したり、逆に、特定の目標を達成するために必要な条件を見つけたりするのに役立ちます。
ゴールシークを使った逆算は、数学や統計の知識がなくても簡単に行えます。Excel内の機能を活用することで、効率的に計算できます。具体的な例や計算手順も分かりやすく説明しましたので、初心者でも試してみることができます。ぜひ実際に操作してみてください。
最後に、データの収集と分析が逆算の基礎となります。実際のデータから情報を得ることは、ビジネス戦略の改善や意思決定の向上に貢献します。データ収集と分析には、時間をかける価値があることを覚えておいてください。
参考
もう一つのゴールシークの記事です。こちらもぜひご覧ください。
