道具としてのExcel活用
Excelで在庫管理［基本的な考え方＋マクロ前提の管理手法5選］
在庫管理とは
意外と説明できない、在庫管理
在庫管理とは何かと問われて、自分の言葉で説明できる方は、果たしてどれだけいるでしょうか？
「そんなもの、在庫を管理することに決まってるじゃないか」と叱られそうですが、「管理する」の具体的な内容が、100社あれば100通りあるので、一筋縄ではいきません。
なぜそんなにバリエーションがあるかといえば、管理対象物の特性によって考え方が大きく異なるのと、在庫を「管理」することで実現したいことが異なるからです。
100社あれば100通りの在庫管理手法
例えば、ある会社では、在庫している原材料が途切れないようにするのが「管理の目的」ですし、別の工場では過剰在庫が過大な資産負担となっているので極小化したいのが「管理の目的」、また別の会社では今取り扱っている商品が何かを即答できるようにしておくのが「管理の目的」・・・というように、目的からして大きく異なります。中には「何がいくつあるか知りたい」というニーズもありますが、「管理」というより「現状把握」です。
このため、万人に通じるような在庫管理の定義というのは難しいのですが、「在庫しているモノを、あるべき適性値に近い状態に維持すること」ということになりましょう。この「適正値」が目的によって「一定数を割らないようにする」のか「一定数を超えないようにする」のかが変わってきます。
在庫管理の基本は、入出庫のみ
どんな在庫管理手法においても共通しているのは、「今、何がどこに何個ある」かを把握できることです。
前提として、在庫は常に変動しており、その原因は「出庫」すなわち販売などにより持ち出した分と、「入庫」すなわち補充です。
例えば、商品A，B，Cを100個ずつ仕入れて販売したとして、初日にそれぞれ10，20，30個、翌日15，20，35個、３日目に15，15，35個売れたとすれば、A＝10+15+15＝40個、B＝（同様に）55個、C＝100個売れたことになります。そうすると、元々100個ずつあったわけですから、残りはそれぞれ60,45,0個となります。Cはこれ以上売れないので、仕入れ発注を行い、到着したらその分を在庫として追加する・・・の繰り返しです。
あとは、拠点間移動や減耗などによる廃棄ロス分の考慮が必要になるケースがありますが、いずれも入出庫の延長、即ち「増えるか減るか」で説明できます。
在庫管理システムが難しく感じる理由
そうはいっても、実際には「入庫がダンボール単位で出庫がケース単位」「入出庫の単位が相手先によりまちまち」「商品入れ替えが激しすぎて、どこにあるのかいつも分からない」・・・のように、少しずつ特殊な事情が積み上がることで、それを同反映すべきか、また他への影響がどう及ぶかといったことが分かりづらくなるのです。特に、「どこに」については、格納する棚の構造や置き方にも依存するため、情報システムだけで完結する話ではないことが、多いのです。
「在庫だけ管理」はメリットが少ない
在庫は必ず、他の業務に繋がる
そもそも何のために在庫しているかと言えば、「販売するため」「製造するための原材料として使うため」･･･のように、必ず次の工程があります。倉庫業であっても、「単に預かっているだけで、出したいと言われるまで保管するだけ」というケースは少なく、「預かったモノを、指定された単位で指定された相手に指定日時に届くように出荷する」ことが目的だったりするので、「次の工程」を意識することが肝要です。
組み合わせ方次第で、こんな使い方も
例えば製造工場では、生産計画に基づいて製造が行われますが、何を作るかによって使う原材料や部材の在庫が減少していきます。同じ原材料が複数の製品で使われることも、珍しく無いため、重要な原材料が不足すると、生産ラインが止まってしまいます。かといって、大量にストックしておくと、会計上は資産計上になってしまうため金融機関から煙たがられる「不良資産」予備軍と見なされます。
つまり、発注～仕入れまでのリードタイムを考慮しつつ、在庫が払底する直前に補充されるようにするのが、一番コストが抑えられることが多いので、情報システム化する場合は、こうした方針を目指すのが一般的です。
とはいえ、輸入資材などでは、発注してから仕入れまでに何ヶ月もかかることがあるため、フォーキャスト（≒生産計画）を基に、先に発注する必要があるケースもあります。このようなケースでは、フォーキャスト自体をシステム化の対象としつつ、原材料によってはフォーキャストの数値を基に不足分の手当が行えるようにする必要があります。
こうして考えて見ると、在庫管理は意外と奥が深いのです。
在庫管理をExcelで行うメリット
Excel在庫管理は、もう旧い？
もう20年も前から「Excelでｘｘ管理をするのはもう旧い、これからは○○○だ！」といったアプローチが流行っていますが、未だに「これからはクラウドだ」「いやロボットだ」・・・と新しい概念が出ては消えているのが現状です。共通するのは、Excelでｘｘをするのが旧い根拠が曖昧なことです。
寧ろ、在庫管理に限って言えば、必ず補充（入庫）を伴うので、「入庫させるための発注」が必要になります。仕入先や原材料種類が多いと、中には「いつもは10単位仕入れるが、今後値上がりしそうだから今回だけ20単位手配しよう」といった例外事象が頻発します。また、廃番予定商品の原材料は新たに仕入れる必要がなくなりますが、一時的に休止するだけの場合もあり、いちいち廃番にして戻し忘れたりすると、生産ラインが混乱するだけです。
そんな時、Excelでフォーキャストを作れるようにしておくと、デフォルトで自動設定できる一方で、任意に数値を打ち替えたり、着色してコメントを付けたり、といった加工が容易にできます。
このように、Excelで在庫管理（に限りませんが）システム化しておくと、驚くほど例外事象に強くなるのです。
実はバーコードも有効な活用手法
Excelを関数と式で作るものだと思い込んでいると、在庫や入庫の登録をバーコードで実現した、などという話を聞くと驚かれるかも知れません。
しかし、検品作業をする際に、手で登録していると大変なので、実際にはバーコードで商品コードを読み込み、あとは数字を打ち込むだけ、といった使い方が圧倒的に多いのが現状です（あくまで当社比ですが）。
こちらのバーコードスキャナーは、現時点（2022年6月）で2000円未満で入手できますが、十分Excel在庫管理で活用できます。
Excel在庫管理システムのデメリット!?
一方で、同調圧力の強い文化の場合、「皆がやっている」ことが暗黙のルールとなっていますから、独りだけ別なことをしようとすると途端に圧力がかかります。
その根拠としては、「式が壊れる」「使っているうちにファイルサイズが肥大化する」「人が転記する際にミスが起こる」・・・という、一見尤もらしい理由が挙げられる事が多いのですが、いずれも「式を保護する／使わないようにする」「データを外部のDBなどに分離する」「マクロなどで自動化されれば人が操作する部分がなくなる」といった対策が知られていますので、大抵は「感覚的にExcelでできると納得できない」ケースが多い様です。
どうやら、「Excelで情報システム化する」といっても、「どうせ関数や式で作るんだろう」という思い込みが強いようですが、プロが設計した「システム」であれば、壊れてもある程度自立的に補正できるように設計されているのが普通ですから、ここでいうExcel在庫管理システムとは「ちょっとExcelに詳しい社員が片手間で作った式と関数の在庫管理シート」とは別物だと考えて下さい。
Excel在庫管理システム手法の考え方（実例）
対象商材の例（５選）
我が社でも、実に様々なExcel在庫管理システムを構築してきました。それぞれの特性により、考慮しなければならない点がかなり異なります。実例を基に、簡単に解説します。
- 生き物：活きている動物そのものの在庫数把握です。オス・メスだけでなく年齢によっても別商品として扱います。病気などで在庫から外されるケースも想定しています。多くの場合、1箇所の巨大倉庫に在庫するのではなく、ショップが在庫場所を兼ねているため、他の店から照会があった際にどの店にいるのか、移動できるのか（郵送等できません）といった情報が必要です。
- 魚介類：基本的に魚介類は急速冷凍すれば鮮度を維持できるのですが、「大きさにばらつきのない魚」「用途により部位毎に管理が必要」「カニの様に大きさがまちまちで、大きさ単位で別商品扱いになる」のように、何をどの用途で扱うかにより、「管理」方法が変わってきます。なお、冷凍しないものを在庫させることは、まずありません（持たないので、干物などに限られます）。
- 果物類：果物は基本的に冷凍保存が利かないので、賞味期限を強く意識する事になります。また大きさだけでなく、産地によっても別商品となるので、管理のメッシュが桁違いです。更にバナナのように、室に入れて熟成させる必要があるものは、仕入れて即出荷できないことから、別の時間軸を持たせる必要があります。管理手法が一番複雑なものを挙げよと言われれば、迷わず果物を挙げたくなります。
- 液体 ：液状のものは、「箱単位」のようなわかりやすい単位がありません。入庫時もローリーが来てホースで供給したものを、メーター読みするだけです。これがそのまま在庫されているのか、半製品に化けているのか、といった把握も、メーター読みで計算するしかありませんが、一定量は揮発・蒸発するため、適度なタイミングで棚卸し（補正）が必要です。この棚卸しもメーター読みとなるので、随所に人の手（目）が介在するのが悩ましいところです。ちょっと特殊な管理手法が要りますが、慣れの問題でしょう。
- 梱包材：製品在庫の場合、出荷するためには何らかの梱包を行う必要があり、製品として完成しているだけでは「完成」になりません。これがダンボールなのかパレットなのか・・・は製品によりますが、相手先により梱包が変わることもあるので、別管理が必要なケースが出てきます（大抵の場合、在庫場所も異なります）。生産管理では、原材料の１つとして扱う管理手法が多いので、在庫管理上も同様に扱うことが多いのですが、他の材料と違って使い回しがきかないことから、季節限定品が多いところでは余らせない工夫が必要です。
- 番外（半導体）：この原稿を執筆している時点（2022年6月）では、様々な産業で「半導体不足」による生産調整が行われています。こうした「半導体不足」が顕在化したのは最近の話ですが、実は工場の調達現場では、日本の生産力低下に伴う調達量の減少により、かなり前から半導体の調達力が落ちている、そうです。例えば、最新鋭の半導体は、スマホメーカーなどが日本メーカーの２～３桁上の量で発注をするため、相対的に日本企業に売って貰えるのが「余り物」になってしまうのだそうで、それを回避するためにグループ全体の調達を一本化して量を稼ぐと共に、ものによっては１年以上前から調達を行い、調達できた部品に合わせて生産計画を立てるのだそうです。こうしたケースでは、在庫管理の発想が完全に逆転しており、いわば「今ある材料でできる献立を考える」手法が要求されます。
対象業務範囲の例（５選）
続いて、在庫管理業務と組み合わせる上流・下流業務の例です。
- 営業予測：意外なようですが、工場で生産予測（フォーキャスト）をする際の有力根拠の一つが、営業予測数値です。向こう6ヶ月の間に、どの商品をどれだけ売るか、と言うのが営業予測（目標）で、そんなの正確な数値ではないと思われるかも知れませんが、目標に対して営業計画を立てている以上、それなりの確度でその通り販売されます。在庫予測もその時同時に行ってしまいます。但し、ずれると大きな不良在庫を抱えることになるので、毎月販売実績数値と予測数値を比べて補正しながら使うケースが多いです。この営業予測システム（予実管理）も、Excelの得意とする分野です。
- 受注集計：相手先が製造業などの場合、相手先の生産計画に合わせて必要な部材を供給する必要があります。多くは複数の取引先があるので、取引先の数だけ生産計画の様式が存在します。このため、10社と取引があると、10通りの生産計画を見ながら、自社の生産計画に転記する・・・といったことが日常的に行われています。大抵の生産計画は、Excel様式で提供されるので、Excelのシステムとして共通様式に変換するしくみを作っておけば、かなり自動化できます。
- 会計ｼｽﾃﾑ：会計系の処理では、仕入れの発注をすれば（もしくは入庫時点で）買掛金が立ち、製品在庫を出荷すれば売掛金を立てるケースも多いのですが、ほとんどのケースで手作業、もしくは別システムで行っています。在庫管理システムと連動させれば、こうしたデータも、会計システム（経理ソフト）側のファイルとして作成する事が可能です。こうした授受ファイルが監査証跡にもなるので、規模の大きい企業にこそお勧めです。
- 輸出入 ：原材料を海外から調達する場合や、現地法人で工場を運営している場合、原材料や完成品のやりとりが国を越えて行われます。こうしたケースの特徴として、発注から納品までに1ヶ月単位で時間を要する事が挙げられます。また、相手国によっては、発注したとおりに納品されているとは限らないので、「発注分を納入数で消し込む」という発想にならない場合もあります。Excelで作った場合、発注側を変更して正しく納入されたかの様にした上で、生産計画を引き直すという荒技も可能です。
- 見積請求：商社では、受注してから外国企業に仕入れ手配を行い、輸入後に当該社に引き渡す場合があります。特に食品系で多いのは、必ずしも大きな単位の発注ではないにも拘わらず、商社側の判断や商取引状の制約などから一定の単位で調達し、以後は小ロットで注文があるたびに出荷するスタイルです。この場合、見積を発行し、受注したらその分だけ出荷指示書を作成し、月末で締めたら相手先ごとに月単位の販売量・金額などを請求書として集計する、といった流れになります。当然見積の数字を基に、出荷・請求に結びつけたいのですが、途中の変更が多いのが悩みの種です。
在庫管理は、管理手法の確立が要
在庫管理に限らず、何でも自動化すれば良いというものではありませんが、在庫管理は特に取り扱い対象物による違いを意識して、前後の業務にどのように接続するかを睨みながら、実在庫と理論在庫の際をどのように縮めるか、あるいは埋めていくか、といった行為の繰り返し運用となります。
釦を押せば答えが得られるといった性格のものではないため、事前の洞察が肝要です。いきなりパッケージものに飛びつく前に、自社が管理したい対象物がどのような特性があり、どうすればあるべき姿になるのかを今一度考えて見てはいかがでしょうか？
判断でお困りでしたら、無料・匿名で相談にも応じていますので、お気軽にお問い合わせ下さい。