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 ( Midpoint)	Average Score
40	220
50	310
65	350
75	440
85	560
105	670
95	700

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

b) What is the regression equation for this relationship?

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

d) Plot the original data and the regression line on a scatter gram. (You may use Excel).

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

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

Answer 1

a)

x	y	(x-x̅)²	(y-ȳ)²	(x-x̅)(y-ȳ)
40	220	1127.04	59675.51	8201.02
50	310	555.61	23804.08	3636.73
65	350	73.47	13061.22	979.59
75	440	2.04	589.80	-34.69
85	560	130.61	9161.22	1093.88
105	670	987.76	42318.37	6465.31
95	700	459.18	55561.22	5051.02

	ΣX	ΣY	Σ(x-x̅)²	Σ(y-ȳ)²	Σ(x-x̅)(y-ȳ)
total sum	515	3250	3335.714286	204171.4	25392.86
mean	73.57	464.29	SSxx	SSyy	SSxy

sample size ,   n =   7          
here, x̅ = Σx / n=   73.57   ,     ȳ = Σy/n =   464.29  
                  
SSxx =    Σ(x-x̅)² =    3335.7143          
SSxy=   Σ(x-x̅)(y-ȳ) =   25392.9          
                  
estimated slope , ß1 = SSxy/SSxx =   25392.9   /   3335.714   =   7.6124
                  
intercept,   ß0 = y̅-ß1* x̄ =   -95.7709          
                  
b) so, regression line is   Ŷ =   -95.7709   +   7.6124   *x

c)

Ŷ
208.726
284.850
399.036
475.161
551.285
703.533
627.409

d)

e)

SSE=   (SSxx * SSyy - SS²xy)/SSxx =    10870.343
      
std error ,Se =    √(SSE/(n-2)) =    46.627
      
correlation coefficient ,    r = Sxy/√(Sx.Sy) =   0.9730
      
R² =    (Sxy)²/(Sx.Sy) =    0.9468

Very accurate model as R square is high.

f)

Ho:   ρ = 0  
Ha:   ρ ╪ 0  
n=   7  
alpha,α =    0.05  
correlation , r=   0.9730  
t-test statistic = r*√(n-2)/√(1-r²) =        9.429
  critical t-value =    2.5706  
Decison:   t value >t critical , So, Reject Ho  

Hence R is significant.

Please revert in case of any doubt.

Please upvote. Thanks in advance