Question

For our final assignment, please apply MS-Excel spreadsheets to solve the (A) WACC, (B) Internal Rate of Return, and (C) Net Present Value of a miniature golf course project under consideration.

You may discuss key concepts with your team member, but you should work on this assignment on an individual basis

 

For submission, please prepare two files to upload.

The first file is an MS-Excel file with functions applied to solve the problems, as we introduced in class. The second file is an MS-Word (or text/PDF) file to provide your answers and discussion of this question.

 ~~~ ~~~

 

Outdoor Sports is considering adding a miniature golf course to its facility. Given the following information for Outdoor Sports, please find the (A) WACC, (B) Internal Rate of Return, and (C) Net Present Value of this project. Assume the company's tax rate is 34 percent.

 

[Project]

 

The miniature golf course would cost $138,000, would be depreciated on a straight-line basis over its five-year life, and would have a zero salvage value. The estimated income from the golfing fees would be $72,000 a year with $24,000 of that amount being variable cost. The fixed cost would be $11,600. In addition, the firm anticipates an additional $14,000 in revenue from its existing facilities if the golf course is added. The project will require $3,000 of net working capital, which is recoverable at the end of the project.

 

[Financial data]

 

Debt: 7,500, 8.4 percent coupon bonds outstanding. $1,000 par value, 22 years to maturity, selling for 103 percent of par, the bonds make semiannual payments.

 

Common stock: 195,000 shares outstanding, selling for $78 per share, beta is 1.21.

 

Preferred stock: 11,000 shares of 6.35 percent preferred stock outstanding, currently selling for $76 per share.

 

Market: 8 percent market risk premium and 5.1 percent risk-free rate.

Answer 1

Initial cash outflow			cost of debt	Using rate function in MS excel	rate(nper,pmt,pv,fv,type)	4.05%
cost of plant	-138000		after tax cost of debt (annual)	4.05*(1-.34)*2	5.346
investment in working capital	-3000
Initial cash outflow	-141000		cost of preferred stock	preferred dividend/market price	6.35/76	8.36%
Annual cash flow			cost of equity	risk free rate+(market risk premium)*beta	5.1+(8)*1.21	14.78
sales		72000
additional revenue		14000	WACC
v.cost		24000	source	value = units issued*market price	weight	cost	weight*cost
less fixed cost		11600	debt	7725000	0.324976	5.35	1.738621
less depreciation	138000/5	27600	preferred stock	836000	0.035169	8.36	0.294012
operating profit		22800	common stock	15210000	0.639855	14.78	9.457061
less taxes -40%		7752	total value	23771000	WACC	sum of weight*cost	11.49
after tax profit		15048
add depreciation		27600
operating income		42648
Year	net operating cash flow	present value of cash flow = operating cash flow/(1+r)^n r= 13.58%
0	-141000	-141000
1	42648	38252.758
2	42648	34310.484
3	42648	30774.494
4	42648	27602.919
5	45648	26499.774
net present value =sum of present value of operating cash flow		16440.429
IRR =using irr function in ms excel		16.02%