Homework SourseA Large school district is reevaluating its teachers salarie
A Large school district is reevaluating its teachers’ salaries. They have decided to use regression analysis to predict mean teacher salaries at each high school. The researcher uses years of experience and subject matter (Math, Science, English, Other) to predict salary. The raw data is given in the table below
Salary ($)
Years Experience
Subject
$24,265
8
English
$27,140
5
English
$22,195
2
Math
$37,950
15
Science
$32,890
11
Other
$40,250
14
Math
$36,800
9
Math
$30,820
6
Science
$44,390
21
Math
$24,955
2
Science
$18,055
1
English
$23,690
7
English
$48,070
20
Science
$42,205
16
Math
$24,265
6
Other
$27,140
8
Other
$36,875
7
Math
$37,950
9
Math
$32,890
1
English
$40,250
4
Science
Use Excel to analyze the data, print out the summary output and attach it to your submission
Use the following: Y = Dependent variable; X1 = Years of experience; X2 = Math;
X3 = Science; X4 = English.
a)What is the prediction equation?
b)What is the correlation coefficient?
c)Is the overall model significant? Why?
d)What is the coefficient of determination?
e)What is the MSE?
f)List the p-value of each independent variable and say whether it is significant to the model or not. Use a 0.05 level of significance.
g)What is the predicted mean salary for a gym teacher with 10 years’ experience?
| Salary ($) | Years Experience | Subject |
| $24,265 | 8 | English |
| $27,140 | 5 | English |
| $22,195 | 2 | Math |
| $37,950 | 15 | Science |
| $32,890 | 11 | Other |
| $40,250 | 14 | Math |
| $36,800 | 9 | Math |
| $30,820 | 6 | Science |
| $44,390 | 21 | Math |
| $24,955 | 2 | Science |
| $18,055 | 1 | English |
| $23,690 | 7 | English |
| $48,070 | 20 | Science |
| $42,205 | 16 | Math |
| $24,265 | 6 | Other |
| $27,140 | 8 | Other |
| $36,875 | 7 | Math |
| $37,950 | 9 | Math |
| $32,890 | 1 | English |
| $40,250 | 4 | Science |
Solution
Large school district is reevaluating its teachers’ salaries. They have decided to use regression analysis to predict mean teacher salaries at each high school. The researcher uses years of experience and subject matter (Math, Science, English, Other) to predict salary. The raw data is given in the table below
Salary ($)
Years ExperienceX1
MathX2
ScienceX3
EngX4
Subject
$24,265
8
0
0
1
English
$27,140
5
0
0
1
English
$22,195
2
1
0
0
Math
$37,950
15
0
1
0
Science
$32,890
11
0
0
0
Other
$40,250
14
1
0
0
Math
$36,800
9
1
0
0
Math
$30,820
6
0
1
0
Science
$44,390
21
1
0
0
Math
$24,955
2
0
1
0
Science
$18,055
1
0
0
1
English
$23,690
7
0
0
1
English
$48,070
20
0
1
0
Science
$42,205
16
1
0
0
Math
$24,265
6
0
0
0
Other
$27,140
8
0
0
0
Other
$36,875
7
1
0
0
Math
$37,950
9
1
0
0
Math
$32,890
1
0
0
1
English
$40,250
4
0
1
0
Science
Use Excel to analyze the data, print out the summary output and attach it to your submission
Regression Analysis
R²
0.726
Adjusted R²
0.652
n
20
R
0.852
k
4
Std. Error
4951.369
Dep. Var.
Salary ($)
ANOVA table
Source
SS
df
MS
F
p-value
Regression
972,550,694.3179
4
243,137,673.5795
9.92
.0004
Residual
367,740,779.4321
15
24,516,051.9621
Total
1,340,291,473.7500
19
Regression output
confidence interval
variables
coefficients
std. error
t (df=15)
p-value
95% lower
95% upper
Intercept
20,747.1002
3,370.5610
6.155
1.84E-05
13,562.9195
27,931.2808
Years ExperienceX1
882.1480
214.2820
4.117
.0009
425.4168
1,338.8792
MathX2
6,661.1081
3,469.4021
1.920
.0741
-733.7475
14,055.9636
ScienceX3
7,369.7088
3,623.1852
2.034
.0600
-352.9276
15,092.3452
EngX4
579.4487
3,712.8979
0.156
.8781
-7,334.4059
8,493.3033
Predicted values for: Salary ($)
95% Confidence Interval
95% Prediction Interval
Years ExperienceX1
MathX2
ScienceX3
EngX4
Predicted
lower
upper
lower
upper
Leverage
10
0
0
0
29,568.580
23,428.095
35,709.065
17,358.589
41,778.571
0.339
Use the following: Y = Dependent variable; X1 = Years of experience; X2 = Math;
X3 = Science; X4 = English.
a)What is the prediction equation?
Salary = 20,747.1002+882.1480*x1+6,661.1081*x2+7,369.7088*x3+579.4487*x4
b)What is the correlation coefficient?
r = 0.852
c)Is the overall model significant? Why?
The overall model is significant, F=9.92, P=0.0004 which is less than 0.05
d)What is the coefficient of determination?
coefficient of determination =0.726
e)What is the MSE?
MSE= 24,516,051.9621
f)List the p-value of each independent variable and say whether it is significant to the model or not. Use a 0.05 level of significance.
For x1, P=0.0009, significant
For x2, P=0.0741, not significant
For x3, P=0.0600, not significant
For x4, P=0.8781, not significant
g)What is the predicted mean salary for a gym teacher with 10 years’ experience?
Salary = 20,747.1002+882.1480*10+6,661.1081*0+7,369.7088*0+579.4487*0
= $29568.58
| Salary ($) | Years ExperienceX1 | MathX2 | ScienceX3 | EngX4 | Subject |
| $24,265 | 8 | 0 | 0 | 1 | English |
| $27,140 | 5 | 0 | 0 | 1 | English |
| $22,195 | 2 | 1 | 0 | 0 | Math |
| $37,950 | 15 | 0 | 1 | 0 | Science |
| $32,890 | 11 | 0 | 0 | 0 | Other |
| $40,250 | 14 | 1 | 0 | 0 | Math |
| $36,800 | 9 | 1 | 0 | 0 | Math |
| $30,820 | 6 | 0 | 1 | 0 | Science |
| $44,390 | 21 | 1 | 0 | 0 | Math |
| $24,955 | 2 | 0 | 1 | 0 | Science |
| $18,055 | 1 | 0 | 0 | 1 | English |
| $23,690 | 7 | 0 | 0 | 1 | English |
| $48,070 | 20 | 0 | 1 | 0 | Science |
| $42,205 | 16 | 1 | 0 | 0 | Math |
| $24,265 | 6 | 0 | 0 | 0 | Other |
| $27,140 | 8 | 0 | 0 | 0 | Other |
| $36,875 | 7 | 1 | 0 | 0 | Math |
| $37,950 | 9 | 1 | 0 | 0 | Math |
| $32,890 | 1 | 0 | 0 | 1 | English |
| $40,250 | 4 | 0 | 1 | 0 | Science |
