Store 24 DA
Shreya Singireddy
15 January 2018
Analysis of the Case Store24 (A): Managing Employee Retention
Reading the dataset
store.df<-read.csv(paste("Store24.csv"))
View(store.df)Summary and description of the provided data
summary(store.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.00library(psych)
describe(store.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.46Attaching the dataframe
attach(store.df)Measure the mean and standard deviation of Profit
mean(store.df$Profit)## [1] 276313.6sd(store.df$Profit)## [1] 89404.08Measure the mean and standard deviation of MTenure
mean(store.df$MTenure)## [1] 45.29644sd(store.df$MTenure)## [1] 57.67155Measure the mean and standard deviation of CTenure
mean(store.df$CTenure)## [1] 13.9315sd(store.df$CTenure)## [1] 17.69752How to sort a dataframe based on a data column. Copy-Paste it and Execute it.
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)Print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.
mp<- store.df[order(Profit), ]
mp[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.347023Print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.
lp<- store.df[order(-Profit), ]
lp[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.636550Draw a scatter plot of Profit vs. MTenure
library(car)##
## Attaching package: 'car'## The following object is masked from 'package:psych':
##
## logitscatterplot(Profit~MTenure, data=store.df,
xlab="MTenure", ylab="Profit",
main="Scatterplot of MTenure v/s Profit")
Draw a scatter plot of Profit vs. CTenure
scatterplot(Profit~CTenure, data =store.df,
xlab="CTenure",ylab="Profit",
main="Scatterplot of CTenure v/s Profit")
Construct a Correlation Matrix for all the variables in the dataset. (Display the numbers up to 2 Decimal places)
options(digits=2)
cor(store.df)## store Sales Profit MTenure CTenure Pop Comp Visibility
## store 1.000 -0.227 -0.200 -0.057 0.0199 -0.2894 0.032 -0.026
## Sales -0.227 1.000 0.924 0.455 0.2543 0.4035 -0.235 0.131
## Profit -0.200 0.924 1.000 0.439 0.2577 0.4306 -0.335 0.136
## MTenure -0.057 0.455 0.439 1.000 0.2434 -0.0609 0.181 0.157
## CTenure 0.020 0.254 0.258 0.243 1.0000 -0.0015 -0.070 0.067
## Pop -0.289 0.403 0.431 -0.061 -0.0015 1.0000 -0.268 -0.050
## Comp 0.032 -0.235 -0.335 0.181 -0.0703 -0.2683 1.000 0.028
## Visibility -0.026 0.131 0.136 0.157 0.0665 -0.0500 0.028 1.000
## PedCount -0.221 0.424 0.450 0.062 -0.0841 0.6076 -0.146 -0.141
## Res -0.031 -0.167 -0.159 -0.062 -0.3403 -0.2369 0.219 0.022
## Hours24 0.027 0.063 -0.026 -0.165 0.0729 -0.2218 0.130 0.047
## CrewSkill 0.049 0.164 0.160 0.102 0.2572 0.2828 -0.042 -0.197
## MgrSkill -0.072 0.312 0.323 0.230 0.1240 0.0836 0.224 0.073
## ServQual -0.322 0.386 0.362 0.182 0.0812 0.1239 0.018 0.210
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## store -0.2212 -0.031 0.027 0.049 -0.072 -0.3225
## Sales 0.4239 -0.167 0.063 0.164 0.312 0.3864
## Profit 0.4502 -0.159 -0.026 0.160 0.323 0.3625
## MTenure 0.0620 -0.062 -0.165 0.102 0.230 0.1817
## CTenure -0.0841 -0.340 0.073 0.257 0.124 0.0812
## Pop 0.6076 -0.237 -0.222 0.283 0.084 0.1239
## Comp -0.1463 0.219 0.130 -0.042 0.224 0.0181
## Visibility -0.1411 0.022 0.047 -0.197 0.073 0.2099
## PedCount 1.0000 -0.284 -0.276 0.214 0.087 -0.0054
## Res -0.2844 1.000 -0.089 -0.153 -0.032 0.0908
## Hours24 -0.2760 -0.089 1.000 0.105 -0.039 0.0583
## CrewSkill 0.2137 -0.153 0.105 1.000 -0.021 -0.0335
## MgrSkill 0.0875 -0.032 -0.039 -0.021 1.000 0.3567
## ServQual -0.0054 0.091 0.058 -0.034 0.357 1.0000Measure the correlation between Profit and MTenure.(Display the numbers up to 2 Decimal places)
cor(store.df$Profit, store.df$MTenure)## [1] 0.44options(digits=2)Measure the correlation between Profit and CTenure.(Display the numbers up to 2 Decimal places)
cor(store.df$Profit, store.df$CTenure)## [1] 0.26options(digits=2)Construct the following Corrgram based on all variables in the dataset.
library(corrgram)
corrgram(store.df,order=TRUE, lower.panel = panel.shade,
upper.panel = panel.pie, text.panel = panel.txt,
main="Corrgram of store24 dataset")
Run a Pearson’s Correlation test on the correlation between Profit and MTenure. What is the p-value?
cor.test(store.df$Profit, store.df$MTenure, method = "pearson")##
## Pearson's product-moment correlation
##
## data: store.df$Profit and store.df$MTenure
## t = 4, df = 70, p-value = 8e-05
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
## 0.24 0.61
## sample estimates:
## cor
## 0.44The p-value is 8e-05
Pearson’s Correlation test on the correlation between Profit and CTenure. What is the p-value?
cor.test(store.df$Profit, store.df$CTenure, method="pearson")##
## Pearson's product-moment correlation
##
## data: store.df$Profit and store.df$CTenure
## t = 2, df = 70, p-value = 0.03
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
## 0.033 0.458
## sample estimates:
## cor
## 0.26The p-value is 0.03
Run a regression of Profit on {MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24, Visibility}
rp<- lm(Profit~MTenure+CTenure+Comp+Pop+PedCount+Res+Hours24+Visibility, data=store.df)
summary(rp)##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility, data = store.df)
##
## Residuals:
## Min 1Q Median 3Q Max
## -105789 -35946 -7069 33780 112390
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 7610.04 66821.99 0.11 0.90967
## MTenure 760.99 127.09 5.99 9.7e-08 ***
## CTenure 944.98 421.69 2.24 0.02840 *
## Comp -25286.89 5491.94 -4.60 1.9e-05 ***
## Pop 3.67 1.47 2.50 0.01489 *
## PedCount 34087.36 9073.20 3.76 0.00037 ***
## Res 91584.68 39231.28 2.33 0.02262 *
## Hours24 63233.31 19641.11 3.22 0.00199 **
## Visibility 12625.45 9087.62 1.39 0.16941
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 57000 on 66 degrees of freedom
## Multiple R-squared: 0.638, Adjusted R-squared: 0.594
## F-statistic: 14.5 on 8 and 66 DF, p-value: 5.38e-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?
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.
Executive Summary
We observe several interesting insights based on the Regression Analysis we have conducted:
Retention of crew and managers increases profit as increase in experience, increases skill and in turn, increases customer satisfaction.
24 hour stores don’t bring in profit and don’t retain managers.
Number of competitors per 10,000 people within 1/2 mile radius affect the profits negatively as increase in competition, diverts customer base.
Population within 1/2 mile radius, visibility of store front, pedestrian foot traffic volume, residential area presence bringing in high profit have a positive impact on the profit as people passing by the store, pay attention to the board and might enter the store to buy some products.
By this we can conclude that, 24 hour stores are unsuccessful in retaining managers nor reaping significant profits, opening them only during day hours will help make those stores successful and the managers and crew who have stayed and helped bring in profits should be given incentives such as better coverage of health insurance to make them stay even in the future.