Question

Demand can be estimated with experimental data, time-series data, or cross-section data. In this case, cross-section data appear in the Excel file. Soft drink consumption in cans per capita per year is related to six-pack price, income per capita, and mean temperature across the 48 contiguous states in the United States.
QUESTIONS

1. Given the data, please construct a multiple linear regression program by MS Excel. (20%)

2. Interpret each coefficient of independent variable in the soft drink demand estimated function in question 1. (20%)

3. Given your answer in question 1, please comment on whether the regression estimated function is a good fit or not. What is the interpretation of coefficient of determination (R-square)? May we use the estimated function to predict for the future demand? Explain why. (20%)

4. How many cans/capita/year on soft drink should be for a state in which 6-pack price=$1.95, Income/Capita=$23,500, and Mean Temp= 68

Answer 1

Answer:

a. To construct a multiple linear regression model for the given data using MS EXCEL:

Statistics homework question answer, step 1, image 1

Denote the dependent variable Soft drink consumption in cans per capita year as Y.

Denote the independent variable Six­-pack price as X1, Income per capita as X2 and Mean temperature as X3.

Software Procedure:

Step-by-step software procedure to perform a multiple linear regression model using EXCEL software is as follows:

Open an EXCEL file.

Enter the data of Cans/Capita/Yr in columns A and name it as Y.

Enter the data of 6-Pack price, Income Capita and Mean Temp in columns A, B and C and name them as X1, X2, and X.

Go to Data > Data Analysis > Regression.

Enter Input Y Range as $A$1:$A$49, enter Input X Range as $B$1:$A$D49.

Select Labels, Confidence Level and enter the confidence level as 95%.

b. Interpretation the coefficients of three independent variables:

The slope bi in a multiple regression equation is the amount of change in the response variable, y, due to unit increase in the corresponding predictor variable, xi.

The ‘Coefficients’ column of the regression analysis output gives the slopes corresponding to the respective variables stored in the column ‘Variable’.

The coefficient or slope of X1 in the regression model is b1 = -242.9708.

The interpretation of the coefficient b1 in the regression model is: The value of Soft drink consumption in cans per capita year (Y) decreases by 242.9708 units for one unit increase in Six­-pack price (X1), provided the effects of Income per capita (X2) and Mean temperature (X3) are constant.

The coefficient or slope of X2 in the regression model is b2 = 1.3602.