Excel

Regression Exercise 1

1. Today’s exercise will teach you a simple way to run a regression in Excel. You will also learn how to compute the coefficients b and a with formulas.

2. This exercise picks up where we left off last week, when we computed correlation coefficients. One of the correlation coefficients we computed was the correlation between vehicle miles traveled per capita and the real price of gasoline. Today we will run the following regression : Vehicle miles traveled per capita = α + β × gas price + ε

3. You will see the data when you open the Excel file. Your first task is to create a scatterplot with vehicle miles traveled on the y axis and the gas price on the x axis. You have done this before (last week), so it should be familiar.

4. You should see the following chart:

5. Does it look like there is a relationship between gas prices and vehicle miles traveled? What would you expect it to be?

6. Try modifying the chart by changing the scale of the axes. First right click on the y axis where the red X is and choose format axis In the dialog box that pops up click on the button next to minimum and enter 7900 . Follow the same steps and set the minimum of the x axis to 1.50 .

7. You should see the following: Now do you detect a relationship between vehicle miles traveled and gas price?

8. The easy way to estimate b and a is to fit a trendline to the scatterplot. Right click on any of the blue data points in the chart and choose add trendline… You should see the following: Select Close and you should see Now do you think there is a relationship between vehicle miles traveled and gas prices?

9. To estimate b and a, right click on the trendline and choose format trendline… In the box that pops up, click the box next to Display equation on chart

10. You should see this:

11. Your estimate of beta (b) is -657.48 and your estimate of alpha (a) is 12,147. Do you think there is a relationship between vehicle miles traveled and gas prices? How do you interpret a and b?

12. If you want to, clean up your chart to make it look pretty:

13. So you now know how to estimate a simple regression in Excel. Unfortunately this trick only works when you have one independent variable. Also, we have no idea if our estimates are any good from a statistical standpoint. Is it just random luck that we found the expected negative relationship between gas prices and vehicle miles traveled? Later on we will learn how to conduct hypothesis tests for the estimated coefficients.

14. Now we will use our results to predict the effect of higher gas prices on driving. Use your results to predict how many miles the average Californian would drive in a given year if the price of fuel were $6 a gallon? What about $1, $10 or even $20 a gallon? At what point do you start to lose faith in the estimates if you had any to begin with?

15. If you still have time, calculate the coefficients on your own. There are two ways to do that. Either use the long formula that is based on the differences between each x or y value and its respective mean. Or you can use the simpler formula that is based on the correlation coefficient and the standard deviations of x y. If you need a reminder about how those functions work in Excel consult earlier exercises.