Question

McGilla Golf is evaluating a new line of golf clubs. The clubs will sell for $1,080 per set and have a variable cost of $490 per set. The company has spent $177,500 for a marketing study that determined the company will sell 54,500 sets per year for seven years. The marketing study also determined that the company will lose sales of 10,300 sets of its high-priced clubs. The high-priced clubs sell at $1,580 and have variable costs of $710. The company also will increase sales of its cheap clubs by 12,900 sets. The cheap clubs sell for $490 and have variable costs of $220 per set. The fixed costs each year will be $10,050,000. The company has also spent $1,375,000 on research and development for the new clubs. The plant and equipment required will cost $33,950,000 and will be depreciated on a straight-line basis to a zero salvage value. The new clubs also will require an increase in net working capital of $2,770,000 that will be returned at the end of the project. The tax rate is 25 percent and the cost of capital is 15 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.) (A negative amount should be indicated by a minus sign. Do not round intermediate calculations and round your answers to 2 decimal places, e.g., 32.16.)

Answer 1

Please find below table useful to compute desired results: -

C D G H Base Sales =B5 Price =B2 Varibale cost =B3 Fixed costs =B12 Sales Lost(Expensive) =B6 Sales Gained(Cheap) =B9 А B 1 Particulars Given 2 SP 1080 3 Variable Cost 490 4 Marketing cost 177500 5 Estimated sale 54500 6 High-priced clubs 10300 7 SP of High-price clubs 1580 8 Variable cost 710 9 Increase in sets 12900 10 SP of cheap clubs 490 11 Variable Cost 220 12 Fixed Costs 10050000 13 R&D costs 1375000 14 Plant & Equipment 33950000 15 Net Working Capital 2770000 16 Tax Rate 1=25/100 17 Cost of Capital =15/100 Selling price =G3 Sales New Clubs Expensive Clubs Cheap Clubs Worst Case Units =H2 =-H6 =H7 =B7 =B10 =G7 Best Case Worst Case =F2+F2*10% =F2-F2*10% =F3+F3*10% =F3-F3*10% =F4-F4*10% =F4+F4*10% =F5-F5*10% =F5+F5*10% =F6-F6*10% =F6+F6*10% =F7+F7*10% =F7-F7*10% Calculation of CFAT Best Case Units Total Selling price =G2 =F11*G11 =H3 =-G6 =F12*G12 =B7 =F13*G13 =B10 =SUM(H11:H13) Units Total Variable Costs =G2 =F16*G16 =H4 =-G6 =F17*G17 =B8 =F18*G18 =SUM(H16:H18) =G5 =B14/7 =H14-H19-H20-H21 =H22*B16 =H22-H23 =H21 =H24+H25 Less: Variable Costs New Clubs Expensive Clubs Cheap Clubs Variable Costs 1=G4 =B8 Units =H2 =-H6 =B11 =HT Total =111*J11 =112*J12 =113*J13 =SUM(K11:K13) Total =116*J16 =117*J17 =118*J18 =SUM(K16:K18) =H5 =B14/7 =K14-K19-K20-K21 =K22*B16 =K22-K23 =K21 =K24+K25 Less: Fixed Costs Less: Depreciation EBT Less: Tax Net Income Add: Depreciation CFAT

B C D Year Calculation of NPV PV Factor CFAT Best Case Worst Case =1/(1+B17) E31 =-B14+B15) =-(B14+B15) =1/(1+B17) E32 =H26 =K26 =1/(1+B17) E33 =H26 =K26 =1/(1+B17) E34 =H26 =K26 =1/(1+B17) E35 =H26 =K26 =1/(1+B17) E36 =H26 =K26 =1/(1+B17) E37 =H26 =K26 =1/(1+B17) E38 =H26+B15 =K26+B15 NPV PV of CFAT Best Case Worst Case =G31*F31 =H31*F31 =G32*F32 =H32*F32 =G33*F33 =H33*F33 =G34*F34 =H34*F34 =G35*F35 =H35*F35 =G36*F36 =H36*F36 =G37*F37 I=H37*F37 =G38*F38 =H38*F38 =SUM(131:138) =SUM(J31:J38) 39

End results would be as follows: -

A E Base Sales Price Varibale cost Fixed costs Sales Lost(Expensive) Sales Gained(Cheap) 54,500 1,080 490 10.050.000 10,300 12,900 в ср 1 Particulars Given 2 SP 1,080 3 Variable Cost 490 4 Marketing cost 177,500 5 Estimated sale 54,500 6 High-priced clubs 10,300 7 SP of High-price clubs 1,580 8 Variable cost 710 9 Increase in sets 12.900 10 SP of cheap clubs 490 11 Variable Cost 220 12 Fixed Costs 10.050,000 13 R&D costs 1.375.000 14 Plant & Equipment 33,950,000 15 Net Working Capital 2,770,000 Less: 16 Tax Rate 25% 17 Cost of Capital 15% Sales New Clubs Expensive Clubs Cheap Clubs Selling price 1.188 1.580 490 H Best Case Worst Case 59.950 49,050 1.188 972 441 539 9.045,000 11,055,000 9,270 11.330 14,190 11,610 Calculation of CFAT Best Case Units Total Selling price 59.950 71,220,600 972 (9.270) (14.646.600) 1.580 14.190 6.953.100 490 63,527,100 Units Total Variable Costs 59.950 26.437.950 (9.270) (6.581.700) 710 14.190 3,121,800 220 22,978,050 9,045,000 4,850,000 26,654,050 6,663,513 19,990,538 4,850,000 24,840,538 Variable Costs New Clubs Expensive Clubs Cheap Clubs Variable Costs 441 710 539 Worst Case Units Total 49,050 47,676,600 (11,330) (17.901.400) 11.610 5.688.900 35,464,100 Units Total 49,050 26.437.950 (11,330) (8.044.300) 11,610 2,554,200 20,947,850 11,055,000 4,850,000 (1,388,750) (347,188) (1,041,563) 4,850,000 3,808,438 Less: Less: Fixed Costs Depreciation EBT Less: Ta Add: Net Income Depreciation CFAT

B C D G H Year PV Factor 1.0000 0.8696 0.7561 0.6575 0.5718 0.4972 0.4323 0.3759 NPV Calculation of NPV CFAT PV of CFAT Best Case Worst Case | Best Case Worst Case (36.720,000) (36,720.000) (36,720,000.00) (36,720,000.00) 24.840.538 3.808.438 21.600.467.39 3.311.684.78 24.840.538 3.808.438 18.783,015.12 2.879.725.90 24.840.538 3.808.438 16,333,056.63 2,504.109.48 24.840.538 3.808.438 14.202.657.94 2.177.486.50 24.840,538 3.808.438 12.350.137.34 1.893.466.52 24,840,538 3.808.438 10.739.249.86 1.646.492.63 27,610,538 6,578,438 10.379.823.74 2.473.078.32 | | 67,668,408.01 (19,833,955.87)