Task 4: Analysis of the Case Store24 (A): Managing Employee Retention
Nimit
15 January 2018
#Task 4c
store<- read.csv("Store24.csv")
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.00library(psych)
describe(store)[,1:9]## vars n mean sd median trimmed mad
## store 1 75 38.00 21.79 38.00 38.00 28.17
## Sales 2 75 1205413.12 304531.31 1127332.00 1182031.25 288422.04
## Profit 3 75 276313.61 89404.08 265014.00 270260.34 90532.00
## MTenure 4 75 45.30 57.67 24.12 33.58 29.67
## CTenure 5 75 13.93 17.70 7.21 10.60 6.14
## Pop 6 75 9825.59 5911.67 8896.00 9366.07 7266.22
## Comp 7 75 3.79 1.31 3.63 3.66 0.82
## Visibility 8 75 3.08 0.75 3.00 3.07 0.00
## PedCount 9 75 2.96 0.99 3.00 2.97 1.48
## Res 10 75 0.96 0.20 1.00 1.00 0.00
## Hours24 11 75 0.84 0.37 1.00 0.92 0.00
## CrewSkill 12 75 3.46 0.41 3.50 3.47 0.34
## MgrSkill 13 75 3.64 0.41 3.59 3.62 0.45
## ServQual 14 75 87.15 12.61 89.47 88.62 15.61
## min max
## store 1.00 75.00
## Sales 699306.00 2113089.00
## Profit 122180.00 518998.00
## MTenure 0.00 277.99
## CTenure 0.89 114.15
## Pop 1046.00 26519.00
## Comp 1.65 11.13
## Visibility 2.00 5.00
## PedCount 1.00 5.00
## Res 0.00 1.00
## Hours24 0.00 1.00
## CrewSkill 2.06 4.64
## MgrSkill 2.96 4.62
## ServQual 57.90 100.00#Task 4d
#mean and standard deviation of Profit.
mean(store$Profit) ## [1] 276313.6sd(store$Profit)## [1] 89404.08#mean and standard deviation of MTenure.
mean(store$MTenure) ## [1] 45.29644sd(store$MTenure)## [1] 57.67155##mean and standard deviation of MTenure.
mean(store$CTenure)## [1] 13.9315sd(store$CTenure)## [1] 17.69752#Task 4f
descStore<- store[order(-store$Profit),]
#Top 10 most profitable stores
head(descStore,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#Top 10 least profitable sotres
tail(descStore,10)[,1:5]## store Sales Profit MTenure CTenure
## 37 37 1202917 187765 23.1985000 1.347023
## 61 61 716589 177046 21.8184200 13.305950
## 52 52 1073008 169201 24.1185600 3.416838
## 54 54 811190 159792 6.6703910 3.876797
## 13 13 857843 152513 0.6571813 1.577002
## 32 32 828918 149033 36.0792600 6.636550
## 55 55 925744 147672 6.6703910 18.365500
## 41 41 744211 147327 14.9180200 11.926080
## 66 66 879581 146058 115.2039000 3.876797
## 57 57 699306 122180 24.3485700 2.956879#Task 4g
library(car)##
## Attaching package: 'car'## The following object is masked from 'package:psych':
##
## logit#Scatter Plot Profit Vs MTenure
scatterplot(Profit~MTenure, data=store, main = "Scatterplot of Profit Vs MTenure", ylab="Profit", xlab="MTenure", cex=0.5, pch=19)
#Scatter plot of Profit Vs CTenure
scatterplot(Profit~CTenure, data=store, main = "Scatterplot of Profit Vs CTenure", ylab="Profit", xlab="CTenure", cex=0.5, pch=19)
#Task4i Correlation Matrix
round(cor(store),2)## store Sales Profit MTenure CTenure Pop Comp Visibility
## store 1.00 -0.23 -0.20 -0.06 0.02 -0.29 0.03 -0.03
## Sales -0.23 1.00 0.92 0.45 0.25 0.40 -0.24 0.13
## Profit -0.20 0.92 1.00 0.44 0.26 0.43 -0.33 0.14
## MTenure -0.06 0.45 0.44 1.00 0.24 -0.06 0.18 0.16
## CTenure 0.02 0.25 0.26 0.24 1.00 0.00 -0.07 0.07
## Pop -0.29 0.40 0.43 -0.06 0.00 1.00 -0.27 -0.05
## Comp 0.03 -0.24 -0.33 0.18 -0.07 -0.27 1.00 0.03
## Visibility -0.03 0.13 0.14 0.16 0.07 -0.05 0.03 1.00
## PedCount -0.22 0.42 0.45 0.06 -0.08 0.61 -0.15 -0.14
## Res -0.03 -0.17 -0.16 -0.06 -0.34 -0.24 0.22 0.02
## Hours24 0.03 0.06 -0.03 -0.17 0.07 -0.22 0.13 0.05
## CrewSkill 0.05 0.16 0.16 0.10 0.26 0.28 -0.04 -0.20
## MgrSkill -0.07 0.31 0.32 0.23 0.12 0.08 0.22 0.07
## ServQual -0.32 0.39 0.36 0.18 0.08 0.12 0.02 0.21
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## store -0.22 -0.03 0.03 0.05 -0.07 -0.32
## Sales 0.42 -0.17 0.06 0.16 0.31 0.39
## Profit 0.45 -0.16 -0.03 0.16 0.32 0.36
## MTenure 0.06 -0.06 -0.17 0.10 0.23 0.18
## CTenure -0.08 -0.34 0.07 0.26 0.12 0.08
## Pop 0.61 -0.24 -0.22 0.28 0.08 0.12
## Comp -0.15 0.22 0.13 -0.04 0.22 0.02
## Visibility -0.14 0.02 0.05 -0.20 0.07 0.21
## PedCount 1.00 -0.28 -0.28 0.21 0.09 -0.01
## Res -0.28 1.00 -0.09 -0.15 -0.03 0.09
## Hours24 -0.28 -0.09 1.00 0.11 -0.04 0.06
## CrewSkill 0.21 -0.15 0.11 1.00 -0.02 -0.03
## MgrSkill 0.09 -0.03 -0.04 -0.02 1.00 0.36
## ServQual -0.01 0.09 0.06 -0.03 0.36 1.00#Task 4j Correlations
#Correlation between MTenure and Profit
round(cor(store),2)[3,4]## [1] 0.44#Correlation between CTenure and Profit
round(cor(store),2)[3,5]## [1] 0.26#TASK 4k
library(corrgram)## Warning: replacing previous import by 'magrittr::%>%' when loading
## 'dendextend'corrgram(store, lower.panel=panel.shade,
upper.panel=panel.pie, text.panel=panel.txt,
main="Corrgram of store variables")
Managerially relevant correlations?
Profit is positively correlated with 1. MTenure 2. CTenure 3. Pop 4. PedCount 5. CrewSkill 6. MgrSkill 7. ServQual
Managers and crews’experience and skills are two important factors contribute positively to the profit of Store24.
Population density within 1/2 mile of neighbourhood of store is also affect positively to the profit of Store24.
Ratings for service quality and pedestrain rating is highly correlated with the profit.
#TASK 4l - Pearson’s Correlation Tests
#correlation between Profit and MTenure.
cor.test(store$Profit,store$MTenure)##
## Pearson's product-moment correlation
##
## data: store$Profit and store$MTenure
## t = 4.1731, df = 73, p-value = 8.193e-05
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
## 0.2353497 0.6055175
## sample estimates:
## cor
## 0.4388692#P-value
cor.test(store$Profit,store$MTenure)$p.value## [1] 8.193133e-05#correlation between Profit and CTenure.
cor.test(store$Profit,store$CTenure)##
## Pearson's product-moment correlation
##
## data: store$Profit and store$CTenure
## t = 2.2786, df = 73, p-value = 0.02562
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
## 0.03262507 0.45786339
## sample estimates:
## cor
## 0.2576789#P-value
cor.test(store$Profit,store$CTenure)$p.value## [1] 0.0256203#TASK 3m - Regression Analysis
model<- lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility, data=store)
summary(model)##
## 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.041 66821.994 0.114 0.909674
## MTenure 760.993 127.086 5.988 9.72e-08 ***
## CTenure 944.978 421.687 2.241 0.028400 *
## Comp -25286.887 5491.937 -4.604 1.94e-05 ***
## Pop 3.667 1.466 2.501 0.014890 *
## PedCount 34087.359 9073.196 3.757 0.000366 ***
## Res 91584.675 39231.283 2.334 0.022623 *
## Hours24 63233.307 19641.114 3.219 0.001994 **
## Visibility 12625.447 9087.620 1.389 0.169411
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 56970 on 66 degrees of freedom
## Multiple R-squared: 0.6379, Adjusted R-squared: 0.594
## F-statistic: 14.53 on 8 and 66 DF, p-value: 5.382e-12 944.98*1.38 ## [1] 1304.072 (34087.36+63233.31+ 12625.45)/3## [1] 36648.71List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05). MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24
List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05). Visibility
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 managers experience on an average increases by one month then profit of Sotre24 increases by 760.993 times
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 crew’s experience on an average increases by one month then profit of Store24 increases by 944.978 times
Executive Summary
There is a huge difference between the profits among the different store. On digging dipper, it’s find that 10 stores with the highest profit have on average 4 times more manager and crew’s tenure than the 10 least profit making stores. On analyzing further it comes out that manager tenure and crew tenure are among two big factors whereas manager tenure have multiplication effect of 760 and other have even more i.e. 944 multiplication effect on profit. It means on an average increase in one month of manager tenure and crew tenure increases profit estimates by 760 and 944 times, respectively. In nutshell, the decision taken to retain manager and crew for longer is beneficial and have the good return.
Store24’s recent manager bonus plan of providing a quarterly bonus of 3% of the manager’s salary for increasing average crew tenure by 1.38 months is legitimate. The expected change in the profit of store due to 1.38 month on an average increase in crew tenure relative to no increase is 1304 times. Interestingly change in profit due to increase in crew tenure is higher than manager tenure, therefore, this plan is well admissible.
Some other feature of the store like, is store open 24 hours? good ratings on the pedestrian and visibility are deeply connected with huge profit and manager and crew are responsible to affect the performance of the store. They ensure it in many ways like ensuring compliance with Store24 merchandising, operating standards, maintaining in-stock position, and managing shrink and cash control. It’s reasonable in financial perspective to give bonus or increase wages to motivate and retain them for the long tenure.