Question

IN A EXCEL FILE USING EXCEL FORMULAS AND CALCULATIONS MUST SHOE FORMULAS IN CELL! SHOW THE STEPS IN SOLUTION APPROACH.

1. Refer to the Johnson Filtration problem introduced in this section. Suppose that in addition to information on the number of months since the machine was serviced and whether a mechanical or an electrical repair was necessary, the managers obtained a list showing which repairperson performed the service. The revised data follow.

Repair Time in Hours	Months Since Last Service	Type of Repair	Repairperson
2.9	2	Electrical	Dave Newton
3	6	Mechanical	Dave Newton
4.8	8	Electrical	Bob Jones
1.8	3	Mechanical	Dave Newton
2.9	2	Electrical	Dave Newton
4.9	7	Electrical	Bob Jones
4.2	9	Mechanical	Bob Jones
4.8	8	Mechanical	Bob Jones
4.4	4	Electrical	Bob Jones
4.5	6	Electrical	Dave Newton

1. Ignore for now the months since the last maintenance service (x₁) and the repairperson who performed the service. Develop the estimated simple linear regression equation to predict the repair time (y) given the type of repair (x₂). Recall that x₂ = 0 if the type of repair is mechanical and 1 if the type of repair is electrical.
2. Does the equation that you developed in part (a) provide a good fit for the observed data? Explain.
3. Ignore for now the months since the last maintenance service and the type of repair associated with the machine. Develop the estimated simple linear regression equation to predict the repair time given the repairperson who performed the service. Let x₃ = 0 if Bob Jones performed the service and x₃ = 1 if Dave Newton performed the service.
4. Does the equation that you developed in part (c) provide a good fit for the observed data? Explain.
5. Develop the estimated regression equation to predict the repair time given the number of months since the last maintenance service, the type of repair, and the repairperson who performed the service.
6. At the .05 level of significance, test whether the estimated regression equation developed in part (e) represents a significant relationship between the independent variables and the dependent variable.
7. Is the addition of the independent variable x₃, the repairperson who performed the service, statistically significant? Use α = .05. What explanation can you give for the results observed?

Answer 1

a) The equation is y = 4.066667 - 0.61667 X2

The R square is 0.08712. The independent variable therefore explains only 8.7% of the variation in y

b) The p vvalue of the regression model is 0.4. At 5% level of significance the model is not significant.

The data is as follows :

R Square	0.08712
Adjusted R Square	-0.02699
Standard Error	1.093351
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.912667	0.912667	0.763472	0.407707
Residual	8	9.563333	1.195417
Total	9	10.476
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	4.066667	0.446359	9.110759	1.69E-05	3.037362
X2	-0.61667	0.705755	-0.87377	0.407707	-2.24414
RESIDUAL OUTPUT
Observation	Predicted y	Residuals
1	4.066667	-1.16667
2	3.45	-0.45
3	4.066667	0.733333
4	3.45	-1.65
5	4.066667	-1.16667
6	4.066667	0.833333
7	3.45	0.75
8	3.45	1.35
9	4.066667	0.333333
10	4.066667	0.433333

c) The equation is y = 4.62 -1.6 X3

The R square for the model is 0.61092.The independent variable is thus able to explain 61% of the variation in the  y

d) The p value is 0.007563 and the model is thus statistically significant.

The data is as follows :

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.781614
R Square	0.61092
Adjusted R Square	0.562285
Standard Error	0.713793
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	6.4	6.4	12.56133	0.007573
Residual	8	4.076	0.5095
Total	9	10.476
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	4.62	0.319218	14.47288	5.08E-07	3.883882	5.356118	3.883882	5.356118
X3	-1.6	0.451442	-3.5442	0.007573	-2.64103	-0.55897	-2.64103	-0.55897
RESIDUAL OUTPUT
Observation	Predicted y	Residuals
1	3.02	-0.12
2	3.02	-0.02
3	4.62	0.18
4	3.02	-1.22
5	3.02	-0.12
6	4.62	0.28
7	4.62	-0.42
8	4.62	0.18
9	4.62	-0.22
10	3.02	1.48

e) The equation is y = 2.962567 + 0.291444X1 -1.10241 X2 -0.60909 X3

The R square of the model is 0.9009 ie 90% of the variation in y is explained by the model. The p value is also highly significant.

f) Addition of X3 or repairperson is not statistically signicant as the p value is 0.167 >0.05 X1 and X2 are significant.

The data is as follows :

ANOVA
	df	SS	MS	F	Significance F
Regression	3	9.430492	3.143497	18.04002	0.002091
Residual	6	1.045508	0.174251
Total	9	10.476
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	2.962567	0.587176	5.045452	0.002344	1.5258	4.399334	1.5258	4.399334
X1	0.291444	0.083598	3.486238	0.013043	0.086886	0.496002	0.086886	0.496002
X2	-1.10241	0.303344	-3.63418	0.010911	-1.84466	-0.36015	-1.84466	-0.36015
X3	-0.60909	0.38793	-1.5701	0.167444	-1.55832	0.34014	-1.55832	0.34014