Question

Please show the way by Excel Data Analysis Tool

1. Mike Wilde is president of the teachers’ union for Otsego School District. In preparing for upcoming negotiations, he is investigating the salary structure of classroom teachers in the district. He believes there are three factors that affect a teacher’s salary: years of experience, a teaching effectiveness rating given by the principal, and whether the teacher has a master’s degree. A random sample of 20 teachers resulted in the FILE4.
   1. Develop a correlation matrix. Which independent variable has the strongest correlation with the dependent variable?
   2. Determine the regression equation. What salary would you estimate for a teacher with 5 years’ experience, a rating by the principal of 60, and no master’s degree?
   3. Conduct a global test of hypothesis to determine whether any of the regression coefficients differ from zero. Use the .05 significance level.
   4. Conduct a test of hypothesis for the individual regression coefficients. Would you consider deleting any of the independent variables? Use the .05 significance level.
   5. If your conclusion in part (d) was to delete one or more independent variables, run the analysis again without those variables

Salary	Years	Rating	Masters
31,1	8	35	0
33,6	5	43	0
29,3	2	51	1
43	15	60	1
38,6	11	73	0
45	14	80	1
42	9	76	0
36,8	7	54	1
48,6	22	55	1
31,7	3	90	1
25,7	1	30	0
30,6	5	44	0
51,8	23	84	1
46,7	17	76	0
38,4	12	68	1
33,6	14	25	0
41,8	8	90	1
30,7	4	62	0
32,8	2	80	1
42,8	8	72	0

Answer 1

a. Correlation matrix:

	Years	Rating	Masters
Salary	0.867597028	0.547196687	0.310558

"Years" has the strongest correlation with "Salary".

b.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.952959718
R Square	0.908132225
Adjusted R Square	0.890907017
Standard Error	2.389667843
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	3	903.1938016	301.0646	52.72111845	1.6231E-08
Residual	16	91.36819837	5.710512
Total	19	994.562
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	19.91519148	1.916260316	10.39274	1.60294E-08	15.8529011	23.97748188
X Variable 1	0.899379367	0.087678764	10.25766	1.92579E-08	0.71350869	1.085250043
X Variable 2	0.153916002	0.031440644	4.895447	0.000161689	0.08726481	0.22056719
X Variable 3	-0.66730799	1.213932825	-0.54971	0.590111376	-3.2407306	1.906114638

X Variable 1=Years, X Variable 2=Rating, X Variable 3=Masters.

Let = Salary, Xi = Year, X2 = Rating, X3-Masters Y 19.91520.8994X1 0.1539X2 0.667313 If Xi = 5, X2 = 60, X3 = 0 then Y-19.9 152 + 0.8994 * 5 + 0.1539 * 60-0.6673 * 0 33.6462 33.6 c. From ANOVA table, p-value 0.05 hence the regression equation is signi ficant i.e. at least one regression coef ficient differs from zero d. The variable "Masters" can be deleted since its 0.5901 > 0.05. p-value e,

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.952048946
R Square	0.906397196
Adjusted R Square	0.895385102
Standard Error	2.340107986
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	2	901.4682084	450.7341	82.3092458	1.80283E-09
Residual	17	93.09379156	5.476105
Total	19	994.562
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	20.11570487	1.842208157	10.91934	4.20253E-09	16.2289854	24.00242434
X Variable 1	0.892648174	0.085018925	10.49941	7.54395E-09	0.713273922	1.072022426
X Variable 2	0.146380408	0.02770797	5.282971	6.08835E-05	0.087921701	0.204839115

Then 20.1 157 y 0.8926Xi + 0. 1464X2