Question

What data is selected to in excel to calculate MAD for part b?

The graduate assistant for the Santa Clara Broncos football team has compiled the following statistics: Year wins avg offensive yards avg interceptions 1 10 500 2 2 8 450 4 3 5 250 10 4 10 485 1 5 7 399 4 6 11 521 2 7 4 158 15 8 11 525 0 9 8 485 4 10 4 300 9 11 5 350 9 12 7 375 9 13 2 150 15 14 5 380 5 a. Using Excel, develop a linear regression model for wins, employing average offensive yards and average interceptions. b. Calculate the MAD for your model. c. Calculate R, F, and t statistics and interpret them.

Answer 1

a)

Excel > Data > Data Analysis > Regression

Excel output :

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.924900573
R Square	0.855441069
Adjusted R Square	0.829157627
Standard Error	1.185422056
Observations	14
ANOVA
	df	SS	MS	F	Significance F
Regression	2	91.47109148	45.73554574	32.54676731	2.4002E-05
Residual	11	15.45747995	1.40522545
Total	13	106.9285714
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0.268468656	5.412325642	0.049603197	0.961327822	-11.64397976	12.18091707	-11.64397976	12.18091707
avg offensive yards	0.018557998	0.00997101	1.861195309	0.089633626	-0.003388048	0.040504044	-0.003388048	0.040504044
avg interceptions	-0.063321046	0.259241663	-0.244254897	0.811529458	-0.633908099	0.507266007	-0.633908099	0.507266007

Wins =  0.2685 + 0.01856 * avg offensive yards - 0.0633 * avg interceptions

b)

Observation	Predicted wins	Residuals (yi-yexp)	ABS(Res)
1	9.420825428	0.579174572	0.579174572
2	8.36628345	-0.36628345	0.36628345
3	4.27475763	0.72524237	0.72524237
4	9.205776508	0.794223492	0.794223492
5	7.419825566	-0.419825566	0.419825566
6	9.81054338	1.18945662	1.18945662
7	2.250816611	1.749183389	1.749183389
8	10.01141746	0.988582537	0.988582537
9	9.01581337	-1.01581337	1.01581337
10	5.265978562	-1.265978562	1.265978562
11	6.193878449	-1.193878449	1.193878449
12	6.657828392	0.342171608	0.342171608
13	2.102352629	-0.102352629	0.102352629
14	7.003902563	-2.003902563	2.003902563
		Average	0.909719227

MAD = 0.9097

c)

From excel output table :

R = 0.9249 (Strong, Positive correlation)

If independent variable increases then Y variable increases vice versa

R^2 = 0.8554

85.54% variation in dependent variable is explained by set of independent variables

or

85.54% variation in dependent variable is explained by Regression model

F = 32.5468

P value = 0 < 0.05, Significant (at least one coefficient is different than 0)

	t Stat	P-value	alpha 0.05	Significant
Intercept	0.049603	0.961328	> alpha	No
avg offensive yards	1.861195	0.089634	> alpha	No
avg interceptions	-0.24425	0.811529	> alpha	No