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.

data for MS

Quantity	Price	Income
365	35.8	37.77
341	27.62	35.37
377	25.71	35.67
419	26.59	43.91
387	32.83	37.68
387	25.05	36.26
392	33.49	36.38
323	29.81	29.11
409	28.69	39.77
388	29.03	37.16
376	30.68	35.37
360	29.84	33.84
291	25.52	30.06
338	26.61	33.2
347	29.55	32.48
363	33.62	36.24
358	28.3	36.17
350	26.9	32.81
383	34.82	37.07
319	31.37	27.08
415	29.22	41.36
340	24.3	33.82
377	29.94	38.41
356	33.37	37.06
347	25.85	33.81
393	26	36.5
396	25.7	40.09
377	26.87	34.68
372	25.52	32.69
342	33.33	32.78
389	42.07	42.16
360	23.13	32.05
364	33.67	34.43
351	26.36	32.81
415	25.65	37.39
379	24	38.9
425	33.06	45.55
347	30.68	33.19
374	30.36	35.13
346	29.4	33.21
335	28.37	33.35
333	36.13	30.35
293	25.3	27.51
410	29.65	39.52
333	32.4	31.98
396	26.47	39.38
307	31.6	33.17
415	36.1	40.35
354	33.21	35.48
334	28.74	31.35

Assuming that the underlying demand relation is a linear function of price and income, use your spreadsheet program to obtain least squares estimates of Mississippi’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

Answer 1

The quantity demanded is considered the dependent variable and price and income are considered as independent variable.

The linear regression equation for more than one independent variable is defined as

y = b₀ + b₁ x₁ + b₂ x₂ + -----+ b_n x_n

In this example we can define the demand function as

y = b₀ + b₁ price + b₂ income

The value of b₀ , b₁ and b₂ is estimated using multiple regression analysis in excel.

In excel, go to data, data analysis and select regression. The values are entered as follows.

The equation is

Q = 124.31 - 0.79 Price + 7.45 Income