【専門家厳選】必ず役立つ４つの在庫分析手法｜エクセルで簡単に作成できる

欠品で販売機会を逃してしまった、不良在庫が減らない・・・、在庫データが活用できていない、

在庫管理で、こんなお悩みはありませんか？

日々の業務に追われ、在庫分析の重要性は分かっていても、何から手をつければ良いか分からず後回しになっていませんか？

しかし、勘や経験だけに頼った在庫管理を続けていると、気づかぬうちに過剰在庫や欠品を繰り返し、会社のキャッシュフローを悪化させてしまう恐れがあります。

在庫分析によって、実績値だけではわからない情報を読み取ることができ、欠品や過剰在庫を防ぎ、適正在庫の判断材料の第一歩になります。

そこでこの記事では、誰でも簡単に始められるExcelを使った４つの在庫分析手法を具体的に解説します。

分析というと統計学や難しい数式などを使わなければいけない・・・と思われるかもしれませんがそんなことはありません。

エクセルの基本的な機能を使って誰でも簡単にできる方法をご紹介します。

この記事を読めば、自社の在庫状況を正確に把握し、課題を可視化できるようになります。データに基づいた最適な在庫管理で無駄なコストを削減して、売上アップを目指しましょう。

現状を把握したり、在庫の改善対象を決めたり、基準を決めたりするために在庫分析は欠かせません。

ABC分析は、重要度や優先度に応じてランク付けし、管理を効率化するための分析方法です。

限られた時間や人員を重要な対象に集中させることで、効率的に最大の効果が得ることが目的です。

「パレートの法則（全体の数値の大部分は、全体を構成するうちの一部の要素が生み出しているという法則、80:20の法則ともいう）」に基づいて考案されました。

例えば、次のような場面で効果的です。

• 重点管理をする商品を決める
• 在庫削減をする際に、効果の大きい在庫を見つけて優先度を決める
• 滞留在庫を廃棄する際に、滞留期間が長く、在庫金額が大きい在庫を見つける

具体的には、在庫品目をA（最優先）、B（現状維持）、C（後回し）といったグループに分けます。

A、B、Cグループは割合で決めます。

• Aグループの目安：全体の0～80％
• Bグループの目安：全体の80~90%
• Cグループの目安：全体の90~100%

ABC分析は「パレートの法則（80:20の法則）」に基づいた分析です。

実際にやってみると分かりますが、例えば原因の多くを大半を

身近な例では、「テストの点数が70点台の生徒が一番多い」や「この部品の重さは10.0g〜10.2gの範囲に集中している」といったような使い方です。

在庫推移をみる「折れ線グラフ」を作ることはよくあると思いますが、あまりヒストグラムを作っている方を見たことはありません。

しかし、在庫推移からはわかりにくい情報が分かります。

例えば、次のような使い方です。

1回当たりの受注数を調べる

1回当たりの受注数や販売数と調達リードタイムがわかれば、大体どれくらいの在庫数を持っていれば良いかが分かります。

例えば、上記の商品（調達リードタイム＝10日）の場合、過去１年間の受注回数533回のうち、受注数＝１だった回数が501回でした。

安全を見て、受注数＝２も加えれば、99.2%をカバーできます。

つまり、10×2=20個の在庫を持っておけば、ほぼ99％、欠品することは無いと考えることができます。

納入リードタイムを調べる

仕入先の納入リードタイムを調べて、標準リードタイムの設定や納期短縮に役立てることも可能です。

上記の場合、発注してから14日以内に納品された割合が99.3%です。

つまり、納入リードタイムを14日に設定すれば、ほぼ納期遅れ無しで入ってくるといえます。

ちなみに、

• 納期短縮を目指す場合：リードタイム10日（全体の91.5％）に着目し、　4日短縮を目指す。
• 納期遅れの防ぐ：リードタイム20日以上に着目し、その理由を明らかにして原因をつぶす。

ヒストグラムは折れ線グラフと違って、少し工夫が必要ですがコツが分かれば簡単に作れます。

Zチャートの活用方法

• 経営分析：全社や事業部ごとの売上トレンドを把握し、経営判断に役立てる。
• 営業管理：営業担当者やチームの目標達成度や活動の効果を測定する。
• 在庫管理：商品の出庫数のトレンドを分析し、適切な在庫量を判断する。

Zチャートの作り方と具体的な活用法法は、こちらのページで解説しています。

➽Ｚチャートの見方と作り方

流動数曲線を見るだけで、次の3つの情報が一目でわかります。

1. 生産計画に対して、実際の生産がどれくらい遅れているのか？
2. 生産計画に対して、いくつ（数量）足りないのか？
3. 当初、計画していた生産能力と比べて実際の生産能力はどのくらいか？

一般的に生産管理は、システムが必要で、設定や運用も複雑で使いこなすのが大変です。

一方、流動数曲線であればエクセルだけで、生産管理が可能になります。

流動数曲線をお勧めする理由

1. 知りたい事が一目で分かるシンプルさ
2. 面倒な設定不要
3. 必要な項目は、生産計画数と実績数だけで、管理項目が少ない
4. 専用のITシステムが無くてもエクセルで実施可能

特に見込み生産品や部品メーカーにお勧めの繰り返し同じ製品を作る部品メーカーなどにお勧めのシンプルな生産管理（進捗管理）方法です。

在庫分析をするためには、次のステップで進めます。

分析の「目的」を明確にする

まず最初に、「何のために在庫分析を行うのか？」という目的をはっきりとさせます。

目的によって、必要なデータ、分析に使う指標や手法が異なります。

目的の例

1. 過剰在庫の削減：
   1. 必要なデータ：在庫金額
   2. 分析手法：ABC分析
2. 滞留在庫の削減や処分の検討
   1. 必要なデータ：在庫回転率（在庫回転率の計算方法はこちら）
   2. 分析手法：ABC分析
3. 標準リードタイムの設定、納期遅れの改善
   1. 必要なデータ：発注データ（発注日から納品日までの日数）
   2. 分析手法：ヒストグラム
4. 今年の売上を前年と比較
   1. 必要なデータ：売上データ
   2. Zチャート
5.  生産計画の精度アップ
   1. 必要なデータ：生産計画、生産実績
   2. 分析手法：流動数曲線

必要な「データ」を収集する

目的に合わせて、分析に必要なデータを集めます。

データは正確で、十分な量があることが重要です。

例えば、季節変動を調べる場合は、少なくとも3年分のデータが必要です。

結果を見るのではなくて「読む」

データ分析をする目的は、課題発見、仮説の検証（事前に想定していたことが思い通りになったか？）など結果から次の行動や判断を決めるためのものです。

ひとつ、在庫管理110番が支援した事例をご紹介します。

下記のグラフを見てください。

2023年度の8月は受注が急に増えて、そして9月～11月はいきなり急減していますが、その結果は見れば誰でにでも分かります。

このデータから読み取るべきことは、次の3点です。

1. 8月、そして9～11月に何が起こったのか？
2. 急増、急減は予測することができたか？
3. 同じようなことが起こったら、防ぐことができるのか？

営業担当者はこの結果を見て、「あ！もしかするとこれは値上げ前の駆け込み需要かもしれない・・・」とすぐに起こったことの原因が分かりました。

発注担当は値上げがあることを知らず、前年の実績を見て発注をしていたため、８月の急増を見て慌てて短納期の追加発注をしました。

しかし、その後反動で受注が急減して、在庫が一気に膨れ上がったという問題が発生しました。

この分析結果を反省点として、次に行動・判断で活かすのは、

• 値上げをする場合は、発注担当者に、該当商品の納入リードタイムを考慮して、事前に知らせる
• 受注は２～３カ月すれば元通りになる。発注担当者は、受注の急増・急減に慌てない。

この会社では、値上げなど、既存商品の価格を変える時は、必ず会議を持つことが決まりました。

在庫分析に必要なデータを大量にしかも安全に一元管理するには、エクセルでは不十分です。

なぜなら、エクセルは属人的な管理になりやすく、データを大量に貯めるようにできていないからです。

そこで、導入をお勧めしたいのがお勧めしたいのが在庫管理システムです。

成長する在庫管理システムは、生産管理機能が導入できるシステムです。

在庫管理110番がコンサルティングした会社でも在庫分析することが多いです。

自分達のデータにもかかわらず、「そんなことになっていたんですか！！」と、今までずっと今気づかなかった事実に驚かれいます。

そこで、研修として体系化しました。

例えば、次のような課題がある場合は、在庫分析はとても効果的です。

• データはたくさん蓄積しているけど、活用できていない
• 在庫分析を行っているけど、いまいデータの読み方が分からない
• 分析をしているが、成果を出せておらず時間をムダにしている

あなたの会社には販売や仕入、生産・・・といった日々の業務活動で生データを蓄積し続けているはずです。

生データはあなたの会社でしか活用できない貴重な財産です。有効活用できていますか？表やグラフを作るだけで満足していませんか？

在庫分析の「考え方、ノウハウ、データの読み方、短い時間で効率的にやる方法」が体系的に学べます。

在庫管理へご相談、お問い合わせは在庫管理110番へ！

在庫管理110番では、適正在庫に関するご相談を随時受付中です。 無料個別相談も実施中ですので、今回の記事で分からなかったこと、自社はどのようにすれば良いかイマイチ理解できなかったことなど、お気軽に在庫管理アドバイザーにご相談できます。 自社の状況、事例に合わせたアドバイスがもらえます。