Question

McGilla Golf has decided to sell a new line of golf clubs. The clubs will sell for $875 per set and have a variable cost of $430 per set. The company has spent $150,000 for a marketing study that determined the company will sell 60,000 sets per year for seven years. The marketing study also determined that the company will lose sales of 12,000 sets of its high-priced clubs. The high-priced clubs sell at $1,100 and have variable costs of $620. The company will also increase sales of its cheap clubs by 15,000 sets. The cheap clubs sell for $400 and have variable costs of $210 per set. The fixed costs each year will be $9,300,000. The company has also spent $1,000,000 on research and development for the new clubs. The plant and equipment required will cost $29,400,000 and will be depreciated on a straight-line basis. The new clubs will also require an increase in net working capital of $1,400,000 that will be returned at the end of the project. The tax rate is 40 percent, and the cost of capital is 14 percent.     
Suppose you feel that the values are accurate to within only ±10 percent.

1)What are the best-case and worst-case NPVs? (Hint: The price and variable costs for the two existing sets of clubs are known with certainty; only the sales gained or lost are uncertain.) (Negative amounts should be indicated by a minus sign.

2) Create an active spreadsheet that calculates the NPV in each scenario by generalizing the uncertainty to unit price, variable costs and fixed costs.

3) how the NPV changes when the discount rate (cost of capital) changes to 16%

4) Calculate the expected NPV and the standard deviation of the NPV

5) Calculate the coefficient of variation

6) Calculate the accounting breakeven quantity in every scenario.

7) Calculate the financial breakeven quantity in every scenario.

8) Calculate the cash breakeven quantity in every scenario.

9) explain whether this project should be undertaken.

Answer 1

Best Case and Worst Case NPV

Best Case

Year	1	2	3	4	5	6	7
Cheap Club line - Best Case
Increased sales quantity	16,500	16,500	16,500	16,500	16,500	16,500	16,500
Sales (Y)	6,600,000	6,600,000	6,600,000	6,600,000	6,600,000	6,600,000	6,600,000
Less Variable Cost	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000
Contribution (A)	3,450,000	3,450,000	3,450,000	3,450,000	3,450,000	3,450,000	3,450,000
High Club line - Best Case
Decreased sales quantity	10,800	10,800	10,800	10,800	10,800	10,800	10,800
Loss of sales (B)	(5,184,000)	(5,184,000)	(5,184,000)	(5,184,000)	(5,184,000)	(5,184,000)	(5,184,000)
New Club Line
Sales (Z)	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000
Less Variable Cost	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000
Contribution (C )	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000
Total Contribution (A+B+C)	24,966,000	24,966,000	24,966,000	24,966,000	24,966,000	24,966,000	24,966,000
Less Fixed Cost	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000
Less Depreciation	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000
EBIT	11,466,000	11,466,000	11,466,000	11,466,000	11,466,000	11,466,000	11,466,000
Less: Tax	4,586,400	4,586,400	4,586,400	4,586,400	4,586,400	4,586,400	4,586,400
PAT	6,879,600	6,879,600	6,879,600	6,879,600	6,879,600	6,879,600	6,879,600
Calculation of Cashflows
PAT	6,879,600	6,879,600	6,879,600	6,879,600	6,879,600	6,879,600	6,879,600
Add Depreciation	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000
Less: working capital	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000
Cashflows	9,679,600	9,679,600	9,679,600	9,679,600	9,679,600	9,679,600	9,679,600
Discount Factor	0.8772	0.7695	0.6750	0.5921	0.5194	0.4556	0.3996
Present Value	8,490,877	7,448,138	6,533,454	5,731,100	5,027,281	4,409,896	3,868,329
Present Value	41,509,076
Add: Net working Capital received back	559,492
Less: Capital expenditure	29,400,000
Less: R&D Cost	1,000,000
Less: Marketing Cost	150,000
Net Present value	11,518,568
Contribution margin [(A+B+C) / (Y+Z)]	42%
Breakeven point (Fixed Cost/Cont. margin)	9,300,000/42% =22,015,141
Weighted Avg Contribution per unit	Total contribution / Total units = 285
Breakeven quantity	76,982

Worst Case

	1	2	3	4	5	6	7
Cheap Club line - Worst Case
Increased sales quantity	13,500	13,500	13,500	13,500	13,500	13,500	13,500
Sales	5,400,000	5,400,000	5,400,000	5,400,000	5,400,000	5,400,000	5,400,000
Less Variable Cost	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000
Contribution (A)	2,250,000	2,250,000	2,250,000	2,250,000	2,250,000	2,250,000	2,250,000
High Club line - Worst Case
Decreased sales quantity	13,200	13,200	13,200	13,200	13,200	13,200	13,200
Loss of sales (B)	(6,336,000)	(6,336,000)	(6,336,000)	(6,336,000)	(6,336,000)	(6,336,000)	(6,336,000)
New Club Line
Sales	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000
Less Variable Cost	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000
Contribution (C )	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000
Total Contribution (A+B+C)	22,614,000	22,614,000	22,614,000	22,614,000	22,614,000	22,614,000	22,614,000
Less Fixed Cost	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000
Less Depreciation	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000
EBIT	9,114,000	9,114,000	9,114,000	9,114,000	9,114,000	9,114,000	9,114,000
Less: Tax	3,645,600	3,645,600	3,645,600	3,645,600	3,645,600	3,645,600	3,645,600
PAT	5,468,400	5,468,400	5,468,400	5,468,400	5,468,400	5,468,400	5,468,400
Calculation of Cashflows
PAT	5,468,400	5,468,400	5,468,400	5,468,400	5,468,400	5,468,400	5,468,400
Add Depreciation	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000
Less: working capital	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000
Cashflows	8,268,400	8,268,400	8,268,400	8,268,400	8,268,400	8,268,400	8,268,400
Discount Factor	0.8772	0.7695	0.6750	0.5921	0.5194	0.4556	0.3996
Present Value	7,252,982	6,362,265	5,580,934	4,895,557	4,294,348	3,766,972	3,304,361
Present Value	35,457,420
Add: Net working Capital received back	559,492
Less: Capital expenditure	29,400,000
Less: R&D Cost	1,000,000
Less: Marketing Cost	150,000
Net Present value	5,466,912
Contribution margin [(A+B+C) / (Y+Z)]	39%
Breakeven point (Fixed Cost/Cont. margin)	9,300,000/39% =23,811,355
Weighted Avg Contribution per unit	Total contribution / Total units = 260
Breakeven quantity	91,290

Change in NPV when Cost of Capial is 16%

Best Case

Cashflows	9,679,600	9,679,600	9,679,600	9,679,600	9,679,600	9,679,600	9,679,600
Discount Factor	0.8621	0.7432	0.6407	0.5523	0.4761	0.4104	0.3538
Present Value	8,344,483	7,193,520	6,201,310	5,345,957	4,608,584	3,972,917	3,424,928
Present Value	39,091,698
Add: Net working Capital received back	495,361
Less: Capital expenditure	29,400,000
Less: R&D Cost	1,000,000
Less: Marketing Cost	150,000
Net Present value	9,037,059

Worst Case

Cashflows	8,268,400	8,268,400	8,268,400	8,268,400	8,268,400	8,268,400	8,268,400
Discount Factor	0.8621	0.7432	0.6407	0.5523	0.4761	0.4104	0.3538
Present Value	7,127,931	6,144,768	5,297,214	4,566,564	3,936,693	3,393,701	2,925,604
Present Value	33,392,474
Add: Net working Capital received back	495,361
Less: Capital expenditure	29,400,000
Less: R&D Cost	1,000,000
Less: Marketing Cost	150,000
Net Present value	3,337,836

Expected NPV

Year	1	2	3	4	5	6	7
Cheap Club line - Best Case
Increased sales quantity	15,000	15,000	15,000	15,000	15,000	15,000	15,000
Sales	6,000,000	6,000,000	6,000,000	6,000,000	6,000,000	6,000,000	6,000,000
Less Variable Cost	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000	3,150,000
Contribution (A)	2,850,000	2,850,000	2,850,000	2,850,000	2,850,000	2,850,000	2,850,000
High Club line - Best Case
Decreased sales quantity	12,000	12,000	12,000	12,000	12,000	12,000	12,000
Loss of sales (B)	(5,760,000)	(5,760,000)	(5,760,000)	(5,760,000)	(5,760,000)	(5,760,000)	(5,760,000)
New Club Line
Sales	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000	52,500,000
Less Variable Cost	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000	25,800,000
Contribution (C )	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000	26,700,000
Total Contribution (A+B+C)	23,790,000	23,790,000	23,790,000	23,790,000	23,790,000	23,790,000	23,790,000
Less Fixed Cost	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000	9,300,000
Less Depreciation	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000
EBIT	10,290,000	10,290,000	10,290,000	10,290,000	10,290,000	10,290,000	10,290,000
Less: Tax	4,116,000	4,116,000	4,116,000	4,116,000	4,116,000	4,116,000	4,116,000
PAT	6,174,000	6,174,000	6,174,000	6,174,000	6,174,000	6,174,000	6,174,000
Calculation of Cashflows
PAT	6,174,000	6,174,000	6,174,000	6,174,000	6,174,000	6,174,000	6,174,000
Add Depreciation	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000	4,200,000
Less: working capital	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000	1,400,000
Cashflows	8,974,000	8,974,000	8,974,000	8,974,000	8,974,000	8,974,000	8,974,000
Discount Factor	0.8772	0.7695	0.6750	0.5921	0.5194	0.4556	0.3996
Present Value	7,871,930	6,905,202	6,057,194	5,313,328	4,660,814	4,088,434	3,586,345
Present Value	38,483,248
Add: Net working Capital received back	559,492
Less: Capital expenditure	29,400,000
Less: R&D Cost	1,000,000
Less: Marketing Cost	150,000
Net Present value	8,492,740
Contribution margin [(A+B+C) / (Y+Z)]	41%
Breakeven point (Fixed Cost/Cont. margin)	9,300,000/41% =22,868,852
Weighted Avg Contribution per unit	Total contribution / Total units = 273
Breakeven quantity	83,631

Standard Deviation of NPV

= square root of the variance of X; i.e., it is the square root of the average value of (X − μ)2; μ=mean

=Square root of [{(11518568-8492740)² + (5466912-8492740)²} / 2]

=$3,025,828

Coefficient of variation

=SD/Mean *100

=3,025,828 / 8492740*100 = 35.63%

The project shoud be undertaken since it creates value for shareholders and NPV is positive.