Inventory Concern Form (ICF) Automation
Improvement of the process of Store Associates requesting replenishment of low inventory.
Done by the use of macros; Excel-AS/400 report integration and by using VBA and SQL scripts.
Improvement of the process of Store Associates requesting replenishment of low inventory.
Done by the use of macros; Excel-AS/400 report integration and by using VBA and SQL scripts.
Situation
The inventory concern form was an existing Excel template used by the stores to request cosmetics merchandise replenishment. The Store Associate would need to connect to the AS400 terminal, navigate through the menu, find the SKU information and then type the information into an excel file. This file was regularly shared many times among the associates, thus not reflecting the many changes and upgrades the planning team made to it.
There was a need to standardize the process; to make certain information to be mandatory and concrete, to map the entire process and the ability to track down its status, and to make sure the used template was the last version available.
Furthermore, the manual process of typing the information from a terminal window into Excel was long and susceptible to errors, taking the Store Associate from hours to days to complete the request. Also, data verification would slow down the requests as it was done row by row, for each UPC.
Task
To give the Store Associates a better way to fill out the template, thus reducing errors, time and make the entire process more effective. Inclusion of dropdowns to reduce the number of options available when entering data.
The addition of a heatmap to show the turnover of the item, this way the Store Associate would be required to explain why it was being requested or to consider removing it from the list.
Action
It was proposed to automate the Excel template file by connecting it directly to the AS400 databases using SQL queries, this way the data would be populated and not typed up by the store assiciate.
A heatmap was added as a preliminary analysis to flag slow and fast sellers, in order to give the Store Associates an immediate overview of the need to request to replenish SKUs they considered to order.
We implemented a series of dropdown menus that would populate dynamic lists based on other cells' values and other dropdown selections. When a class number was selected or input, the brand list dropdown would show brands under that class only. It would also show the assistant buyer's name in charge of that class so the requester would contact that person if required.
The macro performed a series of validation processes before sending requests or reviews. Some cells need to be filled out with the right information.
Example: if a UPC number was included on the list but it belonged to a different class number, the macro would prompt a message box saying that the UPC doesn't belong to the selected class. This was accomplished by extracting all UPC numbers of the selected class and matching all the UPCs input by the user. If it did not match, it would find the class number for the offending UPC and tell the user in the message box the correct class.
A ribbon with buttons was added to perform the new steps mapped out in the process.
a) Connect to AS400: Opens a prompt to ask the user to enter username and password.
b) Get Data: Once connected to the AS400, button to download the data linked to the selected Class #.
c) Send Request: Macro exports the data populated and entered by the Store Associate into the master database.
d) Send Review: Macro run by the Buyer/Planner to send the approval or rejection of the request to the Store Associate. This macro would also create a record in the master database.
Creation of the Master Database:
A centralized file containing records of all requests and their status was created, showing if the requests were approved or rejected. This file was hosted in the company's network and accessible to both Store Associates and Buyers/Planners.
When a request is placed, the macro opens the master database and adds the data at the bottom of the data table. The same for when the review is submitted.
A dashboard sheet shows information regarding status, follow-up contact, inventory quantity approved or reason for rejection.
A ribbon was also added to the master database (Generate Reports). In this cas, it refreshes the tables and populates new views with the most recent data.
Results
Depending on seasonality and the volume of SKUs, the amount of time spent by Store Associates was dramatically improved from several hours or days to a few minutes and follow-ups were properly directed to the right person.
After the project was deployed, it took an average of 15 seconds to fill out the information previously being entered manually. We also added traceability with the master database. Using the master DB, users were consequently able to track down the requests and contact the right Buyer/Planner, avoiding time consuming email/phone call follow-ups.
The project was awarded with a few WOW recognitions by some of the Store Associates and allocation team members.