Project Parameters:

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 $12, Cost per can $2

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

c. Worst Case - Price per can $4, 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 50,000 to 150,000.

You must use Data Table to show your results.

4. Do the following break-even analyses using the base case:

a. Break-even price so that NPV = 0

b. Break-even quantity so the NPV = 0

c. Break-even cost per can so that NPV = 0

You must use Goal Seek/Solver to show your results.

5. Assume that the price per can is a random draw between (3, 15) and the cost per can is a random draw between (2, 8). Take a sample of 2,000 NPVs. Draw a histogram showing your results.

You must use Data Analysis - Histogram to show your results.

Note: In each of the questions, remember to reset all your values to the base case given in the description of the problem.

Number of cans	100000
Price per can	6
Cost per can	3.5
Fixed Cost	20000
Tax rate	35%
Discount rate	15%
Initial Investment	150000
	0	1	2	3
Revenue		$ 600,000	$ 600,000	$ 600,000
COGS		$ 350,000
Gross Profit		$ 250,000
Fixed Cost		$ 20,000
EBITDA		$ 230,000
Depreciation
EBIT
Tax
Net Income
Add back Depreciation
OCF
NCS	$ (150,000)			0
NWC	$ (20,000)			$ 20,000
CFFA
NPV			Hint: It should be $224,448.92 for the Base Case.
IRR