チャンドラです。
エクセルにはデータから将来の値を予測する便利な機能があります。
今回は「家の近所にある貸店舗に小型スーパーを出店したらどれくらい売り上げることができるのか」を予測してみました。
この記事がエクセルでデータ分析したい方の参考になれば幸いです。
売上に関係しそうなデータを集める
まずは売り上げに影響しそうな既存店舗のデータを集めます。ここでは次の項目が売り上げに影響すると考えます。
- 半径2km以内の人口
- 駅からの距離
- 店舗面積
- 食品の種類
- 生活用品の種類
- 1日の営業時間
- 駐車場の台数
他の200店舗のデータを集めると下の表のようになりました。
相関分析で売上に関係する項目を絞る
7項目の中から売上に影響する項目を絞ります。これはエクセルの相関分析機能を使えばすぐにできます。
エクセルで「データ」➡「データ分析」を選ぶと次のようなウインドウが出ます。
ここで相関を選んで「OK」をクリックするとデータを選ぶ画面が出ます。分析したいデータを選び出力すると次のような結果を得ます。
「月間売上」の下に表示された数字は各項目との相関係数を表しています。
相関係数の絶対値が大きいほどその項目は売上と相関が大きいといえます。一方、相関係数がゼロに近いほどその項目は売上と相関が小さいといえます。
相関の大小を判断する基準として一般的に相関係数0.4を考えます。すると売上との相関が大きい項目は、相関係数が0.4よりも大きい「駐車場の台数」「半径2km以内の人口」「店舗面積」の3つに絞られます。
従って売上に大きく影響するのは「駐車場の台数」「半径2km以内の人口」「店舗面積」の3つで、他の「駅からの距離」や「食品の種類」などは影響しないことが分かりました。
重回帰分析で売上を予測する
売上に影響する「駐車場の台数」「半径2km以内の人口」「店舗面積」から売上を予測するための式を導出します。
まずは下のような表を作成します。売上に影響しない4項目を除いた表です。
相関分析をしたときと同様に「データ」➡「データ分析」を選び、「回帰分析」を選びます。
そしてデータを選択し、結果を出力すると次のような結果を得ます。
色々な数字が表示されていますが、予測に必要なのは「係数」の部分です。この係数を用いて今回出店しようと計画している店舗の売上を予測します。
出店を計画している店舗の情報は次の通りです。
半径2km以内の人口：15,626 人
店舗面積：540 m2
駐車場の台数：6台
各々の項目に係数をかけ、最後に切片を加えれば予測値を求めることができます。
予測売上 = 15,626 (0.0018) + 540 (0.1643) + 6 (4.9332) – 8.2641
よって新規店舗の月間売上予測は138 [百万円]と予測することができました。
まとめ
エクセルを使えば既存のデータから予測することができます。
ステップは次の通りです。
① 予測したい項目に影響しそうなデータを集める
② 相関分析で項目を絞る
➂ 重回帰分析で予測する
それでは。
プロコンサルタントの最強エクセル術 ごく簡単なテクニックだけで、武器になる判断力を手に入れる
私の現在のブログランキングはこちらです：
