AI

エクセルで出す売上予測|関数を使う方法や「予測シート」を活用するなど詳しく解説

企業が事業に関するさまざまな計画を立て、成長していくためには現在の売上高だけに注意を払うだけではなく、将来の売上まで予測する必要があります。売上予測に使えるツールはいくつかありますが、エクセルもそのひとつです。この記事では、エクセルで売上予測を算出する方法を複数紹介しているため、ぜひ参考にしてください。

動画で3分で解説

「需要予測×AI」のメリットとは?

「需要予測×AI」のメリットとは?

需要予測とは?AIを活用するメリットと、AIを導入するために必要な事前準備について解説します。

売上予測とは?

売上予測は漠然とした目標や期待値ではなく、客観的なデータに基づく売上の予測です。主に前年度同月や直近数カ月の売上データなどが用いられますが、実際にどのデータを使用するのかは状況や職種などによって異なります。

売上予測を立てるメリット

ここでは、売上予測を立てることで、企業がどのようなメリットを得られるのかを解説します。具体的には以下の2点です。

1.生産量や在庫の最適化ができる

売上予測を正確に立てられれば、生産量や在庫管理を最適化できます。売れ筋商品を在庫切れにして、せっかくの販売機会を逃すこともないでしょう。逆に需要が少ない商品を過剰に抱えることがなくなり、廃棄ロスも防げます。

2.業務効率化につながる

どのくらいの生産量や在庫が最適なのか、ベテラン社員なら経験である程度把握できるかもしれません。ただ、それゆえに発注業務などは属人化しがちです。売上予測をもとに発注量を自動算出できれば担当者の負担が減り、業務効率化につながります。

関連記事: エクセルで売上予測をする方法とは?

売上予測を立てる際に用いる計算式

売上予測を立てるための具体的な分析方法は複数あります。何を目的として売上予測を立てるのか、目的に応じた方法を選ぶことが大事です。

1.時系列分析法

時系列分析は文字通り、時間の経過に沿ってデータを分析する方法です。使用する手法は4つあり、季節的な要因が含まれる場合なら季節手法、含まれない場合は非季節手法が適しています。ほかにも自己回帰和分移動平均モデル(ARIMA)と重回帰分析があります。時系列分析法は特に長期にわたるデータを保有しているケースに向いています。

2.移動平均法

移動平均法は過去の売上の推移をもとに、対象とする時点の予測値を算出する方法です。一般的には前年度の売上実績を平均し、次期の予測値を出します。加重移動平均法と単純移動平均法の2種類があり、計算方法が異なります。

加重移動平均法

加重移動平均法は移動平均法の一種ですが、対象となる期間の値を同等に扱わないことが特徴です。直近のデータの方に重みを持たせ、過去になるにしたがって重みを減少させつつ平均値を算出します。つまり、古い需要の影響は抑え、最新の傾向を多く反映させた予測です。予測値は各月の売上高に加重平均の係数を掛けて求めます。

単純移動平均法

単純移動平均法も移動平均法の一種です。ただし、加重移動平均法のように各データに重みを持たせることはせず、過去のデータも直近のデータも同一に扱います。過去の1カ月や1年間の売上高などを全部足し、日数や月数などで割って次期の予測売上高を算出します。データに重みを持たせることをしない分、計算は簡単で分かりやすい方法です。

3.指数平滑法

指数平滑法は過去の予測と実績を用い、次期の売上予測値を導き出す方法です。次期の予測値は「α×前回実績値+(1-α)×前回予測値(αは平滑化定数)」で算出され、前回の予測値から実績値がどれだけ外れたかも加味されます。過去になるにしたがって重みは指数関数的に減少し、直近のデータに重きがおかれます。発注量予測など、比較的短期の予測に適した手法です。

エクセルで月別の売上予測を立てるには

エクセルにはデータに次に続くものが予測できる規則性を与えておくことで、予測値を導き出す機能があります。ここからはエクセルの連続数値予測機能の使い方を解説します。

1.売上の季節変動率を求める

正確な売上予測を求めるためには季節変動を考慮する必要があります。季節変動率を求めるために、まずはエクセルで過去の月次売上推移表を用意してください。より正確な予測値を出すためには、少なくとも3期分のデータが欲しいところです。

AVERAGE関数で各月の平均売上高を求めたうえで、SUM関数を用いて平均売上高の年間合計値を出します。さらに、「各月の平均売上高÷合計値の合計×100」で季節指数を求めましょう。この際、列番号と行番号には絶対参照となるよう「$」を付けておきます。

2.予測値を算出する

過去分の売上推移データをすべて一列に並べます。年月と売上高の間に列を挿入し、連番を振っておいてください。連番と過去分の売上推移データを選択した状態でポインタを右下のセルのフィルハンドルに合わせ、「+」になったら次年度1年分のセルまでドラッグして連続数値予測機能による売上予想値を算出します。

さらに、下のセルには次期の年間予測合計値も出しておきます。次期の各月売上予測値が入った列のさらに右列のセルには、「年間予測合計値×各月の季節指数」となるよう関数を入力し、季節変動率が反映された売上予測を算出します。

エクセルでの売上予測【1】関数を行う方法

さまざまな分析が行えるエクセルの関数のうち、売上予測に活用できるTREND関数とFORECAST関数、SLOPE関数を使う方法を紹介します。

1.TREND関数を使う方法

複数のデータをもとにして売上高を予測したいときに有効なのがTREND関数を使った重回帰分析です。例えば、広告を出した回数やイベントを行った回数をもとに売上高を予測したり、気温や降水量などの気象条件が来客数や売上高に影響するのかなどを予測したりできます。

既知のyおよび既知のxと、予測に使う新しいxに入力するデータの範囲を指定して求める方法です。求めたい箇所のセルを選択後、「Fx」をクリックし、「=TREND(既知のyの範囲,既知のxの範囲,予測に使う範囲)」の数式に当てはめて求めます。なお、xとyの関係が直線的であるときに有効な方法です。

2.FORECAST関数を使う方法

FORECAST関数は過去のデータから回帰直線で次期の売上高などを予測する方法で、予測に使うxとyが直線的な場合に有効です。予測値を算出するときは、既知のxと既知のyの値、予測に使うxを使い、yの値を予測します。

例えば、既知のxを過去の年度、既知のyを過去の年度に対する売上高、予測に使うxを次年度(複数期も可能)とし、次年度の売上高yを求めたいときなどです。「=FORECAST .LINEAR(予測に使うxのセル,yの範囲,xの範囲)の数式に当てはめて求めます。

3.SLOPE関数を使う方法

SLOPE関数は既知のxとyのデータをもとに、回帰直線と傾きを求めることで売上予測を行います。回帰直線は「y=a+bx(bが傾き)」で表されます。TREND関数およびFORECAST関数と同様に、xとyの関係が直線的である場合に有効です。

例えば、過去の年度をxの範囲、対応する年度の売上高をyの範囲として指定すれば、平均的な売上高の伸び率を求められます。SLOPE関数を使って売上予測を算出するときは、「= SLOPE(yの範囲, xの範囲)」の数式に当てはめます。

エクセルでの売上予測【2】「予測シート」を活用する方法

エクセルでは予測シートを使っても売上予測を立てられます。次の段落では、予測シートを活用する方法を紹介します。

1.「予測シート」とは

エクセルはバージョンが変わると使い勝手が変わったり、新しい機能が加わったりします。2016バージョンからは予測シートが新機能として加わりました。マクロの知識も特に必要とせず、簡単に売上予測に活用できます。予測シートはエクセルに入力されたデータをもとに過去の数値はもちろん、将来の予測値と予測グラフの生成が可能です。

2.予測シートで売上予測値を出す方法

日付のセルを設定し、すでに実績値が出ている月や日付を入れ、その横の列(または下の行)に対応する売上高を入力しておきます。表は縦・横どちらで作成しても可能です。

入力したデータ内のどこかのセルにポインタを置いた状態で、エクセルの『データ』タブから「予測シート」を選んでクリックすると、「予測ワークシートの作成」画面が開きます。右下にある「作成」ボタンを押せば、新しいワークシートに予測データの表と予測グラフが生成されます。

3.作成したグラフは他のシートへの貼り付けも可能

予測ワークシートの作成画面では折れ線グラフと棒グラフの切り替えができるほか、予測終了の日付を変更することで予測期間の切り替えも可能です。また、通常の手順で作成したグラフと同様に移動もでき、デザインの変更も難しくありません。グラフを選択した状態でコピーし、他のシートはもちろんワードやパワーポイントにも貼り付けできます。

なお、エクセルテンプレートを活用すると、売上予測だけでなく売上管理も可能となります。エクセルテンプレートを使った売上予測については、以下記事をご一読ください。

関連記事: 売上予測をエクセルやテンプレートで行う方法とは?

売上予測はAIの活用がトレンドに

扱う数値が少ない場合はさほど負担になりませんが、生産品目が数千種類にもなるとエクセルでの予測には限界があります。そこで活用が進んでいるのがAIです。AIを使えば膨大な品目でも正確な予測ができます。過去の売上高はもちろん、天候や顧客情報に至るまで、さまざまなデータを活用しつつ、売上の最適化が図れるでしょう。

関連記事: 出荷数予測の立て方は?安定した生産管理を実現するポイント3つ

まとめ

売上予測は客観的なデータに基づいて算出されるものであり、単なる期待値や曖昧な目標ではありません。分析方法は複数あり、エクセルを活用すれば特別なマクロの知識がなくても売上予測を求められます。

しかしながら、より正確な売上予測を行いたい場合、売上予測に工数をかけたくない場合にはAIの活用がおすすめです。売上予測は「予測分析」というAIの手法によって行うことが可能です。

下記資料では、予測分析を行うことでビジネスにどんなメリットがあるのか、導入事例を交えながらご紹介していますのでぜひご覧ください。

パンフレットをダウンロード

Prediction One資料パック

AI予測分析ツール「Prediction One」の概要やAI導入のメリットをまとめました。
■3つの資料わかること
 ・Prediction Oneとはどんなツールなのか?何ができるのか?
 ・予測分析をビジネス適用することによるビジネスメリットは?
 ・Prediction One導入企業の導入事例、ROI計算例

関連コラム

このコラムに関連する製品

このコラムに関連する
導入事例

エクセルで出す売上予測|関数を使う方法や「予測シート」を活用するなど詳しく解説

SHARE
シェアシェア ポストポスト
エクセルで出す売上予測|関数を使う方法や「予測シート」を活用するなど詳しく解説
SHARE
ポスト シェア