在庫管理にエクセルを活用することは非常に有用です。
あなたも既にエクセルを活用して在庫管理をされているかもしれませんが、昨今の世界的な半導体不足の影響などにより、急な生産計画の見直しや、需要の変化によって受発注の管理が難しくなっています。
そこで、エクセルを使って数日先の在庫量を予測する方法をご紹介します。エクセルに苦手意識のある方、ご相談を受け付けておりますので、お気軽にどうぞ。
目次
エクセル在庫管理表の目的と作り方
もっと在庫を減らすべきだった！急な追加注文に対応できない！
そんな経験を在庫管理業務をしている人であれば誰もが一度は考えたことがあるはずです。在庫の適正管理は健全な会社経営には欠かせませんので、誰でも簡単に使うことができるエクセルを活用して在庫管理表を作りましょう。
エクセル在庫管理表の作り方ですが、至ってシンプルです。前もって在庫を予測すると聞くと未来予知のようで難しく思えてしまうかもしれませんが、順を追って解説しますのでお付き合い下さい。
在庫管理の項目と時間軸
エクセル在庫管理の基本は現在庫+入庫数-出庫数で表される在庫数が、時間経過とともにどの様に推移するかを見える化することです。
なのでエクセルの
- 列(縦軸)には入庫、出庫と言った在庫の動きを表す項目
- 行(横軸)には日にちや時間と言った時間経過
これで、在庫数×時間経過のマトリクス（在庫管理表）が完成します。
今回のテーマである在庫量の予測をするために必要な縦軸の在庫の動きを表す項目は次の６つです。
- 入庫(予定)
- 入庫(実績)
- 出庫(予定)
- 出庫(実績)
- 在庫(予定)
- 在庫(実績)
在庫管理表には予定と実績を別で記入する
予定と実績を分けて管理する理由は、予定と実績は乖離が発生するモノだからです。
在庫管理表において予定は非常に大事な要素です。
明日以降の入庫予定数や出庫予定数を仮に予定だとしても事前に把握しておかなければ、現在庫が将来の過不足に対して適正かどうかを判断ができません。
「予定は未定」で必ずしも予定通りに在庫が入庫・出庫されるとは限りません。予定なんて当てにならないと言えるかもしれませんが、全く予定無しよりはマシです。そこで、実績だけではなく予定も管理します。
将来の在庫数をIF関数を活用してミスを防ぐ
前述の通りしたようにある日の予定在庫数を求める計算式は、「前日在庫＋入庫ー出庫」です。
しかし、日々の実績を反映しながら、在庫の予定数量に補正をかける作業は、手間や見落としなどでミスの原因になります。
そこで活用してほしいのがIF関数です。
予定と実績を分けて、IF関数を使用して次のような管理表を作りました。
エクセル在庫管理表に予定入出庫数を事前にインプットをしておき、実際に入出庫された数を日々記録します。
⑤と⑥にIF関数を使っています。
⑤：事前にインプットした先々の予定入出庫数を加味して、将来の予定在庫の推移を自動計算する。
⑥：実績数を自動で入力する（②：入庫実績、④：出庫実績が入力されるまでは空白）
⑥をもっと具体的に言うと、
- 入庫または出庫実績の反映前：前日までの在庫数に、予定入庫数と予定出庫数を使って計算する
- 入庫または出庫実績の反映後：実績を使って計算をした在庫数を基に今後の在庫数を計算する。
このように、IF関数を使えば、予定と実績に差が生じた際の補正が不要になり、インプット工数の削減とともに入力ミス、入力漏れの恐れが無くなります。
ただ、今回のようにIF関数を使って複雑な条件設定をするのは、在庫管理の知識とともにエクセルスキルが必要です。エクセルや関数に苦手意識を持って方も多いと思います。あなたがやるべきことは、在庫管理であって、エクセルのエキスパートになることではありません。
そこで、エクセルの複雑な関数を習得するよりも在庫管理業務に集中したいという場合は、お力になれます、是非お問い合わせいただき、やりたいことをお伝えください。
アラート設定で見落としをしない在庫管理表にする
ここまでIF関数を使った、在庫の見通しを立てる方法を紹介しました。
エクセルで数字を表示するだけでは、見落としてしまうリスクもあります。
そこで、自動で教えてくれる「アラート設定」の方法もあわせて紹介します。
過剰在庫や発注点を条件付き書式でセル、文字の色を変えて知らせる
例えば、常に在庫を１００個～２００個で維持したい場合
- ２００個以上は倉庫の保管場所を圧迫するから減らさないといけない。（過剰在庫）
- １００個を割り込んだら追加発注準備をする（発注点）
条件付き書式を使用します。
条件付き書式とは、設定条件（例えば数量が100以下になる）になった時
に、セルの書式（文字の色やセル塗りつぶし）を変える機能です。
今回は、「セルの強調表示ルール」を選択します。
在庫がルール（設定条件）に該当する数に到達した際にセルの色を変えるようにします。
今回の場合は、
- 在庫が２００個を越えたら、文字色を赤色に、そしてセルを赤に塗りつぶし
- 在庫が１００個より少なくなったら、文字色を青色に、そしてセルを水色に塗りつぶし
このような条件付き書式を設定すると次のようになります。
在庫数の過不足が一目瞭然になりました。
条件付き書式を設定することで、見落とすリスクが各段に減ります。
見落としがあっては、せっかく在庫管理表を作った意味がなくなってしまうので、是非「条件付き書式」を活用をしてみてください。
IF関数を活用して警告文を表示して見落としを防止する
条件付き書式ではなく、先ほど紹介したIF関数を使って工夫次第では、見落としを防ぐことも可能です。工夫の一例をご紹介します。
IF関数で任意の警告文を表示する方法です。
- 在庫数が２００個を超えた時：「在庫を減らして下さい」という警告文を自動表示
- 在庫数が１００個より少ない時：「追加発注をして下さい」という警告文を自動表示
IF関数を設定すると次のようになります。
この場合、10/3の在庫数が２００個を超える予定なので「在庫を減らして下さい」という警告文を自動表示します。
これで、何日に在庫が過剰または足りなくなるかが分かります。
前述した通り、こちらのIF関数も在庫管理でエクセルを使用しているだけだと難しく感じると思います。
エクセル在庫管理表のメリットと注意点
最後にエクセルを使って在庫管理表を作るメリットと作成の際の注意点をお伝えします。
エクセル在庫管理表のメリット
エクセルを使った在庫管理のメリットは、無料ですぐに作れる点です。
今回ご紹介したようなエクセルの知識を持っていれば、エクセルで作るだけです。
エクセル在庫管理表を作成する注意点
関数の間違いなどが起こりやすい点です。間違いがあっては、見込んでいる在庫数が正しく計算出来なくなる可能性があります。そうすれば、実際の在庫に大きな乖離が出てしまうことになります。
はじめが肝心だとよく言いますが、在庫管理をする為のエクセルもはじめのフォームの作り込みが非常に大事です。
在庫管理についてまとめ
いかがでしょうか？
エクセルを活用すれば、できることがたくさんあることがお分かり頂けたのではないでしょうか？
しかし一方で、エクセルの機能に精通していなければ、今回のような管理表を作れるというアイデアすら浮かばないでしょう。エクセルは使用する人の力量に大きく依存します。冒頭でお話をした通り、健全な会社経営には在庫管理は欠かせません。あなたがやりたいのは在庫の適正管理であって、エクセルのエキスパートになることではないでしょう。
システムまでいらないが、せめてエクセルで適切な在庫管理をしたい・・・という場合は、ぜひお気軽にご相談ください。