Question

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. 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. 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. Suppose instead that the company thinks it can reduce its variable cost rate. What rate would produce an NPV of $15,000?

4. 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?

Answer 1

a) 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?

Particulars	Year 0	1	2	3	4	5
Investment (a)	(2,500.00)
Unit produced (b)		300.00
Growth Rate in product volume			10.00%	10.00%	10.00%	10.00%
Unit produced (b)			330.00	363.00	399.30	439.23
Sale Price per unit (c )		21.00	22.00	25.00	28.00	31.50
Variable Cost per unit (d)		15.00	15.00	15.00	15.00	15.00
Contribution per unit (e=c-d)		6.00	7.00	10.00	13.00	16.50
Total Contribution (f=e*b)		1,800.00	2,310.00	3,630.00	5,190.90	7,247.30
Less : Fixed Cost (g)		500.00	500.00	500.00	500.00	500.00
Cash Flow (h=f-g)	(2,500.00)	1,300.00	1,810.00	3,130.00	4,690.90	6,747.30
NPV	9,922.79

Excle screen shot with formula :

b) 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?

23.52%

Particulars	Year 0	1.00	2.00	3.00	4.00	5.00
Investment (a)	(2,500.00)
Unit produced (b)		300.00	370.57	457.75	565.42	698.44
Growth Rate in product	23.52%
Sale Price per unit (c )		21.00	22.00	25.00	28.00	31.50
Variable Cost per unit (d)		15.00	15.00	15.00	15.00	15.00
Contribution per unit (e=c-d)		6.00	7.00	10.00	13.00	16.50
Total Contribution (f=e*b)		1,800.00	2,594.00	4,577.45	7,350.52	11,524.18
Less : Fixed Cost (g)		500.00	500.00	500.00	500.00	500.00
Cash Flow (h=f-g)	(2,500.00)	1,300.00	2,094.00	4,077.45	6,850.52	11,024.18
NPV	15,000.00

Note : Using goal seek function from what if analsyis under Data tab of excel you can find out. Under goal seek following dialog box appears and you have to provide appropriate values

c) 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?

A - Data table

Year (X Axis)	1.00	2.00	3.00	4.00	5.00
Sales Price (Y Axis)	21.00	22.00	25.00	28.00	31.50

Scattered diagram with linear trend

Note : Go to insert tab under excel and go to chart and select scattered chart. Thereafter add line line

Find slope and interecept with excel formula :

Slope	2.70
Y Intercept	17.40

Excel screenshot with formula :

Find MAPE :

Year	1	2	3	4	5
Forecast price	20.10	22.80	25.50	28.20	30.90
Actual Price	21.00	22.00	25.00	28.00	31.50
Absolute % error	0.04	(0.04)	(0.02)	(0.01)	0.02
MAPE	-0.03%

Note : The forecast value is Intercept+Slope

The absolute % error is worked out (Actual-forecast)/Actual*100

MAPE is average of Absolute % error