【季節変動を移動平均で補正する】適正在庫シミュレーション by Excel VBA
今まで行ってきた在庫シミュレーションでは、2か月分の需要データから平均と標準偏差を求め、その値から安全在庫と需要予測在庫を計算して固定していました。
そして、その固定した安全在庫と需要予測在庫を足して在庫補充目標量を決め、その後2か月間はその在庫補充目標量を目指して発注量を決めることによって、適正在庫を維持するシミュレーションを行っていました。
つまり下図のように、1～2月の需要データから在庫補充目標量を決め、それを3～4月のシミュレーションに使っていたわけです。
在庫補充目標量＝安全在庫＋需要予測在庫
＝√(N+M)＊標準偏差＊安全係数＋(N+M)＊1日あたりの平均需要
N：発注してから在庫が補充されるまでのリードタイム（日）
M：発注間隔（日）
適正在庫を維持するための発注数の決め方をわかりやすく【定期発注方式の場合】
季節変動を補正しないと欠品や過剰在庫になる
このようにすると、年間を通して需要傾向が安定していて変わらない商品だといいのですが、季節変動のある商品だと欠品や過剰在庫になるリスクがあります。
例えば、使い捨てカイロは1～2月はよく売れるので、需要の平均値が大きくなります。
すると需要予測在庫が大きく算出されますので、3～4月の在庫補充目標量も大きくなります。
その結果、3～4月は実際の需要に対して多めに発注して過剰在庫になるリスクがあります。
こうならないようにするためには、在庫補充目標量の計算に使う平均と標準偏差を逐次更新してやる必要があります。
今回はこの機能を、前回までにエクセルマクロで自作した在庫シミュレーションソフトに付加していきます。
【サンプル付き】最低発注ロットを考慮した在庫シミュレーションの作り方 by Excel VBA
このページの最後でサンプルファイルを無料ダウンロードできます。
トレンドを掴むには移動平均
例えば下図のような需要データが与えられたとします。
一日単位で見ると変動がかなり大きく見えますが、全体としてのトレンドを掴むにはどのようにしたらよいでしょうか。
このような目的で使われるのが移動平均です。
例えば、毎日直近5日間の平均を計算してグラフにすると下図のようになります。
だいぶ日々のデータのばらつきが均されました。
次に直近10日間の平均を毎日とってつなげてみましょう。
更に均されましたね。
同じように20日、30日の移動平均をとってみましょう。
このように平均を取る日数を増やすほど移動平均のグラフは平らになっていきます。
平らになって滑らかになるほどいいように思えますが、あまり滑らかにしすぎると重要なトレンドまで消してしまう可能性がありますので、ほどほどにしないといけません。
今回は30日で移動平均を取ることにします。
つまり、直近30日間のデータで平均と標準偏差を計算し、毎日、在庫補充目標量を計算して更新するということです。
エクセルVBAの変更箇所
それではこのロジックを前回作成したエクセルマクロVBAのプログラムに組み入れていきましょう。
前回作成したプログラムでは平均と標準偏差を下記のようにエクセルシート上で計算していました。
平均と標準偏差を取る期間が固定されていればこれでよいのですが、移動平均のように毎日データ期間が変わる場合にはマクロでプログラムする必要があります。
具体的にはFor～Next文で毎日発注量を計算しますので、その時についでに30日移動平均を計算して上のシートに書き込むことにします。
上のプログラムの赤枠で囲んだ部分が今回追加したプログラムです。
最初の文がConditionシートの（６，３）セルに平均値を、（７，３）セルに標準偏差を書き込んでいます。
それぞれＩ－１、つまり1日前からＩ－３１，つまり31日前までの需要データからAverage関数、及びStdev関数で計算しています。
Ｉは48行目（2月15日のデータが入っている）から需要データの入っている最後の行まで変化しますので、Ｉ＝４８の時、つまり2月15日の発注量を計算する時には1月15日から2月14日までの需要データから平均と標準偏差を求めます。
次にＩ＝４９の時、つまり2月16日の発注量を計算する時には1月16日から2月15日までの需要データから平均と標準偏差を求めます。
このように毎日、直近1か月間の需要データをもとに発注量を計算しますので、需要が上昇傾向でも下降傾向でも自動的に発注量に反映できるようになります。
以上、発注量を計算するアルゴリズムをまとめると下図のようになります。
たった2行追加するだけで、季節変動を考慮した在庫シミュレーションができるようになりました。
まとめ
今回作ったファイルはこちらからダウウンロードできます。
このように常に直近30日間の需要データから平均と標準偏差を計算するようにすることで、季節変動を考慮した動的なシミュレーションができるようになりましたが、直近何日間のデータを使えばよいかは商品の需要パターンにより異なります。
需要変動のサイクルの早い商品だと、直近30日間のデータでは長すぎて、需要パターンの変化に追いつかないこともあります。
また、直近Ｎ日間の需要データだけから計算すると、それより前の需要データが全く反映されません。
過去の経験が全く生かされないわけです。
それも余りに短絡過ぎるということで、過去のデータも生かしながら直近のデータも反映させるベイス統計を使った方法もあります。