Please answer and show formulas used in excel! I have attached the data set that is being used for the questions as well. Thanks!

2. Download the file world returns.xls from the class page. The file provides average returns, standard deviations and the correlation matrix for the rates of return on the stock indexes of seven countries for the period 1980-1993. Assume that you were an analyst at International Capital Management in 1993, and assume that the anticipated expected returns for 1994 and the correlations between stocks are not expected to change, and are perfectly estimated from the historic returns. Answer the following questions: (a) Compute the variance, standard deviation and expected return of the equally weighted portfolio (that is, R= [i-1 Ri). (b) Find the variance and standard deviation of the efficient portfolio with an expected return of 16.5%. (c) Report the weights of US stocks and Germany's stocks in the efficient portfolios with expected returns 9, 10.5, 11, 12, 14, 15, 16, 17, 17.5, 18, 18.5, 21, 22, 26%. Notice that the weight of US stocks is higher than the weight of German stocks, despite the fact that German stocks offer the highest mean return and the highest Sharpe ratio. How can this be? Explain. (d) Derive (draw) the minimum variance frontier, and show on the same graph the position of the stocks of the seven countries. be UK oralla 1.00 0.53 C | D | E | F | G Annualized Standard Deviation, Average Return and Correlation coefficients of International Stocks, 1980-1993 Standard Average Country Deviation (%) Return (%) US 21.1 15.7 Germany 25.0 21.7 UK 23.5 18.3 Japan 26.6 17.3 Australia 27.6 14.8 Canada 23.4 10.5 France 26.6 17.2 Correlation matrix US Germany Japan Australia Canada France US 0.37 0.53 0.26 0.43 0.73 0.44 Germany 0.37 1.00 0.47 0.36 0.29 0.36 0.63 UK 0.47 1.00 0.43 0.50 0.54 0.51 Japan 0.26 0.36 0.43 1.00 0.26 0.29 0.42 Australia 0.43 0.29 0.50 0.26 1.00 0.56 0.34 Canada 0.73 0.36 0.54 0.29 0.56 1.00 0.39 20 France 0.44 0.63 0.51 0.42 0.34 0.39 1.00 21 Source: Roger G. Clarke and Mark P. Kritzman, Currency Management: Concepts and Practice, 22 Charlottesville, VA, Research Foundation of the Institute of Chartered Financial Analysts, 1996 Covariance Matrix: Cell Formulas US Germany UK Japan Australia Canada France 25 US b6*b6"b16 b7*b6*c16 b8"b6%d16b9*b6*e16 b10"b6*f16b11*b6*g16 b12*b6*h16 Germany b6*b7*b17 b7*b7*c17 b8*b7*d17b9*b7*e17 b10*b7*f17b11*b7*g17 b12*b7*h17 UK b6*b8*b18b7*b8*c18b8b8%d18b9*b8*e18 b10%b8*f18 b11*b8*g18 b12*b8*h18 Japan b6*b9*b19b7*b9*c19b8*b9*d19b9*b9*e19b10*b9*f19b11*b9*919 b12*b9*h19 Australia b6*b10*b20 b7*b10*c20b8*b10*d20 b9*b10*e20 b10*10*f20 b11*b10*320 b12*b10*h20 Canada b6 b11*b21b7b11ac21b8*b11*d21b9*b11*e21b10*b11*821 b11b11*g21 b12*b11*h21 France b6 b12b22b7b12"c22 b8*b12d22b9*b12*e22b10*b12"f22b11b12"g22 b12*b12*h22 Covariance Matrix: Results US Germany UK Japan Australia Canada France 445.21 195.18 262.80 145.93 250.41 360.43 246.95 Germany 195.18 625.00 276.13 239.40 200.10 210.60 418.95 UK 262.80 276.13 552.25 268.79 324.30 296.95 318.80 Japan 145.93 239.40 268.79 707.56 190.88 180.51 297.18 Australia 250.41 200.10 324.30 190.88 761.76 361.67 249.61 Canada 360.43 210.60 296.95 180.51 361.67 547.56 242.75 France 246.95 418.95 318.80 297.18 249.61 242.75 707.56 24 US 2. Download the file world returns.xls from the class page. The file provides average returns, standard deviations and the correlation matrix for the rates of return on the stock indexes of seven countries for the period 1980-1993. Assume that you were an analyst at International Capital Management in 1993, and assume that the anticipated expected returns for 1994 and the correlations between stocks are not expected to change, and are perfectly estimated from the historic returns. Answer the following questions: (a) Compute the variance, standard deviation and expected return of the equally weighted portfolio (that is, R= [i-1 Ri). (b) Find the variance and standard deviation of the efficient portfolio with an expected return of 16.5%. (c) Report the weights of US stocks and Germany's stocks in the efficient portfolios with expected returns 9, 10.5, 11, 12, 14, 15, 16, 17, 17.5, 18, 18.5, 21, 22, 26%. Notice that the weight of US stocks is higher than the weight of German stocks, despite the fact that German stocks offer the highest mean return and the highest Sharpe ratio. How can this be? Explain. (d) Derive (draw) the minimum variance frontier, and show on the same graph the position of the stocks of the seven countries. be UK oralla 1.00 0.53 C | D | E | F | G Annualized Standard Deviation, Average Return and Correlation coefficients of International Stocks, 1980-1993 Standard Average Country Deviation (%) Return (%) US 21.1 15.7 Germany 25.0 21.7 UK 23.5 18.3 Japan 26.6 17.3 Australia 27.6 14.8 Canada 23.4 10.5 France 26.6 17.2 Correlation matrix US Germany Japan Australia Canada France US 0.37 0.53 0.26 0.43 0.73 0.44 Germany 0.37 1.00 0.47 0.36 0.29 0.36 0.63 UK 0.47 1.00 0.43 0.50 0.54 0.51 Japan 0.26 0.36 0.43 1.00 0.26 0.29 0.42 Australia 0.43 0.29 0.50 0.26 1.00 0.56 0.34 Canada 0.73 0.36 0.54 0.29 0.56 1.00 0.39 20 France 0.44 0.63 0.51 0.42 0.34 0.39 1.00 21 Source: Roger G. Clarke and Mark P. Kritzman, Currency Management: Concepts and Practice, 22 Charlottesville, VA, Research Foundation of the Institute of Chartered Financial Analysts, 1996 Covariance Matrix: Cell Formulas US Germany UK Japan Australia Canada France 25 US b6*b6"b16 b7*b6*c16 b8"b6%d16b9*b6*e16 b10"b6*f16b11*b6*g16 b12*b6*h16 Germany b6*b7*b17 b7*b7*c17 b8*b7*d17b9*b7*e17 b10*b7*f17b11*b7*g17 b12*b7*h17 UK b6*b8*b18b7*b8*c18b8b8%d18b9*b8*e18 b10%b8*f18 b11*b8*g18 b12*b8*h18 Japan b6*b9*b19b7*b9*c19b8*b9*d19b9*b9*e19b10*b9*f19b11*b9*919 b12*b9*h19 Australia b6*b10*b20 b7*b10*c20b8*b10*d20 b9*b10*e20 b10*10*f20 b11*b10*320 b12*b10*h20 Canada b6 b11*b21b7b11ac21b8*b11*d21b9*b11*e21b10*b11*821 b11b11*g21 b12*b11*h21 France b6 b12b22b7b12"c22 b8*b12d22b9*b12*e22b10*b12"f22b11b12"g22 b12*b12*h22 Covariance Matrix: Results US Germany UK Japan Australia Canada France 445.21 195.18 262.80 145.93 250.41 360.43 246.95 Germany 195.18 625.00 276.13 239.40 200.10 210.60 418.95 UK 262.80 276.13 552.25 268.79 324.30 296.95 318.80 Japan 145.93 239.40 268.79 707.56 190.88 180.51 297.18 Australia 250.41 200.10 324.30 190.88 761.76 361.67 249.61 Canada 360.43 210.60 296.95 180.51 361.67 547.56 242.75 France 246.95 418.95 318.80 297.18 249.61 242.75 707.56 24 US