Question

Times New . | | 12 A ^ Av-- Wrap Text , Paste E35 8 Month Advertising Exp 9 January S 0 February 1 March 2 April 13 May 4 June Revenue 9,992 $ 54,207 11,110 6,527 10,280 4,780 5,428 12,980 7,920 6,780 8,550 75,260 87,880 64,280 79,990 60,287 64,010 85,675 70,900 61,888 73,550 87,790 6 August 7 September 8 October 9 November 0 December 11,858 6,415 2 Required regression analysis output in Excel when comparing each month's advertising expense to the same month's revenue Include a plot/graph f the regression line in Excel showing the equation/function and R-squared. What do you conclude based upon the regression analysis? s 2. The firm realizes that there may be a lag between the adv ertising incurred and the timing of sales revenue. They modify the analysis by comparing cach revenue to the prior month's advertising expense. Generate another regression analysis after discarding January sales revenue and December advertising expense in order to compare each month's Include a plotgraph of the regression line in Excel showing the cquaion/fiunction and R-squared sales to the prior month's advertising expense. What would be the function (equation) for this regression li 3. Is the coefficient for advertising expense s tatistically signifiant requirements #1 and m refly explain how you canne to your conclusion. 4. Whatproportion ofchanges in sales revenue is explained by the amount of advertising expense in both requirements #1 and #2? Briefly explain how you came to your conclusion. Sheet1 Question 1 Sheet3+

Answer 1

1)

Comment: From the regression analysis, we can conclude that increasing the advertising expenditure decreases the revenue.

2)

Comment: From the above regression analysis, we can conclude that increased the revenue by increasing the prior month's advertising expenditure.

3)

The analysis of data based on 1) is

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.3774
R Square	0.1424
Adjusted R Square	0.0566
Standard Error	11135.1715
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	205894461.8	205894461.8	1.660546	0.226543452
Residual	10	1239920451	123992045.1
Total	11	1445814913
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	86007.3892	11228.94383	7.659437121	1.72E-05	60987.7432	111027.035
Advertising Exp	-1.6212402	1.258119653	-1.28862164	0.226543	-4.424505492	1.18202507

Comment: The estimated p-value for the Advertising Expenditure is 0.2265. It is more than 0.05 level of significance. Hence, the Advertising Expenditure does not statistically significant at 0.05 level of significance.

The analysis of data based on 2) is

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9580
R Square	0.9177
Adjusted R Square	0.9085
Standard Error	3164.5380
Observations	11
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1004737388	1E+09	100.3303	3.52947E-06
Residual	9	90128704.55	10014301
Total	10	1094866093
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	41405.2796	3369.42253	12.28854	6.29E-07	33783.11633	49027.443
X Variable 1	3.70097109	0.369487475	10.0165	3.53E-06	2.865132355	4.53680983

Comment: The estimated p-value for the Advertising Expenditure is 0.0000. It is less than 0.05 level of significance. Hence, the Advertising Expenditure is statistically significant at 0.05 level of significance.

4) The R-square value for 1 is 0.1424. Hence, the proportion of change in sales revenue which is explained by the amount of advertising expense is 14.24%.

The R-square value for 2 is 0.9177. Hence, the proportion of change in sales revenue which is explained by the amount of advertising expense is 91.77%.