Store24 analysis
nihir gulati
December 17, 2017
2a
The issues faced are :
To the management the data indicates that on an average, increase in the employee tenure increases the profit of that particular store.
So, the management need to identify :
the extent to which employee tenure affects profits/sales.
the optimum(not too lavish but considerably encouraging) way to retain employees.
To find the increase in profit due to employee retention as compared to other on-site factors like 24x7 availabilty, no. of people living nearby, etc.
Questions :
Should we apply the same strategy for every store?
How much do factors other than employee retention matter?
What should be the qualities in an employee whom we want to retain?(Basically, what kind of employees should we target in the employee retention scheme.)
What incentives to provide to encourage employees to increase their tenure?
Which is more crucial : manager retention or crew retention and by how much?
2b
Now, based on the explanation of the variables in exhibit 2 :
Now other factors include : Pop, Comp, Visible, Hours24, Pedcount and Res. We need to which of these are more closely correlated to the data than MTenure and CTenure.
Also, we have to figure out which one of MTenure or CTenure is more important for increasing profits.
2c
store <- read.csv(paste("Store24.csv",sep = ""))
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.00So, we observe that the summary stats are indeed equal to those given in exhibit 3.
2d
attach(store)## The following object is masked _by_ .GlobalEnv:
##
## storeShowing the mean and sd of the desired variables.
describe(store , skew = FALSE, ranges = FALSE)[3:5,3:4]## mean sd
## Profit 276313.61 89404.08
## MTenure 45.30 57.67
## CTenure 13.93 17.70Another way (using the argument fast):
describe(store, fast = TRUE)[3:5,3:4]## mean sd
## Profit 276313.61 89404.08
## MTenure 45.30 57.67
## CTenure 13.93 17.702e
#detach(store)
attach(mtcars)
#View(mtcars)
some(mtcars)## mpg cyl disp hp drat wt qsec vs am gear carb
## Mazda RX4 21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4
## Mazda RX4 Wag 21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4
## Duster 360 14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4
## Merc 230 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
## Merc 450SL 17.3 8 275.8 180 3.07 3.730 17.60 0 0 3 3
## Honda Civic 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
## Toyota Corolla 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
## Dodge Challenger 15.5 8 318.0 150 2.76 3.520 16.87 0 0 3 2
## Porsche 914-2 26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2
## Ford Pantera L 15.8 8 351.0 264 4.22 3.170 14.50 0 1 5 4newdata <- mtcars[order(mpg),] # sort by mpg (ascending)
#View(newdata)
some(newdata)## mpg cyl disp hp drat wt qsec vs am gear carb
## Cadillac Fleetwood 10.4 8 472.0 205 2.93 5.250 17.98 0 0 3 4
## Ford Pantera L 15.8 8 351.0 264 4.22 3.170 14.50 0 1 5 4
## Merc 450SE 16.4 8 275.8 180 3.07 4.070 17.40 0 0 3 3
## Merc 450SL 17.3 8 275.8 180 3.07 3.730 17.60 0 0 3 3
## Hornet Sportabout 18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2
## Mazda RX4 Wag 21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4
## Toyota Corona 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1
## Fiat X1-9 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
## Honda Civic 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
## Fiat 128 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1newdata[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)
some(newdata)## mpg cyl disp hp drat wt qsec vs am gear carb
## Toyota Corolla 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
## Fiat 128 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
## Merc 240D 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
## Datsun 710 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
## Merc 280 19.2 6 167.6 123 3.92 3.440 18.30 1 0 4 4
## Hornet Sportabout 18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2
## Merc 280C 17.8 6 167.6 123 3.92 3.440 18.90 1 0 4 4
## Merc 450SLC 15.2 8 275.8 180 3.07 3.780 18.00 0 0 3 3
## Maserati Bora 15.0 8 301.0 335 3.54 3.570 14.60 0 1 5 8
## Chrysler Imperial 14.7 8 440.0 230 3.23 5.345 17.42 0 0 3 4newdata[1:5,]## mpg cyl disp hp drat wt qsec vs am gear carb
## Toyota Corolla 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
## Fiat 128 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
## Honda Civic 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
## Lotus Europa 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2
## Fiat X1-9 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1detach(mtcars)2f
The 10 most profitable ones
#attach(store)
good <- store[order(Profit,decreasing = TRUE),]
good[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.636550The 10 least profitable ones
bad <- store[order(Profit,decreasing = FALSE),]
bad[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.3470232g
par(fig = c(0,0.25,0.25,1))
boxplot(Profit, axes = FALSE , ylim = c(0,6e+05) , whisklty = "solid" , medlwd = 1 , staplecol = "white")
par(fig = c(0.2,1,0.35,1) , new = TRUE)
plot(MTenure , Profit , type = "p" , pch = 20 , panel.first = grid(lty = "solid") , xlim = c(0,280) , main = "Scatterplot of Profit v/s MTenure")
abline(lm(Profit ~ MTenure) , col = "green")
par(fig = c(0.2,1,0,0.45) , new = TRUE)
boxplot(MTenure, axes = FALSE , ylim = c(0,270) , horizontal = TRUE , whisklty = "solid" , medlwd = 1 , staplecol = "white")
par(fig = c(0,0.25,0.25,1))
boxplot(Profit, axes = FALSE , ylim = c(0,6e+05) , whisklty = "solid" , medlwd = 1 , staplecol = "white")
par(fig = c(0.2,1,0.35,1) , new = TRUE)
plot(CTenure , Profit , type = "p" , pch = 20 , panel.first = grid(lty = "solid") , xlim = c(0,115) , main = "Scatterplot of Profit v/s CTenure")
abline(lm(Profit ~ CTenure) , col = "green")
par(fig = c(0.2,1,0,0.45) , new = TRUE)
boxplot(CTenure, axes = FALSE , ylim = c(0,100) , horizontal = TRUE , whisklty = "solid" , medlwd = 1 , staplecol = "white")
2i
The correlation matrix is as follows:
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.0000000002j
Correlation between Profit and MTenure
cor(Profit , MTenure)## [1] 0.4388692Correlation between Profit and CTenure
cor(Profit , CTenure)## [1] 0.25767892k
Correlogram
corrgram(store , lower.panel = panel.shade , upper.panel = panel.pie , main = "Corrgram of store variables")
Correlations
Since we sell for profit, so the more the sales, more is the profit.
Manager tenure affects profits more than crew tenure as more skills are required to be a manager. This is also the reason why skills of a manager are more closely correlated to profits.
Nearby population and presence of the store in residential areas positively affects sales as more people means more customers.
Competitor presence negatively affects sales as sometimes they may offer better products and/or less prices.
Things like 24hr service and visibility help in keeping competitors at bay.
Quality of Service very likely to be related to profits as it determines whether the customer would return to your store or not.
Of these the managerially significant ones are :
Employee tenure
24hr service
Quality of service
Presence in residential areas
2l
mytable <- xtabs(Profit ~ MTenure)
chisq.test(mytable)##
## Chi-squared test for given probabilities
##
## data: mytable
## X-squared = 8312700, df = 59, p-value < 2.2e-16Since the p-value < 0.05 , so we can say that there is a significant correlation in between Profit and MTenure
mytable2 <- xtabs(Profit ~ CTenure)
chisq.test(mytable2)##
## Chi-squared test for given probabilities
##
## data: mytable2
## X-squared = 7574500, df = 56, p-value < 2.2e-16Since the p-value < 0.05 , so we can say that there is a significant correlation in between Profit and CTenure
2m
fit <- lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility)
#confint(fit)
summary(fit)##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility)
##
## 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-12All the beta coefficients except those of Visibility have (p<0.05), hence they are significant They are :
MTenure
CTenure
Comp
Pop
PedCount
Res
Hours24
The variable with p>0.05 is :
Visibility
Q17 and 18:
fit$coefficients['MTenure']## MTenure
## 760.9927fit$coefficients['CTenure']## CTenure
## 944.978For an increase of 1 month in the tenure of a Manager, the Profit increases by $761(approx.) and for the same increase in the tenure of a Crew’s tenure, the profit increases by $945(approx.)
Q19: Executive Summary
Crude observation :
Increase in employee tenure causes increase in profits. But the variation is not linear as profit increases during the initial months of an employee’s tenure but ultimately reaches a threshold beyond which there is very little impact of increase in employee tenure.
Questions asked :
What is the extent to which profits are affected?
Are other factors equally or more important?
What kind of employees to retain?(kind - manager or crew)
What steps should be taken to significantly increase profits?
It was observed that extensive data analysis would be required to determine the financial impact of increased tenure and to ensure that the right steps to maximise profits are taken.
Data analysis :
The most statistically significant variables affecting profits are :
Manager tenure - could be because more skill is required to be an effective manager whereas crew skill requirement is less.
Competitor stores present nearby - in presence of more options, the lowest bidder wins.
Pedestrian footfall in the store - more people means more needs, hence we have more sales and more profits.
24 hours operation of the store - gives an edge over competitor stores.
The factors which are not so significant as the above ones but affect profits considerably are :
Crew tenure - more crew means ability to serve more customers. Having the same people creates a sense of familiarity in the customers.
Population residing nearby(half a mile radius)
If the store is present in a residential rather than an industrial area - since the store targets households and not industries.
Another factor - visibility of the store front was taken into consideration but it was discovered that it did not affect profits significantly.
Probable steps(to be taken) based on the above analysis:
Encourage managers to give 24 hour service.
Provide incentives to managers based on profit done.
Provide discounts during festive seasons so as to increase footfall.
Identify high poplulation neighbourhoods where there is no store in vicinity.