** Please show calculations in excel, not just answers. Thank you!

After extensive research and development, Baroto Tires, Inc., has recently developed a new tire, the SuperGrip, 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 SuperGrip would be put on the market beginning this year, and

Baroto 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 SuperGrip-type tire. As a financial analyst at Baroto Tires, you have been asked by your CFO, Xavi Alonso, to evaluate the SuperGrip 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.

Baroto must initially invest $200 million in production equipment to make the SuperGrip. This equipment can be sold for $50 million at the end of four years. Baroto intends to sell the SuperGrip to two distinct markets:

• 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. In the OEM market, the SuperGrip is expected to sell for $41 per tire. The variable cost to produce each tire is $29.

• The replacement market: The replacement market consists of all tires purchased after the automobile has left the factory. This market allows higher margins; Baroto expects to

sell the SuperGrip for $62 per tire there. Variable costs are the same as in the OEM market.

Baroto Tires intends to raise prices at 1 percent above the inflation rate; variable costs will also increase at 1 percent above the inflation rate. In addition, the SuperGrip project will incur $43 million in marketing and general administration costs the first year. This cost is expected to increase at the inflation rate in the subsequent years. Barotos corporate tax rate is 40 percent. Annual inflation is expected to remain constant at 3.25 percent.

The company uses a 14% risk adjusted discount rate to evaluate new product decisions. Automotive industry analysts expect automobile manufacturers to produce 6.2 million new cars this year and production to grow at 2.5 percent per year thereafter. Each new car needs four tires (the spare tires are undersized and are in a different category). Baroto Tires expects the SuperGrip to capture 11 percent of the OEM market.

Industry analysts estimate that the replacement tire market size will be 32 million tires this year and that it will grow at 2 percent annually. Baroto expects the SuperGrip to capture an 8 percent market share.

The appropriate depreciation schedule for the equipment is the seven-year MACRS depreciation schedule. The immediate initial working capital requirement is $9 million. Thereafter, the net working capital requirements will be 15 percent of sales. What are the NPV, payback period, discounted payback period, IRR, and PI on this project?

MACRS Schedules:

Year	3 years	5 years	7 years	10 years	15 years	20 years
1	33.33%	20.00%	14.29%	10.00%	5.00%	3.75%
2	44.45%	32.00%	24.49%	18.00%	9.50%	7.22%
3	14.81%	19.20%	17.49%	14.40%	8.55%	6.68%
4	7.41%	11.52%	12.49%	11.52%	7.70%	6.18%
5		11.52%	8.93%	9.22%	6.93%	5.71%
6		5.76%	8.92%	7.37%	6.23%	5.29%
7			8.93%	6.55%	5.90%	4.89%
8			4.46%	6.55%	5.90%	4.52%
9				6.56%	5.91%	4.46%
10				6.55%	5.90%	4.46%
11				3.28%	5.91%	4.46%
12					5.90%	4.46%
13					5.91%	4.46%
14					5.90%	4.46%
15					5.91%	4.46%
16					2.95%	4.46%
17						4.46%
18						4.46%
19						4.46%
20						4.46%
21						2.23%