在庫回転率・滞留在庫をエクセルで分析する方法　発注点と欠品を防ぐ実務

在庫回転率は「売上原価÷平均在庫」、回転日数は「365÷回転率」で計算できます。この2つの指標にABC分析と発注点の計算を組み合わせれば、専用システムがなくてもエクセルで「どのSKUが滞留していて、どのSKUが欠品リスクを抱えているか」を可視化できます。ただし後述するとおり、エクセル運用にはSKU数とデータ更新頻度の壁があります。

在庫回転率・滞留在庫・発注点は同じ在庫データから導かれる、表裏一体の指標です（目安構成・要検証）。

在庫回転率と回転日数の計算式

在庫回転率は「売上原価 ÷ 平均在庫金額」、回転日数は「365 ÷ 回転率」で求まります。回転率が高いほど在庫が現金化されるスピードが速く、回転日数が長いほど在庫が倉庫に眠っている期間が長いことを意味します。

エクセルでの実装はシンプルです。月次の売上原価を集計したセルと、期首・期末在庫金額の平均を出すセルを用意し、=売上原価セル/平均在庫セルで回転率、=365/回転率セルで回転日数を出せば完成します。SKUごとに行を分けて同じ数式を横展開すれば、商品別の回転率一覧表になります。

ここで実務上つまずきやすいのが「平均在庫」の定義です。期首・期末の単純平均だと月中の急な仕入れや大口出荷の影響を拾えません。月次スナップショットの平均や、可能であれば日次残高の平均（=AVERAGE(日次残高範囲)）を使うほうが実態に近い数値になります。卸売業の在庫データでは、期首期末平均と日次平均で回転率が1.3倍近くずれるSKUが出ることもあり、発注判断に影響します（数値は目安）。

回転率・回転日数はいずれも売上原価と平均在庫金額の2値から機械的に算出できます（数値は算出例・要検証）。

在庫の中身をどの単位まで細かく見るかで、次に説明するABC分析の設計が変わってきます。回転率だけを全社平均で見ていると、動きの速い主力商品と動かない在庫が相殺されて実態が見えなくなるため、SKU単位での回転率把握が前提になります。

ABC分析で管理の優先順位をつける

ABC分析は、売上または出荷金額の累積構成比でSKUをA・B・Cにランク付けし、管理の手間をかける優先順位を決める手法です。目安として累積構成比の上位70%程度をA、70〜90%をB、残り10%をCに区切りますが、SKU構成によって調整が必要です（要検証）。

エクセルでの手順は、まずSKUごとの売上金額を降順に並べ替え、=SUM($B$2:B2)/SUM($B$2:$B$1000)のような累積構成比の数式を1行ずつ積み上げます。次にIF関数で=IF(累積構成比<=0.7,"A",IF(累積構成比<=0.9,"B","C"))のように区分を自動判定させれば、SKU一覧にA/B/Cラベルが付与されます。

ABC分析の本来の目的は「区分ごとに運用ルールを変える」ことにあります。A区分は在庫切れの機会損失インパクトが大きいため発注点の見直し頻度を週次にする、C区分は個々の金額インパクトが小さい代わりに滞留すると倉庫スペースを圧迫するため、次に説明する滞留在庫抽出の監視対象に回す、といった運用に落とし込むところまでがセットです。ランク付けだけして放置すると、集計表を作った工数が回収できません。

累積構成比70%・90%を目安の区切りとし、区分ごとに発注点の見直し頻度を変えます（数値は算出例・要検証）。

発注点・安全在庫をエクセルで計算する

発注点は「発注してから納品されるまでの間に欠品させない在庫水準」を指し、「発注リードタイム中の平均出荷数＋安全在庫」で計算します。安全在庫は需要のばらつきとリードタイムのばらつきを吸収するための緩衝在庫で、目安として「安全係数×出荷数の標準偏差×√リードタイム」で近似できます（安全係数は欠品許容率に応じて設定する仮の値・要検証）。

エクセルでの実装は、まずSKUごとに過去の日次または週次出荷数から=STDEV.S(出荷数範囲)で標準偏差を出し、リードタイム（仕入先からの納品日数）をマスタとして持たせます。安全在庫は=安全係数*標準偏差*SQRT(リードタイム)、発注点は=平均日次出荷数*リードタイム+安全在庫という数式で自動計算できます。現在庫がこの発注点を下回ったSKUをIF関数やフィルタで抽出すれば、発注リストが自動で上がってくる仕組みになります。

このとき安全係数を高くしすぎると欠品は防げますが、その分だけ在庫が積み上がり回転率が悪化するというトレードオフがあります。ABC分析でAランクに分類したSKUは欠品の機会損失が大きいため安全係数を高めに、Cランクは在庫コストを優先して低めに設定する、という区分別の調整が実務では機能します。

発注点は平均出荷数とリードタイムから、安全在庫は出荷のばらつきから算出します（数値は算出例・要検証）。

滞留・不動在庫を抽出する仕組みをつくる

滞留在庫とは回転が遅く在庫日数が長期化している状態、不動在庫とは一定期間（目安90〜180日・業種により要調整）出庫実績がゼロの状態を指します。この違いを区別せずに「動きの遅い在庫」とひとまとめにすると、対策の優先順位を誤ります。

エクセルでの抽出は、SKUごとの「最終出庫日」を管理台帳やSUMIFS関数から取得し、=TODAY()-最終出庫日で経過日数を算出、一定日数を超えたものをフィルタまたは条件付き書式で色付けする方法が基本です。これに前述の回転日数を組み合わせ、「回転日数が業種平均の2倍以上」かつ「最終出庫日から60日以上経過」のように複合条件でフラグを立てると、単純な滞留よりも精度の高い抽出になります。

滞留在庫の実務上のインパクトは「キャッシュが寝ている」ことです。100万円分の滞留在庫があれば、それは倉庫の棚に100万円の現金を積んで眠らせているのと同じ状態にあります。棚卸資産回転率の悪化は決算書にも表れるため、経理・財務側からも滞留在庫の可視化ニーズは強くあります。抽出後は値引き販売・他拠点への転送・廃棄処分（不動在庫の場合）など、区分に応じた出口戦略とセットで運用する必要があります。

最終出庫日からの経過日数と回転日数を組み合わせると、単純な滞留判定より精度の高い抽出になります。

自社でどこまで自動化すべきか判断に迷う場合、まずは現状の在庫管理の課題を洗い出すところから始めると精度の高い打ち手が見えてきます。初月無料の経営AI診断（通常30万円相当）では、こうした在庫データの棚卸しから発注点設計の見直しまで、自社の業務に即した形で可視化するお手伝いをしています。

エクセル運用の限界と脱エクセルの判断基準

ここまでの計算式はすべてエクセルで実装できますが、運用が進むほど3つの壁にぶつかります。

1つ目は月次スナップショットの遅れです。多くの現場では在庫データの更新が月次締めのタイミングに依存しており、月の途中で発生した急な需要変動や大口注文には数式が追いつきません。発注点を下回っていても、次の更新まで気づかないという事態が起こります。

2つ目は需要変動への非追従です。安全在庫の計算式は過去の標準偏差を前提にしていますが、季節性やトレンドの変化、キャンペーンによる一時的な需要増をエクセルの数式だけで織り込むのは困難です。過去データに基づく静的な計算式は、需要構造が変わった瞬間から精度が落ち始めます。

3つ目はSKU数の増加による破綻です。SKUが数十件程度なら1行1商品の数式管理で回りますが、数百〜数千件規模になると、数式のコピー漏れ・シートの重量化・担当者しか触れないブラックボックス化が同時に進みます。SKU管理表が壁一枚をまるごと使うほど肥大化し、更新作業自体が属人化する、というのはこの規模でありがちなパターンです。

これらの壁は「エクセルが悪い」のではなく、「エクセルが想定していない使い方に達した」ことのサインです。SKU数が数百を超えた、需要変動の激しい商材が増えた、担当者交代のたびに数式が壊れる——このいずれかに心当たりがあれば、需要予測や発注点の自動最適化を担うシステム化を検討するタイミングです。どこまでを自動化しシステムに任せるべきかは自社のSKU構成や更新頻度によって変わるため、まずは現状の可視化から始めることをおすすめします。

エクセル運用の限界は「使い方が想定を超えた」サインであり、システム化検討の判断材料になります。

まとめ

在庫回転率・回転日数はシンプルな数式で算出でき、ABC分析・発注点・安全在庫の計算を組み合わせることで、エクセルでも滞留在庫と欠品リスクの両方を可視化する仕組みが作れます。一方で、月次更新の遅れ・需要変動への非追従・SKU数の増加という3つの壁は、エクセル運用が構造的に抱える限界です。自社がどの段階にいるか分からない場合は、初月無料の経営AI診断（通常30万円相当）で在庫データの現状を棚卸しし、システム化すべき範囲を一緒に見極めるところから始められます。

よくある質問

在庫回転率は何回転あれば良いといえますか？

業種・商材によって適正水準が大きく異なるため一律の基準はありません（目安・要検証）。卸売業では年6〜12回転、消費期限のある商材はさらに高い回転数が求められる傾向があります。自社の適正値は、過去実績の中央値と資金繰り（在庫にいくら現金が寝ているか）から逆算するのが実務的です。同業他社比較より、自社の過去12ヶ月トレンドで悪化していないかを見る方が実用的です。

ABC分析のA・B・Cはどこで区切ればいいですか？

累積構成比で上位70%程度をA、70〜90%をB、残り10%をCに区切るのが一般的な目安です（要検証・SKU構成により調整）。ただしSKU数が少ない場合は境界が不安定になるため、件数ベース（上位20%をAとする等）と併用して決めると実務では扱いやすくなります。A区分は発注点の見直し頻度を上げ、C区分は滞留リスクの監視を優先するなど、区分ごとに運用ルールを変えることが本来の目的です。

発注点方式と定期発注方式はどちらがエクセル向きですか？

在庫数を随時監視して閾値を割ったら発注する発注点方式の方が、エクセルでの数式実装は単純です。定期発注方式（一定周期でまとめて発注量を計算）は需要予測要素が絡むため関数が複雑になりがちです。SKU数が少なく更新頻度が週次以下なら発注点方式、日次で欠品を避けたい主力商品が多いなら定期発注方式との併用を検討する価値があります。

滞留在庫と不動在庫の違いは何ですか？

滞留在庫は回転が遅く在庫日数が長期化している状態、不動在庫は一定期間（目安90〜180日・業種により要調整）出庫実績がゼロの状態を指します。滞留は「まだ動く可能性がある」段階、不動は「実質的に動かない」段階と捉えると対策の優先順位をつけやすくなります。不動在庫は評価損計上や廃棄処分の検討対象になるため、経理側との連携が必要です。

関連記事

• 在庫管理をエクセルで自動化する方法　関数とマクロの実装と限界 — 関連: 回転率計算の土台となる在庫データ自動化
• 小売店の在庫管理をエクセルでやる方法 発注点・棚卸の実務設計と限界 — 関連: 発注点設計の業種別実務
• 棚卸表をエクセルで作る方法 卸売・小売の実務テンプレと自動集計のコツ — 関連: 滞留・不動在庫抽出の前提となる棚卸データ
• 製造業の在庫管理改善事例 エクセル脱却で欠品・過剰在庫を減らした進め方 — 関連: 脱エクセル判断の実例
• 中小企業のAI導入 費用相場と内訳 初期費用から運用コストまで実例で解説 — 関連: システム化・AI導入時の費用感