Question

Show your work in Excel

A household- appliance manufacture wants to analyze the relationship between total sales and television advertising expenditures. The following data was collected. All data are in millions of dollars.

TV ad $M spent	Total Sales $M
8.3	361.1
6.3	344
9.9	377.9
9.4	371.5
10.4	365.4
9.0	364.5
9.2	372.9
10.6	379.4
9.3	362.6
10.5	387.5

a. Which variable is the dependent/response variable?

b. Which is the independent/explanatory variable?

Enter the data into your Excel file

c. Develop a scatterplot for these data. Interpret the scatterplot.

Run a regression analysis of the data on Excel using Data/Data Analysis

d. What is the correlation between $M spent on TV ads and total sales?

            What does it say about the strength of the relationship?

e. What is the coefficient of determination?

            How would you interpret it?

f. Write the hypotheses for the test of the slope.

g. What do you conclude about the slope of the line?

            What are you basing your conclusion on?

h. What is the regression formula that represents the relationship between $M spent on TV ads and total sales?

i. Use your formula to predict the total sales if $7.5M was spend on TV ads.

j. Use your formula to predict the total sales if $11.0M was spend on TV ads.

Answer 1

a. Here analysis is done on to find relation between total sales and television advertising expenditures.

Which means based on television advertising expenditures we are trying to predict total sales

So response variable here is total sales

b. Based on a. independent variable is television advertising expenditures.

c.

As the flow is in increasing trend and all the points are nearby, so there is strong positive correlation between both.

d.

X Values
∑ = 92.9
Mean = 9.29
∑(X - M_x)² = SS_x = 14.809

Y Values
∑ = 3686.8
Mean = 368.68
∑(Y - M_y)² = SS_y = 1311.636

X and Y Combined
N = 10
∑(X - M_x)(Y - M_y) = 121.178

R Calculation
r = ∑((X - M_y)(Y - M_x)) / √((SS_x)(SS_y))

r = 121.178 / √((14.809)(1311.636)) = 0.8695

As r is near to 1, there is strong positive correlation between amount spent on advertisement and sales.

e. Here r=0.8695 so R^2=0.7560

Hence 75.60% of variation of sales are been explained by amount spent.