Determining the reorder point: How low should I let my inventory level go before I reorder?

Applies to
Microsoft Office Excel 2003

Book cover


This article was adapted from Microsoft Excel 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

At what inventory level (called the reorder point) should I place an order if my goal is to minimize the sum of annual holding, ordering, and shortage costs?

     The back-order case

     The lost-sales case

What does the term 95 percent service level mean?

Problems

Sample files    You can download the sample files that relate to excerpts from Microsoft Excel Data Analysis and Business Modeling from Microsoft Office Online. This article uses the files ReorderPoint_Backorder.xls, ReorderPoint_LostSales.xls, and ServiceLevelReorder.xls.

 Note   In the download, there is a worksheet 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 (called the reorder point) should I place an order if my goal is to minimize the sum of annual holding, ordering, and shortage costs?

In Chapter 65 of Microsoft Excel Data Analysis and Business Modeling, 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, say, 1,200 units per year, during each month 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 I should let my inventory level go before I place an order?" We call the inventory level at which an order should be placed the reorder point. Clearly, a high reorder point will lower shortage costs and increase holding costs. Similarly, a low reorder point will increase shortage costs and lower holding costs. At some intermediate value for the reorder point, the sum of shortage and holding costs will be minimized. Our first example shows how to determine a reorder point that minimizes the sum of expected ordering, shortage, and holding costs under two assumptions:

  • Each unit we are short is back-ordered by a customer, and a shortage cost cB is incurred for each unit we are short.
  • Each unit we are short results in a lost sale, and a shortage cost cLS > cB is incurred for each unit short.

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-order or the lost sales case. For that reason, most companies set reorder points using the service level approach.

The economic order quantity (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.

Top of Page Top of Page

The back-order case

See the file ReorderPoint_Backorder.xls, shown in the following figure, for the data I’m using in this example. Let’s first suppose that each shortage results in the short units being back-ordered. 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.

Reorder point determination if shortages result in back orders

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, demand during a year is for 1,000 mixers.
  • All customers who try to purchase a mixer when the store is out of stock return at a later date and buy the mixer when the mixer is in stock. The store incurs a penalty cost 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 (which equals .038 years), with a standard deviation of 0.

After we input 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.63 (say 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.

Top of Page Top of Page

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.xls, shown in the following figure, you can see the work I did to estimate the reorder point for the lost-sales case. After inputting 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 our 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.

Reorder point determination if shortages result in lost sales

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.

Top of Page Top of Page

What does the term 95 percent service level mean?

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. The file ServiceLevelReorder.xls (shown in the following figure) enables us to determine the reorder point corresponding to any service level we want.

Reorder point determination using service level approach

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 1,000 units.
  • The standard deviation of annual demand is 69.28 units.
  • The time required to receive a shipment of the drug always takes exactly1 month (0.083 years).

We enter the service level (0.95) we want 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 on the Tools menu, and then click the Solve button. 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 -17.99
85 71.85 -11.48
90 79.57 -3.76
95 90.23 6.90
99 108.44 25.11

Notice that moving from an 80 percent 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 the previous figure). This service level results in a negative safety stock level, which is possible because shortages only occur during the lead time and our lead times usually cover a small portion of a year.

Top of Page Top of Page

Problems

In working with Problems 1 and 2, assume that a restaurant uses an average of 5,000 bottles of wine per year. The standard deviation of the annual demand for wine is 1,000 bottles. The holding cost for a year for a bottle of wine is $1. It costs $10 to place an order for the wine, and it takes an average of 3 weeks (with a standard deviation of 1 week) for the wine to arrive.

  1. Assume that the restaurant incurs a penalty of $5 as the result of lost goodwill when it is out of wine. Also, the restaurant earns a $2 profit per bottle of wine. Determine an optimal ordering policy for the wine.
  2. Determine an inventory policy for wine that yields a 99 percent service level.
  3. 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?

Top of Page Top of Page

 
 
Applies to:
Excel 2003