Assignment

AP.docx

You are a financial analyst for a major consumer products company. They are considering

the product launch of a new cereal with the following expenditures:

New Plant Cost: $10 million ($5 million upfront, $5 million at end of 1st year). Plant will

take 1 year to construct. $3mm salvage value at end of 10 years

Feasibility studies: $50,000 upfront (will be expended regardless of whether the

project goes forward or not).

Depreciation: Straight line over 5 years to zero, beginning at end of 1st year

Unit Sales: 1 million units (sales begin at end of 1st year and grow at 5% p.a.

thereafter)

Price per Unit: $5.75 per unit, growing at 2% inflation per year

COGS: 60% of Sales

Fixed costs: $150,000 per annum

Working capital: $300,000 incurred at end of first year and

recouped at end of 10 years

Cost of Capital: 11%

Marginal Tax Rate: 25%

1. Forecast the project's after-tax cashflows for 10 years using excel

2. What is the NPV?

3. What is the IRR?

4. Which single variable (holding the others constant) has the greatest impact on the NPV?

(change each of the discount rate, price per unit, COGS, inflation rate, and tax rate by 1%

holding the other variables constant).

5. Create a 2-variable data table (columns and rows) showing NPV at 10%, 11%, and 12%

cost of capital and 20%, 25%, and 30% tax rates.