Store 24 Case Study Analysis
Shreyas Jadhav
6 January 2018
(4a)Issues being faced by management and list of most important questions.
How store-level employee retention can be increased?
What is the effect of Manager and Crew Tenure on profit in stores?
What are the strategies to increase Tenure?
Are there any other factors that have an financial impact on sales and profit besides tenure?
(4b)How might a dataset associated with Exhibit 2 in case study help in answering your list of questions from Task (4a)
The dataset asociated in Exhibit 2 may help us understand the relationship between the dependent variable(Profit) and the other independent variables. By performing analysis and tests, we can determine whether to what extent the profit or sales in Store24 stores are impacted by the factors(variables) in the dataset. Therefore we can determine what variables or factors are to be focused on in order to improve sales and profit and what variables or factors are to be neglected.
(4c)Reading & Summary
setwd("C:/Users/Shreyas Jadhav/Downloads")
store.df <- read.csv(paste("Store24.csv",sep="."))
#View(store.df)
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.00attach(store.df)Therefore, the summary statistics generated from R are consistent with Exhibit 3 from the Case.
Analysis
(4d)The mean and standard deviation of:
(1)1.Profit
mean(store.df$Profit)## [1] 276313.6sd(store.df$Profit)## [1] 89404.08Therefore, the mean and standard deviation of profit is $276313.6 and $89404.08respectively.
(2)2.MTenure
mean(store.df$MTenure)## [1] 45.29644sd(store.df$MTenure)## [1] 57.67155Therefore, the mean and standard deviation of MTenure is 45.29644 and 57.67155respectively.
(3)3.CTenure
mean(store.df$CTenure)## [1] 13.9315sd(store.df$CTenure)## [1] 17.69752Therefore, the mean and standard deviation of CTenure is 13.9315 and 17.69752respectively.
(4e)Sort a dataframe based on a data column.(Example)
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)Use R to:
(4)1. Print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.
newdata1<-store.df[order(-store.df$Profit),]
newdata1[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.636550(5)2. Print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 least profitable stores.
newdata1<-store.df[order(store.df$Profit),]
newdata1[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.347023(4g)Scatter plot of Profit vs. MTenure
(6).
library(car)
scatterplot(Profit ~ MTenure, data=store.df, pch = 19)
(4h)Scatter plot of Profit vs. CTenure.
library(car)
scatterplot(Profit ~ CTenure, data=store.df, pch = 19)
(4i)Construct a Correlation Matrix for all the variables in the dataset.
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.00(4j)Use R to:
(9)1.Measure the correlation between Profit and MTenure.
x<-store.df[,c("MTenure")]
y<-store.df[,c("Profit")]
round(cor(x,y),2)## [1] 0.44(10)2.Measure the correlation between Profit and CTenure.
a<-store.df[,c("CTenure")]
b<-store.df[,c("Profit")]
round(cor(a,b),2)## [1] 0.26(4k)Construct the following Corrgram based on all variables in the dataset.
library(corrgram)
cols4<-colorRampPalette(c("peachpuff","lightpink","royalblue3","navyblue"))
corrgram(store.df, order=FALSE, col.regions=cols4, lower.panel=panel.shade, upper.panel=panel.pie, text.panel=panel.txt, main="Corrgram of store variables")
(1)Profit is positively correlated with Sales, MTenure, CTenure, Pop, Visibility, PedCount, CrewSkill, MgrSkill and ServQual.
(2)Profit is negatively correlated with Comp, Res and Hours24.
(3)Profit is strongly correlated with Sales, MTenure, CTenure, Pop, Comp, PedCount, Res, Hours24.
(4)Profit is weakly correlated with Visibilty, CrewSkill, MgrSkill, ServQual.
(4l)Run a:
(12)1.Pearson’s Correlation test on the correlation between Profit and MTenure.
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.4388692The p-value is 8.193e-05.
(13)2.Pearson’s Correlation test on the correlation between Profit and CTenure.
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.2576789The p-value is 0.02562.
(4m)Regression of Profit on {MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24, Visibility}.
fit <- lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility)
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-12(4n)List the :(From (4m))
(15)1.Explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).
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-12The explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05) are MTenure, CTenure, Comp, Pop, PedCount, Res and Hours24.
(16)2.List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
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-12The explanatory variable(s) whose beta-coefficients are not statistically significant (p < 0.05) are Visibilty.
(4o)What is expected change in the Profit at a store:(From (4m))
(17)1.If the Manager’s tenure i.e. number of months of experience with Store24, increases by one month?
coefficients(fit)## (Intercept) MTenure CTenure Comp Pop
## 7610.041452 760.992734 944.978026 -25286.886662 3.666606
## PedCount Res Hours24 Visibility
## 34087.358789 91584.675234 63233.307162 12625.447050The 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 is $ 760.993.
(18)2.If the Crew’s tenure i.e. number of months of experience with Store24, increases by one month?
coefficients(fit)## (Intercept) MTenure CTenure Comp Pop
## 7610.041452 760.992734 944.978026 -25286.886662 3.666606
## PedCount Res Hours24 Visibility
## 34087.358789 91584.675234 63233.307162 12625.447050The 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 is $ 944.978
(4p)Executive Summary
Objective:
To find out the impact on Profit and Sales in various Store24 stores due to various factors and variables like tenure, skill, population etc.
Problem and Summary:
The executives of store24 are trying to figure out whether:
How store-level employee retention can be increased?
What is the effect of Manager and Crew Tenure on profit in stores?
What are the strategies to increase Tenure?
Are there any other factors that have an financial impact on sales and profit besides tenure?
Analysis and Observation
A corrgram was generated, in which dark blue color and hashing that goes from lower left to upper right represent a strong positive correlation between Profit and {MTenure, CTenure, Pop and PedCount} cells.
Conversely, a red color and hashing that goes from the upper left to the lower right represent a strong negative correlation between Profit and {Comp, Res, Hours24} cells.
Correlation test for profit and MTenure had p-value=8.193e-05.
Correlation test for profit and MTenure had p-value=0.02562.
Regression of Profit on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility} had p-values < 0.05 for {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24} and p > 0.05 for {Visibilty}
Multiple R-squared: 0.6379, Adjusted R-squared: 0.594
F-statistic: 14.53 on 8 and 66 DF, p-value: 5.382e-12
Conclusions and Inferences
There exists a strong positive correlation between Profit and {MTenure, CTenure, Pop and PedCount}.
There exists a strong negative correlation between Profit and {Comp, Res, Hours24}.
p-value < 0.05 for Profit and MTenure correlation test, therefore we reject the Null hypothesis and there infer that Mtenure has an impact on Profit.
p-value < 0.05 for Profit and CTenure correlation test, therefore we reject the Null hypothesis and there infer that Ctenure has an impact on Profit.
{MTenure, Comp, PedCount}-> (* x 3) -> Strongly related; {Hours24}-> (* x 2) -> Strongly related; {CTenure, Pop, Res}-> (* x 1) -> weakly related.
Multiple R-squared: 0.6379, Adjusted R-squared: 0.594 indicates that the model is good model staistically but it indicates that there are certain other independent variables that affect Profit.
F-statistics p-value<0.05 indicates that the model as a whole is statistically fit and a great model.