Question Details

(Solved) Working on a WACC project for a Financial Management class and


Working on a WACC project for a Financial Management class and need some help. Attached are the guidelines. The company I chose is Royal Dutch Shell plc


WACC Project

 

In this project, you will find and discern the appropriate data to determine a realistic

 

assessment of the weighted average cost of capital for a firm of your choosing.

 

You will need to search for data from several sources, use subjective judgment to

 

determine which data to use or discard, use subjective judgment to determine which

 

calculation gives a more acceptable estimate and make some simplifying assumptions.

 

The purpose of the projects is to show some of the sources of measurement errors in

 

financial analysis, to introduce the diverse sources of publicly available financial

 

information and to develop skill in analysis in situations where there are too much or too

 

little data.

 

Keep the following in mind when choosing a company:

 

Publicly traded

 

No utilities

 

No financial firms

 

No all equity firms

 

No firms with large amounts of convertible securities or warrants

 

Organization of the paper should be as follows:

 

I.

 


 

Title page

 


 

II.

 


 

Table of Contents

 


 

III.

 


 

Introduction/Background (2-3 pages)

 

This section should include background on the company that has been chosen.

 


 

IV.

 


 

Pages showing equations with data and brief description

 

There is no page length given for this as it can vary greatly. This section is to be

 

divided up based on the topics. In each section, you must show and explain the

 

equations that are used. In addition, you are to draw any conclusions on the

 

company you can from this data. Please note that detailed worksheets showing all

 

of the calculations for this section are to be included in an appendix.

 


 


 

Cost of Equity (Common Stock)

 

o

 

o

 

o

 

o

 


 

Beta from Regression and two Betas from analysts

 

Beta Chosen for CAPM and why

 

Capital Assets Pricing Model (include how determined R F and[ RM or (RM ? RF)]

 

Discounted Cash Flow (DCF) (only if dividends ? include how determined)

 


 

o

 


 


 


 


 


 


 


 


 


 


 


 


 

V.

 


 

Own-Bond-Yield-plus-Judgmental-Risk-Premium (include how determine risk

 

premium)

 


 

Cost of Preferred Stock

 

Cost of Debt (make sure to include table that lists all bond issues with

 

weighted average cost of debt)

 

Market Value of Debt (will have calculated above, but will need to add any

 

long term leases from balance sheet to get total market value of debt)

 

Market Value of Equity

 

Market Value of Preferred Stock

 

Value of Firm

 

Firm?s Tax Rate (explain how determined)

 

Weight for Equity

 

Weight for Preferred Stock

 

Weight for Debt

 

WACC (Weighted Average Cost of Capital)

 

Assumptions

 


 

Including but not limited to RF , RM, RPM (which =Rm - RF), growth rate of

 

dividends. This page should have a brief description of how you came up with the

 

estimates with spreadsheets, etc. to be put in the appendix.

 

VI.

 


 

Appendix

 

Appendix should include all relevant data including debt data from Morningstar,

 

calculations of weighted average cost of debt, stock returns, betas from analysts,

 

beta regression analysis, method/sourcing for RF and RM, growth rates for

 

dividends, different methods to determine tax rates, etc. Detailed descriptions,

 

tables of data and excel sheets etc will be in the appendix.

 


 

VII.

 


 

References

 


 

HELPFUL EQUATIONS

 

WACC = [(wE) x RE] + [(wPF) x RPF] + [(wD) x RD x (1- TC)]

 

Where:

 

Weights

 

(wE) = % of common equity in capital structure

 

(wPF) = % of preferred stock in capital structure

 

(wD) = % of debt in capital structure

 

Component costs

 

RE = firm?s cost of equity

 

RPF = firm?s cost of preferred stock

 

RD = firm?s cost of debt

 


 

TC = firm?s corporate tax rate

 

HELPFUL INFORMATION

 

Useful information will be found in the video, the sample Excel sheets, or your text?s tool

 

kit Excel sheets. For instance:

 

Bonds

 

You enter the ticker symbol about half way down on the Morningstar bond page

 

which you accessed by clicking on bonds on the first page.

 

You will then see all the bonds for the company, in this case, Southwest Airlines.

 

Copy and paste the information into an excel spreadsheet. You may adapt the

 

bond spreadsheet given to you, or show your calculations in a new spreadsheet or

 

show in another document

 

Calculating the Weighted Average Cost of Debt

 

1. Find the market value of each bond issue. To do this find the number of bonds and

 

then multiply by the price of the bond (remember that bond prices are quoted in

 

100s, but are really 1000s).

 

2. If there is no bond price, assume $1000 par is the price.

 

3. For these bonds, the YTM =coupon rate

 

4. Calculate the total market value of bonds

 

5. Calculate the weights for each bond issue as market value of bond issue/ Total

 

market value of debt. Make sure your weights sum to one.

 

6. For each bond issue, multiply the weight by that issue?s YTM.

 

7. Sum the weighted YTMs, and you now have the weighted average RD,

 

Calculating the Weights for the WACC

 

1. MV of bonds has already been calculated. To that you will add in the value of

 

leases from the Balance Sheet

 

2. For Preferred Stock, find the number of preferred shares in the annual report and

 

the prices in the WSJ Market Center

 

3. For common equity, find the price and number of shares in Yahoo Finance.

 

4. Can use example found in bottom half of bond worksheet or develop your own.

 

Calculating the Required Return on Preferred Stock

 

1. To Calculate RPF, we use the constant dividend model, ie. the perpetuity

 

model.

 

2. RPF = Dividend/P0

 

3. Check in your company?s annual report to see if they have preferred stock

 

and what the dividend is. Make sure you use the yearly dividend since we

 

are calculating annual returns.

 


 


 


 

Prices can be found in the WSJ Market Center:

 

http://online.wsj.com/mdc/public/page/2_3024-Preferreds.html

 


 

Calculating the Required Return on Common Stock

 

CAPM

 

Determining Beta

 

1. Find the beta from Yahoo Finance and Value line for your firm.

 

2. Perform a regression using stock returns versus the appropriate market return.

 

For most firms, the S&P 500 will be sufficient; if you chose a relatively small

 

firm, you might want to use the NASDAQ returns.

 

EXAMPLE:

 

The example that I show you uses IBM and 60 months of historical monthly price

 

data. I also used the monthly price data for the S&P 500.

 

This information is downloaded first and then only the needed columns are cut

 

and pasted into the spreadsheet and the monthly returns will be automatically

 

calculated.

 

Data on stock prices and dividends can be downloaded from the web and used to

 

make betas for real companies. I demonstrate the process for IBM in this section.

 

I downloaded stock prices and dividends from http://finance.yahoo.com for IBM

 

using its ticker symbol IBM. I also downloaded data for the S&P 500 Index,

 

whose symbol is ^GSPC to represent the market. Here are the steps I followed:

 

Steps:

 

1.

 

2.

 

3.

 

4.

 

5.

 


 

Access the Internet, then go to http://finance.yahoo.com/

 

Enter IBM in the symbol slot and then click Get quotes.

 

Click on "Historical prices" to get a history of IBM prices.

 

Enter a Start Date 5 years ago and a current ending date .

 

Click the "monthly" button. then "Get prices" to get 5 years of monthly

 

prices for IBM. The closing prices are adjusted for dividends and splits.

 

6. Note that Yahoo's data is downloaded as a CSV file. Save the file as an

 

excel spreadsheet. Save as IBM

 

7. Repeat the process to get the S&P index, symbol ^GSPC . Save this file

 

as SP500 and in excel format.

 

8. Open the IBM file and delete the columns except for the date and closing

 

prices. Then open the SP500 file, copy the closing price data, and paste it

 

into Column B on the IBM file.

 

9. Now move the IBM data over to column D and then calculate the monthly

 

returns on the market and on IBM

 

10. Now you can run the regression of IBM's returns on the market to find its

 

beta

 


 

Regression analysis is performed by following this command path: Tools => Data

 

Analysis => Regression.

 

This will yield the Regression input box. If Data Analysis is not an option in your

 

Tools menu, you will have to load that program. Loading the Analysis ToolPak is

 

different in each version of Office so you will have to determine how to find it.

 

Once it is loaded, you will now be able to access Data Analysis. From this point,

 

you must designate the Y input range (stock returns) and the X input range

 

(market returns). You can have the summary output placed in a new worksheet,

 

or you can have it shown directly in the worksheet, as I did, but be careful that

 

you place it in a blank area of your spreadsheet.

 

You may decide that a years worth of weekly data would be better, perhaps if

 

there were something unusual two years ago. You could also use daily data,

 

generally 200 days worth. In these cases you will have to adjust the spreadsheet

 

for fewer/more data points.

 

3. You must now decide if you want to average your betas or drop one of them. In

 

our example , the calculated beta was .643 and we had two other estimates of .66

 

and .85. It would be your choice to either drop the .85 and average the other two,

 

or average all three.

 

You must justify your choices and provide citations in the body of your

 

report and references at the end.

 

You must now decide what value you will use for RF and either RM or RPM where

 

o CAPM: RE = RRF + (RM ? RRF)b

 

= RRF + (RPM)b.

 

Again, You must justify your choices for RF and either RM or RPM and

 

provide citations in the body of your report and references at the end.

 

The Own-Bond-Yield-Plus-Judgmental-Risk-Premium Method

 


 


 


 


 


 


 


 

RE = RD + Judgmental risk premium

 

This judgmental-risk premium the CAPM equity risk premium, RPM

 

Produces ballpark estimate of RE

 

Useful check, particularly if Dividend Growth and CAPM are significantly

 

different

 

Again, you must justify your answer

 


 

Dividend Growth Model

 


 


 

RE = {[D0* (1+g)] /P0 } + g

 


 


 


 

The challenge here is to estimate the growth rate. There are several suggestions in

 

your text as well as in earlier videos.

 


 

Corporate Tax Rate

 

You may be able to find the corporate tax rate directly in the annual report

 

Or you can choose to use the formula

 

o Taxes = Tax Rate * Earnings Before Taxes

 

Since both Taxes and Earnings before Taxes are in the Balance Sheet, you can

 

then calculate the tax rate

 

Reference

 

McDaniel, William (1997). The Cost of Capital Project Journal of Financial Education,

 

Fall 1997. p. 57-66.

 


 

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Oct 15, 2019

EXPERT

Tutor

ANSWER RATING

YES, THIS IS LEGAL

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now