エクセルを使って、実績から売上を予測する方法です。
次にような売上データの場合、年々売上がアップしていることはわかりますが、月次の売上データを見ると乱高下が激しくて、どの年のデータを参考にすれば良いか判断しにくいです。
そんなときは、エクセルの中にある近似曲線を使うと、予測値を出すことができます。
近似曲線とはグラフに引く線のことですが、グラフに線が引けるということは、「グラフの縦横の関係を式で表せる」ことです。
よって、その計算式から売上高を求めることができます。
売上予測に用意するデータ
まず、売上データを用意します。
今回の期間は５年分です。
なお、あまりにも突飛のあるデータがある場合は外れ値と扱います。
そのときは、データから削除してしまいます。
折れ線グラフを挿入
横軸に年度、縦軸に年次売上を取った折れ線グラフを挿入します。
セルの範囲は、横軸は「C1:H1」まで、縦軸は「C14:H14」までを選択します。
とびとびの範囲を選択するときは、「Ctrl」キーを押しながらドラッグします。
近似曲線の回帰式は、年度を「x」、売上を「y」で表現します。
今回は、線形近似、二次多項近似、対数近似の３種類の近似曲線を引いてそれぞれの数値を計算します。
線形近似：最初に引いてみる線です。実測値が直線的に増加、減少する傾向がみられる場合に利用します。
二次多項近似：実測値に曲線的な上昇がみられるときに引く線です。業績の成長度合いが大きいと判断される強気のときに利用します。
対数近似：実測値は増加傾向を示しているが、次第に横ばいになるときに引く線です。業績の成長が止まりつつあるときなど弱気のときに利用します。
引用：エクセルデータ分析の仕事術より
３種類の近似曲線を引くのは、予測を「普通」「強気」「弱気」で観るためです。
近似曲線をグラフに入れる
まず線形近似を引いてみます。
グラフ上を右クリックし、「近似曲線の追加」を選びます。
そこで線形近似を選び、
さらに、「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にチェックを入れます。
グラフに数式が挿入されました。
同じ手順で、二次多項近似、対数近似を入れていきます。
二次多項近似は、次数を「2」に設定します。
３種類の近似曲線、回帰式、R-2乗値がグラフに入りました。
決定係数で信頼度を測る
最初に各近似曲線の決定係数（R-2乗値）を見ます。
決系係数（R-2乗値）とは、近似曲線の実測値との当てはまりの良さを示す数値で、0～1までの範囲で表示されます。
数値が高いほど、信頼度が上がり、「0.8」以上で精度が高いと判断されます（この回帰式で８割を説明できるということです）。
一般的に決定係数は「0.5」以上あれば、使えると判断されます。
今回の数値は３つとも「0.9」以上あるので、かなり精度が高く、予測に使えると判断できます。
各近似曲線の数式を入力
各近似曲線の回帰式から予測年度の予測売上高をそれぞれ求めます。
線形近似の数式：「=481.1*K2+6528.9」
対数近似の数式：「=1202*LN(K2)+6821.2」
二次多項近似の数式：「=-59.643*K2^2+838.96*K2+6111.4」
※対数近似の「LN」はLN関数。二次多項近似の２乗は「^2」で表します。
データの読み取り方
強気の読み方よりも、弱気の見方と普通の方が売上予測の数値が大きなるという結果が出てしまいました。
どの数値を選択するかにもよりますが、決定係数はどの数式も「0.8」以上あり、どのパターンでもあり得ると考えられます。
ただし、「売上予測」はあくまで予測です。
「売上目標」ではないので、注意が必要です。
売上目標は達成すべき数字です。
売上予測は、現状のままの状態で進めば、「予測した数字が出るだろう」という話で、外部環境の変化（たとえばライバル店の進出など）によっては、予測値に変化が出てきます。
また、売上の増加は、広告費、人件費、在庫費用の増大も招きますので、利益の管理にも注視しなくてはいけません。
いずれにしても、予測から目標額を決める場合は、内部環境や外部環境を考慮して決める必要があります。
まとめ
予測は予測でしかありませんが、その一方、数値が示すものは、「達成できるだけのポテンシャルがある」ということです。
つまり、根拠のないスローガンだけの売上目標ではなく、達成できる根拠のある売上目標を立てられるということです。
自分の感覚で決めていたら、根拠なんて割り出せません。
根拠のないことを頑張ろうとしても、結果が出ているうちは良いのですが、結果が出なくなれば一気に袋小路です。
根拠のないところで笛吹けど、なかなか頑張れるものじゃないでしょう。
エクセルの近似曲線をビジネスに活かしましょう。
コメント