廿TT

譬如水怙牛過窓櫺 頭角四蹄都過了 因甚麼尾巴過不得

バスモデル:エクセルによる需要予測

バスモデルを使って需要予測ができるエクセルシートを以下に公開しました。

http://zisatsu.web.fc2.com/monooki/excel/Bass_model.xlsx

使い方

データの列に予測したい対象のこれまでの販売数を入れます。

f:id:abrahamcow:20170521032218p:plain

今回は例として トヨタ自動車、ハイブリッド車のグローバル累計販売台数が900万台を突破 | トヨタグローバルニュースルームハイブリッド車販売台数を使っています。

「予測値」、「行番号」の列はオートフィルとか使って、予測したい先までの数字を入れてください。

「年」の列は「月」でも「四半期」でもかまいません。

今回は6期先までの予測を行っています。

「二乗誤差」の列はデータの行数にあわせてください。

「残差平方和」のところは、「二乗誤差」の総和になるように範囲を選択してください。

次に、「データ」タブから「ソルバー」を起動します。

「目的セルの設定」に「$F$3」、「変数セルの変更」に「$G$3:$I$3」を入力し「制約のない変数を非負数にする」にチェックを入れたら、「解決」をクリックします。

無事「ソルバーの結果」が出たら「OK」をクリックしてください。

f:id:abrahamcow:20170521020421p:plain

最適なパラメータが推定され、予測値が出てきます。

f:id:abrahamcow:20170521020608p:plain

バスモデルとは

バスモデルは微分方程式に基づくモデルです。

微分方程式」なんていうと難しそうなイメージがありますが、 微分とは「すごく短い時間で関数がどれくらい変化するか」ということです。

データの外形に当てはまる曲線を闇雲に探すのではなく、「どのように変化しているか」というメカニズムを想像する点が微分方程式の醍醐味です。

ぼくの考えでは、エクセルの「多項式近似」は多くの場合「データの外形に当てはまる曲線を闇雲に探す」方に入ります。

ためしに同じデータで4次の多項式を使って予測値を出してみます。

f:id:abrahamcow:20170521022515p:plain

2021年の販売台数がマイナスになっており、予測としては不自然です。

一方、バスモデルでは「どのように変化しているか」に次のような仮説を置きます。

\displaystyle \frac{dF(t)}{dt}=a(m-F(t))+\\~~~~b\frac{F(t)}{m}(m-F(t))

t は時間です。F(t) は商品の累積販売数に対応する関数です。

左辺は微分です。時間 dt での関数の増加量 dF(t) を割ったものなので、変化率を表します。

右辺第一項の m は商品の販売限界を表すパラメータです。a は比例定数です。

つまり第一項は「これから買う人 m-F(t) がたくさん残っているときには売れやすく、m-F(t) が上限 m に近づいてくると売れにくくなる」というメカニズムを表現しています。

第二項は第一項と同じ m-F(t) にこれまでに売れた分の販売限界に対する割合 F(t)/m をかけ算しています。b は比例定数です。

これは「商品が売れるにつれ話題になってもっと売れやすくなる」というメカニズムを表現しています。

この微分方程式を解くと、

\displaystyle F(t)=m(1-\exp\{-(a+b)t\})/\\~~~~(\{b/a\}\exp\{(-(a+b)t\}+1)

という関数が出てきます。

その微分形を閉じた形で書くと、

 f(t)= (ma \{(a + b)^2\}\exp\{(a + b)t\})/\\~~~~((b + a\exp\{(a + b)t\})^2)

となります。

F(t) は商品の累積販売数に対応するので、f(t) は単位時間(上の例では1年)あたりの商品の販売数に対応します。

公開したエクセルファイルでは、この f(t) をデータに当てはめています。

注意点

バスモデルは一人のユーザーが何度も繰り返し購買することは仮定していないため、消耗品や日用雑貨等の需要予測には不向きです。

パラメータの推定方法

パラメータの推定には「最小二乗法」を使っています。

「最小二乗法」とはなにか、ざっと説明します。

t 時点のデータを y_t で表すと、当てはめたい関数 f(t) とデータの差は、

y_t-f(t)

です。

これを残差といいます。

残差をすべての時点について足し合わせた値が小さくなるようにパラメータを探せば、当てはめのいいパラメータが求まりそうです。

ただ、残差は関数が上にずれればマイナスに、下にずれればプラスになるので、両方で打ち消し合って、データからすごくずれているのに残差の合計は小さくなることがありえます。

そこで残差を二乗して、正の値しかとらないようにします。

残差を二乗したものの総和を残差平方和と呼び、残差平方和が小さくなるようにパラメータを探すことを最小二乗法といいます。

実際に残差平方和が小さくなるようにパラメータを探すプロセスはエクセルのソルバーがやってくれます。

関連エントリ

abrahamcow.hatenablog.com

アマゾンアフィリエイトのコーナー

EXCELビジネス統計分析[ビジテク] 第3版 2016/2013/2010対応

EXCELビジネス統計分析[ビジテク] 第3版 2016/2013/2010対応

EXCELグラフ作成 [ビジテク] データを可視化するノウハウ 2013/2010/2007対応

EXCELグラフ作成 [ビジテク] データを可視化するノウハウ 2013/2010/2007対応

データサイエンティスト養成読本 登竜門編 (Software Design plus)

データサイエンティスト養成読本 登竜門編 (Software Design plus)

微分方程式で数学モデルを作ろう

微分方程式で数学モデルを作ろう