Excelで、購買期間と購買間隔を月別売上予測表に反映させる方法について

有識者の皆様何卒ご教示ください。
よろしくお願いいたします。

画像を添付しておりますが、当該画像にありますように「平均購買期間（お客様が平均して利用してくれる期間＝失客までの平均期間）」と「平均購買間隔（お客様の購買間隔＝美容院のように3ヶ月毎に髪を切りに行くというイメージです）」を月別の売上予測表にリンクさせたいのですが、セルに入力する式はどのようなものを使えばいいでしょうか？

何卒ご教示のほどよろしくお願いいたします。

No.1 ベストアンサー 回答者： mygoonickname 回答日時： 2021/11/22 09:11

おはようございます。

13カ月、3か月ごと
この数字を使って、セルの8行に関数で反映したいという事でしょうか？
今、セルC8、F8などに、6800と入っていますが、この数字は正しいと
思って回答します。

セルC8に、下記関数を入れて、
=IF(AND(COLUMN()-2<=$C$2,MOD(COLUMN()-3,$C$3)=0),$C$4,"")

C8セルをコピー、D8からO8へ貼り付けて、どうでしょうか？

No.2 回答者： goomania 回答日時： 2021/11/22 18:21

ご質問者から提示された条件ですと、単月売上予測は可能ですが、「平均購買期間（お客様が平均して利用してくれる期間＝失客までの平均期間）」に基づいて、顧客数が減少するという仮説でない限り、毎月同じ金額の予測値になると思います。

添付画像をご覧ください。理論的には、13カ月後には顧客がいなくなるという仮説もあり得ますが、それを阻止するために、(当然、それなりの費用をかけて)様々なマーケティング活動が行われているのが現実です。
そのため、「平均購買期間（お客様が平均して利用してくれる期間＝失客までの平均期間）」が13カ月だとしても、旧顧客が離れると新規顧客が現れるというのが普通です。その仮説「顧客数維持仮説」で考えると、平均顧客数は50人ですので、仮に「顧客01、顧客02、顧客03、･･･」というお客様が各人3か月ごとに単価6800円の商品(サービス)を購入するものとすると、添付画像下部の図のようになり、これを年間合計すると136万円の売上が発生することになります。

添付画像下部の図の各月売上合計をそのまま、上部の表の「単月売上」に転記していしまうという手もあるかも知れませんが、単価はあくまでも平均単価ですし、顧客数も平均顧客数ですので、年間売上を12分の1にして単月売上として計上することにします。

顧客１人あたりの年間売上は「平均単価×年間利用回数」で計算されますので、
顧客１人あたりの年間売上
=平均単価×年間利用回数=6800(円)×12(月)/平均購買間隔(月)=6800×4=27200(円)
年間総売上は「顧客１人あたり売上×顧客数」で計算されますので、
年間総売上
=27200(円)×50(人)=1360000(円)
単月売上は上記の12分の1なので
単月売上=1360000/12=11333.3･･･≒11333円となります。

従って、C8セルに
=$C$4*(12/$C$3)*$C$5/12
を記述すればよいことになりますが、この数式は整理すると

=$C$4/$C$3*$C$5

という数式になることが判ります。これを右方向へコピーすればよいと思います。
また、累積売上はC9セルに

=SUM($C$8:C8)

という数式を記述し、右方向へコピーすればよいと思います。
これが添付画像の「顧客数維持仮説」の行になります。

なお、平均顧客数50人が現在の数値という前提で「平均購買期間」に基づい顧客数が減少し、新規顧客は現れないという仮説で計算することもできます。つまり、13カ月後には顧客がいなくなり、売上は0になるという仮説です。これが、顧客数減少仮説です。
添付画像「顧客数減少仮説」の行をご覧ください。C10セルに

=$C$4/$C$3*$C$5*($C$2-COLUMN(A1)+1)/$C$2

という数式を記述し、右方向へコピーすればよいと思います。
また、累積売上はC11セルに

=SUM($C$10:C10)

という数式を記述し、右方向へコピーすればよいと思います。

ただ、このような仮説はあくまでも仮説であり新規顧客が現れないというのは強引な仮説のような気がします。

顧客が減少する割合が判っているだけでは、片手落ちであり、新規顧客がどのくらいの頻度で現れるかが判らないといけないということかも知れません。
「平均購買期間（お客様が平均して利用してくれる期間＝失客までの平均期間）」はLTVの計算には登場しますが、単月売上予測に使用する数値としては扱いにくい数値ということになるのかもしれません。