Store Data Analysis
Sai Harshini
December 18, 2017
This is an R Markdown document which is used to analyse store24.csv data set to predict the effect fof various factors that are responsible for managing employee retention.
2 c) ### Reading the data
store <- read.csv(paste("Store24.csv", sep=""))
View(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 ...getting Summary Statistics the data
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.00library(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.46Summary Statistics Generated from R are cinsistent with those given in tose given in Exebit 3 from the case
2d) 1) To use R to measure the mean and standard deviation of Profit.
mean of profit
mean(store$Profit)## [1] 276313.6standard deviation of Profit
sd(store$Profit)## [1] 89404.08- To use R to measure the mean and standard deviation of MTenure. ### mean of MTenure
mean(store$MTenure)## [1] 45.29644standard deviation of Mtenure
sd(store$MTenure)## [1] 57.67155- To use R to measure the mean and standard deviation of CTenure. ### mean of CTenure
mean(store$CTenure)## [1] 13.9315standard deviation of CTenure
sd(store$CTenure)## [1] 17.697522e) To sort a dataframe based on a data column
Example,
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)2f) To replicate Exhibit 1 shown in the case.
- To use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.
newstore1 <- store[order(-store$Profit),]
View(newstore1)
newstore1[1:10 , c("store" , "Sales" , "Profit" , "MTenure", "CTenure")]## 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- To use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.
newstore2 <- store[order(store$Profit),]
View(newstore2)
newstore2[1:10 , c("store" , "Sales" , "Profit" , "MTenure", "CTenure")]## 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.3470232g) ScatterPlots
- To use R to draw a scatter plot of Profit vs. MTenure.
library(car)##
## Attaching package: 'car'## The following object is masked from 'package:psych':
##
## logitscatterplot(store$MTenure, store$Profit,main="Scatterplot of Profit vs MTenure",
xlab="MTenure", ylab="Profit")
- To use R to draw a scatter plot of Profit vs. CTenure.
library(car)
scatterplot(store$CTenure, store$Profit,main="Scatterplot of Profit vs CTenure",
xlab="CTenure", ylab="Profit")
2 i) Correlation Matrix
8 )To Use R to construct a Correlation Matrix for all the variables in the dataset. (Display the numbers up to 2
Decimal places)
cormatrix <- cor(store)
round(cormatrix, 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.002 j) Correlations
- To use R to measure the correlation between Profit and MTenure. (Display the numbers up to 2 Decimal places)
x <-cor(store$Profit,store$MTenure)
round(x,2)## [1] 0.44- To use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)
y <-cor(store$Profit,store$CTenure)
round(y,2)## [1] 0.262 k) To Use R to construct the following Corrgram based on all variables in the dataset.
library(corrgram)
corrgram(store, order=FALSE, lower.panel=panel.shade, upper.panel=panel.pie, text.panel=panel.txt, main="Corrgram of Store Variables")
Profit Correlation with other variables.
We see that profit is (i)strongly and positively correlated to sales,MTenure , Pop and PedCount. (ii) weekly ans positively correlated to CTenure, Visibility, CrewSkil, Mgrskill and SerQual (iii) negatively correlted to Comp,Res, Hours24.
Managerial revelant Correlations.
- Profit is Strongly and Positively Correlated with Sales.
- Profit is Positively correlated to Mtenure ans Ctenure.
- Sales are positively correlates to profit, MTenure, CTenure, Visibility, PedCount, Hours24, Crewskill, Mgrskill, ServQual
2l ) Pearson’s Correlation Tests
12) To run a Pearson's Correlation test on the correlation between Profit and MTenure and to find the p-vlauecor.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.4388692p-value is 8.193e-05. Since, p value is less than 0.05, we reject the null hypothesis in the favour of research hypothesis that the correlatin between Profit and
MTenure is not 0,ie, Profit and MTenure are correlated.
13) To run a Pearson's Correlation test on the correlation between Profit and CTenure and to find the p-vlauecor.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.2576789p-value is 0.02562. Since, p value is less than 0.05, we reject the null hypothesis in the favour of research hypothesis that the correlatin between Profit and CTenure is not 0,ie, Profit and CTenure are correlated.
2 m) Regression Analysis
14) To run a regression of Profit on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility}.model = lm(Profit ~ MTenure + CTenure + Comp + Pop + PedCount + Res + Hours24 + Visibility, data = store)
summary(model)##
## 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-122 n)
15) List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).
Explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05):
(i) MTenure
(ii) CTenure
(iii) Comp
(iv) Pop
(v) PedCount
(vi) Res
(vii) Hours24
16) List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
Explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05):
(i) Visibility
2 m)
model$coefficients## (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.447050What 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?
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 is $760.992734 , which is the beta value of MTenure in the regression equation
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?
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 is $944.978026 , which is the beta value of CTenure in the regression equation
2 n) Executive Summary
We observer, on an average in each store we get a profit of $276313.6 and MTenure is 45.29 months and CTenure is 13.93 but there are big varitations amony different stores.
From the scatter plots of CTenure and MTenure , we observer that there is a signicant difference between the MTenure and CTenure which might be bacause of the difference in salary oaid to the magers and crew mwnbers and also maybe there there is more demand for crew members and for managers.
Then by finding the correlations we observe that MTenure and Profit are more correlated than Profit and CTenure, which shoes that MTenure has more influence in CTenure than CTenure which might be due to the fact that magers of the firms are more involved in making decisions than the crew members, hence they have a significant effect on the Profits of the firm
4.By drawing the correlation matix we observer that the profit is positively correlated with (i) Sales (ii) MTenure (iii) Ctenure (iv) Pop (v)PedCount (vi)Visibility,
(vii)CrewSkil (vii)Mgrskill and (ix)SerQual ans negatively correlated to comp, Res and Hours24
- Then by Running Person’s correlation test we observe that p value for test between Profit and MTenure and for test between Profit and CTenure turned out to be less than 0.05 , hence rejecting the null hypothesis in the favour of research hypothesis that there is a correlation between them.
Insights based on Regression Analysis
When we run a regression on profit based on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility}, we observer that for the F-Test we get a p-value of 5.382e-12, which is less than 0.05 indication that the beta values of the predictor variables are significantly different from 0, byt rejecting the null hyphothesis. This shows that variables {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility} have considerable impact on profit, the same thing whihc we got to know from the correlation test also.
From observing the p values of invidual predictor variables we observe that MTenure has a significant effect on Profit, which is evident from the fact that more the Tenure of the manager in the firm, the more skill, knowlege and experience they would given and hence contribute more to the profits of the firms , hence increasing the profits of the firm
p value of CTenure is also less than 0.05 , and it has effect on Profit, similar to MTenure.
Comp has significant impact on profit of the firms but negatively related because more the competition from other firms, the customers to the firm would be lesser, hence lesser profits.
Pop and Pedcount have impact on profits and they have positive B values because more the pop or PedCount, more the customers who visit the store, hence more the profits. Similarly res also has positive impact on the firm, because in res areas more no. of customers visit the firm than in industrial areas.
Visibility had p-value greater than 0.05, hence it is statistically insignificant in the Regression Analysis.