After extensive research and development, Goodyear has recently developed a new tire, the SuperTread Plus, and must decide whether to make the investment necessary to produce and market it. The tire would be ideal for drivers doing a large amount of wet weather and off-road driving in addition to normal freeway usage. The SuperTread Plus would be put on the market in the next year and they expect it to stay on the market for a total of 8 years. You have just been asked by the CFO to evaluate the project and provide a recommendation on whether to go ahead with the investment. Except for the initial investment that will occur immediately (t=0), assume all cash flows will occur at year end (the project timeline extends from t=0 to t=8, the operating years are 1 through 8). Goodyear must initially (t=0) invest $150 million in production equipment to make the SuperTread Plus. They intend to sell this tire exclusively to the OEM market. They estimate that new U.S. auto sales will be 17 million cars in each year of the project. The marketing department believes the SuperTread Plus will capture 7.5% of the OEM market each year. The sales price will be $75 per tire each year. Production costs per tire are estimated to be $61 per tire sold each year. Administrative costs are estimated to be $20,000,000 each year (t=1 through t=8). Assume you can depreciate the entire $150 million cost of the new production equipment over 8 years using the 7-year MACRS schedule (see chapter appendix or following page) starting in year 1. The project has zero salvage value when it is ended at t=8 years. Goodyears corporate tax rate is 21% and the finance department has calculated that the appropriate discount rate for this project is 14%. The project requires one NWC investment at t=0 equal to 6% of one years annual sales (such as year t=1 sales) it will then fully recover this single NWC investment when the project ends at t=8. If taxable losses are incurred, they can be used in that year. And one more important fact remember that each new car needs 4 tires. (100 points) 1. Estimate the NPV and IRR of this project. Should Goodyear do this project? You can do all of the analysis of this Problem Set 3 using Excel if you wish.

Needing to see excel formulas on this so I can understand how it is calculated