Question

Completely work the problems in Excel, conclusions and answers may be typed in Excel or Word. Then submit the assignment via Canvas to upload the file, so I can grade an electronic version of the homework.

Chapter 14

#1 Scott Bell Builders would like to predict the total number of labor hours spent framing a house based on the square footage of the house. The following data has been compiled on ten houses recently built.

Square Footage (100s)	Framing Labor Hours	Square Footage (100s)	Framing Labor Hours
20	195	27	225
21	170	29	240
23	220	31	225
23	200	32	275
26	230	35	260

a) Least-squares estimated regression equation that related framing labor hours to house square footage.

i. Develop a scatter diagram with Framing Labor Hours on the vertical axis and Square Footage (100s) on the horizontal axis.

ii. What does the scatter diagram developed in part (i) indicate about the relationship between the two variables?

iii. Develop the estimated regression equation that could be used to predict the Framing Labor Hours given the Square Footage.

iv. Test for a significant relationship at the 0.05 level of significance.

v. Did the estimated regression equation provide a good fit? Explain.

vi. Use the regression equation to predict Framing Labor Hours when the house size is 3350 square feet

b) Use residual analysis to determine whether any outliers are present. Briefly summarize your findings and conclusions.

Answer 1

a) i) The scatter diagram :

FramingLabourHoursvs Square Footage(100s) y 5.3605x+80.876 250 S 230 bD 210 190 20 25 30 Square Footage(100s)

ii) Framing Labour Hours increase linearly with increase in Square Footage(100s). Similarly it can be said that,Framing Labour Hours decrease linearly with decrease in Square Footage(100s).

So the relationship is directly proportional.

iii) We develop the Estimated Regression equation in the following way:

Correlation Coefficient	r	0.872214703
Average of y dataset	Mean(y)	224
Standard Deviation of y dataset	Sy	30.80404014
Average of x dataset	Mean(x)	26.7
Standard Deviation of x dataset	Sx	5.01220732
Slope(m) = r*Sy/Sx	m	5.360459973
y-Interecept(Mean(y) - m *Mean(x))	b	80.87571871

Hence the estimated regression equation is

Predicted-(Framing Labour Hours) = 5.360459973 * (Square Footage in 100s) + 80.87571871

iv) The test results for a significant relationship at 0.05 level of significance gives:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.872214703
R Square	0.760758488
Adjusted R Square	0.730853299
Standard Error	15.98093596
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	6496.877488	6496.877488	25.43901	0.000997
Residual	8	2043.122512	255.390314
Total	9	8540
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	80.87571871	28.82327122	2.805917416	0.022987	14.40914	147.3423	14.40914	147.3423
X Variable 1	5.360459973	1.06280094	5.04371023	0.000997	2.909637	7.811283	2.909637	7.811283
RESIDUAL OUTPUT
Observation	Predicted Y	Residuals	Standard Residuals
1	188.08	6.92	0.46
2	193.45	-23.45	-1.56
3	204.17	15.83	1.05
4	204.17	-4.17	-0.28
5	220.25	9.75	0.65
6	225.61	-0.61	-0.04
7	236.33	3.67	0.24
8	247.05	-22.05	-1.46
9	252.41	22.59	1.50
10	268.49	-8.49	-0.56

v) The estimated regression equation provided a good fit as the standard residuals clearly indicated that there were no outliers.

vi) Using the formula Predicted-(Framing Labour Hours) = 5.360459973 * (Square Footage in 100s) + 80.87571871

we have Predicted Framing Labor Hours = 5.360459973 * (33.50) + 80.87571871 = 260.4511 Hrs

b) The standard deviation obtained from the residuals is 14.2938

Twice o f the standard deviation = 28.58757

Since none of the residuals are exceeding this limit , there are no outliers in the data.

Absence of outliers can also be verified from the Standard Residuals column, where the absolute value of no Standard Residual has exceeded the value 2.

So its concluded that there are no outliers in the data.

The screenshot of the Entire excel sheet is also provided below:

Dook1 Excel nsert Page Layout Formulas Dela Revie View Tell me what you want to do. A Saurav Bhattacharyya -X (sauravbh -WIPRO LIMITED st Cisco) Home Cut Copy Share R, |Normal Formational Formlebs Good AutoSum -A Bad B 1 u , E·クー,-E E=円Merge & Center , $ , % , Insert Delete Format Sort &Find & Filter Select Neutral Format Painter 8IU Clear - FramingLabourHours vs Square Footage(100s) SquareFootageHours 21 170 23 220 23 200 Multiple R 0872215 0.76075 0.730953 27 29 225 240 Adjusted R Square Standard Error 32275 Repression 1 64%.37 743U 6496.0775 25.439 0.00099701 2043.122512 255.39031 14 Correlation Coefficient 15 Average of y dataset 16 Standard Devsation of y dataset S 17 Average of x dataset 18 Standard Deviation of x datasetS 0.8722147 Squsre Footage(100 5.0122073 80.87572 2832327122 28059174 0023 14.4091361 147.34230 14.401361 147.34230 20 Slope) rsy/Sx 21 y-Interecep (Meany)-mMx) b 5.36046 80.87572 RESIDUAL OUTPUT 24 Predicted value for Square Footage of 3350 260.451 1188.03 2 19345 3 204.17 4 204.17 5 22025 2345 9.75 723633 8 24705 146 1.50 2205 heetz Sheet +80% 2 co