Please answer only part d, e, f. Thanks

You are a financial advisor and your manager asked you to evaluate the following securities: a treasury bill, a market portfolio Index), a Utility Company stock, a High-Tech Company stock and a Counter-cyclical company stock. Therefore, you have collected annual prices of each of them and calculated the realized returns for 5 years. Realized returns data are included in Table 1 below. Year 2001 2002 2003 2004 2005 Treasury Market Utility bill Portfolio Company 4% -2% 6% 4% 5% 7% 4% 10% 9% 4% 15% 11% 4% 25% 14% High-Tech Company -5% 2% 15% 25% 45% Counter- cyclical company 26% 16% 12% -9% -20% a) Calculate the excess return of each realized return for each security in Table 1. (3 marks) b) Use the excess returns in part a) to calculate the historical average excess returns and volatilities of each security. Briefly comment on the results. (You make the assumption that each historical average excess return is a good proxy of the expected return of each security). (7 marks) C) Now using the information in part b), calculate the beta of each class of security. Briefly interpret the results. [Hint: you have to calculate also the covariances to get beta). (7 marks) d) Calculate the required rate of return of each security using the following: beta * average excess market return. Given all the information above, plot the SML. i. What does it represent? (3 marks) ii. What is the difference with the CML? (3 marks) e) Indicate the securities that are undervalued and those overvalued and explain why. (7 marks) f) Briefly discuss the ways to better diversify a portfolio. (5 marks) You are a financial advisor and your manager asked you to evaluate the following securities: a treasury bill, a market portfolio Index), a Utility Company stock, a High-Tech Company stock and a Counter-cyclical company stock. Therefore, you have collected annual prices of each of them and calculated the realized returns for 5 years. Realized returns data are included in Table 1 below. Year 2001 2002 2003 2004 2005 Treasury Market Utility bill Portfolio Company 4% -2% 6% 4% 5% 7% 4% 10% 9% 4% 15% 11% 4% 25% 14% High-Tech Company -5% 2% 15% 25% 45% Counter- cyclical company 26% 16% 12% -9% -20% a) Calculate the excess return of each realized return for each security in Table 1. (3 marks) b) Use the excess returns in part a) to calculate the historical average excess returns and volatilities of each security. Briefly comment on the results. (You make the assumption that each historical average excess return is a good proxy of the expected return of each security). (7 marks) C) Now using the information in part b), calculate the beta of each class of security. Briefly interpret the results. [Hint: you have to calculate also the covariances to get beta). (7 marks) d) Calculate the required rate of return of each security using the following: beta * average excess market return. Given all the information above, plot the SML. i. What does it represent? (3 marks) ii. What is the difference with the CML? (3 marks) e) Indicate the securities that are undervalued and those overvalued and explain why. (7 marks) f) Briefly discuss the ways to better diversify a portfolio