This computer lab assignment is designed to give you experience estimating demand functions using Microsoft Excel. The market demand for gasoline using actual data from the United States for 1990-2019 is estimated. In the process, you will gain greater familiarity with the Microsoft Excel regression tool including the interpretation of regression results and with the estimation of demand functions using different functional forms.

Several variables are important in determining gasoline demand. First and foremost, of course, is the own-price of gasoline. Also important are income, prices for cars (gasoline and cars are complements), and prices of substitutes for cars (public transportation). The variables are defined as follows:

QGd = total quantity demanded for gasoline, measured in billions of gallons,

PG = own-price of gasoline, as measured by the price index for gasoline,

M = per capita real disposable income,

Pnc = price index for new cars,

Puc = price index for used cars,

Ppt = price index for public transportation.

Thus, the general form is:

QGd = (PG, M, Pnc, Puc, Ppt).

(1) Do you expect PG, M, Pnc, Puc, and Ppt to be positively or negatively related to QGd? Explain. (Note: your expectations may not be matched by your regression results later on.

+ or - Brief Explanation/Reasoning based on Demand Theory

(a) PG:

(b) M: ______

(c) Pnc: ______

(d) Puc: ______

(e) Ppt: ______

Follow these steps to estimate the linear demand function:

i. A regression equation will be estimated using Excel to judge the determinants of gasoline demand. It uses a linear additive form with quantity demanded, QGd, as the dependent variable and PG, M, Pnc, Puc, and Ppt as the explanatory/independent variables plus a constant:

QGd = b0 + b1 PG + b2 M +b3 Pnc + b4 Puc + b5 Ppt

ii. First, download the file LesterGas1990-2019.xlsx from D2L and load the data into Excel by clicking on the file.

iii. Clear the contents of rows 34-39, which provide the variable descriptions included in this document. You will not need them for the regression analysis.

iv. In Excel, click on the Data tab at the top. Then, click on the Data Analysis button in the Analysis group. (Note: If you do not see a Data Analysis button, you will need to install the Analysis Toolpak Add-in. Visit https://www.excel-easy.com/data-analysis/analysis-toolpak.html and/or watch https://youtu.be/AycQedgKAfY for instructions.)

v. Once you have clicked on the Data Analysis button in the Analysis group, scroll down through the list of Analysis Tools until you find Regression and double click on it. (For a brief video overview of performing regression analysis in Excel, watch the following: https://youtu.be/0lpfmFnlDHI.)

vi. You will now need to inform Excel about your variables. Y is considered to be the dependent variable by Excel while X represents the independent variable(s). Before you begin, click on the Labels box. To Input Y Range, click in the box to the right and then highlight the B column (including the variable name in the top cell) which contains the dependent variable, QG.

vii. Now, Input X Range by clicking in the box to the right and highlighting columns C through G (including the variable names in the top cells) which contain the independent variables.

viii. Under Output Options, make sure that New Worksheet Ply is checked and click OK to run the regression. A new worksheet containing the regression Summary Output will appear.

(2) Refer to the Excel regression output* and write down the values for the estimated coefficients (and t-statistics in parentheses):

b0 = ( ) ...the constant (intercept)

b1 = ( ) ...PG

b2 = ( ) ...M

b3 = ( ) ...Pnc

b4 = ( ) ...Puc

b5 = ( ) ...Ppt

R2 = Adjusted R2 = _______

*In addition, please provide the Excel regression output in D2L. (2.5 points)

(3) Do these signs match your expectations recorded on page 1? If so, which ones? What do they tell you about the demand for gasoline? If not, what you have learned about the demand for gasoline? Explain. (1 point)

(4) Which estimated coefficients are statistically significant? Which are not? Name them

and briefly explain your answers. (2 points)

(5) What does the value you obtained for R2 say about your regression (make a specific

numerical reference based on the regression output provided)? Can you think of any other independent variables that might be included in the regression? Name them. (2 points)

Follow these steps to transform the data and to estimate the log-linear demand function:

i. One could also use a multiplicative (nonlinear) form for estimating gasoline demand:

QGd = b0 PGb1 Mb2 Pncb3 Pucb4 Pptb5,

which, after taking the natural log of each side, becomes:

QGd ' = b0' + b1 PG' + b2 M' +b3 Pnc' + b4 Puc' + b5 Ppt'

where, the prime (') indicates that the variable is the natural log of the data. For example, M' = ln (M). The values for the bi can be interpreted as the various elasticities of demand. For example, b1 is the own-price elasticity of demand for gasoline.

ii. In order to estimate a log-linear regression equation, it is first necessary to convert the data for QGd (the dependent variable), PG, M, Pnc, Puc, and Ppt to their natural logs:

a. In H1 enter lnQG

b. In I1 enter lnPG

c. In J1 enter lnM

d. In K1 enter lnPnc

e. In L1 enter lnPuc

f. In M1 enter lnPpt

g. In H2 enter =ln(B2)

h. Copy the formula to columns I through M. [Click on H2. Hold onto the lower right dot of H2 and then drag the mouse to M2. Let go of the mouse. The formula in H2 will be copied to I2, J2, K2, L2, and M2. While the cells H2 through M2 are still highlighted, drag the mouse to cell M31. Cells H2 through M31 now contain the converted data.]

iii. In Excel, click on the Data tab at the top. Then, click on the Data Analysis button in the Analysis group.

iv. Once you have clicked on the Data Analysis button in the Analysis group, scroll down through the list of Analysis Tools until you find Regression and double click on it.

v. Click on the Labels box. To Input Y Range, click in the box to the right and then highlight column H (including the variable name in the top cell) which contains the logged dependent variable, lnQG.

vi. Now, Input X Range by clicking in the box to the right and highlighting columns I through M (including the variable names in the top cells) which contain the logged independent variables.

vii. Under Output Options, make sure that New Worksheet Ply is checked and click OK to run the regression. A new worksheet containing the regression Summary Output will appear.

(6) Refer to the Excel regression output* and write down the values for the estimated coefficients (and t-statistics in parentheses):

b0 = ( ) ... ln (the constant)

b1 = ( ) ... ln (PG)

b2 = ( ) ... ln (M)

b3 = ( ) ... ln (Pnc)

b4 = ( ) ... ln (Puc)

b5 = ( ) ... ln (Ppt)

R2 = Adjusted R2 = ______

*In addition, please provide the Excel regression output in D2L. (2.5 points)

(7) Based on the log-linear regression results, if the own-price of gasoline increases by 10%, what happens to the quantity demanded for gasoline (put in terms of percentage change)? Does it increase or decrease and by what percentage? Show any necessary calculations to support your answer and explain. (Hint: In addition, consider the sign, size and statistical significance of the estimated coefficient on ln (PG).) (3 points)

(8) Based on the log-linear regression results, would you expect a change in income to affect

the demand for gasoline much (make a specific numerical reference)? Explain and

support your answer. (Hint: Consider the sign, size and statistical significance of the

estimated coefficient on ln (M).) (2 points)