Question

Suppose the Kalamazoo Brewing Company (KBC) currently sells its microbrews in a seven-state area: Illinois, Indiana, Michigan, Minnesota, Mississippi, Ohio, and Wisconsin. The company’s marketing department has collected data from its distributors in each state. This data consists of the quantity and price (per case) of microbrews sold in each state, as well as the average income (in thousands of dollars) of consumers living in various regions of each state. The data for each state are available via the link below--please note there are multiple tabs at the bottom of the spreadsheet, each refers to one of the seven states selling the Kalamazoo Brewing Company’s microbrews.

Excel Data File

Assuming that the underlying demand relation is a linear function of price and income, use your spreadsheet program to obtain least squares estimates of Michigan’s demand for KBC microbrews.

Instruction: If the estimate is negative, enter a negative number (-) in the equation. Enter your responses rounded to two decimal places.

Q = _____ + _____Price + ______Income

Quantity	Price	Income
210	33.19	35.34
198	32.83	34.59
195	31.7	32.61
204	28.75	35.12
218	25.79	32.2
178	35.25	25.69
198	24.71	28.19
194	32.09	36.99
205	28.73	37.09
217	24.97	34.85
181	29.89	34.61
196	29.35	32.9
193	34.28	36.14
185	31.02	32.93
175	42.08	31.65
190	21.05	35.19
199	29.25	36.98
191	31.45	30.2
228	24.66	35.42
180	28.82	28.99
205	28.75	40.29
190	39.93	30.94
192	39.79	39.31
199	35.61	38
201	30.15	30.14
217	30	37.43
188	41.05	34.15
187	35.83	25.26
191	35.63	32.22
190	31.11	34.51
200	27.78	34.87
217	27.74	40.66
219	33.94	45.71
205	28.05	41.37
206	32.73	32.22
230	22.12	31.61
200	27.37	38.76
209	31.31	32.35
211	28.94	43.18
209	31.07	33.53
190	34.56	31.7
209	24.13	38.71
196	27.51	27.8
181	32.22	33.2
176	31.84	28.42
176	33.7	29.33
200	39.6	29.14
215	37.53	40.9
206	36.11	37.64
201	23.74	36.1

Answer 1

Excel regression summary output as follows.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.63
R Square	0.40
Adjusted R Square	0.37
Standard Error	10.59
Observations	50
ANOVA
	df	SS	MS	F
Regression	2	3474.75	1737.38	15.51
Residual	47	5266.23	112.05
Total	49	8740.98
	Coefficients	Standard Error	t Stat	P-value
Intercept	182.44	16.25	11.23	0.00
Price	-1.02	0.31	-3.28	0.00
Income	1.41	0.35	4.09	0.00

Estimated regression equation:

Quantity = 182.44 - 1.02 x Price + 1.41 x Income