View the step-by-step solution to:

Use the spreadsheet below to make calculations below and then answer the questions based on your calculations.

Use the spreadsheet below to make calculations below and then answer the questions based on your calculations. The three stocks used in the spreadsheet are Safeway Company (SFY), General Motors (GM), and Microsoft (MSFT). The Russell 3000 index is used to measure the market portfolio.


1.     Go to the tab labelled "Returns." Calculate the following for each of the three stocks and the market index (e.g., write formulas to fill in the yellow shaded cells)

a.      Mean monthly return

b.     Standard deviation of the monthly return

c.      Mean annualized monthly return


2.     Go to the tab labelled "Covariances" and calculate the beta measures for each of the three stocks and the market portfolio.


3.     Go to the tab labelled "Portfolio Analysis." Calculate all of the values indicated by the yellow shaded cells using the first scenario where the portfolio is made up of equal weights of the three stocks.

a.      Mean monthly return for the portfolio

b.     Standard deviation of the monthly portfolio return

c.      Mean annualized monthly return for the portfolio

d.     Covariance of the portfolio with the market return

e.      Mean monthly return for the market return

f.       Standard deviation of the monthly market return

g.      Mean annualized monthly return for the market

h.     Covariance of the market with the market return (i.e., the market variance)

i.       The portfolio beta


4.     Change the portfolio weights to the following:

a.      Microsoft: 50%

b.     Safeway: 25%

c.      General Motors: 25%

d.     Note the changes and answer questions below.


5.     Change the portfolio weights to the following:

a.      Microsoft: 10%

b.     Safeway: 80%

c.      General Motors: 10%

d.     Note the changes and answer questions below.


Word document that answers the following questions:

 

1.     Based on the standard deviation of the monthly returns, which of the three stocks appears to be the most risky: Safeway, General Motors, or Microsoft? How do they compare to the risk in the market as measured by the standard deviation of the market return?

2.     What are the beta measures for each of the three stocks? What do these mean when you compare them for the three stocks?

3.     Describe what happens when you change the weights of each stock in the portfolio from the first to the second scenario, and then from the second to the third scenario. What happens to the portfolio return, standard deviation, and beta measurements? Explain why those changes are taking place.

4.     Which of the three portfolio mixes would you recommend for an aggressive investor? Explain your reason for making that recommendation.

5.     Which of the three portfolio mixes would you recommend for a defensive investor? Explain your reason for making that recommendation.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question