ZuoTi.Pro
Question

I need help understanding what I need to do to complete this spreadsheet (steps on how to start it) in Excel, thank you!
Spreadsheet Assignment 2 Riesk and Return Historical Data NSTRUCTIONS listorical price data are obtained for Stocks X, Y, Z. To erform the required analysis, as demonstrated in the preadsheet modeling video, first calculate the rates f return for each stock. Then use the RETURNS data not price) to perform the required analysis. Please be ure to watch the video before completing this ssignment! ANNUAL DATA OF THR STOCKS Year Stockx StockY Stockz Stock StockY Stockz 24.83 0.2683 0.0558 0.4501 52.68 30.43 2327 49 8215 830 4732 0.125304850 37.35 564725.710.4823 00689-0 8558 60.5060.50 46.51 67.12 3852 04536 05342 0.0577 2955 39.34 2036 1888 2222 0.4618 0.5659 0.0709 60 50 0.2630 0.1038 04515 36.36 0.3725 0.735 0.4925 20.70 mportant! Perform the required tasks exactly in the same LOGICAL ORDER that they are given, presenting ur analysis BELOW each required task from 1 to 11. Expand the rows below each question to allow for a lear presentation of your work. 10.71 Your score will depend on (1) correctness of your nalysis, (2) presentation of your spreadsheet, and 3) adherence to this instruction. 25 4, Present the covariance matrx o 0 : You may alow for short seing, if you wish. Due NOT use the same weights as n the vdeo presentation see the video demo 4 Do not

D2 6 8 Which portfolio on your efficient set has the lowest variance? What is the Expected Return on this portfolio? What is the standard devistion? 9 Consider the risk-return estimates of all 3 stocks: X, Y, Z. Suppose you wish to invest in only one of the stocks. Which should you choose and why? 40 10. Refer to the dataset below for the stock market index and Apple stock (symbot AAPL) Calculate RATE OF RETURN h(PUPt-1). Then run a regression and find beta. 421 1 1 . Calculate the required rate of return on AAPL using the CAPM. Assume rF = 5% and E(Rm) = 9 5% Note: use the beta calculated above

Home Insert Page Layout File Cut Arial aCopy Paste Format Painter Clipboard Font D2 AAPL 2/1/2011 1,307.59 345.03 1312011 1,286.12 339.32 47」 3 12/1/2010 1257 64 32256 11/12010 1,180.55 311.15 5 10/1010 1,183.26 300.98 6 9/1/20101,141.20 283.75 /2/2010 1,049.33 243.10 7/1/2010 1,101.60 257.25 9 6/1/2010 1,030.71 251.53 54 10 53/2010 1,089.41 256.88 4/5/2010 1,16.69 261.09 56 12 3/1/2010 1,169.43 235.00 57 13 2/1/2010 1,104.49 204.62 58 141/4/2010 1,073.87 192.06 59 15 12/1/2009 1,115.10 210.73 60 16 11/2/2009 1,095.63 199.91 61 17 10/1/2009 1,036.19 188.50 62 18 9/1/2009 1,057.08 185.35 63 19 8/3/2009 1,020.62 168.21 64 20 7/1/2009 987.48 163.39 6/1/2009 919.32 142.43 66 22 54/2009 919.14135.81 67 23 4/1/2009 872.81 125.83 2/2009 797.87 105.12 69 25 22/2009 735.09 89.31 68 24 22009 825.88 90.13 1 27 12/1/2008 903.25 85.35 92.67 73 29 10/1/2008 968.75 107.59 30 9/1/2008 1,166.36 113.66 75 31 8/1/2008 1,28283 169.53 76 32 7/1/2008 1267.38 158.95 33 6/2/2008 1,280.00 167.44 78 34 5/22008 1,400.38 188.75 72 28 11/3/2008 896.24 绸 イトト1: ASSIGNMENT. Ready

Clipboard Font D2 77 336/2/2008 1,280.00 167.44 5/2/20081,400.38 188.75 79 35 4/1/2008 1,385.59 173.95 3/3/20081,322.70 143.50 8 2/1/2008 1,330.63 125.02 1/2120081,378.55 135.36 83 39 12/3/2007 1,468.36198.08 84 4011/1/2007 1,481.14 182.22 85 4110/1/2007 1,549.38 189.95 9/3/20071,526.75 153.47 8/1/20071,473.99 138.48 7/2/2007 1,455.27131.76 6/1/2007 1,503.35 122.04 90 465/2/2007 1,530.62 121.19 99.80 8137 86 42 87 43 88 44 89 45 91 47 4/3/2007 1,482.37 92 483/1/2007 1420.86 9349 2/1/20071,406.82 84.61 1/2/20071,438.24 95 5112/1/2006 1,418.30 96 52 11/1/2006 1,400.63 97 530/2/2006 1,377.94 84.84 91.66 81.08 76.98 67.85 67.96 57.27 59.77 70.39 62.72 105 61 2/1/2006 1.280.6668.49 06 621/2/2006 1.280.08 75.51 9/1/2006 1,335.85 558/1/2006 1,303.82 100 567/3/2006 1,276.66 101 57 6/1/2006 1.270.20 102 58 5/2/2006 1.270.09 103 594/3/2006 1,310.61 104 60 3/1/2006 1.294.87 107 6312/1/2005 1.248.29 71.89 108 64 11/1/2005 1.249.48 67.82 09 6510/3/2005 1207.01 57.59

fr 65 10/3/2005 1,207.01 66 9/2005 1,228.81 67 8/1/2005 1,220.33 7/1/2005 1,234.18 6/1/20051,191.33 70 5/2/2005 1,191.50 4/1/2005 1,156.85 3/1/2005 1,180.59 2/1/2005 1,203.60 57.59 46.89 42.65 36.81 39.76 36.06 44.86 1/3/2005 1,181.27 38.45 75 12/1/2004 1,211.92 76 11/1/2004 1,173.82 77 10/1/2004 1,130.20 9/1/2004 1,114.58 8/2/2004 1,104.24 80 7/1/2004 1,101.72 32.20 33.53 26.20 19.38 17.25 16.17 78 6/1/2004 1,140.84 16.27 82 5/3/2004 1,120.68 4/1/2004 1,107.30 84 3/1/2004 1,126.21 85 2/2/2004 1,144.94 1/2/2004 1,131.13 87 12/1/2003 1,111.92 88 11/3/2003 1,058.20 14.03 12.89 13.52 11.96 11.28 10.69 89 10/1/2003 1,050.71 11.44 10.36 11.31 10.54 9.53 8.98 7.11 7.07 9/1/2003 995.97 8/1/2003 1,008.01 7/1/2003 990.31 93 6/2/2003 974.50 963.59 4/1/2003 916.92 96 3/3/2003 848.18 2/3/2003 841.15 1/2/2003 855.70 99 12/2/2002879.82 5/2/2003 98 7.16

2 142 98 112/2003855.707.18 143 99 12/212002879 144 100 11/1/2002936.31 145 101 10/1/2002 885.76 146 102 9/212002815.28 147 103 812002 916.07 1481104一 7/1/2002 911.62 149 105 6/3/2002 989.82 150 106 5/2/2002 1,067.14 151 107 4/2/2002 1,076.92 52 108 3/1/2002 1,147.39 153 109 2/12002 1,106.73 154 110 1/2/2002 1,130.20 155 11112/3/2001 1,148.08 156 112 11/1/2001 1,139.45 157 113 10/1/2001 1,059.78 158 114 9/3/2001 1,040.94 159 115 8/1/2001 1,133.58 160 116 712/2001 1,211.23 161 117 6/1/2001 1,224.38 162 | 118。5/2/2001 1,255.82 163 119 4/3/2001 1,249.46 164 120 3/1/2001 1,160.33 165 121 2/1/2001 1,239.94 166 1221/2/2001 1,366.01 167 123 12/1/2000 1,320.28 168 124 11/12000 1,314.95 169 125 10/9/2000 1,429.40 170 126 9/1/2000 1,436.51 8/1/2000 1,517.68 172 128 7/3/20001,430.83 12.14 10.85 10.95 10.65 12.74 12.88 25.41 21.00 171127 173129 6/1/2000 1,454.60 174 130 S/1/2000 1,420.60 175 131 4/3/2000 1,452.43 178 1323/1/2000 1,498.58 14 > HI ASSIGNMENT..

Clipboard 0 156 112 11/1/2001 1,139.45 157 113 10/1/2001 1,059.78 913/2001 1,040.94 10.65 7.76 159 115 8/1/2001 1,133.589.27 160 116 712/2001 1,211.23 9.40 158 114 161 117 6/1/20011,224.38 162 118 5/2/2001 1,255.82 163 119 4/3/2001 1,249.46 164 120 3/1/20011,160.33 165 121 2/1/2001 1,239.94 1/2/2001 1,366.01 167 123 12/1/2000 1,320.28 168 124 11/2000 1,314.95 169 125 10/9/2000 1,429.40 170 126 9/1/2000 1,436.51 171 127 8/1/2000 1,517.68 172 128 773/2000 1,430.83 173 129 6/1/2000 1,454.60 174 130 5/1/2000 1,420.60 4/3/2000 1,452.43 3/1/2000 1,498.58 177 133 2/1/2000 1,366.42 178 134 1/3/2000 1,394.46 179 135 12/1/1999 1,469.25 180 136 11/1/1999 1,388.91 81 137 10/1/1999 1,362.93 182 138 9/1/19991,282.71 183 139 8/2/1999 1,320.41 184 140 7/1/1999 1,328.72 135 141 6/1/1999 1,372.71 186 142 5/3/1999 1.301.84 4/1/1999 1,335.18 188 144 3/1/1999 1,286.37 189 1452/1/1999 1238.33 190 146 1/7/1999 1,279.64 12.74 11.03 166 122 10.81 9.78 30.47 26.19 175 131 76 132 33.95 28.66 25.94 25.70 24.47 20.03 16.31 13.92 11.58 11.02 187 143 8.70 4 ASSIGNMENT

math   Statistics-And-Probability   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Calculations done in MS Excel is shown here with formula sheets.

Returns
Stock X Stock Y Stock Z
1 0.2683 0.0558 0.4501
2 -0.4732 -0.1253 -0.485
3 -0.4823 -0.0689 -0.8558
4 0.263 -0.1038 0.4515
5 0.4536 0.5342 0.0577
6 0.3725 0.7352 0.4925
7 0.4618 0.5659 0.0709
Total 0.8637 1.5931 0.1819
1 Mean 0.123386 0.227586 0.025986
2 standard deviation 0.418108 0.369211 0.519655
3 Coefficient of variation 3.388625 1.622294 19.99771
4 Covariance matrix
Column 1 Column 2 Column 3
Column 1 0.149841
Column 2 0.094448 0.116843
Column 3 0.157384 0.069946 0.231464
5 Correlation matrix
Column 1 Column 2 Column 3
Column 1 1
Column 2 0.7138 1
Column 3 0.845092 0.425322 1

Formula sheet

Returns
Stock X Stock Y Stock Z
1 0.2683 0.0558 0.4501
2 -0.4732 -0.1253 -0.485
3 -0.4823 -0.0689 -0.8558
4 0.263 -0.1038 0.4515
5 0.4536 0.5342 0.0577
6 0.3725 0.7352 0.4925
7 0.4618 0.5659 0.0709
Total =SUM(C3:C9) =SUM(D3:D9) =SUM(E3:E9)
Mean =C10/7 =D10/7 =E10/7
standard deviation =STDEV(C3:C9) =STDEV(D3:D9) =STDEV(E3:E9)
Coefficient of variation =C12/C11 =D12/D11 =E12/E11
Covariance matrix
Column 1 Column 2 Column 3
Column 1 =VARP(Sheet1!$C$3:$C$9)
Column 2 0.0944480183673469 =VARP(Sheet1!$D$3:$D$9)
Column 3 0.157383914081633 0.0699455440816326 =VARP(Sheet1!$E$3:$E$9)
Correlation matrix
Column 1 Column 2 Column 3
Column 1 1
Column 2 0.713800257994505 1
Column 3 0.845091581257916 0.425321592557462 1
0 0
Add a comment
Know the answer?
Add Answer to:
I need help understanding what I need to do to complete this spreadsheet (steps on how...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • please do the new one with all the 2 steps, completed one shows both the steps...

    please do the new one with all the 2 steps, completed one shows both the steps it asks for The following tables gives the population for a country, in millions of persons, for the years 1990-2009. Forecast the population in the years 2010 and 2011 Year1990 1991 1992 1993 1994 199519961997 1998 1999 Pop250|253257260 263|266269 273276279 Year 2000 | 2001| 2002 |2003|2004 | 2005 | 2006 | 2007| 2008| 2009 Pop 282 |285 288290293296299 302 304307 The forecast for 2010...

  • Falloing the spreadsheet calculates the average return and standard deviation for both Boeing and Bank of...

    Falloing the spreadsheet calculates the average return and standard deviation for both Boeing and Bank of America. 1991 Bank of America 1142 -118 505 13.7 1992 1993 138 1995 72.7 1996 53.2 321 1997 1998 1999 32.5 28.7 2000 2001 2002 61.2 40.4 13.4 30.4 45.6 18.9 2003 246 2004 25.3 37.9 2005 2006 2007 23.4 20.7 2008 -63.4 2009 2010 2011 2012 -50.1 31.8 23.6 15.2 5.2 84.2 -112 -58.1 34,6 155

  • Follow the spreadsheet, which of the stocks is riskier? Which has the higher return? 1991 Bank...

    Follow the spreadsheet, which of the stocks is riskier? Which has the higher return? 1991 Bank of America 1142 -118 505 13.7 1992 1993 138 1995 72.7 1996 53.2 321 1997 1998 1999 32.5 28.7 2000 2001 2002 61.2 40.4 13.4 30.4 45.6 18.9 2003 246 2004 25.3 37.9 2005 2006 2007 23.4 20.7 2008 -63.4 2009 2010 2011 2012 -50.1 31.8 23.6 15.2 5.2 84.2 -112 -58.1 34,6 155

  • Construct a normal probability plot for the residuals. Comment on the normality assumption. Use MINITAB package software/other software to compute and plot your data (No manual calculation is required...

    Construct a normal probability plot for the residuals. Comment on the normality assumption. Use MINITAB package software/other software to compute and plot your data (No manual calculation is required) ​​​​​​​ Value of $1000 Year 2014 1863 2013 1639 Year-X (Independent) Value of $1000 Y-Dependent 2012 1239 2011 1068 2010 1046 2009 909 719 2008 2007 1141 2006 1081 2005 934 2004 890 803 2003 624 2002 801 2001 2000 909 Value of $1000 Year 2014 1863 2013 1639 Year-X (Independent)...

  • 1. You are given the following time-series of precipitation data for Dubai. Using the Moving Windows...

    1. You are given the following time-series of precipitation data for Dubai. Using the Moving Windows technique: (a) First plot your data. (b) Use overlapping 5-year windows, with the windows having 3 common years (for example if the first window is years 2000, 2001, 2002, 2003, 2004 then the second window is years 2002, 2003, 2004, 2005, 2006) and plot the 5-year moving averages. (c) Explain any pattern that you see. (d) Calculate the average of the series that is...

  • (For problems 9-10) The numbers of named storms in the Atlantic Basin from 2000-2009 were entered...

    (For problems 9-10) The numbers of named storms in the Atlantic Basin from 2000-2009 were entered into Excel and the mean and standard deviation were computed as shown below. 1 А Year 2 2000 3 2001 4 2002 5 2003 6 2004 7 2005 8 2006 9 2007 10 2008 11 2009 12 13 mean 14 st. dev. B #storms 15 15 12 16 15 27 10 15 16 9 15.0 4.7 What Excel function was used to compute the...

  • 0 5 10 15 30 - Country B ---Country A 1997 1998 1999 2000 2001 2002...

    0 5 10 15 30 - Country B ---Country A 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 Unemployment Rate 14) (5 pts) Country A experienced higher cyclical unemployment than country B in the period 2005-07 15) (5 pts) Increasing international trade and financial globalization help explain why the two countries business cycles tend to move together 16) (5 pts) Country B experienced an increase...

  • The Nielsen ratings (% of US households that tuned in) for the Masters Golf tournament from...

    The Nielsen ratings (% of US households that tuned in) for the Masters Golf tournament from 1997 to 2008 are provided in the Excel spreadsheet Masters.xls posted on BB (Golf Magazine, January 2009). The rating of 11.2 in 1997 indicates that 11.2% of US households tuned in to watch Tiger Woods win his first major golf tournament and become the first African American to win the Masters. Tiger Woods won again in 2001, 2002 and 2005. 4. Would you recommend...

  • Please provide a guided answer or a brief explanation, your help is very appreciated. The figure...

    Please provide a guided answer or a brief explanation, your help is very appreciated. The figure below illustrates nominal (in current prices) and real (in prices of previous year) GDP of Estonia in 2000- 2015, in billion euro. Estonia 30 25 20 15 10 0 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 BGDP (constant 2010 USS) GDP (current USS) a) b) c) Using the figure above, highlight 4 stages of business...

  • Homework: HW 14 - Chapter 14 Score: 0 of 1 pt 4.2.77 The following tables gives...

    Homework: HW 14 - Chapter 14 Score: 0 of 1 pt 4.2.77 The following tables gives the population for a country, in milions of persons, for the years 1990-2009. Forecast the population in the years 2010 and 2011. Save 11 of 13 (10 complete) HW Score: 74.36%, 9.67 of 13 pts Question Help * Year 1990 1991 | 1992 | 1993 1994 |1995 | 1996 | 1997 1998 1999 Pop T 250 T 253 257T260 T 263 266 269 T...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT