please show excel work.

0 Qty Per Problem 2.4 Material Requirements a. One hundred twenty units of end item Z are needed at the beginning of week 7. Prepare a MRP for component C. Take into account that on hand there are 40 units of Z, 70 units of A, 100 units of B, and 30 units of C. Also there is a scheduled receipt of 20 units of component in week 4. Lead times are two weeks for Zand B, and one week for the other components. Lot-for-lot ordering will be used for all items. Lot-for-lot means the lot size equals the gross requirement Complete the spreadsheet cells colored in blue. Z = 120-40 = 80 units required at 2 per LT-1 wk Lot-for-Lot ROP=0 week 7 -2wk LT-week 5 Part A Week 1 Week 2 Week 3 Week 4 Week 5 Gross Requirements 0 0 0 160 Level Item Net Requirements 70 0 Start Inventory 70 70 70 90 Scheduled Receipts ..2 Planned Order Receipt 160 ..2 Planned Order Release 160 .1 ..2 LT-2 wks Lot-for-Lot ROP O ..2 Part B Week 1 Week 2 Week 3 Week 4 Week 5 2 - 120-40 = 80 units required at 4 per Gross Requirements o 320 week 7 - 2wk LT-week 5 Net Requirements Start Inventory 100 Scheduled Receipts Planned Order Receipt Planned Order Release Z 70 70 .1 2 D 3 2 4 B E 2 2 # 0 0 0 Lot-for-Lot LT-1 wk Week 1 Week 2 Week 3 ROP-O Week 4 Week 5 480 A: 3 per/B: 2 per Part C Gross Requirements Net Requirements Start Inventory Scheduled Receipts Planned Order Receipt Planned Order Release 30