確率的予測を使用したExcelでの優先順位付けされた在庫補充

不確実性は予測の不可避な側面です。しかし、20世紀には、十分な数学モデルが与えられれば、不確実性を排除できるという希望とともに、統計的予測が登場しました。その結果、初期のサプライチェーン理論では、不確実性を軽視または無視し、新しいまたはより良い予測手法がそれを排除するか、それが無関係になることが期待されていました。これらのアプローチは善意を持っていましたが、不確実性は統計モデリングの1世紀後も不可避です。2012年、Lokadは代替のサプライチェーンの視点を先駆的に提案しました。このアプローチは、クラシックなポイント時系列予測の代わりに、確率的予測を活用しています。このガイドと付属のMicrosoft Excelスプレッドシートでは、確率的予測を在庫補充問題に適用します。このアプローチにより、優先順位付けされた在庫補充ポリシーがExcelでデモンストレーションされます。私たちの意図は2つあります。まず、より高度なソフトウェアツールに慣れていない視聴者にこのアプローチを普及させること。そして、不確実性を受け入れるには、洗練されたツールよりも特定のマインドセットが必要であることを示すことです。

ダウンロード: probabilistic-inventory-replenishment.xlsx

1. 在庫補充問題

在庫補充問題は、企業の主要な財務制約と目標を考慮した最適な購入リストを特定することに焦点を当てています。そのようなリストを作成するための方法は、予算制約に関係なく同じように機能する必要があります。つまり、投資対効果を最大化することを試みるため、どのSKUの単位を在庫にするかの財務的なリターンを定量化し、すべてのSKUの追加単位の文脈で順位付けする必要があります。

1.1 優先順位付けされた在庫補充の解決策

上記のように在庫をランキングするプロセスは、マイクロレベルの視点が必要です。購入リストに任意のSKUの単位を追加することによるリターンを比較するためには、いくつかの要素を考慮する必要があります。具体的には、確率的な需要予測による販売の確率と経済的な要因（例：粗利益率と購入価格）です。それぞれの数量は、内部および外部の制約（倉庫の容量制約、ロットの倍数、MOQ/MOVなど）に対してバランスを取る必要があります。2つ以上の単位が予想される利益が等しい場合などの特殊なケースは、各製品の相対的な重要性を評価することで在庫補充ポリシーに組み込まれる必要があります。SKUは単体ではなく、バスケットとして見るべきです。ミルクなどの単体では利益率が低いSKUでも、高利益商品の販売を可能にするために重要です。したがって、他の販売を促進する低利益商品のサービスレベルを維持することに対する財務的な報酬は、在庫切れのカバーなどの別の要素を表します。優先順位付けされた在庫補充（PIR）アプローチは、確率的な予測を入力として使用し、上記のすべての要素を考慮に入れます。

簡単に言えば、PIRソリューションは次の3つのステップで要約できます：

*1. 確率的な需要予測を作成する。

*2. 可能な購入数量をすべてリストアップする。

*3. 経済的な要因に基づいて、可能な購入数量をすべて順位付けする。

1.2 Excelでの優先順位付けされた在庫補充

架空の店舗の財務データを使用し、前のセクションでリストアップされた経済的な要因を含めたこのExcelスプレッドシートは、3つのSKU（ペン、キーボード、本棚）の在庫補充ポリシーをモデル化しています。SKUの追加単位ごとの財務的な結果と販売確率は、Chartsシート（図1参照）で示されています。図やグラフは、入力とモデルの仮定（初期在庫レベル、購入価格、販売価格など）に応じて更新されます。Control Towerシート（図2参照）のキー入力に基づいて、Micro purchasing decisionsシート（図3参照）には、実現可能な意思決定オプションの詳細なリストが生成されます。これらの入力は、Distribution generatorsシート（図4参照）からの確率的な需要予測とControl Towerシートからの入力です。最後に、優先順位付けされた在庫補充の意思決定の表がまとめられ、投資対効果の期待値に基づいて順位付けされます（図5のRanked purchasing decisionsシートを参照）。

2. 確率的需要予測

この文脈では、確率的予測はすべての_確実な_将来の需要値とそれに対応する確率のセットです。これは将来の需要の不確実性を包括し、任意の時間期間にわたって構築することができます。従来の時系列予測と同様に、特定の最も確実な需要値（図6の白い点）と傾向線（白い点を結ぶ灰色の線）があります。ただし、確率的予測では、すべての可能な（同じ確率ではない）需要値を追加することで不確実性を統合します。このアプローチは図6に示されており、異なる信頼区間は異なる確率での需要値を表しています。

図6の白い点は、固定された将来の間隔で最も確実な需要値を表しています。白い点から遠ざかるにつれて、代替の将来の需要値の範囲に対応する色帯があります - 色で表される確率分布です。この色は垂直軸に沿って徐々に薄くなり、不確実性が時間の経過とともに強まります（水平軸に沿って）。ただし、不確実性に関係なく、少なくとも1つの値が常に_最も確実_であり、これは常に白い点で表されます。1つの時点の確率分布の例を図7に示します。

図7は、図6のハイライトされたデータを確率ヒストグラムとして表し、異なる需要値の確率を明示的な数値で示しています。理解のために色分けが維持されています（薄い色ほど確率が低く、濃い色ほど確率が高いことを覚えておいてください）。この例では、最も確実な需要値は167ユニット（+/-）であり、そのため図6の切り取られた値の範囲の白い点がヒストグラムの最も高いバーの直上に配置されています。ただし、非常に低い需要値と高い需要値（それぞれ約80ユニットと260ユニット）にも需要の確率を割り当てています（どちらも非常に薄いオレンジ色です）。これは確率的な予測の潜在的なデータの豊富さを示しており、同様のヒストグラムがExcelスプレッドシートに含まれています - 各SKUに1つずつ（図4を参照）。これらのヒストグラム（図7など）を使用すると、発生確率がゼロでない需要値（単位）を特定し、PIRに組み込むことができます。

2.1 確率的予測の構築

Excelで過去のデータを使用して_実際の_確率的予測を作成することは可能ですが、これはその目的には最も適していないツールと言えます。全体として、本文書の範囲を超える製品グレードの確率的予測の構築の詳細は説明しませんので、単純化のために合成確率的予測が選択されました。これらの合成予測のパラメータは「分布ジェネレータ」で操作できます（図4を参照）。ただし、調整する前にデフォルト設定を最初に学習することをお勧めします。

主流のサプライチェーンの実践では、需要は通常正規分布と見なされますが、これは珍しいことです。実際のサプライチェーンでは、ほとんどのSKUは正規分布パターンから逸脱しています。この現実を考慮して、3つの異なる分布パターンを意図的に選択しました：キーボード用の正規分布、ペン用の負の二項分布、書棚用の二項分布の混合（2つの負の二項分布パターンの混合）。以下にこの仮定の正当性を示します。

たとえば、書棚は個人と企業（学校など）の両方によって購入されると仮定しているため、二項分布を使用しています。デフォルトの書棚設定では、個人から頻繁に需要があり、1人あたり1〜2ユニットが購入されます。これが分布の最初のモードを表しています（図4を参照）。一方、企業は頻繁ではない需要源ですが、個人の購入に加えて大口注文を行います（個人よりも大きな注文です）。これが発生すると、企業の需要は個人の購入によって生成される需要に追加され、分布の2番目のモードが現れます。この2番目のモードは右にシフトされ（高い需要値を表しています）、最初のモードよりも明らかに小さくなります。これは、それがより頻繁に発生しないことを反映しています（図4）。また、モデルでは、ペンは頻繁に高い需要（たとえば、学校の試験日前に購入する学生など）によって購入されると仮定しています。最後に、正規分布が_時折_発生することを反映して、キーボードの販売は正規分布パターンに従います。

Distribution generators（図4）内では、操作可能なセルのパラメータを変更することで需要分布を編集することができます。例えば、キーボードの平均値を40から50に増やすと（図4の「NORM parameters」を参照）、分布が右に10単位シフトします。この需要の平均値の増加により、_すべてのキーボードユニット_の期待ROIが増加します。同様に、負の二項分布（ペン）やバイモーダル分布（書棚）のパラメータを変更することもできます。

このデモでは、Excelはこの種の計算に表現力が不足しているため、製品ごとの修正を100ユニットまでに制限しています。例えば、キーボードの平均値を99に設定すると、需要ユニットの約50％が_Micro purchasing decisions_シートで計算に失敗します。

2.2 確率的需要予測のためのホライズンの選択

通常、予測は日次/週次/月次の区間に分けられますが、これらの離散的な期間は補充の観点からは限定的な有用性と価値があります。次のリードタイムまでの需要は、今日の購買決定ではカバーできません（バックオーダーが許可されていない限り）。なぜなら、購入したユニットはリードタイムと同じ期間後に到着するからです。したがって、需要は店舗の在庫と発注在庫（図8を参照）でカバーする必要があります。ただし、発注在庫のユニットが需要よりも前に到着することを前提としています。したがって、確率的予測は再発注ポイント間の需要、つまり再発注期間1の需要に関係しています（図9を参照）。より遠い将来の需要は将来の発注でカバーされます（図9の_Reorder period 2_を参照）。

理論上、確率的需要予測は_Reorder period 1_に等しい期間に構築する必要があります。この時間枠はResponsibility window Aと呼ばれます（図9を参照）。これを行うためには、リードタイム終了時点での在庫と発注在庫の将来の予測を行う必要があります。ただし、リードタイムの需要（前の発注期間で既に決定した需要）も確率的であり、これにより在庫レベル自体が確率分布になります¹。バックオーダーを許可することで（一部の業界では一般的な慣行）、確率的予測は共同期間（図9の_リードタイム_と_再発注期間1_）に構築できます（Responsibility window Bとしても知られる）。

現在の_在庫_と_発注在庫_のレベルは、リードタイム期間中の需要をカバーするものと仮定できます。在庫切れイベントが発生した場合、その後の需要はバックオーダーでカバーされます。これらのバックオーダーは、今日の時点で行われたマイクロ購買決定によって提供されます。これにより、_在庫_と_発注在庫_を離散値として扱うことができます（ランダムな値ではなく）²。

3. 補充決定の可能なオプションの特定

実際の在庫補充シナリオでは、すべての可能な決定オプションを明確にする必要があります。なぜなら、確率的な予測から製品ごとの単一の最適な決定（この場合は購入数量）に直接移行する方法はないからです。単一の「完璧な」選択肢ではなく、確率的なアプローチでは実現可能性の観点で考慮すべき可能な決定の範囲が示されます。

ここでの「実現可能性」とは、決定が直ちに実行可能であることを意味します。さらなる計算やチェックなしで「そのまま」実行できるということです。たとえば、利益が出て、すべての制約（例：MOQs、EOQs、ロットサイズ、完全なコンテナ出荷、および供給チェーンに存在する他の制約）を満たす場合、決定は「実現可能」です³。

「マイクロ購買決定」シートの各行（図3および10）では、特定の製品の購入注文に1つの在庫単位を追加することを検討する必要があります⁴。私たちの「現在」（またはこの実験のDay 1）は、現在の在庫レベルが表示される行29から始まります。これは在庫残高と発注在庫の合計として計算されます。購入注文に1つの単位を追加することを決定した場合、購入数量は列Lで計算され、これまでに購入を検討したすべての単位の合計となります（図10の注釈を参照）。

これらの実現可能な在庫決定が特定されたら、すべての可能な購入の経済的報酬を計算し、ランク付けします。現在の在庫（列FおよびG）にあるユニットの購入報酬は評価しません（図10）。これらのユニットは既に購入されているため、理論的な経済的報酬は以前の日付で決定されました（およびランク付けされました）。たとえば、図10のキーボードデータでは、現在26個のユニットが在庫にあります。したがって、29行から計算を開始し、追加の在庫の最初のユニットを注文すべきかどうかを検討します（在庫レベルを26から27個に増やします）。

3.1 実現可能な購入決定の評価

各製品の最適な購入数量を選択するためには、確率的な予測によって表される不確実な将来を考慮して、各製品の各実現可能数量の単位レベルでの予想金銭的リターンを計算する必要があります。これは在庫の最も細かいレベルに適応された期待値の概念です決定駆動最適化。

実際には、実現可能な決定ごとに予想リターンを計算しようとする際には、あらゆる種類の経済的要素を考慮する必要があります⁵。このデモンストレーションの目的で、以下の要素を考慮します：

• 販売価格：製品を顧客に請求する金額。
• 保管/ストレージコスト：製品を保持するためにかかる費用。
• 仕入価格：サプライヤー/卸売業者から製品を購入するための費用。
• 在庫切れのカバー：詳細は後述しますが、これはあまり知られていませんが重要な要素です⁶。

_在庫切れのカバー_は、製品の単位を在庫に保持するための財務的なインセンティブを表しますが、それを_明示的な_販売目標とは異なります。この経済的要素は、他の製品との_相対的な重要性_をモデル化するために使用されます。これにより、_直接的な_利益率の貢献によって重要性が低いと見なされる製品に対して在庫切れのイベントを回避することが奨励されます。これらの製品は_間接的な_方法で利益率に大きく貢献する可能性があります。そのため、これはより報酬の要素に近いものです⁷。この要素は曖昧ですが、すべての重要な製品（直接的な利益率の要素でない製品も含む）を特定することは非常に重要です。

3.2 各実現可能な決定のスコアの計算

在庫補充の決定の総経済的結果（または_購入報酬_）は、予想利益、予想在庫コスト、在庫切れのカバー（以下で詳細に定義）を含む、すべての経済的要素の合計です。保管コストは、在庫補充の決定をバランスさせるための反対勢力として、これらの計算に含まれています。

以下は、各列の数式の経済的な意味を分析したもので、マイクロ購買の決定シートの29行を例にしています（図12を参照）。

各決定の予想報酬を計算するためには、以下の要素が必要です：

粗利益（列E）= 販売価格 - 仕入価格。

販売確率（列Q）= シート内の数式を確認してください⁸。

販売しない確率（列なし）= 100% - 販売確率

予想利益（列R）= 粗利益 * 販売確率/100。

攻撃性ファクター（列S）= 0から1の範囲。このツールでは0.8が選択されています。

在庫切れのカバー（列T）= 販売価格 * 攻撃性ファクター * 販売確率

保管コスト（列U）

予想在庫コスト（列V）= 保管コスト * 販売しない確率⁹。

上記のデータを使用して、すべてのマイクロレベルの在庫決定（すべての製品の各単位）の_購入報酬_が以下のように計算されます：

購入報酬（列W）= 予想利益 + 在庫切れのカバー + 予想在庫コスト。

購入報酬の推定値が得られたら、すべての考慮される決定をランク付けするために後で使用する最終スコアを計算できます。

スコア（列Y）= 購入報酬 / 投資（列X）¹⁰。

_在庫切れのカバー_は、_直接的な_および_間接的な_リターンの両方を組み込んだ曖昧なドライバーであり、_購入報酬_は在庫の決定の予想リターンを_単独で_厳密に反映するものではありません。このタイプのリターンを計算する場合、この式から在庫切れのカバーを除外する必要があります¹¹。

4. 可能な在庫補充の決定のランキング

各製品の可能な在庫購入の決定に対してスコアが得られたら、降順（最高から最低）でソートされたリストがランク付けされた購入の決定（図13を参照）に生成されます。各可能な在庫の決定は、正のROI％の観点でソートされます。同じ図の列Aには、各決定に序数ランキング（1位、2位、3位など）も割り当てられます。

ランク付けされた購入の決定は、各製品（キーボード、ペン、書棚）ごとに色分けされた行を特徴としています。これは、任意の製品の単一の追加ユニットが_他のすべての製品の追加ユニット_と相互作用する方法を示すために使用されます。これらの在庫の決定は、ROIに影響を与えます。最後に、累積投資値が計算されます（列AA、図13）。これは、予算制約を考慮して購入の決定を終了する場所を示すために使用できますが、これはただ一つの可能な終了指標です¹²。

5. 終了基準の決定

ランク付けされた購入の決定および実際の終了点を選択する場合、基準はさまざまな変数によって異なります。たとえば、予算が限られているため、ROIを最大化することは問題です。また、サービスレベルに対する全体的な目標がある場合、これを優先することと利益率を最大化することとのバランスを取る必要があります。

より詳細にすると、終了基準は、_各製品またはカテゴリ_ごとに異なるサービスレベルの目標を持つ最大化されたROIを追求することを含めることができます。したがって、終了基準は、企業の全体的なビジネス目標に関して率直な反省の後に行われるべき戦略的な選択です。優先された在庫補充（PIR）は、この点において非常に柔軟です。各購入サイクルの終了基準は、同じ全体的なランキング手順を利用して調整することができます。

明示的な在庫補充の意思決定の視覚化のために、Charts ダッシュボード（シート3、図14参照）には、各製品に対して3つのチャートとグラフがあります。特に興味深いのは、「ドライビングフォース_製品名」（図14ではキーボードの例が使用されています）で、単位レベルでの異なる購入数量によるROIの進化を示しています。

チャートで明らかなように、購入数量を増やすとROIがマイナスになるポイントがあります。これは、あるレベルで、期待されるマージンが増加した予想在庫コストによって重大に減少するため、さらにユニットを購入する意味がないからです。

終了基準が決定されると、優先された在庫補充の意思決定はSKUごとに集計され、それによって各SKUの数量、投資、および予想達成率が_Output-Purchase Recommendation_で更新されます（図15参照）。予算制約（$0から$1450）を変更することができ、それによって推奨される購入リストが更新されます。利便性のために、コントロールタワーには2つの追加ブロックがあります：ベースケース - 紙のコピーとベースシナリオへの変更。前者は静的で、Lokadによって設計されたデモのデフォルト設定を表示します。後者は、行われた変更とLokadのデフォルト設定との違いを表示します。

_コントロールタワー_の購入推奨リストは、このデモの目標を表しています（図15参照）。

6. 結論

伝統的な時系列予測は、将来の不確実性と制約やドライバーの全体像を反映した在庫補充の意思決定を行うために必要な詳細度を捉えることができません。これは、期待される将来の結果に対する確率値で表される明示的な不確実性の次元を欠いているためです。伝統的な時系列はこのタイプのデータに対して実質的に盲目であるため、_安全在庫_のような古典的な対処方法は推測に過ぎません。十分でないと、期待されるROIがプラスの利益を生む販売を失います。余分にあると、（スプレッドシートで示されているように）期待されるROIがマイナスのユニットを在庫にしてROIを減少させます。

確率的な予測を利用した優先された在庫補充は、この問題に対する私たちの解決策です。このようなアプローチでは、在庫補充の選択肢を_組み合わせて_考慮します。これにより、在庫補充の選択肢の期待される財務報酬を完全に定量化して明らかにすることができます。このアプローチの基盤は、不確実性を受け入れ、確率的な予測入力を活用することです。それによって、任意のターゲットを設定するのではなく、どのサービスレベル（SKUごと）が意味のある財務報酬をもたらすかをより深く理解することもできます。

このドキュメントで示されているPIRアプローチは、合成データと狭いパラメータを使用して構築されました。これらの選択肢は、一般的なツール（Excel）を特殊な目的（PIR）に適応するために行われました。他の必要な譲歩の中で、SKUとユニットは制限されました（それぞれ3つと100個）が、処理時間を短縮するためです。なぜなら、カタログ全体のデータ（複数の店舗のデータは言うまでもなく）を処理するのは手間がかかりすぎるからです。さらに、供給チェーンの制約は追加されていません。重要なことは、Excelはランダム変数を処理するために設計されていないということです。これは確率的な予測とPIRポリシーを生成するための重要なステップです。これらの制限は、本番のPIRソリューションには適用されません。

Excelを使いこなせるほどビジネスが成長したサプライチェーンの専門家は、contact@lokad.comまでメールでご連絡いただき、本番のPIRソリューションのデモをご予約ください。

7. スプレッドシートの概要

7.1 Read Me

このシートはユーザーのランディングページとして機能します。オンラインチュートリアルへのリンク（現在お読みのもの）があります。

7.2 マイクロ購買の意思決定

これは2番目のシートで、すべての実行可能な補充意思決定オプションの細かい財務分析に専念しています。ここでは_手動のデータ操作は行われません_。このシートは、_コントロールタワー_と_ディストリビューションジェネレータ_シートからの入力に基づいて計算結果を表示するだけです。

主な特徴：

• 条件付き書式の行は「過去の決定」として変更できません。書式設定に関しては、Excelのブラウザベースのアプリは信頼性に欠けることがありますので、デスクトップアプリを使用することをお勧めします。
• 各列のヘッダーにカーソルを合わせると、役立つ定義/ノートが表示されます。

7.3 チャート

これは3番目のシートで、在庫補充の意思決定に影響を与える主要な要素を視覚化するために専用されています。ここでは手動のデータ操作は行われません。このシートは、実践者がPIRプロセスの内部動作を視覚化（そしてより理解）するのに役立つように設計されています。

主な特徴：

• SKUごとに3つのグラフ（キーボード、ペン、本棚）があります。
• 「駆動要因」チャートは、各SKUのユニットレベルでの各意思決定の主要な駆動要因を視覚化します。これが、x軸にはまだ注文されていないSKUのユニットのみが含まれている理由です。
• 他の2つのチャート（「フィルレートの増分」と「確率的な需要予測」）には、在庫のすべてのユニット（在庫残高と注文可能なユニット）が含まれています。

7.4 ランク付けされた購買の意思決定

これは4番目のシートで、ROI/スコアの降順で並べ替えられたすべての実行可能な補充意思決定がリストアップされています。このリストは自動的にシート2のデータ（マイクロ購買の意思決定）からソートされます。実行可能な意思決定は、お互いに関連して表示されます（以下の「主な特徴」を参照）。ここでは手動のデータ操作は行われません。シート5と6の入力に変更が加えられると、このリストも変更されます。

主な特徴：

• 実行可能な在庫補充の意思決定は、ROI/スコアの降順（最高から最低）でランク付けされます。
• ソートされた意思決定の累積投資が計算されます（シート4の列AAを参照）。
• 各列のヘッダーにカーソルを合わせると、役立つ定義/ノートが表示されます。

7.5 コントロールタワー・ミニオプティマイザ

これは5番目のシートで、モデルの仮定（入力）と推奨される意思決定（出力）をまとめています。操作可能なセルのデータを変更することで、モデルの仮定を変更し、モデルの出力を変更することができます。

主な特徴：

• デモンストレーションを支援するための3つのブロックがあります。「コントロールタワー」は入力の手動操作、「ベースケース - ハードコピー」はデフォルトの設定を表示、「ベースシナリオへの変更」は更新された設定とデフォルトの設定の違いを表示します（シート5を参照）。
• 「コントロールタワー」の下にある第4のブロック（「モデルの仮定」）は、初期在庫の仮定を操作するために使用されます（シート5を参照）。
• 操作可能なセルのデータのみを変更できます。

7.6 分布ジェネレーター

これは6番目のシートで、確率的需要予測の生成に使用されます。操作可能なセルのパラメータを変更すると、分布が更新され、新しい確率需要値が表示されます。

主な特徴：

• SKUごとに1つの分布グラフがあります。
• 各SKUには異なる分布パターンがあります（理由はセクション2.1で説明されています）。
• 分布のパラメータを操作するためのテーブルが、分布グラフのシリーズの左側にあります。
• 操作可能なセルのパラメータのみを変更できます。
• テーブルの関連する列のヘッダーにカーソルを合わせると、役立つ定義/ノートが表示されます。

ノート