6. (***) (Microsoft Excel problem). The following figure shows a Microsoft Excel spreadsheet that calculates the number of kanban production cards needed, based on the MRP planned orders. Re-create this spreadsheet in Excel. Your spreadsheet should calculate new results any time a change is made to any of the highlighted cells.

Your formatting does not need to be the same, but your answers should be. To test your spreadsheet, change the production lead time to 2.5 hours and the container size to 144. The number of production cards (not rounded up) for week 1 should be 6.84, and there should be 2.69 hours’ worth of inventory. Note: To round up the kanban card calculation, use Excel’s ROUNDUP function.

Transcribed Image Text:

2 3 40 A B C DE F G H 1 Using MRP Planned Orders to Determine the Number of Kanban Production Cards Needed Hours per week: 4 Production lead time (T): 3 hours 5 6 Container size (C): Safety factor (x): 5.0% 300 7 8 WEEK 9 2 3. 4 5 10 Planned orders (from MRP): 15,000 16,000 15,000 15,000 14,500 6 14,000 7 13,000 11 12 13 Hourly demand (D): 375 400 375 375 362.5 350 325 14 15 # of production cards (not rounded) 16 #of production cards (rounded up) 3.94 4.20 3.94 3.94 3.81 3.68 3.41 4 5 4 4 4 4 4 17 18 Hours' worth of inventory 3.20 3.75 3.20 3.20 3.31 3.43 3.69 K L