Question

Analyze: There is considerable debate within your firm concerning the effect of advertising on sales. The marketing department believes advertising has a large positive effect; others are not so sure. For instance, the production and technical staffs believe the quality of the product itself largely determines sales. To clarify the debate, you have gathered the attached data for the last 24 quarters. Also refer to an Introduction to Regression in MS-Excel as needed.

a. Does advertising affect unit sales? Base your written explanation on the results of a regression analysis.

b. Others in the company argue that the last quarter’s sales best predict this quarter’s sales. Test this hypothesis via regression analysis.

Compare the performance of the regressions in parts (a) and (b).

c. Some believe the impact of advertising takes time (as long as three months) to affect sales. Perform a regression to test this hypothesis and interpret the results.

d. Provide your best estimate and your calculation of the own-advertising elasticity of demand from your analysis. Evaluate the elasticity at the mean value of the independent variable(s).

e. Because of a recent, new marketing plan, some at your firm argue that the effectiveness of advertising has changed over time and especially 2012-2013 compared to 2016-2017. Using dummy independent variables in your regression, test this hypothesis and take a justifiable stand on the argument.

Year	Quarter	Unit Sales	Advertising	Dum12_13	Dum16_17
2012	1	120	39	1	0
2012	2	115	36	1	0
2012	3	97	38	1	0
2012	4	118	39	1	0
2013	1	88	23	1	0
2013	2	63	22	1	0
2013	3	82	40	1	0
2013	4	80	42	1	0
2014	1	95	36	0	0
2014	2	106	49	0	0
2014	3	105	66	0	0
2014	4	136	65	0	0
2015	1	122	51	0	0
2015	2	112	56	0	0
2015	3	116	60	0	0
2015	4	104	51	0	0
2016	1	137	55	0	1
2016	2	114	47	0	1
2016	3	104	50	0	1
2016	4	122	47	0	1
2017	1	108	32	0	1
2017	2	94	41	0	1
2017	3	98	45	0	1
2017	4	104	34	0	1

Answer 1

a).

So, here we have to regress “Sales” on “Advertising”. Consider the following regression result.

SUMMARY OUTPUT Regression Statistics Multiple F R Square Adjusted R Square Standard Error Observations 0.60927623 0.37121753 0.34263651 13.9873735 24 ANOVA df Significance F 1 2541.107729 2541.10773 12.9882527 0.001576508 MS Regression Residual 22 4304.225604 195.646618 23 6845.333333 Coefficients Standard Errort Stat P-value Intercept Advertising Lower 95% Upper 95% Lower 95.0% Upper 95.0% 65.2561538 11.61554639 5.61800122 1.1967E-05 41.16698501 89.3453227 41.16698501 89.34532268 0.915274730.253966307 3.60392186 0.00157651 0.3885808411.44196861 0.388580841 1.44196861

So, the estimated regression equation is given by

=> Sales = 65.2562 + 0.9153*Advertise, => if “Advertising” increases by “$1”, => the “Sales” will also increase by “$0.92”. Now, the “p-value” of the coefficient of “Advertising” is “0.0016=0.16% < 1%”, => “Advertising” is a significant at “1%” level of significance.

b).

Now, here we have to regress “Sales” on “Advertising” only for the last quarter. Consider the new data set and the regression result.

Year QuarterUnit Sales Advertising Dum1 13 Dum16 17 118 80 136 104 122 104 39 2012 2013 2014 2015 2016 2017 4 4 4 42 4 47 4 34

SUMMARY OUTPUT Regression Statistics Multiple F R Square Adjusted R Square Standard Error Observations 0.58136294 0.33798287 0.17247859 17.5138403 6 ANOVA df MS Significance F Regression Residual Total 1 626.3949235 626.394923 2.04213975 0.226200943 4 5 1853.333333 1226.93841 306.734602 Coefficients Standard Error t Stat Lower 95% Upper 95% Lower 95.0% Upper 95.0% 63.1399776 34.01779515 1.85608671 0.13701993 -31.30856324 157.588518-31.30856324 157.5885184 1.02575588 0.717796416 1.42903455 0.22620094-0.967166467 3.01867823 -0.967166467 3.018678225 P-value Intercept Advertising

So, the estimated equation is given by, => Sales = 63.14 + 1.03*Advertise, => if “Advertising” increases by “$1”, => the “Sales” will also increase by “$1.03”, => approximately by “$1”. Now, the “p-value” of the coefficient of “Advertising” is “0.2262 = 22.62% > 5%”, => “Advertising” is not a significant at “5%” level of significance. So, here we can’t say that the “last quarter sales” will better predict the “Sales”.

Now, if we compare the regression result of “part a” and “part b” then we can see that the “part a” has higher “R^2”, => “part a” shows better result compare to “part b”.

c).

Some people believe that the impact of “Advertising” takes time to affects sales, => here we have to introduce new variable “Advertising-1” will takes the value of previous quarter’s adverting values, => the new data set and regression result is given by.

Unit Sales Advertising-t 115 97 118 39 36 38 39 23 63 82 30) 40 42 36 49 106 105 136 122 112 116 104 137 114 104 122 108 94 98 104 65 51 56 60 51 47 50 47 32 41 45

SUMMARY OUTPUT Regression Statistics Multiple F R Square Adjusted R Square Standard Error Observations 0.75035621 0.56303444 0.54222656 11.7507151 23 ANOVA Significance F 1 3736.247607 3736.24761 27.0587078 3.72754E-05 df MS Regression Residual Total 21 2899.665437 138.079307 22 6635.913043 Coefficients Standard Errort Stat Lower 95% Upper 95% Lower 95.0% Upper 95.0% 54.5774061 10.03869798 5.436701670.000022 33.7007908 75.4540214 33.7007908 75.45402142 1.130795790.217385541 5.201798520.000037 0.678717805 1.58287377 0.678717805 1.582873767 P-value Intercept Advertising-1

So, the estimated equation is given by, => Sales = 54.58 + 1.13*Advertise, => if “Advertising-1” increases by “$1”, => the “Sales” will also increase by “$1.13”. Now, the “p-value” of the coefficient of “Advertising-1” is “0.000037 < 1%”, => “Advertising” is significant at “1%” level of significance. Now, the “R^2” is also larger compare to “part-a”, => we can say that “Advertising-1” has much more effect compare to “Advertising”.

d).

Now, to get the elasticity we need the “log values” of the data set. So, the new data set is given below.

1 Year Quarter Unit Sales Log(Sales) Advertising Log Advertising) 136 122 104 104 1 122 3 4 104

SUMMARY OUTPUT Regression Statistics Multiple F R Square Adjusted R Square Standard Error Observations 0.6454037 0.41654593 0.39002529 0.05934722 24 ANOVA df MS Significance F Regression Residual Total 1 0.055319674 0.05531967 15.7064815 0.000659823 22 0.077486025 0.00352209 23 0.132805699 Coefficients Standard Errort Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 1.359967220.166625487 8.16181991 4.2268E-08 1.014407108 1.70552733 1.0144071081.705527328 Intercept Log(Advertising) 0.403703230.101864481 3.96314036 0.00065982 0.192449231 0.61495724 0.192449231 0.614957237

So, here the estimated regression equation is given by, “Log(Sales) = 1.36 + 0.4037*Log(Advertising)”, => the elasticity is “0.40”, => as “Advertising” increases by “1%”, => the “Sales” also increases by “0.4%”.