ONLY USE EXCEL FUNCTIONS

8. Refer to the table below: End of month Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Rates of return Share index Share A Risk-free asset -1.710% 4.786% 0.442% 3.337% -0.627% 0.436% 0.023% 0.076% 0.452% 1.799% -0.799% 0.413% 0.141% 0.316% 0.450% 0.028% 3.806% 0.449% -2.544% 4.211% 0.384% -2.105% -4.441% 0.445% -4.611% 4.062% 0.387% -3.200% 2.082% 0.453% -6.108% 1.839% 0.366% 7.443% 0.132% 0.308% (a) Calculate the monthly excess returns of the Share index and Share A. (2 marks) (b) Add a graph of the excess returns of Share A on the vertical axis (Y axis) and the Share index on the horizontal axis (X axis). On this graph, plot the linear regression line and display the regression equation and the R-squared. (2 marks) (c) Using appropriate Excel functions to calculate the Alpha, Beta and R-squared of the regression of the excess returns of the Share index on the excess returns of Share A. Provide a brief explanation on what these quantities measure. (3 marks) 8. Refer to the table below: End of month Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Rates of return Share index Share A Risk-free asset -1.710% 4.786% 0.442% 3.337% -0.627% 0.436% 0.023% 0.076% 0.452% 1.799% -0.799% 0.413% 0.141% 0.316% 0.450% 0.028% 3.806% 0.449% -2.544% 4.211% 0.384% -2.105% -4.441% 0.445% -4.611% 4.062% 0.387% -3.200% 2.082% 0.453% -6.108% 1.839% 0.366% 7.443% 0.132% 0.308% (a) Calculate the monthly excess returns of the Share index and Share A. (2 marks) (b) Add a graph of the excess returns of Share A on the vertical axis (Y axis) and the Share index on the horizontal axis (X axis). On this graph, plot the linear regression line and display the regression equation and the R-squared. (2 marks) (c) Using appropriate Excel functions to calculate the Alpha, Beta and R-squared of the regression of the excess returns of the Share index on the excess returns of Share A. Provide a brief explanation on what these quantities measure