solve everything step by step without using excel

Question 12 (answer all parts) You are asked to compare the following securities: a treasury bill, FTSE100 (market portfolio), JBond PLC (a utility company stock), Skyfall PLC (a high-tech company stock) and Thunderball PLC (a counter-cyclical company stock). You have collected annual prices of each of them and calculated the realized returns for 6 years. Realized returns data are included in Table 1 below. year Treasury bill FTSE100 Skyfall PLC 2014 2015 2016 2017 2018 2019 2% 2% 1% 1% 1% 1% -10% 1% 3% 9% 14% 13% JBond PLC 5% 7% 9% 10% 12% 7% -5% 3% 9% 20% 16% 14% Thunderball PLC 16% 5% 3% -10% -16% -11% a) Calculate: i. the excess return of each realized return for each security in Table 1; (2 marks) ] ii. the historical average excess returns and volatilities of each security, using the excess returns in part ai). Briefly comment on the results (6 marks) b) Assuming that each historical average excess return is a good proxy of the expected return of each security, using the information found in parts a), now calculate the beta of each security. Briefly interpret the results. (6 marks) c) Once you have calculated the required rate of return of each security using the formula "beta x average excess return" with the information in part b), indicate whether each security is undervalued or overvalued and explain why. (6 marks) d) Briefly discuss the ways you know to diversify a portfolio. (6 marks) e) As for the asset pricing models: i. briefly describe the CAPM assumptions; (4 marks) ii. describe what each of the following pairs of asset pricing models has in common, and how they differ: - CAPM and APT; - CAPM and F-F-C (Fama-French-Carhart) model. (5 marks) (TOTAL 35 marks) Question 12 (answer all parts) You are asked to compare the following securities: a treasury bill, FTSE100 (market portfolio), JBond PLC (a utility company stock), Skyfall PLC (a high-tech company stock) and Thunderball PLC (a counter-cyclical company stock). You have collected annual prices of each of them and calculated the realized returns for 6 years. Realized returns data are included in Table 1 below. year Treasury bill FTSE100 Skyfall PLC 2014 2015 2016 2017 2018 2019 2% 2% 1% 1% 1% 1% -10% 1% 3% 9% 14% 13% JBond PLC 5% 7% 9% 10% 12% 7% -5% 3% 9% 20% 16% 14% Thunderball PLC 16% 5% 3% -10% -16% -11% a) Calculate: i. the excess return of each realized return for each security in Table 1; (2 marks) ] ii. the historical average excess returns and volatilities of each security, using the excess returns in part ai). Briefly comment on the results (6 marks) b) Assuming that each historical average excess return is a good proxy of the expected return of each security, using the information found in parts a), now calculate the beta of each security. Briefly interpret the results. (6 marks) c) Once you have calculated the required rate of return of each security using the formula "beta x average excess return" with the information in part b), indicate whether each security is undervalued or overvalued and explain why. (6 marks) d) Briefly discuss the ways you know to diversify a portfolio. (6 marks) e) As for the asset pricing models: i. briefly describe the CAPM assumptions; (4 marks) ii. describe what each of the following pairs of asset pricing models has in common, and how they differ: - CAPM and APT; - CAPM and F-F-C (Fama-French-Carhart) model. (5 marks) (TOTAL 35 marks)