Please complete the rest showing formulas and cells used!

A D E G H K XYZ, Inc. is considering a 5-year project. The production will require net working capital investments each year equal to 15% of the projected sales. Total fixed costs are $1,350,000 per year, variable production costs are $210 per unit, and the units are priced at $345 each. The equipment needed to begin production has an intalled cost of $23,000,000. The equipment is qualified as seven-year MACRS property. The company is in the 35% marginal tax bracket and has a required rate of return on all its projects of 18%. Salvage value will be the same as the book value at the end of year 5. Please finish the project valuation and answer the following questions. All the colored cells need to 1 be filled In. 2 3 Input Area 4 Year 1 2 3 5 6 B Projected unit 5 sales 80,000 85,000 90,000 95,000 95,000 a 0 0 6 7 MACRS Rates 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4 7 4.46% 8 2 3 4 5 ($19,713,300) ($14,080,600) ($10,057,900) {$7,185,200) (5,632,700) $ (4,022,700) $ (3,972,700) $ 12,053,9001 ($8,919,400) ($12,942,100) $ (15,814,800) $ (17,868,700) 9 NWC/ year (% of projected sales 15% 10 Fixed costs $1,350,000 11 Variable cost per unit $210 12 Unit price $345 13 Equipment cost, year o $23,000,000 14 Tax Rate 35% 15 Required return 18% 16 17 1. Please complete the cash flow estimation table. What are the projected cash flows for each year? (5 points) 18 19 20 21 Year O 1 22 Ending book value ($23,000,000 {$23,000,000) 23 Depreciation OS 13,286,7001 $ 24 Depreciation (Accum) 05 13,286,700) 25 26 Sales $ 27,600,000 $ 27 Variable costs (16,800,000) 28 Fixed costs $1,350,000) 29 Depreciation (3,286,7001 30 EBIT 6,163,300 31 Taxes 12,157,1551 32 Net incorne 4,006,145 33 34 NOPAT $ 7,292,845 $ 35 Change in NWC $4,140,000) 36 CAPEX {$23,000,000) 37 Salvage value 38 Total cash flow ($23,000,000 $ 3,152,845 $ 39 40 41 2. What is the NPV and IRR for this project? Shall the project be accepted? Why? (2 points) 42 43 NPV = $2,459,868 44 IRR = 21.70% 45 29,325,000 $ 31,050,000 $ 32,775,000 $ 32,775,000 (17,850,000) (18,900,000) (19,950,000) (19,950,000) ($1,350,000) ($1,350,000) $1,350,000) ($1,350,000) 15,632,700) (4,022,700) (2,872,700) (2,053,9001 4,492.300 6,777,300 8,602,300 9,421,100 (1,572,305) (2,372,055) (3,010,805) (3,297,385) 2,919.995 4,405,245 5,591,495 6,123,715 8,552,695 $ ($258,750) 8,427,945 $ $258,750) 8,464,195 $ {$258,750) 8,177,615 $1,140,000 $5,131,300 8,205,445 $ 17,448,915 8,293,945 $ 8,169,195 $ 46 47 Type your answer here: % Change 48 3. please identify top 3 value drivers using break-even senistivity analysis (2 points) 49 50 Variable Expected Value Critical Valur 51 NWC (96) 15% 52 Variable Cost $210 53 Unit Price $345 54 Tax Rate 35%. 55 Discount Rate 18% 56 57 58 4. Using the following table and Excel scenario manager function to conduct a scenario analysis. In this analysis, please cover worst, expected, and best scenario. Please make sure 59 to change the cell references in the scenario summary table to proper variable names so your results are understandable. (2 points) 60 61 Variable Expected Value Minimum Maximum 62 NWC (%) 15% 12% 17% 63 Variable Cost 210 200 230 Please note: minimum doesn't mean warst 64 Unit Price 345 32D 355 scenario, and maximum doesn't mean best 65 Tax Rate 35% 20% 40% scenario elther. 66 Discount Rate 18% 15% 20% 67 A D E G H K XYZ, Inc. is considering a 5-year project. The production will require net working capital investments each year equal to 15% of the projected sales. Total fixed costs are $1,350,000 per year, variable production costs are $210 per unit, and the units are priced at $345 each. The equipment needed to begin production has an intalled cost of $23,000,000. The equipment is qualified as seven-year MACRS property. The company is in the 35% marginal tax bracket and has a required rate of return on all its projects of 18%. Salvage value will be the same as the book value at the end of year 5. Please finish the project valuation and answer the following questions. All the colored cells need to 1 be filled In. 2 3 Input Area 4 Year 1 2 3 5 6 B Projected unit 5 sales 80,000 85,000 90,000 95,000 95,000 a 0 0 6 7 MACRS Rates 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4 7 4.46% 8 2 3 4 5 ($19,713,300) ($14,080,600) ($10,057,900) {$7,185,200) (5,632,700) $ (4,022,700) $ (3,972,700) $ 12,053,9001 ($8,919,400) ($12,942,100) $ (15,814,800) $ (17,868,700) 9 NWC/ year (% of projected sales 15% 10 Fixed costs $1,350,000 11 Variable cost per unit $210 12 Unit price $345 13 Equipment cost, year o $23,000,000 14 Tax Rate 35% 15 Required return 18% 16 17 1. Please complete the cash flow estimation table. What are the projected cash flows for each year? (5 points) 18 19 20 21 Year O 1 22 Ending book value ($23,000,000 {$23,000,000) 23 Depreciation OS 13,286,7001 $ 24 Depreciation (Accum) 05 13,286,700) 25 26 Sales $ 27,600,000 $ 27 Variable costs (16,800,000) 28 Fixed costs $1,350,000) 29 Depreciation (3,286,7001 30 EBIT 6,163,300 31 Taxes 12,157,1551 32 Net incorne 4,006,145 33 34 NOPAT $ 7,292,845 $ 35 Change in NWC $4,140,000) 36 CAPEX {$23,000,000) 37 Salvage value 38 Total cash flow ($23,000,000 $ 3,152,845 $ 39 40 41 2. What is the NPV and IRR for this project? Shall the project be accepted? Why? (2 points) 42 43 NPV = $2,459,868 44 IRR = 21.70% 45 29,325,000 $ 31,050,000 $ 32,775,000 $ 32,775,000 (17,850,000) (18,900,000) (19,950,000) (19,950,000) ($1,350,000) ($1,350,000) $1,350,000) ($1,350,000) 15,632,700) (4,022,700) (2,872,700) (2,053,9001 4,492.300 6,777,300 8,602,300 9,421,100 (1,572,305) (2,372,055) (3,010,805) (3,297,385) 2,919.995 4,405,245 5,591,495 6,123,715 8,552,695 $ ($258,750) 8,427,945 $ $258,750) 8,464,195 $ {$258,750) 8,177,615 $1,140,000 $5,131,300 8,205,445 $ 17,448,915 8,293,945 $ 8,169,195 $ 46 47 Type your answer here: % Change 48 3. please identify top 3 value drivers using break-even senistivity analysis (2 points) 49 50 Variable Expected Value Critical Valur 51 NWC (96) 15% 52 Variable Cost $210 53 Unit Price $345 54 Tax Rate 35%. 55 Discount Rate 18% 56 57 58 4. Using the following table and Excel scenario manager function to conduct a scenario analysis. In this analysis, please cover worst, expected, and best scenario. Please make sure 59 to change the cell references in the scenario summary table to proper variable names so your results are understandable. (2 points) 60 61 Variable Expected Value Minimum Maximum 62 NWC (%) 15% 12% 17% 63 Variable Cost 210 200 230 Please note: minimum doesn't mean warst 64 Unit Price 345 32D 355 scenario, and maximum doesn't mean best 65 Tax Rate 35% 20% 40% scenario elther. 66 Discount Rate 18% 15% 20% 67