Store24 Analysis
Chandrasekar Venkatarama
January 16, 2018
R Markdown
This is an R Markdown document in which the case Store24 (A): Managing Employee Retention is analysed and commands are run on R.
TASK 4c - Reading and generating summary statistics in R
setwd("D:/R Internship")
store<-read.csv(paste("Store24.csv",sep = ""))
View(store)
library(psych)
describe(store)## 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.46TASK 4d - Use R to measure the mean and standard deviation of Profit,MTenure and CTenure
mean(store$Profit)## [1] 276313.6sd(store$Profit)## [1] 89404.08mean(store$MTenure)## [1] 45.29644sd(store$MTenure)## [1] 57.67155mean(store$CTenure)## [1] 13.9315sd(store$CTenure) ## [1] 17.69752The mean and sd of Profit is 276313.6 and 89404.08. The mean and sd of MTenure is 45.29644 and 57.67155. The mean and sd of CTenure is 13.9315 and 17.69752.
TASK 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
store.max<-store[order(-store$Profit),]
View(store.max)
store.max10<-store.max[1:10,1:5]
View(store.max10)
store.min10<-store.max[66:75,1:5]
View(store.min10) TASK 4g - Scatter Plots
plot(store$MTenure,store$Profit,pch=19,cex=0.7,
main="Scatterplot of Profit vs. MTenure",xlab ="MTenure",ylab = "Profit")
abline(lm(store$Profit~store$MTenure),col="green3") 
TASK 4h - Scatter Plots (contd.)
plot(store$CTenure,store$Profit,pch=19,cex=0.7,
main="Scatterplot of Profit vs. CTenure",xlab ="CTenure",ylab = "Profit")
abline(lm(store$Profit~store$CTenure),col="green3") 
TASK 4i - Correlation Matrix
round(cor(store),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
round(cor(store$Profit,store$MTenure),2)## [1] 0.44round(cor(store$Profit,store$CTenure),2) ## [1] 0.26TASK 4k - Corrgram
library(corrgram)## Warning: package 'corrgram' was built under R version 3.3.3corrgram(store,lower.panel = panel.shade
,upper.panel = panel.pie,text.panel = panel.txt
, main="Corrgram of store variables") 
From the Corrgram, we can see that there is a very high correlation between Profit and Sales. Profit has a high correlation with MTenure and a slightly lower correlation value with CTenure. So, the above 2 would be the manegerially relevant correlations. Profit has a good correlation with Pop, but has a slightly negative correlation value with respect to Comp.
TASK 4l - Pearson’s Correlation Tests
cor.test(store$Profit,store$MTenure)##
## Pearson's product-moment correlation
##
## data: store$Profit and store$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.4388692cor.test(store$Profit,store$CTenure) ##
## Pearson's product-moment correlation
##
## data: store$Profit and store$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 for the correlation between Profit and MTenure is 8.193e-05. The p-value for the correlation between Profit and CTenure is 0.02562.
TASK 4m - Regression Analysis
fit<-lm(Profit~MTenure+CTenure+Comp+Pop+PedCount+Res+Hours24+Visibility,data = store)
fit##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility, data = store)
##
## Coefficients:
## (Intercept) MTenure CTenure Comp Pop
## 7610.041 760.993 944.978 -25286.887 3.667
## PedCount Res Hours24 Visibility
## 34087.359 91584.675 63233.307 12625.447summary(fit) ##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + Visibility, data = store)
##
## 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
List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05). MTenure,CTenure,Comp,Pop,PedCount,Res,Hours24 are the explanatory variables whose beta-coefficients are statistically significant.
List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05). Visibility is the only explanatory variable whose beta-coefficiants are not statistically significant.
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? The expected increase in profit is 760.993.
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? The expected increase in profit is 944.978.
TASK 4p - Executive Summary
This dataset contains the details of 75 stores, like the average tenures of the managers and crew members, its competitors and population within 0.5 mile radius. This dataset looks to establish the relationship between the performance of the company and its managers and crew tenures. In that respect, the cor.test function calculated the extent of correlation between these factors and we found out that there is very high correlation between the Profit and the Manager’s tenure and that there is a good correlation between Profit and Crew tenure, but not as much as the former. Through the Corrgram, we can establish many such correlations like between Profit and Population, Population and PedCOunt, between Profit and PedCOunt,etc. By creating the linear regression model with Profit being dependent on factors like MTenure, CTenure, Pop, PedCount, Comp, Res, Hours24, Visibility, etc. By looking at the coefficients and the p-values, apart from visibility all other factors impact profit and f-statistic gives a very small p-value, implying that the model explains the relationship between profit and the independent variables.