MANAGING EMPLOYEE RETENTION-HCS
rahulchintu
January 15, 2018
.....### STORE24(A):MANAGING EMPLOYEE RETENTION###.....
....##Under the guidance of Prof.Sameer Mathur(Ph.D,Carnige Mellon University),IIM-LUCKNOW##....#To read the data from .csv file#
storedata.df <- read.csv(paste("store24.csv") )
View(storedata.df)#To summarize all the given data by command function summary#
summary(storedata.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.00#To describe the data by command function describe#
library(psych)
describe(storedata.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.46##Three very important variables in this analysis are the store Profit, the management tenure (MTenure) and the crew tenure (CTenure).##
#To measure the mean of Profit.#
mean(storedata.df$Profit)## [1] 276313.6#To measure standard deviation of Profit.#
sd(storedata.df$Profit)## [1] 89404.08#To measure the mean of MTenure.#
mean(storedata.df$MTenure)## [1] 45.29644#To measure the standard deviation of MTenure.#
sd(storedata.df$MTenure)## [1] 57.67155#To measure the mean and standard deviation of CTenure.#
mean(storedata.df$CTenure)## [1] 13.9315#To measure the mean and standard deviation of CTenure.#
sd(storedata.df$CTenure)## [1] 17.69752#To sort a dataframe based on a data column.#
attach(storedata.df)
View(storedata.df)
data <- storedata.df[order(Profit),] # sort by profit (ascending)#
View(data)
data[1:10,] # see the first 10 rows ### 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.68654data <- storedata.df[order(-Profit),] # sort by profit (descending)#
View(data)
data[1:10] # see the last 10 rows ### store Sales Profit MTenure CTenure Pop Comp Visibility
## 74 74 1782957 518998 171.0972000 29.5195100 10913 2.319850 3
## 7 7 1809256 476355 62.5308000 7.3264880 17754 3.377900 2
## 9 9 2113089 474725 108.9935000 6.0616020 26519 2.637630 2
## 6 6 1703140 469050 149.9359000 11.3511300 16926 3.184613 3
## 44 44 1807740 439781 182.2364000 114.1519000 20624 3.628561 3
## 2 2 1619874 424007 86.2221900 6.6365500 8630 4.235555 4
## 45 45 1602362 410149 47.6456500 9.1663250 17808 3.472609 5
## 18 18 1704826 394039 239.9698000 33.7741300 3807 3.994713 5
## 11 11 1583446 389886 44.8197700 2.0369610 21550 3.272398 2
## 47 47 1665657 387853 12.8479000 6.6365500 23623 2.422707 2
## 34 34 1557084 382199 29.1788500 19.7453800 10923 2.361195 4
## 69 69 1574290 375393 44.1297300 26.7597500 5050 3.949484 3
## 22 22 1433440 367036 18.3682200 25.9548300 8280 4.464360 4
## 53 53 1355684 365018 57.2404900 8.2464070 6909 3.156869 2
## 67 67 1228052 362067 5.2574510 3.4168380 11552 3.583143 3
## 8 8 1378482 361115 0.0000000 56.7720800 20824 2.895114 4
## 60 60 1433624 356071 33.5162500 6.4065710 8845 2.719548 3
## 43 43 1296711 337233 177.5704000 5.4866530 3495 3.653641 4
## 30 30 1874873 333607 73.3414400 23.4250500 1116 3.578323 3
## 12 12 1444714 329020 277.9877000 6.6365500 11160 4.903895 4
## 35 35 1443230 322624 36.9993100 14.8008200 14361 3.613021 4
## 46 46 1339214 315780 6.1775050 5.2566730 9285 3.144458 4
## 59 59 1334898 303069 13.3079200 13.7659100 6231 3.301353 3
## 65 65 1349972 301641 150.2317000 23.4250500 1075 3.218960 3
## 5 5 1227841 300480 3.8773700 6.8665300 20335 1.651364 2
## 75 75 1321870 296826 2.2672760 8.7063660 8966 1.886111 4
## 33 33 1369092 292745 51.7201700 3.8767970 8177 5.309016 3
## 48 48 1243167 284169 31.4789900 8.2464070 8491 4.848749 3
## 21 21 1237518 282584 24.1185600 7.2114990 14022 4.020201 3
## 25 25 1282886 282124 0.0000000 10.3162200 6183 3.517020 3
## 73 73 1115450 279193 41.1395500 6.4065710 6276 4.180132 4
## 10 10 1080979 278625 31.4789900 23.1950700 16381 2.270771 4
## 23 23 1351972 277414 12.3878700 3.4168380 13797 3.594539 3
## 28 28 1141465 273036 23.8885400 16.9856300 14673 3.193422 3
## 20 20 1320950 269235 65.0609500 5.9466120 15377 4.148495 3
## 24 24 1071307 267354 44.8197700 3.4168380 9069 3.280590 2
## 17 17 1095695 265584 31.7090000 3.6468170 14477 2.561704 3
## 1 1 1060294 265014 0.0000000 24.8049300 7535 2.797888 3
## 42 42 1273855 264072 2.4972890 86.0944600 2106 3.231049 3
## 29 29 924782 263956 19.5182900 23.5400400 11350 5.392077 3
## 14 14 1171491 261571 87.3722600 2.9568790 6898 4.233057 4
## 19 19 1127332 261495 3.4173430 16.9856300 4669 2.753616 2
## 70 70 1207204 254203 14.9180200 3.8767970 19809 3.122484 3
## 50 50 935257 251013 12.8479000 16.0657100 14653 1.751638 3
## 63 63 1045264 239036 8.2476260 6.8665300 7581 4.136580 3
## 51 51 1027035 237344 3.4173430 7.0965090 3126 2.447474 2
## 68 68 1018195 236339 17.4481600 2.2669400 9018 3.504810 4
## 27 27 985862 230194 50.1100800 17.4455900 8153 3.719806 3
## 58 58 989760 227601 4.5674100 4.1067760 8477 3.993874 4
## 40 40 1042664 222913 122.7943000 16.7556500 2521 11.127880 3
## 3 3 1099921 222735 23.8885400 5.0266940 9695 4.494666 3
## 64 64 969509 221157 0.0000000 0.8870637 17110 2.378613 4
## 39 39 979361 221130 34.6991800 5.4866530 8896 5.046338 2
## 36 36 1016950 219292 41.5995800 20.8952800 3218 3.929021 3
## 26 26 898548 211912 0.6571813 20.4353200 9999 4.178195 3
## 31 31 993597 211885 0.0000000 10.7761800 2578 3.100689 2
## 4 4 1053860 210122 0.0000000 5.3716630 2797 4.253946 4
## 15 15 1005627 203951 0.0000000 8.4763860 8684 3.844220 3
## 38 38 991524 203184 15.6080600 1.5770020 6557 4.225993 3
## 62 62 942915 202641 12.1578600 6.8665300 9820 4.201450 3
## 71 71 977566 198529 43.8997200 38.3737200 3265 3.856324 2
## 72 72 848140 196772 126.4745000 27.4496900 3151 3.680586 2
## 16 16 883864 196277 23.6585300 4.6817250 6872 3.344703 3
## 49 49 983296 195276 55.4003900 14.6858300 1863 3.713871 4
## 56 56 916197 189235 4.7974240 2.7268990 13740 4.597269 2
## 37 37 1202917 187765 23.1985000 1.3470230 8870 4.491863 3
## 61 61 716589 177046 21.8184200 13.3059500 3014 3.263994 3
## 52 52 1073008 169201 24.1185600 3.4168380 14859 6.585143 3
## 54 54 811190 159792 6.6703910 3.8767970 3747 3.756011 3
## 13 13 857843 152513 0.6571813 1.5770020 14186 4.435671 3
## 32 32 828918 149033 36.0792600 6.6365500 9697 4.641468 3
## 55 55 925744 147672 6.6703910 18.3655000 10532 6.389294 4
## 41 41 744211 147327 14.9180200 11.9260800 9701 4.364600 2
## 66 66 879581 146058 115.2039000 3.8767970 1046 6.569790 2
## 57 57 699306 122180 24.3485700 2.9568790 3642 2.973376 3
## PedCount Res
## 74 4 1
## 7 5 1
## 9 4 1
## 6 4 1
## 44 4 0
## 2 3 1
## 45 3 1
## 18 3 1
## 11 5 1
## 47 5 1
## 34 4 1
## 69 3 1
## 22 3 1
## 53 2 1
## 67 3 1
## 8 3 1
## 60 4 1
## 43 3 1
## 30 2 1
## 12 4 1
## 35 3 1
## 46 3 1
## 59 3 1
## 65 1 1
## 5 5 0
## 75 4 0
## 33 2 1
## 48 2 1
## 21 3 1
## 25 3 1
## 73 3 1
## 10 3 1
## 23 4 1
## 28 4 1
## 20 2 1
## 24 3 1
## 17 4 1
## 1 3 1
## 42 2 1
## 29 2 1
## 14 2 1
## 19 3 1
## 70 4 1
## 50 4 1
## 63 3 1
## 51 4 1
## 68 2 1
## 27 2 1
## 58 2 1
## 40 4 1
## 3 3 1
## 64 3 1
## 39 4 1
## 36 2 1
## 26 2 1
## 31 2 1
## 4 2 1
## 15 4 1
## 38 2 1
## 62 4 1
## 71 1 1
## 72 1 1
## 16 3 1
## 49 1 1
## 56 3 1
## 37 3 1
## 61 1 1
## 52 3 1
## 54 2 1
## 13 2 1
## 32 3 1
## 55 3 1
## 41 3 1
## 66 3 1
## 57 2 1#To print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.#
mpdata <- storedata.df[order(Profit),]
View(mpdata)
mpdata[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.347023#To print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.#
lpdata <- storedata.df[order(-Profit),]
View(lpdata)
lpdata[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.636550library(car)##
## Attaching package: 'car'## The following object is masked from 'package:psych':
##
## logit#To draw a scatter plot : profit Vs MTenure #
scatterplot(Profit ~ MTenure, data=storedata.df,
xlab="MTenure", ylab="Profit",
main="Scatterplot of Profit Vs MTenure")
# To Draw a scatter plot of Profit vs. CTenure#
scatterplot(Profit
~ CTenure, data =storedata.df,
xlab="CTenure",ylab="Profit",
main="Scatterplot of Profit Vs CTenure")
# To Construct a Correlation Matrix for all the variables in the dataset. (Displaying the numbers up to 2 Decimal places)#
round(cor(storedata.df),2)## store Sales Profit MTenure CTenure Pop Comp Visibility
## store 1.00 -0.23 -0.20 -0.06 0.02 -0.29 0.03 -0.03
## Sales -0.23 1.00 0.92 0.45 0.25 0.40 -0.24 0.13
## Profit -0.20 0.92 1.00 0.44 0.26 0.43 -0.33 0.14
## MTenure -0.06 0.45 0.44 1.00 0.24 -0.06 0.18 0.16
## CTenure 0.02 0.25 0.26 0.24 1.00 0.00 -0.07 0.07
## Pop -0.29 0.40 0.43 -0.06 0.00 1.00 -0.27 -0.05
## Comp 0.03 -0.24 -0.33 0.18 -0.07 -0.27 1.00 0.03
## Visibility -0.03 0.13 0.14 0.16 0.07 -0.05 0.03 1.00
## PedCount -0.22 0.42 0.45 0.06 -0.08 0.61 -0.15 -0.14
## Res -0.03 -0.17 -0.16 -0.06 -0.34 -0.24 0.22 0.02
## Hours24 0.03 0.06 -0.03 -0.17 0.07 -0.22 0.13 0.05
## CrewSkill 0.05 0.16 0.16 0.10 0.26 0.28 -0.04 -0.20
## MgrSkill -0.07 0.31 0.32 0.23 0.12 0.08 0.22 0.07
## ServQual -0.32 0.39 0.36 0.18 0.08 0.12 0.02 0.21
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## store -0.22 -0.03 0.03 0.05 -0.07 -0.32
## Sales 0.42 -0.17 0.06 0.16 0.31 0.39
## Profit 0.45 -0.16 -0.03 0.16 0.32 0.36
## MTenure 0.06 -0.06 -0.17 0.10 0.23 0.18
## CTenure -0.08 -0.34 0.07 0.26 0.12 0.08
## Pop 0.61 -0.24 -0.22 0.28 0.08 0.12
## Comp -0.15 0.22 0.13 -0.04 0.22 0.02
## Visibility -0.14 0.02 0.05 -0.20 0.07 0.21
## PedCount 1.00 -0.28 -0.28 0.21 0.09 -0.01
## Res -0.28 1.00 -0.09 -0.15 -0.03 0.09
## Hours24 -0.28 -0.09 1.00 0.11 -0.04 0.06
## CrewSkill 0.21 -0.15 0.11 1.00 -0.02 -0.03
## MgrSkill 0.09 -0.03 -0.04 -0.02 1.00 0.36
## ServQual -0.01 0.09 0.06 -0.03 0.36 1.00#To find Correlation between Profit and Mtenure#
round(cor(storedata.df),2)[3,4]## [1] 0.44#To find correlation between profit and CTenure#
round(cor(storedata.df),2)[3,5]## [1] 0.26#tO Construct the Corrgram FOR THE STORE24 #
library(corrgram)
cols <- colorRampPalette(c("gold", "blue",
"red", "darkgreen"))
corrgram(storedata.df,order=TRUE, col.regions=cols,
lower.panel = panel.shade,
upper.panel = panel.pie, text.panel = panel.txt,
main="Corrgram of store24 :MANAGING EMPLOYEE RETENTION")
#To run a Pearson's Correlation test on the correlation between Profit and MTenure. #
cor.test(storedata.df$Profit, storedata.df$MTenure,
method = "pearson")##
## Pearson's product-moment correlation
##
## data: storedata.df$Profit and storedata.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#To find the p-value.#
cor.test(storedata.df$Profit,storedata.df$MTenure)$p.value## [1] 8.193133e-05# By using Pearson's Correlation test finding the correlation between Profit and CTenure.#
cor.test(storedata.df$Profit, storedata.df$CTenure, method="pearson") ##
## Pearson's product-moment correlation
##
## data: storedata.df$Profit and storedata.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#To find the p-value.#
cor.test(storedata.df$Profit,storedata.df$CTenure)$p.value## [1] 0.0256203#Regression Analysis of Profit on {MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24, Visibility}#
rmodel <- lm(Profit~MTenure+CTenure+Comp+Pop+PedCount+Res+Hours24+Visibility, data=storedata.df)
summary(rmodel)##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility, data = storedata.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-12 #List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).#MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24
#List the explanatory variable(s) whose beta-coefficients are not statistically significant(p > 0.05).Visibility
#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?From the above analysis, we can decipher that if the Manager’s tenure is increased by a month, the profit changes by $760.99.
#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?From the above analysis, we can decipher that if the Crew’s tenure is increased by a month, the profit changes by $944.98.