In our discussion of the “order-up-to model” in class, we assumed that the excess demand is backordered. That is, if the demand in any period is larger than the on-hand inventory at the beginning of the period, we hold on to the excess demand (which is why our inventory at the end of a period can be negative) and meet it when an order arrives at the beginning of the next period.
In this question, you will compare the backordering assumption with an alternative assumption, under which excess demand becomes lost sales. That is, if the demand in any period is larger than the on-hand inventory, the excess demand in that period is lost forever.
Consider a firm ABC, which makes and sells Product Z. ABC manages the inventory of Product Z according to an order-up-to policy with periodic review. The production lead time for Product Z is five days. ABC reviews the inventory of Product Z at the beginning of each day, and uses a constant
order-up-to level of 180 for Product Z’s inventory position. That is, at the beginning of each day, ABC starts producing enough of Z to bring its inventory position to 180. As for the daily demand for Product Z, we are given 45 days of demand observations – see “Question 3 Product Z Demand Data.xlsx.”
(a) Assume that the excess demand for Product Z is backordered. Run a simulation (with 5000 iterations) that tracks Product Z’s inventory over 100 days. Determine the average cycle service level and average leftover inventory per day for Product Z.
Hints and Guidance: (i) See the template “Question 3 Backorder — Template.xlsx.” do the template to answer part (a). (ii) Do not change any of the data for days -4 through 0; those indicate what happened prior to Day 1. (iii) Note that we do not know the probability distribution of the daily demand for Product Z, but we are given 45 days of demand data in “Question 3 Product Z Demand Data.xlsx.” Therefore, first, fit a distribution to the daily demand data, using @RISK’s “Fit” feature. One suggestion: When you are doing the fit, choose “Continuous Sample Data” as your data type. Because the demand numbers are relatively large, there is no harm in approximating it as a continuous variable that can take fractional values. (iv) To determine the number of days without shortages, you can use Excel’s “Countif” function.
(b) Now, assume that the excess demand for Product Z becomes lost sales. Run a simulation that tracks Product Z’s inventory over 100 days, across 5000 iterations. Determine the average cycle service level and average leftover inventory per day for Product Z.
Hints and Guidance: Revise the spreadsheet for the backorder case to model this alternative scenario. To revise, think about the following question: Under the lost sales assumption, if the demand exceeds on-hand inventory, what is the inventory at the end of the day?
(c) Compare your results for parts (a) and (b), answer the following question: Which assumption led to a higher cycle service level – backordering or lost sales? Why do you think that is?
The needed templates are in the below links.