I would appreciate if these were done through Excel so I can apply them to other similar questions. Explanations so I can follow would be nice as well.

Problem 3 - GOODWEEK TIRES, INC. (50 points) After extensive research and development, Goodweek Tires, Inc., has recently developed a new tire, the SuperTread, 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 research and development costs so far have totaled about $10 million. The SuperTread would be put on the market beginning this year, and Goodweek expects it to stay on the market for a total of four years. Test marketing costing $5 million has shown that there is a significant market for a SuperTread-type tire. As a financial analyst at Goodweek Tires, you have been asked by your CFO, Adam Smith, to evaluate the SuperTread project and provide a recommendation on whether to go ahead with the investment. Except for the initial investment that will occur immediately, assume all cash flows will occur at year-end. Goodweek must initially invest $160 million in production equipment to make the SuperTread and the firm will use straight line depreciation. This equipment can be sold for $65 million at the end of four years. The immediate initial working capital requirement is $9 million. Thereafter, the net working capital requirements will be 15 percent of sales. Goodweek intends to sell the Super Tread to two distinct markets: 1. The original equipment manufacturer (OEM) market: The OEM market consists primarily of the large automobile companies (like General Motors) that buy tires for new cars. The following table has the projected sales and associated operating costs for the OEM market. OEM Market Revenues ($ millions) Operating costs ($ millions) Year 1 Year 2 111.848 119.5166 79.112 84.53612 Year 3 Year 4 127.7109 136.4671 90.33212 96.52552 The replacement market: The replacement market consists of all tires purchased after the automobile has left the factory. This market allows higher margins. The following table has the projected sales and associated operating costs for the Replacement market. Replacement Market Revenues ($ millions) Operating costs ($ millions) Year 1 Year 2 Year 3 Year 4 158.72 168.7749 179.4668 190.836 74.24 | 78.9431 | 83.94415 89.26201 | In addition, the SuperTread project will incur the following marketing and general administration costs: Year 1 43 Year 2 Year 3 Year 4 44.3975 45.84042 47.33023 Marketing and Gen. Admin Costs ($ Millions) | Goodweek's corporate tax rate is 21 percent. The company uses an 18.5 percent discount rate to evaluate new product decisions. Questions: 1. What are the Cash flows this project is expected to generate? (30 points) This section will be graded as follows: Prepare an income statement for the life of the project and estimate the net income for each year (5 points) Prepare a "balance sheet" i.e. "investment section" and in your analysis pay attention to how you treat: o Capital expenditure for the new production equipment (10 points) o Changes in NWC (5 points) o The research and development costs (2.5 points) o Test marketing costing (2.5 points) Finally estimate the cash flows of the project (5 points) 2. Calculate the profitability index (4 points), net present value (4 points), and internal rate of return (4 points) for the project. Should GOODWEEK TIRES undertake this project, why (4 points)? 3. How high does the discount rate on this project have to be for the project to start having a negative NPV (4 points)? Problem 3 - GOODWEEK TIRES, INC. (50 points) After extensive research and development, Goodweek Tires, Inc., has recently developed a new tire, the SuperTread, 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 research and development costs so far have totaled about $10 million. The SuperTread would be put on the market beginning this year, and Goodweek expects it to stay on the market for a total of four years. Test marketing costing $5 million has shown that there is a significant market for a SuperTread-type tire. As a financial analyst at Goodweek Tires, you have been asked by your CFO, Adam Smith, to evaluate the SuperTread project and provide a recommendation on whether to go ahead with the investment. Except for the initial investment that will occur immediately, assume all cash flows will occur at year-end. Goodweek must initially invest $160 million in production equipment to make the SuperTread and the firm will use straight line depreciation. This equipment can be sold for $65 million at the end of four years. The immediate initial working capital requirement is $9 million. Thereafter, the net working capital requirements will be 15 percent of sales. Goodweek intends to sell the Super Tread to two distinct markets: 1. The original equipment manufacturer (OEM) market: The OEM market consists primarily of the large automobile companies (like General Motors) that buy tires for new cars. The following table has the projected sales and associated operating costs for the OEM market. OEM Market Revenues ($ millions) Operating costs ($ millions) Year 1 Year 2 111.848 119.5166 79.112 84.53612 Year 3 Year 4 127.7109 136.4671 90.33212 96.52552 The replacement market: The replacement market consists of all tires purchased after the automobile has left the factory. This market allows higher margins. The following table has the projected sales and associated operating costs for the Replacement market. Replacement Market Revenues ($ millions) Operating costs ($ millions) Year 1 Year 2 Year 3 Year 4 158.72 168.7749 179.4668 190.836 74.24 | 78.9431 | 83.94415 89.26201 | In addition, the SuperTread project will incur the following marketing and general administration costs: Year 1 43 Year 2 Year 3 Year 4 44.3975 45.84042 47.33023 Marketing and Gen. Admin Costs ($ Millions) | Goodweek's corporate tax rate is 21 percent. The company uses an 18.5 percent discount rate to evaluate new product decisions. Questions: 1. What are the Cash flows this project is expected to generate? (30 points) This section will be graded as follows: Prepare an income statement for the life of the project and estimate the net income for each year (5 points) Prepare a "balance sheet" i.e. "investment section" and in your analysis pay attention to how you treat: o Capital expenditure for the new production equipment (10 points) o Changes in NWC (5 points) o The research and development costs (2.5 points) o Test marketing costing (2.5 points) Finally estimate the cash flows of the project (5 points) 2. Calculate the profitability index (4 points), net present value (4 points), and internal rate of return (4 points) for the project. Should GOODWEEK TIRES undertake this project, why (4 points)? 3. How high does the discount rate on this project have to be for the project to start having a negative NPV (4 points)