Question

McGilla Golf has decided to sell a new line of golf club. The clubs will sell for $715 per set and cost of goods sold will be 60% of sales revenue. The company has spent $250,000 for a marketing study that determined the company will sell 65,000 sets per year for seven years. The company will also increase sales of its golf balls by 200,000 boxes of a dozen balls per year. The balls sell for $35/dozen and have a variable cost of $15 per dozen. The fixed costs each year will be $11,200,000. The company has also spent $1,000,000 on research and development for the new clubs. The plant and equipment required will cost $24,500,000 and will be depreciated on MACRS schedule for a seven-year useful life. The equipment will be sold for its book value in the final year of the project. The new clubs will also require an increase in net working capital of $1,500,000 that will be returned at the end of the project. The tax rate is 25 percent. Information for computing the cost of capital is given in the table below.  

Book Value of Debt	$1,500,000,000
Market Value of Debt	$2,255,000,000
Book Value of Equity	$3,500,000,000
Market Value of Equity	$3,500,000,000
Expected Dividend for next year	$1.85
Growth rate of dividends	4%
Current stock price	$23.50
Bond information	Coupon rate = 5%, maturity = 15 years, maturity value =$1,000 and the current price is $987.25. Assume interest is paid semiannually.

Calculate the Payback Period, NPV, and IRR. Please show all formulas!

Answer 1

Let's start with cost of capital

Cost of equity, re = D1 / P + g = 1.85 / 23.5 + 4% = 11.87%

Cost of debt can be calculated using RATE function in excel

rd = RATE(nper = 15*2, pmt = 5% x 1000 / 2, pv = -987.25, fv = 1000, 0) x 2 = 5.12%

Weight of equity, we = 3.5 / (3.5 + 2.255) = 60.82% and wd = 1 - 60.82% = 39.18%

=> WACC = wd x rd x (1 - tax) + we x re

= 39.18% x 5.12% x (1 - 25%) + 60.82% x 11.87% = 8.73%

McGilla	0	1	2	3	4	5	6	7
MACRS%		14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%
Investment	-24,500,000
NWC	-1,500,000							1,500,000
Salvage								1,092,700
Sales		130,475,000	130,475,000	130,475,000	130,475,000	130,475,000	130,475,000	130,475,000
VC		-63,885,000	-63,885,000	-63,885,000	-63,885,000	-63,885,000	-63,885,000	-63,885,000
FC		-11,200,000	-11,200,000	-11,200,000	-11,200,000	-11,200,000	-11,200,000	-11,200,000
Depreciation		-3,501,050	-6,000,050	-4,285,050	-3,060,050	-2,187,850	-2,185,400	-2,187,850
EBT		51,888,950	49,389,950	51,104,950	52,329,950	53,202,150	53,204,600	53,202,150
Tax (25%)		-12,972,238	-12,347,488	-12,776,238	-13,082,488	-13,300,538	-13,301,150	-13,300,538
Net Income		38,916,713	37,042,463	38,328,713	39,247,463	39,901,613	39,903,450	39,901,613
Cash Flows	-26,000,000	42,417,763	43,042,513	42,613,763	42,307,513	42,089,463	42,088,850	44,408,988
Payback	1.63
NPV	$190,760,337
IRR	163.57%

Sales = 65,000 x 715 + 200,000 x 35 x 12 = 130,475,000

VC = 65,000 x 715 x 60% + 200,000 x 15 x 12 = 63,885,000

Depreciation = Investment x MACRS%

Salvage = Investment - Accumulated Depreciation = 1,092,700

Cash Flows = Investment + NWC + Net Income + Depreciation + Salvage x (1 - tax)

Payback = 42,417,763 / 26,000,000 = 1.63

NPV and IRR can be calculated in excel with the same formula with WACC as the discount rate.