Need to know how to do it within Excel. The second and the third pictures are from the previous problems that lead up to the question at the top that needs to be answered. Overall, the first picture is the one that needs to be answered.

Please show how you do it with the functions. Thank you.

The product tree (Bill of Materials) for Product X is shown below: -X A(2 B(5 C(3 K(2 K(3 C(2 D K(3 Construct the ERP plan of July and August for Product X, use the best option from Question 3 with the following assumptions: 1 2 Assume there are 4 weeks for each month. The lead time for each level is ONE week. There is no inventory on hand. Product X are needed in beginning of Week 4 and Week 8. 3 4 A. Determine total demand 670 Determine total defectives 33.5 B. C. Determine new values for each month based on percent defects eg (July will be 100+(100*0.05) = 105 105 115.5 126 July August September October November December 147 105 105 Month Demand July 100 August 110 September 120 October 140 November 100 December 100 Cost Total Cost $ 853,000.0 Option #1 Regualr @ 1,250 Overtime @ 1,400 Subcontract @ 1,650 100 0 0 100 10 0 100 20 0 100 20 20 100 0 0 100 0 0 750,000.00 $ 70,000.00 $ 33,000.00 $ 100 Option #2 Regualr @ 1,250 Overtime @ 1,400 10 100 20 100 20 100 20 100 0 100 0 750,000.00 $ 98,000.00 Month Demand July 100 August 110 September 120 October 140 November 100 December 100 Cost Total Cost $ 873,500.0 Subcontract @ 850 0 10 20 0 0 0 $ S 25,500.00 Option #3 Overtime @ 1,400 0 Inventory @ 850 0 Subcontract @ 1,650 0 20 0 0 20 10 0 Month Demand July 100 August 110 September 120 October 140 November 100 December 100 Cost Total Cost $ 859,000.0 Regualr @ 1,250 100 100 100 100 100 100 750,000.00 20 0 10 0 0 0 0 0 0 $ $ 84,000.00 $ 8,500.00 $ 16,500.00 The product tree (Bill of Materials) for Product X is shown below: -X A(2 B(5 C(3 K(2 K(3 C(2 D K(3 Construct the ERP plan of July and August for Product X, use the best option from Question 3 with the following assumptions: 1 2 Assume there are 4 weeks for each month. The lead time for each level is ONE week. There is no inventory on hand. Product X are needed in beginning of Week 4 and Week 8. 3 4 A. Determine total demand 670 Determine total defectives 33.5 B. C. Determine new values for each month based on percent defects eg (July will be 100+(100*0.05) = 105 105 115.5 126 July August September October November December 147 105 105 Month Demand July 100 August 110 September 120 October 140 November 100 December 100 Cost Total Cost $ 853,000.0 Option #1 Regualr @ 1,250 Overtime @ 1,400 Subcontract @ 1,650 100 0 0 100 10 0 100 20 0 100 20 20 100 0 0 100 0 0 750,000.00 $ 70,000.00 $ 33,000.00 $ 100 Option #2 Regualr @ 1,250 Overtime @ 1,400 10 100 20 100 20 100 20 100 0 100 0 750,000.00 $ 98,000.00 Month Demand July 100 August 110 September 120 October 140 November 100 December 100 Cost Total Cost $ 873,500.0 Subcontract @ 850 0 10 20 0 0 0 $ S 25,500.00 Option #3 Overtime @ 1,400 0 Inventory @ 850 0 Subcontract @ 1,650 0 20 0 0 20 10 0 Month Demand July 100 August 110 September 120 October 140 November 100 December 100 Cost Total Cost $ 859,000.0 Regualr @ 1,250 100 100 100 100 100 100 750,000.00 20 0 10 0 0 0 0 0 0 $ $ 84,000.00 $ 8,500.00 $ 16,500.00