Answer to Question #124405 in Statistics and Probability for name

Question

On the basis of the following data, the marketing manager wants to predict the sales
volume for the locality on the basis of # households, number of cars and marketing
expense
Sl.No. Sales Volume #Households number of cars marketing expense
1 15727 161 3 180
2 9328 99 1 150
3 13681 135 2 175
4 12379 120 2 165
5 15351 164 3 178
6 24174 221 5 220
7 20154 179 4 205
8 20671 204 5 210
9 22978 214 5 128
10 13522 101 1 176
11 22471 231 5 226
12 19529 206 4 296
13 24216 248 3 240
14 11521 107 2 168
15 197.82 205 3 100

i. Draw three scatter plots of sales volume with each of the three variables and comment
on their correlation.
ii. Regress the sales volume on #household, number of cars and marketing expense.
Calculate R square and interpret the same.
iii. Determine which variable is/are significant variable/s. Is there any insignificant
variable? If yes, regress again, by dropping the variable. Will dropping that variable
increases the adjusted R square?

Answer 1

i)

The correlation between Sales Volume and # Households is quite strong. The correlation coefficient is quite big and positive.

The correlation between Sales Volume and # cars is quite strong (but not so strong as between Sales Volume and # Households). The correlation coefficient is quite big and positive.

The correlation between Sales Volume and marketing expense is not so strong as between Sales Volume and # Households, Sales Volume and # cars. The correlation coefficient is positive.

ii)

Using DataAnalysis in Excel we find the multiple linear regression model:

$\hat{Y}=2240.6+80.7X_1+578.1X_2-1.9X_3\\ \text{where } \hat{Y}\text{ --- prediction for Sales Volume},\\ X_1, X_2, X_3\text{ --- number of Households, number of cars},\\ \text{marketing expense respectively}.\\ R\text{ Square}\approx 0.93\\ \text{It is high. So the model fits our data quite well}.\\ \text{Adjusted } R\text{ Square}\approx 0.92.$

iii) If we look at Summary Output (p-value column) we can see that only X Variable 1 (# Households) explains $Y$(Sales Volume). So we drop X Variable 2, X Variable 3 (they do not explain $Y$).

We have the new model:

$\hat{Y}=1486.8+93.7X_1$

$\text{Adjusted } R\text{ Square}\approx 0.92.$

It is the same as for the multiple model.