The VP Sales showed the VP Supply Chain a report he pulled from the ERP system showing them that there are many out of stock items. This seemed odd to the VP Supply Chain and he asked me to take a look. I found out that report was analysing the data in a incorrect way causing everyone to run after their tales. After understanding what is needed I used my VBA coding abilities to create the “Inventory Health Report”, a report that was used by many people throughout the company.
Box vector created by upklyak – www.freepik.com
Project goals:
- Create a unified company report for all users to refer to the same data
- Allow users to analyse the inventory situation in different views:
- Lone warehouse view
- Grouped warehouses view
- Entire company view
- Generate an inter company transfer recommendation
- Dashboard that reflects information for the different views:
- items at risk of stock outs
- Items with excess inventory
- Sales potential for the future in units and in money
Data sources:
- 4 on hand reports
- 4 in transit reports
- 4 open sales orders reports
- 4 open deliveries to be shipped reports
- Forecast report
- MTD sales report
- Average sales price report
- User defined parameters
Description of the solution:
I developed a custom VBA based excel file. These were the main steps of the file:
- Clear the current database
- Copy the updated data files from the directory
- Merge all the files into one sheet
- Create a unified database where under one headline will appear the data from many columns.
- Delete all pictures and unneeded data to shrink file size
- Freeze the date – critical for demurrage and detention cost calculations
- Clean up the data based on user definitions – replace blanks with zeros, fill in missing data from fixed table.
- Define default values is any critical are missing.
- Created a “dictionary” between different sources. For example, you would see US, USA and the United states of America in different sources. In the file they would all show as USA.
- Paste all cells by value to shrink file size
- Predict the sales pace MTG based on a past daily sales
- Present a rolling plan by month:
- Reflect for each month the in transit and expected in transit
- Calculate the demand based on actual sales, open sales orders and forecast
- Highlight sales orders > forecasts
- Calculate the expected closing inventory in units and in months (base on future demand)
- Calculate the max potential to be sold based on available to promise
User interface:
- The user needs to replace the source files in the directory on their server
- The user needs to press the upload button, the code will run and populate a message that will notify the user if the code ran or if there was an issue. In the end the user will receive a data message that is supposed to give a clue if the data is ok. For example, it can show the total quantity on hand, total forecasts etc.
- The user should correct the data and press the upload button again.
- Assuming no data issues appear, the user will view the file’s main outputs:
- A very detailed spreadsheet showing all the data per SKU# for a lone warehouse, grouped warehouses and total company.
- The user can see the rolling inventory plan for the next few months based on the demand (sales orders, forecasts, sales pace) and supply (opening inventory, in transit, expected in transit).
- Out of stock dashboard- a summary of the expected out of stock items and volume, what is the potential sales in units and money.
- Intercompany transfer recommendations.
- Demand review – what is the delta between open sales orders and forecasts.
- Expected stock position versus target.
- The user can press a button and two duplicate files will be saved in the directory:
- Complete file including all the data and formulas for historic analysis.
- By value file to be distributed to colleagues.