Inventory modeling uncertain demand
This article was adapted from Microsoft Office Excel 2007 Data Analysis and Business Modeling by Wayne L. Winston. Visit Microsoft Learning to learn more about this book.
This classroom-style book was developed from a series of presentations by Wayne Winston, a well known statistician and business professor who specializes in creative, practical applications of Excel. So be prepared — you may need to put your thinking cap on.
In this article
Overview
-
At what inventory level should I place an order if my goal is to minimize annual holding, ordering, and shortage costs?
-
What does the term 95 percent service level mean?
In Chapter 67, "The Economic Order Quantity Inventory Model," we used the economic order quantity (EOQ) to determine an optimal order quantity and production batch size. We assumed that demand occurred at a constant rate. Thus, if annual demand occurred at a rate of, for example, 1200 units per year, then monthly demand would equal 100 units. As long as demand occurs at a relatively constant rate, the EOQ is a good approximation of the cost- minimizing order quantity.
In reality, demand during any time period is uncertain. When demand is uncertain, a natural question is how low to let the inventory level go before placing an order. We call the inventory level at which an order should be placed the reorder point. Clearly, a high reorder point will decrease shortage costs and increase holding costs. Similarly, a low reorder point will increase shortage costs and decrease holding costs. At some intermediate reorder point, the sum of shortage and holding costs is minimized. Our first example shows how to determine a reorder point that minimizes expected ordering, shortage, and holding costs based on the following two assumptions:
-
Each unit we are short is back ordered by a customer, and we incur the shortage cost cB. This cost is primarily a measure of the customer's dissatisfaction caused by late receipt of an ordered item.
-
Each unit we are short results in a lost sale, and we incur the shortage cost cLS>cB. The lost sales cost includes the profit lost from the lost sale as well as the shortage cost included in cB.
The second example shows how to determine the optimal reorder point based on a service level approach. For example, a 95 percent service level means that we set the reorder point at a level ensuring that, on average, 95 percent of all demand is met on time. It is usually difficult to determine the cost of a shortage in either the back-ordered case or the lost-sales case. For that reason, most companies set reorder points by using the service level approach.
Note: On this book's companion CD, for the back ordered and lost sales models, I've included a worksheet named Protected in which all formulas are protected for both the back-ordered and lost-sales cases. You can use these worksheets as templates.
At what inventory level should I place an order if my goal is to minimize annual holding, ordering, and shortage costs?
As I indicated in Chapter 67, the EOQ depends on the following parameters:
-
K=Cost per order
-
h=Cost of holding one unit in inventory for a year
-
D=Annual demand for the product
Because demand is now uncertain, we will let D stand for the expected annual demand for the product.
The back-order case
See the file Reorderpoint_backorder.xlsx, shown in Figure 68-1, for the data I'm using in this example. Let's first suppose that each shortage results in the back-ordered units. In other words, a shortage does not result in any lost demand. We assume that each unit we are short incurs a cost cB. In this case, the reorder point depends on the following quantities:
-
EOQ is the economic order quantity (the quantity ordered each time an order is placed)
-
K is the cost per order
-
h is the annual holding cost per unit
-
D is the mean annual demand
-
SOC is the cost per unit short
-
annsig is the standard deviation of annual demand
-
meanLT is the average lead time; that is, the average time between placing an order and the time the order is received
-
sigmaLT is the standard deviation of lead time
Let's suppose that a department store wants to determine an optimal inventory policy for ordering electric mixers. They have the following information:
-
It costs $50 to place an order for mixers.
-
It costs $10 to hold a mixer in inventory for a year.
-
On average, the store sells 1000 mixers per year.
-
All customers who try to purchase a mixer when the store is sold out of them return at a later date and buy a mixer when the mixer is in stock. The store incurs a penalty of $20 for each unit it is short.
-
The annual demand for mixers (based on historical data) has a standard deviation of 40.8.
-
Lead time is always two weeks (0.038 years), with a standard deviation of 0.
After we enter K, h, and D in cells C2:C4, our spreadsheet computes the EOQ (100 mixers) in C5. After we enter SOC, annsig, meanLT, and sigmaLT in cells C7:C10, our spreadsheet computes in cell C14 the reorder point that minimizes the sum of expected annual holding and shortage costs (51.63 mixers). Thus, our department store should order 100 mixers whenever their stock decreases to 51.62 (or 52) mixers.
The safety stock level associated with a given reorder point is reorder point–mean lead time demand. The department store maintains a safety stock level of 51.62–38.46=13.16 mixers, computed in cell C15. Essentially, the safety stock is always in inventory, resulting in extra holding costs. A higher level of safety stock will, of course, reduce shortages.
The lost-sales case
Now suppose that each shortage results in a lost sale. The cost associated with a lost sale is usually estimated as the back-order penalty plus the profit associated with a unit sold. Suppose that the department store earns a $20 profit on each mixer it sells. The unit shortage cost for the lost-sales case is then $40 ($20 lost profit+$20 back-order penalty).
In the file Reorderpoint_lostsales.xlsx, shown in Figure 68-2, you can see the work I did to estimate the reorder point for the lost-sales case. After entering in cell C7 of the spreadsheet the lost-sales cost of $40, we find the optimal inventory policy is to order 100 mixers and place an order when inventory is down to 54.23 mixers. Our safety stock level is 15.77 mixers, and 2.4 percent of the store's demand for mixers will be unmet. Notice that the assumption of a lost sale has increased our reorder point and reduced the probability of a shortage. This happens because the increased cost of a shortage (from $20 to $40) makes us more eager to avoid shortages.
Increased uncertainty greatly increases the reorder point. For example, in the lost-sales case, if the standard deviation for lead time is one week (0.019 years) rather than 0, the reorder point increases to 79.50 mixers and the safety stock more than doubles from the case in which our lead time was known with certainty.
What does the term 95 percent service level mean?
As stated earlier in this chapter, a 95 percent service level simply means that we want 95 percent of our demand to be met on time. Because estimating the back-order penalty and/or the penalty that results from a lost sale is often difficult, many companies set safety stock levels for products by setting a service level. Using the file Servicelevelreorder.xlsx (shown in Figure 68-3), we can determine the reorder point corresponding to any service level we want.
As an example, consider a pharmacy that is trying to determine an optimal inventory policy for a drug they stock. They would like to meet 95 percent of their demand on time. The following parameters are relevant.
-
Each order for the drug costs $50.
-
The cost to hold a unit of the drug in inventory for a year is $10.
-
Average demand per year for the drug is 1000 units.
-
The standard deviation of annual demand is 69.28 units.
-
The time required to receive a shipment of the drug always takes exactly one month (0.083 years).
We enter the service level we want (0.95) in cell C1 and all other parameters in cells C2:C4 and C7:C9. To determine the reorder point yielding the desired service level, click Solver in the Data Analysis group on the Data tab of the Ribbon. Our Solver model adjusts the reorder point until the percentage of demand met on time matches the service level we want. We find that we should order 100 units of the drug whenever our inventory level drops to 90.23 units. This reorder point corresponds to a safety stock level of 6.90 units.
In the following table, I've listed the reorder point and safety stock levels corresponding to service levels between 80 percent and 99 percent.
| Service level percentage | Reorder point | Safety stock |
| 80% | 65.34 units | -17.99 units |
| 85% | 71.85 units | -11.48 units |
| 90% | 79.57 units | -3.76 units |
| 95% | 90.23 units | 6.90 units |
| 99% | 108.44 units | 25.11 units |
Notice that moving from an 80 percent service level to a 99 percent service level increases the reorder point by almost 67 percent! Also note that we can attain a 90 percent service level with a reorder point less than our mean lead time demand (refer back to cell C10 in Figure 68-3). A 90 percent service level results in a negative safety stock level, which is possible because shortages occur only during the lead time and our lead times usually cover a small portion of a year.
Problems
When working with Problems 1 and 2, assume that a restaurant serves an average of 5000 bottles of wine per year. The standard deviation of the annual demand for wine is 1000 bottles. The annual holding cost for a bottle of wine is $1. It costs $10 to place an order for wine, and it takes an average of three weeks (with a standard deviation of one week) for the wine to arrive.
-
Assume that when the restaurant is out of wine it incurs a penalty of $5 as the result of lost goodwill. Also, the restaurant earns a profit of $2 per bottle of wine. Determine an optimal ordering policy for the wine.
-
Determine an inventory policy for wine that yields a 99 percent service level.
-
A reorder point policy is often referred to as a two-bin policy. How can a reorder point policy be implemented in a situation in which two bins are used to store inventory?
No comments:
Post a Comment