大量購入が安全在庫に与える影響
We have published an 入門チュートリアル that provides the formula used to compute the 再注文点 based on the forecasted demand, the demand 不確実性, the リードタイム and a couple of other factors.
This 古典的 safety stock calculation comes with a couple of key assumptions about the demand. We have already posted about how to handle 変動するリードタイム. Then, there is another implicit assumption in the classical formula: 購入者が独立していると見なされている.
Recently, we have been approached by 教室向けに頻繁に大量購入を行う企業. 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.
免責事項: 数字は作り話であり、簡略化のためにいくつかの結果は大まかに近似されています.
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.
平均月間売上は23注文ですが、大量注文の要素を除くと平均購入数は13注文に落ち込みます.
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, _古典的_な安全在庫計算は最適とは言えません: 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 ストックアウト.
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 再注文点計算 becomes:
Technically, y``Q is the inverse cumulative distribution of sales function taken at the quantile Q. The 再注文点計算 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.
’ 逆累積分布 Function ICMD(r As Range, q As Double) ’ 合計の計算 Dim s As Double For Each c In r s = s + c.Value Next ’ しきい値を探す 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.
読者コメント (1)
私も同様の問題を抱えています。非常に興味深い記事です。
Martin (8年前)
