Analysing Case Study, Store24 (A): Managing Employee Retention. 1. Reading the dataset into R

setwd("C:/Users/Abhi/Desktop/Data Analytics/Week 3 Day 1")
store  <- read.csv(paste("Store24.csv" , sep = ""))
View(store)
head(store)
##   store   Sales Profit   MTenure   CTenure   Pop     Comp Visibility
## 1     1 1060294 265014   0.00000 24.804930  7535 2.797888          3
## 2     2 1619874 424007  86.22219  6.636550  8630 4.235555          4
## 3     3 1099921 222735  23.88854  5.026694  9695 4.494666          3
## 4     4 1053860 210122   0.00000  5.371663  2797 4.253946          4
## 5     5 1227841 300480   3.87737  6.866530 20335 1.651364          2
## 6     6 1703140 469050 149.93590 11.351130 16926 3.184613          3
##   PedCount Res Hours24 CrewSkill MgrSkill  ServQual
## 1        3   1       1      3.56 3.150000  86.84327
## 2        3   1       1      3.20 3.556667  94.73510
## 3        3   1       1      3.80 4.116667  78.94776
## 4        2   1       1      2.06 4.100000 100.00000
## 5        5   0       1      3.65 3.588889  68.42164
## 6        4   1       0      3.58 4.605556  94.73510
  1. Summary of datasets
summary(store)
##      store          Sales             Profit          MTenure      
##  Min.   : 1.0   Min.   : 699306   Min.   :122180   Min.   :  0.00  
##  1st Qu.:19.5   1st Qu.: 984579   1st Qu.:211004   1st Qu.:  6.67  
##  Median :38.0   Median :1127332   Median :265014   Median : 24.12  
##  Mean   :38.0   Mean   :1205413   Mean   :276314   Mean   : 45.30  
##  3rd Qu.:56.5   3rd Qu.:1362388   3rd Qu.:331314   3rd Qu.: 50.92  
##  Max.   :75.0   Max.   :2113089   Max.   :518998   Max.   :277.99  
##     CTenure              Pop             Comp          Visibility  
##  Min.   :  0.8871   Min.   : 1046   Min.   : 1.651   Min.   :2.00  
##  1st Qu.:  4.3943   1st Qu.: 5616   1st Qu.: 3.151   1st Qu.:3.00  
##  Median :  7.2115   Median : 8896   Median : 3.629   Median :3.00  
##  Mean   : 13.9315   Mean   : 9826   Mean   : 3.788   Mean   :3.08  
##  3rd Qu.: 17.2156   3rd Qu.:14104   3rd Qu.: 4.230   3rd Qu.:4.00  
##  Max.   :114.1519   Max.   :26519   Max.   :11.128   Max.   :5.00  
##     PedCount         Res          Hours24       CrewSkill    
##  Min.   :1.00   Min.   :0.00   Min.   :0.00   Min.   :2.060  
##  1st Qu.:2.00   1st Qu.:1.00   1st Qu.:1.00   1st Qu.:3.225  
##  Median :3.00   Median :1.00   Median :1.00   Median :3.500  
##  Mean   :2.96   Mean   :0.96   Mean   :0.84   Mean   :3.457  
##  3rd Qu.:4.00   3rd Qu.:1.00   3rd Qu.:1.00   3rd Qu.:3.655  
##  Max.   :5.00   Max.   :1.00   Max.   :1.00   Max.   :4.640  
##     MgrSkill        ServQual     
##  Min.   :2.957   Min.   : 57.90  
##  1st Qu.:3.344   1st Qu.: 78.95  
##  Median :3.589   Median : 89.47  
##  Mean   :3.638   Mean   : 87.15  
##  3rd Qu.:3.925   3rd Qu.: 99.90  
##  Max.   :4.622   Max.   :100.00
  1. Mean and standard deviation of Profit.
mean(store$Profit)
## [1] 276313.6
sd(store$Profit)
## [1] 89404.08

Average profit is $ 276313.6 with standard deviation of $ 89404.08. 4. Mean and standard deviation of MTenure.

mean(store$MTenure)
## [1] 45.29644
sd(store$MTenure)
## [1] 57.67155

Mean of Manager Tenure is 45.29 with standard deviation of 57.67 months.

  1. Mean and standard deviation of CTenure.
mean(store$CTenure)
## [1] 13.9315
sd(store$CTenure)
## [1] 17.69752

6a. Sorting and Subsetting data in R: Top 10

lp<- store[order(-store$Profit), ]
lp[1:10, 1:5]
##    store   Sales Profit   MTenure    CTenure
## 74    74 1782957 518998 171.09720  29.519510
## 7      7 1809256 476355  62.53080   7.326488
## 9      9 2113089 474725 108.99350   6.061602
## 6      6 1703140 469050 149.93590  11.351130
## 44    44 1807740 439781 182.23640 114.151900
## 2      2 1619874 424007  86.22219   6.636550
## 45    45 1602362 410149  47.64565   9.166325
## 18    18 1704826 394039 239.96980  33.774130
## 11    11 1583446 389886  44.81977   2.036961
## 47    47 1665657 387853  12.84790   6.636550

6b. Sorting and Subsetting data in R: Bottom 10

lp<- store[order(store$Profit), ]
lp[1:10, 1:5]
##    store   Sales Profit     MTenure   CTenure
## 57    57  699306 122180  24.3485700  2.956879
## 66    66  879581 146058 115.2039000  3.876797
## 41    41  744211 147327  14.9180200 11.926080
## 55    55  925744 147672   6.6703910 18.365500
## 32    32  828918 149033  36.0792600  6.636550
## 13    13  857843 152513   0.6571813  1.577002
## 54    54  811190 159792   6.6703910  3.876797
## 52    52 1073008 169201  24.1185600  3.416838
## 61    61  716589 177046  21.8184200 13.305950
## 37    37 1202917 187765  23.1985000  1.347023
  1. A scatter plot of Profit vs. MTenure.
library(car)
scatterplot(Profit~MTenure, data=store,
            xlab="MTenure", ylab="Profit", 
            main="Scatterplot of MTenure v/s Profit")

8. A scatter plot of Profit vs. CTenure.

scatterplot(Profit~CTenure, data=store,
            xlab="CTenure", ylab="Profit", 
            main="Scatterplot of CTenure v/s Profit")

  1. A Correlation Matrix for all the variables in the dataset.
options(digits=2)
cor(store)
##             store  Sales Profit MTenure CTenure     Pop   Comp Visibility
## store       1.000 -0.227 -0.200  -0.057  0.0199 -0.2894  0.032     -0.026
## Sales      -0.227  1.000  0.924   0.455  0.2543  0.4035 -0.235      0.131
## Profit     -0.200  0.924  1.000   0.439  0.2577  0.4306 -0.335      0.136
## MTenure    -0.057  0.455  0.439   1.000  0.2434 -0.0609  0.181      0.157
## CTenure     0.020  0.254  0.258   0.243  1.0000 -0.0015 -0.070      0.067
## Pop        -0.289  0.403  0.431  -0.061 -0.0015  1.0000 -0.268     -0.050
## Comp        0.032 -0.235 -0.335   0.181 -0.0703 -0.2683  1.000      0.028
## Visibility -0.026  0.131  0.136   0.157  0.0665 -0.0500  0.028      1.000
## PedCount   -0.221  0.424  0.450   0.062 -0.0841  0.6076 -0.146     -0.141
## Res        -0.031 -0.167 -0.159  -0.062 -0.3403 -0.2369  0.219      0.022
## Hours24     0.027  0.063 -0.026  -0.165  0.0729 -0.2218  0.130      0.047
## CrewSkill   0.049  0.164  0.160   0.102  0.2572  0.2828 -0.042     -0.197
## MgrSkill   -0.072  0.312  0.323   0.230  0.1240  0.0836  0.224      0.073
## ServQual   -0.322  0.386  0.362   0.182  0.0812  0.1239  0.018      0.210
##            PedCount    Res Hours24 CrewSkill MgrSkill ServQual
## store       -0.2212 -0.031   0.027     0.049   -0.072  -0.3225
## Sales        0.4239 -0.167   0.063     0.164    0.312   0.3864
## Profit       0.4502 -0.159  -0.026     0.160    0.323   0.3625
## MTenure      0.0620 -0.062  -0.165     0.102    0.230   0.1817
## CTenure     -0.0841 -0.340   0.073     0.257    0.124   0.0812
## Pop          0.6076 -0.237  -0.222     0.283    0.084   0.1239
## Comp        -0.1463  0.219   0.130    -0.042    0.224   0.0181
## Visibility  -0.1411  0.022   0.047    -0.197    0.073   0.2099
## PedCount     1.0000 -0.284  -0.276     0.214    0.087  -0.0054
## Res         -0.2844  1.000  -0.089    -0.153   -0.032   0.0908
## Hours24     -0.2760 -0.089   1.000     0.105   -0.039   0.0583
## CrewSkill    0.2137 -0.153   0.105     1.000   -0.021  -0.0335
## MgrSkill     0.0875 -0.032  -0.039    -0.021    1.000   0.3567
## ServQual    -0.0054  0.091   0.058    -0.034    0.357   1.0000
  1. Correlation between Profit and MTenure.
cor(store$Profit, store$MTenure)
## [1] 0.44
  1. Correlation between Profit and CTenure.
cor(store$Profit, store$CTenure)
## [1] 0.26
  1. Corrgram based on all variables in the dataset.
library(corrgram)
corrogram<-corrgram(store,order=TRUE, lower.panel = panel.shade, upper.panel = panel.shade, text.panel = panel.txt, main="Corrgram of Store dataset")

13. How the Profit is correlated with the other variables Profit has positive correlation with MTenure, CTenure, Sales, Pop, Comp, Visibility, ServQual, CrewSkill and Profit is negatively correlated with Comp, Hours24, store and Res. Also, manager’s tenure has negative correlation with longer working hours, which could be source of demotivation for continuing their job. Sales are also affected by no. of competitors near the store as more competitors will cause reduction in sales and utlimately profit. Profit is positively affected by Crew’s Skill and Manager’s Skill as more the customers are satisfied due to efficient customer service, more returning customers the store has.

  1. Pearson’s Correlation test on the correlation between Profit and MTenure.
cor.test(store$Profit, store$MTenure, method = "pearson")
## 
##  Pearson's product-moment correlation
## 
## data:  store$Profit and store$MTenure
## t = 4, df = 70, p-value = 8e-05
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
##  0.24 0.61
## sample estimates:
##  cor 
## 0.44
  1. Pearson’s Correlation test on the correlation between Profit and CTenure.
cor.test(store$Profit, store$CTenure, method = "pearson")
## 
##  Pearson's product-moment correlation
## 
## data:  store$Profit and store$CTenure
## t = 2, df = 70, p-value = 0.03
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
##  0.033 0.458
## sample estimates:
##  cor 
## 0.26
  1. Regression Analysis - Profit on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility}.
rp<- lm(Profit~MTenure+CTenure+Comp+Pop+PedCount+Res+Hours24+Visibility, data=store)
summary(rp)
## 
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount + 
##     Res + Hours24 + Visibility, data = store)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -105789  -35946   -7069   33780  112390 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept)   7610.04   66821.99    0.11  0.90967    
## MTenure        760.99     127.09    5.99  9.7e-08 ***
## CTenure        944.98     421.69    2.24  0.02840 *  
## Comp        -25286.89    5491.94   -4.60  1.9e-05 ***
## Pop              3.67       1.47    2.50  0.01489 *  
## PedCount     34087.36    9073.20    3.76  0.00037 ***
## Res          91584.68   39231.28    2.33  0.02262 *  
## Hours24      63233.31   19641.11    3.22  0.00199 ** 
## Visibility   12625.45    9087.62    1.39  0.16941    
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 57000 on 66 degrees of freedom
## Multiple R-squared:  0.638,  Adjusted R-squared:  0.594 
## F-statistic: 14.5 on 8 and 66 DF,  p-value: 5.38e-12
  1. List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).

MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24

  1. List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05). Visibility

  2. What is expected change in the Profit at a store, if the Manager’s tenure i.e. number of months of experience with Store24, increases by one month?

If the Manager’s tenure is increased by a month, the profit changes by $760.99.

  1. What is expected change in the Profit at a store, if the Crew’s tenure i.e. number of months of experience with Store24, increases by one month?

If the Crew’s tenure is increased by a month, the profit changes by $944.98.

  1. Executive Summary

From correlation matrix, we can conclude that profit has positive correlation with the manager tenure, Population, Pedestrian Count. Therefor, store performance is strongly affected by these variables. The Performance of store also depends on the ‘location factors’ like Population and Pedestrian count. Service quality is also one domain where management has to rethink on service blueprint So, the company should surely take measures of implementing new models of service delivary that will add value to the customers. With the probability from Pearson’s correlation test, the crew tenure surely indulges in profit margin. One can also look at the linear regression with profit (response variable), with others as predictor variables(MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility).

So increasing in the management tenure and crew tenure will have significant positive effect on the financial performance.