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.