在庫不足リスクを「見える化」する ― まず押さえるべき考え方（欠品・安全在庫・リードタイム）
在庫管理で怖いのは、「在庫が足りないかもしれない」という不安そのものより、足りない確率がどれくらいか分からない状態です。Excelでシミュレーションする目的は、勘や経験を否定することではなく、欠品が起きる条件を数字で説明できるようにすること。まずは、在庫不足リスクを見える化するために必須の3要素（欠品・安全在庫・リードタイム）を整理します。
欠品（Stockout）＝「需要に在庫が負ける」瞬間
欠品は単に在庫がゼロになることではなく、その日に必要な出庫（販売・消費）を、手元の在庫でさばけない状態を指します。実務では次のようなコストにつながります。
- 売上機会の損失（買いたい人が買えない）
- 顧客満足の低下（次から選ばれない）
- 社内の火消しコスト（緊急発注・割り込み対応）
重要なのは、欠品は「たまたま」ではなく、需要のブレ×補充の遅れで起きる再現性のある現象だという点。ここをExcelで再現できるようになると、対策が打ちやすくなります。
安全在庫＝「ブレの吸収材」
安全在庫は、ざっくり言えば想定外に売れた（使われた）ときの保険です。平均需要ぴったりで在庫を組むと、需要が少し上振れしただけで欠品します。逆に保険を厚くしすぎると、今度は在庫過多（保管費・廃棄・キャッシュ圧迫）になります。
ここで押さえたいのは、安全在庫は「気合」ではなく、次の2つで決まるという考え方です。
- 需要のブレ：日によって売れ方が変わる（平均±ばらつき）
- 補充のブレ：入荷が予定より遅れることがある（リードタイムのばらつき）
この2つのブレをExcelで再現し、欠品確率が何%か、逆に安全在庫を何個積めば欠品確率が下がるかを数字で比較できるようにします。
リードタイム＝「発注しても今日入らない」現実
在庫が足りなくなってから発注しても、入荷まで時間がかかります。この発注→入荷までの時間がリードタイムです。リードタイムが長いほど、途中で需要が上振れしたときに逃げ道がなくなり、欠品リスクが上がります。
さらに厄介なのは、リードタイムは多くの場合一定ではないこと。たとえば「通常3日だけど、混雑すると5日」みたいなブレがあると、平均で計算した在庫は簡単に崩れます。シミュレーションでは、リードタイムを固定値にするのか、ばらつきを持たせるのかで結果が大きく変わるため、前提条件として明確にするのがポイントです。
まずは「欠品が起きる構造」を式に落とす
Excelシミュレーションの最小構成はシンプルで、考え方はこの1行に集約できます。
在庫（今日）＝在庫（昨日）＋入荷（今日）－需要（今日）
そして、在庫がマイナスになった（または需要を満たせなかった）ら欠品です。ここに、発注してから入荷するまでの遅れ（リードタイム）と、需要のブレを入れることで「欠品が起きるかどうか」を何度も試せるようになります。
次章では、シミュレーションを回すための土台として、入力シート（需要・在庫・発注・納期）をどう設計するかを具体的に作っていきます。
シミュレーション用の入力シートを作る ― 必要データとExcel設計（需要・在庫・発注・納期）
1章で整理した「在庫＝昨日＋入荷－需要」を回すには、まず前提条件を1か所に集めた“入力シート”が必要です。ここが雑だと、後から式を直すたびに破綻します。逆に言えば、入力が整っていれば3章以降はコピペで増やせる設計になります。
入力シートは「触る場所」を固定する
おすすめは、ブック内にSheet名「INPUT」を作り、黄塗りセルだけを手入力（他は数式）にします。20代のサラリーマンが引き継ぎで評価されるのは、だいたいこの“触る場所が迷わない”設計です。
最低限そろえるデータ（4ブロック）
① 需要（Demand）に関する入力
- シミュレーション期間（日数）：例）90日
- 平均需要（1日あたり）：例）20個/日
- 需要のばらつき（標準偏差の目安）：例）6
- 需要の下限（0未満は0にする等）：例）0
ここではまだ乱数は作りません（3章で実装）。2章では「需要は平均とブレで表現する」ためのパラメータ置き場を作ります。
② 在庫（Inventory）に関する入力
- 初期在庫：例）200個
- 安全在庫（仮置き）：例）50個
安全在庫は最終的に最適化しますが、まずは仮の数値でOK。重要なのは「初期在庫」と「安全在庫（基準線）」を分けて持つことです。
③ 発注（Order）に関する入力
- 発注点（在庫がここを下回ったら発注）：例）80個
- 発注量（固定ロット）：例）200個
- 発注頻度の制約（任意）：例）同日に複数回発注しない＝TRUE
まずはシンプルに「発注点方式（Reorder Point）」で組むと、後の検証がラクです。発注点や発注量は5章で“欠品確率とのトレードオフ”として調整する対象になります。
④ 納期（Lead Time）に関する入力
- リードタイム（日）：例）3日
- リードタイムのばらつき（任意）：例）標準偏差1日
- 最短/最長（任意）：例）2〜6日
リードタイムを固定にするか、ブレを持たせるかで結果が変わります。ここも入力欄として分けておくと、シナリオ比較が一瞬です。
Excel設計：この「表の骨格」を先に作る
INPUTとは別に、計算用のシート（例：Sheet名「SIM」）を用意し、1行＝1日で回します。2章の段階では、列見出しだけ先に作っておきましょう。
| 列 | 項目 | 役割 |
|---|---|---|
| A | 日付/Day | 1〜期間（日次で回す） |
| B | 需要 | 3章で乱数から生成 |
| C | 入荷 | 発注済みの到着分 |
| D | 期首在庫 | 前日の期末在庫 |
| E | 期末在庫 | 期首＋入荷－需要 |
| F | 欠品フラグ | 需要を満たせない日を1 |
| G | 発注数 | 発注点判定で発注 |
| H | 到着予定日 | 発注日＋リードタイム |
ポイントは、発注（G列）と入荷（C列）を分けること。実務でも「発注した＝在庫が増える」ではなく、「発注した＝将来の入荷予定が立つ」ですよね。この分離ができていると、リードタイムの遅れも自然に表現できます。
入力と計算を“参照”でつなぐ（ベタ打ち禁止）
SIM側の式や条件は、必ずINPUTのセルを参照します。たとえば発注点なら「INPUT!B5」みたいに一元管理。後から「発注点80→100」に変えても、シミュレーション全体が崩れません。ここまでできたら土台は完成です。
次章では、INPUTで用意した「平均」と「ばらつき」を使い、Excelの乱数で需要のブレ（売れ方）を再現していきます。
需要のブレを再現する ― 乱数＋分布で「売れ方」を作る（RAND・NORM.INV など）
2章で用意した「平均需要」と「ばらつき（標準偏差）」は、まだただのパラメータです。3章ではここに乱数を流し込み、日別の需要（売れ方）を作ります。狙いはシンプルで、毎日同じ20個売れる世界ではなく、18の日もあれば35の日もある現実をExcel上で再現すること。この“ブレ”が欠品を生むので、最初にちゃんと作り込みます。
まずは「乱数」＝毎回変わるダイスを作る
Excelの乱数はRAND()で作れます。これは0以上1未満の小数をランダムに返します。
- 例：
=RAND()→ 0.153… みたいな値が出る - 再計算のたびに値が変わる（F9、または他セル変更でも変わる）
SIMシートの需要（B列）をいきなり作る前に、補助列（例：I列）に「乱数U」を置くと後でデバッグしやすいです。
SIM!I2: =RAND()
需要の作り方：最初は「正規分布」を使うのが手堅い
日別需要は「平均の周りに上下する」ことが多いので、まずは正規分布で作るのが分かりやすいです。ExcelではNORM.INVで「乱数（0〜1）→ 正規分布の値」に変換できます。
INPUTで置いた平均需要・標準偏差を使い、SIMの需要（B列）を次の形にします。
SIM!B2:
=MAX(INPUT!需要下限,
ROUND(NORM.INV(SIM!I2, INPUT!平均需要, INPUT!需要標準偏差), 0)
)
- NORM.INV(U, 平均, 標準偏差)：乱数Uに対応する需要を返す
- ROUND(…,0)：需要は「個数」なので整数に丸める
- MAX(下限, …)：負の需要が出たら0などに丸める（現実的にマイナス販売はない）
これで「平均20、標準偏差6」なら、だいたい14〜26が多く出つつ、たまに30超えも混ざる…という需要ができます。ここが“欠品が起きる世界”の入口です。
需要が偏る商品なら：正規分布が合わないケースもある
注意点として、正規分布は左右対称で、マイナスが出る可能性もあります。食品や日用品のように「ゼロ近辺もある」「たまにドカ売れする」タイプだと、正規分布は少し不自然になることがあります。その場合は次のような代替案が現実的です。
- 下限0でクリップ：今回のように
MAX(0, ...)でまず対応（簡単で実務向き） - 三角分布（最小・最頻・最大）：現場の感覚値を入れやすい（Excelだけでも実装可能）
- ポアソン分布：注文件数のような「回数データ」に近い需要に向く（Excel関数でも可）
ただ、最初から凝りすぎると全体が進まないので、まずは正規分布＋下限処理で回し、結果の違和感が出たら分布を変える、で十分です。
乱数が毎回変わって困るとき：値を固定して検証する
RAND()は便利ですが、再計算で結果が動くため「どの修正が効いたのか分からない」状態になりがちです。検証段階では、必要に応じて乱数列（I列）をコピーして値貼り付けし、需要を固定してからロジック（4章の入出庫計算）を確認すると事故が減ります。
ここまでで、SIMシートのB列に「日ごとに揺れる需要」が入るようになりました。次章では、この需要と入荷を使って在庫推移を日次で回し、欠品フラグを立てる入出庫ロジック（IF・SUMIFS・在庫計算）を組み立てます。
在庫推移を日次で回す ― 入出庫ロジックと欠品判定（IF・SUMIFS・在庫計算）
3章で「日別需要（B列）」が作れたら、次は本丸の在庫推移です。やることは1章の式を、SIMシートで1日ずつ再現するだけ。
期末在庫＝期首在庫＋入荷－需要
ただし実務っぽくするなら、欠品は「在庫がマイナス」ではなく需要をさばけなかった日として判定します。ここを丁寧に作ると、5章の欠品確率がブレません。
列の役割を固定して、日次で“回る形”にする
SIMシートの想定列（2章の表）に沿って、2行目（1日目）から下にコピーできる式にします。
① 期首在庫（D列）：前日の期末を持ってくる
初日だけは初期在庫、2日目以降は前日の期末在庫を参照します。
SIM!D2: =INPUT!初期在庫
SIM!D3: =E2
以降はD3を最終日までコピーでOKです。
② 入荷（C列）：到着予定日が「今日」の発注を合計する
発注（G列）と入荷（C列）を分けた設計がここで効きます。入荷は「今日が到着予定日の注文」を集計すればよいので、SUMIFSで回せます。
SIM!C2:
=SUMIFS($G:$G, $H:$H, $A2)
- G列：発注数
- H列：到着予定日（後述）
- A列：日付/Day（1,2,3…でも実日付でもOK）
これで「過去に発注した分が、リードタイム後にまとめて入る」動きになります。
③ 出庫（需要）に上限をかける：売れる量は在庫を超えない
欠品判定を正しくするコツは、需要と実際に出庫できた量を分けることです。列追加できるなら「実出庫（例：J列）」を作るのがおすすめです。
SIM!J2: =MIN(B2, D2 + C2)
在庫は無限にマイナスにならないので、「今日使える在庫（期首＋入荷）」の範囲でしか出庫できない、という現実に合わせます。
④ 期末在庫（E列）：期首＋入荷－実出庫
SIM!E2: =D2 + C2 - J2
⑤ 欠品フラグ（F列）：需要を満たせなかったら1
SIM!F2: =IF(B2>J2, 1, 0)
「需要Bに対して、実出庫Jが足りない＝欠品」という判定なので、在庫がギリギリ0でも欠品は0になります（売り切っただけ）。この差は集計の精度に効きます。
発注（G列）と到着予定日（H列）：発注点方式を式にする
最後に「いつ発注するか」。ここは2章で置いた発注点方式をそのまま使います。判断基準は迷いやすいですが、最初はシンプルに期末在庫が発注点未満なら発注で進めると崩れません。
SIM!G2:
=IF(E2 < INPUT!発注点, INPUT!発注量, 0)
到着予定日は、発注した日にだけ入れておけばSUMIFSで拾えます。
SIM!H2:
=IF(G2>0, $A2 + INPUT!リードタイム, "")
ここまで組めたら、A列の日付（Day）を最終日まで、B列の需要、そしてC〜H（＋J列）を下までコピーします。すると乱数で需要が変わるたびに、在庫推移と欠品日が自動で更新されます。
次章では、この日次結果を何百回も回して欠品フラグを集計し、欠品確率として意思決定できる数字に落とし込みます。
欠品確率と対策を数字で出す ― モンテカルロの集計と意思決定（安全在庫・発注点の最適化）
4章までで「1回分の90日シミュレーション（需要がブレる→在庫が動く→欠品フラグが立つ）」は完成しました。とはいえ、その1回はたまたまの世界です。重要なのは、乱数を変えて何百回も回し、欠品が起きる確率として意思決定できる数字にすること。ここで使うのがモンテカルロ法です。
まず決める：欠品確率を「何で定義するか」
欠品は集計の定義次第で印象が変わります。最初は次の2つを押さえるとブレません。
- 欠品日発生確率：期間内で「欠品日が1日でもあった」割合（サービスレベルで見やすい）
- 欠品日数の平均：1期間あたりの欠品日数の期待値（現場の痛みが伝わる）
モンテカルロの土台：試行（Run）を横に並べて集計する
やり方はシンプルで、「SIMで作った90日分の欠品フラグ（F列）」を試行回数ぶん繰り返すだけです。おすすめは別シートにSheet名「MC」を作り、次の列を用意します。
| 列 | 項目 | 例 |
|---|---|---|
| A | Run | 1〜500 |
| B | 欠品日数 | 期間内のF合計 |
| C | 欠品あり（0/1） | 欠品日数>0なら1 |
欠品日数は、SIMの欠品フラグ範囲を合計するだけです（例：90日ならF2:F91）。
MC!B2: =SUM(SIM!F2:F91)
MC!C2: =IF(B2>0, 1, 0)
あとはA列に1〜500を入れ、B2:C2を500行までコピーします。ポイントは各行を計算するたびにRAND()が更新され、別世界の90日が生成されること。これで「500回分の結果」がたまります。
欠品確率を出す：平均を取るだけでOK
集計は驚くほど単純です。
- 欠品日発生確率（＝期間内で欠品が起きる確率）
=AVERAGE(MC!C2:C501)
- 欠品日数の期待値（平均欠品日数）
=AVERAGE(MC!B2:B501)
たとえば「欠品日発生確率が0.18」なら、約18%の確率でどこか1日でも欠品するという意味です。上司や他部署に説明するなら、この1行がいちばん刺さります。
対策を“最適化”する：安全在庫・発注点を振って比較する
欠品確率が出たら、次は対策です。ここでやりがちなのが「安全在庫を増やす」で終わること。でも現実はキャッシュが死ぬので、欠品確率を目標値に収める最小コストを探します。
実務向きの進め方は、INPUTの安全在庫（仮）と発注点を候補値で振り、都度モンテカルロの欠品確率を見る方法。
- 欠品日発生確率：5%以下にしたい
- その条件を満たす「発注点の最小値」を探す
- 同じ発注点でも安全在庫を厚くするとどう動くか比較する
Excelでは、発注点候補（例：60,80,100,120…）を並べ、データテーブル（What-If分析）で欠品確率セルを参照させると、半自動で一覧化できます。ここまでできると「勘」ではなく、欠品確率◯%のために発注点を△個にすると説明できます。
意思決定の結論は「確率×トレードオフ」で書く
最後に、ブログ読者（＝若手社員）が社内で使える形に落とすなら、結論のテンプレはこれです。
- 現状：欠品日発生確率18%（500回、90日）
- 対策案：発注点を80→110に変更
- 効果：欠品日発生確率が18%→6%に低下
- 副作用：平均在庫が増える（保管費・キャッシュ）
欠品をゼロにするのは可能ですが、コストが跳ねます。だからこそ、Excelモンテカルロで「このコストで、この欠品確率まで下げる」を数字で示せるのが強い。ここまで作れれば、在庫議論の主導権を取れます。
コメント