はじめに
在庫管理では、「在庫金額を減らしたい」という経営課題と、「欠品を防ぎたい」という現場課題が常に衝突する。
在庫を一律に削減すれば、売れ筋商品の欠品や納期遅延が発生する。一方、安全を見込んで多めに保有すれば、資金の固定化や保管スペースの圧迫、廃棄・値下げリスクにつながる。
こうした問題に対して広く使われてきたのがABC分析である。しかし、ABC分析だけで分かるのは、主に売上高や年間消費金額に対する貢献度である。
実際の在庫管理では、次の3つを同時に見なければならない。
- どれだけ売上や利益に貢献しているか
- 需要が安定しているか、激しく変動するか
- 実際にどの程度の頻度で出庫されているか
そこで有効なのが、ABC分析・XYZ分析・FSN分析を組み合わせた複合在庫分析である。
本記事では、Excelを計算基盤として使い、生成AIをデータ整形、数式作成、分類基準の検討、運用ルール作成、経営報告に活用する実務的な構築方法を解説する。
ABC分析だけでは在庫最適化できない理由
ABC分析では、商品を売上高や年間消費金額の大きい順に並べ、累積構成比によってA・B・Cに分類する。
一般的には、次のような基準が使われる。
- Aランク：累積売上構成比80％まで
- Bランク：80％超95％まで
- Cランク：95％超
この方法は、重点管理すべき商品を把握するには有効である。しかし、同じAランクでも、その性質は大きく異なる。
例えば、毎月安定して売れ、頻繁に出庫される商品と、特定の季節に大量販売される商品では、必要な安全在庫や発注方法が違う。
また、売上金額は小さくても、毎日のように出庫される消耗品をCランクとして一律に管理すると、ピッキング作業や補充作業の負担を見落とす可能性がある。
つまり、ABC分析は「経済的重要度」は示せても、「需要の読みやすさ」や「物流現場での動き」までは示せない。
ABC×XYZ×FSN分析の3つの評価軸
| 分析軸 | 主な評価指標 | 分類 | 管理上の意味 |
|---|---|---|---|
| ABC分析 | 年間売上高、年間消費金額、粗利益額 | A・B・C | 資金や管理工数を優先配分する |
| XYZ分析 | 需要の変動係数（CV） | X・Y・Z | 需要予測のしやすさと安全在庫を判断する |
| FSN分析 | 出庫頻度、移動率、最終出庫日 | F・S・N | 補充方法、保管場所、滞留在庫を判断する |
ABC分析は経済的重要度を評価する
ABC分析では、売上や消費金額への貢献度が高い商品をA、低い商品をCとする。
ただし、売上高だけでなく、企業の目的に応じて粗利益額、在庫金額、欠品時の影響額などを評価指標に加えることも検討すべきである。
XYZ分析は需要の不確実性を評価する
XYZ分析では、需要の標準偏差を平均需要で割った「変動係数」を使用する。
変動係数は次の式で表される。
CV＝需要量の標準偏差÷平均需要量
分類例は次のとおりである。
- X：CVが0.33以下で、需要が比較的安定
- Y：CVが0.33超0.66以下で、中程度の変動
- Z：CVが0.66超で、需要が不規則
この数値は絶対的な基準ではない。季節商品が多い企業と、定番品中心の企業では、適切な閾値が異なる。
FSN分析は在庫の動きを評価する
FSN分析では、観察期間中に商品が何回、または何か月出庫されたかを評価する。
- F（Fast-moving）：高頻度で動く商品
- S（Slow-moving）：動きが遅い商品
- N（Non-moving）：一定期間動いていない商品
月次データを使う場合は、過去12か月のうち出庫があった月の割合を「出庫発生率」として使用できる。
- F：出庫発生率75％以上
- S：25％以上75％未満
- N：25％未満
取引明細を保有している場合は、月数だけでなく、出庫日数、出庫伝票数、最終出庫日からの経過日数も組み合わせると精度が高まる。
ABC・XYZ・FSNを組み合わせることで、各SKUを3×3×3の27区分に分類できる。自動車部品在庫を対象とした2025年の研究でも、価値、需要変動、移動頻度を重ねたABC×XYZ×FSN分類とダッシュボードが、従来の分類よりも実態を捉える方法として提案されている。ただし、この研究では実装後の効果測定までは行われていない点に注意が必要である。South African Journal of Industrial Engineering
Excelで使用するデータ構造
最初から複雑なシステムを構築する必要はない。まずはExcelに、SKUマスターと過去12か月の出庫実績をまとめる。
基本的な列構成は次のとおりである。
| 列 | 項目 |
|---|---|
| A列 | SKUコード |
| B列 | 商品名 |
| C列 | 単価 |
| D～O列 | 過去12か月の月別出庫数量 |
| P列 | 年間総出庫数量 |
| Q列 | 年間売上高 |
| R列 | 累積売上構成比 |
| S列 | 変動係数 |
| T列 | 出庫発生率 |
| U列 | ABC分類 |
| V列 | XYZ分類 |
| W列 | FSN分類 |
| X列 | 複合セグメントコード |
データが複数のCSVや基幹システムから出力される場合は、Power Queryを使って取り込み、列名統一、不要行削除、データ型変換、月次集計までを定型化する。
Power Queryは、データの接続・変換・統合手順を保存し、更新時に再実行できるデータ準備機能である。Microsoft公式でも、繰り返し可能なデータ変換処理として説明されている。Microsoft Learn「Power Queryとは」
Excel関数による分類ロジック
年間総出庫数量と年間売上高
P2セルに年間総出庫数量を計算する。
=SUM(D2:O2)Q2セルに年間売上高を計算する。
=P2*C2その後、Q列の年間売上高を降順に並べ替える。
累積売上構成比
R2セルでは、売上高の累積構成比を計算する。
=SUM($Q$2:Q2)/SUM($Q$2:$Q$1000)データ件数に応じて、最終行の1000は変更する。
ABC分類を行うU2セルには、次の式を設定する。
=IF(R2<=0.8,"A",IF(R2<=0.95,"B","C"))変動係数とXYZ分類
S2セルで変動係数を計算する。
=IF(AVERAGE(D2:O2)=0,"",STDEV.S(D2:O2)/AVERAGE(D2:O2))V2セルでXYZ分類を行う。
=IF(AVERAGE(D2:O2)=0,"Z",IF(S2<=0.33,"X",IF(S2<=0.66,"Y","Z")))平均需要がゼロの商品に対して、単純にIFERROR関数で変動係数をゼロにすると、「需要が安定したX商品」と誤分類される可能性がある。
実務では、平均需要がゼロの商品を空白、判定対象外、またはZとして処理したうえで、FSN分析のN判定と組み合わせる必要がある。
出庫発生率とFSN分類
T2セルで、12か月中に出庫があった月の割合を計算する。
=COUNTIF(D2:O2,">0")/COLUMNS(D2:O2)W2セルでFSN分類を行う。
=IF(T2>=0.75,"F",IF(T2>=0.25,"S","N"))複合セグメントコード
X2セルで3つの分類を連結する。
=U2&V2&W2これにより、「AXF」「BYS」「CZN」といった複合コードがSKUごとに付与される。
ExcelのSTDEV.S、AVERAGE、COUNTIFなどの関数は、この程度の分析であれば特別なアドインを使わずに利用できる。Microsoft公式Excel関数リファレンス
27分類を管理ルールへ変換する
27分類を作るだけでは在庫は改善しない。重要なのは、各分類を発注、補充、安全在庫、保管場所、棚卸頻度のルールへ変換することである。
ただし、27種類すべてに異なる運用ルールを設定すると、現場が複雑になる。そのため、類似するセグメントを5つ程度のポリシーグループにまとめる。
| ポリシーグループ | セグメント例 | 発注・補充 | 安全在庫 | 倉庫配置 |
|---|---|---|---|---|
| 主力・安定高回転群 | AXF、BXF | 高頻度小口補充、JIT型発注 | 小さめ | ピッキング最短エリア |
| 高価値・高変動群 | AYF、AYS、AZF、AZS | 需要連動発注、個別承認 | 動的に見直す | 管理しやすい集中エリア |
| 標準流通群 | BYF、BYS、CXF、CXS | 定期発注、自動補充 | 標準水準 | 標準保管エリア |
| 低価値・高変動群 | CYF、CYS、CZF、CZS | まとめ発注、発注点管理 | 品揃え方針で判断 | 後方・高層エリア |
| 滞留・陳腐化リスク群 | AXN、BZN、CZNなど | 新規補充停止、受注生産化 | 原則ゼロ | 隔離・処分検討エリア |
例えばAXFは、売上貢献度が高く、需要が安定し、出庫頻度も高い。欠品の影響は大きいが需要予測はしやすいため、短い補充サイクルと少なめの安全在庫を設定しやすい。
一方、AZSは売上貢献度が高いものの、需要変動が大きく、出庫も断続的である。単純な自動発注では過剰在庫や欠品が発生しやすいため、営業情報や受注見込み、サプライヤーのリードタイムを含めた個別管理が必要になる。
安全在庫と発注点を動的に計算する
需要とリードタイムの双方が変動する場合、安全在庫は次のように計算できる。
安全在庫＝安全係数×√（リードタイム×需要量の分散＋平均需要量²×リードタイムの分散）
発注点は次の式で求める。
発注点＝平均需要量×平均リードタイム＋安全在庫
計算する際は、需要量を日次で扱うなら、リードタイムも日数に統一する必要がある。
また、すべての商品に同じサービス率を設定するのではなく、AXFは高いサービス率、CZSは低めのサービス率、N商品は原則として安全在庫ゼロとするなど、複合分類ごとに設定を変える。
生成AIを組み込む4つの実務フェーズ
Microsoftは、Copilot in Excelについて、数式の生成、データの要約、グラフやピボットテーブルの作成、傾向や外れ値の抽出などに利用できるとしている。Microsoft公式「Copilot in Excel」
ただし、生成AIに発注数を直接決めさせるのではなく、次の4段階で使うことが重要である。
フェーズ1：データ整形と数式作成
生成AIに、列構造、評価期間、分類基準、欠損値の扱いを伝え、Excel関数、Power QueryのMコード、VBA、Office Scriptsなどのたたき台を作成させる。
生成されたコードは、少数のテストデータで結果を照合してから全SKUへ適用する。
フェーズ2：分類閾値の調整
ABCの80％・95％、XYZの0.33・0.66、FSNの75％・25％は初期値にすぎない。
全SKUの分布、中央値、四分位範囲、季節性、商品ライフサイクルなどを生成AIに整理させ、自社に適した閾値候補を複数提示させる。
最終的な閾値は、購買、営業、物流、経営の関係者が決定する。
フェーズ3：管理ルールとSOPの作成
分類別に、次の内容を生成AIに作成させる。
- 発注方式
- 発注承認者
- 目標サービス率
- 安全在庫の考え方
- 棚卸頻度
- 倉庫ロケーション
- 長期滞留時の対応
- セグメント変更時の確認手順
フェーズ4：経営報告の自動化
生成AIに集計結果を読み込ませ、次の項目を含む月次報告書を作成する。
- 在庫金額の増減
- Aランク商品の欠品状況
- Z商品の需要変動
- N商品の在庫金額
- 前月から分類が変わった商品
- 廃棄、値下げ、返品、補充停止の候補
- 翌月に実施すべき改善策
そのまま使える生成AIプロンプト
「分析してください」だけでは、実務に使える結果は得られない。役割、入力データ、判定方法、出力形式を明確に指定する。
以下は、ABC×XYZ×FSN分析用のプロンプト例である。
あなたは、製造業・卸売業の在庫管理に詳しいサプライチェーンアナリストです。
添付した在庫データについて、ABC分析、XYZ分析、FSN分析を組み合わせた複合分類を行うためのExcel設計を作成してください。
データには、SKUコード、商品名、単価、過去12か月の月別出庫数量が含まれています。
ABC分析は年間売上高の累積構成比を使用し、Aは80％以下、Bは80％超95％以下、Cは95％超としてください。
XYZ分析は月別出庫数量の変動係数を使用し、Xは0.33以下、Yは0.33超0.66以下、Zは0.66超としてください。ただし、平均需要がゼロの商品をXに分類しないでください。
FSN分析は、過去12か月のうち出庫があった月の割合を使用し、Fは75％以上、Sは25％以上75％未満、Nは25％未満としてください。
必要なExcel列、各列の数式、並べ替え手順、エラー処理、検算方法を提示してください。
最後に、複合コードごとの発注方式、安全在庫、棚卸頻度、倉庫配置、滞留在庫対応をMarkdown形式の表で出力してください。
不明な点は推測で補わず、確認が必要な項目として分けて示してください。
実務で注意すべき5つの落とし穴
欠品期間を需要ゼロとして扱わない
在庫がゼロだったために販売できなかった数量は、出庫実績には現れない。
これをそのまま需要データとして使うと、本来は人気商品であるにもかかわらず、需要が少ない商品、または不規則な商品と判定される。
受注残、失注記録、欠品日数、販売機会損失の推計値を補正データとして使用する必要がある。
評価期間を短くしすぎない
1か月、3か月、12か月では、変動係数と出庫頻度が大きく変わる。
季節商品の場合、繁忙期だけを見ればAXF、年間で見ればAZSになることもある。基本は12か月以上とし、必要に応じて移動平均や前年同月比較を追加する。
商品ライフサイクルを無視しない
新商品、成長商品、定番商品、終売予定商品を同じ数式だけで評価するのは危険である。
「発売後3か月以内」「終売予定」「キャンペーン対象」といった例外フラグを設け、数値分類と併用する。
分類結果を固定しない
ABC×XYZ×FSN分析は、一度作って終わる資料ではない。
月次または四半期ごとに再計算し、AXFからAYSへの変化、CYSからCZNへの変化などを監視する。分類の変化自体が、需要減少や陳腐化の早期警告になる。
生成AIの出力を無検証で実行しない
生成AIが作った数式やVBA、Power Queryコードには、列参照の間違い、ゼロ除算、境界条件の欠落などが含まれる可能性がある。
また、在庫数、仕入価格、取引先情報を外部AIへ入力する場合は、自社の情報管理規程と利用環境を確認し、必要に応じて匿名化や法人向けAI環境を使用する。
4週間で始める導入ステップ
第1週：データをそろえる
SKUマスター、単価、出庫数量、在庫数、欠品期間、リードタイムを収集し、データ定義を統一する。
第2週：Excel分析表を構築する
ABC、XYZ、FSNの各数式と複合コードを設定する。代表的な20SKU程度を選び、手計算とExcelの結果を照合する。
第3週：管理ルールを決める
購買、営業、物流、経営が参加し、分類ごとの発注方式、安全在庫、棚卸頻度、保管場所、滞留在庫処分ルールを決定する。
第4週：月次更新を定着させる
Power Queryなどで更新処理を定型化し、次のKPIをダッシュボードで確認する。
- 在庫金額
- 在庫回転率
- 欠品率
- 長期滞留在庫金額
- 緊急発注回数
- 値下げ・廃棄金額
- 分類変更SKU数
- ピッキングや補充にかかる作業時間
まとめ
ABC×XYZ×FSN複合在庫分析の目的は、商品を27種類に分類することではない。
分類結果を、発注方法、安全在庫、補充頻度、倉庫配置、棚卸、滞留在庫処分といった具体的な業務判断へつなげることが目的である。
Excelは、計算根拠をセル単位で確認でき、既存の業務にも導入しやすい。一方、生成AIは、数式やコードの作成、分類基準の検討、例外の発見、SOPや経営報告の作成を支援できる。
重要なのは、「AIに在庫管理を任せる」ことではなく、Excelで再現可能な計算基盤を作り、その構築・検証・説明・改善を生成AIによって高速化することである。
この仕組みを月次のS&OPや在庫会議に組み込めば、経験や勘だけに依存しない、継続的な在庫コントロール体制を構築できる。
コメント