Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started