1. 法人のお客さまトップ
  2. BizDrive
  3. お役立ち情報2
  4. オフィスソフト使いこなし
  5. エクセルで売り上げ予測を立てて目標を設定する
閉じる

2016.08.09 (Tue)

オフィスソフト使いこなし

オフィスソフト使いこなし（第22回）

エクセルで売り上げ予測を立てて目標を設定する

posted by 小山田 明人

　年度計画や新商品を発売するときに、必ず立てるのが売り上げ目標です。予測分析は過去の売り上げ実績などがベースとなりますが、計算表に手打ちをすると時間がかかり、ミスも起こりやすくなります。

　今回は、エクセルを使った効率的な売り上げ目標の設定の仕方を紹介します。

（１）過去の売り上げ実績を分析する

　まずは過去の実績を分析することから始めます。売り上げデータは様々な角度から集計されているかと思われますが、できれば数年分についてわかりやすくまとめていきましょう。

　今回は、ある会社のTシャツの売り上げについて、簡単に月ごとの売り上げを表にまとめてみました。

（2）グラフ化でさらに見やすく

　売り上げの推移をグラフ化することでさらに見やすく、分析が容易になります。

　行と列（ここでは“月”と“年”を含む範囲）を指定し、グラフの「折れ線」を選びクリックすると、すぐにグラフが作成、表示されます。

　タイトルの部分をクリックしタイトルを入力すると、ナビゲーションが表示されるので、文字や枠線などが変更できます。

　このグラフでは平均値が見にくいので、平均値のみ黒の点線に変更します。平均値の線の上でクリックするとナビゲーションが表示されるので、「線の色」を黒に、「実線／点線」を点線に変更します。

　売り上げの単位として「（単位　千円）」というテキストを、数値の上部に追加します。テキストボックスを使い、大きさや位置を調整しましょう。

（3）要因データを分析する

　続いて、売り上げに関係する「要因分析」の表も作りましょう。今回は、「Tシャツは暑い時期によく売れる」との仮定から、気温と降水量に注目した表を作成します。

　気温と降水量は、気象庁のホームページで公開されている情報を用います。

　気象庁のホームページで表示されたデータは、エクセルにそのままコピーできます。「Ctrl + C」のショートカットなどを利用して、新しいエクセルのシートにコピーします。

必要な年度分のデータをエクセルに貼り付け、それぞれの年度の降水量と気温をまとめます。その後、必要なデータを別のシートにまとめます。

 

　データは折れ線グラフにして比較しやすいようにしておきましょう。データを比較すると、売り上げは気温が高いほど伸びる傾向があり、さらに、降水量が多い月は特によく売れている傾向が確認されました。

（4）伸び率から目標を設定する

　過去の分析が終わったら、実績の伸び率を元に、未来の目標を設定してみましょう。目標値との区別がつきやすいように、すでに完成したシート名を「過去実績」に変更します。

　まずは、これまでの実績データを元に、前年比の伸び率の表を作ります。前年比の伸び率は、「その年の実績／前年の実績」の数式で計算できます。計算結果が「1.0」を上回っていれば、昨年よりも高い数値を記録していることになり、逆に下回るようでれば、昨年よりも売上が落ちていることを意味します。

　その後、過去数年間の伸び率の平均値を「Average」関数を用いて計算することで、「予想伸び率」が算出されます。

　数式はコピーしてしまえば、簡単に一覧が作成できます。小数点の桁数が多く表示される場合は、「小数点ボタン」を使って、小数点2桁程度に収めておきましょう。左側をクリックすると小数点以下の桁数が増え、右側のボタンをクリックすれば減ります。

　ここで得たデータを元に、目標値を算出しましょう。新しく「目標」というシートを作り、これまでの伸び率のデータをコピーします。

　数式による計算結果をコピーして貼り付けると、数式のみが貼り付けられてしまいます。数値をそのままコピーするには、貼付け時に「Ctrl + Alt + V」ボタンを押し、「形式を選択して貼り付け」というウインドウで「値（V）」を選択すれば大丈夫です。これは一般的に「値コピー」と呼ばれます。

　その後、直近の年の実績数値と、すでに計算した予想伸び率を掛け合わせることで、売上目標値が設定できるというわけです。

　欄外に、年間の合計を表示させる数式も作ってみました。たとえば「今年の夏は涼しいからあまり売上が伸びないかもしれない」という場合は、予想伸び率の欄を平均より低く設定することで、年間売上を低く見積もることができます。

　一方で、「今年は猛暑だから売上が伸びそう」という場合は、7～8月の予想伸び率の数値を高く設定しましょう。

（5）目標値を決めて逆算する「ゴールシーク」

　過去のデータから予測するのではなく、目標値を決めて、そこから逆算する方法もあります。この場合「ゴールシーク」という機能を使うと簡単です。

　以下の表では、7月の予想伸び率を極端に低く見込んでいます。しかし、最終的な目標を達成するためには、この数値をどうにか伸ばす必要があります。

　そこで活躍するのがゴールシークです。「データ」タブの中の「what-if分析」→「ゴールシーク」の順にクリックします。

 

　ゴールシークをクリックするとウインドウが表示されるので、売り上げ目標値が表示されているセル、目標にしたい数値（この場合は年間売上目標）、変化させたいセル（この場合は7月の予想伸び率）を指定します。

　たとえば「今年の年間売上は5,300万円だけど、やっぱり5,500万円にしたい。そのためには7月にどれほどの数値を達成すべきなのか」という場合は、目標の数値を「5,300→5,500」に変えることで、7月の伸び率のデータも連動して変わります。

　これにより、より高い目標達成が可能かどうかわかりやすくなり、社員全員での共有もしやすくなります。

まとめ

　ゴールシークを上手に使うと、それが現実的な目標かどうか、わかりやすくなります。会社全体で共有することもやりやすくなるので、色々とシミュレーションしながら検討すると良いでしょう。

連載記事一覧

小山田 明人

小山田 明人

株式会社ネクストアド代表取締役。大手金融企業、学習塾経営を経て2015年株式会社ネクストアド代表取締役に就任。テストに合格した専門ライターだけが在籍するプロライターキャッチを軸にさまざまなメディアのマーケティングや制作の支援を行う。

メルマガ登録


NTT EAST DX SOLUTION


ミライeまち.com


「ビジネスの最適解」をお届けします　無料ダウンロード資料


イベント・セミナー情報

用語辞典

BYOD ICT スマートデバイス マルウェア BCP／事業継続計画 標的型攻撃 情報漏洩 ランサムウェア サプライチェーン VPN UTM／統合脅威管理 無線LAN 仮想化 Wi-Fi

用語一覧へ

ページトップへ

ページ上部へ戻る