Analysis of the Harvard Case Store24 (A):Managing Employee Retention
Jayesh Dosi
15 January 2018
store.df <-read.csv(paste ("Store24.csv", sep=""))
View(store.df)TASK 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.df$Profit)## [1] 276313.6This is the required mean.
sd(store.df$Profit)## [1] 89404.08This is the required standard deviation.
Use R to measure the mean and standard deviation of MTenure.
mean(store.df$MTenure)## [1] 45.29644This is the required mean.
sd(store.df$MTenure)## [1] 57.67155This is the required standard deviation.
Use R to measure the mean and standard deviation of CTenure.
mean(store.df$CTenure)## [1] 13.9315This is the required mean.
sd(store.df$CTenure)## [1] 17.69752This is the required standard deviation.
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)R has an inbuilt dataset called mtcars. In the above code, the order() function helps us sort the data in mtcars, based on a data column called mpg. We can sort in ascending order or descending order. After sorting, notice how we can view the top 5 cars that have the highest and lowest mpg respectively.
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.
attach(store.df)
max_prof<-store.df[order(-Profit),]
max_prof[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.
min_prof<-store.df[order(Profit),]
min_prof[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)
scatterplot(Profit ~ MTenure , data=store.df,
xlab="Mtenure", ylab="Profit",
main="Scatterplot of Profit Vs Mtenure", )
TASK 4h - Scatter Plots (contd.)
Use R to draw a scatter plot of Profit vs. CTenure.
scatterplot(Profit ~ CTenure , data=store.df,
xlab="Ctenure", ylab="Profit",
main="Scatterplot of Profit Vs Ctenure", )
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)
round(cor(store.df$Profit,store.df$MTenure),2)## [1] 0.44Use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)
round(cor(store.df$Profit,store.df$CTenure),2)## [1] 0.26TASK 4k
Use R to construct the following Corrgram based on all variables in the dataset.
Critically think about how the Profit is correlated with the other variables (e.g. MTenure, CTenure, Sales, Pop, Comp etc).
Qualitatively discuss the managerially relevant correlations.
library(corrgram)
corrgram(store.df,order= FALSE,lower.panel = panel.shade,
upper.panel = panel.pie, text.panel = panel.txt,
main = "Corrgram of store variables")
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:8.193e-05 for correlation between Profit and MTenure. ####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.02562 for correlation between Profit and CTenure.
TASK 4m - Regression Analysis
Run a regression of Profit on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility}.
summary(lm(Profit~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility, data=store.df))##
## 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).
1.MTenure
2.CTenure
3.Comp
4.Pop
5.PedCount
6.Res
7.Hours24
List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
1.Visibility
TASK 4o
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?
Ans- 760.993 is expected change in the Profit at a store.
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?
Ans- 944.978 is expected change in the Profit at a store.
TASK 4p
Please prepare an “Executive Summary”.
There is a statistically significant association between Profit and Mtenure, CTenure, Comp, Pop, PedCount, Res, Hours24 while on the other hand visibility is not in statistically significant association with Profit. There is a moderate positive correlation between Profit and MTenure and Profit and CTenure. Profit shows a highly negative correlation with Comp.
Since sales and profit made is directly related they are highly correlated.
On the basis of regression analysis, there is a need for more predictors, since the R-squared value is not too high. We got the values of all the variables of the linear model.
And our equation that is derived from the regression analysis is:
Profit = 7610.041 + 760.993 MTenure + 944.978 CTenure - 25286.887 Comp + 3.667 Pop + 34087.359 PedCount + 91584.675 Res + 63233.307 Hours24 + 12625.447 Visibility
But also we need to find more dependencies,relationships and other factors affecting these variables.