Question

3. Consider the following problem related to Chapter 12 of the text. City utility people have observed over time that as temperatures warm up, water consumption in the city increases. Examine the following data gathered on various days over a yearly period. For each of the eight days, the high temperature and the water usage in millions of gallons is given.

                Temperature                       Water Use

                            (degrees Fahrenheit)               (millions of gallons)

                                          103                                          219

                                          39                                            56

                                          77                                          107

                                          78                                          129

                                          50                                            68

                                          96                                          184

                                          90                                          150

                                          75                                          112

Construct a scatter plot of the data, then add a trend line. Directions for scatter plot and trend line: To get a scatter plot of the data in Excel, click and drag over the data, go to the Insert Tab and create a Scatter Plot of the data. To place the regression line on the scatter plot, right hand click on one of the points on the graph. A command, add trendline, should appear. This is your regression line. Now, using Data Analysis, conduct a regression analysis of these data to develop a model for predicting Water Use by Temperature. Directions in Excel: Use the Data tab, select Data Analysis and then select Regression. In the regression dialog box, enter the location of the y variable and of the x variable. Check the “labels” box since we have titled each column. Check the box “residuals” at the bottom of the dialog box. Click OK and the regression output should appear including the residuals.

Answer 1

a) Scatterplot with trend line:

b) Excel output:

Excel output with residual

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.954053
R Square	0.910216
Adjusted R Square	0.895252
Standard Error	17.88776
Observations	8
ANOVA
	df	SS	MS	F	Significance F
Regression	1	19463.04	19463.04	60.82736	0.000234
Residual	6	1919.831	319.9719
Total	7	21382.88
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-54.356	24.23709	-2.24268	0.066103	-113.662	4.949978	-113.662	4.949978
Temperature	2.401066	0.307861	7.79919	0.000234	1.647758	3.154375	1.647758	3.154375
RESIDUAL OUTPUT
Observation	Predicted Water use	Residuals
1	192.9538	26.04621
2	39.28555	16.71445
3	130.5261	-23.5261
4	132.9271	-3.92713
5	65.69727	2.302725
6	176.1463	7.853673
7	161.7399	-11.7399
8	125.7239	-13.7239

Estimated regression equation:

Water use^ = 2.4011*Temp + (-54.356)