Below is a walkthrough which takes the current inventory at a customer facility, and calculates the quantity of each item we need to bring over in order to restock their inventory.
We assume that the inventory analysis is being done on a customer using Vendor Managed Inventory with a Min/Max setup
Vendor Managed Inventory
Vendor Managed Inventory (or VMI) is a type of inventory ordering/control system where the supplier manages the inventory on the customer’s site. So the customer will have bins or shelves, and the supplier will regularly stop by and replenish the inventory for the customer. This removes the need for the customer buyer to do demand planning and ordering and places the responsibility on the supplier instead.
Min / Max Setup
Min / Max is a typical VMI setup. What means is that for each part on the VMI system:
“I will bring over enough parts to fill your inventory to [max] after it falls below [min]”
The max ensures that there is no too much inventory on hand for the customer which can cause money or space issues.
The min reduces the number of parts and smaller partial deliveries the supplier has to make. If they have to fill all parts to the max every time, then a simple use of 10 pieces of a bolt would trigger a refill. The costs of delivery and distribution would be astronomical. So the min states:
“You only need to fill to the [max] when the parts fall below the [min]”
There are a lot of theories on how to set these numbers, but that is outside the scope of this tutorial.
I use python for most data analysis done in for supply chain. In this case, I will be importing Numpy and Pandas libraries to add some functionality
Numpy allows us to work with arrays mathematically. so we can multiply an array
2 and get the entire array multiplied as such
[0,2,4,6,8]. This is known as vector and matrix math and is extremely useful when working with “spreadsheet” data.
Pandas allows us to interact with csv and excel files fairly easily. A must when using data sent to you or available from many supply chain software packages.
Our data consists of two tables.
- VMI Settings
- Current Customer Inventory
This file shows us the settings for each part on the system. Its columns are:
- Part Number
- Estimated Annual Usage
- Program Starting Inventory
- Bag Quantity
- Lead Time
Current Customer Inventory
This file is the real-time export of inventory at the customer facility. When parts are delivered they are scanned into the system. When parts are used at the customer facility, they are scanned out. So inventory is
Starting Inventory + Scanned_In - Scanned_Out
The export’s columns are:
- Part Number
- Estimated Annual Usage
- 6 Month Usage
- Run Rate (Usage compared to EAU)
- Weeks Inventory Remaining
- Safety Margin (Weeks Left / Lead Time)
- Lead Time
- Use EAU (Boolean operator to use EAU when calculating Run Rate rather than actual usage)
Here we go!
First, we need to import our packages.
import pandas as pd import numpy as np
Next, let’s set the path where our settings and inventory file are located. At the end of this analysis, we are going to export a pick report for our warehouse, so let’s set a location for that as well.
settings_path = '~/Desktop/vmi-load-export.csv' inventory_path = '~/Desktop/Vmi Analytics Export.csv' save_path = '~/Desktop/refill_sheet.csv'
Now we need to import the two files we are going to use, using Pandas.
settings = pd.read_csv(settings_path) inventory = pd.read_csv(inventory_path)
With those sheets imported into dataframes, we can view the first few rows by using
head. This gives us an idea of what the data looks like.
|Part Number||EAU||Inventory||Bag Qty||Min||Max||Lead Time|
We can also use
shape to see how many rows there are. This is a good idea to know how big the dataset is.
settings.shape #=> (15, 7)
Let’s do the same with the inventory sheet.
|Part Number||EAU||Inventory||Sell||Usage||Run Rate||Weeks Left||Safety Margin||Lead Time||Use EAU|
inventory.shape #=> (15, 10)
We now have two tables loaded into the analysis. Our approach from here forward will be as follows
- For each part in the settings, get the current inventory at the customer’s facility
- If it is less than the min, fill to the max
- If it is not less than the min, do nothing
In order to carry out step one, we need to combine the data on two dataframes or sheets. In Excel, you might use something like
vlookup to accomplish this. What I am about to show is much better than
vlookup and results in fewer errors. Just a few of the reasons include:
- formatting in Excel with
vlookupcan result in part numbers not being found (a number as a string). This happens far less in python.
- If you are doing a
vlookupin Excel, and the other sheet being referenced has rows that yours does not, you will never know (unless you do another 2-3 formulas and use dummy variables to find it).
Using Python, we can do something called
merge. So are going to, in one command,
merge both tables based on a column. This simply creates a dataframe with columns from both sheets and rows that include a unique list of the “merged” column from both sheets.
Let’s take this to our example here. We are going to
merge these two sheets based on
Part Number. In Excel, we would
vlookup the part number in the inventory sheet and carry over the inventory number. If the inventory sheet had part numbers not on our settings sheet, we would never know… they would just never be looked up.
merge, all settings and inventory columns are added together. Then each row is a unique list of Part Numbers from both sheets. This way you don’t lose any data. If one sheet has a part number that the other one doesn’t, that the columns from the 2nd sheet are simply blank for that row.
So, let’s get back to our analysis. We are going to
merge both sheets. If the name of each column is different, you need to specify, here they are the same though, so we are good.
data = inventory.merge(settings, how='left', on='Part Number' ) data.head()
|Part Number||EAU_x||Inventory_x||...||EAU_y||Inventory_y||Bag Qty||Min||Max||Lead Time_y|
I often do a quick check of the data to make sure that what I expected to happen, actually happened.
Now, we need to create a new column which takes the max and subtracts the inventory out.
data['Exact Stocking Quantity'] = data['Max'] - data['Inventory_x'] data.head()
|Part Number||EAU_x||Inventory_x||...||EAU_y||Inventory_y||Bag Qty||Min||Max||Lead Time_y||Exact Stocking Quantity|
The next thing we need to do is to make sure that the quantity being stocked is in denominations allowable by the bag quantities. If we have the parts bagged in quantities of 100, restocking 123 is impossible.
data['Stocking Quantity'] = data['Bag Qty'] * round(data['Exact Stocking Quantity']/data['Bag Qty'])
Finally, we don’t want parts on our pick report that are negative or below our minimum, so this filters those out.
refill_data = data[data['Stocking Quantity'] >= data['Min']] refill_data.shape #=> (3, 18)
Looks like we are a few rows less, so a few parts don’t need stocking.
We now have a lot of unnecessary columns. Let’s just grab what we need and get rid of the rest.
refill_sheet = refill_data[['Part Number', 'Inventory_x','Min', 'Max', 'Stocking Quantity']] refill_sheet
|Part Number||Inventory_x||Min||Max||Stocking Quantity|
Finally, Let’s export this to a csv that we can send to our warehouse!
This can be built into a script and run automatically. One of the things we did was to
- automate the download of these sheets every Sunday
- automate the running of the above script every Sunday Night
- export the sheet to a shared drive that syncs with the warehouse computer
In this way, there are automated refill reports for all customers every Monday morning for the warehouse.
This can be taken a lot further and lacks for parts that have irregular usage and other outlier situation. However, it is a good start.