Question

MGM Co. has decided to sell a new line of golf clubs. The clubs will sell for $850 per set and have a variable cost of $400 per set. The company has spent $300,000 for a marketing study that determined the company will sell 68,500 sets per year for seven years. The marketing study also determined that the company will lose sales of $12,400 sets of its high-priced clubs. The high-priced clubs sell at $1,200 and have variable costs of $660. The company will also increase sales of its cheap clubs by 14,400 sets. The cheap clubs sell for $420 and have variable costs of $210 per set. The fixed costs each year will be $10,400,000. The company has also spent $2,500,000 on research and development for the new clubs. The plant and equipment required will cost $38,500,000 and will be depreciated on a straight-line basis. The new clubs will also require an increase in net working capital of $2,900,000 that will be returned at the end of the project. The tax rate is 21 percent, and the cost of capital is 12 percent.

Suppose you feel that the values are accurate to within only +/-10 percent. 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.)

Answer 1

Inputs for scenario analysis:

%age change		10% increase in sales & price 10% decrease in variable costs	10% decrease in sales & price 10% increase in variable costs
	Base case	Best case	Worst case
Unit sales (new)	68,500	75,350	61,650
Price (new)	850	935	765
VC (new)	400	360	440
Fixed costs	10,400,000	9,360,000	11,440,000
Sales lost (expensive)	12,400	11,160	13,640
Sales gained (cheap)	14,400	15,840	12,960
Tax	21%	21%	21%
Initial investment	-38,500,000
Life of project	7

Base-case scenario:

Formula		New golf clubs	High-priced clubs	Cheap clubs
N	Number	68,500	-12,400	14,400
sp	Selling price/unit	850	1,200	420
vc	Variable cost/unit	-400	-660	-210
S = N*sp	Total Sales	58,225,000	-14,880,000	6,048,000
VC = N*vc	Total VC	-27,400,000	8,184,000	-3,024,000
FC	Fixed cost	-10,400,000
S-FC-VC	EBITDA	16,753,000
D	Depreciation	-5,500,000
EBITDA-D	EBIT	11,253,000
T = 21%*EBIT	Tax	-2,363,130
NI = EBIT-T	Net income	8,889,870
	Add: depreciation	5,500,000
NI + D	OCF	14,389,870

Year 0 expenditure = initial investment + NWC = 38,500,000 + 2,900,000 = 41,400,000

PMT (or OCF) = 14,389,870; N = 7; rate = 12%, CPT PV1. PV1 = 65,671,863.31

Year 7 Net Working Capital (NWC) return) = 2,900,000: PV = 2,900,000/(1+12%)^7 = 1,311,812.72

NPV = -41,400,000 + 65,671,863.31 + 1,311,812.72 = 25,583,676.03

Best-case scenario NPV:

Formula		New golf clubs	High-priced clubs	Cheap clubs
N	Number	75,350	-11,160	15,840
sp	Selling price/unit	935	1,200	420
vc	Variable cost/unit	-360	-660	-210
S = N*sp	Total Sales	70,452,250	-13,392,000	6,652,800
VC = N*vc	Total VC	-27,126,000	7,365,600	-3,326,400
FC	Fixed cost	-9,360,000
S-FC-VC	EBITDA	31,266,250
D	Depreciation	-5,500,000.00
EBITDA-D	EBIT	25,766,250
T = 21%*EBIT	Tax	-54,10,913
NI = EBIT-T	Net income	20,355,338
	Add: depreciation	55,00,000
NI + D	OCF	25,855,337.50

Year 0 expenditure = initial investment + NWC = 38,500,000 + 2,900,000 = 41,400,000

PMT (or OCF) = 25,855,337.50; N = 7; rate = 12%, CPT PV1. PV1 = 117,997,465.58

Year 7 Net Working Capital (NWC) return) = 2,900,000: PV = 2,900,000/(1+12%)^7 = 1,311,812.72

NPV = -41,400,000 + 117,997,465.58 + 1,311,812.72 = 77,909,278.30

Worst-case scenario:

Formula		New golf clubs	High-priced clubs	Cheap clubs
N	Number	61,650	-13,640	12,960
sp	Selling price/unit	765	1,200	420
vc	Variable cost/unit	-440	-660	-210
S = N*sp	Total Sales	47,162,250	-16,368,000	5,443,200
VC = N*vc	Total VC	-27,126,000	9,002,400	-2,721,600
FC	Fixed cost	-11,440,000
S-FC-VC	EBITDA	3,952,250
D	Depreciation	-5,500,000.00
EBITDA-D	EBIT	-1,547,750.00
T = 21%*EBIT	Tax	325,027.50
NI = EBIT-T	Net income	-1,222,722.50
	Add: depreciation	5,500,000.00
NI + D	OCF	4,277,277.50

Year 0 expenditure = initial investment + NWC = 38,500,000 + 2,900,000 = 41,400,000

PMT (or OCF) = 4,277,277.50; N = 7; rate = 12%, CPT PV1. PV1 = 19,520,453.16

Year 7 Net Working Capital (NWC) return) = 2,900,000: PV = 2,900,000/(1+12%)^7 = 1,311,812.72

NPV = -41,400,000 + 19,520,453.16 + 1,311,812.72 = -20,567,734.12

Best-case NPV = 77,909,278.30

Worst-case NPV = -20,567,734.12