1. (15+2 for Excel work) Consider the following data for the average cost of various fuels and electricity for a twelve year period. The data measure the following:

 Electricity Residential rate per kilowatt hour Natural Gas Residential natural gas per 1000 cubic feet Fuel Oil Residential fuel oil per gallon Gasoline Regular gasoline per gallon

Your model will attempt to predict residential electricity costs using the cost of the other fuels.

a) (1) State whether you would expect a positive or negative relationship between electricity costs and each of the independent variables and explain why.

b) (2) Use EXCEL, to create a correlation using all independent variables provided. Discuss all aspects of the correlation matrix, as it compares to your expectations in part (a) and what it tells you about the potential results of a regression analysis.

c) (3) Use EXCEL to run a multiple regression to estimate electricity costs using all independent variables provided. Test if the overall regression is significant at the 5% level.

d) (3) Test for the significance of each slope coefficient at the 5% level. (Incorporate all tests into ONE five step answer as in the slides.)

e) (2) Based on your correlation matrix and the results of your regression do you suspect multicollinearity? If yes, which independent variable(s) to you think could be responsible?

f) (2) Explain how you test for multicollinearity but do not test.

g) (2) How could you rerun the regression taking into account the issues in the first regression? Which variable or variables would you keep as an explanatory variable(s), and why?

 Electricity Natural Gas Gasoline Fuel Oil 2.54 1.29 0.39 0.21 3.51 1.71 0.57 0.31 4.64 2.98 0.86 0.44 5.36 3.68 1.19 0.61 6.2 4.29 1.31 0.76 6.86 5.17 1.22 0.68 7.18 6.06 1.16 0.65 7.54 6.12 1.13 0.69 7.79 6.12 1.12 0.61 7.41 5.83 0.86 0.34 7.41 5.54 0.9 0.42 7.49 4.49 0.9 0.33

1. (15+2 for Excel work) Consider the following data for the average cost of various fuels and
electricity for a twelve year period. The data measure the following:
Electricity
Natural Gas
Fuel Oil
Gasoline

Residential rate per kilowatt hour
Residential natural gas per 1000 cubic feet
Residential fuel oil per gallon
Regular gasoline per gallon

Your model will attempt to predict residential electricity costs using the cost of the other fuels.
a) (1) State whether you would expect a positive or negative relationship between electricity costs
and each of the independent variables and explain why.
b) (2) Use EXCEL, to create a correlation using all independent variables provided. Discuss all
aspects of the correlation matrix, as it compares to your expectations in part (a) and what it tells
you about the potential results of a regression analysis.
c) (3) Use EXCEL to run a multiple regression to estimate electricity costs using all independent
variables provided. Test if the overall regression is significant at the 5% level.
d) (3) Test for the significance of each slope coefficient at the 5% level. (Incorporate all tests into
ONE five step answer as in the slides.)
e) (2) Based on your correlation matrix and the results of your regression do you suspect
multicollinearity? If yes, which independent variable(s) to you think could be responsible?
f) (2) Explain how you test for multicollinearity but do not test.
g) (2) How could you rerun the regression taking into account the issues in the first regression?
Which variable or variables would you keep as an explanatory variable(s), and why?

Electricit Natural
Gasolin Fuel
y
Gas
e
Oil
2.54
1.29
0.39
0.21
3.51
1.71
0.57
0.31
4.64
2.98
0.86
0.44
5.36
3.68
1.19
0.61
6.2
4.29
1.31
0.76
6.86
5.17
1.22
0.68
7.18
6.06
1.16
0.65
7.54
6.12
1.13
0.69
7.79
6.12
1.12
0.61
7.41
5.83
0.86
0.34
7.41
5.54
0.9
0.42
7.49
4.49
0.9
0.33

1