Trying to understand how to find the following below using a scenario, sensativity, and break-even analysis in Excel:

Project Parameters:

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

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

We will need to invest a total of $90,000 in manufacturing equipment. For simplicity, we will assume that this $90,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 34%.

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 $6, Cost per can $1.50

b. Base Case - Price per can $4, Cost per can $2.50

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

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

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

Scenario:

Number of cans	50000
Price per can	4
Cost per can	2.5
Fixed Cost	12000
Tax rate	34%
Discount rate	20%
Initial Investment	90000
	0	1	2	3
Revenue		$ 200,000
COGS		$ 125,000
Gross Profit		$ 75,000
Fixed Cost		$ 12,000
EBITDA		$ 63,000
Depreciation
EBIT
Tax
Net Income
NCS	$ (90,000)
NWC	$ (20,000)			$ 20,000
CFFA
NPV			Hint: It should be $10,648 for the Base Case.
IRR