在庫管理において、在庫分析をすることは、コスト削減や欠品防止に欠かせない取り組みです。
しかし「専用システムを導入するほどでもない」「まずは手軽に試したい」と考えている企業も多いのではないでしょうか。
そんなときに真っ先に候補として挙がるのがエクセルを利用した在庫分析ではないでしょうか。
在庫分析をエクセルでする際に使える機能や代表的な在庫分析手法、在庫分析をエクセルで効果的に分析するコツをわかりやすく解説します。
在庫分析はエクセルでできる？
エクセルは在庫分析のツールとして十分に活用できます。
在庫分析は、単に現在の在庫数を数えるだけではなく、入出庫の履歴や販売データをもとに傾向や兆候を読み解く作業です。
エクセルには、データを計算・整理し、視覚化するための豊富な機能が備わっているため、中小規模の在庫であれば、有効な分析ツールとなります。
ただし、専門的なツールに比べると制限もあるため、エクセルでできることを十分に理解したうえで活用することが重要です。
在庫分析をエクセルでするときに役立つ機能
エクセルで在庫分析を行うには、分析用の豊富な機能の活用が必要です。
在庫データの集計や可視化の分析に役立つエクセルの機能を紹介します。
関数
関数は、在庫分析のための計算処理を自動化する機能です。
在庫分析では、合計を求めるSUM関数や、条件に合うデータだけを合計するSUMIF関数がよく使われます。
また、VLOOKUP関数を活用すれば、マスターから商品名や単価を自動で引っ張ってくることが可能です。
こうした関数の活用により、手計算のミスを防ぎ、正確な分析が実現します。
条件付き書式
条件付き書式は、指定した条件に当てはまるセルの色や文字の装飾などを変更する機能です。
例えば、過去3ヶ月の平均出庫数に対して在庫保有日数を算出し、その数値に応じてセルの色をグラデーションで表示させる「カラースケール」を適用します。
これにより、過剰在庫となっている商品と、欠品リスクが迫っている商品がヒートマップのように一目で把握可能です。
このように、数値の強弱を視覚化することで、データに埋もれた課題を浮き彫りできます。
ピボットテーブル
ピボットテーブルは、大量のデータをさまざまの切り口で集計・分析できる機能です。
例えば、月別の入出庫データを「カテゴリ別」「倉庫別」「担当者別」などで、ドラッグ&ドロップだけで簡単に表にまとめられます。
ひとつずつ関数を組む必要がないため、関数が苦手な方でも高度な分析ができ、さまざまな角度から在庫の傾向を探る際に有効な機能です。
グラフ
グラフ機能は、数値データの推移や内訳を視覚的にわかりやすく表現できるツールです。
在庫分析では、在庫数の推移を見る「折れ線グラフ」や、売上構成比を把握するための「円グラフ」、各カテゴリーの在庫金額を比較する「棒グラフ」などがよく使われます。
グラフはレポートや報告資料への活用にも適しており、経営層への説明資料としても効果的です。
エクセルでできる在庫分析の代表的な手法
機能を理解したら、次は具体的な「分析手法」を見ていきましょう。
エクセルでできる在庫分析の代表的な手法を紹介します。
ABC分析
ABC分析とは、商品を売上金額や販売数量などの重要度に応じてA・B・Cの3つのクラスに分け、それぞれに適した管理方法を適用する手法です。
一般的に、売上の大半を占める主力商品をAランク、中間のものをBランク、売上貢献度の低いものをCランクとします。
エクセルで累計構成比を計算し、棒グラフと折れ線グラフを組み合わせたパレート図を作成することで、どの商品を重点管理すべきかが明確になります。
在庫回転率・在庫回転日数
在庫回転率は、「売上原価 ÷ 平均在庫金額」で求められる、一定期間に在庫が何度入れ替わったかを示す指標で、数値が高いほど在庫が効率良く利益に結びついていることを意味します。
一方、在庫回転日数は、現在の在庫が何日分の販売量に相当するかを示し、短いほど資金効率が良い状態です。
エクセルでこれらの数値をグラフ化することで、動きの鈍い「滞留在庫」を早期に発見し、適正な仕入れ量の目安を立てられます。
欠品率
欠品率とは、顧客からの注文に対して、在庫不足で応えられなかった割合のことです。
「欠品回数 ÷ 全注文回数」で算出でき、数値が高いほど販売機会のロスや顧客満足度の低下につながっていることを意味します。
エクセルで日々の注文データと在庫引き当てデータを照合し、欠品率をモニタリングすれば、適切な安全在庫の設定に役立てることが可能です。
交差比率
交差比率とは、「在庫回転率 × 粗利益率」で算出される、在庫の「儲かりやすさ」を示す指標です。
在庫回転率が高くても利益率が低ければ儲けは少なく、逆に利益率が高くても売れなければ意味がありません。
エクセルで商品ごとにこの交差比率を算出して比較することで、「本当に儲かっている商品」を客観的に見極め、陳列スペースの配分や販売促進の優先順位を決定する際の判断材料になります。
Zチャート
Zチャートとは、「月次売上」「売上累計」「移動年計」の3本の折れ線グラフを重ね合わせたもので、その形状が「Z」に見えることからこう呼ばれます。
Zチャートの作成により、単月の売上の上がり下がりだけでなく、長期的な成長トレンドの視覚化が可能です。
季節要因に左右されず、商品の本当のライフサイクルを見極められ、将来的な在庫計画の立案に役立ちます。
在庫分析をエクセルで効果的に行うコツ
エクセルで在庫分析を行う場合、ツールの機能や手法を知っているだけでは不十分です。
分析を適切に行うためには、運用設計の段階から意識すべきポイントがあります。
在庫分析をエクセルで効果的に行うコツを見ていきましょう。
分析を見据えてシートや項目を設計する
在庫分析の精度は、データ入力の段階での設計に大きく左右されます。
例えば、商品コード・商品名・カテゴリ・入庫日・出庫日・数量・単価・仕入先などを項目として定義しておくことで、後々の集計や分析がスムーズになるでしょう。
後から必要な項目を記録しておけばよかったとならないよう、あらかじめ分析に必要な項目を洗い出してシートを設計することが重要です。
入出庫データを正確に蓄積する
どれほど高度な分析手法を用いても、元となるデータが間違っていれば正しい結果は得られません。
「データの入力規則」を活用して、誤った数値や日付が入力されないように制限をかけたり、VLOOKUP関数を使って手入力を減らしたりする工夫が有効です。
また、定期的にデータと実在庫の照合（棚卸し）を行って、正確性を保つようにしましょう。
わかりやすいアウトプットにする
分析の結果は、自分だけでなく関係者が見てもすぐに状況が理解できる形で出力することが重要です。
数値の羅列だけでなく、前述の条件付き書式やグラフを積極的に活用し、視覚的なわかりやすさを追求しましょう。
分析はアウトプットして活用しなければ価値を生みません。
「誰に、何を伝えるか」を意識したレポート設計を心がけましょう。
在庫分析をエクセルからステップアップする方法
エクセルを使った手作業での分析に限界を感じ始めたら、次のステップへ移行する時期かもしれません。
エクセルでの経験を活かしつつ、在庫分析をエクセルからステップアップする方法を紹介します。
マクロ（VBA）を活用して自動化する
エクセルでさらに効率化を進めるなら、マクロ（VBA：Visual Basic for Applications）の活用が効果的です。
マクロを活用すると、複数のシートからデータを集めて集計し、特定の書式でレポートを出力するような一連の作業をボタン1つで自動実行できます。
これにより、作業時間の短縮とミスの削減が可能です。
ただし、作成者に専門知識が必要であり、メンテナンスが属人化するリスクには注意しましょう。
在庫管理システムを活用する
エクセルでの分析に限界を感じたり、エクセルで分析機能を自作するのは面倒だと感じたりする場合には、専用の在庫管理システムの活用がおすすめです。
在庫管理システムには、入出庫や在庫一元管理、棚卸などの標準機能に加えて、蓄積したデータを活用した豊富な在庫分析機能を備えた製品もあります。
エクセルでの自作の仕組みではなく、多くの企業で活用されている業界標準の分析機能を活用するなら、在庫管理システムを検討すると良いでしょう。
BIツールを活用する
在庫データと販売管理システムや会計システムなどのデータを組み合わせて、より高度な分析を行いたい場合にはBI（ビジネス・インテリジェンス）ツールの導入が効果的です。
BIツールは、企業内に点在するさまざまなデータを自動的に収集・統合し、高度なデータ視覚化を可能とします。
リアルタイムで在庫回転率や利益率の推移をグラフィカルに表示できるため、データに基づいた迅速かつ正確な経営判断が可能です。
在庫分析はエクセルよりzaico
エクセルは、在庫分析を手軽に始められる優れたツールです。
関数やピボットテーブル、グラフなどの機能を駆使すれば、ABC分析や在庫回転率の算出など、在庫管理に役立つ分析ができます。
在庫分析には正確な在庫データの蓄積が欠かせませんが、そのためには在庫管理システムの活用が有効です。
在庫分析のための正確なデータ蓄積を行うなら、「クラウド在庫管理システムzaico」の導入をご検討ください。
zaicoは、スマートフォンをスキャナーとして利用できるため、現場でバーコードを読み取るだけでデータがリアルタイムに更新されます。
これにより、エクセル管理で懸念される在庫のズレを最小限に抑えることが可能です。
在庫データのエクスポート機能も標準搭載しているので、必要な情報をピンポイントで抽出して自在に分析できます。
在庫分析のエクセルからのステップアップをお考えなら、zaicoまでお気軽にご相談ください。
