Em previous post, I commented on the use of the service level to calculate the safety stock. The main point discussed was the difference between the service level usually monitored by companies (such as the IFR, or Item Fill Rate) and the appropriate service level indicator for use in the safety stock formula (the CSL, or Cycle Service Level). In this post, I will explore a practical way to transform the IFR information to define the service level associated with the CSL, thus allowing the appropriate calculation of the safety stock based on an indicator usually monitored by companies. And vice versa, that is, defining an optimal service level (CSL), which should be the associated IFR that can become the target in corporate indicators.
Case 1: Defining the Item Fill Rate (IFR) from the Cycle Service Level (CSL) great
Initially, it is worth remembering that, in the previous post, the method of calculating the ideal service level was presented. This calculation balances the cost of shortage and excess and stipulates what the optimal service level should be for each product. This service level refers to the CSL and is calculated considering the following ratio:
CSL great =
Figure 1 – Calculation of the optimal service level (cycle service level) based on the costs of shortage and excess.
Source: ILOS.
With the optimal CSL calculated, we can use the normal curve to define the term k that goes into the safety stock formula. To define the k, it is possible to use the formula “INV.NORMP.N” of Excel. As an example, for a CSL 98% we have a k associate 2,054. As k, it will be possible to calculate the unitary loss function G(k).
The unitary normal loss function G(k) is a statistical concept used to calculate the expected loss associated with variability in a production or decision-making process, and is quite useful in the context of inventory optimization. Its formula relates the normal distribution (cumulative and non-cumulative) as a function of the term k, calculated previously. The formula for the loss function G(k) is:
Gk=NORMDISTk,0 -k*(1-NORMDISTk,1)
NORMDIST(k,0): Non-cumulative normal distribution for term k
NORMDIST(k,1): Cumulative normal distribution for term k
Figure 2 – Formula for the unitary normal loss function.
Source: MIT Center of Transportation and Logistics.
For the same example cited above, for a k of 2,054, we would have a G(k) = 0.73%. With the function G(k) calculated, it is possible to calculate the item fill rate (IFR). In addition to G(k), two other parameters are required to calculate the IFR: the combined standard deviation (σDL) and the lot size (Q).
G(k) =
* (1 − IFR)
Q: Lot size
σDL<: Pooled standard deviation
Figure 3 – Formula that associates the function G(k) with the IFR.
Source: MIT Center of Transportation and Logistics.
Remembering that the combined standard deviation is the term that, multiplied by k, generates the safety stock. If there are any doubts about your formula, the previous article discourse more detail about this parameter.
By making adjustments to the formula in Figure 3, it is possible to isolate the IFR term and define it, from G(k), Q and σDL. Following the same example and considering a size of lot Q of 4.500 units and a pooled standard deviation of 1.000, we would have an IFR = 99,8%. That is, for the assumptions we made, a CSL 98% corresponds to a IFR of 99,8%. Although it may seem like a small difference, it is worth noting that, when we talk about stock levels, maintaining a volume of products in stock that guarantees a 99,8% service level is considerably more costly than a 98% service level. This will become clearer with the next example.
Case 2: Defining the Cycle Service Level (CSL) from the Item Fill Rate (IFR) managerial:
As mentioned in the previous article, it is very common in business contexts to define goals for certain strategic indicators, and this applies to the level of service. It is common for executives in the logistics areas, customer service or operations set a target value for the service level. For example, let's imagine that the desired service level for a certain product is 98%. If we consider this value directly for the definition of the k and calculate the safety stock, we will (most likely) be making a mistake! Because it is expected that, when talking about service level, the executive is referring to a metric such as the IFR. And in this case, we will need to perform a transformation to the CSL before defining the k of the safety stock. Here is an example of how to do it!
Once we have the IFR, we will need the lot size Q and the combined standard deviation σDL. Thus it is possible to calculate G(k) from the formula shown in figure 3. Considering IFR of 98%, batch size Q of 4.500 and pooled standard deviation equal to 1.000, we will have a G(k) = 9,00%.
To set the k from G(k), we would need to isolate the term k in the formula described in figure 2, but this is not possible algebraically. To do so, there are some ways to perform such an operation, such as creating an objective function in Excel. Another option would be to use some numerical approximation, where you create, for example, a decreasing table with values of k (ranging from 3,000 to 0,000) and corresponding G(k), allowing an association between G(k) and the approximate value of k. To better illustrate this process, a table built in Excel is presented, where k varies in steps decreasing by 0,001. From a “LOOKUP”, a formula widely mastered by Excel users, it is possible to estimate the approximate value of k from G(k). As can be seen in Figure 4, for a G(k) of 9,00% we would have, approximately, a k of 0,960.
Figure 3 – Construction of a table in Excel that defines the values of G[k] for each k corresponding. The table contains values of k ranging from 3 to 0, in steps of 0,001.
Source: ILOS.
With this value of k, we could already calculate the safety stock. But to finish our example, let's calculate what the corresponding CSL would be. To do this, we will use the formula “NORMP.DIST.N”. For an ok of 0,960, we obtain a CSL 83,2%. In other words, it is a numerically smaller value, but it is equivalent to the IFR of 98%. Table 1 summarizes the results obtained in the two cases presented.
Table 1 – Comparison of values obtained in the cases presented.
Source: ILOS.
Observing the values k resulting, and considering the same characteristics of lot size, demand and supply (same combined standard deviation considered in both examples), we have that in case 1 we would have a safety stock more than twice as large as case 2! This difference would generate an impact with an increase in the volume in stock and, consequently, an increase in cash stopped, in the associated storage costs, in the risks of loss and obsolescence due to greater stock coverage, among others.
We could expand our discussion by bringing in other elements, such as: What lot value are we considering? Is it the current lot or should we look for an ideal lot size, such as the Economic Purchase Lot (EPL)? Or what are the shortage and excess costs associated with the CSL calculated from the target IFR, and what actions can I implement to change these costs and meet the desired service level?
Knowledge of the applications of each of the different service levels is essential. As has been shown, it is not enough to have a good data analyst who is well-versed in the tooling aspect, but rather who fundamentally understands the concepts, parameters and their relationships, thus enabling the correct application of these concepts and efficient inventory management.
References:
Henry Alvarenga
He has been working in consultancy for 7 years, with experience in more than 20 Demand Planning and S&OP projects, Logistics Network Design, Logistics Master Plan, Inventory Policies, Operations Strategy and Market Intelligence
