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 be filled in. Input Area Year Projected 1 2 3 4 5 6 7 8 unit sales 80,000 85,000 90,000 95,000 95,000 0 0 0 MACRS Rates 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% NWC/year (% of projected sales) Fixed costs Variable cost per unit Unit price Tax Rate Required return Equipment cost, year 0 15% $1,350,000 $210 $345 $23,000,000 35% 18% 1. Please complete the cash flow estimation table. What are the projected cash flows for each year? (5 points) Ending book value Depreciation (annual) Depreciation (Accum) Sales Variable costs Fixed costs Depreciation (annual) EBIT Taxes Year 0 $23,000,000.00 Net income (without considering interests) NOPAT Change in NWC CAPEX Depreciation (annual) Salvage value Total cash flow 1 $23,000,000 2 3 4 $23,000,000 $23,000,000 $ 3,286,700.00 $ 5,632,700 $ 4,022,700 5 $23,000,000 $23,000,000 $2,872,700 $ 2,053,900 $ 3,286,700.00 $ 8,919,400 $ 12,942,100 $ 15,814,800 $ 17,868,700 $ 27,600,000 $ 29,325,000 $31,050,000 $ 32,775,000 $ 32,775,000 16,800,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 $1,350,000 3,286,700 5,632,700 4,022,700 2,872,700 2,053,900 6,163,300 4,492,300 6,777,300 8,602,300 9,421,100 2,157,155 1,572,305 2,372,055 3,010,805 3,297,385 4,006,145 2,919,995 4,405,245 5,591,495 6,123,715 4,006,145 $ 2,919,995 $ 4,405,245 $ 5,591,495 $ 6,123,715 $23,000,000 $23,000,000 ($23,000,000) $19,713,300 $14,080,600 $10,057,900 (15,707,155) (7,154,460) 1,273,485 $7,185,200 $5,131,300 9,737,680 17,915,295 2. What is the NPV and IRR for this project? Shall the project be accepted? Why? (2 points) NPV = IRR = Type your answer here: There are two factors in which why the company should reject this project. The first is that the NPV is negative. Secondly, the IRR is lower than the return rate of 18% and so therefore, the company should reject the project 3. please identify top 3 value drivers using break-even senstivity analysis (3 points) Variable NWC (%) Variable Cost Unit Price Tax Rate Discount Rate Expected Value Critical Value % Change 15% $210 $345 35% 18% 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 to change the cell references in the scenario summary table to proper variable names so your results are understandable. (3 points) Variable Expected Value Minimum NWC (%) 15% 12% Maximum 17% Variable Cost 210 200 230 Unit Price 345 320 355 Tax Rate 35% 30% 40% Discount Rate 18% 15% 20% Please note: minimum doesn't mean worst scenario, and maximum doesn't mean best scenario either.