ABC分析とは？エクセルのやり方を覚えれば在庫管理が楽になる

実店舗で限られたスペース内に商品を保管している場合、適切に在庫管理を行うのは重要です。ニーズに対して在庫が少なく品切れを起こせば、大きな機会損失につながります。

一方で、日常的に在庫と売れ行きを把握し、頭を悩ませながら発注を行うのは非効率かもしれません。

こちらでは、在庫管理が大幅に楽になる「ABC分析」をエクセルで実施する方法についてわかりやすくお話します。

そもそもABC分析とは？何ができるの？

ABC分析とは、複数のデータを「重要度」にもとづいて分類する方法です。重要度が高い順にA、B、Cと分類することからABC分析と名付けられています。重点分析という名称も一般的です。

ABC分析はさまざまなデータの分類に活用されます。

代表的な例が在庫管理です。複数の商品を並べ「売上」を重要度としてABC分析を実施すると、総売上高への貢献度が高い商品と貢献度の低い商品がわかります。このABC分析の結果にもとづいて、在庫の補充数や取り扱い中止する商品を決められます。

以下は生鮮食品店の売上データから、ABC分析を行った例です。

売上への貢献度が大きい重要な品目はAランクに格付けされます。Bランクの商品はAランクより一段階重要度が低い商品です。Cランクはさらに一段階重要度が低いことを意味します。

ABC分析の指標の元となるパレートの法則

ABC分析に関連する重要な考え方が、「パレートの法則」です。

商品の売上高の例では、少数の商品の売上高が総売上高のほとんどを占めているケースは少なくありません。このような経験則をパレートの法則といいます。

商品管理だけではなく、多くの経済、ひいては自然現象や社会現象にまであてはまる法則です。

エクセルABC分析のやり方を解説

エクセルでは、簡単にABC分析が可能です。さまざまな方法がありますが、以下では売上を重要度として商品のABC分析を行う場合の、一般的な例をご紹介します。

エクセルABC分析のやり方①品目・売上高・累積売上高のデータをつくる

品目ごとに売上高のデータを入力します。この際、データの並びは意識しなくてかまいません。

B列には売上高が入力されていますが、まず売上高が大きい順に並べ替えておきましょう。B列を選択した状態で、「ホーム」のリボンメニューから「並べ替えフィルター」で「降順」を選んでください。

 上から下へと売上高が下がっていく順番に品目のデータが並びました。

累計売上高を入力していきます。累計売上高はその商品よりも売上高が高い商品すべての売上高を加算した数値です。エクセルでは、売上高と累計売上高のセルを引数としてSUM関数を使えば簡単に求められます。

一つ一つのセルに関数を入力してもかまいませんが、オートフィルを使うと楽です。最初の関数を入力したセルの右下をクリックし、真下に向かってドラッグすれば、自動的に同様の関数が他のセルにも入力されます。

続いて累積売上割合を入力します。累積売上割合は、累計売上高が全体の売上高に占める割合です。

全体の売上高とは、つまり売上高最下位の品目までの累計売上高です。エクセルでは、それぞれの累計売上高を最下部の累計売上高で割る数式を入力すれば求められます。この際もオートフィルを使うと手間がかかりません。

エクセルABC分析のやり方②ランク付けの指標

続いて、ABC分析にもとづくランク付けを行いましょう。
A、B、C、それぞれのランクの条件を決める必要があります。今回は、累積売上割合が0～80%までの品目をA、80～90%の品目をB、90～100%の品目をCとしてランク付けします。エクセルで計算を行っている場合、条件をセルに記載しておくとわかりやすいかもしれません。

データが少ない場合は一つ一つ分析結果を入力してもかまいませんが、vlookup関数を用いると簡単です。以下のような関数をE2セルに入力します。

=vlookup(D2, $G$2:$J$4, 4, TRUE)

オートフィルで下のセルにも同様の関数を入力してください。

すべての品目に対して、ABC分析の結果が入力されます。

VLOOKUP関数について、詳しく知りたい方は「ExcelのVLOOKUP関数を使いこなして初級者から中級者へ！」の記事をご覧ください。

ABC分析で在庫管理が容易に

ABC分析によるランク付けは、商品の在庫管理や営業対策の策定に活かせます。
以下は、ABC分析によるランク付けに応じた管理方法や対策、対応の例です。

Aランク
極めて総売上への貢献度が大きい商品・顧客です。在庫管理では十分な保管場所を確保し、品切れが起きることのないように発注を行います。

Aランクに該当する顧客は離れることがないよう関係を維持すると同時に、一つでも多くAランクの顧客を増やしていく施策が大切です。

Bランク
Bランクの商品は適切に販売予測を行い、定期的に、もしくは在庫切れを見計らって発注を行うのが一般的です。

営業では、Cランクと判別された顧客への訪問回数を減らし、Bランクの顧客への訪問回数を増加させることで関係性の強化を狙います。施策次第では、Aランクへと押し上げられるかもしれません。

Cランク
限られたスペースの在庫管理において、Cランクの商品はごく少数の確保で十分だと考えられています。品切れの場合は機会損失が起こりますが、総売上への影響はごく小さなものです。取り扱いの中止も選択肢に入ります。

一方、在庫管理コストが実店舗よりも低いインターネット通販などでは、Cランクの商品も重要視されます。ニッチな商品を取り扱っていることで評価されるECサイトは少なくありません。Cランクに属する多数の商品の売上が総売上に少なからず貢献する現象を、「ロングテール理論」といいます。
Cランクの顧客は「重要度が低い顧客」と判別され、対応や訪問のリソースを軽減します。その分のリソースをAランク、Bランクの顧客対応に配分可能です。

商品や顧客の重要度は、感覚や印象によって決められがちです。
ABC分析を用いれば、主観に左右されない数値によって重要度が“見える化”されます。事業には、直観も重要な一方で冷静な分析が必要になるシーンは少なくありません。

消費や顧客の重要度を見つめなおしたい場合は、ABC分析を活用してみてください。