5. Go to Yahoo Finance and obtain the monthly adjusted-closing prices for General Motors (GM) and the S&P500 Index (GSPC) from 1st Jan 2016 to 1st Jan 2021 (both dates included). An Excel file with the raw data and workings should be submitted as supplementary materials for this question, in addition to the clear write-up in Word. [20 marks] a) Derive the monthly returns of GM and the Index. [lm] b) Assuming a monthly risk-free rate of 0.1%, create a column for the Excess monthly return for GM and another column for the Excess monthly return for the Index. [lm] c) Calculate the average monthly excess return and the standard deviation of the monthly excess returns for GM and the Index. [4m] d) Perform a Linear regression of the Excess monthly return for GM (dependent variable) and the Excess monthly return for the Index (independent variable).[lm] e) Highlight the cell in the Regression Output that shows the Beta of GM stock. [lm] f) Based on the Regression Output, is there alpha for GM stock? How do you know? [2m] g) From the ANOVA table output, calculate the (i) Systematic Variance, (ii) Unsystematic Variance and (iii) Total Variance of GM Stock? [6m] h) Using the values in (g), derive the R-square value for the regression (check that it matches the regression output!) [2m] i) Using the R-square, derive the Beta of GM (check that it matches the regression output!) [2m] 5. Go to Yahoo Finance and obtain the monthly adjusted-closing prices for General Motors (GM) and the S&P500 Index (GSPC) from 1st Jan 2016 to 1st Jan 2021 (both dates included). An Excel file with the raw data and workings should be submitted as supplementary materials for this question, in addition to the clear write-up in Word. [20 marks] a) Derive the monthly returns of GM and the Index. [lm] b) Assuming a monthly risk-free rate of 0.1%, create a column for the Excess monthly return for GM and another column for the Excess monthly return for the Index. [lm] c) Calculate the average monthly excess return and the standard deviation of the monthly excess returns for GM and the Index. [4m] d) Perform a Linear regression of the Excess monthly return for GM (dependent variable) and the Excess monthly return for the Index (independent variable).[lm] e) Highlight the cell in the Regression Output that shows the Beta of GM stock. [lm] f) Based on the Regression Output, is there alpha for GM stock? How do you know? [2m] g) From the ANOVA table output, calculate the (i) Systematic Variance, (ii) Unsystematic Variance and (iii) Total Variance of GM Stock? [6m] h) Using the values in (g), derive the R-square value for the regression (check that it matches the regression output!) [2m] i) Using the R-square, derive the Beta of GM (check that it matches the regression output!) [2m]