エクセルで在庫分析｜分析〜販促の方法まで分かりやすく解説
最終更新：2023年7月7日
皆さんは日々の業務の中、売上増加や発注、在庫適正化など様々な目的で在庫分析をしているかと思います。しかしどんな方法で行えば良いか、分析した結果どうすれば良いかなどで困っていませんか？本記事では、エクセルでできる在庫分析の手法をご紹介いたします。
在庫分析とは
在庫分析とは、適正在庫を維持するために自社の在庫状況を正確に把握することです。
最適な在庫管理を行うために必須であり、その他にも商品の売れ行きの把握や発注数の算出、セールやVMD変更などの施策立案の際に必要になります。
在庫分析についてはこちらの記事で詳しく解説しています＞
在庫分析の５つの手法と役立つグラフを徹底解説！
在庫分析のメリットは在庫リスクを可視化できること
在庫分析の最大のメリットは、在庫リスクを可視化できることです。在庫リスクを可視化することで、以下のようなことが可能になります。
- 欠品による機会損失の抑制
- 残在庫の減少
- 不要な値引きの抑制
- プロパー消化率の向上 など
では、在庫リスクを可視化できることがなぜメリットになるのでしょうか？
そもそも、売上増加と在庫適正化は相反する関係性であり、下記のように両立は非常に難しいものです。
- 仕入れを増やせば売上も増えるが、在庫も増えてしまう
- 仕入れを減らせば在庫も減るが、売上も減ってしまう
小売業界にとっては積年の課題でありますが、在庫は事業を成長させる重要なポイントです。
在庫分析を行うことで在庫リスクをうまくコントロールできれば、効率よく売上を伸ばすことができ、小売事業は成長していくと考えることができます。
在庫は「売れる」「売れない」の2択ではない
企業規模に関係なく、商品の在庫リスクを下記のように評価をすることが多いのではないでしょうか。
- 売れていれば安心
- 売れていなければ危険
しかしこの考え方では在庫リスクを充分に判定できていません。
商品が「売れる」「売れない」の間に、元々は売れる商品だったのが何かしらの要因で売れなくなってきた商品たちがあるからです。
そして商品のライフサイクルは、「入荷」「売れ始め」「よく売れる」「だんだん売れなくなる」「全然売れなくなる」という過程をたどります。上記の「商品は売れる・売れないの2択ではない」を念頭に、ライフサイクルに合わせて在庫リスクを見ていくと、下記4つの状態に分けることができるのです。
- 新商品
- 隠れた売れ筋商品
- 売れ筋商品
- 不良在庫
在庫リスクを判断する上で重要なのは、在庫の状態は4つあるということを理解し、いかに「隠れた売れ筋商品」を「売れ筋商品」にするかを考えることが重要なのです。
在庫リスクをコントロールする2つのポイント
ではこの在庫リスクを上手にコントロールするためにはどうすれば良いでしょう。
ポイントは次の2点です。
- 在庫リスク悪化の兆しに気付くことができるか
- 正しい在庫リスク判定ができるか
そしてこの2点を満たすためには、SKUごとにそのSKUがこれからどれくらい売れそうなのかを予測する必要があります。これからも売れそうなら売れ筋商品、あまり売れなさそうなら隠れた売れ筋商品、全然売れなさそうなら不良在庫となります。
「予測するために高度な機能を備えたシステムを活用しなきゃいけないのか。」と思われるかもしれませんが、エクセルでできる方法をご紹介しますのでご安心ください。
エクセルでできる在庫分析の2ステップ
では、エクセルでできる在庫分析の手法についてご説明します。
移動平均法（※）を使うことで、2ステップで分析することができます。
※棚卸資産となる商品を仕入れた時点で平均単価を随時計算し、売上原価とする方法
ステップ1：直近2週間程度の販売数から1週間あたりの平均販売数を算出し、販売個数の基準を決める
ステップ2：基準をもとに以下4つに分類する
- 売れている
- 売れ始めている
- 売れなくなってきた
- 売れていない
このように在庫リスクを判定すると統一した基準のもと分類することになるため、時間をかけることなく全在庫の状態を掴むことができ、在庫リスクに応じた販促が非常にやりやすくなります。
【販促例】
- 売れている→優秀な商品なので、引き続き販促していく
- 売れ始めている→薄いOFF率にしながら、しっかり売り切れるように販促強化
- 売れなくなってきた→深いOFF率にして売り切っていく
- 売れていない→セールや福袋、アウトレットに回す
在庫分析は販促に活かしてはじめて効果が生まれる
分析後は在庫リスクに応じて販促を行っていくことが重要です。
まず不良在庫に分類された商品は今後売れる見込みがほとんどない商品です。そのような商品は、セールで値引きしてでも売り切っていくのが得策です。
ですが、不良在庫の削減を目的にしたセールは値引き幅が大きくなります。そのような値引きセールを何度も行ってしまうと、ブランド価値の毀損や値崩れ、安物買いのお客様が多くなり、長期的に見ると事業にとってはマイナス面が多くなります。
そのため、不良在庫の削減を目的にしたセールは1年に数回に留めておき、本質的には不良在庫化する商品を増やさないようにすることが重要です。
次に隠れた売れ筋商品です。これは新商品として入荷してから徐々に売れ始めた商品や、これまでよく売れていたが徐々に売れなくなってきた商品です。
こういった商品は、販促を強化すればまだまだ売れる可能性が高いですが、それをせずに放置してしまうと在庫リスクがどんどん悪化し、ある日突然不良在庫に変わってしまいます。
不良在庫化してしまうと売れる可能性が極めて低くなりますので、それは避けなければなりません。
これらの商品を販売するためには店舗間での在庫移動、VMD変更、メルマガ、広告、特集、ポイントキャンペーンを行ったり、小さな値引きを段階的に行ったりすることでなるべく早く消化してくのが効果的な方法です。
各施策については以下の記事で詳しく解説しております＞
在庫分析はツールを使って効率化しよう
エクセルでも分析はできる一方で、在庫分析は非常に業務負荷が高い業務です。
商品数が多いとエクセルが動かず作業が滞ってしまうこともある上、人によって見る指標や在庫分析のやり方が違うことで属人化の原因にもなりやすくもあります。
ですが、在庫分析自体は付加価値を生みません。在庫分析で出た結果を販促に活かすことで初めて価値を生みます。
そのため在庫分析は、ツール等を使い効率化していくことをおすすめします。
在庫を利益に変えるクラウドシステム「FULL KAITEN」は、在庫データを活用してEC・店舗・倉庫、全ての在庫をAIで予測・分析し、商品力はあるのに眠っている在庫を明らかにすることが可能です。
過去データをもとにSKUごとの完売予測日と売上貢献度（※）を算出し、在庫の質を4つに分類することで、どの在庫に対してどの施策を行えば良いかが視覚的に分かるようになります。
（※）売れたらどれだけ売り上げに貢献するかを算出した指標
例えば、Bestの商品は早く売り切ることができるため、追加発注してより売上粗利をとっていき、Goodの商品は売上貢献度は低いが、順調に売れているためこのままでも問題はないと判断することができます。
またBetterの左側にいる商品は、少し露出強化すれば消化を促進することが可能なため、VMDやメルマガでの露出強化を行い、右側の商品は薄いOFF率にすることで、余計な値引きを抑制しながら消化を促進する。
Badの商品は、売上貢献度が低く売れ行きも悪いため、値引きや販促など早急に対処が必要と見ることができるのです。
このように視覚的に判断できるようになることで、分析業務の効率化と属人化の排除が可能となります。
まとめ
- 在庫分析の最大のメリットは在庫リスクを可視化できること
- 在庫リスクをコントロールするためには、以下2点が重要
- 在庫リスク悪化の兆しに気付くことができるか
- 正しい在庫リスク判定ができるか
- 移動平均法を使うことで、2ステップで在庫分析が可能になる
- 分析後は在庫リスクに応じて販促を行っていくことが重要
【お役立ち資料】在庫と利益を適正化するポイント解説
メルマガ登録受付中！
