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.

Given the data, please construct the demand estimation for soft drink consumption in the United States by
(1) a multiple-linear regression equation, and
(2) a log-linear (exponential) regression equation (show Excel please)

TABLE 1. SOFT DRINK DEMAND DATA
State	Cans/Capita/Yr	6-Pack Price ($)	Income/Capita ($1,000)	Mean Temp. (F)
Alabama	200	3.19	35.1	66
Arizona	150	2.99	45.9	62
Arkansas	237	2.93	29.7	63
California	135	3.59	67.5	56
Colorado	121	3.29	51.3	52
Connecticut	118	3.49	72.9	50
Delaware	217	2.99	75.6	52
Florida	242	3.29	48.6	72
Georgia	295	2.89	37.8	64
Idaho	85	3.39	43.2	46
Illinois	114	3.35	64.8	52
Indiana	184	3.19	54	52
Iowa	104	3.21	43.2	50
Kansas	143	3.17	45.9	56

Answer 1

(1) a multiple-linear regression equation,

The Excel regression output is:

	R²	0.768
	Adjusted R²	0.698
	R	0.876
	Std. Error	34.313
	n	14
	k	3
	Dep. Var.	Cans/Capita/Yr
ANOVA table
Source	SS	df	MS	F	p-value
Regression	38,917.5555	3	12,972.5185	11.02	.0016
Residual	11,773.9445	10	1,177.3944
Total	50,691.5000	13
Regression output					confidence interval
variables	coefficients	std. error	t (df=10)	p-value	95% lower	95% upper
Intercept	315.9683
6-Pack Price ($)	-160.9033	53.7244	-2.995	.0135	-280.6087	-41.1980
Income/Capita ($1,000)	1.1644	0.8234	1.414	.1877	-0.6703	2.9991
Mean Temp. (F)	5.4509	1.4716	3.704	.0041	2.1718	8.7299

The estimated regression equation is:
Cans/Capita/Yr = 315.9683 -160.9033*6-Pack Price ($) + 1.1644Income/Capita ($1,000) + 5.4509Mean Temp. (F)

(2) a log-linear (exponential) regression equation

The exponential regression between Cans/Capita/Yr and 6-Pack Price ($) is:

The estimated regression equation is:

y = 3.6085e^-7E-04x

The exponential regression between Cans/Capita/Yr and Income/Capita ($1,000) is:

The estimated regression equation is:

y = 64.722e^-0.002x

The exponential regression between Cans/Capita/Yr and Mean Temp. (F) is:

The estimated regression equation is:

y = 43.278e^0.0016x