DEMAND ESTIMATION Soft Drinks Demand can be estimated with e

DEMAND ESTIMATION: Soft Drinks

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

Solution

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

Cans = 514.2669-242.9708*6-Pack Price+1.3602*Income/Capita+2.9312*Mean Temp

------------------------------------------------------------------------------------------------------------------------

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

When 6-Pack Price variable increases one unti, and fixed other variables, the regression line decreases 242.9708

When Income/Capita variable increases one unti, and fixed other variables, the regression line increases 1.3602

When Mean Temp increases one unti, and fixed other variables, the regression line increases2.9312

------------------------------------------------------------------------------------------------------------------------

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%)

Since the p-value of F test is closed to 0, we can conclude that the regression estimated function is a good fit. Since R^2=0.698, 69.8% of variance can be explained by the regression line. Yes, we may use the estimated function to predict for the future demand because the regression estimated function is a good fit

------------------------------------------------------------------------------------------------------------------------

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= 68F? (20%)

Cans = 514.2669-242.9708*1.95+1.3602*23.5+2.9312*68 =271.7601

------------------------------------------------------------------------------------------------------------------------

5. Now omit the price and temperature from the regression equation. Should a marketing plan for soft drinks be designed that relocates most canned drink machines into low-income neighborhoods? Why or why not? (20%)

No, because it is no much different.