Excelで競合店とのシェア分析をし、売上高予測をする方法
新たに、最寄品を扱うお店、例えばコンビニを開店する場合に、想定される競合店とのシェア分析を行い、売上高の予想を、Excelにて、ハフモデルを用いて、求めてみましょう。この方法は、サービス業のクリーニング店や介護サービス施設などの開業にも使用できます。
この方法の重要なポイントを３つあげます。
1.家から1歩も出なくてもできること。
2.タダでできること。
3.誰が計算しても同じ結果が得られること。
つまり、誰でも、いつでもできて、誰からも納得されやすいことです。
まず、ハフモデルを簡単に説明します。
ハフモデルは、アメリカの経済学者ハフ博士が1960年代に発表したモデルで消費者が、ある店舗に買い物に出かける確率（出向比率）を算出します。ある店舗を選択する確率を店舗の規模（売場面積）に比例し、消費者から店舗までの所要時間に反比例するものとしています。ハフモデルは次の式で表されます。
ここで、λは距離の抵抗係数で、最寄品では大きくなり、買回品では小さくなります。産業研究所(1979)による経験データに基づくλの推定値を表１に示します。
表1 係数λの推定値
扱う品目によりλが異なるため、使いづらく旧通産省（現在の経済産業省）は次のようにモデルをアレンジしました。
係数λを2に固定しました。また、店舗までの所要時間を直線距離に入れ替えました。これを修正ハフモデルと呼びます。これで、使いやすくなりました。図１に修正ハフモデルの簡単な事例を紹介します。
今回は、修正ハフモデルを用いて、分析しますが、パラメータλの値を変更して計算することは簡単にできます。また、店舗の魅力として、売場面積を使用しますが、駐車場の面積を加味したり、商品の魅力度を加味することも簡単にできます。
STEP1から8まであります。順番に実施してください。
それでは、始めましょう。
（注意）このNoteでは、政府統計の総合窓口e-Statとその関連サービスである、地図で見る統計（jSTAT MAP)にログインしている前提で説明しています。そのログイン方法については、別のNoteにて説明します。
STEP1 jSTAT MAPで商圏範囲（今回は、仮に500mとします）の2倍（1km）の円を描きます。
① 「統計地図作成」ボタンをクリックすると、サブメニューが表示されます。
② 「エリア作成」を選択すると、「エリア作成」画面が表示されます。
③ 「新規グループ」をチェックし、任意のグループ名を入力して「次へ」ボタンを押してください。
④ エリアの作成方法を選択します。「円」ボタンをクリックすることでプルダウンリストがでますので、半径指定円をクリックします。
⑤ メイン地図上に「半径指定円」画面が表示されます。任意のエリア名、半径(km単位、今回は1.0)を入力し、地図上で出店予定地をクリックします。
円が描かれますので、「登録」ボタンをクリックします。
STEP２ Google Mapにて開店予定地周辺のコンビニを検索します。そして、STEP1で求めた1km円内にあるコンビニ（競合店）を特定します。ここでは、仮に4店の競合店が見つかったとします。
STEP3 ExcelでjSTAT MAPにインポートする表を準備します。
STEP4 Google Mapにて特定した競合店の住所と緯度、経度、店舗面積を求め、Excelに入力します。
① Google Map上で、競合店を左クリックします。住所が表示されますので、それをコピーして、Excelの表に貼り付けます。
② Google Map上で、左下のボタンをクリックして、航空写真に変更し、右クリックすると次のメニューが出ます。一番上にある、緯度、経度を左クリックして、コピーして、Excelに貼り付けます。
③ 「距離を測定」を左クリックすると、次の表示が出ます。
④ 店舗の角を順番に、左クリックし、店舗建物を囲みます（①⇒②⇒③⇒④⇒①と左クリック）。すると、総面積が表示されますので、その数字をコピーしてExcelに貼り付けます。
⑤ 入力されたExcelファイルは次のようになります。
STEP5 jSTAT MAPで出店予定地と競合店の商圏エリアを見える化します。
① メニューバー上の「ファイル」メニューから「インポート」を選択すると、「インポート」画面が表示されます。
② 「住所マッチング」をクリックします。
③「住所マッチングインポート」画面で「参照」ボタンをクリックして、STEP4で作成したExcelファイルを指定します。表示するアイコンを選択する場合は、アイコンのプルダウンボタンで選択できます。
④ 「インポート」ボタンをクリックするとファイルのアップロードが開始されます。
⑤ 完了すると取り込んだ結果が「住所マッチングインポート」画面に表示され、全件成功したことを確認し、「閉じる」ボタンをクリックすると、地図上にプロットが登録されます。
⑥ STEP1で説明しました、「統計地図作成」の「半径指定円」で、商圏範囲とする半径0.5km（500ｍ）と設定して、プロットされた地点をクリックして円を描きます。
⑦ 「エリア作成」画面で、新しくエリア名を設定し、「色の設定」ラベルをクリックすると、下図の様な入力欄が表示されます。ハッチ色、線色のラベルをクリックするとパレットで色が選択できます。ハッチパターンや線種、線幅も変更できます。
⑧ 自店の商圏を赤で、競合店の商圏を緑で表したものは次の図になります。これで、商圏の重なり具合がよくわかります。
STEP6 商圏内の世帯数を調べます。
① 「統計地図作成」をクリックします。続いて、「統計グラフ作成」をクリックします。すると、次の「統計グラフ作成」画面が表示されます。
② 「調査名」は「国勢調査」を選択します。「年」は最新の「2020年」を選択します。集計単位は一番細かい集計単位である「5次メッシュ（250ｍメッシュ）」を選択します。統計表は「人口及び世帯」を選択します。すると、右側の「指標/データ」欄にチェックボタンが表示されます。
③ 下に送り、「世帯総数」にチェックを入れ、「指標選択」ボタンをクリックします。すると、「選択/データ」欄に追加されます。
④ 「次へ」ボタンをクリックします。次の「統計グラフ作成」画面が表示されます。
⑤ 「集計単位」は「エリア」を選択。「集計範囲」は「画面表示範囲」を選択すると、「既存エリアグループ一覧」に登録しているエリアの一覧が表示されるので、STEP5で登録した、自店の500ｍ商圏のエリア名を選択し、「集計方法」は「按分する」を選択します。そして「集計開始」ボタンをクリックします。
⑥ 「グラフ集計完了」画面が表示されたら、「OK」ボタンをクリックします。
⑦ 「統計地図作成」をクリックし、レポート作成をクリックします。すると、次のレポート作成画面が表示されます。
⑧ 「シンプルレポート」をクリックすると、下図の「シンプルレポート作成」画面が表示され、作成したグラフの情報が一覧に表示されます。グラフを選択し、「Excelレポート作成」ボタンをクリックします。
⑨ すると、「レポート作成」画面が表示され、「処理を終了しました」のメッセージと「ダウンロード」ボタンが表示されますので、ボタンをクリックして、ファイルをダウンロードします。
⑩ ダウンロードされたレポート（Excelファイル）を開くと、下の地図と共に、世帯に関する表があります。この表には、商圏範囲の世帯総数4,300とメッシュコードエリア毎での円のラインでの按分率（集計率）と按分された世帯数が得られます。参考までに、「按分しない」を選択して、統計地図を作成したときの図からも、エリアごとの按分の度合いがチェックできます。
STEP7 シンプルレポートのExcelファイルを使用して自店、競合店と各メッシュの中央地点との距離を求めます。
① 緯度、経度の欄を追加し、メッシュコードから緯度、経度を算出します。
セルD52に「=(LEFT(A52,2)/1.5*3600+MID(A52,5,1)*5*60+MID(A52,7,1)*30+((IF(VALUE(MID(A52,9,1))<3,0.5,2.5)+IF(VALUE(RIGHT(A52,1))<3,0,1)))*7.5)/3600」と入力し、それをセルD53:D69にコピーペーストします。
セルE52に
「=((MID(A52,3,2)+100)*3600+MID(A52,6,1)*7.5*60+MID(A52,8,1)*45+(IF(ISODD(VALUE(MID(A52,9,1))),0.5,2.5)+IF(ISODD(VALUE(RIGHT(A52,1))),0,1))*11.25)/3600」と入力し、それをセルE53:E69にコピーペーストします。
（注意）今回のシンプルレポートのメッシュコードは18個でしたので、レポートの行番号は52から69となりましたが、他の地点や商圏のサイズを変更すれば、当然、行番号は変わります。適宜、行番号を変更して対応してください。以下の記述も同様です。
② STEP4 ⑤で作成した店舗データファイルのセルA1:E6をコピーし、シンプルレポートファイルのセルE47に縦横を入れ替えて貼り付けます。
③ 各店舗と各メッシュ（約250m四方）の中央地点との距離（単位：m)を計算します（下図を参照）。
セルF52に「= SQRT(POWER((F$49 - $D52) *110941, 2) + POWER((F$50- $E52) *91288, 2))」と入力し、F53:F69にコピーペーストします。F52:F69をG52:J69にコピーペーストします。
これで、各店舗と各メッシュエリアとの距離を算出することができました。
STEP8 修正ハフモデルを使用し、売上高を予測します。
① STEP7の表の下に次の表を作成します。メッシュコード、世帯総数はそれぞれ、セルA51:A69とC51:C69をコピーペーストします。ここで、修正ハフモデルを使用しますのでセルC72には2を入力しますが、係数λに他の値を使用したい場合は、この値を変更すれば、自由に再計算することができます。
② セルD74に「=F$51/F52^$C$72」と入力して、D75:D91にコピーペーストします。D74:D91をE74:H91にコピーペーストします。
セルC74に「=SUM(D74:H74)」と入力して、C75:C91にコピーペーストします。
③ 上の表の下に次の表を作成します。表タイトルを「買い物出向比率」とし、メッシュコードと世帯総数はそれぞれ、セルA51:A69とC51:C69をコピーペーストします。
④ セルC96に「=D74/$C74」と入力して、C97:C113にコピーペーストします。C96:C113をコピーし、D96:G113にペーストします。
セルH96に「=SUM(C96:G96)」と入力して、H97:H113にコピーペーストします。
C96:H113を選択して、パーセント表示とします。
合計欄が100%となっていることを確認します。
これで、各メッシュコードで表された地域に住んでいる世帯の方々がどのコンビニにどのくらいの確率で出向くかがわかります。
例えば、先頭のメッシュコード「5236572244」の世帯の方々は自分の新しいお店に34%の確率で出向くことがわかりました。
⑤ 上の表の下に次の表を作成します。表タイトルを「買い物出向世帯数」とし、メッシュコードと世帯総数はそれぞれ、セルA51:A69とC51:C69をコピーペーストします。
⑥ セルC117に「=C96*$B117」と入力して、C118:C134にコピーペーストします。C117:C134をコピーし、D117:G134にペーストします。
セルH117に「=SUM(C117:G117)」と入力して、H118:H134にコピーペーストします。
合計欄が世帯総数と同数になっていることを確認します。
セルB135に「=SUM(B117:B134)」と入力して、C135:H135にコピーペーストします。
C117:H135を選択して、「セルの書式設定」で「表示形式」を「数値」を選択し、小数点以下の桁数を0に、「桁区切りを使用する」にチェックを入れます。
これで、各メッシュエリアから買い物に来られる世帯数が求められました。
半径500mの商圏内には4,298世帯ありますが、その中の1,836世帯が自店へ買いに来られると求められました。
⑦ e-Statでトップページのキーワード検索欄に「全国家計構造調査 コンビニ 名古屋市 総世帯」と入力し、最新の2019年の調査結果をExcelでダウンロードします。
名古屋市全体で、総世帯平均で、コンビニでは、1世帯当たり、1ヶ月4,157円支出していることがわかります。
⑧ 売上高予測（単位：万円）のセルを作成し、世帯数合計と1世帯当たり月購入額をかけて算出します。セルC139に「=C135*C137/10000」と入力します。
結果、月763万円の売上高が予測できました。
参考文献
[1]産業研究所、「大型小売店の出店によるその周辺の中小小売店への影響メカニズムに関する研究」、産業構造調査研究報告書、53-3，1979
[2]「1990年度大規模小売店舗法の解説」、通商産業省産業政策局流通産業課（編）、1990
[3]「地図で見る統計（jSTAT MAP）操作説明書」第2版、総務省統計局統計センター、令和5年1月
この記事が気に入ったらサポートをしてみませんか？