Question

Research into the relationship between hours of study and grades shows widely different conclusions. A recent survey of graduates who wrote the Graduate Management Admissions Test (GMAT) had the following results.

Hours

Studied Average

(Midpoint) Score

40 210

50 300

65 345

75 455

85 540

105 660

95 700

1. Run the regression analysis in Excel on this data. Include your output with your answer. (Note: You may calculate by hand if you prefer).

2. What is the regression equation for this relationship?

3. Use the regression equation to predict the average score for each category of hours studied.

4. Plot the original data and the regression line on a scattergram. (You may use Excel).

5. How accurate is this regression at predicting GMAT scores based on hours studied? Explain.

6. Use the t statistic to determine whether the Correlation Coefficient is “significant” at the 95% confidence level.

Answer 1

Run the regression analysis in Excel on this data. Include your output with your answer. (Note: You may calculate by hand if you prefer).

The regression analysis in Excel is:

Hours Studied	Average Score
40	210
50	300
65	345
75	455
85	540
105	660
95	700
	r²	0.948
	r	0.974
	Std. Error	46.083
	n	7
	k	1
	Dep. Var.	Average Score
ANOVA table
Source	SS	df	MS	F	p-value
Regression	1,94,717	1	1,94,717	91.69	.0002
Residual	10,618	5	2,124
Total	2,05,336	6
Regression output				confidence interval
variables	coefficients	std. error	t (df=5)	p-value	95% lower	95% upper
Intercept	-104
Hours Studied	8	0.7979	9.575	.0002	5.5892	9.6913

1. What is the regression equation for this relationship?

y = -104 + 8*x

1. Use the regression equation to predict the average score for each category of hours studied.

Hours Studied	Predicted
40	202.077
50	278.480
65	393.084
75	469.486
85	545.889
105	698.694
95	622.291

1. Plot the original data and the regression line on a scattergram. (You may use Excel).

1. How accurate is this regression at predicting GMAT scores based on hours studied? Explain.

94.8% of the variability in the model is explained. So, the model is accurate.

1. Use the t statistic to determine whether the Correlation Coefficient is “significant” at the 95% confidence level.

The t statistic = 9.575

Please give me a thumbs-up if this helps you out. Thank you!