Excelで売上予測を計算する方法｜予測シートや時系列分析法、関数について紹介

自分の会社の売上予測をひと目で確認できるものがないかとお困りの方もいるのではないでしょうか。また、ちょうど良いツールがなければ、Excelで計算できないかと考える方も多いでしょう。Excelなら、さまざまな計算ができるため売上予測にも利用できます。

今回は、Excelを使用した売上予測の計算方法について解説します。

売上予測とは？

売上予測とは、一定期間内における売上金額を予測した数値のことです。売上予測をもとにして経営戦略を練ります。

売上予測を作成する際には、具体的なデータの数値を計算式に当てはめて算出します。そのため、次のようなデータを一通り揃えなければなりません。

・商品・サービス別の売上実績

・期間別の売上実績

・顧客別の売上実績

・受注率

・解約率

・見込み顧客からの成約率

・成長率

・コンバージョン率

いずれも前年と過去数年のデータが必要です。商品・サービス別の売上実績は、売上への貢献度の高い商品やサービスに関するデータのみで問題ありません。顧客別の売上実績に関しても同様です。

他にも業態に合わせて重要度の高いデータがあれば用意しておきましょう。

売上予測の計算方法

売上予測は、以下の計算式で求めることができます。

前年の売上金額または過去数年の平均売上金額×成長率

前年の売上金額の代わりに過去数年の平均売上金額を用いることも可能です。年度により売上金額の変化が激しい場合には、過去数年の平均売上金額を用いれば精度が増すでしょう。

また、成長率は以下の計算式で求めることができます。

（前年の売上－2年前の売上）÷2年前の売上×100

売上予測の重要性

売上予測は、企業が他の数値を算出したり、計画を立案したりする際の基礎として活用される重要な指標です。

ここでは、売上予測が活用され、重要とされる具体的なシーンを見ていきましょう。

生産量や在庫の管理

企業が商品の生産量を決定したり生産計画を策定したりする際には、売上予測をもとにしています。過不足なく生産できているのは、適正な売上予測があるためです。売上予測が実際の売上と大幅に違っていた場合には、欠品や過剰生産が発生してしまいます。

機会損失や在庫の保管コストを減らすために、売上予測は重要です。

販売戦略やプロモーション費用の予算策定

商品やサービスのプロモーション費用の予算も、売上予測を参考にして決められています。あまり売上が見込めない商品にはプロモーション費用もそれほど多くは割り当てられません。逆に高い売上が見込める商品には相応の予算を割り当てるという具合です。

スタッフの採用や異動などの人事

スタッフの配置人数も、売上予測をもとに調整されます。売上の増加が見込まれる部門にはスタッフを増員し、逆に売上の減少が見込まれる部門は人員を削減するという具合です。

事業計画

事業計画を策定する際には、健全な経営を維持できるように無理のない内容にしなければなりません。無理のある目標設定をすると、従業員のモチベーションを下げてしまうリスクがあります。

そのためには、現実的かつ客観的な指標をもとにする必要があり、売上予測の数値が用いられます。

銀行や株主からの信頼

銀行や株主は、過去のデータに基づいた根拠をもとにして、融資や投資をするのにふさわしいかどうか判断しています。そのため、売上予測をもとにして事業計画を立てることが重要です。銀行や株主からの評価の向上につなげられるでしょう。

Excelで売上予測を作成する方法　その1：予測シートを使う

Excelの予測シートは、Excel2016から追加された機能です。予測シートを使えば、売上予測を作成できます。

Excelのバージョンが2016より古い場合には、2016以上へのアップデートが必要です。では、作成手順と精度を高めるコツについて見ていきましょう。

予測シートを使った売上予測の作成手順

ここでは、売上予測の作成手順を詳しく紹介します。

最初に表を作成して日付と売上のデータを入力しましょう。日付は1日単位で入力する方法と、1週間や1か月など、まとまった期間で入力する方法があります。

1日単位で入力する場合には、定休日などで売上がゼロになる日もあるでしょう。データがなくてゼロであれば、「0」と入力するのではなく空白にしておきます。

入力を終えたら、表の中にあるセルのうちのどこかひとつのセルをクリックして選択しましょう。どのセルでも問題ありません。その状態で画面上にある「データ」タブの「予測シート」のボタンをクリックします。

そうすると、「予測ワークシート作成」のダイアログボックスが出てきて、予測データのグラフのプレビューが表示される仕組みです。プレビューは折れ線グラフと棒グラフを切り替えることができます。

作成ボタンを押すと、新しいワークシートにグラフが表示され、表にも予測の数値が加えられます。

予測の精度を確かめるコツ

「予測ワークシート作成」のダイアログボックスの左下にある「オプション」をクリックしましょう。そうすると項目が展開されて、予測開始の日付を変更できるようになります。

他に信頼区間や季節性などの項目があるため、必要に応じて設定を変更しましょう。

信頼区間は、予測の範囲内に収まる確率です。デフォルトでは95％に設定されています。

信頼区間の数値を大きくすると、信頼上限と信頼下限の幅が広がります。これは予測範囲が広くなることを意味し、実績値がその範囲に収まりやすくなります。

逆に小さくすると信頼上限と信頼下限の幅が狭まり、実績値が範囲外になりやすくなります。また、信頼区間のチェックを外すと信頼上限と信頼下限は非表示になります。

季節性は「自動的に検出する」が選択されていると、土日や平日などのパターンが自動的に検出されて予測に反映されます。手動設定で数値を0にすると、パターンが反映されません。

Excelで売上予測を作成する方法　その2：代表的な計算式を使う

時系列分析法という大きな枠組みの中には、移動平均法や指数平滑法といった具体的な計算手法が含まれます。ここでは、時間軸データをもとに論理的かつ定量的に将来売上を推定する代表的な方法を解説します。

時系列分析

時系列分析とは、時間の経過に沿って蓄積された売上データから「長期的な傾向（トレンド）」「季節的変動（シーズナリティ）」「不規則変動（ノイズ）」を分解し、将来値を推定する手法です。

例えば月次売上データが36か月分ある場合、前年同月比の平均成長率や、月別平均指数を算出することで、傾向と季節性を数値化できます。

売上＝トレンド×季節指数＋誤差といった構造で整理することで、感覚ではなく計算根拠に基づく予測が可能になります。

Excelでは、折れ線グラフによる可視化、回帰分析、FORECAST関数などを組み合わせることで、定量的な将来推定が実行できます。

移動平均法

売上が比較的安定しており、大きな流れを把握したい場合は移動平均法が有効です。短期的な変動を平準化し、基調トレンドを抽出できます。

単純移動平均法は、直近n期間の平均値を次期予測値とする方法です。

例えば3か月移動平均なら「直近3か月売上合計÷3」で算出します。急激な変動を平滑化できる一方、直近データの変化が反映されるまでにn期間の遅れが生じます。

加重移動平均法は、直近データに大きな重みを置く手法です。

例えば直近から順に0.5、0.3、0.2の重みを設定し、加重合計で算出します。単純平均よりも変化への追随性が高く、需要が緩やかに変動する商品に適しています。

指数平滑法

流行の影響を受けやすく、直近の動きを即座に予測へ反映させたい場合は指数平滑法が適しています。

指数平滑法は、予測値＝前回予測値＋α×（実績値−前回予測値）という式で算出します。α（平滑化係数）は0〜1で設定し、0.3〜0.5程度が一般的です。αを大きくすると直近データへの感度が高まり、変動の激しい市場に対応しやすくなります。

移動平均法が一定期間の平均に基づくのに対し、指数平滑法は過去全データを指数的に減衰させながら考慮します。そのため、トレンド変化が早い商材では予測誤差を抑制しやすいのが特徴です。

Excelで売上予測を作成する方法　その3：関数を使う

予測シートを使用せず関数を使用して売上予測を求める方法もあります。主に次の関数が売上予測に使用できます。

TREND関数

TREND関数は重回帰分析を行える関数です。

引数が4つあり1番目から順番に既知のy・既知のx・新しいx・定数を入力します。既知のyは売上実績を指定し、既知のxは日付や年度などを指定するものです。新しいxは予測したい将来の日付や年度を指定します。いずれもセルの範囲で指定しましょう。

4番目の定数は、特定の得意先などで毎月同じ金額の売上が含まれている場合にTRUEを指定します。特にそのような事情がなければ、FALSEを指定しましょう。

FORECAST関数

FORECAST関数は回帰直線で売上予測ができる関数です。既知のxの範囲と既知のyの範囲、予測に使うxによって、yの値を予測します。

予測に使うxは予測したい将来の日付や年度を指定するものです。既知のyの範囲は売上実績の金額を指定し、既知のxの範囲は売上実績の日付や年度を指定します。

戻り値は、予測に使うxの時点での予測売上高です。

SLOPE関数

SLOPE関数は、回帰直線と傾きにより将来の売上予測を行える関数です。引数はyの範囲とxの範囲のふたつです。yの範囲は売上実績を指定し、xの範囲は日付や年度を指定しましょう。

GROWTH関数

GROWTH関数を使用すれば、指数関数的に増加する数値に関して将来の数値を予測できます。

引数は既知のy・既知のx・新しいx値の3つです。既知のyには売上データの範囲を指定し、既知のxには年度や月度の範囲を指定します。新しいx値は、予測したい対象の年度や月度です。

主に新商品の発売初期など、今後売上の増加が見込める場合に活用されます。

また、FORECAST関数が線形な成長を前提としているのに対して、GROWTH関数は指数関数的な成長を前提としている点が大きな違いです。

Excelで売上予測を立てるメリット・デメリット

Excelで売上予測を立てる場合のメリットとデメリットについてみていきましょう。

【メリット】手軽に運用できる

Excelはほとんどの企業ですでに導入されています。Excelを利用した売上予測なら、新しいツールを導入する必要がありません。新たなコストをかけずに簡単に始められるのがメリットです。

また、多くの方が普段の業務でExcelを使用しています。誰もが使い慣れており、新たに覚えなければならないことも限定的です。予測シートを使う方法も関数を使う方法も、もととなるデータさえあればすぐに売上予測を表示できます。

【メリット】導入費用がかからない

追加のライセンス費用が不要なため、予算が限られている企業でも導入しやすいのがメリットです。

【デメリット】データの保存に限界がある

精度の高い売上予測をするには、膨大なデータ量が必要です。データ量が少ないと、どうしても予測の精度は低くなってしまいます。しかし、Excelにはいくらでもデータを入力できるわけではありません。入力できるデータ量には限界があります。

無理に多くのデータを入力すると、ファイルが重くなるため、使っているとストレスを感じることも多いです。場合によっては、ファイルが破損して使えなくなってしまうこともあります。ファイルが重くならない程度のデータ量に留めておきましょう。

また、Excelで売上予測ができるとはいっても、専門のツールではないため、複雑な分析をする機能はありません。あくまで、計算式に機械的に当てはめて数値を算出するに留まります。

【デメリット】関数・統計の知識が必要

Excelで売上予測を立てるには、関数や統計の知識が必要不可欠です。一方でExcelそのものの使用には慣れていても、関数や統計の知識も習得している方はそう多くありません。

一部の従業員しか扱えない可能性が高く、全社的に導入するには難しいでしょう。

【デメリット】外部要因の反映が難しい

Excelでの売上予測は、過去のデータをもとにして計算するものがほとんどです。一方で、需要予測には、経済状況の変化や競合他社の動向などの外部要因も大きく影響します。季節性や消費者の嗜好の変化なども考慮しなければなりません。

Excelでは、そのような外部要因を反映した予測が難しいのがデメリットです。

売上予測の精度を高めるならSFAを使うのもアリ

Excelで売上予測をするにあたって、精度の面で不安がある場合や、重くて不便だと感じる場合には、SFAを導入するのがおすすめです。

SFAとは、営業活動を管理するためのシステムで、顧客や案件、商談などのデータを蓄積できます。さらに、入力したデータを自動的に集計してくれる機能もついており、Excelを使用するよりも効率良く数値の管理ができます。

売上予測にSFAを活用する場合は、「GoCoo! SFA」をご検討ください。GoCoo!は、日本企業の営業現場向けに設計されたSFAで、Excelのように直感的にデータ入力・編集ができます。ノーコードでの設定・カスタマイズにも対応しており、現場の運用にフィットしやすい仕様です。

営業データを入力するだけでパイプラインや予実状況などをダッシュボード上に     反映できます。また、案件進捗や放置案件の可視化を通じて、日々の営業活動の把握と管理が容易になります。

導入後は専任担当が支援し、操作性や運用定着をフォローします。現状の運用に合わせたフィット設計や導入前の無料デモも提供されるため、導入ギャップを抑えた立ち上げが可能です。

売上予測業務の効率化と精度向上を図りたい企業様は、以下よりお問い合わせください。

＞＞お問い合わせ

まとめ

売上予測はExcelの予測シートやTREND・FORECAST・SLOPE関数などを使って行えます。いずれも特に難しい点はありません。必要なデータが入力されている表があれば簡単に売上予測を表示できます。Excelを使用する方法ならコストがかからないのもメリットです。

一方で、正確なデータを収集しにくい、膨大なデータを集めると使い勝手が悪くなるというデメリットもあります。

Excelでの売上予測が非効率になってしまう場合は、SFAの活用も検討してみましょう。