DATA SCIENCE , AUTOMATION , DEMAND PLANNING

Data Science in Supply Chain Series

How To Calculate Restocking Quantities for a VMI Based on a Min / Max Setup

using: python, pandas, numpy

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.

Assumptions

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.

Libraries

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

Numpy allows us to work with arrays mathematically. so we can multiply an array [0,1,2,3,4] times 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

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.

Data

Our data consists of two tables.

  1. VMI Settings
  2. Current Customer Inventory

VMI Settings

This file shows us the settings for each part on the system. Its columns are:

  1. Part Number
  2. Estimated Annual Usage
  3. Program Starting Inventory
  4. Bag Quantity
  5. Min
  6. Max
  7. 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:

  1. Part Number
  2. Estimated Annual Usage
  3. Sell
  4. 6 Month Usage
  5. Run Rate (Usage compared to EAU)
  6. Weeks Inventory Remaining
  7. Safety Margin (Weeks Left / Lead Time)
  8. Lead Time
  9. Use EAU (Boolean operator to use EAU when calculating Run Rate rather than actual usage)

Analysis

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.

settings.head()
Part Number EAU Inventory Bag Qty Min Max Lead Time
0 123321 50000 0 100 10000 20000 16
1 123323 75000 0 100 30000 45000 16
2 123324 65000 0 100 10000 15000 16
3 123325 15000 0 100 5000 7700 16
4 123326 15000 0 100 4000 6700 16

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.

inventory.head()
Part Number EAU Inventory Sell Usage Run Rate Weeks Left Safety Margin Lead Time Use EAU
0 123321 50000 15500 0.0917 19483 81% 19 1.0 16 NaN
1 123323 75000 33122 0.0983 57600 160% 14 0.0 16 NaN
2 123324 65000 14500 0.1050 7500 24% 48 3.0 16 NaN
3 123325 15000 8700 0.1117 9300 129% 23 1.0 16 NaN
4 123326 15000 0 0.1333 20100 221% 0 0.0 16 NaN
inventory.shape
#=> (15, 10)

We now have two tables loaded into the analysis. Our approach from here forward will be as follows

  1. For each part in the settings, get the current inventory at the customer’s facility
  2. If it is less than the min, fill to the max
  3. 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 vlookup can result in part numbers not being found (a number as a string). This happens far less in python.
  • If you are doing a vlookup in 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.

With the 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
0 123321 50000 15500 50000 0 100 10000 20000 16
1 123323 75000 33122 75000 0 100 30000 45000 16
2 123324 65000 14500 65000 0 100 10000 15000 16
3 123325 15000 8700 15000 0 100 5000 7700 16
4 123326 15000 0 15000 0 100 4000 6700 16

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
0 123321 50000 15500 50000 0 100 10000 20000 16 4500
1 123323 75000 33122 75000 0 100 30000 45000 16 11878
2 123324 65000 14500 65000 0 100 10000 15000 16 500
3 123325 15000 8700 15000 0 100 5000 7700 16 -1000
4 123326 15000 0 15000 0 100 4000 6700 16 6700

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.

Output

refill_sheet = refill_data[['Part Number', 'Inventory_x','Min', 'Max', 'Stocking Quantity']]
refill_sheet
Part Number Inventory_x Min Max Stocking Quantity
0 123326 0 4000 6700 6700.0
1 123331 28730 30000 48000 19300.0
2 123420 3596 4540 7000 3400.0

Finally, Let’s export this to a csv that we can send to our warehouse!

refill_sheet.to_csv(save_path)

Afterthoughts

This can be built into a script and run automatically. One of the things we did was to

  1. automate the download of these sheets every Sunday
  2. automate the running of the above script every Sunday Night
  3. 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.

Get our Free
Data Science in Supply Chain
Email Course

Enter your email and begin advancing your career in minutes!

    We won't send you spam. Unsubscribe at any time.