How to calculate safety stock and reorder point

Although the techniques for reducing its impact have existed for decades, inventory management is still a huge issue in many businesses. Various things happen that can result in costly stock outs that impact the customers, the ability to hit the sales target, and increase the propensity of affected customers churning. When stock outs affect several top-selling lines, the impact can be devastating.

Procurement managers need to be able to monitor every SKU to ensure they hold sufficient units in stock to meet demand, and that they place their order at the right time so that the supplier’s lead time doesn’t cause a stock out while the remaining units are being depleted waiting for the next batch to arrive. This is achieved using two operations management metrics: safety stock and reorder point.

Safety stock (or buffer inventory, as it’s sometimes known) is the stock held to protect the business from the impact of unusual deviations, such as variances in demand, variances in the quantity delivered, and variances in the delivery date. Reorder point is the stock level at which the next order must be placed in order to keep the product on sale, while allowing for those variances in supply and demand.

Import your sales data

First, open up a Jupyter notebook and import pandas. Then import a CSV of your sales data for a recent period. I’ve included the product id, sku, quantity, order_id, and the day number so I can group the data more easily.

import pandas as pd
df_sales = pd.read_csv('sales.csv')
df_sales.sample()

Calculate the safety stock level for a SKU

To calculate the safety stock level we need four values: the maximum number of units of a SKU sold per day within a period, the average number of units of a SKU sold per day within a period, the maximum lead time in days it takes the SKU’s supplier to send the stock, and the average lead time in days it takes the supplier to send the stock.

To calculate the maximum number of units sold daily for a given SKU, we’ll create a helper function called get_max_units_sold_daily() that we can re-use. This takes a given SKU, filters the dataframe of sales data, then sums the quantity sold per day and returns the maximum quantity sold.

def get_max_units_sold_daily(df, sku):

    df = df[df['sku']==sku]
    df = df[['order_day','quantity']]
    df = df.groupby(['order_day']).sum()
    max_units_sold_daily = round(df['quantity'].max())

    return max_units_sold_daily

max_units_sold_daily = get_max_units_sold_daily(df_sales, 'A385')
max_units_sold_daily

12

We can use the same approach to create a helper function called get_avg_units_sold_daily(). This uses mean() instead of sum() to return the average number of units of the SKU sold within the period in the dataset provided.

def get_avg_units_sold_daily(df, sku):

    df = df[df['sku']==sku]
    df = df[['order_day','quantity']]
    df = df.groupby(['order_day']).sum()
    avg_units_sold_daily = round(df['quantity'].mean())

    return avg_units_sold_daily

avg_units_sold_daily = get_avg_units_sold_daily(df_sales, 'A385')
avg_units_sold_daily

3

Finally, we can perform the safety stock level calculation. This is calculated by multiplying the maximum number of units sold per day by the maximum lead time, and then subtracting that value from the average units sold daily multiplied by the average lead time.

def safety_stock(max_units_sold_daily, avg_units_sold_daily, max_lead_time, avg_lead_time):
    """Returns the safety stock level for a given product based on sales and lead time.

    Args:
        max_units_sold_daily (int): Maximum number of units sold daily in previous period.
        avg_units_sold_daily (float): Average number of units sold daily in previous period.
        max_lead_time (int): Maximum number of days required to obtain stock.
        avg_lead_time (int): Average number of days required to obtain stock.

    Returns:
        Safety stock level for the product based on sales and lead time.
    """

    return  round( (max_units_sold_daily * max_lead_time) - (avg_units_sold_daily * avg_lead_time) )

Based on a maximum units sold daily of 12, and an average of 3, with a maximum lead time of 10 days, and average of 7 days, we get back a safety stock level of 99 units required to avoid stock outs. This level is highly dependent on the supplier lead times.

safety_stock = safety_stock(max_units_sold_daily, 
                            avg_units_sold_daily, 
                            max_lead_time=10, 
                            avg_lead_time=7)

safety_stock

99

Reducing these to a maximum of 5 days and an average of 2 means only 54 units are required in safety stock. As a result, put-away times are similarly important. If you get stock in, and your operations team don’t immediately put it on sale, you’re going to experience more stock outs than you should.

safety_stock = safety_stock(max_units_sold_daily, 
                            avg_units_sold_daily, 
                            max_lead_time=5, 
                            avg_lead_time=2)

safety_stock

54

Calculate the reorder point

It’s easy to calculate the reorder point once you have the safety stock level. The reorder point tells you the stock level at which the new order should be triggered (which assumes it’s instantaneous). It is calculated by adding the safety stock metric to the sum of the lead time multiplied by the average units sold daily.

def reorder_point(safety_stock,
                  avg_units_sold_daily, 
                  lead_time):
    """Returns the reorder point for a given product based on sales and lead time.

    The reorder point is the stock level at which a new order should be placed in order to avoid stock outs.

    Args:
        safety_stock (int): Safety stock level from safety_stock()
        avg_units_sold_daily (float): Average number of units sold daily in previous period.
        lead_time (int): Number of days required to obtain stock.

    Returns:
        Safety stock level for the product based on sales and lead time.
    """

    reorder = round(safety_stock + (lead_time * avg_units_sold_daily))

    return reorder

For a safety stock of 54, an average of 3 units sold per day, and a lead time of 5 days, we need to place our next order when the level reaches 69. That’s it. Just calculate this metric for each of your SKUs, and you’ll know the right level of stock to hold to reduce the likelihood of running out, or holding too much stock.

reorder = reorder_point(safety_stock, avg_units_sold_daily, 5)
reorder

69

Matt Clarke, Saturday, March 13, 2021