Answer the following questions: a. Use the Excel ‘Regression’ tool and develop a

Question

Answer the following questions:

a. Use the Excel ‘Regression’ tool and develop an estimated regression equation that can be used to predict the fuel efficiency for highway driving given the engine’s displacement, fuel type, and car size. [Hint: Use the COMPACT class as a reference category. Use the REGULAR fuel type as a reference category. Use the =IF function in Excel to create dummy variables: =IF(logical_test, [value_if_true], [value_if_false]). The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(B2=“midsize”,1,0) returns 1 if B2 is “midsize”, and 0 if B2 is not “midsize”.]

b. Using the Excel results:
(i) carefully interpret the coefficient of determination (r-square);
(ii) carefully interpret all the coefficients (b’s) in the estimated regression equation.

HELPFUL HINTS: How to create dummy variables for regression analysis

The ‘car size’ variable is qualitative and has three categories: compact, midsize, and large. As explained on pp. 724-725 in the textbook, when a qualitative variable has k levels (categories), k-1 dummy variables need to be included in the regression equation. One category (called the ‘reference category’) is excluded from the equation. In this exercise, k=3, hence, we need to include 3-1=2 dummy variables for the car size. As suggested in the lab assignment, we exclude a ‘compact car’ category. Hence, the two dummy variables in the equation will be labeled ‘midsize car’ and ‘large car.’ The ‘midsize car’ dummy variable will be coded as 1 for those observations with the midsize car attribute, and 0 otherwise. The ‘large car’ dummy variable will be coded as 1 for those observations with the large car attribute, and 0 otherwise. For example,

1

The ‘fuel type’ variable is qualitative and has two categories: regular and premium. As explained on pp. 724- 725 in the textbook, when a qualitative variable has k levels (categories), k-1 dummy variables need to be included in the regression equation. One category (called the ‘reference category’) is excluded from the equation. In this exercise, k=2, hence, we need to include 2-1=1 dummy variables for the car size. As suggested in the lab assignment, we exclude a ‘regular’ category. Hence, the dummy variable in the equation will be labeled ‘premium.’ The ‘premium’ dummy variable will be coded as 1 for those observations with the premium type of fuel attribute, and 0 otherwise. For example

Car Class Displacement (liters) Fuel Type Hwy MPG 1 Compact 3.1 P 25 2 Compact 3.1 P 25 3 Compact 3 P 25 4 Compact 3 P 25 5 Compact 3 P 25 6 Compact 3 P 25 7 Compact 2.4 P 25 8 Compact 3.5 P 25 9 Compact 3 P 25 10 Compact 2.4 P 24 11 Compact 2.8 P 24 12 Compact 2.5 P 24 13 Compact 3 P 24 14 Compact 2.5 P 24 15 Compact 2.4 R 24 16 Compact 5.3 P 24 17 Compact 6 P 24 18 Compact 3.5 R 24 19 Compact 3.5 R 24 20 Compact 3.5 R 24 21 Compact 3.5 R 24 22 Compact 3.5 R 24 23 Compact 3.8 R 24 24 Compact 3.5 R 24 25 Compact 3.5 P 24 26 Compact 5.3 P 24 27 Compact 4.6 P 24 28 Compact 3.5 P 24 29 Compact 4.2 P 24 30 Compact 4.6 P 24 31 Compact 4.6 P 24 32 Compact 4.6 P 24 33 Compact 3.5 P 24 34 Compact 3.6 P 24 35 Compact 3.2 P 24 36 Compact 3.2 P 24 37 Compact 3.5 R 24 38 Compact 2.4 R 24 39 Compact 3.5 R 24 40 Compact 3.5 R 24 41 Compact 3.2 P 23 42 Compact 4.2 P 23 43 Compact 4.8 P 23 44 Compact 4.8 P 23 45 Compact 3.5 P 23 46 Compact 4.6 R 23 47 Compact 4.6 R 23 48 Compact 5.7 R 23 49 Compact 5.7 R 23 50 Compact 4.6 R 23 51 Compact 4.6 R 23 52 Compact 4.2 P 23 53 Compact 4.2 P 23 54 Compact 4.8 P 23 55 Compact 3.5 P 23 56 Compact 3 P 23 57 Compact 4.4 P 23 58 Compact 3 P 22 59 Compact 3.9 R 22 60 Compact 3.5 P 22 61 Compact 4.2 P 22 62 Compact 4.2 P 22 63 Compact 4.6 R 22 64 Compact 4.6 R 22 65 Compact 3.5 R 22 66 Compact 5.7 R 22 67 Compact 5.7 R 22 68 Compact 3.5 R 22 69 Compact 4.8 P 22 70 Compact 3 P 22 71 Compact 4.2 P 22 72 Compact 5 P 22 73 Compact 3.5 P 22 74 Compact 5.5 P 22 75 Compact 4.2 P 21 76 Compact 5.5 P 21 77 Compact 5.5 P 21 78 Compact 4.5 P 21 79 Compact 5.5 P 21 80 Compact 4.6 P 21 81 Compact 4.2 P 20 82 Compact 4.2 P 20 83 Compact 6 P 20 84 Compact 4.5 P 20 85 Compact 5.5 P 20 86 Compact 6 P 19 87 Compact 5.2 P 19 88 Compact 5.2 P 19 89 Compact 4.4 P 19 90 Compact 5.5 P 19 91 Compact 6.2 P 19 92 Compact 6.2 P 18 93 Compact 6.2 P 18 94 Compact 6.1 P 18 95 Compact 6.1 P 18 96 Compact 4.6 R 18 97 Compact 4.6 R 18 98 Compact 6.1 P 18 99 Compact 5.5 P 17 100 Compact 5.5 P 17 101 Compact 6.2 P 17 102 Compact 6 P 17 103 Compact 5.5 P 17 104 Compact 5 P 17 105 Compact 5 P 17 106 Compact 5.5 P 16 107 Compact 6 P 16 108 Compact 5.5 P 16 109 Compact 6 P 16 110 Compact 5.7 P 16 111 Compact 5.7 P 15 112 Midsize 1.8 R 37 113 Midsize 2 R 35 114 Midsize 1.6 R 35 115 Midsize 1.8 R 35 116 Midsize 1.6 R 34 117 Midsize 2.4 R 34 118 Midsize 2 R 33 119 Midsize 1.6 R 33 120 Midsize 2 R 33 121 Midsize 2 R 33 122 Midsize 1.8 R 33 123 Midsize 2.5 R 33 124 Midsize 2 R 33 125 Midsize 1.6 R 32 126 Midsize 1.6 R 32 127 Midsize 1.6 R 32 128 Midsize 2 R 32 129 Midsize 2.4 R 32 130 Midsize 2 R 32 131 Midsize 1.8 R 32 132 Midsize 2.5 R 32 133 Midsize 2.4 R 32 134 Midsize 2 P 31 135 Midsize 2.4 R 31 136 Midsize 2 R 31 137 Midsize 2 R 31 138 Midsize 2.4 R 31 139 Midsize 2.4 R 31 140 Midsize 2.4 R 31 141 Midsize 2.4 R 31 142 Midsize 2.4 R 31 143 Midsize 2.2 R 31 144 Midsize 2.4 R 31 145 Midsize 2.4 R 31 146 Midsize 2.5 R 31 147 Midsize 1.8 R 31 148 Midsize 2 R 31 149 Midsize 2.4 R 31 150 Midsize 2.4 R 31 151 Midsize 2 P 30 152 Midsize 2.4 R 30 153 Midsize 2.4 R 30 154 Midsize 2 R 30 155 Midsize 2.4 R 30 156 Midsize 2.4 R 30 157 Midsize 2.4 R 30 158 Midsize 2.4 R 30 159 Midsize 2 R 30 160 Midsize 2.5 R 30 161 Midsize 2.4 R 30 162 Midsize 2 P 29 163 Midsize 2 P 29 164 Midsize 2 P 29 165 Midsize 2 P 29 166 Midsize 2 P 29 167 Midsize 2.3 R 29 168 Midsize 2.3 R 29 169 Midsize 2 R 29 170 Midsize 2 R 29 171 Midsize 3.5 R 29 172 Midsize 2.5 R 29 173 Midsize 2.5 R 29 174 Midsize 2.5 R 29 175 Midsize 2.5 R 29 176 Midsize 3.5 R 29 177 Midsize 3.5 R 29 178 Midsize 3.5 R 29 179 Midsize 2.5 P 29 180 Midsize 2 P 29 181 Midsize 3.5 R 29 182 Midsize 1.8 R 29 183 Midsize 2.4 R 29 184 Midsize 2.3 R 29 185 Midsize 2.3 R 29 186 Midsize 2.3 R 29 187 Midsize 3.5 R 29 188 Midsize 2.4 P 28 189 Midsize 2.4 P 28 190 Midsize 2 P 28 191 Midsize 3 P 28 192 Midsize 3 P 28 193 Midsize 2.5 P 28 194 Midsize 2.5 P 28 195 Midsize 2.4 P 28 196 Midsize 2.4 P 28 197 Midsize 2.4 P 28 198 Midsize 2.4 P 28 199 Midsize 3.5 R 28 200 Midsize 2 R 28 201 Midsize 2 R 28 202 Midsize 2 R 28 203 Midsize 2 R 28 204 Midsize 3.9 R 28 205 Midsize 3.5 R 28 206 Midsize 3.3 R 28 207 Midsize 3.5 R 28 208 Midsize 3.5 R 28 209 Midsize 3 P 28 210 Midsize 2.3 P 28 211 Midsize 2 P 28 212 Midsize 3.8 R 28 213 Midsize 3.5 R 28 214 Midsize 2.3 R 28 215 Midsize 2.7 R 28 216 Midsize 2.3 R 28 217 Midsize 3.5 R 28 218 Midsize 2.3 R 28 219 Midsize 3.8 R 28 220 Midsize 3.5 R 28 221 Midsize 2 P 27 222 Midsize 3.1 P 27 223 Midsize 2.5 P 27 224 Midsize 2.5 P 27 225 Midsize 2.5 P 27 226 Midsize 2.5 P 27 227 Midsize 2.5 R 27 228 Midsize 2.5 R 27 229 Midsize 2.5 R 27 230 Midsize 2.5 R 27 231 Midsize 3.3 R 27 232 Midsize 3.5 P 27 233 Midsize 3.1 P 27 234 Midsize 3 P 27 235 Midsize 3.5 P 27 236 Midsize 3.5 P 27 237 Midsize 3.5 P 27 238 Midsize 2.7 R 27 239 Midsize 2.7 R 27 240 Midsize 2.7 R 27 241 Midsize 2 R 27 242 Midsize 2.7 R 27 243 Midsize 2.4 R 27 244 Midsize 3 R 26 245 Midsize 3 R 26 246 Midsize 3.6 R 26 247 Large 3 P 26 248 Large 3 P 26 249 Large 3 P 26 250 Large 2 P 26 251 Large 2.8 P 26 252 Large 2.5 P 26 253 Large 2.5 P 26 254 Large 2.5 P 26 255 Large 2.4 P 26 256 Large 2.4 P 26 257 Large 2.4 R 26 258 Large 3.5 R 26 259 Large 3.6 R 26 260 Large 3.3 R 26 261 Large 2.7 R 26 262 Large 2.7 R 26 263 Large 3.8 R 26 264 Large 3.3 R 26 265 Large 3.2 P 26 266 Large 3.5 P 26 267 Large 3 P 26 268 Large 3 P 26 269 Large 3 P 26 270 Large 2.3 P 26 271 Large 3.5 P 26 272 Large 2.3 P 26 273 Large 3.6 P 26 274 Large 3.6 R 26 275 Large 3.6 R 26 276 Large 3.6 R 26 277 Large 3.6 R 26 278 Large 3.6 R 26 279 Large 3.6 R 26 280 Large 3.5 R 26 281 Large 3.5 R 26 282 Large 3 P 25 283 Large 3.5 P 25 284 Large 2.5 P 25 285 Large 2.5 P 25 286 Large 2.5 P 25 287 Large 2.5 P 25 288 Large 3.5 R 25 289 Large 4.2 P 25 290 Large 4.2 P 25 291 Large 4.2 P 25 292 Large 3.8 R 25 293 Large 3.1 P 25 294 Large 3 P 25 295 Large 3 P 25 296 Large 3 P 25 297 Large 3 P 25 298 Large 3.5 P 25 299 Large 3.5 P 25 300 Large 3.8 P 25 301 Large 3.5 P 25 302 Large 5.3 P 25 303 Large 3.6 R 25 304 Large 3.6 R 25 305 Large 3.6 R 25 306 Large 3.6 R 25 307 Large 2.4 R 25 308 Large 3 R 25 309 Large 3 R 25 310 Large 3 R 25 311 Large 3 R 25

Explanation / Answer

Output:

i) Coefficient of determination is 0.83 . Which means 83% of model variance of fuel efficiency for highway driving is explained by independent variables.

ii) We can see that both Displacement in liters and Dummy:Premium has negative coefficients.

A unit increase in displacement will decrease effciency by -1.63 keeping all other variable constant.

Similarly, A unit increase in dummy:Premium with reference to regular will decrease effciency by -1.63 keeping all .

Regression Statistics Multiple R 0.91 R Square 0.83 Adjusted R Square 0.83 Standard Error 1.65 Observations 311 ANOVA df SS MS F Significance F Regression 4 4160.27 1040.07 383.90 0.00 Residual 306 829.01 2.71 Total 310 4989.28 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 29.76 0.55 53.91 0.00 28.68 30.85 28.68 30.85 Displacement (liters) -1.63 0.12 -13.98 0.00 -1.86 -1.40 -1.86 -1.40 dummy:midsize 3.96 0.32 12.41 0.00 3.34 4.59 3.34 4.59 dummy:large 1.64 0.29 5.60 0.00 1.07 2.22 1.07 2.22 dummy:Premium -1.12 0.21 -5.36 0.00 -1.53 -0.71 -1.53 -0.71

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.