Store24
Aditi Gupta
March 6, 2018
store.df <- read.csv(paste("Store24.csv", sep=""))
library(psych)
View(store.df)Comments: 75 observations of 14 variables
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.00Task 4d: 1. Mean and standard deviation of Profit
mean(store.df$Profit)## [1] 276313.6sd(store.df$Profit)## [1] 89404.08Comments: Mean=276313.6, Standard Deviation=89404.08
- Mean and standard deviation of MTenure
mean(store.df$MTenure)## [1] 45.29644sd(store.df$MTenure)## [1] 57.67155Comments: Mean=45.29644, Standard Deviation=57.67155
- Mean and standard deviation of CTenure
mean(store.df$ CTenure)## [1] 13.9315sd(store.df$CTenure)## [1] 17.69752Comments: Mean=13.9315, Standard Deviation=17.69752
Task 4f: 4. Print {StoreID, Sales, Profit, MTenure, CTenure} of the Top 10 most profitable stores
new.df <- store.df[order(-store.df$Profit),]
View(new.df)
new.df[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- Print {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores
leastprofit.df <- tail(new.df, 10)
leastprofit.df[,1:5]## store Sales Profit MTenure CTenure
## 37 37 1202917 187765 23.1985000 1.347023
## 61 61 716589 177046 21.8184200 13.305950
## 52 52 1073008 169201 24.1185600 3.416838
## 54 54 811190 159792 6.6703910 3.876797
## 13 13 857843 152513 0.6571813 1.577002
## 32 32 828918 149033 36.0792600 6.636550
## 55 55 925744 147672 6.6703910 18.365500
## 41 41 744211 147327 14.9180200 11.926080
## 66 66 879581 146058 115.2039000 3.876797
## 57 57 699306 122180 24.3485700 2.956879Task 4g: Scatter plot of Profit vs. MTenure
plot(store.df$MTenure, store.df$Profit, col="black", main="Scatterplot of Profit vs. MTenure", xlab="MTenure", ylab = "Profit")
abline(lm(store.df$Profit ~ store.df$MTenure), col="red")
Task 4h: Scatterplot of Profit vs. CTenure profitable stores
plot(store.df$CTenure, store.df$Profit, xlab = "CTenure", ylab = "Profit", main = "Scatterplot of Profit vs. CTenure", col="black")
abline(lm(store.df$Profit ~ store.df$CTenure), col="red")
Task 4i: Correlation Matrix
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 9. Measure the correlation between Profit and MTenure
round(cor(store.df$MTenure, store.df$Profit),2)## [1] 0.44Comments: Correlation between Profit and MTenure is 0.44
- Measure the correlation between Profit and CTenure
round(cor(store.df$CTenure, store.df$Profit),2)## [1] 0.26Comments: Correlation between Profit and CTenure is 0.26
Task 4k: Construct corrgram
library(corrgram)
corrgram(store.df, order = TRUE, lower.panel = panel.shade, upper.panel=panel.pie, text.panel=panel.txt, main="Corrgram of store variables")
Comments: On how the Profit is correlated with the other variables (e.g. MTenure, CTenure, Sales, Pop, Comp etc). -
- Profit is strongly related to sales
- Profit is strongly related to MTenure, which means greater the tenure of managers, greater are the profits
- Profit is moderately related to CTenure
- Profit is highly correlated to Population
- Profit is also correlated with Padestrian Count, Crew Skill, Manager Skill, and Service Quality
Comments: Managerially relevant correlations- Profit - MTenure: Higher the tenure of managers, higher the profits Profit - CTenure: Higher the tenure of crew, higher the profits Sales - MTenure and Sales - CTenure: Higher the tenure of managers and crew, higher the sales
Task4l: Pearson’s Correlation Tests 12. 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.4388692Comments: p-value=8.193e-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.2576789Comments: p-value=0.02562
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: 15. List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05). Answer- MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24
List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05). Answer- Visibility
Task 4o: 17. 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? Answer- The expected increase in Profit at the store if the Manager’s tenure increases by one month 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 Answer- The expected increase in Profit at the store if the Crew’s tenure increases by one month is : 944.978
EXECUTIVE SUMMARY- This case study on Store24 data has shown that there is high level of correlation between profits of the store and tenure of managers and crew, as is evident from the corrgram and Pearson’s correlation test. Our case study has also shown that increased skills in managers and the crew as well as the service quality has resulted in greater profits. Other factors that are moderately correlated with Profit are population, number of competitors, and pedestrians access. We see that visibility does not affect the profit that much (p-value=0.169411(>0.05)) From regression analysis, the p-value obtained is 5.382e-12. We predicted that with increase in tenure of managers by one month, the profits will increase by 760.993, and that with the increase in tenure of crew will increase by 944.978.
The equation for regression comes out to be: 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(Visibilty)