After completing the “Blue Ray Excel Simulation” (as shown in class), you will write a brief response about your experience and submit it in Bb Learn. The Excel file needed, is attached here.

Instructions:

• Play the Blue Ray Excel Simulation (as shown during class)
• Save your completed Excel where you can find it
• Write a response to ONE the following:
  • What is something that is clearer about the topic of inventory now that you have completed the activity?
  • What is your most important takeaway (thing that you learned) from completing this simulation?
  • When completing the simulation, how did you think about (or, what was your strategy with) the challenge of ordering inventory, amount and when to place an order, with variable demand?

  • Notes: (Before simulation)

  • And:

    What do we know?

    How to handle demand?

• Submit, in Bb Learn (click the title here), your question response and completed Excel.
  • Your response to one of the questions, above, may be written directly in Bb Learn by clicking the box “Write Submission” in the Assignment Submission section. Or, you may attach a MS Word or pdf file.

Transcribed Image Text:

1 2 3 4 5 6 7 8 18. 2 19 20 21 22 24 5 Number of units ordered for next week Order cost $100 if order is placed in #5 Actual demand (check row 15 after orders are placed and enter. 7 here) 23 8 Ending inventory #3 #7 (but not less than 0) Average inventory (#3+ # 8)/2 25 A 28 Inventory Management Simulation A discount retail store buys a particular model of Blue Ray 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. Your objective is to minimize total inventory costs (the sum of ordering, carrying, and shortage costs) for the 14-week period. For this activity you only need to decide how much to order for the current week and enter it in row 5. Excel will then do the calculations for you. Steps: 1) For the current week, enter a value in row 5 (how much you will order). 9 2) After you have decided on an order quantity for the current week, enter the Actual Demand, row 7. Check row 15 for the actual demand fro the current week and enter it into row 7. This number changes each time you do something in Excel, so you will not know exactly what demand will be before you look it up. Use the current value when you are looking it up. Excel will then do the calculations for you. 10 11 3) Go back to step #1 for the next week 12 Please note that the rest of the worksheet (besides rows 5 and 7) is locked and cannot be edited. 13 14 15 16 Row Measure 17 1 Beginning inventory #8 (ending inv.) from previd Received Units from order placed previous week 3 Available inventory (#1 + #2) 4 32 6 33 34 35 36 37 9 Forecasted demand. 26 10 Carrying cost = $2* #9 27 11 Units short (if #7 > #3, then enter # 7-# 3; otherwi 12 Shortage cost $60* #11 30 14 Cumulative cost 31 29 13 Total cost #6 #10 #12 B 15 = -----1- C 1 7 0 7 5 22 $100 3 4 5.5 $11 0 0 D $111 $111 2 41 6 22 26 E 5 3 $- 6 4 4 G 8 6 5 H 14 16 6 I 12 12 J Week Number 7 8 8 9 K 7 5 9 L 6 5 10 $- 4 6 11 $- Actual Demand is randomly generated. Enter this demand in row 7 after you place an order for the 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. N 14 14 12 $ - 0 24 28 13. $- 22 33 $ P 14 6 5 1 2 3 4 5 6 7 8 18. 2 19 20 21 22 24 5 Number of units ordered for next week Order cost $100 if order is placed in #5 Actual demand (check row 15 after orders are placed and enter. 7 here) 23 8 Ending inventory #3 #7 (but not less than 0) Average inventory (#3+ # 8)/2 25 A 28 Inventory Management Simulation A discount retail store buys a particular model of Blue Ray 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. Your objective is to minimize total inventory costs (the sum of ordering, carrying, and shortage costs) for the 14-week period. For this activity you only need to decide how much to order for the current week and enter it in row 5. Excel will then do the calculations for you. Steps: 1) For the current week, enter a value in row 5 (how much you will order). 9 2) After you have decided on an order quantity for the current week, enter the Actual Demand, row 7. Check row 15 for the actual demand fro the current week and enter it into row 7. This number changes each time you do something in Excel, so you will not know exactly what demand will be before you look it up. Use the current value when you are looking it up. Excel will then do the calculations for you. 10 11 3) Go back to step #1 for the next week 12 Please note that the rest of the worksheet (besides rows 5 and 7) is locked and cannot be edited. 13 14 15 16 Row Measure 17 1 Beginning inventory #8 (ending inv.) from previd Received Units from order placed previous week 3 Available inventory (#1 + #2) 4 32 6 33 34 35 36 37 9 Forecasted demand. 26 10 Carrying cost = $2* #9 27 11 Units short (if #7 > #3, then enter # 7-# 3; otherwi 12 Shortage cost $60* #11 30 14 Cumulative cost 31 29 13 Total cost #6 #10 #12 B 15 = -----1- C 1 7 0 7 5 22 $100 3 4 5.5 $11 0 0 D $111 $111 2 41 6 22 26 E 5 3 $- 6 4 4 G 8 6 5 H 14 16 6 I 12 12 J Week Number 7 8 8 9 K 7 5 9 L 6 5 10 $- 4 6 11 $- Actual Demand is randomly generated. Enter this demand in row 7 after you place an order for the 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. N 14 14 12 $ - 0 24 28 13. $- 22 33 $ P 14 6 5

Answer rating: 100% (QA)

Question 1 What was something that is clearer about the topic of inventory now that you have completed the activity The most important thing that I learned from completing this simulation was how diff... View the full answer