Managing Employee Retention
Sahil Garg
17 December 2017
Analysis of the Case Store24 (A): Managing Employee Retention
This case study aims to find the best policies and steps that should be taken up for better employee retention in a particular group of stores, since this would help in increasing the profits earned from the stores. The president and CEO of Store24 feels that while site-location factors such as population, number of competitors, and pedestrian access are traditionally considered the primary drivers of store success, he has always been a big believer in the power of ‘people factors,’ such as employee skill and experience, in optimizing a given site’s performance.
TASK 2 (a)
Please critically read the Harvard Business School case study Store24 (A): Managing Employee Retention, by Frances X. Frei and Dennis Campbell.
This case provides a retailing context in which employee retention strategies are explored through analyzing detailed store-level data. Qualitatively identify the crucial issues being faced by the management. Based on your judgment, prepare a list of the most important questions that matter.
The most crucial issues faced by the management are that of considering many strategies for increasing manager and crew tenure. The management wants to use data to get some estimate of the actual financial impact of increased tenure so that they can make more informed decisions when considering increasing wages and bonuses or how much to spend on training and development programs. While site-location factors such as population, number of competitors, and pedestrian access are traditionally considered the primary drivers of store success, ‘people factors,’ such as employee skill and experience, also play a vital role in optimizing a given site’s performance.
The most important questions are - 1. Does increased tenure of crew and managers affect profits? 2. How much does increased tenure affect profits? 3. Do increased wages and bonuses reflect in increased tenure? 4. Do better training and development programmes help in increased tenure? 5. How much do site-location factors such as population, competitors etc affect a store’s growth?
TASK 2 (b)
Play close attention to Exhibit 2 - Variable Names and Descriptions from the case. Understand what each variable means. Go back to the text of the case and relate each variable of Exhibit 2 to the different points of discussion presented in the case. Think about how might a dataset associated with Exhibit 2 help in answering your list of questions from TASK 2a.
The variable names and desciptions are studied and are related to the issue in consideration.
TASK 2 (c)
Download and review the Store24.csv data file associated with this case. You may open it in Excel for convenience.
Using R, read the data into a data frame called store. Play close attention to Exhibit 3 - Summary Statistics from Sample Stores from the CASE. Using R, get the summary statistics of the data. Confirm that the summary statistics generated from R are consistent with Exhibit 3 from the Case.
setwd("D:/manipal-year2/internship/IIML_dataAnalytics/Datasets")
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.00library(psych)
describe(store.df)## 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 range skew kurtosis se
## store 1.00 75.00 74.00 0.00 -1.25 2.52
## Sales 699306.00 2113089.00 1413783.00 0.71 -0.09 35164.25
## Profit 122180.00 518998.00 396818.00 0.62 -0.21 10323.49
## MTenure 0.00 277.99 277.99 2.01 3.90 6.66
## CTenure 0.89 114.15 113.26 3.52 15.00 2.04
## Pop 1046.00 26519.00 25473.00 0.62 -0.23 682.62
## Comp 1.65 11.13 9.48 2.48 11.31 0.15
## Visibility 2.00 5.00 3.00 0.25 -0.38 0.09
## PedCount 1.00 5.00 4.00 0.00 -0.52 0.11
## Res 0.00 1.00 1.00 -4.60 19.43 0.02
## Hours24 0.00 1.00 1.00 -1.82 1.32 0.04
## CrewSkill 2.06 4.64 2.58 -0.43 1.64 0.05
## MgrSkill 2.96 4.62 1.67 0.27 -0.53 0.05
## ServQual 57.90 100.00 42.10 -0.66 -0.72 1.46TASK 2 (d)
Three very important variables in this analysis are the store Profit, the management tenure (MTenure) and the crew tenure (CTenure).
Use R to measure the mean and standard deviation of Profit. Use R to measure the mean and standard deviation of MTenure. Use R to measure the mean and standard deviation of CTenure.
attach(store.df)
mean(Profit)## [1] 276313.6sd(Profit)## [1] 89404.08mean(MTenure)## [1] 45.29644sd(MTenure)## [1] 57.67155mean(CTenure)## [1] 13.9315sd(CTenure)## [1] 17.69752TASK 2 (e) - Sorting and Subsetting data in R
TASK 2e - Sorting and Subsetting data in R In this TASK, we will learn how to sort a dataframe based on a data column Understand what the following R code does. Copy-Paste it and Execute it in R.
attach(mtcars) View(mtcars) newdata <- mtcars[order(mpg),] # sort by mpg (ascending) View(newdata) newdata[1:5,] # see the first 5 rows newdata <- mtcars[order(-mpg),] # sort by mpg (descending) View(newdata) detach(mtcars)
R has an inbuilt dataset called mtcars. In the above code, the order() function helps us sort the data in mtcars, based on a data column called mpg. We can sort in ascending order or descending order. After sorting, notice how we can view the top 5 cars that have the highest and lowest mpg respectively.
attach(mtcars)
View(mtcars)
newdata <- mtcars[order(mpg),] # sort by mpg (ascending)
View(newdata)
newdata[1:5,] # see the first 5 rows## 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),] # sort by mpg (descending)
View(newdata)
detach(mtcars)TASK 2 (f)- Replicate Exhibit 1 shown in the case, using R
Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores. Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.
mostprof <- store.df[order(-Profit),]
mostprof[1:10,]## store Sales Profit MTenure CTenure Pop Comp Visibility
## 74 74 1782957 518998 171.09720 29.519510 10913 2.319850 3
## 7 7 1809256 476355 62.53080 7.326488 17754 3.377900 2
## 9 9 2113089 474725 108.99350 6.061602 26519 2.637630 2
## 6 6 1703140 469050 149.93590 11.351130 16926 3.184613 3
## 44 44 1807740 439781 182.23640 114.151900 20624 3.628561 3
## 2 2 1619874 424007 86.22219 6.636550 8630 4.235555 4
## 45 45 1602362 410149 47.64565 9.166325 17808 3.472609 5
## 18 18 1704826 394039 239.96980 33.774130 3807 3.994713 5
## 11 11 1583446 389886 44.81977 2.036961 21550 3.272398 2
## 47 47 1665657 387853 12.84790 6.636550 23623 2.422707 2
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## 74 4 1 0 3.50 4.405556 94.73878
## 7 5 1 1 3.94 4.100000 81.57837
## 9 4 1 1 3.22 3.583333 100.00000
## 6 4 1 0 3.58 4.605556 94.73510
## 44 4 0 1 4.06 4.172222 86.84327
## 2 3 1 1 3.20 3.556667 94.73510
## 45 3 1 1 3.58 4.622222 100.00000
## 18 3 1 1 3.18 3.866667 97.36939
## 11 5 1 1 3.43 3.200000 100.00000
## 47 5 1 1 4.23 3.950000 99.80105leastprof <- store.df[order(Profit),]
leastprof[1:10,]## store Sales Profit MTenure CTenure Pop Comp Visibility
## 57 57 699306 122180 24.3485700 2.956879 3642 2.973376 3
## 66 66 879581 146058 115.2039000 3.876797 1046 6.569790 2
## 41 41 744211 147327 14.9180200 11.926080 9701 4.364600 2
## 55 55 925744 147672 6.6703910 18.365500 10532 6.389294 4
## 32 32 828918 149033 36.0792600 6.636550 9697 4.641468 3
## 13 13 857843 152513 0.6571813 1.577002 14186 4.435671 3
## 54 54 811190 159792 6.6703910 3.876797 3747 3.756011 3
## 52 52 1073008 169201 24.1185600 3.416838 14859 6.585143 3
## 61 61 716589 177046 21.8184200 13.305950 3014 3.263994 3
## 37 37 1202917 187765 23.1985000 1.347023 8870 4.491863 3
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## 57 2 1 1 3.35 2.956667 84.21266
## 66 3 1 1 4.03 3.673333 80.26675
## 41 3 1 1 3.03 3.672222 81.13993
## 55 3 1 1 3.49 3.477778 76.31346
## 32 3 1 0 3.28 3.550000 73.68654
## 13 2 1 1 4.10 3.000000 76.30609
## 54 2 1 1 3.08 3.933333 65.78734
## 52 3 1 1 3.83 3.833333 94.73510
## 61 1 1 1 3.07 3.126667 73.68654
## 37 3 1 1 3.38 4.016667 73.68654TASK 2 (g) - Scatter Plots
Use R to draw a scatter plot of Profit vs. MTenure.
plot(MTenure,Profit,cex = 0.6,
main = "Scatterplot of Profit vs MTenure",
col = "black",
)
abline(lm(Profit~MTenure),col = "green")
abline(h = mean(Profit),col = "red", lty = "dotted")
TASK 2 (h) - Scatter Plots (contd.)
Use R to draw a scatter plot of Profit vs. CTenure.
plot(CTenure,Profit,cex = 0.6,
main = "Scatterplot of Profit vs CTenure",
col = "black"
)
abline(lm(Profit~MTenure),col = "green")
abline(h = mean(Profit),col = "red", lty = "dotted")
TASK 2 (i) - Correlation Matrix
Use R to construct a Correlation Matrix for all the variables in the dataset.
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 2 (j) - Correlations
Use R to measure the correlation between Profit and MTenure. Use R to measure the correlation between Profit and CTenure.
cor(Profit,MTenure)## [1] 0.4388692cor(Profit,CTenure)## [1] 0.2576789TASK 2 (k)
Use R to construct the following Corrgram based on all variables in the dataset.
library(corrgram)
par(mfrow = c(1,1))
cols <- colorRampPalette(c("red","lightblue2"))
corrgram(store.df,order = FALSE,
upper.panel = panel.pie,
lower.panel = panel.shade,
text.panel = panel.txt,
main = "Corrgram of store variables",
col.regions = cols
)
TASK 2 (l) - Pearson’s Correlation Tests
Run a Pearson’s Correlation test on the correlation between Profit and MTenure. What is the p-value? Run a Pearson’s Correlation test on the correlation between Profit and CTenure. What is the p-value?
cor.test(Profit,MTenure)##
## Pearson's product-moment correlation
##
## data: Profit and 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.4388692cor.test(Profit,CTenure)##
## Pearson's product-moment correlation
##
## data: Profit and 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.2576789The p-values are 8.193e-5 and 0.02562 respectively for correlation between Profit and MTenure, and Profit and CTenure. Thus, there exists a relation between Profit and MTenure, as well as Profit and CTenure neglecting the null hypothesis since the p values < 0.05.
TASK 2 (m) - Regression Analysis
Run a regression of Profit on {MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24, Visibility}.
fit <- lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility)
fit##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility)
##
## Coefficients:
## (Intercept) MTenure CTenure Comp Pop
## 7610.041 760.993 944.978 -25286.887 3.667
## PedCount Res Hours24 Visibility
## 34087.359 91584.675 63233.307 12625.447TASK 2 (n)
Based on TASK 2m, answer the following questions:
List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05). List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility)$coefficient## (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.447050summary(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-12The explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05) are MTenure, CTenure, Comp, Pop, PedCount, Res and Hours24.
The explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05) are the intercept and Visibility.
TASK 2 (o)
Based on TASK 2m, answer the following questions:
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? 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?
lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility)$coefficient## (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.447050The 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 is 760.99 dollars according to the coefficient of MTenure obtained from the regression analysis.
The 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 is 944.98 dollars according to the coefficient of CTenure obtained from the regression analysis.
This is the end of the analysis of the Managing Employee Retention Data.