常に適正な在庫を確保しておくためにはどうすればいいのでしょうか。実は、データ分析を仕組み化してしまえば、労力をかけずに管理が可能になります。

「適正な在庫」をどう確保するか？

販売・仕入・在庫の数量の関係は以下のようになります。

販売数量 － 仕入数量 ＝ 在庫数量

適正な在庫を常に持っておくためには、この在庫数が、常にマイナスにならず過剰なプラスにならないように管理しておく必要があります。

実際の例を見ながら説明しましょう。

実例 / エクセルを使った在庫管理の方法

それでは、以下の実例をもとにを在庫管理の方法について解説していきます。

Case Study
手芸品の材料や素材を取り扱う企業G社では、このたびネットショップ内の商品の発送を外部委託することになりました。それにより、常に今ある在庫を確認して指示を出すことが必要となったため、在庫管理表の作成を行うことにしました。 適正な在庫数量とするためにも必要となってくるのが、現状をしっかりと把握することになります。

適正な在庫（正確には、売り逃しをせず、過剰在庫を持たない）にするためには、「仕入数量」について、適切なタイミングで的確な数量を追加していくことが必要です。

今回の例では、次の条件を前提としますが、自社に合った条件で同じように分析・管理ができますので、ぜひ自社に合わせて作成してみてください。

G社の事例における前提条件
・商品が小さいため、3カ月分の保管が可能
・各商品、発注してから1カ月で入荷可能
・送料をなるべく抑えるため、毎月1回在庫補充する

販売数量と在庫数量は、「結果」としてデータが保管されています。その「結果」をもとに仕入数量を分析する表を作成していきます。

そのために、まずは販売数量と在庫数量の各データを統合することが必要です。

次ページ図表1は、販売数量の元データです。ネットショップにおける販売データをCSV形式でダウンロードしたデータとなります。過去1年分の販売実績を今回は使用します（分析で使用しない個人情報等のデータは除いています）。

図表1　G 社のネットショップ販売実績データ

そして、図表2は在庫数量の元データです。こちらは商品を保管してある外部委託先のシステム（WMS）から現在の在庫状況をCSV形式でダウンロードしています。なお、WMSとは、Warehouse（倉庫）Management Systemの略で、「倉庫管理システム」と訳されます。

図表2　G 社のネットショップ在庫実績データ

図表1のF列と図表2のB列が「商品ID」として共通しています。したがって、図表2に図表1の販売数量を、このあと述べる「SUMIF関数」を使って紐づけます。

そして、在庫数量と販売数量から、仕入数量と仕入れるタイミングを算出します。その結果が図表3になります。

図表3　G 社ネットショップにおける在庫実績と販売実績

それでは、図表3のつくり方を順番に説明していきます。A列～F列は、図表2と同じ情報が入っています。

それでは、まずはセルG2に、次の式を記入します。

=sumif(図表75!F:F,図表77!B2,図表75!I:I)

※=sumif(範囲,検索条件,合計範囲)

このSUMIF関数もよく使う関数のひとつで、検索条件に合う数字の合計値を計算する関数です。複数条件を組み合わせて合計値を出すSUMIFS関数も覚えておくといいでしょう。

これで、同じ商品IDの販売数量を持ってくることができます。同じ式をコピー＆ペーストして、全商品について販売数量を計算します。

計算されたG列の数値は過去1年分の販売数量となります。

G社の場合は、毎月1回在庫補充すると決めたため、次に1カ月の平均販売数量を計算します。具体的には「＝G2/12」をセルH2に記入し、そのセルをコピー＆ペーストして、全商品における1カ月分の販売数量を計算します。

次にI列ですが、H列の結果をもとに、現在、何カ月分の在庫を保管しているのかを計算しています。販売実績がない商品もある可能性がありますので、セルI2に次の式を記入し、全商品に同じように式を挿入します。H列の数字（販売実績）が0で割り算ができない場合に「販売実績なし」と記入されるようIFERROR関数を用いています。

=iferror(D2/H2,”販売実績なし”)

さて、G社では、「各商品3カ月分の在庫を保管する」「発注してから1カ月で入荷できる」という前提条件があったかと思います。そこで、J列に、在庫補充するべき商品についてフラグを立てるようにします。このフラグをもとにフィルタリングすることで、一目で在庫補充すべき商品を確認できるようにするためです。

前提条件に加え、G社では、次の条件も加えました。

• そもそも在庫数量が3未満となったら、在庫補充（発注）する

そして、発注してから1カ月で入荷できるので、在庫数量が1.5カ月分を切ってしまったら「要発注」とし、2.5カ月分を切ったら「発注準備」とするフラグを立てるようにしました。

それらを踏まえて、セルJ2に次のように入力しています。

= if(G2=0,””,if(D2<3,”要発注”,if(I2<1.5,”要発注”,if(I2<2.5,”発注準備”,””))))

この式を左から日本語訳すると、次のようになります。

• 過去1年間の販売数量が0だった場合は発注せず
• 過去1年間の販売数量が1以上かつ総在庫数が3未満だった場合は要発注とし
• 過去1年間の販売数量が1以上で総在庫数が3以上でも残りの在庫期間が1.5カ月分を切っても要発注とし
• 過去1年間の販売数量が1以上で総在庫数が3以上で残りの在庫期間が1.5カ月以上2.5カ月未満となったら発注準備とし
• その他の条件であれば発注しない

そして「要発注」および「発注準備」のフラグが立った商品について、K列に発注数量を記入する計算式を入力します。

G社の場合は3カ月分の販売数量分の在庫を保管するため、H列で計算されている月間販売数量の3倍の数を記入できるようにします。ただ、その数が10未満であった場合には、最低10在庫を補充するようにしました。

その結果、セルK2には次の式を記入しています。

=IF(J2=””,””,IF(H23<10,10,ROUND(H23,-1)))

「ROUND関数」は、数字の桁数を指定する関数です。小数点以下を四捨五入する際によく使いますが、今回は10未満の数を四捨五入する計算式としています。

あとは、セルJ2とセルK2にある式をコピー＆ペーストして全商品に反映させることで完成です。図表4は、図表3のJ列において、「要発注」と「発注準備」のみにフィルタリングした表になります。

図表4　G 社ネットショップにおける在庫実績と販売実績（発注商品のみフィルタリング）

この表を見て発注指示をして在庫補充していくことになるのです。

G社の場合は、毎月この業務を実施しています。いったん図表77を作成してしまえば、元データである図表1と図表2の表を更新するだけ（元データを貼りつけ変えるだけ）で、自動的に計算されます。1回目は関数などを多少は考えて作成する必要はありますが、その後は業務効率が格段に上がり、間違いも激減します。

ぜひチャレンジしてみてください。このように、エクセルを活用して、日々の業務管理も簡単にできるのです。

近年では、「BIツール」と呼ばれる、「業務における数値の見える化」をうたったツールがたくさん出ています。しかし、残念なことに、ツールを入れることが目的化してしまっており、本来の「業務効率アップ」という結果とはならないケースをよく見かけます。

重要なのは、「そもそも何の業務のためのどんな帳票が必要なのか」という部分を明確にすることです。そして、それはすべてエクセルで実行することができます。むしろエクセルで実行できないことは、どのツールを入れても実行できませんし、そもそもその業務の効率化の仕方、作成したい帳票が間違っている、といえます。

まずはエクセルで帳票を作成してみてください。システムが組まれたツールは簡単に直せませんが、エクセルであれば簡単に直すことができます。「一度エクセルで運用してみてから、よりスピードを上げるためにツールを導入する」というプロセスが最善だと、私は考えています。

＼ この記事を読んでいる方におすすめ！ ／