For our final assignment, please apply MS-Excel spreadsheets to solve the (A) WA
ID: 2617199 • Letter: F
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.
Explanation / Answer
Calculation of WACC
Cost of debt semiannual
Using rate function in MS excel
rate(nper,pmt,pv,fv,type)
4%
4.05%
Annual rate
4.05*2
8.1
after tax cost of debt = cost of debt*(1-tax rate)
8.1*(1-.34)
5.35
cost of common stock
risk free rate+(market risk premium)*beta
5.1+(8)*1.21
14.78
cost of preferred stock
preference dividend/market price
6.35/76
8.36%
WACC
source
value
weight
cost
weight*cost
debt
7725000
0.324976
5.35
1.737321
common stock
15210000
0.639855
14.78
9.457061
preferred stock
836000
0.035169
8.36
0.294012
23771000
WACC
sum of present value of cash flow
11.49
cost of golf course
-138000
working capital
-3000
cash outflow
-141000
annual benefits
annual income
72000
add saving from other facilities
14000
total income from golf course
86000
less variable cost
24000
less fixed cost
11600
less depreciation
138000/5
27600
operating profit
22800
less tax -34%
7752
after tax savings
15048
add depreciation
27600
net operating annual savings
42648
Year
0
1
2
3
4
5
cash outflow
-141000
annual savings
42648
42648
42648
42648
42648
working capital
3000
net operating annual savings
-141000
42648
42648
42648
42648
45648
present value of net operating savings
-141000
38252.76
34310.48
30774.49
27602.92
26499.77
NPV =sum of present value of cash flow
16440.43
IRR=using irr function in MS excel
16.02%
Calculation of WACC
Cost of debt semiannual
Using rate function in MS excel
rate(nper,pmt,pv,fv,type)
4%
4.05%
Annual rate
4.05*2
8.1
after tax cost of debt = cost of debt*(1-tax rate)
8.1*(1-.34)
5.35
cost of common stock
risk free rate+(market risk premium)*beta
5.1+(8)*1.21
14.78
cost of preferred stock
preference dividend/market price
6.35/76
8.36%
WACC
source
value
weight
cost
weight*cost
debt
7725000
0.324976
5.35
1.737321
common stock
15210000
0.639855
14.78
9.457061
preferred stock
836000
0.035169
8.36
0.294012
23771000
WACC
sum of present value of cash flow
11.49
cost of golf course
-138000
working capital
-3000
cash outflow
-141000
annual benefits
annual income
72000
add saving from other facilities
14000
total income from golf course
86000
less variable cost
24000
less fixed cost
11600
less depreciation
138000/5
27600
operating profit
22800
less tax -34%
7752
after tax savings
15048
add depreciation
27600
net operating annual savings
42648
Year
0
1
2
3
4
5
cash outflow
-141000
annual savings
42648
42648
42648
42648
42648
working capital
3000
net operating annual savings
-141000
42648
42648
42648
42648
45648
present value of net operating savings
-141000
38252.76
34310.48
30774.49
27602.92
26499.77
NPV =sum of present value of cash flow
16440.43
IRR=using irr function in MS excel
16.02%
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.