A small sporting goods company is considering investing $2500 in a project that will produce volleyballs over the next five years. The company plans to produce and sell 300 volleyballs in the first year, and expects that volume to grow by 10% each year thereafter. The unit selling price forecast the company has developed is $21 in year 1, $22 in year 2, $25 in year 3, $28 in year 4, and $31.50 in year 5. Variable costs are forecast to be a rate of $15 per unit produced, and there will be a fixed overhead cost in each year of $500.

1. (8 Marks) Use the above information to develop a simple cash flow sheet, and then apply Excel's NPV function to calculate the project value assuming a 10% discount rate. What is your answer?

Hint 1: Make sure your rates (discount, growth, variable cost) are in separate cells and built into formulas, not manually input into the formulas or else goal seek will not work!

Hint 2: Your column rows should look like this:

Year

Units Produced

Price

Revenue

Variable Cost

Fixed Cost

Net Cash Flow

2. (3 Marks) Suppose the company thinks it may be able to produce and sell more than currently planned. What growth rate of production would produce an NPV of $15,000? Hint: Goal Seek

3. (3 Marks) Suppose instead that the company thinks it can reduce its variable cost rate. What rate would produce an NPV of $15,000?

4. (8 Marks) Use the graphing function in Excel to construct a scatterplot of forecasted price versus time, and fit a linear trendline to the data. What are the coefficients (intercept and slope) of the linear model, and what is the MAPE of a linear model forecasted prices, compared to the company's forecasted prices?