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 68500 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 will sell at $1200 and a variable cost of $660. The company will also increase sales of its cheap clubs by 14,400 sets. The cheap clubs sell for $420 and have a variable cost of $210 per set. The fixed cost each year will be $10400,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 it will be depreciated on a straight-line basis. The new clubs will also require an increase in net working capital of $2900,000 that it will be returned at the end of the project. The tax rate is 21 percent and the cost of capital is 12 percent.

Suppose that you feel that the values are accurate to within only ± 10 percent. What are the best-case and worst-case NPV”s?(hint: The price and variable cost for the two existing set of clubs are known with certainty; only the sales gained or lost are uncertain)

Answer 1

In the best case :

lost sales of high-priced clubs = 12,400 * 0.9 = 11,160 sets

increased sales of cheap clubs = 14,400 * 1.1 = 15,840 sets

contribution = units sold * (selling price - variable cost)

operating cash flow (OCF) of each year = income after tax + depreciation

NPV is calculated using NPV function in Excel

NPV is $25,583,676

B24 foc =NPV(12%,C22:122) +B22 G H I $38,500,000 $2,900,000 1 2 Initial Investment 3 cost of equipment 4 increase in working capital 5 6 OCF 7 Contribution of new clubs 8 - Lost contribution of high-priced clubs 9 + Increased contribution of cheap clubs 10 - Fixed Costs 11 - Depreciation 12 EBIT 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF 17 18 Terminal Cash Flow 19 NWC Return 20 21 NPV Analysis 22 Project Cash Flows $30,825,000 $6,696,000 $3,024,000 $10,400,000 $5,500,000 $11,253,000 $2,363,130 $8,889,870 $5,500,000 $14,389,870 $30,825,000 $30,825,000 $30,825,000 $30,825,000 $6,696,000 $6,696,000 $6,696,000 $6,696,000 $3,024,000 $3,024,000 $3,024,000 $3,024,000 $10,400,000 $10,400,000 $10,400,000 $10,400,000 $5,500,000 $5,500,000 $5,500,000 $5,500,000 $11,253,000 $11,253,000 $11,253,000 $11,253,000 $2,363,130 $2,363,130 $2,363,130 $2,363,130 $8,889,870 $8,889,870 $8,889,870 $8,889,870 $5,500,000 $5,500,000 $5,500,000 $5,500,000 $14,389,870 $14,389,870 $14,389,870 $14,389,870 $30,825,000 $30,825,000 $6,696,000 $6,696,000 $3,024,000 $3,024,000 $10,400,000 $10,400,000 $5,500,000 $5,500,000 $11,253,000 $11,253,000 $2,363,130 $2,363,130 $8,889,870 $8,889,870 $5,500,000 $5,500,000 $14,389,870 $14,389,870 $2,900,000 ($41,400,000) $14,389,870 $14,389,870 $14,389,870 $14,389,870 $14,389,870 $14,389,870 $17,289,870 23 24 NPV $25,583,676

А 38500000 2900000 1 2 Initial Investment 3 cost of equipment 4 increase in working capital 5 6 OCF 7 Contribution of new clubs 8 - Lost contribution of high-priced clubs 9 + Increased contribution of cheap clubs 10 - Fixed Costs 11 - Depreciation 12 EBIT 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF 17 18 Terminal Cash Flow 19 NWC Return =(850-400)*68500 =(850-400)*68500 =(850-400) *68500 =(850-400)*68500 =(850-400)*68500 =(850-400) *68500 =(850-400)*68500 =(1200-660) *12400 =(1200-660) *12400 =(1200-660) *12400 =(1200-660) *12400 =(1200-660) *12400 =(1200-660) *12400 =(1200-660) *12400 =(420-210) *14400 =(420-210) *14400 =(420-210) *14400 =(420-210) *14400 =(420-210)*14400 =(420-210)*14400 =(420-210) *14400 10400000 10400000 10400000 10400000 10400000 10400000 10400000 =$B$3/7 =$B$3/7 =$B$3/7 =$B$3/7 =$B$3/7 =$B$3/7 =$B$3/7 =C7-C8+C9-C10-C11 =D7-D8+D9-D10-D11 =E7-E8+E9-E10-E11 =F7-F8+F9-F10-F11 =G7-G8+G9-G10-G11 =H7-H8+H9-H10-H11 =17-18+19-110-111 =C12*21% =D12*21% =E12*21% =F12*21% =G12*21% =H12*21% =112*21% =C12-C13 =D12-D13 =E12-E13 =F12-F13 =G12-G13 =H12-H13 =112-113 =C11 =D11 =F11 =G11 =H11 =111 =C14+C15 =D14+D15 =E14+E15 =F14+F15 =G14+G15 =H14+H15 =114+115 =E11 =B4 20 --B3-B4 =C16 =D16 =E16 =F16 =G16 =H16 =116+119 21 NPV Analysis 22 Project Cash Flows 23 24 NPV =NPV(12%,C22:122)+B22

In the best case :

lost sales of high-priced clubs = 12,400 * 1.1 = 13,640 sets

increased sales of cheap clubs = 14,400 * 0.9 = 12,960 sets

contribution = units sold * (selling price - variable cost)

operating cash flow (OCF) of each year = income after tax + depreciation

NPV is calculated using NPV function in Excel

NPV is $22,079,259

foc =(1200-660) *13640 в A с р G H 2 Initial Investment 3 cost of equipment 4 increase in working capital $38,500,000 $2,900,000 5 6 OCF 7 Contribution of new clubs 8 - Lost contribution of high-priced clubs 9 + Increased contribution of cheap clubs 10 - Fixed Costs 11 - Depreciation 12 EBIT 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCE 17 18 Terminal Cash Flow 19 NWC Return 20 21 NPV Analysis 22 Project Cash Flows 23 24 NPV $30,825,000 $30,825,000 $7,365,600 $7,365,600 $2,721,600 $2,721,600 $10,400,000 $10,400,000 $5,500,000 $5,500,000 $10,281,000 $10,281,000 $2,159,010 $2,159,010 $8,121,990 $8,121,990 $5,500,000 $5,500,000 $13,621,990 $13,621,990 $30,825,000 $7,365,600 $2,721,600 $10,400,000 $5,500,000 $10,281,000 $2,159,010 $8,121,990 $5,500,000 $13,621,990 $30,825,000 $30,825,000 $30,825,000 $30,825,000 $7,365,600 $7,365,600 $7,365,600 $7,365,600 $2,721,600 $2,721,600 $2,721,600 $2,721,600 $10,400,000 $10,400,000 $10,400,000 $10,400,000 $5,500,000 $5,500,000 $5,500,000 $5,500,000 $10,281,000 $10,281,000 $10,281,000 $10,281,000 $2,159,010 $2,159,010 $2,159,010 $2,159,010 $8,121,990 $8,121,990 $8,121,990 $8,121,990 $5,500,000 $5,500,000 $5,500,000 $5,500,000 $13,621,990 $13,621,990 $13,621,990 $13,621,990 $2,900,000 ($41,400,000) $13,621,990 $13,621,990 $13,621,990 $13,621,990 $13,621,990 $13,621,990 $16,521,990 $22,079,259