MIS171 Business Analytics

The following two sample questions are indicative of the type of multiple?choice questions you will receive in the quiz.
Q1. When testing the contribution of all independent variables included in a multiple linear regression model.
a. The more independent variables that are included in the model, the need to consider multi?collinearity reduces.
b. The more independent variables there are, the need to consider multi?collinearity increases.
c. Limiting the number of independent variables reduces the need to consider multi collinearity.
d. None of the above are correct.
Q2.  When testing if an independent measure should be included in a regression model, which of the following statements is correct?
a. The larger the independent coefficient, the more likely it is to have a significant contribution on the dependent measure.
b. If an independent measure has a non?zero effect on the predicted variable, the p?value will be greater than alpha
c. If an independent measure has a non?zero effect on the predicted variable, the p?value will be less than alpha
d. None of the above are correct

Project A: Super mart Sales prediction

Results from the multiple regression analysis:

In the first step the data was loaded into the statistical software and the sample from the data frame is shown in the table below:

## 'data.frame':    150 obs. of  14 variables:

##  $ Store.No.     : int  1 2 3 4 5 6 7 8 9 10 ...

##  $ Sales..m      : num  12.5 14.5 19 18.2 7.6 18.5 13.1 14.9 17.1 9.2 ...

##  $ Wages..m      : num  2.3 2.7 3.1 2.6 2 2.7 2.4 2.5 2.7 2.1 ...

##  $ No..Staff     : int  60 69 79 66 51 62 61 59 65 55 ...

##  $ Age..Yrs.     : int  10 8 7 7 15 6 7 6 8 16 ...

##  $ GrossProfit..m: num  0.712 0.091 1.72 1.372 0.935 ...

##  $ Adv...000     : int  171 213 255 287 112 238 124 214 215 154 ...

##  $ Competitors   : int  3 4 1 1 3 0 2 2 2 5 ...

##  $ HrsTrading    : int  110 134 98 85 72 77 100 95 112 75 ...

##  $ SundayD       : int  0 0 1 1 0 1 1 0 1 0 ...

##  $ Mng.GenderD   : int  1 1 1 1 1 1 1 1 1 0 ...

##  $ Mng.Age       : int  33 33 40 29 36 32 52 41 31 42 ...

##  $ Mng.Exp       : int  12 16 13 10 4 15 15 4 12 13 ...

##  $ Car.Spaces    : int  46 73 64 66 29 40 69 45 42 34 ...

After loading the data, the nest step is to check for the missing values in the data. It was found that there are no missing values in the data set.  Also there are 150 observations in the data set with 13 different variables.

Questions:

1. Which independent variables have the strongest linear relationship with sales?

Results from the multiple regression analysis shows that advertisement & promotional expenses have the strongest linear relationship with sales.

1. Is your multiple regression models overall significant?
2. Call:
3. ## lm(formula = sales_data$Sales..m ~ Adv...000 + Wages..m + Mng.Exp +
4. ##     Mng.Age + Competitors + HrsTrading + SundayD, data = sales_data)
5. ##
6. ## Residuals:
7. ##     Min      1Q  Median      3Q     Max
8. ## -4.9523 -0.8091 -0.1140  0.9140  3.4853
9. ##
10. ## Coefficients:
11. ##              Estimate Std. Error t value Pr(>|t|)    
12. ## (Intercept)  3.339111   0.982191   3.400 0.000876 ***
13. ## Adv...000    0.021164   0.002863   7.392 1.14e-11 ***
14. ## Wages..m     2.055372   0.336267   6.112 8.97e-09 ***
15. ## Mng.Exp      0.184404   0.031292   5.893 2.63e-08 ***
16. ## Mng.Age     -0.064327   0.015781  -4.076 7.58e-05 ***
17. ## Competitors -0.402110   0.099351  -4.047 8.48e-05 ***
18. ## HrsTrading   0.017513   0.007007   2.499 0.013581 *  
19. ## SundayD      0.589674   0.263707   2.236 0.026905 *  
20. ## ---
21. ## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
22. ##
23. ## Residual standard error: 1.356 on 142 degrees of freedom
24. ## Multiple R-squared:  0.8625, Adjusted R-squared:  0.8558
25. ## F-statistic: 127.3 on 7 and 142 DF,  p-value: < 2.2e-16

As the results shown in the above table F statistics is significant at 5 % significance level as the p value for F statistics is less than 0.05 so the overall model is significant.

1. c) If so, which variables do not help you in modeling the dependent measure?

On the basis of the results from the regression analysis it can be said that age, number of staff, gross profit, Mng gender and car.Spaces do not help in modeling the dependent measure.

1. d) Once you have built your final model, are there any potential mult-collinearity problems?  If so, which variable are they?

To check the multi-collinearity problem, the Variance inflation factor (VIF) method was used and the results from the test show that VIF for the all variables are less than 10. S0, it can be said that there is no multicollinearity among the variables.

1. e) How well does the model explains sales (use R²in your explanation)?

Results from the regression results shows that the value of R² is 0.86. So it can be said that 86 % of variation in sales is explained by the independent variables included in the model. The R² value of greater than 0.6 is considered as a good model.

2. f) What would be the sales for an 8 year old store with 60 staff and 80 car spaces that open for 100 hours per week including Sunday; managed by 37 years old male manager with seven years of experience, that pays $2.6 million on wages, spends $ 150,000 on advertising, reports $1 million gross profit with three competitor stores?

On the basis of the regression results coefficients and putting the value of the independent variables as given, the predicted sales comes out to be $11.90 million.

Task two: Development of an RFM model

On the basis of the given data set ( Bilka customer data) theRFM model was performed.

1. What is the total net revenue attributable to the campaign of all customers for the period the data covers?

Total net revenue in this case is $44369.659

On the basis of the revenue generated:

1. What is the net revenue generated by the various RFM segments?

The net revenue generated by various RFM segments is shown in the excel sheet (column “M”).

1. What are the 5 top revenue generating RFM segments that we should target in our next email sales campaign?

Top five revenue generating RFM segments and the average revenue generated by those segments is shown in the table below. So these RFM segments should be targeted in the next email sales campaign.

1. What is the response rate for each RFM customer segment?

Response rate for each segment is shown in the table below. The table has been created using the pivot table in Microsoft Excel.