Please help! Do in excel please! Goodweek Tires, Inc.

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 $$185$ million in production equipment to make the SuperTread. This equipment can be sold for $$75$

million at the end of four years. Goodweek intends to sell the SuperTread 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 SuperTread is expected to sell for $$43$ per tire. The variable cost

to produce each tire is $$31.$

The replacement market: The replacement market consists of all tires purchased after the automobile has left the factory. This market

allows higher margins; Goodweek expects to sell the SuperTread for $$64$ per tire there. Variable costs are the same as in the OEM

market.

Goodweek Tires intends to raise prices at I percent above the inflation rate; variable costs also will increase at $1$ percent above the

inflation rate. In addition, the SuperTread project will incur $$53$ million in marketing and general administration costs the first year. This

cost is expected to increase at the inflation rate in the subsequent years.Excel Master It$!$ Problems

For this Master It$!$ assignment, refer to the Goodweek Tires, Inc., case

at the end of this chapter. For your convenience, we have entered the

relevant values such as the price and variable costs in the case on the

next page. For this project, answer the following questions:

EXCEL MASTER

coverage online

a$.$ What is the profitability index of the project?

b$.$ What is the IRR of the project?

c$.$ At what OEM price would Goodweek Tires be indifferent to accepting the project? Assume the replacement market price

is constant.

d$.$ At what level of variable costs per unit would Goodweek Tires be indifferent to accepting the project?

show excel spreadsheet and formulas!