Managing Employee Retention
Srijani Das
February 12, 2018
This report is based on the analysis of the case Managing Employee Retention.
4c)
store <- read.csv(paste("Store24.csv", sep=""))
View(store)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.464d)
Use R to measure the mean and standard deviation of Profit.
mean(store$Profit)## [1] 276313.6sd(store$Profit)## [1] 89404.08Use R to measure the mean and standard deviation of MTenure.
mean(store$MTenure)## [1] 45.29644sd(store$MTenure)## [1] 57.67155Use R to measure the mean and standard deviation of CTenure.
mean(store$CTenure)## [1] 13.9315sd(store$CTenure)## [1] 17.697524e) Sorting and Subsetting data in R
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)4f) Replicate Exhibit 1 shown in the case, using R
attach(store)
View(store)
new <- store[order(-Profit),] # sort by Profit (descending)
View(new)
head(new[ , c(2, 3:5)], 10) # see the first 10 rows## Sales Profit MTenure CTenure
## 74 1782957 518998 171.09720 29.519510
## 7 1809256 476355 62.53080 7.326488
## 9 2113089 474725 108.99350 6.061602
## 6 1703140 469050 149.93590 11.351130
## 44 1807740 439781 182.23640 114.151900
## 2 1619874 424007 86.22219 6.636550
## 45 1602362 410149 47.64565 9.166325
## 18 1704826 394039 239.96980 33.774130
## 11 1583446 389886 44.81977 2.036961
## 47 1665657 387853 12.84790 6.636550tail(new[ , c(2, 3:5)], 10) # see the first 10 rows## Sales Profit MTenure CTenure
## 37 1202917 187765 23.1985000 1.347023
## 61 716589 177046 21.8184200 13.305950
## 52 1073008 169201 24.1185600 3.416838
## 54 811190 159792 6.6703910 3.876797
## 13 857843 152513 0.6571813 1.577002
## 32 828918 149033 36.0792600 6.636550
## 55 925744 147672 6.6703910 18.365500
## 41 744211 147327 14.9180200 11.926080
## 66 879581 146058 115.2039000 3.876797
## 57 699306 122180 24.3485700 2.956879#detach(store)4g) Scatter Plots
plot(store$MTenure, store$Profit,
col="black",
main="Scatterplot of Profit vs Mtenure",
xlab="MTenure", ylab="Profit ")
abline(lm(store$Profit ~ store$MTenure), col="green")
4h) Scatter Plots
plot(store$CTenure, store$Profit,
col="black",
main="Scatterplot of Profit vs Ctenure",
xlab="CTenure", ylab="Profit ")
abline(lm(store$Profit ~ store$CTenure), col="green")
4i) Correlation Matrix
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.0000000004j) Correlations
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.4388692cor.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.25767894k)Use R to construct the following Corrgram based on all variables in the dataset.
library(corrgram)
corrgram(store, order=TRUE, lower.panel=panel.shade,
upper.panel=panel.pie, text.panel=panel.txt,
main="Corrgram of store variables")
4l) Pearson’s Correlation Tests
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.4388692cor.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.25767893m) Regression Analysis
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-124n)
List of the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05): CTenure, Comp, Pop, PedCount, Res, Hours24
List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05). MTenure, Visibility
4p)Executive summary
Here Profit is the dependent variable and MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24, Visibility are independent variables. Here all regression coefficients are significantly different from zero, (p < 0.001). The multiple R-squared (0.6379) indicates that the model accounts for 63.79% of the variance in profit. The residual standard error (56970) can be thought of as the average error in predicting profit from the data set using this model. The F statistic tests whether the predictor variables, taken together, predict the response variable above chance levels.Here F-statistic 14.53