Question

Suppose, we can sell 100,000 cans of shark attractant per year at a price of $6.00 per can. It costs us about $3.50 per can to make the attractant. A new product such as this one typically has only a three-year life. We require a 15% return on new products.

Fixed costs for the project will run $20,000 per year.

We will need to invest a total of $150,000 in manufacturing equipment. For simplicity, we will assume that this $150,000 will be fully depreciated over the three year life of the project.

The project will require an initial $20,000 investment in net working capital.

The tax rate is 35%.

Questions:

1. Set-up the problem below in the space provided. Calculate the NPV. Calculate the IRR.

2. Do a Scenario Analysis with the three following scenarios:

a. Best Case - Price per can $10, Cost per can $2.50

b. Base Case - Price per can $6, Cost per can $3.50

c. Worst Case - Price per can $5, Cost per can $4

You must use Scenario Manager to show your results.

3. Do a Sensitivity Analysis where you vary the number of cans from 60,000 to 120,000.

Note: Please proper explain in excel with formulas and do not copy from Chegg. otherwise i have to report the answer. (use in excel formulas.)