Question

one.xls:

(QdB)	(PB)	(AD)	(I)
3750	3.00	4000	5850
3750	3.00	4200	5900
4000	2.75	4400	5950
4500	2.25	4800	5900
3750	3.00	3800	5950
3000	3.25	3000	5900
4250	2.25	4000	6000
4250	2.50	4600	6100
4250	2.50	3800	6150
4000	2.75	3600	6150
4500	2.25	4400	6200
5000	2.00	4600	6250

I.          Students are expected to view the video, read and understand Units 2 & 4 before attempting this exercise.

Consider the data provided in your one.xls provided in your diskette. This worksheet contains data regarding the market for Beer. Information on Quantity demanded (Q), Price of the product (P), total Advertisement expenditure (AD) and the per-capita income (I) in the market are provided for 12 months. Answer the following questions by performing the necessary operations in EXCEL:

1.         Perform 3 simple linear regressions indicated below:                                                        

a.         Qd = a - b P. Is P a significant variable ? Interpret the R-squared value.

Calculated t:

Table value of t:

Reject Null ?

Interpret R-squared here:

b.         Qd = a + b AD. Is AD a significant variable ? Interpret the R-squared value.

Calculated t:

Table value of t:

Reject Null ?

Interpret R-squared here:

c.         Qd = a + b I.   Is I a significant variable ? Interpret the R-squared value.

Calculated t:

Table value of t:

Reject Null ?

Interpret R-squared here:

4.         A multi-variate linear demand equation is given below.

                                                          Q = a - b P + c AD + d I

For the given data, estimate the multiple regression provided above. Explicitly state the equation estimated in the space below:

5.         Interpret the R-squared for this problem. Is this number bigger or smaller than those generated in the previous single variable regressions in (i.), (ii.), (iii.). Why ?

6.Calculate the mean of all these variables. Predict Q at these mean values. Build a 95% confidence around this prediction.

7.From the results of your multiple regression above, calculate at the mean values, for the commodity in question the following:

a.         the own-price elasticity of demand. Is demand elastic ?

b.         the income elasticity of demand. Is the commodity a normal good ?

c.         the advertising elasticity. What will happen to demand if AD increases 5% from its mean value ?

8. Which of the variables are significant in this regression ? Test at 95%.

Indicate the calculated t values for each variable and tell me if that variable is significant:

                        Calculated t                 Reject Null (yes or no)

P

AD

I

9.         Economic theory tells us that Qd and P are inversely related. How will you statistically test the validity of this theory ?

Think of a one-tail test.

Answer 1

1.

a.

(QdB)	(PB)	(AD)	(I)
3750	3	4000	5850
3750	3	4200	5900
4000	2.75	4400	5950
4500	2.25	4800	5900
3750	3	3800	5950
3000	3.25	3000	5900
4250	2.25	4000	6000
4250	2.5	4600	6100
4250	2.5	3800	6150
4000	2.75	3600	6150
4500	2.5	4400	6200
5000	2	4600	6250
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.939225
R Square	0.882144
Adjusted R Square	0.870359
Standard Error	181.3873
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2462653	2462653	74.84964	5.8932E-06
Residual	10	329013.4	32901.34
Total	11	2791667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	7410.535	388.1262	19.09311	0.00000000	6545.73616	8275.334	6545.736	8275.334
(PB)	-1257.53	145.3523	-8.65157	0.00000589	-1581.39011	-933.66	-1581.39	-933.66
RESIDUAL OUTPUT
Observation	Predicted (QdB)	Residuals
1	3637.96	112.0401
2	3637.96	112.0401
3	3952.341	47.65886
4	4581.104	-81.1037
5	3637.96	112.0401
6	3323.579	-323.579
7	4581.104	-331.104
8	4266.722	-16.7224
9	4266.722	-16.7224
10	3952.341	47.65886
11	4266.722	233.2776
12	4895.485	104.5151

Qd = a - b P.

Qd = 7410.53 - 1257.53 P

Yes, P is a significant variable

R-squared = 0.88

t-value:

			t Stat
Intercept			19.09311
(PB)			-8.65157

Reject Null - Yes

Interpret R-squared -

R-squared is a statistical measure of how close the data are to the fitted regression line. It is the percentage of the response variable variation that is explained by a linear model. R-squared = Explained variation / Total variation

Here, 88% of data is explained by all the variability of the response data around its mean. Hence, fit is good.

b.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.781325
R Square	0.610469
Adjusted R Square	0.571516
Standard Error	329.7638
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1704225	1704225	15.671883	0.00269178
Residual	10	1087441	108744.1
Total	11	2791667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	907.277	807.9109	1.122991	0.2876786	-892.86071	2707.415	-892.861	2707.415
(AD)	0.774648	0.195679	3.958773	0.0026918	0.33864838	1.210647	0.338648	1.210647
RESIDUAL OUTPUT
Observation	Predicted (QdB)	Residuals
1	4005.869	-255.869
2	4160.798	-410.798
3	4315.728	-315.728
4	4625.587	-125.587
5	3850.939	-100.939
6	3231.221	-231.221
7	4005.869	244.1315
8	4470.657	-220.657
9	3850.939	399.061
10	3696.009	303.9906
11	4315.728	184.2723
12	4470.657	529.3427

Qd = a + b AD.  

Qd = 907 + 0.77 AD

Yes, P is a significant variable

R-squared = 0.61

t-value:

			t Stat
Intercept			1.122991 3.958773
(AD)

Reject Null - Yes

Interpret R-squared -

R-squared is a statistical measure of how close the data are to the fitted regression line. It is the percentage of the response variable variation that is explained by a linear model. R-squared = Explained variation / Total variation

Here, 61% of data is explained by all the variability of the response data around its mean. Hence, fit is moderate.

c.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.673369
R Square	0.453426
Adjusted R Square	0.398768
Standard Error	390.6217
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1265813	1265813	8.2957723	0.01637884
Residual	10	1525853	152585.3
Total	11	2791667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-10797.7	5167.825	-2.08941	0.0631919	-22312.3229	716.9414	-22312.3	716.9414
(I)	2.46988	0.857526	2.880238	0.0163788	0.55919227	4.380567	0.559192	4.380567
RESIDUAL OUTPUT
Observation	Predicted (QdB)	Residuals
1	3651.104	98.89558
2	3774.598	-24.5984
3	3898.092	101.9076
4	3774.598	725.4016
5	3898.092	-148.092
6	3774.598	-774.598
7	4021.586	228.4137
8	4268.574	-18.5743
9	4392.068	-142.068
10	4392.068	-392.068
11	4515.562	-15.5622
12	4639.056	360.9438

Qd = a + b I.  

Qd = -10798 + 2.47 I

yes, P is significant variable at 95% but not at 1%

R-squared = 0.45

t-value:

			t Stat
Intercept			-2.08941 2.880238
(I)

Reject Null - Yes at 95% CI

Interpret R-squared -

R-squared is a statistical measure of how close the data are to the fitted regression line. It is the percentage of the response variable variation that is explained by a linear model. R-squared = Explained variation / Total variation

Here, 45% of data is explained by all the variability of the response data around its mean. Hence, fit is not so good.

4.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.983037
R Square	0.966361
Adjusted R Square	0.953746
Standard Error	108.3448
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	3	2697758	899252.6	76.606462	3.1085E-06
Residual	8	93908.8	11738.6
Total	11	2791667
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-1912.71	2390.959	-0.79998	0.4468266	-7426.27024	3600.852	-7426.27	3600.852
(PB)	-687.623	154.376	-4.45421	0.0021274	-1043.61505	-331.632	-1043.62	-331.632
(AD)	0.37076	0.09309	3.982798	0.0040458	0.15609349	0.585427	0.156093	0.585427
(I)	1.044857	0.318012	3.285587	0.0110949	0.31151927	1.778194	0.311519	1.778194
RESIDUAL OUTPUT
Observation	Predicted (QdB)	Residuals
1	3619.874	130.1259
2	3746.269	3.731028
3	4044.57	-44.5697
4	4484.443	15.55721
5	3650.208	99.79235
6	3129.451	-129.451
7	4292.32	-42.3202
8	4447.356	-197.356
9	4202.991	47.00927
10	3956.933	43.06721
11	4477.69	22.3102
12	4947.896	52.10356

  Q = -1913 - 688 P + 0.37 AD + 1.04 I

Note: max. 4 questions at a time