Based on the above first 2 pictures of case study fill up the bottom 2 pictures of excel sheet.

Nancy Barfield, production planner at Illustrious Corporation, a small assembly shop, was preparing the operating plan for the next 10 weeks as she did each Friday. One of the products she had to deal with almost every week was X500, a product assembled for a regular customer. Even though the customer gave Nancy a forecast of required shipments of X500 every two weeks, the forecasts frequently changed. a The manufacturing and assembly process for X500 began with part 1590, which Illustrious bought from another local company. The H590s came with a number of holes. Illustrious first had to tap the holes on some of the H590s for mounting screws. Illustrious carried the tapped H590s as part P712. Just this last week Illustrious had returned a shipment of 900 H590s to its supplier because of poor quality. Nancy did not expect the shipment to be replaced until the following Monday, 10 days hence. The P712 was then attached to a 6418 to form part Q307. In a similar operation an untapped H590 was attached to a 6418 to form 1600, which Illustrious carried as a separate part number even though the only difference between it and Q307 was its untapped Lples. Nancy knew inventory was tight on G418 because of a recently settled strike at the supplier's plant. Illustrious workers next mounted an F416 on each Q307 to make the subassembly L477. Illustrious purchased the F416 in kit form, the kits included all the necessary mounting screws and accessories. X500s were formed by screwing two L600s onto one L477 in a final assembly operation. Nancy reviewed the projected week-ending inventory levels for X500 and each of its components, the latest shipment forecast, and the standard lead times. The relevant figures were: Part Inventory (units) Lead time (weeks) X500 11590 P712 G118 400 210 115 290 0 1 1 2 Q307 490 3 L600 620 3 F416 2477 750 310 2 2 Page 2 Week Week Week 2 u u N Demand 205 395 100 295 265 Week 6 7. 8 Demand 300 215 50 600 310 Demand 150 $25 425 0 120 12. 13 14 15 Demand 200 190 450 0 350 18 9 5 10 20 Week 1 2 3. 5 7 8 10 12 13 15 14 Demand 16 19 13 20 205 395 100 295 205 300 215 50 600 310 150 525 425 0 120 200 150 450 0 250 1 2 3 4 5 6 7 8 9 10 11 12 14 15 15 15 15 15 15 Period Gross requirement Scheduled receipt Net Requirement Projected On-hand Inventory Planned order receipts X500 LTEO L4L 400 Planned order release Pened 1 2 3 24 5 6 77 8 9 10 11 12 13 1 15 15 12 19 20 Gross requirement Scheduled receipt Net Requirement Projected On-hand Inventory Planned order receipts LTE Planned order release Penod 2 2 5 6 7 8 9 10 11 12 13 14 15 16 18 19 20 Gross requirement Scheduled receipt L600 LE3 L4L Net Requirement Projected On-hand Inventory Planned order receipts 620 Planned order release Period 1 2 2 4 6 7 8 9 10 11 12 13 15 17 16 18 19 20 F416 Gross requirement Scheduled receipt Net Requirement Projected On-hand Inventory Planned order receipts L4L 750 Planned order release 6 5 7 B 4 3 10 2 11 13 12 17 16 15 14 18 20 19 Period Gross requirement 1 2 3 5 G 7 Period Gross requirement Scheduled receipt 9 9 10 11 12 1 14 15 16 17 18 19 20 Net Requirement 307 LT=3 L4L Projected On-hand Inventory 490 Planned order receipts Planned order release Period 1 2 3 24 5 6 7 8 9 10 11 12 13 14 15 16 17 18 20 Gross requirement Scheduled receipt Net Requirement P712 LT=1 L4L Projected On-hand Inventory 115 Planned order receipts Planned order release Period 1 2 3 14 5 6 8 9 10 11 12 13 14 15 16 17 18 19 20 Gross requirement Scheduled receipt Net Requirement G418 LT2 L4L Projected On-hand Inventory 290 Planned order receipts Planned order release Period 2 13 4 -5 6 7 8 9 10 11 12 13 14 15 15 18 19 20 Gross requirement Scheduled receipt Net Requirement H590 LT=1 LAL Projected On-hand Inventory 210 Planned order receipts Planned order release Nancy Barfield, production planner at Illustrious Corporation, a small assembly shop, was preparing the operating plan for the next 10 weeks as she did each Friday. One of the products she had to deal with almost every week was X500, a product assembled for a regular customer. Even though the customer gave Nancy a forecast of required shipments of X500 every two weeks, the forecasts frequently changed. a The manufacturing and assembly process for X500 began with part 1590, which Illustrious bought from another local company. The H590s came with a number of holes. Illustrious first had to tap the holes on some of the H590s for mounting screws. Illustrious carried the tapped H590s as part P712. Just this last week Illustrious had returned a shipment of 900 H590s to its supplier because of poor quality. Nancy did not expect the shipment to be replaced until the following Monday, 10 days hence. The P712 was then attached to a 6418 to form part Q307. In a similar operation an untapped H590 was attached to a 6418 to form 1600, which Illustrious carried as a separate part number even though the only difference between it and Q307 was its untapped Lples. Nancy knew inventory was tight on G418 because of a recently settled strike at the supplier's plant. Illustrious workers next mounted an F416 on each Q307 to make the subassembly L477. Illustrious purchased the F416 in kit form, the kits included all the necessary mounting screws and accessories. X500s were formed by screwing two L600s onto one L477 in a final assembly operation. Nancy reviewed the projected week-ending inventory levels for X500 and each of its components, the latest shipment forecast, and the standard lead times. The relevant figures were: Part Inventory (units) Lead time (weeks) X500 11590 P712 G118 400 210 115 290 0 1 1 2 Q307 490 3 L600 620 3 F416 2477 750 310 2 2 Page 2 Week Week Week 2 u u N Demand 205 395 100 295 265 Week 6 7. 8 Demand 300 215 50 600 310 Demand 150 $25 425 0 120 12. 13 14 15 Demand 200 190 450 0 350 18 9 5 10 20 Week 1 2 3. 5 7 8 10 12 13 15 14 Demand 16 19 13 20 205 395 100 295 205 300 215 50 600 310 150 525 425 0 120 200 150 450 0 250 1 2 3 4 5 6 7 8 9 10 11 12 14 15 15 15 15 15 15 Period Gross requirement Scheduled receipt Net Requirement Projected On-hand Inventory Planned order receipts X500 LTEO L4L 400 Planned order release Pened 1 2 3 24 5 6 77 8 9 10 11 12 13 1 15 15 12 19 20 Gross requirement Scheduled receipt Net Requirement Projected On-hand Inventory Planned order receipts LTE Planned order release Penod 2 2 5 6 7 8 9 10 11 12 13 14 15 16 18 19 20 Gross requirement Scheduled receipt L600 LE3 L4L Net Requirement Projected On-hand Inventory Planned order receipts 620 Planned order release Period 1 2 2 4 6 7 8 9 10 11 12 13 15 17 16 18 19 20 F416 Gross requirement Scheduled receipt Net Requirement Projected On-hand Inventory Planned order receipts L4L 750 Planned order release 6 5 7 B 4 3 10 2 11 13 12 17 16 15 14 18 20 19 Period Gross requirement 1 2 3 5 G 7 Period Gross requirement Scheduled receipt 9 9 10 11 12 1 14 15 16 17 18 19 20 Net Requirement 307 LT=3 L4L Projected On-hand Inventory 490 Planned order receipts Planned order release Period 1 2 3 24 5 6 7 8 9 10 11 12 13 14 15 16 17 18 20 Gross requirement Scheduled receipt Net Requirement P712 LT=1 L4L Projected On-hand Inventory 115 Planned order receipts Planned order release Period 1 2 3 14 5 6 8 9 10 11 12 13 14 15 16 17 18 19 20 Gross requirement Scheduled receipt Net Requirement G418 LT2 L4L Projected On-hand Inventory 290 Planned order receipts Planned order release Period 2 13 4 -5 6 7 8 9 10 11 12 13 14 15 15 18 19 20 Gross requirement Scheduled receipt Net Requirement H590 LT=1 LAL Projected On-hand Inventory 210 Planned order receipts Planned order release