You work as a junior portfolio manager position with ABC Capital. As part of the normal portfolio review process, the company provides a list of 20 possible investments. You must select at least 6, but not more than 12 investments to create a fully asset-allocated portfolio that will have the characteristics of an 8% expected return, a beta of 0.5 or less, a correlation of less than 60% to the stock market. You will create the portfolio and present it to the company, explaining: Show the calculations for achieving required characteristics (expected return) Calculations for the risk-adjusted rate of return (including Sharpe Ratio and Treynor Ratio) Show your calculations in this spreadsheet. Enter your final answers in the blue area below. 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 A B Investment 1 Electric Company 2 Telephone Company 3 Chemical Company 4 IT Company 5 Airline Company 6 Healthcare Company 7 The Bank 8 Oil Drillers 9 Food Company 10 Car Company 11 High Yield Bond 12 Corporate Bond 13 Treasury Bond 14 Municipal Bond 15 Real Estate 16 Managed Futures 17 Agriculture 18 Oil 19 Gold 20 Cash C E F Beta Standard Deviation Correlation Dividend Yield 4% 5% 2% 3% 1% 2% 2% 3% 3% 5% 0.02 0.51 1.5 3.18 1.2 2.62 2.89 4.09 1.04 1.3 0.7 0.55 0.33 0.4 2.1 0.08 0.7 1.96 0.42 0 D 8% 12% 18% 20% 18% 24% 20% 20% 15% 18% 11% 4% 3% 5% 12% 5% 18% 36% 19% 0% 1 1 1 1 1 1 1 1 1 1 0.7 -0.31 -0.28 -0.06 0.75 -0.05 0.124 0.5 0.04 0 4% G Coupon 5% 3% 1% 3% 0.25% H Actual Return 2.35% 6.85% 7.39% 29.96% 10.17% 26.92% 20.42% 30.56% 6.06% 4.06% 6.12% 4.17% 2.75% 5.64% 13.04% 1.62% 3.76% 10.31% 5.20% 1.77% 1 2 3 4 5 6 7 8 9 10 11 12 Your Portfolio Investment Portfolio Beta Standard Deviation Correlation Sharpe Ratio Treynor Ratio Weighting K Expected Return Instructions: 1) Copy and paste the name, beta, standard deviation, and correlation for each selected investment. You should have at least 6 listed, but no more than 12. 2) Calculate the Sharpe Ratio, Treynor Ratio, Weight, and Expected Return for each investment. 3) Finally, calculate the required numbers for the entire portfolio.