Store24 Case Study Analysis
by Devesh Patidar
19 December 2017
Setting Working Directory
store <- read.csv(paste("Store24.csv", sep=""))
View(store)Summary
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.00TASK 2d
Use R to measure the mean and standard deviation of Profit.
Use R to measure the mean and standard deviation of MTenure.
Use R to measure the mean and standard deviation of 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.69752attach(store)## The following object is masked _by_ .GlobalEnv:
##
## storeTASK 2f- 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.
new<-store[order(-Profit),]
View(new)
new[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.
new<-store[order(Profit),]
View(new)
new[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 2g - Scatter Plots
Use R to draw a scatter plot of Profit vs. MTenure
library(car)
scatterplot(MTenure, Profit,main="Scatterplot of Profit vs MTenure")
TASK 2h - Scatter Plots (contd.)
Use R to draw a scatter plot of Profit vs. CTenure.
library(car)
scatterplot(CTenure, Profit,main="Scatterplot of Profit vs CTenure")
TASK 2i - 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),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 2j - Correlations
Use R to measure the correlation between Profit and MTenure. (Display the numbers up to 2 Decimal places)
Use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)
round(cor(Profit,MTenure),2)## [1] 0.44round(cor(Profit,CTenure),2)## [1] 0.26TASK 2k
Use R to construct the following Corrgram based on all variables in the dataset.
library(corrgram)
storenew <- store[,c("Profit", "CTenure")]
corrgram(storenew, order=FALSE,
lower.panel=panel.shade,
upper.panel=panel.pie,
text.panel=panel.txt,
diag.panel=panel.minmax,
main="Corrgram")
corrgram(store, order=FALSE,
lower.panel=panel.shade,
upper.panel=panel.pie,
text.panel=panel.txt,
diag.panel=panel.minmax,
main='corrgram of all Variables')
We see that profit and sales are the most highly positively correlated of all the variables following which profit is highly positively correlated to MTenure, Pop & PedCount. We also observed that manager Tenure is highly positively corelated to Sales and profits following which MTenuew is somewhat positively correlated to CTenure, MgrSkill, ServQual, Comp and Visibility. Managerial’s Skill is highly positively correlated to Srevice quality, sales and profits. We also see a positive correlation between managers skill and his tenure as well as Comp.
TASK 2l - Pearson’s Correlation Tests
Run a Pearson’s Correlation test on the correlation between Profit and MTenure. What is the p-value? Run a Pearson’s Correlation test on the correlation between Profit and CTenure. What is the p-value?
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.4388692cor.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.2576789TASK 2m - 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)
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-12store$Profit## [1] 265014 424007 222735 210122 300480 469050 476355 361115 474725 278625
## [11] 389886 329020 152513 261571 203951 196277 265584 394039 261495 269235
## [21] 282584 367036 277414 267354 282124 211912 230194 273036 263956 333607
## [31] 211885 149033 292745 382199 322624 219292 187765 203184 221130 222913
## [41] 147327 264072 337233 439781 410149 315780 387853 284169 195276 251013
## [51] 237344 169201 365018 159792 147672 189235 122180 227601 303069 356071
## [61] 177046 202641 239036 221157 301641 146058 362067 236339 375393 254203
## [71] 198529 196772 279193 518998 296826fitted(fit)## 1 2 3 4 5 6 7 8
## 282884.6 311616.6 247387.2 188867.1 308773.0 379779.2 392304.9 371985.2
## 9 10 11 12 13 14 15 16
## 443237.0 300474.6 390414.7 420779.0 210319.6 268639.8 279296.3 202381.0
## 17 18 19 20 21 22 23 24
## 352534.2 455293.3 256081.6 275088.3 277490.0 271166.4 309003.2 214340.6
## 25 26 27 28 29 30 31 32
## 246051.2 219299.0 258929.7 280699.0 210844.3 260034.8 197082.6 191247.4
## 33 34 35 36 37 38 39 40
## 207234.6 370486.2 318628.6 232328.1 240430.8 199026.7 260630.9 173787.2
## 41 42 43 44 45 46 47 48
## 237766.0 277755.6 375932.0 475485.8 350220.8 279391.3 399517.8 208750.4
## 49 50 51 52 53 54 55 56
## 215972.9 307812.7 282907.8 212113.7 252711.1 195979.6 214674.3 167063.9
## 57 58 59 60 61 62 63 64
## 227968.7 218550.3 265067.8 331875.7 192084.1 218925.7 238526.9 318618.1
## 65 66 67 68 69 70 71 72
## 293397.2 218979.5 261546.3 240964.4 280082.4 282110.4 205893.0 262434.7
## 73 74 75
## 269862.0 412871.4 252828.2TASK 2n
Based on TASK 2m, answer the following questions:
Question- List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).
Answer-MTenure,CTenure,Comp,Pop, PedCount, Res, Hours24,
Question-List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
Answer- Visibility
TASK 2m
Based on TASK 2m, answer the following questions:
Question- 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- Profit will increase by 760.993 if Manager tenure is increased by a month.
Question-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- Profit will increase by 944.978 if Manager tenure is increased by a month.
TASK 2n
Executive Summary
From the above analysis and regression, In Store24, mean sale = 1205413.12 and median sale = 1227332.0, not to much diffrence in mean and median, so there is possiblity that data is uniformly distributed(not to much outliers). One of the explaination for CTenure has higher beta-coefficient with profit than MTtenure is it’s standard deviation is 17.7 which is less than MTenure’s standard deviation = 57.67. If we see the corrgram plot, we find higher correlation between profit and Manager’s Tenure, Population and Pedesterian Count while Visibility of store have negative correlation and also not statistically significant, which is very surprising and need a real world analysis to find root cause of occurance. R-square(coefficient of determination) is more than 50%, which means all the variables taken to explain variation in profit are significant.While adjusted R-square is less than R-square, so adding more variable is not right way to reduce error.May be there is some other factors that are affecting sales. F-statistic value is not so high so taking 8 variable all together is not fruitful inspite of that indivisual analysis need to done. Opening store for 24 hours is profitable for stores. Although p-value of visibility is statistically significant but beta coefficient is high so it really matter for sales