Question

Year	Average Stock Price	Year Open	Year Close
2020	294.2787	300.35	331.5
2019	208.2559	157.92	293.65
2018	189.0534	172.26	157.74
2017	150.5511	116.15	169.23
2016	104.604	105.35	115.82
2015	120.0385	109.33	105.26
2014	92.2646	79.0186	110.38
2013	67.5193	78.4329	80.1457
2012	82.2928	58.7471	76.0247
2011	52.0006	47.0814	57.8571
2010	37.1203	30.5729	46.08
2009	20.9736	12.9643	30.1046
2008	20.2827	27.8343	12.1929
2007	18.3249	11.9714	28.2971
2006	10.116	10.6786	12.12
2005	6.668	4.5207	10.27
2004	2.5376	1.52	4.6
2003	1.3245	1.0571	1.5264
2002	1.3671	1.6643	1.0236
2001	1.4442	1.0629	1.5643

• Use Excel to conduct a regression of the values of the security against the predictors and verify the validity of underlying assumptions
  • Check for homoscedasticity and serial correlation
  • If necessary, rerun the regression using robust standard errors
  • Look for evidence of multicollinearity and eliminate redundant predictors if necessary

Answer 1

For the given below data, we conduct regression analysis with Average stock price as response variable and taking Year Open and Year Close as independent predictors.

Year	Average Stock Price	Year Open	Year Close
2020	294.2787	300.35	331.5
2019	208.2559	157.92	293.65
2018	189.0534	172.26	157.74
2017	150.5511	116.15	169.23
2016	104.604	105.35	115.82
2015	120.0385	109.33	105.26
2014	92.2646	79.0186	110.38
2013	67.5193	78.4329	80.1457
2012	82.2928	58.7471	76.0247
2011	52.0006	47.0814	57.8571
2010	37.1203	30.5729	46.08
2009	20.9736	12.9643	30.1046
2008	20.2827	27.8343	12.1929
2007	18.3249	11.9714	28.2971
2006	10.116	10.6786	12.12
2005	6.668	4.5207	10.27
2004	2.5376	1.52	4.6
2003	1.3245	1.0571	1.5264
2002	1.3671	1.6643	1.0236
2001	1.4442	1.0629	1.5643

Using excel, the multiple regression output is given below.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9940
R Square	0.9880
Adjusted R Square	0.9865
Standard Error	9.5688
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	2	127773	63886.5	697.7369	<0.0001
Residual	17	1556.562	91.56245
Total	19	129329.6
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	2.3744	2.8760	0.8256	0.4205	-3.6935	8.4422
Year Open	0.6324	0.0903	7.0066	<0.0001	0.4420	0.8228
Year Close	0.3606	0.0735	4.9044	0.0001	0.2055	0.5158

We have adjusted R square 98.65%, implying that 98.65% variation is explained by independent predictors in response variable.

Now in order to see the problem of autocorrelation in the model, we plot year (time) versus residuals

Year	Residuals
2020	-17.5899
2019	0.110629
2018	20.85401
2017	13.69208
2016	-6.1633
2015	10.56259
2014	0.111229
2013	-13.36
2012	15.34907
2011	-1.01375
2010	-1.20674
2009	-0.45635
2008	-4.09141
2007	-1.82528
2006	-3.38251
2005	-2.26905
2004	-2.45697
2003	-2.26887
2002	-2.42894
2001	-2.16651

Since, Year versus Residuals show pattern, thus there is a serious problem of autocorrelation. Now we run the correlation matrix of independent predictors to identify redundant variables.

	Year Open	Year Close
Year Open	1
Year Close	0.949126	1

Since correlation between Year Open and Year Close is 0.949126, which is very high positive correlation. Thus, we need to remove one variable to ret rid of redundancy in the model.

Now we run again the regression model by removing redundant variable, the output is given below

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.985
R Square	0.971
Adjusted R Square	0.969
Standard Error	14.451
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	125570.7	125570.7	601.3112	2.78E-15
Residual	18	3758.905	208.8281
Total	19	129329.6
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	4.1366	4.3093	0.9599	0.3498	-4.9169	13.1902
Year Open	1.0525	0.0429	24.5216	<0.0001	0.9624	1.1427

We have adjusted R square 96.9%, implying that 96.9% variance is explained by independent predictor in case of dependent variable. We have Coefficient of Year Open as 1.0505, which is statistically significant with p-value<0.05. Unit change in Year Open will change Average Stock Price by 1.0505 times. 95% confidence intervals for Coefficient of Year Open is also reported as (0.9624, 1.1427).