Question

The following data are based on a study on drilling rock.

Depth at Which Drilling Begins, x (in feet)	Time to Drill 5 Feet, y (in minutes)
36	6.0
50	6.1
75	6.5
95	6.4
120	7.0
130	6.8
145	6.5
155	7.1
160	7.7
175	7.5
185	7.6
190	7.8
200	8.0

We want to determine whether the time it takes to drill a distance of 5 feet in rock increases with the depth at which the drilling begins. So, depth at which drilling begins is the independent variable, x, and time (in minutes) to drill five feet is the dependent variable, y.

1. Draw a scatter diagram of the data.

2. Find the least-squares regression line for the drilling data.

3. Use the line to predict the drilling time at x = 190 feet.

4. Should the line be used to predict the drilling time at x = 500 feet? Explain your answer.

5. Interpret the values of the slope and y-intercept.

Perform all of the tasks manually, and then perform regression analysis using the Excel data analysis regression tool.

You must hand in a report with all steps of your manual analysis, and also printouts of your Excel analysis.

Answer 1

The following scatter plot is obtained based on the data provided:

The following data are passed:

X	Y
36	6
50	6.1
75	6.5
95	6.4
120	7
130	6.8
145	6.5
155	7.1
160	7.7
175	7.5
185	7.6
190	7.8
200	8

The independent variable is X, and the dependent variable is Y. In order to compute the regression coefficients, the following table needs to be used:

	X	Y	X*Y	X2	Y2
	36	6	216	1296	36
	50	6.1	305	2500	37.21
	75	6.5	487.5	5625	42.25
	95	6.4	608	9025	40.96
	120	7	840	14400	49
	130	6.8	884	16900	46.24
	145	6.5	942.5	21025	42.25
	155	7.1	1100.5	24025	50.41
	160	7.7	1232	25600	59.29
	175	7.5	1312.5	30625	56.25
	185	7.6	1406	34225	57.76
	190	7.8	1482	36100	60.84
	200	8	1600	40000	64
Sum =	1716	91	12416	261346	642.46

Based on the above table, the following is calculated:

Therefore, based on the above calculations, the regression coefficients (the slope mm, and the y-intercept nn) are obtained as follows:

Therefore, we find that the regression equation is:

Y=5.4691+0.0116*X

Graphically:

Use the line to predict the drilling time at x = 190 feet

Y=5.4691+0.0116*190 = 7.6731

predict the drilling time at x = 500 feet

Y=5.4691+0.0116*500 = 11.2691

Should not be used since we do not have data for drilling so deep, there may be many more constraints.

Excel Output: Data->Data Analytics->Regression