Question

Student ID	Student ACT Score  (Independent Variable)	Student            GPA     (Dependent Variable)
1	24	3.25
2	21	2.87
3	18	2.66
4	22	3.33
5	22	2.87
6	22	3.21
7	18	2.76
8	28	3.91
9	29	3.55
10	18	2.55
11	20	2.44
12	24	3.22
13	25	3.22
14	24	3.44
15	21	3.01
Instructions:  Generate  on a separate worksheet a standard set of SUMMARY OUTPUT for two-variable regression, and label the worksheet "Simple Regression Output".  Then, using the information in this output 1) indicate whether there is a significant relationship between students' ACT scores and their GPAs, and 2) show the general prediction line (i.e., regression equation between these two variables), and 3) use this prediction line to compute the predicted GPA with an ACT score of 20. THIS NEEDS TO BE DONE IN EXCEL. PLEASE SHOW HOW TO DO IN EXCEL

Answer 1

Solution:

The regression analysis is done based on the given data and the screenshot of it is shared for your understanding.

Step 1: Enter the given data in cell A1 and B1.

Go to Data->Data analysis and select Regression as data analysis tool.Click OK.

Step 2: Select GPA as 'Input Y Range' since it is dependent variable and Student ACT score as 'Input X Range' since it is independent variable.

Click on Labels since there are label names in first row.

Select any output range to display the regression summary, then click OK.

Step 3: The regression summary is as follows.

Regression Statistics
Multiple R	0.881977
R Square	0.777883
Adjusted R Square	0.759373
Standard Error	0.202708
Observations	14

ANOVA
	df	SS	MS	F	Significance F
Regression	1	1.72685568	1.726856	42.02557	3.01194E-05
Residual	12	0.493087177	0.041091
Total	13	2.219942857

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0.720923	0.367041611	1.964144	0.073102	-0.078792461	1.520637479	-0.078792461	1.520637479
24	0.1056	0.01628942	6.482713	3.01E-05	0.070108035	0.141091227	0.070108035	0.141091227

1) Indicate whether there is a significant relationship between students' ACT scores and their GPAs.

From the above summary, we have F-value=42.02 which is greater than the F-critical value=0.00003011, we reject the null hypothesis and conclude that there is is a significant relationship between students' ACT scores and their GPA.

2) show the general prediction line (i.e.., regression equation between these two variables)

	Coefficients
Intercept	0.720923
24	0.1056

Using the above result,the regression equation is y=0.72+0.1056x

3) use this prediction line to compute the predicted GPA with an ACT score of 20.

Here x=20, so y=0.72+0.1056x

y=0.72+0.1056*20

y=2.832