帳票だけで調べられない商品欠品の理由をVBAで解決する

内勤小売マンです、主に需要予測やら在庫管理などを行ってます。
以前に深堀りしたアイディアの中から、今回は具体的な実装計画を作成し
改善に着手できるよう準備することにしました。

欠品の調査作業を簡略化したい

背景

うちの会社は、グループ内の事業会社に対し商品を供給する機能会社である。商品が欠品することは基本的に許されない。しかしながら商品が欠品した場合、欠品した背景をお客様へ説明する責任があるため、欠品リストに「出荷が急激に増加」「メーカーで欠品が発生」などの状況説明のコメントを書き込み、お客様へ配信している。

商品が欠品した理由をコメントとして記載

現状

悲しいことに欠品は毎日発生する、その全てに担当者はコメントする必要がある。チームはリーダーを含めて７名だが、毎日１人５分～３０分程度の作業時間が欠品の調査に費やされている。商品が欠品した理由は、入荷・出荷等の状況から複合的に導き出さなければならないが、調査するためのツールが整備されていないこともあり、下記のデータベースの画面を電卓でカウントするなどして出荷・入荷状況を判断している。

画面に表示されている数値を手動でカウントし出荷状況を判断

課題

欠品する理由の大半は出荷の急増である。つまり出荷が急増していることを手早く検知し、欠品リストに反映することができれば、作業時間の大幅な削減につながる。今まで解決されなかった理由は、出荷データが１日５万レコード程度あること、出荷作業は当日の夕方にならないと反映されないデータもあるため、前日のデータを翌日に必ず洗い替えしなければならない。そのため手動ではデータベースを管理できなかった。

工程表

改善案の詳細

①データ整形の完全自動化(VBA)
№2.帳票整形工程では、RawデータからExcelマクロで帳票整形がされているが、不完全な状態のため一部手動作業が入っている。№3.データ抽出工程の作業はExcelの列追加、ユニークキーの設定やVlookup関数の設定などの作業が手動で行われているため、合わせて完全自動化する。
②帳票設計の見直し
出荷データを含む必要なデータ項目を見直し、欠品の調査を極力帳票内で完結するように可視化する。ピボットテーブルなどの担当者が別途加工を行っている要件は極力盛り込む。
③日別出荷実績をExcelでデータベース化(VBA)
元々１日５万レコード程度のレコードを、ピボットテーブルで店舗の単位を除外し１日２万レコードまで圧縮する。日毎のマクロを実施する際に、前日のデータの洗い替えとデータの追加を行い、本日の出荷分を含む最新の状況を維持する。

期待される効果

①人時削減効果 年間150h想定
②手動作業の排除に伴うミスの削減
③日別出荷実績のデータベース化
④データベースを用いた出荷状況の数値化

導入スケジュール

2/8スタート
2/23正規リリースを目標

導入に向けて

本当はAPI連携等の最近覚えた知識を使って色々やりたかったのですが、圧倒的なExcel文化圏のため利用できる素地がほとんどありませんでした…
今回のプランはイレギュラーさえ見つからなければ実装は予定通り行けると考えてます。データベースは最低限Accessを使うべきなんでしょうが、現状ではスキルレベルが足りないのでそこは妥協しています。気に入らない部分は後で直せばOK！ぐらいで今回は走って行きます。

もし計画が失敗してしまった場合のPlanB