Question

The data in the attached excel file comes from Consumer Reports and was collected over a two-year period. It gives the average mpg over a 195-mile trip, the weight of the vehicle (pounds), engine displacement (liters), number of cylinders, horsepower, type of transmission (0 = manual, 1 = automatic), the number of gears and whether the car was foreign (1) or domestic (0).

Part a: Build a model for predicting the average mpg based on the data in the attached excel spread sheet. Show and annotate your work in the excel spread sheet. Include an interpretation of the final model.

Part b: Using your final model from part a, construct an interval estimate of the average mpg of a vehicle which weighs 3000 pounds, with an engine displacement of 2.5 liters, 4 cylinders, 150 hp, manual transmission, 5 gears and is a domestic car. Explain what this means.

CASE	TRIP MPG	WEIGHT	DISPLACE	NO CYL	HP	TRANS	GEARS	FOR/DORM
1	32	2365	1.6	4	113	0	5	1
2	33	2430	1.6	4	108	0	5	1
3	42	1895	1.3	4	60	0	4	1
4	36	2320	1.6	4	74	1	3	0
5	32	2330	1.6	4	82	1	3	1
6	34	2255	1.5	4	68	1	3	1
7	36	2350	1.6	4	74	1	3	1
8	41	1635	1.3	4	58	0	4	1
9	36	2070	1.6	4	82	0	4	1
10	34	2115	1.5	4	68	0	4	1
11	35	1840	1.1	4	52	0	4	1
12	43	1970	1.5	4	78	0	4	1
13	51	1575	1.0	3	48	0	5	1
14	37	2185	1.5	4	68	0	4	1
15	36	2115	1.8	4	81	0	4	1
16	28	3040	2.2	4	145	1	4	1
17	34	2620	2.0	4	108	1	3	1
18	25	3230	3.0	6	142	1	4	1
19	29	2745	2.0	4	102	1	4	1
20	28	2573	1.9	4	110	1	4	1
21	27	2802	2.3	4	100	1	3	0
22	31	2699	2.0	4	90	1	3	0
23	36	2695	2.2	4	110	0	5	0
24	31	2885	2.5	4	100	0	5	0
25	23	3310	5.0	8	225	1	4	0
26	23	3430	5.0	8	170	1	4	0
27	29	2670	2.2	4	97	1	3	0
28	35	2925	2.0	4	115	1	4	1
29	28	2735	2.5	4	98	1	3	0
30	29	3155	3.0	6	140	1	4	0
31	30	2995	3.0	6	150	1	4	0
32	27	3150	3.0	6	136	1	3	0
33	29	2950	2.8	6	125	1	3	0
34	26	3295	3.8	6	140	1	4	0
35	28	2915	2.5	4	100	1	3	0
36	29	3220	2.8	6	125	1	4	0
37	26	2900	2.2	4	146	1	3	0
38	27	3205	2.5	4	153	1	4	1
39	26	2930	2.2	4	103	1	3	0
40	25	3320	3.0	6	157	1	4	1
41	26	3080	2.3	4	114	1	4	1
42	24	3625	3.0	6	136	1	3	0
43	23	3665	3.0	6	145	1	4	0
44	22	3625	2.4	4	106	1	4	1
45	23	3415	2.4	4	107	1	4	1

Answer 1

using excel>data>data analysis>Regression

we have

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.916998
R Square	0.840885
Adjusted R Square	0.810783
Standard Error	2.665201
Observations	45
ANOVA
	df	SS	MS	F	Significance F
Regression	7	1388.956	198.4222	27.93382	6.28E-13
Residual	37	262.8221	7.103299
Total	44	1651.778
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	47.63431	4.710892	10.11153	3.39E-12	38.08914	57.17948
WEIGHT	-0.00951	0.001707	-5.56943	2.4E-06	-0.01296	-0.00605
DISPLACE	-1.68525	1.631644	-1.03286	0.308375	-4.99128	1.62077
NO CYL	1.000934	0.835687	1.197738	0.238636	-0.69233	2.694198
HP	-0.02358	0.027863	-0.84642	0.40276	-0.08004	0.032872
TRANS	2.091025	2.04431	1.022851	0.313019	-2.05114	6.23319
GEARS	2.743086	1.128762	2.430173	0.02006	0.455998	5.030174
FOR/DORM	-1.5362	1.106093	-1.38885	0.173181	-3.77736	0.704956

a ) the regression model is  

mpg = 47.634-0.0095*weight - 1.685*displacement +1 *Nocyl-0.0236 *HP+2.0910*TRANS +2.743 GEARS-1.5362*FOR/DORM

since p value of f stat is less than 0.05 so the model is significant to use

b ) weighs 3000 pounds, with an engine displacement of 2.5 liters, 4 cylinders, 150 hp, manual transmission(=0), 5 gears and is a domestic car

mpg =47.634-0.0095*3000 - 1.685*2.5 +1 *4-0.0236 *150+2.0910*0 +2.743*5-1.5362*1 =27.56

an interval estimate of the average mpg of a vehicle which weighs 3000 pounds, with an engine displacement of 2.5 liters, 4 cylinders, 150 hp, manual transmission, 5 gears and is a domestic car. is 27.56