Managing Employee Retention
Ashwin Ingle
14 January 2018
R Markdown
This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.
When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:
TASK 4c
store.df <-read.csv(paste ("Store24.csv", sep=""))
View(store.df)
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"## [1] "mean"apply(store.df[,3:5],2,mean)## Profit MTenure CTenure
## 276313.61333 45.29644 13.93150"standar deviation"## [1] "standar deviation"apply(store.df[,3:5],2,sd)## Profit MTenure CTenure
## 89404.07634 57.67155 17.69752TASK 4e
attach(mtcars)
View(mtcars)
newdata <- mtcars[order(mpg),]
View(newdata)
newdata[1:5,]## mpg cyl disp hp drat wt qsec vs am gear carb
## Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 3 4
## Lincoln Continental 10.4 8 460 215 3.00 5.424 17.82 0 0 3 4
## Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 3 4
## Duster 360 14.3 8 360 245 3.21 3.570 15.84 0 0 3 4
## Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 3 4newdata <- mtcars[order(-mpg),]
View(newdata)
detach(mtcars)TASK 4f
"TOP 10 most profitable"## [1] "TOP 10 most profitable"topten.df <- store.df[order(-store.df$Profit),]
topten.df[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"Bottom 10 least profitable"## [1] "Bottom 10 least profitable"leastprofitable <- store.df[order(store.df$Profit),]
leastprofitable[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.347023TASK 4g
library(car)
scatterplot(store.df$Profit ~ store.df$MTenure , main="Profit vs MTenure Scatterplot", xlab="MTenure", ylab="Profit")
TASK 4h
library(car)
scatterplot(store.df$Profit ~ store.df$CTenure , main="Profit vs CTenure Scatterplot", xlab="CTenure", ylab="Profit")
TASK 4i
round(cor(store.df, use = "complete.obs", method = "kendall"),2)## store Sales Profit MTenure CTenure Pop Comp Visibility
## store 1.00 -0.16 -0.14 -0.01 -0.01 -0.19 -0.02 -0.02
## Sales -0.16 1.00 0.78 0.26 0.14 0.20 -0.18 0.15
## Profit -0.14 0.78 1.00 0.25 0.19 0.23 -0.26 0.14
## MTenure -0.01 0.26 0.25 1.00 0.10 -0.04 0.12 0.01
## CTenure -0.01 0.14 0.19 0.10 1.00 -0.13 -0.11 0.05
## Pop -0.19 0.20 0.23 -0.04 -0.13 1.00 -0.11 0.01
## Comp -0.02 -0.18 -0.26 0.12 -0.11 -0.11 1.00 0.07
## Visibility -0.02 0.15 0.14 0.01 0.05 0.01 0.07 1.00
## PedCount -0.14 0.31 0.32 0.00 -0.05 0.46 -0.22 -0.11
## Res -0.03 -0.13 -0.15 0.04 -0.10 -0.17 0.19 0.02
## Hours24 0.02 0.07 0.02 -0.09 0.02 -0.24 0.10 0.04
## CrewSkill -0.03 0.11 0.11 0.12 0.17 0.16 -0.05 -0.18
## MgrSkill -0.06 0.18 0.15 0.19 0.02 0.03 0.17 0.01
## ServQual -0.23 0.28 0.25 0.17 0.06 0.06 0.06 0.16
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## store -0.14 -0.03 0.02 -0.03 -0.06 -0.23
## Sales 0.31 -0.13 0.07 0.11 0.18 0.28
## Profit 0.32 -0.15 0.02 0.11 0.15 0.25
## MTenure 0.00 0.04 -0.09 0.12 0.19 0.17
## CTenure -0.05 -0.10 0.02 0.17 0.02 0.06
## Pop 0.46 -0.17 -0.24 0.16 0.03 0.06
## Comp -0.22 0.19 0.10 -0.05 0.17 0.06
## Visibility -0.11 0.02 0.04 -0.18 0.01 0.16
## PedCount 1.00 -0.26 -0.29 0.12 0.05 -0.05
## Res -0.26 1.00 -0.09 -0.16 -0.03 0.09
## Hours24 -0.29 -0.09 1.00 0.14 0.00 0.04
## CrewSkill 0.12 -0.16 0.14 1.00 0.05 -0.01
## MgrSkill 0.05 -0.03 0.00 0.05 1.00 0.24
## ServQual -0.05 0.09 0.04 -0.01 0.24 1.00TASK 4j
"Correlationg between Profit MTenure"## [1] "Correlationg between Profit MTenure"round(cor(store.df$Profit, store.df$MTenure),2)## [1] 0.44"Correlationg between Profit CTenure"## [1] "Correlationg between Profit CTenure"round(cor(store.df$Profit, store.df$CTenure),2)## [1] 0.26TASK 4k
library(corrgram)
corrgram(store.df, lower.panel=panel.shade, upper.panel=panel.pie,diag.pane=panel.minmax,text.panel=panel.txt, main="Corrgram of Store Variables")
Profit is positively correlated with MTenure, CTenure, Pop, and PedCount.
Profit is negatively correlated with Comp, which represents the retail competition
Managerially Relavant Correlations in the decreasing order of correlation value with the profit are:
Sales > PedCount > MTenure > Pop > ServQual
TASK 4l
cor.test(store.df$Profit,store.df$MTenure)##
## 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.4388692"p value is 8.193e-05"## [1] "p value is 8.193e-05"cor.test(store.df$Profit,store.df$CTenure)##
## 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.2576789"p value is 0.02562"## [1] "p value is 0.02562"TASK 4m
summary(lm(Profit~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility, data=store.df))##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility, data = store.df)
##
## 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
Explanatory variable whose beta coffecient is statistically not significant is Visibility
explanatory variable(s) whose beta-coefficients are not statistically significant are MTenure, CTenure, Pop, PedCount, Res, Hours24, Comp
TASK 4o
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 = $810.971201
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 = $1016.017324
TASK 4p
From this analysis we can tell that the 10 most profitable stores have higher manager tenures and crew tenures than the 10 least profitable stores. Manager tenure has outliers because there are some managers who have very high experience. Crew tenure has very few outliers with crew members with high experience.
The highest correlation exists between profit and sales.
The number of competitors has a declining effect on Profit i.e. there will be adverse effect on profit if competitors increase
A greater increase in Profit is observed by increasing Crew’s tenure rather than the Manager’s tenure. Therefore, measures must be taken to increase Crew’s tenure.
Given that: F-statistic of 14.53; 8 datapoints; 66 degrees of freedom and p-value = 5.382e-12 we can say that Profit is closely related to {MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24 and Visibility} all taken together as the p-value is very small.
From Multiple R-squared: 0.6379 we can say that this model accounts for 63.79% of the variances and Adjusted R-squared: 0.594 indicates 59.4% of weighted variances considered.
Profit = 7610.041 + 760.993(MTenure) + 944.978(CTenure) - 25286.887(Comp) + 3.667(Pop) + 34087.359(PedCount) + 91584.675(Res) + 63233.307(Hours24) + 12625.447(Visibility)