在庫シミュレーションで発注量・発注点の最適化①
需要の不確実性が在庫管理を難しくしています。Excelベースのリスク分析ツールCrystal Ball は、世界中で在庫管理に利用されています。定量発注を例に活用例をご紹介します。
在庫管理におけるリスク
在庫管理の「リスク」とは何でしょうか？リスクと聞くと在庫が余ることで増大するコストや、欠品による損失を思い浮かべがちですが、本質的なリスクは「需要の不確実性」にあります。もし、将来どれだけの需要があるのかが完全に分かっていれば在庫が余ったり欠品が発生することはまず起こりません。
需要が思っていたよりも多ければ「結果として」欠品が発生します。逆に、思っていたよりも少なければ結果として過剰な在庫が余ります。将来の需要が不確実だからこそ、どのようなルールで在庫を管理するかが重要になります。
Excelで在庫の計算：よく利用される在庫管理手法
在庫管理の代表的な手法として、発注点（再注文点）を利用した方法があります。これは在庫が発注点を下回ったら決められた発注量（注文量）を補充するという方法で、「定量発注方式」と呼ばれます。この方法は、厳密に在庫残数を把握して実施することもできますが、あらかじめ発注点となる商品に印をつけるなど、簡便に実現する方法があることが特徴です。
製品ごとに適切な発注点・発注量の組合せを計算するには多くの手間と時間が必要です。また、需要が不確実であることを含めるとさらに難しくなります。
ここでは、Excelベースのリスク分析ツール である Crystal Ballを使用して、在庫管理のシミュレーションモデルを作成し、需要の不確実性を考慮していきます。さらに、Crystal Ball に搭載された最適化ツールであるOptQuestを使用すれば、不確実な需要を考慮した上で適切な発注点・発注量を簡単に求めることが可能です。
Crystal Ball を活用して需要の不確実性を考慮（モンテカルロ・シミュレーションの実施）
①Excelで在庫シミュレーション用のシートを準備
Excelシートに上図の定量発注を表現しました。52週間分のシミュレーションを行います（下図は15週間まで表示）。ルールは簡単で、在庫が発注点を下回ったら発注し、事前に設定したリードタイムを過ぎると入荷されます。
少し細かいですが、シート上では在庫を2種類に分けて計算しています。実質在庫は帳簿上の在庫となり、発注してから手元に来ていなくても計上されます。一方の手元在庫は倉庫上の在庫となり、発注してから手元に来ていなければ計上しないよう設定しています。これにより、発注されてからも工場内の在庫が減り続けることを再現しています。
ベースケースとして、発注点と発注量が共に250の場合、ワークシート上では年間総コストが ¥709,000となっています。欠品(逸失売上)は3週目に発生しているだけで、ほとんどの週では欠品が発生せずに少ない在庫量で対応できているように見えます。
②需要の変動を確率分布として設定
上記は需要が100で固定だった場合です（①内、図の緑色セル）。この需要が変動する場合には、どうなるでしょうか。Crystal Ball では簡単に実施可能です。需要の各セルに以下のような不確実性を与えてみたいと思います。需要は固定ではなく変動するという意味です。ここでは以下のように変動すると仮定しました。おおよそですが、100を中心として、80から120あたりまで変動します。ごくまれですが80以下、120以上になるケースもあるという設定です。
需要は週ごとに独立して発生すると仮定します。しかし、中には一度多くなるとしばらく多くなり続ける、季節性がある、などのケースもあるかと思います。そのような場合にはスプレッドシートを編集するか、Crystal Ball の機能で需要のばらつき同士に相関を持たせるという設定が可能です。
③需要が変動したケース
このモデルに需要の不確実性を与えてシミュレーションを実施してみます。まずは1回分のシミュレーションを実施してみました。先ほどと異なり需要が100ではなく様々な値を取っていることが分かります。欠品も2回発生しているようです。 年間総コストは 先ほどの¥709,000から ¥1,732,600 へと、大幅に上がっています。
これはあくまでも1回だけシミュレーションを行った様子です。ばらつきは様々なケースが想定されるため、これを1万回実施して、年間総コストがどうなるかを確認します。このように多くのケースを想定して分析することをモンテカルロ・シミュレーションと呼びます。
④モンテカルロ・シミュレーションにより需要がぶれた場合のコストを網羅的に確認
以下のような実施結果となりました。横軸は年間総コストの金額、縦軸は頻度（どのぐらい起こりやすいか、起こったのか）となります。需要にばらつきがある場合には、年間総コストはかなりかさむようです。平均が254万円、90%で315万円、95%で333万円となりました。また、300万円に収まるのは83%と分析しています（下図 中央下部の信頼度より）。
当初、需要を100と固定した際には70万円程度だったコストが、需要がぶれると見込んだ際には254万円とかさんでおり、過小評価だったと分かります。Crystal Ballシミュレーションによって、この在庫管理方法にはワークシートからは見えなかった大きな問題があることが分かりました。
上の問題を解決する方法としては、発注点・発注量を適切な値に設定すればよいのですが、具体的にどの値が適切なのかは分かりません。そこで、Crystal Ball に搭載されている最適化ツール OptQuestを利用して算出していきます。（最適化についての記事は公開予定です）
Crystal Ball で在庫管理を実施するメリット
複雑な分析をExcelベースで手軽に実施
在庫の分析をExcelシートで始める方も多いのではないでしょうか。Excelはどの企業でも導入されています。多くのデータを簡単に扱える上で、計算機能があるため、分析に向いています。しかし、確率的な想定をするにはもう一歩不足しています。
Crystal Ball を利用すれば上記のような不確実性を加味した在庫管理が可能となります。さらに、時系列分析機能 Predictor を活用すれば、過去データさえあれば数クリックで需要予測が可能です。
需要予測 → 在庫管理のコスト予測 → 発注点や発注量の最適化 という一連の流れをExcelベースで完結できます。
他にも、Crystal Ball には様々な用途があります。Excelで確率を扱えるようになるという強みを生かして、設計、製造、サプライチェーンの最適化、為替の分析など、製造業においても広く利用されています。
分析作業の負荷を下げて、透明性を上げる
既にExcel でこうした分析を実施されているかもしれませんが、Crystal Ball を利用すれば後付けで分析を発展させることが可能です。また、そうしたスプレッドシートは動作が重くなりがちで、かつ作成に手間がかかりますが、Crystal Ball を利用すればそうした分析の手間から解放されます。本来実施すべき分析そのもの、価値を生む作業にフォーカスすることが可能です。
また、個人で業務をする分には、長い関数が織り込まれた複雑なスプレッドシートを利用しても問題ないですが、業務として継続性を持たせるためにはソフトウェアの利用がおすすめです。
改めて、本記事においては、在庫シミュレーションについて簡単な検討例をお示ししました。以下のフォームより、今回利用したCrystal Ball について、ご紹介資料をお配りしております。また、Crystal Ball は15日間の無料体験を活用すれば、お手元でお試しいただくことも可能です。ぜひお試しください。
モンテカルロ・シミュレーションなど確率的な分析の参考となる事例資料を無料で公開
メーカー、製薬/ヘルスケア、資源/電力・ガス（インフラ）、監査法人・会計事務所、飲料・食品、消費財、保険・再保険、不動産、金融機関、教育（MBA、MOT）…
さまざまな業界事例！
