Store 24
Aayushi Shanker
January 16, 2018
Store24: Managing Employee Retention
This case revolves around a chain of stores named Store24. The CEO,CFO and COO have addressed the need of analysis of relation between the tenure of managers and crew members in their stores and the profits gained. They have done so to take more informed decisions regarding the strategies to be adopted to increase employee retention.
The data is provided with the following variables:
Sales -> Fiscal Year 2000 Sales
Profit -> Fiscal Year 2000 Profit before corporate overhead allocations, rent, and depreciation
MTenure -> Average manager tenure during FY-2000 where tenure is defined as the number of months of experience with Store24
CTenure -> Average crew tenure during FY-2000 where tenure is defined as the number of months of experience with Store24
Comp -> Number of competitors per 10,000 people within a ½ mile radius
Pop -> Population within a ½ mile radius
Visible -> 5-point rating on visibility of store front with 5 being the highest
PedCount -> 5-point rating on pedestrian foot traffic volume with 5 being the highest
Hours24 -> Indicator for open 24 hours or not
Res -> Indicator for located in residential vs. industrial area
- Reading the data from a CSV format file into R
setwd("C:/Users/Dell/Desktop/Project/Week 3/Day 1")
store=read.csv("Store24.csv")
View(store)- Summarizing the data given
library(psych)
describe(store)## 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.46- Mean and Standard deviation of Profit, MTenure and CTenure
mean(store$Profit)## [1] 276313.6sd(store$Profit)## [1] 89404.08mean(store$MTenure)## [1] 45.29644sd(store$MTenure)## [1] 57.67155mean(store$CTenure)## [1] 13.9315sd(store$CTenure)## [1] 17.69752- Listing the 10 most and least profitable companies
top=store[order(-store$Profit),]
top[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.80105bottom=store[order(store$Profit),]
bottom[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.68654- Plotting the scatterplot of Profit versus CTenure
library(ggplot2)##
## Attaching package: 'ggplot2'## The following objects are masked from 'package:psych':
##
## %+%, alphalibrary(gtable)
library(grid)
p1=ggplot(store,aes(CTenure,Profit))+geom_point()+expand_limits(y=c(min(store$Profit)-0.1*diff(range(store$Profit)),max(store$Profit)+0.1*diff(range(store$Profit))))+expand_limits(x=c(min(store$CTenure)-0.1*diff(range(store$CTenure)),max(store$CTenure)+0.1*diff(range(store$CTenure))))
p2=ggplot(store,aes(factor(1),Profit))+geom_boxplot()+expand_limits(y=c(min(store$Profit)-0.1*diff(range(store$Profit)),max(store$Profit)+0.1*diff(range(store$Profit))))+theme(axis.text=element_blank(),axis.title=element_blank())
p3=ggplot(store,aes(factor(1),CTenure))+geom_boxplot()+expand_limits(y=c(min(store$CTenure)-0.1*diff(range(store$CTenure)),max(store$CTenure)+0.1*diff(range(store$CTenure))))+theme(axis.text=element_blank(),axis.title=element_blank())+coord_flip()
gt1=ggplot_gtable(ggplot_build(p1))
gt2=ggplot_gtable(ggplot_build(p2))
gt3=ggplot_gtable(ggplot_build(p3))
gt=gtable(widths=unit(c(7,1),"null"),heights=unit(c(1,7),"null"))
gt=gtable_add_grob(gt, gt1, 2, 1)
gt=gtable_add_grob(gt, gt2, 2, 2)
gt=gtable_add_grob(gt, gt3, 1, 1)
grid.draw(gt)
- Plotting the scatterplot of Profit versus MTenure
p1=ggplot(store,aes(MTenure,Profit))+geom_point()+expand_limits(y=c(min(store$Profit)-0.1*diff(range(store$Profit)),max(store$Profit)+0.1*diff(range(store$Profit))))+expand_limits(x=c(min(store$MTenure)-0.1*diff(range(store$MTenure)),max(store$MTenure)+0.1*diff(range(store$MTenure))))
p2=ggplot(store,aes(factor(1),Profit))+geom_boxplot()+expand_limits(y=c(min(store$Profit)-0.1*diff(range(store$Profit)),max(store$Profit)+0.1*diff(range(store$Profit))))+theme(axis.text=element_blank(),axis.title=element_blank())
p3=ggplot(store,aes(factor(1),MTenure))+geom_boxplot()+expand_limits(y=c(min(store$MTenure)-0.1*diff(range(store$MTenure)),max(store$MTenure)+0.1*diff(range(store$MTenure))))+theme(axis.text=element_blank(),axis.title=element_blank())+coord_flip()
gt1=ggplot_gtable(ggplot_build(p1))
gt2=ggplot_gtable(ggplot_build(p2))
gt3=ggplot_gtable(ggplot_build(p3))
gt=gtable(widths=unit(c(7,1),"null"),heights=unit(c(1,7),"null"))
gt=gtable_add_grob(gt, gt1, 2, 1)
gt=gtable_add_grob(gt, gt2, 2, 2)
gt=gtable_add_grob(gt, gt3, 1, 1)
grid.draw(gt)
- Correlation matrix for all variables
library(corrplot)## corrplot 0.84 loadedcor=cor(store)
corrplot(cor,method="circle")
- Correlation measured between Profit & MTenure
Prof_MTen=cor(store$Profit,store$MTenure)
Prof_MTen## [1] 0.4388692- Correlation measured between Profit & CTenure
Prof_CTen=cor(store$Profit,store$CTenure)
Prof_CTen## [1] 0.2576789- Constructing a Corrgram of all the store variables
library(corrgram)
corrgram(store,order=TRUE,lower.panel=panel.shade,upper.panel=panel.pie,text.panel=panel.txt,main="Corrgram of Store Variables")
We can observe from the corrgram that as far as profit is concerned, the most correlated variables include Sales, MTenure, Pop, Pedcount, CTenure, MgrSkill, ServQual
- Running a Pearson’s Correlation test on the 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- Running a Pearson’s Correlation test on the 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.2576789Between MTenure and CTenure, it is clear that MTenure is more correlated with Profit than CTenure.
- Running a regression of Profit on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility}
fit=lm(Profit~MTenure+CTenure+Comp+Pop+PedCount+Res+Hours24+Visibility, data=store)
summary(fit)##
## 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-12From the summary of regression analysis, we can make out that Visibility, Res, Pop are some of the statistically insignificant variables by consider their p-values.
- Preparing the Regression equation
Intercept=coef(fit)[1]
XMTenure=coef(fit)[2]
XCTenure=coef(fit)[3]
XComp=coef(fit)[4]
XPop=coef(fit)[5]
XPedCount=coef(fit)[6]
XRes=coef(fit)[7]
XHours24=coef(fit)[8]
XVisibility=coef(fit)[9]
Prof_fun=function(x1,x2,x3,x4,x5,x6,x7,x8){
profit=Intercept+XMTenure*x1+XCTenure*x2+XComp*x3+XPop*x4+XPedCount*x5+XRes*x6+XHours24*x7+XVisibility*x8
return(profit)
}
#Change in Profit if the Manager's tenure increases by 1 month
Profit.before=Prof_fun(mean(store$MTenure),mean(store$CTenure),mean(store$Comp),mean(store$Pop),mean(store$PedCount),mean(store$Res),mean(store$Hours24),mean(store$Visibility))
Profit.after=Prof_fun(mean(store$MTenure)+1,mean(store$CTenure),mean(store$Comp),mean(store$Pop),mean(store$PedCount),mean(store$Res),mean(store$Hours24),mean(store$Visibility))
Change=Profit.after-Profit.before
Change## (Intercept)
## 760.9927#Change in Profit if the Crew's tenure increases by 1 month
Profit.before=Prof_fun(mean(store$MTenure),mean(store$CTenure),mean(store$Comp),mean(store$Pop),mean(store$PedCount),mean(store$Res),mean(store$Hours24),mean(store$Visibility))
Profit.after=Prof_fun(mean(store$MTenure),mean(store$CTenure)+1,mean(store$Comp),mean(store$Pop),mean(store$PedCount),mean(store$Res),mean(store$Hours24),mean(store$Visibility))
Change=Profit.after-Profit.before
Change## (Intercept)
## 944.978EXECUTIVE ANALYSIS
We can see that:
A.) MTenure is more correlated with Profit than CTenure.
B.) But the increase in profit, after the tenure of a crew member is increased by a month, is more than that obtained when the tenure of a manager is increased. This is happening because with an increase in crew member’s tenure by a month, his or her skills are developing which shows an immediate effect in that particular month’s profit figures. Hence, steps must be taken to increase crew member’s retention in the stores, especially the ones targeted for skill development (like new training programs or developing a career development program).
C.) Take a close look below:
# Average manager's tenure of the 10 most profitable stores
top_avg=mean(top$MTenure[1:10])
top_avg## [1] 110.6299# Average manager's tenure of the 10 least profitable stores
bottom_avg=mean(bottom$MTenure[1:10])
bottom_avg## [1] 27.36832# Average manager's skill rating of the 10 most profitable stores
top_avgskill=mean(top$MgrSkill[1:10])
top_avgskill## [1] 4.006222# Average manager's tenure of the 10 least profitable stores
bottom_avgskill=mean(bottom$MgrSkill[1:10])
bottom_avgskill## [1] 3.524C.) Clearly, we cannot ignore the fact that the average tenure of a manager is quite high in profitable stores than the lesser profitable ones despite the fact that there is not a large difference in the skill rating of managers in both sections of stores. In order to ensure more managers are retained in the less profitable bracket of stores, we need to increase their satisfaction level by increasing wages or issuing bonuses.
D.) Moreover, on removing the previously mentioned statistically insignificant variables (Visibility, Res, Pop) we obtain a regression equation with a lesser value of R-squared making our analysis even better, like shown below:
fit=lm(Profit~MTenure+CTenure+Comp+PedCount+Hours24, data=store)
summary(fit)##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + PedCount + Hours24,
## data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -122472 -43526 -5550 27835 123730
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 170626.3 39667.5 4.301 5.48e-05 ***
## MTenure 747.6 133.6 5.594 4.15e-07 ***
## CTenure 681.9 423.6 1.610 0.1120
## Comp -25645.2 5702.8 -4.497 2.71e-05 ***
## PedCount 39228.2 7550.5 5.195 1.97e-06 ***
## Hours24 51603.3 20605.1 2.504 0.0146 *
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 61220 on 69 degrees of freedom
## Multiple R-squared: 0.5628, Adjusted R-squared: 0.5312
## F-statistic: 17.77 on 5 and 69 DF, p-value: 2.805e-11We can observe that there exists more variables like Comp, PedCount, Hours24 that contribute to the profits other than Manager’s Tenure and Crew’s Tenure. Hence, they must be taken care of as well.