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

Step 1: Initial Investment And Calculate Annual Operating Cash Flow

The value of initial investment and annual operating cash flow is arrived as below:

Initial Investment = -Cost - Initial Working Capital = -138,000 - 3,000 = -$141,000

The annual operating cash flows is arrived as below:

Annual Operating Cash Flow = (Sales - Variable Cost - Fixed Cost + Increase in Revenues)*(1-Tax Rate) + Depreciation*Tax Rate = (72,000 - 24,000 - 11,600 + 14,000)*(1-34%) + 138,000/5*34% = $42,648

_____

Step 2: Calculate Weights

The weight of each source of finance is calculated as below:

Market Value of Debt = Number of Bonds*Par Value*Current Selling Percentage = 7,500*1,000*103% = $7,725,000

Market Value of Preferred Stock = Number of Preferred Stock*Current Selling Price Per Share = 11,000*76 = $836,000

Market Value of Common Stock = Number of Common Stock*Current Selling Price Per Share = 195,000*78 = 15,210,000

Total Market Value = Market Value of Debt + Market Value of Preferred Stock + Market Value of Common Stock = 7,725,000 + 836,000 + 15,210,000 = $23,771,000

Now, we can determine the weights as below:

Weight of Debt = Market Value of Debt/Total Weight = 7,725,000/23,771,000

Weight of Preferred Stock = Market Value of Preferred Stock/Total Weight = 836,000/23,771,000

Weight of Common Stock = Market Value of Common Stock/Total Weight = 15,210,000/23,771,000

_____

Step 3: Calculate Cost of Debt, Preferred Stock and Equity

The cost of debt can be calculated with Rate function/formula of EXCEL/Financial Calculator. The function/formula for Rate is Rate(Nper,PMT,PV,FV) where Nper = Period, PMT = Payment (here, Coupon Payment), PV = Present Value (here, Current Selling Price) and FV = Future Value (here, Face Value)

Here, Nper = 22*2 = 44, PMT = 1,000*8.40%*1/2 = $42, PV = 1,000*103% = $1,030 and FV = $1,000

Using these values in the above function/formula for Rate, we get,

Yield to Maturity = Rate(44,42,1030,1000)*2 = 8.11%

____

The cost of preferred stock is arrived as below:

Cost of Preferred Stock = Annual Dividends/Current Selling Price*100 = (100*6.35%)/76*100 = 8.36%

____

The cost of equity is determined as follows:

Cost of Equity = Risk Free Rate + Beta*(Market Risk Premium) = 5.1% + 1.21*8% = 14.78%

_____

Step 4: Calculate WACC

The WACC can be calculated with the use of following formula:

WACC = Weight of Debt*After-Tax Cost of Debt + Weight of Preferred Stock*Cost of Preferred Stock + Weight of Common Stock*Cost of Common Stock

Substituting values in the above formula, we get,

WACC = 7,725,000/23,771,000*8.11%*(1-34%) + 836,000/23,771,000*8.36% + 15,210,000/23,771,000*14.78% = 11.49%

_____

Step 5: Calculate IRR

IRR is the minimum rate of return acceptable from a project. It can be calculated with the use of IRR function of EXCEL/Financial Calculator. The basic formula for calculating IRR is provided as below:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+IRR)^1 + Cash Flow Year 2/(1+IRR)^2 + Cash Flow Year 3/(1+IRR)^3 + Cash Flow Year 4/(1+IRR)^4 + Cash Flow Year 5/(1+IRR)^5

IRR is calculated with the use of EXCEL as below:

where IRR = IRR(B2:B7) = 16.02%

_____

Step 6: Calculate NPV

The NPV can be calculated with the use of formula given below:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+WACC)^1 + Cash Flow Year 2/(1+WACC)^2 + Cash Flow Year 3/(1+WACC)^3 + Cash Flow Year 4/(1+WACC)^4 + Cash Flow Year 5/(1+WACC)^5

Substituting values in the above formula, we get,

NPV = -141,000 + 42,648/(1+11.49%)^1 + 42,648/(1+11.49%)^2 + 42,648/(1+11.49%)^3 + 42,648/(1+11.49%)^4 + (42,648+3,000)/(1+11.49%)^5 = $16,442.72