Store24 Case Study
Vashishtha Vidyarthi
22 January 2018
TASK 4a: Qualitatively identify the crucial issues being faced by the management. Based on your judgment, prepare a list of the most important questions that matter.
The management is interested in increasing the financial performance of the poor performing stores. They are considering the issue of increasing store level employee retention as they hypothesize that with better employee skill and experience, a store’s performance will go up. In this pursuit they are looking at the following questions:-
How important is the ‘tenure of manager and the crew’ relative to the ‘site location factors’ in determining ‘store level financial performance’?
What is the implication of the nonlinear relationship between tenure and finacial performance in determining how bonuses and other incentives should be tied to retention?
What should be the course of action for increasing store performance, e.g. increasing wages or bonus or something else?
Task 4b: relate each variable of Exhibit 2 to the different points of discussion presented in the case.
- ‘tenure of manager and crew’
- ‘MTenure’
- ‘CTenure’
- ‘site location factors’
- ‘Comp’
- ‘Pop’
- ‘Visible’
- ‘PedCount’
- ‘Hours24’
- ‘Res’
- ’store level financial performance
- ‘Sales’
- ‘Profit’
TASK 4c: Using R, read the data into a data frame called store. 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
store.df<- read.csv(paste("Store24.csv", sep = ""))
library(psych)
describe(store.df)[(2:11),c(3,4,8,9)]## mean sd min max
## Sales 1205413.12 304531.31 699306.00 2113089.00
## Profit 276313.61 89404.08 122180.00 518998.00
## MTenure 45.30 57.67 0.00 277.99
## CTenure 13.93 17.70 0.89 114.15
## Pop 9825.59 5911.67 1046.00 26519.00
## Comp 3.79 1.31 1.65 11.13
## Visibility 3.08 0.75 2.00 5.00
## PedCount 2.96 0.99 1.00 5.00
## Res 0.96 0.20 0.00 1.00
## Hours24 0.84 0.37 0.00 1.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.
describe(store.df)['Profit',c(3,4)]## mean sd
## Profit 276313.6 89404.08Use R to measure the mean and standard deviation of MTenure.
describe(store.df)['MTenure',c(3,4)]## mean sd
## MTenure 45.3 57.67Use R to measure the mean and standard deviation of CTenure.
describe(store.df)['CTenure',c(3,4)]## mean sd
## CTenure 13.93 17.7TASK 4e - 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)TASK 4f- Replicate Exhibit 1 shown in the case, using R
Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.
store.df[order(-store.df$Profit),][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.636550Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.
store.df[order(store.df$Profit),][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.347023TASK 4g - Scatter Plots
Use R to draw a scatter plot of Profit vs. MTenure.
library(car)##
## Attaching package: 'car'## The following object is masked from 'package:psych':
##
## logitscatterplot(Profit~ MTenure, main= "Scatterplot of Profit vs. MTenure", cex=0.6, pch=19, spread= FALSE, lty='dashed', data= store.df)
TASK 4h - Scatter Plots (contd.)
Use R to draw a scatter plot of Profit vs. CTenure.
scatterplot(Profit~ CTenure, main= "Scatterplot of Profit vs. CTenure", cex=0.6, pch=19, spread= FALSE, lty='dashed', data= store.df)
TASK 4i - Correlation Matrix
Use R to construct a Correlation Matrix for all the variables in the dataset. (Display the numbers up to 2 Decimal places)
round(cor(store.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.00TASK 4j - Correlations
Use R to measure the correlation between Profit and MTenure. (Display the numbers up to 2 Decimal places)
attach(store.df)
round(cor(Profit,MTenure), 2)## [1] 0.44Use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)
round(cor(Profit,CTenure), 2)## [1] 0.26TASK 4k
Use R to construct the following Corrgram based on all variables in the dataset.
library(corrgram)
corrgram(store.df, order = FALSE, lower.panel = panel.shade, upper.panel = panel.pie, text.panel = panel.txt, main= "Corrgram of Store Variables")
From the observation of Corrgram, the following points can be deduced 1. Profit is strongly correlated with Sales 2. MTenure and CTenure are positively corelated with Profit, but MTenure has a stronger corelation 3. Pop is an important variable affecting Profit (positive corelation) 4. Com is an important variable affecting Profit (negative corelation) 5. PedCount is a comparatively more important factor affecting Profit than Visibility 6. There is a weak corelation between Profit and factors like Res and Hours24 7. MgrSkill is comparatively more important factor than CrewSkill affecting Profit 8. There is a strong corelation between ServQual and Profit 9. Thus the manegerially relevant correlations are those between Profit and- Sales, Manager’s Tenure (MTenure), Tenure of Crew (CTenure), Number of competitors per 10,000 people within a ½ mile radius(Comp), Population within a ½ mile radius (Pop), pedestrian foot traffic volume (PedCount), (Residential Area)Res, Skill Levels (MgrSkill, CrewSkill), and service quality (ServQual)
TASK 4l - Pearson’s Correlation Tests
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)##
## Pearson's product-moment correlation
##
## data: store.df$Profit and store.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.4388692p value<0.05
Run a Pearson’s Correlation test on the correlation between Profit and CTenure. What is the p-value?
cor.test(store.df$Profit, store.df$CTenure)##
## Pearson's product-moment correlation
##
## data: store.df$Profit and store.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.2576789p value<0.05
TASK 3m - Regression Analysis
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.df)
summary(fit)##
## 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.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-12TASK 4n
Based on TASK 3m, answer the following questions:
List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).
Explanatory variables with statistically significant beta coefficients: MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24
List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
Explanatory variables whose beta-coefficients are not statistically significant: Visibility
TASK 4o
Based on TASK 2m, answer the following questions:
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?
An increase in Manager’s tenure by 1 month inceases the sales by $ 761
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?
An increase in Crew’s tenure by 1 month increases the sales by $ 945
Executive Summary
The tenure of employees is important to increase profits and sales at stores. Going back to the questions raised at the beginning -
1. How important is the ‘tenure of manager and the crew’ relative to the ‘site location factors’ in determining ‘store level financial performance’?
The regression reveals that tenure of manager and crew are important determinants for improving stroe level perfermance. While Pedestrian Count and the distance from competition are important variables in site location factors to influence performance of the store. 24 hour running shops in residential areas are observed to perform better in terms of profit.
2. What is the implication of the nonlinear relationship between tenure and finacial performance in determining how bonuses and other incentives should be tied to retention?
Tenure and financial performance(Profit) are positively corelated. Thus having greater tenure is profitable for the business. So offering a bonus to ensure good retention is suggested. However, skill development expenditure can be de-prioritized in favour of opening stores in location where competition is less and pedestrian count is high.
3. What should be the course of action for increasing store performance, e.g. increasing wages or bonus or something else?
The course of action can be the following a. increase bonus and wages for manager and crew respectively, so that greater retention is ensured b. store location should be decided on the basis of location factors like residential location, good pedestrian pedestrian foot traffic volume, and low competition density. c. skill development should be aimed at getting better service quality reviews from customers. Thus, the regression analysis suggests ensuring better employee retention for better financial performance