エクセルで作るクロスABC分析で儲かる商品を見つける方法

「粗利とはビジネスのガソリン」といわれるように、キャッシュの源泉となる粗利はビジネスの原動力となります。

ビジネスの基盤を太く安定したものにするためには、粗利益の厚い商品・サービスを重点的に販売する、これに尽きます。

そこで活用したいのが「クロスABC分析」です。

クロスABC分析を使うことで

1. よく売れる商品・サービス
2. 粗利益の厚い商品・サービス

という２つの視点からランク分けすることができ、「今何に力を入れて販売すべきか」を、客観的なデータとして観ることができます。

この記事では、エクセルを使った「クロスABC分析」の方法をご紹介します。

※エクセルは２０１６を使用

交差比率を使って一番儲かる商品を見つける方法

動画解説

動画での解説はこちら↓テキストでの解説はこのまま読み進めてください。

データを用意

次のような飲食店の販売データがあったとします。

このデータをクロスABC分析でランク分けしていきます。

ABC評価の境界値は、A評価を累計構成比の７０％以下、B評価を９０％以下と判定します。

売上と粗利の累計構成比を出す

まず、売上累計構成比と粗利益累計構成比を出していきます。

売上累計構成比の先頭となる「D5」セルに、「=SUM(D4,B5/$b$2)」と数式を入れます。

セル「D5」に数式を入力したら、オートフィルで「D15」までドラッグして入力します。

同じように粗利益累計構成比にも数式を入れます。セルは「E5」、数式は「=SUM(E4,C5/$E$2)」です。

セル「E5」に数式を入力したら、オートフィルで「E15」までドラッグして入力します

売上累計構成比と粗利益累計構成比の入力が完了しました。

ABC評価を付ける数式を入力

次にABC評価をしていきます。

使う関数は「IF」です。

IF関数は「ある条件が”○○”なら”■■”にする。”○○”でないなら”▲▲”にする。」と、「条件を満たす場合」と「満たさない場合」で処理を変える場合に使用します。

ここで使う数式は「=IF(D5<=70%,”A”,IF(D5<=90%,”B”,”C”))」です。

これは要するに、７０％以下は「A」と付け、９０％以下は「B」「C」にしろという数式です。

この数式をセル「F5」に入れ、オーフィルで「G15」までドラッグして入力します。

順位の変動に対応させる

売上高の降順に並べ替えて、売上累計構成比をもとに仕分けしたABC評価をつけます。

ただ、このままだと粗利益累計構成比を並べ替えたときに、売上累計構成比の順序が変わってしまいます。

そこで、評価の変化を防ぐため、「コピー＆値」のペーストで式から値に変更し、順序がかわらないようにします。

１）売上の列のセル「B5」をクリックし、「データ」→「並び替え」→「降順」をクリックします。

並び替えの更新に伴って、売上累計構成比と売上評価も更新されました。

次に、粗利益構成比を「降順」で並び替えたときに、売上累計構成比と売上評価が動かないとように数値を「値」に変更します。

２）セル範囲「F5:F15」まで選択しします。次に「ホーム」→「貼り付け」→「値の貼り付け」→「値」をクリックします。

粗利益累計構成比と粗利益評価も同じ作業をします。

１）粗利益の列のセル「C5」をクリックし、「データ」→「並び替え」→「降順」をクリックします。

並び替えの更新に伴って、粗利益累計構成比と粗利益評価も更新されました。

次に、売上累計構成比と売上評価でした手順を繰り返します。

２）セル範囲「G5:G15」まで選択しします。次に「ホーム」→「貼り付け」→「値の貼り付け」→「値」をクリックします。

粗利益累計構成比の列も同じ作業で、数式を「値」に変更します。

これですべてのABC評価がつけられました。

クロスABC分析での評価

AA評価

「かけうどん」「ホルモン炒飯」「かつ丼」は、売上も良く粗利益もとれる商品です。お店が推していきたいメニューになります。メニューブックの目立つところやPOPで積極的に販促していけば、さらに利益が残りやすくなります。

AB評価

「キムチ炒飯」「そぼろごはん」は売上がよくて、粗利もぼちぼちの商品です。原価を落とす努力をすれば、「AA評価」の商品に化けます。

BB評価

「ざるそば」は売上も粗利も中間評価です。告知回数を増やしたりして販売を頑張れば「AB評価」に行く可能性はあります。

BC評価

「和風ユッケ」「かけそば」「和風クッパ」は売れてない商品ですが、粗利はB評価です。売れると粗利益は取れるので、セットメニューなどを考えて販売数が伸びないか検討してみても良いでしょう。

CC評価

「メンチカツ」「串カツ」は、売れないし粗利も低い商品です。撤退を考えてもいいでしょう。

まとめ

クロスABC分析をすることで、何が売れて何が利益を取れているかが一発で判定できます。

「あの商品だろう」と感覚でやっていると、利益の取れない商品を一生懸命販売したり、その反対に売れる商品や粗利の取れている商品へ積極的に販促を行わず、大きな機会損失を起こしてしまうことにもなります。

やみくもにやっても良い結果は生まれません。

客観的に観れるデータがあって、正しい選択を行えます。

クロスABC分析を活用して、儲かるお店を目指しましょう。

エクセルを使えば、そのほかにも効果的な分析ができます。

曜日ごとの売上を予測する計算方法

エクセルデータ分析で売上高を予測する方法

エクセルで「季節指数」を算出して、チラシの反響を予測する方法

広告費から集客数を予測するエクセル分析法

アンケートで改善点と優先順位を見つける方法～エクセルポジションマップの作り方～

「集客できる商品」をエクセルで割り出す方法