在庫管理でのエクセルテンプレート

在庫管理で用いる在庫表をエクセルにしたテンプレートをご紹介します。入荷・出荷・在庫の３点は必須項目としてどの在庫表にも存在しますが、その他便利な指標として在庫日数や内示変動差、注残を管理できるタイプのエクセルを作って運用しているところもあります。在庫管理を効率的に実施するためには、正確な在庫表が必須です。理論在庫と実際の在庫に差異があると在庫表としては役に立たないものとなります。

• エクセルでの在庫管理表テンプレート（excelファイル、16.2KB）

在庫管理テンプレートファイルは上記からダウンロードできます。適宜、数字や計算式を打ち換えてご使用ください。

使い方｜指標の意味と活用方法

このサンプルでは、横軸に日付、縦軸に品種・品番・製品の種類をとって、一覧にしていますので１つのエクセルシートで数百を超える製品でも管理できます。入荷・出荷・在庫の必須指標が同時に見られるようになっており、フィルタをかけて適宜絞り込んで使うこともできます。ある時点の理論在庫がいくつなのか、１週間後の未来在庫がいくつなのかを知ることでき、いつ何個の発注をしないと欠品するか予測を立てることができ、ほとんどすべての在庫数量の管理に使うことができます。

エクセルでの在庫管理はコストが安くすみ、自分の管理したい指標を追加したり、合計数を比較検証したりといった様々な改良を施すことができる点にメリットがあります。半面、打ち間違えやセル内の計算式指定ミスなどのヒューマンエラーや業務が属人化してしまう、個々の裁量が大きいなどのデメリットもあるので、在庫システムでの運用にするか、excelでの運用かは業務実態にあわせて検討すべき事項となります。以下項目を具体的に説明していきます。

日付

この事例では１行目のE列から１０月１日としていますが、任意の日付でよく、最後列に日付を足して運用することもできれば、１カ月たったらシートやファイルを作り直すということもできます。このテンプレート例では１日を単位にしていますが、１週間単位など任意の間隔や土日を飛ばした日程にすることもできます。在庫運用上は１日単位のものが精度も高くなりますので使いやすいと思います。

ある基準日までに型落ち品を消化させて、新しいモデルのものに切り替えを行うといったシミュレーションにも活用できます。

出荷

出荷の予測数や出荷実績を記入します。出庫とも言います。未来日の部分については内示数や出荷予測・需要予測を日割りにした予測数を入力し、本日から過去については実績数を入力します。必要な情報としては、客先内示・営業からの出荷予測・販売予測、工場の生産計画といった情報と、出荷実績数となります。セルに計算式を入れて置き、月間の内示欄だけ数字を入れ替えると各日の数量が一斉に入れ替わるようにしておくと大量の製品の一括更新も簡単にできます。

出荷実績を取り込む方法はいろいろありますが、品目が少なければ手打ちでもよいものの、種類が多いと手間がかかったり、転記ミスにもつながるため、エクセル関数やマクロを活用して自動で流し込むものがよいでしょう。例えば、別シートに出荷実績をシステム等から取り出したデータを張り付けるとそれがこの在庫表の出荷欄の実績のある部分に差し替えられるというようなものです。

入荷

発注したものや生産指示したものが倉庫に入庫する数量を記載しますが、発注数を決めるファイルとして使う場合は、出荷と在庫を更新したあとに、最後にここに数字を入れていき、決めた基準在庫を下回らないように発注数を入れていくことになります。

在庫管理表の用途や目的により意味が変わってきますが、輸入品であればインボイスやパッキングリストの情報から入力する部分です。国内品は仕入れ先からの納品書や検収実績等から入力する項目です。自分で発注数を決める用途で使う場合は、この部分は発注する数字を入れて置き、別のシートに発注書の書式を準備しておき、そちらに関数などで自動転記されるようにしておけば、在庫管理表の入荷部分に入力した数字がそのまま発注書に転記されて工場や仕入先への注文が容易になります。

実際の運用では入荷した部分に色付けして、発注済みで入荷がまだないものと区別して管理したり、入荷が遅れた場合には、対象となる入荷数量の入ったセルにマイナスの計算式を入れて未入荷数を差し引いて管理する等ルールを決めておくことでより正確な運用ができるようになります。

在庫

前の日の在庫に、当日の入荷数を足し、当日の出荷数を差し引く計算式が入れてあります。前月末の実在庫が正しいという前提になるため、ここにもし不安があるなら実在庫が正しいかを検証する方法を検討する必要があります。

• 当日在庫数　＝　前日在庫数＋当日入荷数−当日出荷数

入荷してから出荷までに時間がかかる場合や、そもそも入荷したものがすぐに使えないタイプの製品などで、当日入荷数と当日出荷数で計算すると支障が出る場合は、適宜参照するセルを変更します。在庫数が一定以上ある場合はあまり気にする必要がないこともあります。

• 当日在庫数　＝　前日在庫数＋当日入荷数−２日後の出荷数
• 当日在庫数　＝　前日在庫数＋前日入荷数−３日後の出荷数

上記のように実情にあわせて簡単に変えることができるのはエクセル在庫表の大きなメリットの一つです。最初のセルだけ式をいれたら、あとはフィルハンドルをクリックして引っ張っていくだけでオートフィル機能によってすべての在庫セルに同じ法則での計算式を入れることができます。

安全在庫となる基準在庫を決める

欠品させないよう運用することが在庫管理の基本ですが、欠品させずに、最小の在庫で運用するというのが在庫管理者の腕の見せ所になります。在庫を潤沢に持てば、頻度の高いチェックも不要で、発注側の管理工数は下がりますが、反面、在庫数量が増えて倉庫がパンクしたり、在庫金額上がってしまいます。

在庫金額は各社のキャッシュフローに影響する要素でもあります。過剰在庫は、自社で様々な経営資源へ配分可能なキャッシュを減らしてしまうことを意味します。一度購入して在庫となると、ほとんどの製品は短期間では現金化できません。反対に在庫が少なすぎると自社や客先の生産ラインに必要な数を供給することができなくなります。この業務においては、生産管理が必要な情報を様々な利害関係者から集め、在庫のバランスをとる必要があります。

在庫を妥当な範囲で持つためには、安全在庫レベルを管理することが鍵となります。次月内示や出荷予測に対して何日分の在庫を持つかという基準や、当月内示に対して何日分の在庫を持つかという基準で安全在庫のレベルを決めることもできます。例えば、客先の次月内示が4000個で20日稼働日とした場合、1日に必要な個数は200個となります。安全在庫を14日で設定した場合、200 x 14 =2800個が基準在庫となります。

基準在庫には最低在庫と最高在庫を決める必要があります。というのも、入荷直前には在庫が最も少なくなり、入荷直後には最も在庫が多くなりますので、それぞれでの上限下限のリミットを決めておく必要があります。

基本的に基準在庫の設定というのは、在庫管理している品目や品番ごとに行う方法がよいです。「面倒なのでこの倉庫の中のものは一括で２日分に設定」というような方法だと各品目ごとに出荷動向・入荷動向が異なる場合、品目によって過剰在庫や過少在庫が発生して欠品というリスクに見舞われます。 品目が多すぎてどうしても個品ごとの在庫日数設定や最低在庫、最高在庫の設定が難しいという場合、例えば製品をA、B、Cの３つのカテゴリーに分類して、Aは１日分の在庫でよい、Bは３日分の在庫、Cは５日分の在庫などと設定する方法もあります。

安全在庫の設定方法には様々な要因を加味する必要があり、例えば以下のような要素があります。

• １．発注から納品までのリードタイム（例：最大日数、平均日数）
• ２．入荷までのリードタイム（例：発注してから入荷までのリードタイム）
• ３．入荷回数・入荷ロットと間隔（例：品物を週に1度受領、１００個単位、月に1度受領）
• ４．発注間隔（例：月に1度発注、週に1度発注、毎日発注）
• ５．振れのレベル（例：客先の内示、生産計画が計画に対してどのレベルで変動するか）
• ６．緊急調達時に必要な日数
• ７．品質保持期間（例：錆の為１カ月以上在庫できない等）
• ８．欠品許容率（例：在庫ショート時に数日は調整可、欠品不可）
• ９．キャッシュフロー（例：在庫購入に使える現金）

在庫表の確認

在庫管理表に使われる数字は毎度実地棚卸で得たものではなく理論値となります。このため、倉庫や物流拠点となるデポなどの在庫管理場所からの在庫表と、自分で作成した在庫表との間に差異がないか定期的に確認を行う方法も有効です。

契約しているデポや自社倉庫によっては、半期に一度の実地棚卸や月間、毎週の間隔で棚卸を行っているかと思います。棚卸を実施していなくとも在庫表と実在庫の間で差異が発生している場合、入荷、出荷、在庫数が間違っている可能性がある為、程度に応じて修正を行います。

スポンサーリンク

>このページ「在庫管理でのエクセルテンプレート」の先頭へ