Please solve the following question in detailed steps for Excel. I will make sure to give a thumbs up. Thank you. Capital Budgeting Analysis: A firm plans to produce and sell a new product for five years. First year sales of this product are expected to be 200,000 units and sales are expected to grow at 3.5% per year for years 2 and 3 and then at 4% for years 4 and 5. The selling price per unit of the product is $50 and variable operating costs are 48 percent of sales. The product will have fixed operating costs of $850,000 a year for the first 3 years and then $900,000 per year for the last two years.

The firm will also need to purchase some new equipment for $8 million in order to produce this product. This equipment has a five-year life and salvage value of $5.2 million. The firm expects to be able to sell some old equipment for $1.2 million today (with no depreciation or tax effects on this sale).

Depreciation is straight-line and the firms tax-rate is 25%. The new equipment that is purchased will be financed by borrowing the $8 million at a rate of 7.9% per year. Test marketing costs of $400,000 for the new product were incurred over the past two months. The new product is also expected to reduce sales of one of the firms existing products by $520,000 per year. The required return on the investment is 9 percent. Any annual working capital can be ignored.

Required:

(a) Set up a data table and then calculate the expected cash flows for the project.

(b) Find the NPV, PI, IRR of this project for the base case above.

(c) Conduct a sensitivity analysis varying the NPV over discount rates starting from 3% and going up to 30%, increasing the rate by 3% each time. Graph the NPV against the discount rates.

(d) What starting level (first year) unit sales will be needed to obtain an NPV of exactly $2.12 million?

(e) Conduct a scenario analysis for a scenario where the selling price is $80 per unit, variable operating costs are $7 per unit and starting sales are 250,000 units. There are no other changes.