Answered step by step
Verified Expert Solution
Question
1 Approved Answer
You have been hired to conduct business research for the purpose of identifying ways to improve fuel efficiency without disturbing consumer preference. Use the data
You have been hired to conduct business research for the purpose of identifying ways to improve fuel efficiency without disturbing consumer preference. Use the data presented to create a multiple regression model which explains the impact of the variables you selected on City and Highway fuel economy. Use Excel to analyze the data and test your theories, then use Word to present your findings. Be sure to upload two separate files for this assignment: one for your calculations (Excel) and one for your written report (Word). Both files will be due before the course ends on June 27. |
(1) Make a frequency distributions and/or histograms for CityMPG and HwyMPG. Describe the distributions. |
(2) Create and discuss the descriptive statistics for all varables. Use tables to present measures of central tendencies and measures of dispersion for continous variables. Use tables, graphs, and charts to present frequencies and descriptives for nominal data. Be sure to identify, report, and recommend a treatment for any outliers. |
(3)Detimine if there is a statisticsally significant difference in CityMPG based on vehicle body style. Do the same for HwyMPG. Are the results the same for City and Hwy? |
(4)Create a scatter plot for X = Weight and Y = CityMPG. What does it suggest? Fit a simple regression on the scatter plot. Interpret the slope and fit. |
(5)Estimate a multiple regression model using CityMPG as the response variable (dependent variable) and several numerical or binary predictors of your choice. Caution: If you use binary predictors, be careful to omit one of the binary predictors in each group (body style, drive type, transmission type) to avoid perfect multicollinearity. |
(6)Did your multiple regression model offer a substantially better fit than the simple regression in step (4)? |
Obs | Model | Doors | Style | Engine | Cylinders | CityMPG | HwyMPG | EngineSize | HP | Length | Width | Height | Weight | Drive | Transmission | TransType | NumGears | Coupe? | Hatchback? | Sedan? | SUV? | FWD? | RWD? | AWD? | Automatic? | Manual? | CVT? |
1 | Acura TL | 4 | Sedan | V6 | 6 | 20 | 29 | 3500 | 220 | 109.3 | 74.0 | 57.2 | 3968 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
2 | Audi A5 | 2 | Coupe | L4 | 4 | 22 | 32 | 1984 | 220 | 108.3 | 73.0 | 54.0 | 3583 | A | M-6 | M | 6 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 |
3 | BMW 4 Series 428i | 2 | Coupe | L4 | 4 | 22 | 35 | 1997 | 240 | 182.6 | 71.9 | 54.2 | 3470 | R | A-8 | A | 8 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
4 | BMW X1 sDrive28i | 4 | SUV | L4 | 4 | 23 | 34 | 1997 | 240 | 176.5 | 70.8 | 60.8 | 3527 | R | A-8 | A | 8 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
5 | Buice LaCrosse | 4 | Sedan | V6 | 6 | 18 | 28 | 3600 | 304 | 196.9 | 73.1 | 59.2 | 3990 | A | A-6 | A | 6 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 |
6 | Buick Enclave | 4 | SUV | V6 | 6 | 17 | 24 | 3600 | 288 | 201.9 | 79.0 | 70.3 | 4724 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
7 | Buick Regal | 4 | Sedan | L4 | 4 | 21 | 30 | 2000 | 259 | 190.2 | 73.1 | 58.4 | 3692 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
8 | Cadillac ATS | 4 | Sedan | L4 | 4 | 22 | 33 | 2500 | 202 | 182.8 | 71.1 | 55.9 | 3315 | R | A-6 | A | 6 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
9 | Cadillac CTS | 4 | Sedan | L4 | 4 | 20 | 30 | 2000 | 272 | 195.5 | 72.2 | 57.2 | 3616 | R | A-6 | A | 6 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
10 | Cadillac Escalade | 4 | SUV | V8 | 8 | 14 | 18 | 6200 | 403 | 202.5 | 79.0 | 75.9 | 5527 | R | A-6 | A | 6 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
11 | Chevrolet Camaro 1SS | 2 | Coupe | V8 | 8 | 16 | 24 | 3600 | 426 | 190.6 | 75.5 | 54.2 | 3719 | R | M-6 | M | 6 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 |
12 | Chevrolet Cruze LS | 4 | Sedan | L4 | 4 | 26 | 38 | 1400 | 138 | 181.0 | 70.7 | 58.1 | 3097 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
13 | Chevrolet Impala LTZ | 4 | Sedan | V6 | 6 | 19 | 29 | 3600 | 305 | 201.3 | 73.0 | 58.9 | 3800 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
14 | Chevrolet Malibu 2LT | 4 | Sedan | L4 | 4 | 25 | 36 | 2500 | 196 | 191.5 | 73.0 | 57.6 | 3532 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
15 | Chevrolet Spark LS | 4 | Hatchback | L4 | 4 | 31 | 39 | 1200 | 84 | 144.7 | 61.0 | 62.9 | 2269 | F | M-5 | M | 5 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
16 | Chevrolet Suburban LTZ | 4 | SUV | V8 | 8 | 15 | 21 | 5300 | 320 | 224.0 | 80.5 | 74.4 | 5674 | R | A-6 | A | 6 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
17 | Chrysler 200 Touring LX | 4 | Sedan | L4 | 4 | 20 | 31 | 2400 | 173 | 191.7 | 72.5 | 54.9 | 3402 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
18 | Chrysler 300 S | 4 | Sedan | V6 | 6 | 19 | 31 | 3600 | 300 | 198.6 | 75.0 | 58.7 | 4029 | R | A-8 | A | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
19 | Dodge Charger SXT | 4 | Sedan | V6 | 6 | 19 | 31 | 3600 | 292 | 199.9 | 75.0 | 58.4 | 3996 | R | A-8 | A | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
20 | Dodge Dart Limited | 4 | Sedan | L4 | 4 | 23 | 35 | 2400 | 184 | 183.9 | 72.0 | 57.7 | 3242 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
21 | Fiat 500 Sport | 2 | Hatchback | L4 | 4 | 31 | 40 | 1400 | 135 | 139.6 | 64.1 | 59.8 | 2434 | F | M-5 | M | 5 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
22 | Ford Fiesta S | 4 | Hatchback | L4 | 4 | 29 | 39 | 1600 | 120 | 159.7 | 67.8 | 58.1 | 2575 | F | A-6 | A | 6 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
23 | Ford Focus SE | 4 | Sedan | L4 | 4 | 27 | 37 | 2000 | 160 | 178.5 | 71.8 | 57.7 | 2960 | F | M-5 | M | 5 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
24 | Ford Mustang GT | 2 | Coupe | V8 | 8 | 15 | 26 | 5000 | 420 | 188.5 | 73.9 | 55.8 | 3618 | R | M-6 | M | 6 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 |
25 | Ford Taurus | 4 | Sedan | V6 | 6 | 19 | 29 | 3500 | 288 | 202.9 | 76.2 | 60.7 | 4054 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
26 | Honda Accord LX | 4 | Sedan | L4 | 4 | 24 | 34 | 2356 | 185 | 191.4 | 72.8 | 57.7 | 3192 | F | M-6 | M | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
27 | Honda CRV LX | 4 | SUV | L4 | 4 | 23 | 31 | 2354 | 185 | 178.3 | 71.6 | 64.7 | 3305 | F | A-5 | A | 5 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
28 | Hyundai Azera Limited | 4 | Sedan | V6 | 6 | 19 | 29 | 3300 | 293 | 193.3 | 73.2 | 57.9 | 3605 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
29 | Hyundai Genesis 5.0 | 4 | Sedan | V8 | 8 | 15 | 23 | 5000 | 429 | 196.5 | 74.4 | 58.3 | 4240 | R | A-8 | A | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
30 | Hyundai Santa Fe GLS | 4 | SUV | V6 | 6 | 18 | 25 | 3300 | 290 | 193.1 | 74.2 | 66.9 | 3933 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
31 | Infiniti Q60 | 4 | Coupe | V6 | 6 | 19 | 27 | 3700 | 330 | 183.1 | 71.8 | 54.8 | 3633 | R | A-7 | A | 7 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
32 | Infiniti QX50 | 4 | SUV | V6 | 6 | 17 | 25 | 3700 | 325 | 182.3 | 71.0 | 61.9 | 3790 | R | A-7 | A | 7 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
33 | Jaguar F-Type | 2 | Coupe | V6 | 6 | 20 | 28 | 3000 | 340 | 176.0 | 75.7 | 51.5 | 3477 | R | A-8 | A | 8 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
34 | Jeep Compass Limited | 4 | SUV | L4 | 4 | 21 | 28 | 2400 | 172 | 175.1 | 71.4 | 65.0 | 3258 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
35 | Jeep Grand Cherokee Limited | 4 | SUV | V6 | 6 | 17 | 25 | 3600 | 290 | 189.8 | 76.5 | 68.1 | 4685 | R | A-8 | A | 8 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
36 | Kia Forte LX | 4 | Sedan | L4 | 4 | 25 | 36 | 1797 | 148 | 179.5 | 70.1 | 56.5 | 2776 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
37 | Kia Soul | 4 | Hatchback | L4 | 4 | 24 | 30 | 1600 | 138 | 163.0 | 70.9 | 63.0 | 2615 | F | M-6 | M | 6 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
38 | Kia Sportage LX | 4 | SUV | L4 | 4 | 21 | 30 | 2359 | 176 | 174.8 | 73.0 | 64.4 | 3186 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
39 | Land Rover Range Rover Sport | 4 | SUV | V8 | 8 | 13 | 19 | 5000 | 510 | 196.8 | 81.6 | 72.3 | 5137 | A | A-8 | A | 8 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
40 | Lexus IS 250 | 2 | Sedan | V6 | 6 | 21 | 30 | 2500 | 204 | 183.7 | 71.3 | 56.3 | 3461 | R | A-6 | A | 6 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
41 | Lexus LS 460 | 4 | Sedan | V8 | 8 | 16 | 24 | 4600 | 386 | 200.0 | 73.8 | 58.1 | 4233 | R | A-8 | A | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
42 | Lexus RX 350 | 4 | SUV | V6 | 6 | 18 | 25 | 3500 | 270 | 187.8 | 74.2 | 66.7 | 4178 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
43 | Lincoln MKT | 4 | SUV | V6 | 6 | 17 | 25 | 3700 | 303 | 207.6 | 76.0 | 67.4 | 4702 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
44 | Lincoln MKZ | 4 | Sedan | L4 | 4 | 22 | 33 | 2000 | 240 | 194.1 | 73.4 | 58.2 | 3713 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
45 | Lincoln Navigator | 4 | SUV | V8 | 8 | 14 | 20 | 5400 | 310 | 208.4 | 78.8 | 78.3 | 5794 | R | A-6 | A | 6 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
46 | Mazda 2 | 4 | Hatchback | L4 | 4 | 28 | 34 | 1500 | 100 | 155.5 | 66.7 | 58.1 | 2306 | F | M-5 | M | 5 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
47 | Mazda CX-5 Sport | 4 | SUV | L4 | 4 | 26 | 32 | 2000 | 155 | 179.3 | 72.4 | 67.3 | 3194 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
48 | Mercedes-Benz C-250 | 4 | Sedan | L4 | 4 | 22 | 31 | 1800 | 201 | 180.8 | 69.7 | 56.9 | 3428 | R | A-7 | A | 7 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
49 | Mercedes-Benz CL600 | 2 | Coupe | V12 | 2 | 12 | 18 | 5500 | 510 | 200.6 | 84.2 | 55.8 | 4894 | R | A-5 | A | 5 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
50 | Mercedes-Benz ML350 | 4 | SUV | V6 | 6 | 17 | 22 | 3500 | 302 | 189.1 | 84.3 | 70.7 | 4751 | R | A-7 | A | 7 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
51 | Mini-Cooper | 2 | Hatchback | L4 | 4 | 29 | 37 | 1600 | 121 | 146.6 | 66.3 | 55.4 | 2605 | F | M-6 | M | 6 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
52 | Mitsubishi Outlander Sport SE | 4 | SUV | L4 | 4 | 25 | 31 | 2400 | 166 | 183.3 | 70.9 | 66.1 | 3296 | F | CVT | C | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | |
53 | Nissan Armada SV | 4 | SUV | V8 | 8 | 13 | 19 | 5600 | 317 | 207.7 | 79.3 | 77.2 | 5267 | R | A-5 | A | 5 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 |
54 | Nissan Cube S | 4 | Hatchback | L4 | 4 | 25 | 30 | 1800 | 127 | 156.7 | 66.7 | 65.0 | 2789 | F | M-6 | M | 6 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
55 | Nissan Maxima SV | 4 | Sedan | V6 | 6 | 19 | 26 | 3500 | 290 | 190.6 | 73.2 | 57.8 | 3570 | F | CVT | C | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | |
56 | Nissan Murano SV | 4 | SUV | V6 | 6 | 18 | 24 | 3500 | 260 | 189.9 | 74.1 | 67.0 | 4011 | F | CVT | C | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | |
57 | Nissan Versa S | 4 | Sedan | L4 | 4 | 27 | 36 | 1600 | 109 | 175.4 | 66.7 | 59.6 | 2363 | F | M-5 | M | 5 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
58 | Porsche Cayenne | 4 | SUV | V6 | 6 | 15 | 22 | 3600 | 300 | 190.8 | 76.3 | 67.4 | 4398 | A | M-6 | M | 6 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 |
59 | Scion FR-S | 2 | Coupe | L4 | 4 | 25 | 34 | 2000 | 200 | 166.7 | 69.9 | 50.6 | 2806 | R | A-6 | A | 6 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 |
60 | Scion iQ | 2 | Hatchback | L4 | 4 | 36 | 37 | 1329 | 94 | 120.1 | 66.1 | 59.1 | 2127 | F | CVT | C | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | |
61 | Scion XD | 4 | SUV | L4 | 4 | 27 | 33 | 1798 | 128 | 154.7 | 67.9 | 59.4 | 2665 | F | A-4 | A | 4 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
62 | Suburu Forester 2.5i Limited | 4 | SUV | L4 | 4 | 24 | 32 | 2500 | 170 | 180.9 | 70.7 | 68.2 | 3419 | A | CVT | C | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | |
63 | Suburu Legacy 2.5i Limited | 4 | Sedan | L4 | 4 | 24 | 32 | 2500 | 173 | 187.2 | 71.7 | 59.3 | 3427 | A | CVT | C | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | |
64 | Toyota Camry XLE | 4 | Sedan | L4 | 4 | 25 | 35 | 2500 | 178 | 189.2 | 71.7 | 57.9 | 3280 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
65 | Toyota Land Cruiser | 4 | SUV | V8 | 8 | 13 | 18 | 5700 | 381 | 194.9 | 77.6 | 74.0 | 5765 | A | A-6 | A | 6 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
66 | Toyota RAV4 XLE | 4 | SUV | L4 | 4 | 24 | 31 | 2500 | 176 | 179.9 | 72.6 | 67.1 | 3465 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
67 | Toyota Yaris | 3 | Hatchback | L4 | 4 | 30 | 37 | 1500 | 106 | 153.5 | 66.7 | 59.4 | 2295 | F | M-5 | M | 5 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
68 | Volkswagen Beetle 2.5L | 2 | Coupe | L4 | 4 | 22 | 29 | 2500 | 170 | 168.4 | 71.2 | 58.6 | 3038 | F | A-6 | A | 6 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
69 | Volkswagen Jetta SE | 4 | Sedan | L4 | 4 | 25 | 36 | 1800 | 170 | 182.2 | 70.0 | 57.2 | 3070 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
70 | Volkswagen Toureg V6 Sport | 4 | SUV | V6 | 6 | 17 | 23 | 3600 | 280 | 188.8 | 76.4 | 68.2 | 4711 | A | A-8 | A | 8 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
71 | Volkswagen Passat SE | 4 | Sedan | L4 | 4 | 24 | 34 | 1800 | 170 | 191.6 | 72.2 | 58.5 | 3230 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
72 | Volvo S60 T5 | 4 | Sedan | L5 | 5 | 21 | 30 | 2500 | 250 | 182.2 | 73.4 | 58.4 | 3528 | F | A-6 | A | 6 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 |
73 | Volvo XC90 | 4 | SUV | L6 | 6 | 16 | 25 | 3200 | 240 | 189.3 | 76.2 | 70.2 | 4667 | F | A-6 | A | 6 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
unusual | unusual | does not apply |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started