wwing/contact/chx Are Throwing Chattanooga, TN X + Roel - Google Sheets X + m/spreadsheets/d/1OwnvyAdmuBzvyo4HznTfTh3W01947VDW7eB91-aY6PO/edit#gid=1352416971 x x bols Add-ons Help All changes saved in Drive Default (C.. 11 - BI&A . - . L V 00 OY.. D E F G H 1000 89000 97000 92000 77000 Question: Company X projects numbers of unit sales for a new project as follows: 81000 [year 1), 89000 (year 2), 97000 (year 3), 92000 (year 4), and 77000 (year 5). The project will require $1,500,000 in net working capital to start and require additional networking capital investments each year equal to 15% of the projected sales for subsequent years (meaning that the second year requires $1,500,000 + 15% of projected sale for the second year). NWC is recovered at the end of the fifth year. Total fixed costs are $1,850,000 per year Variable costs are $190/unit, and the units are priced at $345 each. The equipment needed to begin production has a cost of $19,500,000 The equipment is qualified for accounting as seven-year MACRS depreciation: Year 1: 14.29%, Year 2:24.49%, Year 3:17.49%, Year 4:12.49%, Year 5: 8.93% In 5 years, this equipment can be sold for 35% of its acquisition cost (original cost) The company is in the 35% marginal tax bracket and has a required rate of return of 18% (a) Summarize and estimate cash flows (b) Calculate NPV, IRR (c) Create a NPV profile corresponding to discount rates between (6%, 8%, ... 40%) (c) Do a sensitivity analysis using 2 input data table analyzing NPV given Selling price ($300, $320,...$400), and VC($160, $170, ...$210) (d) Do a scenario analysis (Best case and worst case) and report the summary results for NPV and IRR: Optimistic: Variable cost $160/unit, Fixed cost $1,600,000, selling price $400/unit Pessimistic: Variable cost $210/unit, Fred cost= $2,050,000, selling price $300/unit npr irr 0.2449 0.1749 #75550 3410550 562100 10972650 345 345 0500033465000 0.0893 1741350 15149550 $267937.12! WNUM! 0.1249 2435550 13408200 345 31740000 discount rate 6% 8% 10% 12% npr $650,941.57) 5419,118.99) ($255,156.29) 95,397.66) 345 26565000 Question 2.- o Question 3 - e A . duos ta Prisen Home End Pgup