Question

A fast-food chain decided to carry out an experiment to assess the influence of advertising expenditure on sales. Different relative changes in advertising expenditure, compared to the previous year, were mad in eight regions of the country, and resulting changes in sales levels were observed. The companying table shows the results

Increase in advertising expenditure (%)	0	4	14	10	9	8	6	1
Increase in Sales (%)	2.4	7.2	10.3	9.1	10.2	4.1	7.6	3.5

Use Excel’s Data Analysis to get the descriptive statistics for each variable.

Calculate the sample covariance and correlation. Show your work by constructing a table as on page 74 of the course packet. You may construct the table in Excel and use “=sum” function to get the sum (xi - x bar)sqr, sum((yi-y bar)sqr and sum(xi- x bar) (yi - y bar) for the formulas of the sample covariance and the correlation.

Use Excel’s functions “=covariance.s()” and “=correl()” to get the sample covariance and the correlation. Compare your results with part b.

Determine the dependent and the independent variable for the regression model. Write down the regression model.

Use Excel to draw the scatter diagram with the fitted line. Label your horizontal and vertical axes.

Use the table in part b to calculate the intercept and slope for the regression.

Write the estimated regression equation.

Interpret the estimated slope and intercept.

Predict the percent in sales if the increase in advertising expenditure is 5%.

Calculate the standard error of the regression. Show your work as the table on page 85 of the course packet. You may use Excel to construct the table and use “=sum” function.

Construct a table to show the calculation of SST, SSR, and SSE as the table on page 89 of the course packet and prove SST = SSR + SSE. You may use Excel to construct the table.

Use the value from part k to calculate the coefficient of determination and explain its meaning.

Use the values of summation (xi- xbar) sqr and Se from parts b and j to compute the standard error of the slope. Show your work as on page 93 of the course packet.

Construct a 95% confidence interval of the slope.

Pick an appropriate hypothesis for the slope and conduct a test with the 5% significance level.

Use Excel’s Data Analysis to get the regression printout.

In your Excel regression printout in part o, identify the intercept, the slope, the standard error of the regression, SST, SSR, SSE, the coefficient of determination, and the standard error of the slope. Compare these numbers with your results in parts f, j, k, l, and m.

Answer 1

A fast-food chain decided to carry out an experiment to assess the influence of advertising expenditure on sales. Different relative changes in advertising expenditure, compared to the previous year, were mad in eight regions of the country, and resulting changes in sales levels were observed. The companying table shows the results.

Use Excel’s Data Analysis to get the descriptive statistics for each variable.

Calculate the sample covariance and correlation.

increase in advertising expenditure		increase in sales
Mean	6.5	Mean	6.8
Standard Error	1.669046	Standard Error	1.0974
Median	7	Median	7.4
Mode	#N/A	Mode	#N/A
Standard Deviation	4.720775	Standard Deviation	3.103915
Sample Variance	22.28571	Sample Variance	9.634286
Kurtosis	-0.69068	Kurtosis	-1.72827
Skewness	0.048884	Skewness	-0.29225
Range	14	Range	7.9
Minimum	0	Minimum	2.4
Maximum	14	Maximum	10.3
Sum	52	Sum	54.4
Count	8	Count	8

Covariance = 10.51

Correlation = 0.8199

Determine the dependent and the independent variable for the regression model. Write down the regression model.

Here dependent variable is increase in sales and independent variable is increase in advertising expenditure.

Use Excel to draw the scatter diagram with the fitted line. Label your horizontal and vertical axes.

Use the table in part b to calculate the intercept and slope for the regression.

Write the estimated regression equation.

Intercept = 3.30

slope = 0.54

The regression equation is,

increase in sales = 0.54 + 3.30*increasing in advertising expenditure

Interpret the estimated slope and intercept.

SLope interpretation :

For one unit change in increase in advertising expenditure there will be 3.30 unit increase in increase in sales.

Intercept interpretation :

If we take  increase in advertising expenditure then increasing in sales will be 0.54 unit.

Predict the percent in sales if the increase in advertising expenditure is 5%.

This we can find by using regression equation.

increase in sales = 0.54 + 3.30*increasing in advertising expenditure

increase in sales = 0.54 + 3.30*5 = 17.02

Calculate the standard error of the regression

standard error of regression = 1.92

Construct a table to show the calculation of SST, SSR, and SSE

ANOVA
	df	SS	MS	F	Significance F
Regression	1	45.33852564	45.33853	12.30828	0.012697
Residual	6	22.10147436	3.683579
Total	7	67.44

Use the value from part k to calculate the coefficient of determination and explain its meaning.

Rsq = 0.6723 = 67.23%

It expresses the proportion of variation in increase in sales which is explained by variation in increase in advertising expenditure.