【無料サンプル付き】汎用的な適正在庫シミュレーションソフトの作り方を徹底解説で適正量を発注したら在庫推移がどうなるかをシミュレーションするソフトを、エクセルマクロを使って作成する方法を解説しました。このソフトでは商品1個単位で発注できることを想定していました。実際にはカートン単位、またはパレット単位、またはトラック単位でしか発注できないことがほとんどです。今回は最低発注ロットを考慮する機能を、前回作成した在庫シミュレーションソフトに追加していきます。このページの最後でサンプルファイルを無料ダウンロードできます。Udemyの関連講座リンクリンク最低発注ロットの入力欄をExcelシートに追加するまずは前回作成した条件設定シート(”Condition”シート)に、下図のように最低発注ロットを入力する欄を追加します。クリックすると拡大しますC11セルに最低発注ロットの数値を入力できるようにしておきます。LOTという名前の変数を定義して、入力された最低発注ロットがLOTに読み込まれるようにしておきます。3)VBAで発注量の計算式を変更する端数の発注は現実的でないシミュレーション結果は”Work”シートに書き込まれますが、最低発注ロットの影響を受けるのは発注量の列だけですね。クリックすると拡大しますそしてこの発注量は次の式によって計算されています。15)))言葉で書くと次のようになります。先の図で計算された発注量を見ると、1,485個とか4,996個などとなっていますね。これでは仕入れ先でカートンから出してバラで出荷しなくてはいけないものが出てきてしまいます。仕入れ先によってはカートン単位での発注しか受け付けないところもあります。そこでカートンが60個入りとして、カートン単位でしか発注できない場合の発注量を計算してみましょう。2月17日の発注量はいくつにすべきでしょうか?1,485÷60=24.7524.75カートンなんてありませんので、25カートン、つまり60個/カートン×25カートン=1,500個の発注量になります。これをエクセルマクロでどのようにプログラムするかを考えます。通常の割り算(/)余り、剰余だけを計算する割り算(MOD)商、整数部だけを計算する割り算(¥)の3種類の演算子があります。1と2は次の記事で説明しました。2の計算で4を返す演算子です。これら3種類の演算子を使うと、1,485割る60はそれぞれ次のようになります。60=451,485¥60=24あなたなら、これたを使ってどのように発注量1,500を算出しますか?一番オーソドックスなのは、MODで余りを計算して、それがゼロでなければ¥で計算した値(この場合は24)に1を足して、それにLOT(この場合は60)を掛けるというやり方です。エクセルマクロで書くと次のようになります。5))には最低発注量を無視した発注量(この場合は1,485)が入っていますので、このようになります。【注意】プログラム中では¥が「\」と表示されます。【参考】If~Then~Elseを使うのが面倒くさいという人は、/で60を割った値から¥で60を割った値を引いて、それをROUNDUP関数で整数に切り上げ、¥で60を割った値を足して、60を掛けるというやり方でもできます。以上の文を前回のエクセルマクロのプログラムに追加して実行すると、次のような結果になります。クリックすると拡大します発注量がすべて60の倍数になっていることが分かります。1パレットに30カートン載って、パレット単位でしか発注できないとすると、最低発注ロットに1,800を入力して、次のような結果になります。クリックすると拡大します発注量はすべて1,800の倍数になっていますね。それぞれの最低発注ロットにおける在庫推移をグラフで比較すると次のようになります。最低発注ロットが1個の時と60個の時はほとんど変わりませんが、1,800個になると在庫が多く推移していることが分かります。在庫データの平均を取ってみると、25%在庫が増えることも分かります。この数字を使って、パレット単位で発注することによる商品価格のディスカウントと倉庫コストの増加額を比較することも可能です。このように最低発注ロットの違いによる在庫の増減を正確に予測するのは、机上の計算で解析的に解くことは難しいです。(解析的に解くとは、式から計算して解くこと)過去データを使って統計的にシミュレーションするしかないのです。高価なシミュレーションソフトを買わなくてもエクセルマクロを使えば、このように自作できます。需給調整/業務改善の求人数が多いのはここだ!最後に、今回作ったファイルを公開しますので、自由にお使いください。VBA[第4弾]大量ブック・大量シート操作編!50個のExcelブックを一気にデータ収集するマクロ3時間講義シリーズ延べ5万人が学んだExcelマクロVBA講座の人気シリーズから待望の[第4弾]が登場!今度のマクロは、大量のExcelブック、大量のワークシートからデータ収集。本格実用マクロを手を動かして作りながら学ぶVBAプログラミング。基礎を効率的に学べるベーシック講座です。ついて豊富な図を用いて説明していきます。使った複数SKUの適正在庫シミュレーションをPythonで実装する前回の記事【Pythonで実装する】適正在庫シミュレーションソフトを作ってみた。【区間推定の使い方】タイヤの摩耗を具体例にわかりやすく解説します。TOP28【作業分析6】MMCモデル|最適なトラックドック数をSimpyでシミュレーション【作業分析5】トラックの待機台数の多さを人の表情でアニメーション(2)カンボジアの物流でお困りのことがあればお気軽にご相談下さい。学生時代にアジアの雑踏にハマる。誰もが羨むメーカーを退職し「旅するように働く」ことを目指し物流業界に転職。理工学と経営学のダブル修士。2023年1月からカンボジアのオートレスビーチ在住。