Inventory Management Simulation A discount retail store buys a particular model of DVD player from a distributor and sells it to its customers. The purchase cost of the player is $230 and the selling price is $290. Store management estimates that ordering cost is $100 per order and that it costs $2 to carry each unit in stock for one week. It is assumed that shortage results in a lost sale - with a resulting shortage cost of $60 ($290-$230) per unit short in any period. Forecasted demand is shown for the next 14 weeks. Although demand is variable, in this simulation, the actual demand for any period will never differ from the forecast by more than 50%. You have been put in charge of ordering inventory of this product. You must determine when to place an order and how many units to order each time you order. Orders are placed at the beginning of a week. Replenishment lead time is one week - so products are available for sale at the beginning of the following week. You objective is to minimize total inventory costs (the sum of ordering, carrying, and shortage costs) for the 14-week period. Week Number Row Measure 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Purchase cost $230 1 Beginning inventory = #5 from previous week 7 4 Selling price $290 2 Received = Units from order placed previous week (#7) 0 22 Ordering cost $100 3 Available inventory (#1 + #2) 7 26 Holding cost (carrying cost $2 per item per week 4 Actual demand 3 Shortage $60 5 Ending inventory = #3 - #4 (but not less than 0) 4 Forecast error +-50% 6 Forecasted demand 5 5 6 8 14 12 8 7 6 4 14 24 22 5 Lead time (week) 1 7 Number of units ordered for next week 22 8 Order cost = $ 100 if order is placed in #7 #### $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 9 Average inventory = (#3 + #5)/2 5.5 10 Carrying cost = $2 * #9 $11 11 Units short (if #4 > #3, #4-#3 otherwise 0) 0 12 Shortage cost = $60 * #11 $0 13 Total cost = #8 + #10+ #12 #### 14 Cumulative cost Actual Demand is randomly generated. Enter this 15 demand in row 7 after you place an order for the week 3 6 8 8 18 5 3 3 17 17 22 3 .: LL Note: the numbers for actual demand in row 15 will change each time you enter a value on the sheet. Use the current demand when you look it up. This represents random variation in the market