Question: 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.
6. Is this project is worth pursuing? Why or why not?