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.