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/Dom
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