Analysis of the Case Store24 (A): Managing Employee Retention
Aishwarya
February 12, 2018
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("C:/Users/AEAIRA/Desktop/Internship/StoreData")
store<- read.csv(paste("Store24.csv", sep=""))
View(store)
library(psych)
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.00TASK 4d 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.
mean(store$Profit)## [1] 276313.6sd(store$Profit)## [1] 89404.08The mean of the profit is 276313.6 and the standard deviation is 89404.08
Use R to measure the mean and standard deviation of MTenure.
mean(store$MTenure)## [1] 45.29644sd(store$MTenure)## [1] 57.67155The mean of the MTenure is 45.29644 and the standard deviation is 57.67155
Use R to measure the mean and standard deviation of CTenure.
mean(store$CTenure)## [1] 13.9315sd(store$CTenure)## [1] 17.69752he mean of the CTenure is 13.9315 and the standard deviation is 17.69752
TASK 4e - 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)
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)Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.
top10 <- store[order(store$Profit),]
View(top10)
top10[1:20,]## 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
## 56 56 916197 189235 4.7974240 2.726899 13740 4.597269 2
## 49 49 983296 195276 55.4003900 14.685830 1863 3.713871 4
## 16 16 883864 196277 23.6585300 4.681725 6872 3.344703 3
## 72 72 848140 196772 126.4745000 27.449690 3151 3.680586 2
## 71 71 977566 198529 43.8997200 38.373720 3265 3.856324 2
## 62 62 942915 202641 12.1578600 6.866530 9820 4.201450 3
## 38 38 991524 203184 15.6080600 1.577002 6557 4.225993 3
## 15 15 1005627 203951 0.0000000 8.476386 8684 3.844220 3
## 4 4 1053860 210122 0.0000000 5.371663 2797 4.253946 4
## 31 31 993597 211885 0.0000000 10.776180 2578 3.100689 2
## 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
## 56 3 1 0 3.10 3.800000 64.35046
## 49 1 1 1 3.23 3.576667 81.58205
## 16 3 1 0 3.05 4.000000 97.36939
## 72 1 1 1 3.73 3.416667 73.68654
## 71 1 1 1 3.88 3.466667 79.94253
## 62 4 1 0 3.08 3.300000 61.40299
## 38 2 1 1 3.68 4.494444 100.00000
## 15 4 1 1 3.50 3.427778 94.73510
## 4 2 1 1 2.06 4.100000 100.00000
## 31 2 1 1 2.86 3.673333 88.92860Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.
bottom10 <- store[order(-store$Profit),]
View(bottom10)
bottom10[1:20,]## store Sales Profit MTenure CTenure Pop Comp Visibility
## 74 74 1782957 518998 171.097200 29.519510 10913 2.319850 3
## 7 7 1809256 476355 62.530800 7.326488 17754 3.377900 2
## 9 9 2113089 474725 108.993500 6.061602 26519 2.637630 2
## 6 6 1703140 469050 149.935900 11.351130 16926 3.184613 3
## 44 44 1807740 439781 182.236400 114.151900 20624 3.628561 3
## 2 2 1619874 424007 86.222190 6.636550 8630 4.235555 4
## 45 45 1602362 410149 47.645650 9.166325 17808 3.472609 5
## 18 18 1704826 394039 239.969800 33.774130 3807 3.994713 5
## 11 11 1583446 389886 44.819770 2.036961 21550 3.272398 2
## 47 47 1665657 387853 12.847900 6.636550 23623 2.422707 2
## 34 34 1557084 382199 29.178850 19.745380 10923 2.361195 4
## 69 69 1574290 375393 44.129730 26.759750 5050 3.949484 3
## 22 22 1433440 367036 18.368220 25.954830 8280 4.464360 4
## 53 53 1355684 365018 57.240490 8.246407 6909 3.156869 2
## 67 67 1228052 362067 5.257451 3.416838 11552 3.583143 3
## 8 8 1378482 361115 0.000000 56.772080 20824 2.895114 4
## 60 60 1433624 356071 33.516250 6.406571 8845 2.719548 3
## 43 43 1296711 337233 177.570400 5.486653 3495 3.653641 4
## 30 30 1874873 333607 73.341440 23.425050 1116 3.578323 3
## 12 12 1444714 329020 277.987700 6.636550 11160 4.903895 4
## 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.80105
## 34 4 1 1 3.58 3.577778 97.36939
## 69 3 1 1 3.56 3.940000 92.89294
## 22 3 1 1 3.30 3.973333 100.00000
## 53 2 1 1 3.63 3.683333 94.73510
## 67 3 1 1 3.37 4.150000 97.36939
## 8 3 1 1 3.98 3.800000 78.94776
## 60 4 1 1 3.37 3.344445 57.89552
## 43 3 1 1 3.73 3.608233 94.73510
## 30 2 1 1 3.52 3.473333 100.00000
## 12 4 1 0 3.35 3.238889 100.00000TASK 4g - Scatter Plots
Use R to draw a scatter plot of Profit vs. MTenure.
plot(store$MTenure, store$Profit,
col="blue",
main="A scatterplot of Profit vs. Manager Tenure",
xlab="MTenure", ylab="Profit")
abline(v=mean(store$MTenure),col='dark blue', lty="dotted")
abline(h=mean(store$Profit),col='dark blue', lty="dotted")
abline(lm(store$MTenure ~ store$Profit))
Use R to draw a scatter plot of Profit vs. CTenure.
plot(store$CTenure, store$Profit,
col="blue",
main="A scatterplot of Profit vs. Manager Tenure",
xlab="CTenure", ylab="Profit")
abline(v=mean(store$CTenure),col='dark blue', lty="dotted")
abline(h=mean(store$Profit),col='dark blue', lty="dotted")
abline(lm(store$CTenure ~ store$Profit))
Use R to construct a Correlation Matrix for all the variables in the dataset. (Display the numbers up to 2 Decimal places)
matrix<- cor(store)
round(matrix,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.00Use R to measure the correlation between Profit and MTenure. (Display the numbers up to 2 Decimal places)
matrix1<-cor(store$Profit,store$MTenure)
round(matrix1,2)## [1] 0.44Use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)
matrix2<-cor(store$Profit,store$CTenure)
round(matrix2,2)## [1] 0.26Use 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")
Run a Pearson’s Correlation test on the correlation between Profit and MTenure. What is the p-value?
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.4388692The p-value is 8.193e-05
Run a Pearson’s Correlation test on the correlation between Profit and CTenure. What is the p-value?
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.2576789The p-value is 0.02562
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, 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-12List 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?
The profit will increase by 760.9 if the manager’s tenure 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?
The profit will increase by 944.97 if the crew’s tenure increases by one month.
Please prepare an “Executive Summary”. Please add this to the end of your Rmd file. Specifically, please create a qualitative summary of Managerial Insights, based on your data analysis, especially your Regression Analysis. You may write this in paragraph form or in point form.
From the p value of the above analysis, we reject the null hypothesis that the profit is independent of these variables. Now profit is not exactly dependent on the visibility of the store. Now for the rest of the variables, manager tenure and crew tenure positively affect the profits, therefore the company should try to retain them by incentives and taking feedback on why they leave and look to eliminate those reasons. Now, there is a correlation between Crew/manager skill and their tenure, thus the company must invest in training programmes for the crew as well as select more skilled managers to increase profits. Also, more population and less competition both obviuosly increase the profits and hence now store locations should be selected with these in mind. Also if the store is in residential area, there is a positive impact on sales and 24 hours open stores also see rise in profits.