How bulk purchases impact safety stocks
We have published an introductory tutorial that provides the formula used to compute the reorder point based on the forecasted demand, the demand uncertainty, the lead time and a couple of other factors.
This classical safety stock calculation comes with a couple of key assumptions about the demand. We have already posted about how to handle varying lead time. Then, there is another implicit assumption in the classical formula: buyers are assumed to be independent.
Recently, we have been approached by a company that frequently sell items in bulk to classrooms. Although most of the sales are single-item order, from time to time, comes a 20-30 items order for a whole classroom. The graph below illustrate the resulting sales patterns of intermittent bulk purchase.
Dislaimer: numbers made up and some results are widly approximated for the sake of simplicity.
Over those 12 months, we can see that we have 2 patterns:
- ongoing single-unit orders which account for 13 orders per months on average.
- intermittent bulk sales which account for +30 orders on average.
The average monthly sales is at 23 orders per month, but if we remove the bulk order factor, then the average purchase drop to 13 orders per month.
Now, what is the right safety stock in this situation? If we consider the classical safety stock formula with typical settings, then we are going to have a reorder point established at roughly 30 items: the 23 orders per month average, plus the safety stock itself covering the demand uncertainty. Bulk purchases at 30 items are very likely to be missed short of a hand-few items.
Yet, the classical safety stock calculation is less than optimal: here, we end-up storing about twice as much items than we need to to address the individual purchase, and yet, the safety stock is not high enough to cover big bulk purchases.
In order to address bulk purchases, we need to refine our safety stock formula to take this pattern into account. For sake of simplicity, we are going to model the bulk purchase pattern as a single factor later reintegrated in the safety stock formula.
In order to reflect the bulkiness of the sales, we could consider the largest purchase for each item being sold. Yet, this value is not robust in the statistical sense, as a single super-large historical purchase can completely skew the results.
Instead, we should rather consider a quantile of the bulk order quantity distribution as illustrated by the threshold Q in the illustration here above where all orders have been ordered from the smallest quantity to the largest one.
In this safety stock analysis, there is a natural fit for the quantile value to adopted for Q: it should be equal to the service level - as defined for the classical safety stock formula - that is to say the desired probability of not having a shortage.
Let call y``Q the bulk quantity associated to probability Q (in this illustration here above, we have y``Q = 30). Technically, y``Q is the inverse cumulative distribution of sales function taken at the quantile Q. The reorder point calculation becomes:
R = D + MAX(σL * cdf(P); y``Q``)
where σL * cdf(P) happens to be the safety stock as computed based on the demand uncertainty.
Computing y``Q in Excel is a bit tedious because there is not equivalent of the PERCENTILE function for inverse cumulative distribution. We have to resort either to an histogram scheme or to a VBA macro.
The ICMD User Defined Function for Excel, pasted below, performs the y``Q calculation, assuming the sales orders are listed in an Excel range and sorted in increasing order.
’ Inverse cumulative distribution
Function ICMD(r As Range, q As Double)
’ Computing the total
Dim s As Double
For Each c In r
s = s + c.Value
Next
’ Finding the threshold
Dim a As Double
For Each d In r
a = a + d.Value
If a >= (q * s) Then
ICMD = d.Value
Exit For
End If
Next
End Function
Based on this refined formula applied to the sample data, we obtain a reorder point R = 13 (demand forecast) + 30 (bulk quantity) = 43 which is sufficient to address the bulk purchase with a high probability while keep the inventory as low as possible.
Got business-specific constraints too? Don’t hesitate to let us know. We can adjust Salescast to better fit your business.
Reader Comments (1)
I have a similar issue in my trade. Very interesting article. 8 years ago | Martin
