【無料サンプル付き】エクセルで超簡単!適正在庫シミュレーション!誰でもシミュレーションできる方法

2021年7月26日

前回は適正在庫にコントロールするための、適正発注量の計算方法を解説しました。

>> 適正在庫の計算方法と意味をわかり易く解説

 

でも本当にこれで欠品を起こさずに在庫管理ができるのか?心配ですよね。

何の証拠もなければ、現場を納得させることもできないでしょう。

今回は、エクセルで簡単にできる適正在庫シミュレーションの方法を解説します。

数字で見せることができれば皆が納得です。

 

ページの最後でサンプルファイルを無料でダウンロードできます。

 

現場を説得するには適正在庫シミュレーションが一番

「統計だか数理だか知らんが、そんな在庫では欠品しちまうよ」

適正在庫量を計算すると、大抵、今の在庫量より少なくなりますので、現場からはこのような声が挙がります。

無理もありません、今までやってきたことを否定されるのですから。

しかし、ここで

「大丈夫です。統計的に計算した量なので間違いありません」

と言ったところで何の解決にもなりません。

 

ここで役に立つのが、適正在庫シミュレーションです。

例えば、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回分の倉庫の外にある在庫も加えないといけないのです。

現在庫量=昨日の在庫量-今日の出荷量+今日の入荷量+発注後未入荷の在庫量

 

これさえ注意すれば、後はそれほど難しくありません。

手順を追って説明していきます。

 

まず、新しいシートに下記の項目を入力しておきます。

 

これらの項目のうち、未入荷在庫1と未入荷在庫2だけ説明が必要でしょう。

これらは発注後まだ倉庫に届いていない在庫を入力する欄です。

今回はリードタイムを3日としていますので、発注してから3日後に入荷しますので、1日後の未入荷在庫を未入荷在庫1に、2日後の未入荷在庫を未入荷在庫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も加えることに注意が必要です。

 

発注した数量は、1日後の未入荷在庫1に入り、2日後には未入荷在庫2に入り、3日後の入荷量になりますので、次のように入力します。

2月16日の未入荷在庫1(F52):=E51

2月17日の未入荷在庫2(G53):=F52

2月18日の入荷量(H54):=G53

 

最後にD列からH列を4月30日までコピペします。

 

これで出来上がりです。

倉庫内在庫を見てみると、2月15日から4月30日まで0以下になることなく推移していることが分かります。

更にわかり易く示したい場合には、次のようにグラフにすると良いでしょう。

 

まとめ

いかがでしたでしょうか?

意外と簡単にできますので、特別な適正在庫シミュレーションソフトなどを買わなくても、自分でもできると思っていただけたと思います。

「でも少し私にはハードルが高い。」

と思われた方、安心して下さい。

こちらから今回作成したファイルをダウンロードできます。

 

ただ、今回は1アイテムについて行いましたが、これをすべてのアイテムについて行うのは確かに手間がかかります。

数千、数万アイテムを扱っている会社では、もはや人手では不可能でしょう。

次回はこれをマクロ(VBA)を使って自動化する方法を紹介します。

>> マクロで簡単!自分でできる適正在庫シミュレーションの自動化!