Task 4a- prepare a list of the most important questions that matter.
1.How significantly does each variable affect the profit at the stores?
2.Once we realise, what are the most significant variables that lead to profit, what turns out to be the most effective stratey to retain them?
3.Upto what value of the tenure of the managers and the crew matters, and after what extent it will no longer affect the financial performance of the store.
4.
Task 4c
store <- read.csv(paste("Store24.csv", sep = ""))
View(store)
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.00
Task 4d
mean(store$Profit)
## [1] 276313.6
mean(store$MTenure)
## [1] 45.29644
mean(store$CTenure)
## [1] 13.9315
sd(store$Profit)
## [1] 89404.08
sd(store$MTenure)
## [1] 57.67155
sd(store$CTenure)
## [1] 17.69752
Task 4e
attach(mtcars)
View(mtcars)
newdata <- mtcars[order(mpg),]
View(newdata)
newdata[1:5,]
## 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 4
newdata <- mtcars[order(-mpg),]
View(newdata)
Task 4f
attach(store)
## The following object is masked _by_ .GlobalEnv:
##
## store
newdata1 <- store[order(-Profit),]
View(newdata1)
newdata1[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
newdata1 <- store[order(Profit),]
View(newdata1)
newdata1[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.347023
Task 4g
library(car)
scatterplot(MTenure, Profit)
Task 4h
scatterplot(CTenure, Profit)
Task 4i
library(psych)
##
## Attaching package: 'psych'
## The following object is masked from 'package:car':
##
## logit
library(corrplot)
## corrplot 0.84 loaded
corr.test(store, use="complete")
## Call:corr.test(x = store, use = "complete")
## Correlation matrix
## 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.00
## Sample Size
## [1] 75
## Probability values (Entries above the diagonal are adjusted for multiple tests.)
## store Sales Profit MTenure CTenure Pop Comp Visibility
## store 0.00 1.00 1.00 1.00 1.00 0.89 1.00 1.00
## Sales 0.05 0.00 0.00 0.00 1.00 0.03 1.00 1.00
## Profit 0.09 0.00 0.00 0.01 1.00 0.01 0.26 1.00
## MTenure 0.63 0.00 0.00 0.00 1.00 1.00 1.00 1.00
## CTenure 0.87 0.03 0.03 0.04 0.00 1.00 1.00 1.00
## Pop 0.01 0.00 0.00 0.60 0.99 0.00 1.00 1.00
## Comp 0.79 0.04 0.00 0.12 0.55 0.02 0.00 1.00
## Visibility 0.82 0.26 0.25 0.18 0.57 0.67 0.81 0.00
## PedCount 0.06 0.00 0.00 0.60 0.47 0.00 0.21 0.23
## Res 0.79 0.15 0.17 0.60 0.00 0.04 0.06 0.85
## Hours24 0.82 0.59 0.83 0.16 0.53 0.06 0.27 0.69
## CrewSkill 0.68 0.16 0.17 0.39 0.03 0.01 0.72 0.09
## MgrSkill 0.54 0.01 0.00 0.05 0.29 0.48 0.05 0.53
## ServQual 0.00 0.00 0.00 0.12 0.49 0.29 0.88 0.07
## PedCount Res Hours24 CrewSkill MgrSkill ServQual
## store 1.00 1.00 1.00 1.00 1.00 0.37
## Sales 0.01 1.00 1.00 1.00 0.49 0.05
## Profit 0.00 1.00 1.00 1.00 0.37 0.11
## MTenure 1.00 1.00 1.00 1.00 1.00 1.00
## CTenure 1.00 0.22 1.00 1.00 1.00 1.00
## Pop 0.00 1.00 1.00 1.00 1.00 1.00
## Comp 1.00 1.00 1.00 1.00 1.00 1.00
## Visibility 1.00 1.00 1.00 1.00 1.00 1.00
## PedCount 0.00 0.99 1.00 1.00 1.00 1.00
## Res 0.01 0.00 1.00 1.00 1.00 1.00
## Hours24 0.02 0.45 0.00 1.00 1.00 1.00
## CrewSkill 0.07 0.19 0.37 0.00 1.00 1.00
## MgrSkill 0.46 0.78 0.74 0.86 0.00 0.14
## ServQual 0.96 0.44 0.62 0.78 0.00 0.00
##
## To see confidence intervals of the correlations, print with the short=FALSE option
Task 4j
x <- store[, c("Profit")]
y <- store[, c("MTenure")]
cor(x,y)
## [1] 0.4388692
x <- store[, c("Profit")]
y <- store[, c("CTenure")]
cor(x,y)
## [1] 0.2576789
Task 4k
library(corrgram)
corrgram(store, order = FALSE, lower.panel = panel.shade, upper.panel = panel.pie, text.panel= panel.txt, main = "Corrgram of store variables")
Task 4l
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.4388692
the p value is 0.44
cor.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.2576789
the p value is 0.44
Task 4m
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-12
Task 4n
model <- lm(Profit ~ MTenure, data = store)
summary(model)
##
## Call:
## lm(formula = Profit ~ MTenure, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -177817 -52029 -8635 50871 188316
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 245496.3 11906.4 20.619 < 2e-16 ***
## MTenure 680.3 163.0 4.173 8.19e-05 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 80880 on 73 degrees of freedom
## Multiple R-squared: 0.1926, Adjusted R-squared: 0.1815
## F-statistic: 17.41 on 1 and 73 DF, p-value: 8.193e-05
model$coefficients
## (Intercept) MTenure
## 245496.2904 680.3475
model <- lm(Profit ~ CTenure, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ CTenure, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -139848 -64869 -9022 45057 222393
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 258178.4 12814.4 20.148 <2e-16 ***
## CTenure 1301.7 571.3 2.279 0.0256 *
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 86970 on 73 degrees of freedom
## Multiple R-squared: 0.0664, Adjusted R-squared: 0.05361
## F-statistic: 5.192 on 1 and 73 DF, p-value: 0.02562
model$coefficients
## (Intercept) CTenure
## 258178.442 1301.739
model <- lm(Profit ~ Comp, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ Comp, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -172707 -65521 -24559 56628 209205
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 362702 30119 12.042 < 2e-16 ***
## Comp -22807 7520 -3.033 0.00335 **
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 84830 on 73 degrees of freedom
## Multiple R-squared: 0.1119, Adjusted R-squared: 0.09975
## F-statistic: 9.2 on 1 and 73 DF, p-value: 0.003351
model$coefficients
## (Intercept) Comp
## 362702.27 -22807.37
model <- lm(Profit ~ Pop, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ Pop, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -152198 -52285 -17228 43501 235602
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 2.123e+05 1.829e+04 11.611 < 2e-16 ***
## Pop 6.513e+00 1.598e+00 4.077 0.000115 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 81240 on 73 degrees of freedom
## Multiple R-squared: 0.1854, Adjusted R-squared: 0.1743
## F-statistic: 16.62 on 1 and 73 DF, p-value: 0.000115
model$coefficients
## (Intercept) Pop
## 212323.4932 6.5126
model <- lm(Profit ~ PedCount, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ PedCount, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -131878 -57678 -1538 45741 200501
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 156254 29373 5.320 1.09e-06 ***
## PedCount 40561 9415 4.308 5.06e-05 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 80370 on 73 degrees of freedom
## Multiple R-squared: 0.2027, Adjusted R-squared: 0.1918
## F-statistic: 18.56 on 1 and 73 DF, p-value: 5.057e-05
model$coefficients
## (Intercept) PedCount
## 156253.57 40560.82
model <- lm(Profit ~ Res, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ Res, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -151243 -62419 -9467 57891 245575
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 345696 51305 6.738 3.18e-09 ***
## Res -72273 52363 -1.380 0.172
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 88860 on 73 degrees of freedom
## Multiple R-squared: 0.02543, Adjusted R-squared: 0.01208
## F-statistic: 1.905 on 1 and 73 DF, p-value: 0.1717
model$coefficients
## (Intercept) Res
## 345695.67 -72272.97
model <- lm(Profit ~ Hours24, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ Hours24, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -153138 -64315 -11246 52884 237458
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 281540 25976 10.84 <2e-16 ***
## Hours24 -6222 28342 -0.22 0.827
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 89980 on 73 degrees of freedom
## Multiple R-squared: 0.0006598, Adjusted R-squared: -0.01303
## F-statistic: 0.0482 on 1 and 73 DF, p-value: 0.8268
model$coefficients
## (Intercept) Hours24
## 281540.417 -6222.385
model <- lm(Profit ~ Visibility, data= store)
summary(model)
##
## Call:
## lm(formula = Profit ~ Visibility, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -152838 -63359 -10946 43839 243980
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 226431 43854 5.163 2.02e-06 ***
## Visibility 16196 13840 1.170 0.246
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 89180 on 73 degrees of freedom
## Multiple R-squared: 0.01841, Adjusted R-squared: 0.004966
## F-statistic: 1.369 on 1 and 73 DF, p-value: 0.2457
model$coefficients
## (Intercept) Visibility
## 226430.94 16195.67
statistically significant variables are :MTenure, CTenure Comp, Pop, PedCount(with p-values less than 0.05)
statistically in-significant variables are : res, Hours24, Visibility
(with p-values less than 0.05)
Task 4o:
Formula for Profit and MTenure:
Profit = 680Mtenure + 245496 #Formula for Profit amd CTenure:; #Profit = 6.51Ctenure + 2.12e+05
So 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 680.
And 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 6.51
Task 4p:
Executive summary:
1. Almost always, the profit of a store is more if the value of the managerial tenure at the store is greater than the value of the crew tenure.
2. For lower values of MTenure, the graph is less steep as compared to the case of the graph with the lower values on CTenure, which means for smaller range of values, increasing CTenure has a greater effect on the profit, as compared to MTenure.Not only that, MTenure affects the profits less steeply after crossing the value of ‘200 months’ (approx.) and in the case of CTenure, it is ‘70 months’(approx.).
3. In the corrgram plotted, the blue rectangles for ‘Mangerial skills’ and ‘Service quality’ are positive(blue color, ofcourse), and darker which suggests that while focusing on the factors that will improve the profits of the stores the most, career development for mangers is beneficial, and training program for the crew, as if they retain longer, they will provide better service(this is said in keeping in assumption of the idea that the service offered by the crew depends on what they learn and not what they earn;))
4. The coefficient for ‘Pop’ is least, which means the profit of a store is least affected(in increased) as compared to all other factors.