EXCEL MASTER IT$!$ PROBLEM $-$ Please help and PLEASE DO IN EXCEL.. easy$-$to$-$follow steps would be greatly appreciated! :$)$

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. 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 SuperTreadwould 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 $$120$ million in production equipment to make the SuperTread. This equipment can be sold for $$51$ 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 $($e$.$g$.,$ GeneralMotors$)$ that buy tires for new

cars. In the OEM market, the SuperTread is expected to sell for $$36$ per tire. The variable cost to produce each tire is $$18.$

The Replacement Market. The replacement market consists of all tires purchased after the automobile has left the factory. This market allows for higher margins, and

Goodweek expects to sell the SuperTread for $$59$ per tire there. Variable costs are the same as in the OEM market.

Goodweek Tires intends to raise prices at $1$ percent above the inflation rate; variable costs will also increase $1$ percent above the inflation rate. In addition, the SuperTread

project will incur $$25$ million in marketing and general administration costs the first year. This cost is expected to increase the inflation rate in subsequent years.

Goodweek's corporate tax rate is $21$ percent. Annual inflation is expected to remain constant at $3\mathrm{.}25$ percent. The company uses a $15\mathrm{.}9$ percent discount rate to evaluate

new product decisions.

Automotive industry analysts expect automobile manufacturers to produce$2$ million new cars this year and project that production will grow at $2\mathrm{.}5$

percent per year thereafter. Each new car needs four tires $($the spare tires are undersized and are in a different category$).$ Goodweek Tires expects the SuperTread to capture

$11$ percent of the OEM market. Industry analysts estimate that the replacement tire market size will be $16$ million tires this year and that it will grow at $2$ percent annually.

Goodweek expects the super read to capture an $8$ percent market share. The appropriate depreciation schedule for the equipment is the seven$-$year MACRSschedule. The

immediate initial working capital requirement is $$11$ million. Thereafter, the net working capital requirements will be $15$ percent of sales.

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

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

c$.$ What is the NPV of the project?

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

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