$1.$ What are the investment cash outflows in years $0$ through $4?$

$2.$ What are the operating cash inflows in years $1$ through $4?$

$3.$ What are the terminal cash flows at year $4?$

$4.$ What are the total cash flows in years $0$ through $4?$

$5.$ What is the Net Present Value $($at $15\%)$ and IRR of the project?

After extensive development and testing, Florida Tires has recently developed a new tire, the Wet$/$Dry$.$ R&D costs so far have totaled $$4$ million. Wet$/$Dry would be put on the market beginning in Year $1,$ and Florida expects it to sell for $4$ years. If Wet$/$Dry is accepted, test marketing of $$8$ million will be spent to ensure its advertising efforts are successful.

You have been tasked to evaluate the Wet$/$Dry project and determine its incremental cash flows. The initial investment will occur in Year $0,$ and subsequent cash flows will come in Years $1,2,3$ and $4.$

Florida$$s initial investment requires $$148$ million in production equipment to make Wet$/$Dry$.$ This equipment will be sold for $$15$ million at the end of Year $4.$ Florida intends to sell Wet$/$Dry to $2$ distinct markets:

$1.$ The original equipment manufacturer $($OEM$)$ market $$ This consists of large automobile manufacturers, like Toyota, that buy tires for new cars. In the OEM market, Wet$/$Dry is expected to sell for $$48$ per tire. The variable cost $($COGS$)$ to produce each tire is $$34.$

$2.$ The replacement market $$ This consists of all tires purchased after the automobile has left the factory. This market allows for higher margins; Wet$/$Dry will sell for $$68$ per tire here. Variable costs $($COGS$)$ are the same as in the OEM market.

Florida intends to raise prices at $1\%$ above the inflation rate, and variable costs $($COGS$)$ will also increase at $1\mathrm{.}5\%$ above the inflation rate. In addition, Wet$/$Dry will incur $$43$ million in marketing costs during Year $1,$ and this cost is expected to increase at the inflation rate in following years. Annual inflation is expected to be $3\mathrm{.}75\%$ during the life of Wet$/$Dry$.$

Florida$$s income tax rate is $25\%.$ Wet$/$Dry will be partially funded by a $$75$ million bank loan carrying an interest rate of $8\%.$ The balance of this bank loan remain at $$75$ million in years $1$ through $4.$

Auto industry analysts expect automobile manufacturers to produce $7\mathrm{.}8$ million new cars in Year $1,$ and for production to grow at a $3\%$ annual rate. Each new car needs only $4$ tires, as smaller spare tires are in a different product category. Florida expects Wet$/$Dry to capture an $9\mathrm{.}5\%$ share of the OEM market.

Industry analysts estimate the replacement market will be $39$ million tires in Year $1,$ and that it will grow $6\%$ per year. Florida expects Wet$/$Dry to capture a $7\%$ share of the replacement market.

The investment in production equipment will be depreciated using the $5-$year MACRS schedule shown here $($for years $1,2,3$ and $4,$ the expected life of the project$)$:

Year $120\%$ Year $319\%$

Year $232\%$ Year $412\%$

Florida also must invest in net working capital to support the same year$$s activity. Accounts receivable will equal $40$ days of sales revenue, inventory will equal $60$ days of cost of goods sold, and accounts payable will equal $33$ days of cost of goods sold. This means NWC in Year $1$ supports Year $1$ activity, NWC in Year $2$ supports Year $2$ activity, and so on$.$

Wet$/$Dry will also receive administrative support from finance, marketing, and IT$,$ and will receive an overhead cost allocation equal to $2\%$ of each year$$s sales. This is an accounting charge and does not involve any cash.

What is the incremental investing, operating, total, and terminal cash flows for the proposed investment in Wet$/$Dry tires? What is the NPV and IRR? Do not calculate payback period. NEED IN EXCEL