【サンプル付き】最低発注ロットを考慮した在庫シミュレーションの作り方 by Excel VBA
前回の記事
【無料サンプル付き】汎用的な適正在庫シミュレーションソフトの作り方を徹底解説
で適正量を発注したら在庫推移がどうなるかをシミュレーションするソフトを、エクセルマクロを使って作成する方法を解説しました。
このソフトでは商品1個単位で発注できることを想定していました。
しかし、実際にはカートン単位、またはパレット単位、またはトラック単位でしか発注できないことがほとんどです。
今回は最低発注ロットを考慮する機能を、前回作成した在庫シミュレーションソフトに追加していきます。
このページの最後でサンプルファイルを無料ダウンロードできます。
最低発注ロットの入力欄をExcelシートに追加する
まずは前回作成した条件設定シート（”Condition”シート）に、下図のように最低発注ロットを入力する欄を追加します。
C11セルに最低発注ロットの数値を入力できるようにしておきます。
そしてLOTという名前の変数を定義して、入力された最低発注ロットがLOTに読み込まれるようにしておきます。
Dim LOT As Long
LOT = Worksheets(“Condition").Cells(11, 3)
VBAで発注量の計算式を変更する
端数の発注は現実的でない
シミュレーション結果は”Work”シートに書き込まれますが、最低発注ロットの影響を受けるのは発注量の列だけですね。
そしてこの発注量は次の式によって計算されています。
Range(Cells(I, 5), Cells(I, 5)) = Range(Cells(I, 3), Cells(I, 3)) – Range(Cells(I, 4), Cells(I, 4)) – WorksheetFunction.Sum(Range(Cells(I, 6), Cells(I, 15)))
言葉で書くと次のようになります。
発注量＝在庫補充目標量－倉庫内在庫－すべての未入荷在庫
先の図で計算された発注量を見ると、1,485個とか4,996個などとなっていますね。
これでは仕入れ先でカートンから出してバラで出荷しなくてはいけないものが出てきてしまいます。
仕入れ先によってはカートン単位での発注しか受け付けないところもあります。
そこでカートンが60個入りとして、カートン単位でしか発注できない場合の発注量を計算してみましょう。
まず2月17日の発注量はいくつにすべきでしょうか？
1,485÷60＝24.75
24.75カートンなんてありませんので、25カートン、つまり
60個／カートン×25カートン＝1,500個
の発注量になります。
これをエクセルマクロでどのようにプログラムするかを考えます。
MOD演算子を使って端数を判断する
エクセルマクロの割り算には、
- 通常の割り算（／）
- 余り、剰余だけを計算する割り算（MOD）
- 商、整数部だけを計算する割り算（￥）
の3種類の演算子があります。
１と２は次の記事で説明しました。
【参考】
物流シミュレーションに必要な機能はたったこれだけ！最短距離でマクロを理解【場合分け】
３の￥は
14÷3＝4 余り2
の計算で４を返す演算子です。
これら3種類の演算子を使うと、1,485割る60はそれぞれ次のようになります。
1,485／60＝24.75
1,485 MOD 60＝45
1,485￥60＝24
あなたなら、これたを使ってどのように発注量1,500を算出しますか？
一番オーソドックスなのは、MODで余りを計算して、それがゼロでなければ￥で計算した値（この場合は24）に1を足して、それにLOT（この場合は60）を掛けるというやり方です。
エクセルマクロで書くと次のようになります。
If Range(Cells(I, 5), Cells(I, 5)) Mod LOT <> 0 Then （余りがゼロでなければ）
Range(Cells(I, 5), Cells(I, 5)) = (Range(Cells(I, 5), Cells(I, 5)) \ LOT + 1) * LOT（￥の計算結果に１を加えてLOTを掛ける）
Else
Range(Cells(I, 5), Cells(I, 5)) = (Range(Cells(I, 5), Cells(I, 5)) \ LOT + 0) * LOT（さもなければ￥の計算結果にLOTを掛ける）
End If
Range(Cells(I, 5), Cells(I, 5))には最低発注量を無視した発注量（この場合は1,485）が入っていますので、このようになります。
【注意】
プログラム中では￥が「\」と表示されます。
【参考】
If～Then～Elseを使うのが面倒くさいという人は、／で60を割った値から￥で60を割った値を引いて、それをROUNDUP関数で整数に切り上げ、￥で60を割った値を足して、60を掛けるというやり方でもできます。
｛ROUNDUP(1,485／60－1,485￥60,0)＋(1,485￥60)｝＊60＝1,500
実行結果
以上の文を前回のエクセルマクロのプログラムに追加して実行すると、次のような結果になります。
発注量がすべて60の倍数になっていることが分かります。
更に、もし1パレットに30カートン載って、パレット単位でしか発注できないとすると、最低発注ロットに1,800を入力して、次のような結果になります。
発注量はすべて1,800の倍数になっていますね。
そして、それぞれの最低発注ロットにおける在庫推移をグラフで比較すると次のようになります。
最低発注ロットが1個の時と60個の時はほとんど変わりませんが、1,800個になると在庫が多く推移していることが分かります。
在庫データの平均を取ってみると、25%在庫が増えることも分かります。
この数字を使って、パレット単位で発注することによる商品価格のディスカウントと倉庫コストの増加額を比較することも可能です。
このように最低発注ロットの違いによる在庫の増減を正確に予測するのは、机上の計算で解析的に解くことは難しいです。
（解析的に解くとは、式から計算して解くこと）
過去データを使って統計的にシミュレーションするしかないのです。
でも、高価なシミュレーションソフトを買わなくてもエクセルマクロを使えば、このように自作できます。
最後に、今回作ったファイルを公開しますので、自由にお使いください。