Need Stats/Excel help! PLEASE ANSWER USING EXCEL W/SCREEN SHOTS AND FORMULAS THA
ID: 3060337 • Letter: N
Question
Need Stats/Excel help!
PLEASE ANSWER USING EXCEL W/SCREEN SHOTS AND FORMULAS
THANK YOU!!!
Student School_Ranking GPA Experience Salary 1 78 2.92 3 73,590 2 56 3.84 9 87,000 3 23 3.04 6 76,970 4 67 3.20 6 79,320 5 56 3.61 7 79,530 6 78 2.99 5 71,040 7 68 3.78 8 82,050 8 89 3.20 5 78,890 9 37 3.42 7 82,170 10 67 3.05 5 76,120 11 48 3.12 4 77,500 12 78 3.56 7 83,920 13 56 3.01 5 71,800 14 25 3.15 6 77,000 15 68 3.05 7 79,000 16 36 3.24 5 77,800 17 76 3.25 6 80,600 18 78 3.78 9 87,000 19 67 3.12 4 78,450 20 67 3.24 8 80,600 21 15 2.98 5 74,900 22 29 3.24 6 79,200 23 49 3.08 4 77,000 24 67 3.00 6 77,900 25 39 2.95 4 76,950 26 81 3.01 5 76,800 27 54 3.23 7 79,300 28 72 3.01 2 72,120 29 73 3.45 7 83,900 30 78 3.85 8 85,200 31 51 3.00 5 77,300 32 86 3.23 6 83,500 33 76 3.80 7 77,000 34 30 3.08 5 75,000 35 58 3.15 7 79,200 36 86 3.35 7 80,400 37 34 3.09 7 80,200 38 72 3.35 9 84,800 39 38 3.16 3 72,800 40 89 2.76 7 75,000 3. Develop a multiple regression equation for starting salaries using School Ranking, GPA and Experience as independent variables. Is this regression equation "good for use""? Explain.Explanation / Answer
First of all we model the equation using all the three independent variables i.e. School_Ranking and Experience. Please see below the excel output: -
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.840
R Square
0.705
Adjusted R Square
0.681
Standard Error
2242.363
Observations
40
ANOVA
df
SS
MS
F
Significance F
Regression
3
433447942.730
144482647.577
28.735
0.000
Residual
36
181014847.270
5028190.202
Total
39
614462790.000
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
53086.914
4627.483
11.472
0.000
43701.944
62471.884
School_Ranking
9.738
18.157
0.536
0.595
-27.086
46.561
GPA
5449.971
1721.276
3.166
0.003
1959.061
8940.880
Experience
1243.192
286.806
4.335
0.000
661.523
1824.861
As we can see based on the p-value, School_Ranking is not a significant variable. Hence, we would remove that and rerun and this would be our final model
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.84
R Square
0.70
Adjusted R Square
0.69
Standard Error
2220.67
Observations
40
ANOVA
df
SS
MS
F
Significance F
Regression
2
432001745.96
216000872.98
43.80
0.00
Residual
37
182461044.04
4931379.57
Total
39
614462790.00
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
53295.45
4566.51
11.67
0.00
44042.82
62548.08
GPA
5539.80
1696.54
3.27
0.00
2102.29
8977.30
Experience
1257.26
282.84
4.45
0.00
684.17
1830.35
We can see that the r^2 is 0.7 which means that about 70% of the variation in dependent variable is accounted for by the independent variables i.e. GPA and Experience. This is a moderate enough explanation of variation in the dependent variable by the independent variables.
r would be 0.8366.
F is significant (p-value less than 0.05) and hence we can conclude that atleast one of the independent variables is able to predict the variation in dependent variables
GPA: For every increase of 1 in GPA, Salaries would go up by 5539.80 keeping everything else constant
Experience: For every increase of 1 in Experience, Salaries would go up by 1257.26 keeping everything else constant
Intercept: Salaries would remain constant at 53295.45 even in absence of all other factors. This measure can be particularly useful in predicting starting salaries as it shows the figures in absence of all other factors.
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.840
R Square
0.705
Adjusted R Square
0.681
Standard Error
2242.363
Observations
40
ANOVA
df
SS
MS
F
Significance F
Regression
3
433447942.730
144482647.577
28.735
0.000
Residual
36
181014847.270
5028190.202
Total
39
614462790.000
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
53086.914
4627.483
11.472
0.000
43701.944
62471.884
School_Ranking
9.738
18.157
0.536
0.595
-27.086
46.561
GPA
5449.971
1721.276
3.166
0.003
1959.061
8940.880
Experience
1243.192
286.806
4.335
0.000
661.523
1824.861
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.