Guidelines: This is a 15 point, individual assignment. 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:
- 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.
A B D E F G H I J K L N 0 P Inventory Management Simulation 1 2 3 4 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. 5 6 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. 7 9 8 Steps: 1) For the current week, enter a value in row 5 (how much you will order). 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 10 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. 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 Week Number 16 Row Measure 1 2 3 4 5 6 7 8 9 10 11 12 13 14 17 1 Beginning inventory = #8 (ending inv.) from previd 71 4 18 2 Received = Units from order placed previous week 0 221 19 3 Available inventory (#1 + #2) 7 26 20 4 Forecasted demand 5 5 6 81 14 12 8 7 6 41 141 24 22 5 21 5 Number of units ordered for next week 22 22 $100 $- S- 3 - $ - $ - $ - - 6 Order cost = $100 if order is placed in #5 Actual demand (check row 15 after orders are placed and enter 7 here) 23 3 24 8 Ending inventory = #3 - #7 (but not less than 0) 4 4 25 9 9 Average inventory = (#3 + #8)/2 5.5 26 10 Carrying cost - $2 * #9 $11 27 11 Units short (if #7 > #3, then enter #7-#3; otherwi 01 28 12 Shortage cost = $60 * #11 0 29 13 Total cost = #6 + #10 + #12 $111 14 Cumulative cost $111 30 31 Actual Demand is randomly generated. Enter this 15 demand in row 7 after you place an order for the 6 4 6 16 12 9 5 5 6 14 28 33 6 32 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 33 34 35 36 37 A B D E F G H I J K L N 0 P Inventory Management Simulation 1 2 3 4 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. 5 6 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. 7 9 8 Steps: 1) For the current week, enter a value in row 5 (how much you will order). 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 10 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. 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 Week Number 16 Row Measure 1 2 3 4 5 6 7 8 9 10 11 12 13 14 17 1 Beginning inventory = #8 (ending inv.) from previd 71 4 18 2 Received = Units from order placed previous week 0 221 19 3 Available inventory (#1 + #2) 7 26 20 4 Forecasted demand 5 5 6 81 14 12 8 7 6 41 141 24 22 5 21 5 Number of units ordered for next week 22 22 $100 $- S- 3 - $ - $ - $ - - 6 Order cost = $100 if order is placed in #5 Actual demand (check row 15 after orders are placed and enter 7 here) 23 3 24 8 Ending inventory = #3 - #7 (but not less than 0) 4 4 25 9 9 Average inventory = (#3 + #8)/2 5.5 26 10 Carrying cost - $2 * #9 $11 27 11 Units short (if #7 > #3, then enter #7-#3; otherwi 01 28 12 Shortage cost = $60 * #11 0 29 13 Total cost = #6 + #10 + #12 $111 14 Cumulative cost $111 30 31 Actual Demand is randomly generated. Enter this 15 demand in row 7 after you place an order for the 6 4 6 16 12 9 5 5 6 14 28 33 6 32 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 33 34 35 36 37
