【無料サンプル付き】適正在庫シミュレーションをエクセルで作る方法
前回は適正在庫にコントロールするための、適正発注量の計算方法を解説しました。
でも本当にこれで欠品を起こさずに在庫管理ができるのか？心配ですよね。
何の証拠もなければ、現場を納得させることもできないでしょう。
今回は、エクセルで簡単にできる適正在庫シミュレーションの方法を解説します。
数字で見せることができれば皆が納得です。
ページの最後でサンプルファイルを無料でダウンロードできます。
現場を説得するには適正在庫シミュレーションが一番
「統計だか数理だか知らんが、そんな在庫では欠品しちまうよ」
適正在庫量を計算すると、大抵、今の在庫量より少なくなりますので、現場からはこのような声が必ず挙がります。
無理もありません、今までやってきたことを否定されるのですから。
しかし、ここで
「大丈夫です。統計的に計算した量なので間違いありません」
と言ったところで何の解決にもなりません。
ここで役に立つのが、適正在庫シミュレーションです。
例えば、2月1か月間の売上データから安全在庫と需要予測在庫を求め、3月の売上データで適正発注した場合の日々の在庫量をシミュレーションして欠品しないことを示せれば、さすがの現場も納得せざるを得ません。
ところがどういうわけか、適正在庫シミュレーションがあまり活用されているとは言えません。
このようなシミュレーションは専用のソフトウェアがないとできないと思われているからでしょうか。
ところが、このシミュレーションはエクセル初心者でも簡単にできるのです。
適正在庫の計算式をおさらい
適正在庫の計算式
まずは適正発注量の決め方をおさらいしておきましょう。
適正発注量＝在庫補充目標量－現在庫量
＝安全在庫＋需要予測在庫－現在庫量
＝√(N+M)＊標準偏差＊安全係数＋(N+M)＊1日あたりの売上平均―現在庫量
N：発注してから在庫が補充されるまでのリードタイム（日）
M：発注間隔（日）
つまり、過去の売上データから安全在庫と需要予測在庫を求めて在庫補充目標量を決め、それと現在の在庫量との差を発注すればよいのでした。
安全在庫と需要予測在庫は過去データから平均と標準偏差を計算すれば求まります。
ですから、後は四則演算関数を使うだけで、エクセルで計算できます。
適正在庫の計算事例
例を挙げましょう。
これはある商品の1月から4月までの売上データです。
ここで今日が2月15日だと仮定します。
そして、1月1日から2月14日までの過去データから安全在庫と需要予測在庫を求め、在庫補充目標量を計算します。
その在庫補充目標量を使って、2月15日から4月30日までの毎日の在庫量がどのように推移するかをシミュレーションしてみましょう。
発注してから在庫補充されるまでのリードタイム（N）が3日、発注間隔（M）は1日、許容欠品率は1%とします。
まず、1月1日から2月14日までの売上平均(AVERAGE)と標準偏差(STDEV)を求めます。
平均＝1,103
標準偏差＝685
すると安全在庫と需要予測在庫は次のように求まります。
安全在庫＝√(N+M)＊標準偏差＊安全係数
＝√(3+1)＊685＊2.33
＝3,193
需要予測在庫＝N＊売上平均
＝3＊1,103
＝3,309
従って、
在庫補充目標量＝安全在庫＋需要予測在庫
＝6,503
となります。
発注間隔は1日ですので、毎日の在庫量を見て、6,503より多ければ発注しない、少なければ6,503との差だけ発注すればよいわけです。
ここで一つだけ注意することがあります。
今日倉庫にある在庫量は昨日の在庫量－今日の出荷量＋今日の入荷量で計算できますが、発注してからまだ倉庫に着いていない在庫もあるので、在庫量の計算にはこれも加える必要があります。
今回はリードタイムが3日ですので、発注2回分の倉庫の外にある在庫も加えないといけないのです。
現在庫量＝昨日の在庫量－今日の出荷量＋今日の入荷量＋発注後未入荷の在庫量
これさえ注意すれば、後はそれほど難しくありません。
手順を追って説明していきます。
エクセルへの入力手順
項目名を入力する
まず、新しいシートに下記の項目を入力しておきます。
これらの項目のうち、未入荷在庫１と未入荷在庫２だけ説明が必要でしょう。
これらは発注後まだ倉庫に届いていない在庫を入力する欄です。
今回はリードタイムを3日としていますので、発注してから3日後に入荷しますので、1日後の未入荷在庫を未入荷在庫１に、2日後の未入荷在庫を未入荷在庫２に入力するようにしています。
売上高データをコピペする
次に、売上データを日付と売上の欄にコピペします。
上図では1月20日までしか見えていませんが、4月30日分までコピペしています。
在庫補充目標量の計算式を入力する
次に平均から在庫補充目標量までを計算します。
各セルには次のように入力します。
平均（K1）：=AVERAGE(B6:B50)
標準偏差（K2）：=STDEV(B6:B50)
安全在庫（K3）：=SQRT(M2+M3)*K2*2.33
需要予測在庫（K4）：=M2*K1
在庫補充目標量（K5）：=K3+K4
安全在庫の計算式で最後に2.33を掛けていますが、許容欠品率1%に対応する安全係数を標準正規分布表から求めています。
この係数をわざわざ表から探すのが面倒とか、表に載っていない例えば許容欠品率1.5%に対応する安全係数を知りたいという場合には、下記のエクセル関数で求めることもできます。
安全在庫係数＝NORMSINV（1－欠品許容率）
次に2月15日の在庫目標補充量に先ほどの計算値を入れて、4月30日までコピペします。
在庫推移を入力する
続いて在庫量の計算式を入力していきます。
倉庫内在庫の初期値は、2月14日の実際の在庫量を使うのがいいのですが、ここでは初期値として5,000を入力しておきます。
2月15日の倉庫内在庫は、前日の倉庫内在庫から2月15日の売上が引かれて、同じ日の入荷量が加えられますので次のように入力します。
2月15日の倉庫内在庫（D51）： =D50-B51+H51
2月15日の発注量は、在庫補充目標量と同じ日の在庫量の差になりますので、次のように入力します。
2月15日の発注量（E51）：=C51-(D51+SUM(F51:G51))
ここで現在庫量は、倉庫内在庫量だけでなく、未入荷在庫１と２も加えることに注意です。
発注した数量は、1日後の未入荷在庫１に入り、2日後には未入荷在庫２に入り、3日後の入荷量になりますので、次のように入力します。
2月16日の未入荷在庫１（F52）：=E51
2月17日の未入荷在庫２（G53）：=F52
2月18日の入荷量（H54）：=G53
最後にD列からH列を4月30日までコピペします。
最後に在庫推移をグラフにすれば完璧
これで出来上がりです。
倉庫内在庫を見てみると、2月15日から4月30日まで0以下になることなく推移していることが分かります。
更にわかり易く示したい場合には、次のようにグラフにすると良いでしょう。
無料サンプルダウンロード
いかがでしたでしょうか？
意外と簡単にできますので、特別な適正在庫シミュレーションソフトなどを買わなくても、自分でもできると思っていただけたと思います。
「でも少し私にはハードルが高い。」
と思われた方、安心して下さい！
こちらから今回作成したファイルをダウンロードできます。
ただ、今回は1アイテムについて行いましたが、これをすべてのアイテムについて行うのは確かに手間がかかります。
数千、数万アイテムを扱っている会社では、もはや人手では不可能でしょう。
次回はこれをマクロ（VBA）を使って自動化する方法を紹介します。
Pythonで実装する適正在庫シミュレーション
このシミュレーションはPythonを使えば、更に自由度が高くなります。
興味のある方は、こちらもご覧下さい。