Question

The following data represent a company's yearly sales volume and its advertising expenditure over a period of 8 years.

Sales in Millions of Dollars	Advertising in ($10,000)
11	32
16	33
18	35
17	34
16	36
19	37
19	39
24	42

a.	Which variable is the dependent variable?
b.	Using the Excel spreadsheet, run the least squares model and write the summary output here.
c.	If the company's advertising expenditure is $400,000, what are the predicted sales? Give the answer in dollars.
d.	What does the slope of the estimated regression line indicate?
e.	Compute the coefficient of determination and fully interpret its meaning.
f.	Use the F test to determine whether or not the regression model is significant at a = 0.05.
g.	Use the t test to determine whether the slope of the regression model is significant at a = 0.05.
h.	Develop a 95% confidence interval for predicting the average sales for the years when $400,000 was spent on advertising.
i.	Compute the correlation coefficient.

Answer 1

a) Sales are depends on Advertising.

So here Sales in millions of dollar is dependent variable.

b) Excel output is given by,

Data tab > Data analysis > regression.

Insert ranges of x and y . So we get the output as :

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.899172
R Square	0.808511
Adjusted R Square	0.776596
Standard Error	1.732051
Observations	8
ANOVA
	df	SS	MS	F	Significance F
Regression	1	76	76	25.33333	0.002373
Residual	6	18	3
Total	7	94
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-18.5	7.178642	-2.57709	0.041933	-36.0655	-0.9345
Advertising in($10000)	1	0.19868	5.033223	0.002373	0.513848	1.486152

c) From output we get :

Slope = b= 1 , Intercept = a = -18.5

Hence the regression equation is given by,

y = a + b * x

y = -18.5 + 1 * x

Here expenditure = x = 400,000

y = -18.5 + 1 * 40= 21.5 millions of dollar.

d) Here from output : Slope = b =1

So this tells us that, as expenditure in advertising increases by 1 unit , sales will increase by b= 1 units.

e) Coefficient of determination = R² = 0.8085

80.85% of the variations in Advertising are explained by regression equation.

f) The hypothesis are :

H0: The model is not significant. v/s H1 : The model is significant.

From output we get :

The test statistic,

F = 25.333

P value = 0.0024

Here p value < ( 0.05 ).

Hence we reject null hypothesis.

We conclude that model is significant.

g)

The hypothesis are :

H0: = 0 v/s H1 : 0

From output we get,

The test statistics is,

t = 5.033

P value = 0.0024

Here p value < ( 0.05 )

Hence we reject null hypothesis.

Conclusion : Slope of regression model is significant.

h) x = 40, = 21.5

The 95% confidence interval is given by,

Here c = 0.95, = 1-c = 1-0.95 = 0.05, df = n-2= 8-2 = 6

-----( Using excel formula "=t.inv.2t(0.05,6) " )

From output we get MS_error = 3, SS_x = 76

n= 8,

So the confidence interval is ,

21.5 2.46

( 19.04, 23.96 )

i) From output we get,

correlation coefficient = r = 0.899