Question

Suppose you work for the city of San Antonio and are part of a team challenged to provide information to the City Manager about the possibility of energy rate increases for 2010. You found data provided by the U.S. Energy Information Administration which releases figures in their publication, Monthly Energy Review, about the cost of various fuels and electricity. The figures for four different items over a 12-year period are shown in the dataset on blackboard “Electricity.xls”.

File:  https://drive.google.com/file/d/1j3VY1cjtMtSSLg39TRiRoIf9mm-BgBG4/view?usp=sharing

Use the data to predict the cost of residential electricity from the cost of residential natural gas, residual fuel oil, and leaded regular gasoline. Your goal as the researcher is to choose the best model to predict residential electricity.

Must complete all the parts (1 - 7) to this problem:

Perform the following transformations and run the regression analysis in Excel:

• PART 1: ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x₃
• PART 2: ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x₃ + b₄x₁²
• PART 3: ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x₃ + b₄x₂²
• PART 4: ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x₃ + b₄x₃²
• PART 5: ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x₃ + b₄x₁x₂
• PART 6: ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x3 + b₄x₁x₃
• PART 7: Write the model you have chosen that best predicts residential electricity (include the partial regression coefficients), and write a 3-4 sentence conclusion arguing why you chose the model (use values to support your decision based on the summaries above).

Each time you transform a model you should evaluate the following:

• R²
• Adjusted R²
• F-test
• p-values for each independent explanatory variable
• residuals and the standard error

Answer 1

I assume from your question that x1 is Residential Natural Gas, x2 is Residual Fuel Oil, and x3 is Leaded Regular Gasoline.

Part (1)

1.1984 + 0.8806 x1 - 5.4371 x2 + 3.9211 x3

R-squared: 0.9661,   Adjusted R-squared: 0.9534
F-statistic: 76.02, p-value: 3.202e-06

Part (2)

1.7233 + 0.6383 x1 - 9.1243 x2 + 5.2139 x3 + 0.4826 x1x2

R-squared: 0.9674,   Adjusted R-squared: 0.9487
F-statistic: 51.91, p-value: 2.748e-05

Part (3)

1.8778 + 0.8933 x1 - 3.5828 x2 + 0.2453 x2^2 + 4.2093 x3

R-squared: 0.9673,   Adjusted R-squared: 0.9487
F-statistic: 51.81, p-value: 2.765e-05

Part (4)

1.1115 + 0.8776 x1 - 5.094 x2 + 3.992 x3 - 0.2656 x2x3

R-squared: 0.9661,   Adjusted R-squared: 0.9468
F-statistic: 49.92, p-value: 3.133e-05