Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copypaste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Based on the spreadsheet enclosed further below: a. Compute the average return for each of the assets from 1929 to 1940 (The Great Depression). b. Compute the variance and standard deviation for each of the assets from 1929-1940. c. Which asset was riskiest during the Great Depression? How does that fit with your intuition? a. Compute the average return for each of the assets from 1929 to 1940 (The Great Depression). S&P 500 Small Stocks Corp Bonds World Portfolio Treasury Bills Average return b. Compute the variance and standard deviation for each of the assets from 1929-1940. S&P 500 Small Stocks Corp Bonds World Portfolio Treasury Bills Variance of returns Standard deviation c. Which asset had the highest standard deviation during the Great Depression? How does that fit with your intuition? Most Volatile Asset Which? Highest average return Which? Does the asset with the highest average return also have the highest variance of returns? Data for Figure 11.1 Yearly returns from 1926-2015 for the S&P 500, small stocks, corporate bonds, world portfolio, Treasury bills, and inflation as measured by the Year S&P 500 Small Stocks Corp Bonds World Portfolio Treasury Bills 1926 0.111382 -0.072000 0.062852 0.228000 0.031943 -0.011876 1927 0.371260 0.257543 0.065504 0.253257 0.031247 -0.021635 1928 0.433079 0.468723 0.033772 0.334633 0.038241 -0.012285 1929 -0.089069 -0.504667 0.043206 -0.076923 0.047367 0.007463 1930 -0.252573 -0.455830 0.063433 -0.225738 0.023474 -0.064198 1931 -0.438580 -0.502165 -0.023803 -0.393052 0.010230 -0.092348 1932 -0.088612 0.086957 0.121985 0.030303 0.008055 -0.104651 1933 0.528949 1.872000 0.052552 0.661488 0.002933 0.009740 1934 -0.023405 0.252089 0.097282 0.025524 0.001547 0.012862 1935 0.472081 0.647386 0.068602 0.227824 0.001652 0.031746 1936 0.328010 0.875084 0.062197 0.192827 0.001749 0.012308 1937 -0.352577 -0.534030 0.025456 -0.169499 0.003187 0.030395 1938 0.331991 0.262751 0,043571 0.056139 0.000407 -0.029499 1939 -0.009096 0.001836 0.012473 -0.014406 0.000075 0.000000 1940 -0.100825 -0.123396 0.045124 0.035282 -0.000582 0.009119 1941 -0.117673 -0.137282 0.017882 0.187439 0.000422 0.099398 1942 0.210758 0.525040 0.031188 0.011890 0.002616 0.090411 1943 0.257576 1.000000 0.033648 0.198947 0.003396 0.027638 1944 0.196935 0.604078 0.031013 -0.102399 0.003215 0.024450 1945 0.364606 0.826647 0.035119 0.110316 0.003211 0.021480 1946 -0.081804 -0.126085 0.025578 -0.151238 0.003521 0.182243 1947 0.052401 -0.026580 0.004497 0.031962 0.004611 0.088933 1948 0.050990 -0.066086 0.037072 -0.057298 0.009790 0.029038 1949 0.215017 0.180648 0.043267 0.054209 0.011061 -0.021161 1950 0.459082 0.305792 0.018908 0.254772 0.012134 0.059459 1951 0,097671 0.245529 -0.002101 0.224464 0.061224 0.014833 1952 0.185011 0.064718 0.034268 0.006410 0.158215 0.016420 -0.059851 1953 0.017800 0.020612 -0.010987 0.048380 0.007962 1954 0.524037 0.652319 0.046578 0.498225 -0.007899 0.008626 1955 0.221095 0.004777 0.010754 0.314288 0.015551 0.247387 1956 0.028526 0.024215 0.034592 -0.017923 0.066270 0.065810 1957 0.029276 0.031286 -0.150527 -0.108535 0.044688 -0.060174 1958 0.014151 0.017961 0.433444 0.008487 0.709232 0.344562 LAT 200000000000000000001 K M N m 1 109 ms 198 6 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 0.433444 0.119044 0.004830 0.268114 -0.087840 0.226913 0.163576 0.123558 -0.101046 0.239425 0.110023 -0.084656 0.039857 0.143255 0.189356 -0.147912 -0.265389 0.372500 0.236746 -0.073880 0.064399 0.183529 0.322665 -0.050530 0.214833 0.224997 0.061523 0.316476 0.186034 0.051709 0.166084 0.316862 -0.031043 0.304659 0.076194 0.100787 16 0.709232 0.186804 -0.050042 0.308362 -0.129502 0.180929 0.194053 0.363655 -0.073020 0.779159 0.476107 -0.322186 -0.045027 0.163471 0.042220 -0.359362 -0.273271 0.827689 0.571158 0.199153 0.204200 0.396976 0.291406 0.146172 0.322502 0.480-178 -0.024955 0.291249 0.059082 -0.092043 0.202876 0.014882 -0.363252 0.417989 0.220748 0.137919 0.008187 0.001569 0.067221 0.036809 0.062038 0.031670 0.039876 0.020821 -0.002550 -0.011624 0.224570 -0.02.1591 0.111788 0.096820 0.083226 0.029869 0.002332 0.110394 0.145568 0.055102 0.018334 -0.015579 -0.049761 0.089767 0.348984 0.073167 0.171031 0.294865 0.209130 -0.015778 0.137929 0.153096 0.086061 0.158685 0.106420 0.146619 2.7 0.3414562 0.233035 0.03.1919 0.207775 -0.062116 0.153811 0.112465 0.098323 -0.101164 0.212827 0.139405 -0.038576 -0.019840 0.195673 0.235470 -0.145057 -0.244795 0.344929 0.147118 0.020020 0.182157 0.126705 0.277209 -0.033045 0.112723 0.232802 0.057733 0.417705 0.427988 0.167637 0.239504 0.171936 -0.165171 0.189665 -0.046597 0.231287 E24 0.014151 0.028153 0.025824 0.021590 0.027240 0.031514 0.035182 0.039620 0.047050 0.041473 0052941 0.065913 0.063829 0.043171 0.038911 0.070587 0.080781 0.058211 0.051556 0.051520 0.073082 0.106898 0.115227 0.148559 0.1066-13 0.088471 0.099558 0.076766 0.060571 0.053814 0.063228 0.082206 0.076800 0.055065 0.034015 0.028993 2000 0.017964 0.017647 0.013006 0.007133 0.012748 0.016783 0.009629 0.019074 0.034759 0.031008 0.046366 0,062275 0.055242 0.033120 0.034126 0.087000 0.123275 0.069615 0.018239 0.067202 0.090349 0.133082 0.125208 0.089119 0.037975 0.038328 0.039430 0.037934 0.010886 0.01231 0.044199 0.016561 0.061004 0.030813 0.028968 0.027553 11-11 A1 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 0.076194 0.100787 0.013205 0.375778 0.229603 0.333633 0.285786 0.210415 -0.091044 -0.118858 -0.221005 0.286846 0.108820 0.049114 0.157950 0.054937 -0.369976 0.264635 0.150640 0.021118 0.160032 0.323885 0.136884 0.013838 0.220748 0.137919 -0.010961 0.200561 0.215107 0.320803 -0.119749 -0.012600 -0.024901 0.327806 -0.056243 0.691508 0.313921 0.039876 0.263790 -0.111001 -0.458645 1.121272 0.336100 -0.071553 0.213115 0.508143 -0.045410 -0.231836 0.106420 0.146619 -0.024347 0.219912 0.042387 0.108472 0.109077 -0.030366 0.116935 0.114614 0.111815 0.092294 0.065101 0.077340 0.041671 0.047424 0.134495 0.000180 0.079724 0.147028 0.045135 -0.030670 0.116575 0.012582 -0.046597 0.231287 0.055836 0.213178 0.139931 0.162270 0.247953 0.253544 -0.129282 -0.165210 -0.195412 0.337610 0.152466 0.100233 0.206522 0.095660 -0.403341 0.307927 0.123385 -0.050167 0.165374 0.273649 0.055036 0.004941 0.034015 0.028993 0.038800 0.055318 0.051450 0.050819 0.047812 0.045609 0.057560 0.037787 0.016340 0.010183 0.012004 0.029633 0.047856 0.046748 0.014743 0.000967 0.001215 0.000428 0.000565 0.000277 0.000165 0.000092 0.028968 0.027553 0.026523 0.025554 0.033223 0.016881 0.016074 0.026971 0.033838 0.015633 0.023569 0.018797 0.032749 0.03-4167 0.025265 0.040859 0.001012 0.027087 0.014958 0.029668 0.017326 0.015180 0.007476 0.007240 23 Requirements