Question

1. A supervisor suggests that the old computer program for estimating delivery times be abandoned. Instead, schedulers could use an alternative such as MapQuest to estimate driving times. As a pilot study, 500 recent deliveries were randomly selected. The Excel data listed below contains the following variables:

Actual Times: actual time required to make delivery

Old Model Deviations: difference in actual delivery time and predicted delivery time using old computer prediction model

MapQuest: predicted delivery times for the randomly selected routes using MapQuest

Use the data to calculate the deviations for the MapQuest estimates (DMapQuest = actual delivery time – MapQuest predicted delivery time), then construct an appropriate graph for comparing the deviations for the Old Computer model and the MapQuest model. Interpret the results and indicate which prediction model should be used in the future. (Do NOT include the calculated deviations here. Do include the graph and interpretation.)

2. A driver reads your answer to part (1) and makes the following comment: MapQuest gives driving times for cars. Driving times for trucks will probably be longer because we can’t go as fast, especially in city traffic. Is there some way to adjust the MapQuest prediction to make it fit trucks better? Construct a fitted line plot (scatterplot with fitted linear regression line) using the MapQuest times as the predictor variable and the actual times as the response variable. Call the fitted values ‘Adjusted MapQuest’ times. Provide the fitted line plot, regression equation and R2 values associated with the ‘Adjusted MapQuest’ times. Interpret your results and indicate whether the driver’s suggestion is valid or invalid.

3. Use the regression equation given in part (2) to provide ‘Adjusted MapQuest’ times for the sample of 500 deliveries and calculate the Adjusted Deviations = Actual times - Adjusted MapQuest times. (You do NOT need to put those values here.) Construct an appropriate graph for comparing the deviations associated with the Old Computer Model, the MapQuest model and Adjusted MapQuest model and include it here. Interpret the graph and recommend the modeling tool (method of predicting delivery times) you feel is most accurate.

Actual Times	Old Model Deviation	MapQuest
142	42	113
82	-18	79
142	42	101
151	51	112
154	54	127
124	24	90
134	34	89
135	35	113
123	23	94
150	50	111
121	21	105
111	11	101
142	42	109
118	18	102
159	59	119
98	-2	89
150	50	115
128	28	101
134	34	110
158	58	123
106	6	89
147	47	123
117	17	84
153	53	105
126	26	96
129	29	100
98	-2	76
139	39	111
119	19	95
98	-2	69
137	37	105
140	40	110
140	40	121
134	34	99
131	31	100
98	-2	76
123	23	116
108	8	96
112	12	85
143	43	116
133	33	92
127	27	99
124	24	95
102	2	82
106	6	87
158	58	110
122	22	93
117	17	93
179	79	142
124	24	91
127	27	114
109	9	90
121	21	96
119	19	92
125	25	107
121	21	106
138	38	112
129	29	111
137	37	112
121	21	99
156	56	110
142	42	113
112	12	89
105	5	85
142	42	114
142	42	118
137	37	107
94	-6	79
132	32	115
112	12	98
97	-3	85
140	40	112
126	26	106
131	31	98
93	-7	81
161	61	115
113	13	87
108	8	81
117	17	102
147	47	108
113	13	98
92	-8	83
136	36	106
118	18	88
120	20	84
82	-18	68
139	39	111
121	21	101
157	57	129
150	50	110
133	33	95
118	18	107
115	15	93
144	44	110
124	24	110
110	10	77
145	45	112
172	72	123
111	11	88
134	34	106
131	31	103
118	18	116
122	22	100
118	18	93
132	32	108
109	9	100
100	0	83
111	11	97
109	9	82
141	41	102
124	24	108
85	-15	67
126	26	97
140	40	114
121	21	102
148	48	118
110	10	92
164	64	127
136	36	106
138	38	107
142	42	100
112	12	96
129	29	94
151	51	112
122	22	97
100	0	73
124	24	98
141	41	115
126	26	106
120	20	92
105	5	97
144	44	92
136	36	107
99	-1	94
97	-3	102
93	-7	86
132	32	97
130	30	108
89	-11	76
107	7	82
128	28	91
108	8	98
134	34	101
122	22	93
135	35	105
118	18	95
121	21	98
98	-2	78
104	4	82
117	17	83
135	35	112
175	75	131
120	20	113
94	-6	81
106	6	95
132	32	110
116	16	98
137	37	99
81	-19	73
122	22	93
101	1	85
126	26	111
148	48	116
136	36	109
90	-10	85
135	35	102
138	38	111
158	58	125
118	18	94
120	20	91
165	65	123
116	16	104
117	17	90
137	37	99
123	23	110
144	44	109
159	59	126
114	14	104
101	1	93
114	14	98
101	1	81
122	22	87
168	68	133
111	11	104
147	47	122
130	30	106
131	31	105
117	17	102
113	13	102
125	25	98
123	23	96
126	26	113
92	-8	78
136	36	106
124	24	95
153	53	131
91	-9	87
135	35	102
129	29	94
123	23	97
150	50	115
145	45	105
103	3	86
127	27	104
122	22	92
119	19	92
117	17	97
136	36	107
85	-15	70
106	6	84
111	11	89
166	66	119
124	24	112
163	63	122
154	54	104
134	34	93
100	0	83
109	9	82
125	25	105
72	-28	79
93	-7	86
148	48	110
70	-30	70
125	25	93
163	63	119
111	11	94
111	11	84
124	24	111
117	17	98
148	48	109
152	52	118
112	12	94
115	15	82
89	-11	87
124	24	88
126	26	104
133	33	103
131	31	107
178	78	134
69	-31	63
129	29	100
119	19	96
106	6	95
163	63	132
160	60	135
107	7	96
118	18	87
130	30	111
123	23	102
133	33	112
139	39	123
115	15	104
101	1	89
152	52	123
117	17	105
110	10	86
130	30	98
138	38	117
111	11	85
123	23	98
99	-1	87
149	49	107
130	30	95
161	61	121
157	57	123
86	-14	76
108	8	98
128	28	92
112	12	88
149	49	104
127	27	96
93	-7	82
136	36	105
119	19	93
118	18	90
114	14	89
146	46	105
106	6	83
94	-6	74
129	29	112
133	33	97
156	56	125
151	51	122
108	8	87
84	-16	74
127	27	91
150	50	103
137	37	107
112	12	97
124	24	103
101	1	77
125	25	98
122	22	92
117	17	91
136	36	110
110	10	86
123	23	116
129	29	102
128	28	105
126	26	105
141	41	104
113	13	99
127	27	98
137	37	96
112	12	93
159	59	124
148	48	108
111	11	90
133	33	113
118	18	104
157	57	108
118	18	98
147	47	125
118	18	94
125	25	109
110	10	85
105	5	97
75	-25	74
130	30	100
127	27	110
93	-7	70
123	23	99
114	14	94
159	59	129
113	13	108
89	-11	78
131	31	102
154	54	138
90	-10	74
98	-2	87
139	39	114
136	36	107
103	3	76
114	14	96
126	26	98
147	47	117
101	1	74
133	33	92
143	43	109
110	10	95
98	-2	89
121	21	88
113	13	91
149	49	123
128	28	97
133	33	103
116	16	94
176	76	124
117	17	99
118	18	99
92	-8	89
96	-4	77
126	26	90
81	-19	72
139	39	108
107	7	93
76	-24	64
133	33	107
109	9	79
137	37	111
127	27	101
136	36	103
134	34	95
100	0	83
103	3	88
106	6	75
125	25	101
120	20	101
127	27	94
129	29	99
148	48	110
108	8	92
123	23	106
136	36	118
162	62	116
113	13	81
136	36	108
119	19	96
135	35	113
144	44	91
166	66	124
143	43	114
90	-10	74
146	46	111
167	67	124
128	28	107
125	25	96
112	12	93
151	51	122
73	-27	76
125	25	104
116	16	94
116	16	101
103	3	100
104	4	86
133	33	104
166	66	119
125	25	107
148	48	108
147	47	117
85	-15	78
123	23	95
98	-2	91
145	45	120
148	48	115
101	1	80
131	31	110
115	15	90
99	-1	84
146	46	120
129	29	107
91	-9	77
116	16	92
126	26	92
90	-10	69
125	25	92
143	43	101
114	14	94
144	44	107
76	-24	82
124	24	97
151	51	123
133	33	101
103	3	91
103	3	97
124	24	108
96	-4	77
132	32	107
142	42	108
136	36	110
124	24	104
146	46	123
97	-3	83
99	-1	90
118	18	81
117	17	101
135	35	116
151	51	123
124	24	88
115	15	92
142	42	111
92	-8	90
117	17	88
148	48	116
146	46	114
167	67	122
114	14	91
149	49	109
123	23	102
111	11	100
106	6	92
126	26	103
148	48	117
108	8	83
108	8	85
166	66	137
140	40	103
119	19	85
126	26	94
132	32	92
141	41	118
157	57	127
142	42	112
145	45	100
81	-19	77
119	19	112
153	53	112
97	-3	78
123	23	91
121	21	91
121	21	89
120	20	106
104	4	83
97	-3	70
159	59	122
106	6	83
180	80	134
144	44	108
152	52	120
120	20	98
126	26	102
101	1	89
131	31	109
94	-6	86
106	6	79
124	24	95
145	45	118
168	68	111
123	23	95
130	30	95
113	13	94
137	37	110
90	-10	90
93	-7	79
147	47	112
146	46	108
125	25	97
128	28	99
132	32	97
119	19	77

Answer 1

1. If you observe the following graph it is evident that dmapquest values are varying less. Hence MapQuest can be used to predict the delivery times in the future.2.The regression equation is actual time = - 0.98 + 1.26*mapquest with R² = 0.76.

Below I m producing the anlysis from regression.

Predictor Coef SE Coef T P
Constant -0.977 3.200 -0.31 0.760
mapquest 1.26021 0.03177 39.66 0.000

You can observe that the regression coefficient is significant approving the driver's claim and need of adjustment.

Now in the following graph one can observe that the variation in the 'adjusted deviation' is minimum and its varying around zero unlike 'old model deviation' and 'dmapquest'.

Hence, reaching to the concluson MapQuest can be used to used to predict the delivery time with adjustment provided by regression equation given above.