For our final assignment, please apply MS-Excel spreadsheets to solve the (A) WA
ID: 2390844 • 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
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%
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%
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.