Description

To assess your ability to:

Discuss an efficient portfolio and the relevance of the capital asset pricing model.

Evaluate the success of portfolio managers in diversifying their portfolios as measured against benchmarks.

Explain how the portfolio effect works to reduce the risk of an individual security.

In this assignment, you will measure the level of risk using the Sharpe method and then analyze the risk-adjusted returns in your original portfolio of 5 stocks.

Read Key Points in Portfolio Management on the Sharpe approach. If you have any questions about risk-adjusted returns communicate with your professor.

Review the original portfolio you developed in

3-3: Portfolio Project (Part 1) – Initial Selection

.

Analyze the risk-adjusted returns using the Sharpe model on the stocks in your original portfolio.

Use the

Risk Analysis Guide

and the associated

Excel file

to assist you in setting up your analysis. (I’ve attcahed those documents to the files section)

The original stocks I chose to use are

AT&T (Ticker Symbol: T)

Coca-Cola (Ticker Symbol: KO)

Tesla (Ticker Symbol: TSLA)

Logistics XPO (Ticker Symbol: XPO)

Apple (Ticker Symbol: AAPL)

Chevron (CVX)

UPS (UPS)

Joy Global (JOY)

Walgreens (WAG)

Whole Foods (WFM)

Total

Shares

1,710

2,413

3,222

4,840

2,331

My Original Cost

$

116.90

$

82.87

$

62.06

$

41.32

$

85.79

Original Value Original Weight

$ 199,899.00

20%

$ 199,965.31

20%

$ 199,957.32

20%

$ 199,988.80

20%

$ 199,976.49

20%

$ 999,786.92

Current Price

$

118.64

$

83.55

$

58.15

$

48.31

$

83.68

Do this simple exercise: use the closing price as of the day that you turned in your assign

near then for the “My original cost” value. Once you have that, you’ll be able to calculat

shares you purchased at the beginning of this assignment. Just take what you allocated

companies, and then divide it by the “My Original Cost” value to determine the shares.

Then collect the “current price and input those values. Pretty much everything else will

this worksheet once you do that. The only other thing that you want to input here are th

that you would have received during the period that you held the stock.

On this worksheet, we’re going to calcualte your portfolio return, and you’ll also determn

portfolio weights, which you need in order to determine the portfolio return and standa

tab “4. Portfolio return and SD”

Current Value

Current Weight

$ 202,874.40

19.9%

$ 201,606.15

19.8%

$ 187,359.30

18.4%

$ 233,820.40

22.9%

$ 195,058.08

19.1%

$ 1,020,718.33

Change in Value % Change In value

$

2,975.40

1.5%

$

1,640.84

0.8%

$

(12,598.02)

-6.3%

$

33,831.60

16.9%

$

(4,918.41)

-2.5%

$

20,931.41

2.1%

that you turned in your assignment or some e date

that, you’ll be able to calculate the number of

Just take what you allocated to each of your

ue to determine the shares.

tty much everything else will take care of itself on

you want to input here are the dividends per share

eld the stock.

eturn, and you’ll also determne your current

e portfolio return and standard deviation over on

Dividends

$

$

$

$

$

$

–

Return, including dividends

1.5%

0.8%

-6.3%

16.9%

-2.5%

2.1%

No dividends were received during the holding

s were received during the holding period

The average Risk Free Rate

Date

3 month T-Bill

Jan-06

4.24

Feb-06

4.43

Mar-06

4.51

Apr-06

4.6

May-06

4.72

Jun-06

4.79

Jul-06

4.95

Aug-06

4.96

Sep-06

4.81

Oct-06

4.92

Nov-06

4.94

Dec-06

4.85

Jan-07

4.98

Feb-07

5.03

Mar-07

4.94

Apr-07

4.87

May-07

4.73

Jun-07

4.61

Jul-07

4.82

Aug-07

4.2

Sep-07

3.89

Oct-07

3.9

Nov-07

3.27

Dec-07

3

Jan-08

2.75

Feb-08

2.12

Mar-08

1.26

Apr-08

1.29

May-08

1.73

Jun-08

1.86

Jul-08

1.63

Aug-08

1.72

Sep-08

1.13

Oct-08

0.67

Nov-08

0.19

Dec-08

0.03

Jan-09

0.13

Feb-09

0.3

Mar-09

0.21

Apr-09

0.16

May-09

0.18

Jun-09

0.18

The instructions for the assignment suggest determining the risk free rate first. This is how

I approached calculating the average risk free rate. I’ve pulled 60 months of data and

calculated the average. This illustrates data through December 2010. Please pull the most

current information.

You’re given links in the assignment. If these (or any you are given) don’t work, please let

your instructor know.

For more recent data your risk free rate will probably be lower than what’s shown here.

You’ll be pulling in 60 data points, and at the start of those 60, the rate will be lower and

toward the end of the data set, the rate will likely be even lower. (May 2014)

Jul-09

Aug-09

Sep-09

Oct-09

Nov-09

Dec-09

Jan-10

Feb-10

Mar-10

Apr-10

May-10

Jun-10

Jul-10

Aug-10

Sep-10

Oct-10

Nov-10

Dec-10

Average

0.18

0.17

0.12

0.07

0.05

0.05

0.06

0.11

0.15

0.16

0.16

0.12

0.16

0.16

0.15

0.13

0.14

0.14

2.146333

Make sure that you use the number that you calculate as the average as a Percent. If I

used 2.14 in my calculation of the sharpes ratio, I would be using a risk free rate of 214%

this would make my analysis useless.

risk free rate first. This is how

d 60 months of data and

ber 2010. Please pull the most

given) don’t work, please let

er than what’s shown here.

0, the rate will be lower and

wer. (May 2014)

s the average as a Percent. If I

be using a risk free rate of 214%–

Returns

2008

2009

2010

2011

2012

Chevron UPS

Joy Global Walgreens Whole Foods

-18.0%

-19.5%

-64.2%

-34.1%

-75.4%

7.7%

7.3%

128.4%

50.9%

190.8%

22.2%

29.8%

69.6%

7.8%

84.3%

20.0%

3.7%

-12.8%

-13.1%

38.3%

4.9%

3.9%

-14.0%

15.0%

34.7%

Average

7.4%

5.0%

21.4%

5.3%

54.5%

Standard Deviation

16%

17%

77%

32%

96%

Range of Returns based on Standard deviation

High

23%

23%

Low

-9%

-12%

98%

-55%

37%

-27%

151%

-42%

Note, returns are pulled f

1. You might comment o

measurements of risk and

your risk tolerance. Also

in return, and whether it

historical reputations. Th

2. You might comment o

example, it probably does

in the retail space. It may

two very differnet compa

together doesn’t create a

seeking diversification.

Correlation Tables

Chevron / UPS

0.870381

Chevron / Joy Global

0.523774

Chevron / Walgreens

0.389199

Chevron / Whole Foods 0.587598

UPS / Joy Global

0.67499

UPS / Walgreens

0.512551

UPS / Whole Foods

0.635245

Joy Global / Walgreens 0.885016

Joy Global / Whole Foods0.962634

Walgreens / Whole Foods0.938383

This tab of my spreadsheet mimics the set up of the table in the word docum

includes the instructions for this assignment on the course website for this a

This version illustrates how you would use it. Feel free to use this format.

Note, returns are pulled from the Morningstar site, rather than calculating them out.

1. You might comment on the average return and standard deviations that you’re seeing. These are good

measurements of risk and reward. Perhaps make a comment relating to any that are within or possibly outside of

your risk tolerance. Also consider whether any reputationally “solid” or “stable” companies have had really big swings

in return, and whether it makes sense to try to debunk any biases we hold about certain companies because of their

historical reputations. This can be eye opening.

2. You might comment on the correlations that result from this exercise. Are there any that surprise you? For

example, it probably doesn’t surprise you that Walgreens and Whole Foods are highly correlated because they’re both

in the retail space. It may surprise you that there’s a high correlation between Joy Global and Walgreens. These are

two very differnet companies with highly correlated returns, so this exercise points out that holding these companies

together doesn’t create a lot of diversity, as measured by correlated returns. That’s a troubling conclusion if you are

seeking diversification.

up of the table in the word document that

t on the course website for this assignment.

it. Feel free to use this format.

ossibly outside of

e had really big swings

ies because of their

prise you? For

because they’re both

algreens. These are

ing these companies

onclusion if you are

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Correlations

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Covariance

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Portfolio

Weights

19.9%

19.8%

18.4%

22.9%

19.1%

1

Portfolio

mean

sigma

risk free rate

Sharpe’s Measure

Mean

7.4%

5.0%

21.4%

5.3%

54.5%

Chevron

1

0.870381

0.523774

0.389199

0.587598

Chevron

0.025767

0.02441

0.064463

0.019927

0.090699

Chevron

19.9%

0.001018

0.000958

0.002352

0.000907

0.003445

0.00868

Sigma

16.1%

17.5%

76.7%

31.9%

96.2%

UPS

0.870381

1

0.67499

0.512551

0.635245

UPS

0.02441

0.030524

0.090417

0.028562

0.106721

UPS

19.8%

0.000958

0.001191

0.003278

0.001292

0.004028

0.010748

The Mean and Sigma should match the

summary sheet info for average return

and standard deviation

This tab of the spreadsheet is

assignment. Again, feel free t

Joy Global Walgreens

Whole Foods

0.523774 0.389199 0.587598

The numbers here in blue should match t

0.67499 0.512551 0.635245

Sheet”; the numbers in black are linked t

1 0.885016 0.962634 the other way, for example Whole Foods

0.885016

1 0.938383 Foods in blue.

0.962634 0.938383

1

Joy Global Walgreens

Whole Foods

0.064463 0.019927 0.090699

0.090417 0.028562 0.106721 Here you don’t have to do anything, the s

0.587847 0.216428 0.709707

0.216428 0.101732 0.287804

0.235409 0.287804 0.92464

Joy Global Walgreens

Whole Foods

18.4%

22.9%

19.1%

0.002352

0 0.003445

Enter your current weights from the “1. M

0.003278 0.001292 0.004028

vertical column A, beginning at row 21.

0.019806

0.0091 0.024895

0.0091 0.005338 0.012599

0.008258 0.012599 0.033767

0.042794 0.02833 0.078733

18.0%

41.1%

2.2%

0.38559

Please note that the spreadsheet that you’re downloading doesn’t actually run the

calculation of the Sharpe measures. You will have to include this yourself.

All that work for these tw

actuallly calculate the Sha

deviation

incorporate the risk free r

Standing on its own the S

compare your portfolio t

the risk that you’re takin

Please comment on your

large cap mutual fund (if

Sharpe ratio), compare it

something. The ratio’s u

Further, another way to

consider weigthing it diff

Scenario 1… and 6. Scen

when you change your a

Scenario 1… and 6. Scen

when you change your a

profile of the portfolio.

This tab of the spreadsheet is a more explanatory version of the spreadsheet that you can download from the course website fo

assignment. Again, feel free to use this format.

mbers here in blue should match the correlations you set up on “3. Summary

the numbers in black are linked to the numbers in blue to show the correlation

er way, for example Whole Foods / Walgreens in black, vs. Walgreens / Whole

u don’t have to do anything, the spreadsheet is set up to calculate covariance

ur current weights from the “1. My Portfolio and Weights” here and they’ll flow down the

olumn A, beginning at row 21.

All that work for these two numbers–and you’re not quite done yet!!! You need to

actuallly calculate the Sharpe Ratio: (portfolio return – risk free rate)/ portfolio standard

deviation–please note in your worksheet, this formula isn’t set up,. You need to

incorporate the risk free rate.

Standing on its own the Sharpe ratio won’t tell you that much. You have to use it to

compare your portfolio to others to determine whether you’re getting a good return for

the risk that you’re taking.

Please comment on your Sharpe ratio: compare it to the results here, or to some other

large cap mutual fund (if you pick mutial funds on Morningstar.com, they’ll have a

Sharpe ratio), compare it to some average Sharpe ratio–whatever you do, compare it to

something. The ratio’s usefulness is in the comparison of one to another.

Further, another way to create context for your ratio is to use your own portfolio, but

consider weigthing it differently to determine a better potential allocation. Tabs 5.

Scenario 1… and 6. Scenario 2… in this workbook attempt to illustrate what happens

when you change your allocation–which will inevitably change your risk and return

Scenario 1… and 6. Scenario 2… in this workbook attempt to illustrate what happens

when you change your allocation–which will inevitably change your risk and return

profile of the portfolio.

from the course website for this

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Correlations

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Covariance

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Portfolio

Weights

60.0%

5.0%

30.0%

2.0%

3.0%

1

Portfolio

mean

sigma

risk free rate

Sharpe’s Measure

Mean

7.4%

5.0%

21.4%

5.3%

54.5%

Chevron

1

0.870381

0.523774

0.389199

0.587598

Chevron

0.025767

0.02441

0.064463

0.019927

0.090699

Chevron

60.0%

0.009276

0.000732

0.011603

0.000239

0.001633

0.023484

Sigma

16.1%

17.5%

76.7%

31.9%

96.2%

UPS

0.870381

1

0.67499

0.512551

0.635245

UPS

0.02441

0.030524

0.090417

0.028562

0.106721

UPS

5.0%

0.000732

7.63E-05

0.001356

2.86E-05

0.00016

0.002354

Joy Global Walgreens

Whole Foods

0.523774 0.389199 0.587598

0.67499 0.512551 0.635245

1 0.885016 0.962634

0.885016

1 0.938383

0.962634 0.938383

1

Joy Global Walgreens

Whole Foods

0.064463 0.019927 0.090699

0.090417 0.028562 0.106721

0.587847 0.216428 0.709707

0.216428 0.101732 0.287804

0.235409 0.287804 0.92464

Joy Global Walgreens

Whole Foods

30.0%

2.0%

3.0%

0.011603

0 0.001633

0.001356 2.86E-05 0.00016

0.052906 0.001299 0.006387

0.001299 4.07E-05 0.000173

0.002119 0.000173 0.000832

0.069283 0.001541 0.009185

12.8%

32.5%

2.2%

0.328056

Comment on how or whether your shar

allocation

ent on how or whether your sharpes measure changed when you changed your

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Correlations

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Covariance

Chevron

UPS

Joy Global

Walgreens

Whole Foods

Portfolio

Weights

0.0%

10.0%

0.0%

45.0%

45.0%

1

Portfolio

mean

sigma

risk free rate

Sharpe’s Measure

Mean

7.4%

5.0%

21.4%

5.3%

54.5%

Chevron

1

0.870381

0.523774

0.389199

0.587598

Chevron

0.025767

0.02441

0.064463

0.019927

0.090699

Chevron

0.0%

0

0

0

0

0

0

27.4%

58.0%

2.2%

0.435463

Sigma

16.1%

17.5%

76.7%

31.9%

96.2%

UPS

0.870381

1

0.67499

0.512551

0.635245

UPS

0.02441

0.030524

0.090417

0.028562

0.106721

UPS

10.0%

0

0.000305

0

0.001285

0.004802

0.006393

Joy Global Walgreens

Whole Foods

0.523774 0.389199 0.587598

0.67499 0.512551 0.635245

1 0.885016 0.962634

0.885016

1 0.938383

0.962634 0.938383

1

Joy Global Walgreens

Whole Foods

0.064463 0.019927 0.090699

0.090417 0.028562 0.106721

0.587847 0.216428 0.709707

0.216428 0.101732 0.287804

0.235409 0.287804 0.92464

Joy Global Walgreens

Whole Foods

0.0%

45.0%

45.0%

0

0

0

0 0.001285 0.004802

0

0

0

0 0.020601 0.05828

0 0.05828 0.18724

0 0.080166 0.250322

FINA 405

Guide to Portfolio Project (Part III)

Risk Adjusted Return Analysis

In this individual assignment you are asked to measure the level of risk of your original portfolio

of five stocks using the Sharpe ratio. In preparation for the assignment and the discussion about

it, you might review the following in addition to the discussion in the moduleÃ¢â‚¬â„¢s Key points

section:

http://www.advisor.ca/images/other/ae/ae_0105_rrspsg_riskybusiness.pdf

http://www.investopedia.com/articles/07/sharpe_mpt.asp

For the Sharpe index you will need three numbers: mean return on your portfolio, standard

deviation on your portfolio, and the average risk-free return. It is proposed that everyone rely

upon monthly data for the last 5 years or 60 data points. A sample Excel document has been

created by Professor Jeff Racz and is similarly attached here to help you with this assignment.

The following walks you through the Excel document:

In the first tab there is a sample portfolio–yours will look different and the holdings should end

up at about $1,000,000, as this is what you were starting with for your allocation. Please create a

similar document with your original initial portfolio with the date from the due date of that

assignment. What becomes important from this tab are the CURRENT weights of each company

in the portfolio. You’ll use these weights in the “Portfolio weights and SD” tab to follow.

The second tab is the average risk-free rate. This is where you’d pull the 3 month Treasury bill

data in and take an average. In this example there is old data. Please collect more current data

yourself for your analysis.

You can find the data that you’ll need to determine the risk-free rate at this address:

http://www.federalreserve.gov/datadownload/Output.aspx?rel=H15&series=8a0cb4c31786ea79a

513ab14cf2e5389&lastObs=&from=&to=&filetype=csv&label=include&layout=seriescolumn

Further, make sure that when you use this number to calculate your Sharpe Ratio, you use this

number as a percent. In other words, if you determine that the average you determine is 2.15 as

in the sample, if you donÃ¢â‚¬â„¢t convert it to a percent, your spreadsheet will think that the risk free

rate is 215%. Your results will be incorrect to the point that the Sharpe ratio that you determine

will be useless.

1

The “3. Summary Sheet” tab shows 5 years of annual returns for the 5 stocks, their averages and

standard deviations, and their correlations with each other. Here you can simply replace

company names with your own and input the returns.

One way to calculate annual returns is as follows:

Ã¢â‚¬Â¢

Go to Yahoo! Finance at http://finance.yahoo.com/, enter the ticker symbol to go to your

stockÃ¢â‚¬â„¢s main page, and then click under Ã¢â‚¬Å“QuotesÃ¢â‚¬Â on Ã¢â‚¬Å“Historical Prices.Ã¢â‚¬Â There you can

set the dates for the past 5 years going up to the present and select monthly prices. Use

the prices in the last column titled Adj Close to get end-of-month prices that reflect

dividends and stock splits. Notice that at the bottom of the Ã¢â‚¬Å“Historical PricesÃ¢â‚¬Â page you

can download this data to a spreadsheet. You are asked to calculate average returns and

standard deviations for each of the 5 stocks in your portfolio based on 5 years of monthly

stock price data that is 60 data points going back 5 years.

How do we calculate monthly returns and annualize them? Assume that we are currently

in June 2009. Start with the Adj Close price for June 2004 and subtract the Adj Close

price for May 2004. Divide this difference by the May 2004 price. In Excel, for example,

= (G3-G2)/G2 . This is the monthly return on the stock for June 2004. The precise way to

transform this into an annual return is as follows: Annual Return = (1 + Monthly

Return)^12 Ã¢Ë†â€™ 1 Or in Excel, assuming the monthly return is in cell H3: =((1+H3)^12) Ã¢â‚¬â€œ 1.

Alternatively, a less tedious approach, and one probably preferable all the way around, is to go to

Morningstar, type in the ticker and then click the Ã¢â‚¬Å“performanceÃ¢â‚¬Â tab, and youÃ¢â‚¬â„¢ll see returns on

the stock for the last 5 years. For

example: http://performance.morningstar.com/stock/performancereturn.action?t=WMT®ion=USA&culture=en-US

Here, youÃ¢â‚¬â„¢ll see the returns for the last five years for WMT.

Once you have the returns, the worksheet should calculate the various correlations. ItÃ¢â‚¬â„¢s probably

worth commenting on any interesting correlations that you see, strong or weak, or something that

you might not have expected. YouÃ¢â‚¬â„¢ll notice that text boxes are included right on the

worksheets. This is a totally acceptable approach for your analysis. ItÃ¢â‚¬â„¢s worth commenting on

what youÃ¢â‚¬â„¢re seeing, and how youÃ¢â‚¬â„¢re interpreting it, and how youÃ¢â‚¬â„¢re thinking about it.

The “4. Portfolio Return and SD” provides the tools to calculate your portfolioÃ¢â‚¬â„¢s return and

standard deviation. It is suggested that you first save the original and then enter your own data on

a second file.

In creating your own version of this Excel file here is what it is suggested that you do:

2

1. Mean and Sigma: In cells 2-6 enter your average returns for each stock in Column B and

standard deviations for each stock in Column C.

2. Correlation. Enter your correlations on the upper right of the matrix and the same

numbers should also appear in the lower left as mirror images. Notice the diagonal row of 1Ã¢â‚¬â„¢s

down the middle: the correlation of any stock returnÃ¢â‚¬â„¢s with itself is one

3. Covariance. You need do nothing in cells 14 through 18.

4. Portfolio Weights. In Row 20 enter your weights summing to 1.0 in the 5 columns for

your 5 stocks. In the template they are approximately evenly weighted. You should then get

both the return on the portfolio as well as the portfolioÃ¢â‚¬â„¢s standard deviation (SD) below.

Please note that you have to calculate the Sharpe ratio.

Notice too, in Tabs 5 and 6 are a couple of different scenarios on the portfolio weights to see if

the Sharpe ratio changes. This provides context to say whether the sharpes ratio is good or

bad. Again, the number’s just a number. With context it can start to mean something.

As for what to deliver:

1. Please do complete the worksheets given to you in tabs 1-4. through the course site, or use

this as a model. whichever you’re most comfortable with.

2. Determine a Sharpe ratio.

3. Please comment on the Sharpe ratio: relevant things to include might be a restatement or

definition of what the Sharpe ratio tells you, and what you make of the ratio that you

determine. Compare your ratio to something. A ratio on its own doesnÃ¢â‚¬â„¢t tell you much. For

example you might find a large cap mutual fund and use that as a benchmark. See how your

Sharpe ratio stacks up against some fund that you have in your 401k plan or IRA. That could be

interesting and could be a useful exercise to see what youÃ¢â‚¬â„¢ve got.

4. If you run scenarios, make comments on those, comparing your results across different

portfolios.

The comments that you make don’t have to be long or involved: a paragraph, two at the

most. You can even include them in text boxes in Excel if thatÃ¢â‚¬â„¢s easier for you. We want to see

you do more than just produce numbers. We want to understand how youÃ¢â‚¬â„¢re thinking about them.

3

Purchase answer to see full

attachment