Case Store24 (A)
Ayan Ghosh
17 December 2017
this is HBR Case Study Store24 (A): Managing Employee Retention
setwd("I:/Ayan7926/My Files/IIEST 2K15-2K20/Intern/Internship/Resources/Week 3/week 3 day 1")
store <- read.csv(paste("Store24.csv", sep=""))
View(store)
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)## 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.46mean,Sd of Profit,Ctenure,Mtenure
mean(store$Profit)## [1] 276313.6mean(store$MTenure)## [1] 45.29644mean(store$CTenure)## [1] 13.9315apply(store[,3:5],2,sd)## Profit MTenure CTenure
## 89404.07634 57.67155 17.69752top 10 and bottom profitable stores
ascorder<- store[order(store$Profit),]
View(ascorder)
ascorder[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.347023descorder<- store[order(-store$Profit),]
View(descorder)
descorder[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.636550plot(store$MTenure,store$Profit,main="Scatterplot of Profit vs MTenure",xlab = "Mtenure",ylab = "Profit")
plot(store$CTenure,store$Profit,main="Scatterplot of Profit vs CTenure",xlab = "Ctenure",ylab = "Profit")
cor(store)## 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.000000000cor(store$Profit,store$MTenure)## [1] 0.4388692cor(store$Profit,store$CTenure)## [1] 0.2576789library(corrgram)
corrgram(store[,1:14],order=FALSE,main ="Corrgram of store variables",lower.panel=panel.shade,upper.panel=panel.pie,text.panel=panel.txt)
cor.test(store$Profit,store$MTenure,method = "pearson")##
## 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.4388692cor.test(store$Profit,store$CTenure,method = "pearson")##
## 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.2576789the p-value in both the cases are 8.193e-05 and 0.02562 respectively.
fit<-lm(Profit~MTenure,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ MTenure, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -177817 -52029 -8635 50871 188316
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 245496.3 11906.4 20.619 < 2e-16 ***
## MTenure 680.3 163.0 4.173 8.19e-05 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 80880 on 73 degrees of freedom
## Multiple R-squared: 0.1926, Adjusted R-squared: 0.1815
## F-statistic: 17.41 on 1 and 73 DF, p-value: 8.193e-05fit$coefficients## (Intercept) MTenure
## 245496.2904 680.3475fit<-lm(Profit~CTenure,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ CTenure, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -139848 -64869 -9022 45057 222393
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 258178.4 12814.4 20.148 <2e-16 ***
## CTenure 1301.7 571.3 2.279 0.0256 *
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 86970 on 73 degrees of freedom
## Multiple R-squared: 0.0664, Adjusted R-squared: 0.05361
## F-statistic: 5.192 on 1 and 73 DF, p-value: 0.02562fit$coefficients## (Intercept) CTenure
## 258178.442 1301.739fit<-lm(Profit~Comp,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ Comp, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -172707 -65521 -24559 56628 209205
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 362702 30119 12.042 < 2e-16 ***
## Comp -22807 7520 -3.033 0.00335 **
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 84830 on 73 degrees of freedom
## Multiple R-squared: 0.1119, Adjusted R-squared: 0.09975
## F-statistic: 9.2 on 1 and 73 DF, p-value: 0.003351fit$coefficients## (Intercept) Comp
## 362702.27 -22807.37fit<-lm(Profit~Pop,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ Pop, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -152198 -52285 -17228 43501 235602
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 2.123e+05 1.829e+04 11.611 < 2e-16 ***
## Pop 6.513e+00 1.598e+00 4.077 0.000115 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 81240 on 73 degrees of freedom
## Multiple R-squared: 0.1854, Adjusted R-squared: 0.1743
## F-statistic: 16.62 on 1 and 73 DF, p-value: 0.000115fit$coefficients## (Intercept) Pop
## 212323.4932 6.5126fit<-lm(Profit~PedCount,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ PedCount, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -131878 -57678 -1538 45741 200501
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 156254 29373 5.320 1.09e-06 ***
## PedCount 40561 9415 4.308 5.06e-05 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 80370 on 73 degrees of freedom
## Multiple R-squared: 0.2027, Adjusted R-squared: 0.1918
## F-statistic: 18.56 on 1 and 73 DF, p-value: 5.057e-05fit$coefficients## (Intercept) PedCount
## 156253.57 40560.82fit<-lm(Profit~Res,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ Res, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -151243 -62419 -9467 57891 245575
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 345696 51305 6.738 3.18e-09 ***
## Res -72273 52363 -1.380 0.172
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 88860 on 73 degrees of freedom
## Multiple R-squared: 0.02543, Adjusted R-squared: 0.01208
## F-statistic: 1.905 on 1 and 73 DF, p-value: 0.1717fit$coefficients## (Intercept) Res
## 345695.67 -72272.97fit<-lm(Profit~Hours24,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ Hours24, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -153138 -64315 -11246 52884 237458
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 281540 25976 10.84 <2e-16 ***
## Hours24 -6222 28343 -0.22 0.827
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 89980 on 73 degrees of freedom
## Multiple R-squared: 0.0006598, Adjusted R-squared: -0.01303
## F-statistic: 0.0482 on 1 and 73 DF, p-value: 0.8268fit$coefficients## (Intercept) Hours24
## 281540.417 -6222.385fit<-lm(Profit~Visibility,data = store)
summary(fit)##
## Call:
## lm(formula = Profit ~ Visibility, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -152838 -63359 -10946 43839 243980
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 226431 43855 5.163 2.02e-06 ***
## Visibility 16196 13840 1.170 0.246
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 89180 on 73 degrees of freedom
## Multiple R-squared: 0.01841, Adjusted R-squared: 0.004966
## F-statistic: 1.369 on 1 and 73 DF, p-value: 0.2457fit$coefficients## (Intercept) Visibility
## 226430.94 16195.67the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05)- 1.Ctenure (0.025) 2.Comp(0.003) 3.Pop(0.0001) 4.PedCount(0.034) In () p-value are indicated (Ans)
the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05)- 1.Mtenure(0.0552) 2.Res(0.1717) 3.Hours24(0.8268) 4.Visibility(0.2457) In () p-value are indicated (Ans)
The 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 680.3475 units.(Ans)
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 1301.739 units.(Ans)