Store24 Employee Retention
Siddhant Mishra
January 16, 2018
Task 4a
Crucial issues faced by management is what action should they take to improve the manager and crew tenure across their store because they suspected there is a strong link between store level performance and employee’s tenure.
The questions that matter most are listed below:
1. Is there any relation between employee tenure and store level performance, if there is how significant it is?
2.What will be the financial impact by the quaterly bonus given and increase in salary given to managers and crew members?
3. How important are manager and crew tenure relative to site-location factors in determining store level financial performance?
4. How bonuses and other incentives should be tied to retention as there may exsit a non linear relation between tenure of an employee and store level performance.
Task 4b
1. To see the relation between employees tenure and store level performance we should the correlation between profit and MTenure and CTenure
2. To see the financial impact we should analyse the differnce in profit if managers are having a long tenure vs short tenure and then how much is in expense for the bonus given to them. Is there and overall companys financial benifit?
3. To relatively compare the crew tenure vs site location factors we should compare the correlation of profit vs MTenure and CTenure to profit vs visibility, pedcount and Res variables
Setting working directory
setwd("E:/Internship/Tasks/Week 3/Week 3, Day 1 (Mon)")Reading Data
store.df <-read.csv(paste("Store24.csv",sep=""),)Task 4c
Viewing data
View(store.df)Summary Statistics, statistic match with Exhibit 3- Summary Statistics
summary(store.df)## 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.00Task 4d
Mean of Profit is 276313.6 and Standard Deviation of Profit is 89404.08
mean(store.df$Profit)## [1] 276313.6sd(store.df$Profit)## [1] 89404.08Mean of MTenure is 45.29644 and standard deviation of MTenure is 57.67155
mean(store.df$MTenure)## [1] 45.29644sd(store.df$MTenure)## [1] 57.67155Mean of CTenure is 13.9315 and Standard Deviation of CTenure is 17.69752
mean(store.df$CTenure)## [1] 13.9315sd(store.df$CTenure)## [1] 17.69752Task 4e Sorting data on basis of Profit in decreasing order
attach(store.df)
profitwise <- store.df[order(-Profit),]Task 4f
Top 10 most profitable stores with their StoreID, Sales, Profit, MTenure & CTenure
View(profitwise[1:10,1:5])10 Least profitable stores with their StoreID, Sales, Profit, MTenure & CTenure
View(profitwise[66:75,1:5])Task 4g Scatterplots
Scatterplot for Profit vs. MTenure.
plot(x=store.df$MTenure, y=store.df$Profit, xlab="MTenure", ylab="Profit")
Task 4h Scatterplot for Profit vs. CTenure.
plot(x=store.df$CTenure, y=store.df$Profit, xlab="CTenure", ylab="Profit")
Task 4i
Correlation Matrix
cor(store.df)## store Sales Profit MTenure CTenure
## store 1.00000000 -0.22693400 -0.19993481 -0.05655216 0.019930097
## Sales -0.22693400 1.00000000 0.92387059 0.45488023 0.254315184
## Profit -0.19993481 0.92387059 1.00000000 0.43886921 0.257678895
## MTenure -0.05655216 0.45488023 0.43886921 1.00000000 0.243383135
## CTenure 0.01993010 0.25431518 0.25767890 0.24338314 1.000000000
## Pop -0.28936691 0.40348147 0.43063326 -0.06089646 -0.001532449
## Comp 0.03194023 -0.23501372 -0.33454148 0.18087179 -0.070281327
## Visibility -0.02648858 0.13065638 0.13569207 0.15651731 0.066506016
## PedCount -0.22117519 0.42391087 0.45023346 0.06198608 -0.084112627
## Res -0.03142976 -0.16672402 -0.15947734 -0.06234721 -0.340340876
## Hours24 0.02687986 0.06324716 -0.02568703 -0.16513872 0.072865022
## CrewSkill 0.04866273 0.16402179 0.16008443 0.10162169 0.257154817
## MgrSkill -0.07218804 0.31163056 0.32284842 0.22962743 0.124045346
## ServQual -0.32246921 0.38638112 0.36245032 0.18168875 0.081156172
## Pop Comp Visibility PedCount Res
## store -0.289366908 0.03194023 -0.02648858 -0.221175193 -0.03142976
## Sales 0.403481471 -0.23501372 0.13065638 0.423910867 -0.16672402
## Profit 0.430633264 -0.33454148 0.13569207 0.450233461 -0.15947734
## MTenure -0.060896460 0.18087179 0.15651731 0.061986084 -0.06234721
## CTenure -0.001532449 -0.07028133 0.06650602 -0.084112627 -0.34034088
## Pop 1.000000000 -0.26828355 -0.04998269 0.607638861 -0.23693726
## Comp -0.268283553 1.00000000 0.02844548 -0.146325204 0.21923878
## Visibility -0.049982694 0.02844548 1.00000000 -0.141068116 0.02194756
## PedCount 0.607638861 -0.14632520 -0.14106812 1.000000000 -0.28437852
## Res -0.236937265 0.21923878 0.02194756 -0.284378520 1.00000000
## Hours24 -0.221767927 0.12957478 0.04692587 -0.275973353 -0.08908708
## CrewSkill 0.282845090 -0.04229731 -0.19745297 0.213672596 -0.15331247
## MgrSkill 0.083554590 0.22407913 0.07348301 0.087475440 -0.03213640
## ServQual 0.123946521 0.01814508 0.20992919 -0.005445552 0.09081624
## Hours24 CrewSkill MgrSkill ServQual
## store 0.02687986 0.04866273 -0.07218804 -0.322469213
## Sales 0.06324716 0.16402179 0.31163056 0.386381121
## Profit -0.02568703 0.16008443 0.32284842 0.362450323
## MTenure -0.16513872 0.10162169 0.22962743 0.181688755
## CTenure 0.07286502 0.25715482 0.12404535 0.081156172
## Pop -0.22176793 0.28284509 0.08355459 0.123946521
## Comp 0.12957478 -0.04229731 0.22407913 0.018145080
## Visibility 0.04692587 -0.19745297 0.07348301 0.209929194
## PedCount -0.27597335 0.21367260 0.08747544 -0.005445552
## Res -0.08908708 -0.15331247 -0.03213640 0.090816237
## Hours24 1.00000000 0.10536295 -0.03883007 0.058325655
## CrewSkill 0.10536295 1.00000000 -0.02100949 -0.033516504
## MgrSkill -0.03883007 -0.02100949 1.00000000 0.356702708
## ServQual 0.05832565 -0.03351650 0.35670271 1.000000000Task 4j
Correlations
Correlation between Profit and MTenure is 0.4388692 and Coorelation between Profit and CTenure is 0.2576789
cor(store.df$Profit,store.df$MTenure)## [1] 0.4388692cor(store.df$Profit,store.df$CTenure)## [1] 0.2576789Task 4k
Corrgrams
library("corrgram", lib.loc="~/R/win-library/3.4")
corrgram(store.df,main="Corrgram of store Variables",lower.panel=panel.shade,upper.panel=panel.pie)
## Task 4l
Pearsons Correlation tests
p-value = 8.193e-05 for Profit and MTenure
cor.test(store.df$Profit,store.df$MTenure,method="pearson")##
## Pearson's product-moment correlation
##
## data: store.df$Profit and store.df$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.4388692p-value = 0.02562 for Profit and CTenure
cor.test(store.df$Profit,store.df$CTenure,method="pearson")##
## Pearson's product-moment correlation
##
## data: store.df$Profit and store.df$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.2576789Task 3m Regression Analysis
fit <- lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility )
summary(fit)##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility)
##
## 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-12Task 4n
Variable whose p-value is less than 0.05 are “MTenure”, “CTenure”, “Comp”, “Pop”, “Pedcount” , “Res” and “Hours24”
Variable whose p-value is more than 0.05 is “Visibility”
Task 4o
fit$coefficients## (Intercept) MTenure CTenure Comp Pop
## 7610.041452 760.992734 944.978026 -25286.886662 3.666606
## PedCount Res Hours24 Visibility
## 34087.358789 91584.675234 63233.307162 12625.447050Since coeffiecient of MTenure is 760.992734 so profit will increase by 760.992734 if we increase Manager Tenure by 1 months
Since coeffiecient of CTenure is 944.978026 so profit will increase by 944.978026 if we increase Crew Tenure by 1 months
Task 4p
Summary:
There is a strong relation between manager tenure and profit of a store. It can be seen from scatterplot of Profit vs MTenure and regression coefficients of MTenure too.
Since just by 1 month increment in manager tenure profit increase by 761 rs the store level performance can increase much by increasing managers tenure.
Crew Tenure doesnot afffect much the profitaability of a store.
PPedcount is also a very dominating factor for store level performance.
Hours24 is also another important factore which can also be focused