Analysis of Store24
Himanshu Dwivedi
18 December 2017
Basic Analysis
Read Data
store <- read.csv(paste("Store24.csv", sep = ""))attach(store)
str(store)## 'data.frame': 75 obs. of 14 variables:
## $ store : int 1 2 3 4 5 6 7 8 9 10 ...
## $ Sales : int 1060294 1619874 1099921 1053860 1227841 1703140 1809256 1378482 2113089 1080979 ...
## $ Profit : int 265014 424007 222735 210122 300480 469050 476355 361115 474725 278625 ...
## $ MTenure : num 0 86.22 23.89 0 3.88 ...
## $ CTenure : num 24.8 6.64 5.03 5.37 6.87 ...
## $ Pop : int 7535 8630 9695 2797 20335 16926 17754 20824 26519 16381 ...
## $ Comp : num 2.8 4.24 4.49 4.25 1.65 ...
## $ Visibility: int 3 4 3 4 2 3 2 4 2 4 ...
## $ PedCount : int 3 3 3 2 5 4 5 3 4 3 ...
## $ Res : int 1 1 1 1 0 1 1 1 1 1 ...
## $ Hours24 : int 1 1 1 1 1 0 1 1 1 0 ...
## $ CrewSkill : num 3.56 3.2 3.8 2.06 3.65 3.58 3.94 3.98 3.22 3.54 ...
## $ MgrSkill : num 3.15 3.56 4.12 4.1 3.59 ...
## $ ServQual : num 86.8 94.7 78.9 100 68.4 ...Summary Statistics
summary(store)## 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.00Mean and Standard Deviation of Profit, MTenure and CTenure
library(psych)
describe(store)[3:5,3:4]## mean sd
## Profit 276313.61 89404.08
## MTenure 45.30 57.67
## CTenure 13.93 17.70Analysis of 10 most and least profitable stores
pstore <- store[order(-Profit),]head(pstore[,1:5],10)## 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.636550tail(pstore[,1:5],10)## 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.956879Plots
Plot between Profit vs MTenure
library(car)
scatterplot(Profit~MTenure, data=store, main="Scatterplot of Profit vs. MTenure",pch=16)
Plot of Profit vs CTenure
scatterplot(Profit~CTenure, data=store, main="Scatterplot of Profit vs. CTenure",pch=16)
Correlations between variables
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.00Correlation between Profit and MTenure,CTenure
round(cor(store)[3,4:5],2)## MTenure CTenure
## 0.44 0.26Correlation Plot
library(corrgram)
corrgram(store, lower.panel = panel.shade, upper.panel = panel.pie, text.panel = panel.txt, main = "Corrgram of store variables")
cor.test(Profit,MTenure)##
## Pearson's product-moment correlation
##
## data: Profit and 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.4388692correlation between Profit and MTenure = 0.44, p-value = 0.00008 < 0.05
cor.test(Profit,CTenure)##
## Pearson's product-moment correlation
##
## data: Profit and 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.2576789correlation between Profit and CTenure = 0.26, p-value = 0.026 < 0.05
Regression Analysis
fit <- lm(Profit ~ MTenure+CTenure+Comp+Pop+PedCount+Res+Hours24+Visibility , data = store)
summary(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-12F-statistic = 14.53 and p-value = 5.38e-12 < 0.05
MTenure, CTenure, Comp, Pop, PedCount, Res, Hours24 have beta coefficients statistically significant (p-value < 0.05). Visibility has beta coefficients not statistically significant (p-value > 0.05).
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 Store24, if the Manager’s tenure increases by one month is 760.993 The expected change in the Profit at Store24, if the Crew’s tenure increases by one month is 944.978
Summary
From the analysis of “Store24 (A) Harvard Case Study” we get the following points
- Based on regression analysis
- Visibility of Store does not affect the profit of Store (null hypothesis)
- Manager Tenure, No. of competitors and pedastrian foot traffic volume has highest affect on the profit of store
- These regression analysis are correct up to r-square = 63.79% which is good fit
- Based on correlation matrix and plots
- Profit of store has a very high positive correlation with Sales(0.92)
- Profit has significant positive correlation with Manager Tenure(0.44), Population(0.43), Pedastrian foot traffic(0.45)
- Profit has slight positive correlation with Crew Tenure(0.26), Manager Skill(0.32) and Service Quality(0.36)
- There is also strong positive correlation between Population and Pedastrian foot traffic(0.61) and also slight positive correlation between Manager Skill and Service Quality(0.36)
- Crew Tenure has only a slight negative correlation with location in residential area(-0.34)
- Manager and Crew Tenure are only slightly positively correlated with Manager(0.23) and Crew Skills(0.26)
- Based on Plots
- Most of the crew tenure is slightly less than mean(13.93) and there are only a few outliers (sd = 17.70)
- Manager tenure is more widely distributed around mean(45.30) with many outliers (sd = 57.67)
From this analysis we get that Profit is very closely related to sales. This is to be expected as more sales happen, profit can be gained. Also the tenure of the Manager is more dominant in getting more profits than the tenure of the Crew. This is maybe due to the fact that more experienced manager can manage the crew more effectively and thus get more profits.