「在庫を減らしたいが、商品が多すぎて手をつけられない」「商品が多く、発注作業ばかりに時間を取られる」在庫管理の担当者には、こんな悩みをお持ちの方も多いのではないでしょうか？
そんな時に役立つのが、「ABC分析」です。
ABC分析を使えば、商品の優先順位を明確化でき、在庫管理において最小限の労力で最大限の成果を生み出せます。
本記事では、ABC分析の必要性から実際のデータの作り方、データの分析、活用法までまるっと解説します。
- ABC分析の必要性
- エクセルを使ったABC分析のデータの作り方
- ABC分析の活用法
ぜひ最後まで読み、在庫管理の効率化を実現させましょう！
目次
エクセルを使ったABC分析で在庫管理の「優先順位」を明確化
ここでは、ABC分析がどのような目的で使われ、どのような考え方に基づいたものなのかを解説します。
ABC分析の必要性
ABC分析は、在庫管理において「どの商品を重点的に管理すべきか」を判断するのに必要なツールです。
ABC分析では基本的に、分析結果を以下のような形で表します。
グラフは棒グラフと折れ線グラフで構成されており、
- 棒グラフ：ある工場で扱う部品それぞれの在庫金額（単価×在庫数）
- 折れ線グラフ：全体の金額を100%とした時の累積比率
を表しています。
それぞれの商品を
- Aグループ：在庫金額全体の80％くらいを占めるもの
- Bグループ：在庫金額全体の20～80％くらいを占めるもの
- Cグループ：在庫金額全体の20％以下を占めるもの
に分け、Aから順に在庫管理の重要度が高いとするのが、ABC分析の考え方です。
これによりAグループの商品を重点的に管理すれば、最小限のリソースで総在庫金額の80%に対して効率的な改善が図れます。
パレートの法則（80：20の法則）
ABC分析の考え方は、イタリアの経済学者ヴィルフレド・パレートが提唱した「パレートの法則（80：20の法則）」に基づいています。
「パレートの法則」は「全体の80%の出来事は、20%の要因によってもたらされる」という法則です。
在庫管理では、「売上・在庫金額の80%は、20%の主力商品によるもの」となり、その20%であるAグループの管理に注力すれば良いのです。
エクセルを使ったABC分析のデータの作り方
ABC分析を最も簡単に行うには、エクセルを使うのがおすすめです。
ここでは、エクセルの通常機能を使ったABC分析の手順について説明します。
もし継続的に分析を続けたい方は、以下の動画を参考にエクセルのピボットテーブルを使用するのもおすすめです。
ピボットテーブルを一度作成すれば、以降はデータを用意するだけで済むため、より効率的にABC分析が可能です。
1：データ収集
まずは、ABC分析に必要なデータを収集しましょう。
本記事では、在庫削減に取り組みたいある工場が保有する部品A〜Lについて分析するとします。
分析対象の部品を決めたら、各部品の単価と在庫数を調べ、エクセルに入力します。
入力後、「単価 × 在庫数」の計算式を入れる列を作りすべての部品の在庫金額を算出し終えたら、データ収集は完了です。
2：金額順に並べ替え
次に、先ほど算出した在庫金額を並べ替えます。
「データ」タブの「ユーザー設定の並べ替え」を選択し、在庫金額の列を降順に並べ替えましょう。
すると、上から在庫金額が高い順に部品が並びます。
3：累積金額を計算
続いて、先ほど並べ替えた在庫金額を元に累積金額を算出します。
累積在庫金額とは、上からある特定の行までの在庫金額の合計です。
以下のような式を入力し、算出していきましょう。
4：累積金額比率を計算
累積在庫金額を算出し終えたら、それを元に累積金額比率を計算します。
各部品の累積金額比率は、「その部品までの累積在庫金額 ÷ 全部品の在庫金額の合計」で算出できます。
よって、一番下にある部品の累積金額比率は100%です。
ここまで終えれば、データの計算は完了します。
5：A・B・Cに分類
データを計算し終えたら、VLOOKUP関数を使うなどして、以下のように各部品をA・B・Cのグループに分類します。
- 累積金額比率が0~80%以内：A
- 累積金額比率が81~95%以内：B
- 累積金額比率が96~100%以内：C
※比率の区切りは一例です
分類ができれば、ABC分析は完了です。
6：グラフ化
表だけでは結果が一目でわかりづらいため、グラフ化がおすすめです。
ABC分析のグラフは、棒グラフと折れ線グラフの組み合わせで作成します。
まずは、先ほど作成した表を選択して、図のように「挿入」から棒グラフを選択します。
次に、グラフに必要なデータは在庫金額と累積比率のみのため、それ以外のデータ項目をすべて削除します。
続いて、2軸グラフにしていきます。
「累積比率」の項目を選択し、第2軸にチェックを入れて形式を「折れ線」に変更してください。
最後に、第2軸の表示を調整します。
グラフの「書式設定」を選択し、第2軸の最大値を1に変更しましょう。
これでABC分析のグラフは完成です！
7：分析
今回のABC分析では、この工場が在庫削減を行う場合、部品L・F・K・D・Gに重点をおいて対策を練れば良いとわかります。
全12部品のうちたった5部品（41.6%）に着目するだけで、在庫金額全体のうち80%に対して改善を行えるため、費用対効果は約2倍です！
ABC分析の活用法
ABC分析は、分析対象のデータを変えたり考え方を応用したりすることで、さまざまな場面に活用できます。
過剰在庫・滞留在庫・不良在庫の削減
回転日数（在庫が入れ替わる平均日数）を基準にABC分析を行えば、過剰在庫・滞留在庫・不良在庫の削減につながります。
この分析でAグループに分類された部品や商品は在庫が滞留しているため、仕入れ数や発注タイミングの見直しを行うことで、改善が見込めます。
仕掛品在庫の削減
製造業においては、仕掛品在庫（製造過程の途中段階にある製品）の削減も課題の一つです。
仕掛品在庫削減のためのABC分析には、2つの方法があります。
ひとつは、各工程の作業時間を基準にABC分析を行うことです。これにより、どの工程に絞って改善を行えば良いかがわかります。
もうひとつは、複数の段階にある仕掛品在庫それぞれの在庫金額を基準にABC分析を行うことです。分析の結果、在庫金額の高い仕掛品在庫を生み出している原因に絞って改善を行いましょう。
重要な商品・部品・顧客の決定／廃棄品の決定
ABC分析の考え方は、
- ABCといったようにグループに分類する
- グループにごとに対応を決める（グループの定義）
というものです。
これを応用すると、どの商品にどの程度リソースを割くべきか、また、どの商品を廃棄すべきかという判断基準を作成できます。
例えば、以下の図のように「単価」と「出庫頻度（流動性）」を組み合わせて、4つのグループの基準を定めます。
基準を定めたら、以下のようにグループごとの対策を決定します。
| グループ | 特徴 | 管理方針 | 目的 |
|---|---|---|---|
| a | 重点管理品（高単価・高流動） | 定期不定量発注を採用ベテランが担当 | 欠品と過剰在庫の両方を防ぐ |
| b | 要検討部品（高単価・低流動） | 経緯調査を実施取り扱いの継続か処分かを検討当 | キャッシュフローの改善 |
| c | 効率管理品（低単価・高流動） | 不定期定量発注を採用 | 発注コストと手間の削減 |
| d | 廃却品（低単価・低流動） | 早期処分・廃番 | 保管スペースの有効活用 |
グループごとの定義を定めたら、単価基準と出庫頻度基準で2回に分けてABC分析を行います。
- 両方でAグループに分けられた物：aグループ
- 単価基準ではAグループ、出庫頻度基準ではBまたはCグループに分けられた物：bグループ
と割り当てることで、各部品・商品にどうアプローチすれば良いかが明確化します。
上記の例では基準を単価と出庫頻度としましたが、これを売上や来店頻度などに変更することで、さまざまな分析が可能です。
エクセルを使ったABC分析をより活用するためには？
ABC分析を活用していくためには、データの蓄積が最も重要です。
また、そのデータをどの角度から分析するかも重要になってきます。
そこで、データの蓄積〜分析までをシステムやプロにお任せ・相談するのがおすすめです。
在庫管理システムの導入
エクセルでの在庫管理も可能ではありますが、エクセルはデータの保存数に上限があるため、長期間大量のデータを貯めるには不向きです。
一方、在庫管理システムを使えば、半永久的に効率の良い在庫管理を行えます。
在庫管理システムというと世の中にたくさんありますが、大事なのは「使い切れるシステムだけを搭載している」「現場の実務に合っている」ことです。
在庫管理110番が提供する「成長する在庫管理システム」は、使い切れることをテーマにしています。
それぞれの会社に合った機能をカスタマイズした独自のシステムを、低コストで導入可能なため、現場が使いやすいシステムを実現できます。
過去のカスタマイズ事例の紹介や30日間の無料トライアルも可能です！
まずはどんなことが実現できるのかだけ知りたいという方も遠慮なくご相談ください。
在庫管理アドバイザーに無料相談
在庫管理110番では、アドバイザーに直接相談ができる無料個別相談も実施中です。
「自社の課題がわからない」「改善を試みているがなかなかうまくいかない」という方は、ぜひご活用ください。
ABC分析×エクセルで、在庫管理を効率化
効率的な在庫管理を行うには、エクセルを使ったABC分析で在庫に優先順位をつけることが重要です。
むやみやたらに改善を目指すのではなく、最小限のコストで最大限の結果を生み出しましょう。
自社で頑張るだけではなく、プロの視点も取り入れたい、システム導入を検討したいという方は、ぜひ在庫管理110番にお任せください！
ABC分析の関連情報