# Task 4a: Qualitatively identify the crucial issues being faced by the management. Based on your judgment, prepare a list of the most important questions that matter.
# The crucial issues faced by management are:
# Q1: They are considering many strategies for increasing manager and crew tenure for which they want to use their data to get some estimate of the actual financial impact of increased tenure so that they can make more informed decisions when considering increasing wages and bonuses or how much to spend on training and development programs.
# Q2: They also want to use their data to help understand how important manager and crew tenure are relative to site-location factors such as population, number of competitors, and pedestrian access in determining store level financial performance."
# Q3: They also want to use their data to help understand how important manager and crew tenure are relative to people factors such as employee skill and experiences in optimizing a given site's performance.
# Task 4b: Think about how might a dataset associated with Exhibit 2 help in answering your list of questions from TASK 2a.
# The dataset has the following variables that can help establish relationships between them to answer the above questions.
# Ans 1: They can find the relationship between sales, profit and Mtenure and Ctenure to establish whether increase in tenure has a positive impact on sales and profit.
# Ans 2: They can find the relationship between pop, Comp, Visible,PedCount, Hours24,Res and Mtenure and Ctenure to establish whether increase in tenure has a positive impact on sales and profit.
# Ans 3: They can find the relationship between MgrSkill, crewSkill and Mtenure and Ctenure to establish whether employee skill and experiences has a positive impact on sales and profit.
# Task 4c: Using R, read the data into a data frame called store. Play close attention to Exhibit 3 - Summary Statistics from Sample Stores from the CASE. Using R, get the summary statistics of the data. Confirm that the summary statistics generated from R are consistent with Exhibit 3 from the Case.
setwd("C:/Users/GOWRI/Desktop/iim_internship/Week_3/Day_1")
store <- read.csv(file="Store24.csv",head=TRUE,sep=",")
View(store)
library(psych)
summaryStore <- describe(store)
SummStore <- summaryStore[ c(3,4,5,8,9)]
SummStore
## mean sd median min max
## store 38.00 21.79 38.00 1.00 75.00
## Sales 1205413.12 304531.31 1127332.00 699306.00 2113089.00
## Profit 276313.61 89404.08 265014.00 122180.00 518998.00
## MTenure 45.30 57.67 24.12 0.00 277.99
## CTenure 13.93 17.70 7.21 0.89 114.15
## Pop 9825.59 5911.67 8896.00 1046.00 26519.00
## Comp 3.79 1.31 3.63 1.65 11.13
## Visibility 3.08 0.75 3.00 2.00 5.00
## PedCount 2.96 0.99 3.00 1.00 5.00
## Res 0.96 0.20 1.00 0.00 1.00
## Hours24 0.84 0.37 1.00 0.00 1.00
## CrewSkill 3.46 0.41 3.50 2.06 4.64
## MgrSkill 3.64 0.41 3.59 2.96 4.62
## ServQual 87.15 12.61 89.47 57.90 100.00
# Task 4d: 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.
SummStore[3:5,]
## mean sd median min max
## Profit 276313.61 89404.08 265014.00 122180.00 518998.00
## MTenure 45.30 57.67 24.12 0.00 277.99
## CTenure 13.93 17.70 7.21 0.89 114.15
# Task 4e: Sorting and Subsetting data in R. In this TASK, we will learn how to sort a dataframe based on a data column. Understand what the following R code does. Copy-Paste it and Execute it in R.
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 4
newdata <- mtcars[order(-mpg),] # sort by mpg (descending)
View(newdata)
detach(mtcars)
# Task 4f:
# Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the top 10 most profitable stores.
sortProfitStoreDesc <- store[order(-store$Profit),]
View(sortProfitStoreDesc)
sortProfitStoreDesc[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
#Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.
sortProfitStoreAsc <- store[order(store$Profit),]
View(sortProfitStoreAsc)
sortProfitStoreAsc[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: Use R to draw a scatter plot of Profit vs. MTenure.
library(car)
##
## Attaching package: 'car'
## The following object is masked from 'package:psych':
##
## logit
scatterplot(Profit~MTenure, data = store, main="Scatterplot of Profit vs Mtenure",xlab="Mtenure",
ylab="Profit", smoother=loessLine)
# Task 4h: Use R to draw a scatter plot of Profit vs. CTenure.
scatterplot(Profit~CTenure, data = store, main="Scatterplot of Profit vs Ctenure",xlab="Ctenure",
ylab="Profit", smoother=loessLine)
# Task 4i: 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), digits=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.00
# Task 4j
# Use R to measure the correlation between Profit and MTenure. (Display the numbers up to 2 Decimal places)
round(cor(store$Profit,store$MTenure), digits=2)
## [1] 0.44
#Use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)
round(cor(store$Profit,store$CTenure), digits=2)
## [1] 0.26
# Task 4k
# Use R to construct the following Corrgram based on all variables in the dataset.
library("corrgram")
## Warning: replacing previous import by 'magrittr::%>%' when loading
## 'dendextend'
corrgram(store, order=FALSE, lower.panel=panel.shade,
upper.panel=panel.pie, text.panel=panel.txt,
main="Corrgram of store variables")
# Critically think about how the Profit is correlated with the other variables (e.g. MTenure, CTenure, Sales, Pop, Comp etc).
# Qualitatively discuss the managerially relevant correlations.
# Ans: Profit is significantly correlated to Mtenure, Comp, Pop, PedCount, Hours24. It means that if Manager tenure, Competitors, Pedestrian count and the availibility period values have a high impact on the profit. These variables could be either positively or negatively correlated to it, which shall help a manager take decisions on these parameters to increase the sales and hence the profit of the store.
# Task 4l
# Run a Pearson's Correlation test on the correlation between Profit and MTenure. What is the p-value?
cor.test(store$Profit, store$MTenure, method="pearson")
##
## 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
# Run a Pearson's Correlation test on the correlation between Profit and CTenure. What is the p-value?
cor.test(store$Profit, store$CTenure, method="pearson")
##
## 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
# Task 4m
# Run a regression of Profit on {MTenure, CTenure Comp, Pop, PedCount, Res, Hours24, Visibility}.
lmMod <- lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
Res + Hours24 + CrewSkill, data = store)
summary(lmMod)
##
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount +
## Res + Hours24 + CrewSkill, data = store)
##
## Residuals:
## Min 1Q Median 3Q Max
## -115203 -29815 -7920 24073 115787
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 121335.197 77044.205 1.575 0.120066
## MTenure 806.634 125.321 6.437 1.61e-08 ***
## CTenure 1107.371 433.793 2.553 0.013008 *
## Comp -25294.903 5477.740 -4.618 1.85e-05 ***
## Pop 4.294 1.495 2.872 0.005483 **
## PedCount 33882.432 9018.906 3.757 0.000367 ***
## Res 95189.799 39187.386 2.429 0.017867 *
## Hours24 70461.295 19978.468 3.527 0.000770 ***
## CrewSkill -27259.352 18060.985 -1.509 0.135996
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 56820 on 66 degrees of freedom
## Multiple R-squared: 0.6398, Adjusted R-squared: 0.5961
## F-statistic: 14.65 on 8 and 66 DF, p-value: 4.585e-12
lmMod$coefficients
## (Intercept) MTenure CTenure Comp Pop
## 121335.196996 806.634064 1107.370688 -25294.903065 4.293792
## PedCount Res Hours24 CrewSkill
## 33882.431773 95189.799031 70461.295165 -27259.351945
confint(lmMod)
## 2.5 % 97.5 %
## (Intercept) -32488.522182 275158.916174
## MTenure 556.422524 1056.845603
## CTenure 241.274958 1973.466419
## Comp -36231.564702 -14358.241428
## Pop 1.308543 7.279041
## PedCount 15875.605702 51889.257845
## Res 16949.658247 173429.939815
## Hours24 30572.996922 110349.593407
## CrewSkill -63319.272663 8800.568772
fitted(lmMod)
## 1 2 3 4 5 6 7 8
## 280640.0 308220.0 237819.9 208951.9 317986.6 380101.8 398337.6 359191.4
## 9 10 11 12 13 14 15 16
## 476519.7 285649.4 411063.9 416194.5 193976.1 281808.0 276542.9 204202.5
## 17 18 19 20 21 22 23 24
## 362265.2 448217.7 261098.2 271321.0 276457.5 264862.8 305113.5 248644.9
## 25 26 27 28 29 30 31 32
## 257773.4 219204.4 245550.4 275588.2 214587.9 258176.7 221360.4 189444.9
## 33 34 35 36 37 38 39 40
## 204539.5 357504.5 298829.1 230471.1 241166.0 190031.4 260820.8 175422.0
## 41 42 43 44 45 46 47 48
## 262529.7 285645.5 358854.6 486830.9 328252.8 267085.0 398954.2 209858.0
## 49 50 51 52 53 54 55 56
## 207828.8 272335.0 284875.6 204699.7 260916.8 201546.9 202822.1 183267.0
## 57 58 59 60 61 62 63 64
## 226773.8 218487.0 270084.3 333969.7 199895.7 221396.4 238670.4 313505.2
## 65 66 67 68 69 70 71 72
## 289506.1 214307.9 263760.7 244259.4 278601.6 286133.7 209481.3 272036.6
## 73 74 75
## 262894.5 415526.6 232269.5
# Task 4n
# List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).
#Ans: Mtenure, Comp, Pop, PedCount, Hours24
# List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).
#Ans: Ctenure, Res, CrewSkill
# Task 4o:
# 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?
# The linear equaltion is:
# Profit = 121335.197 + 806.634* MTenure + 1107.371* CTenure - 25294.903*Comp + 4.294*Pop
# + 33882.432*PedCount + 95189.799*Res +70461.295* Hours24 - 27259.352*CrewSkill
# Ans: If the Mtenure increases by 1 month. as Mtenure is positively correlated, the profit will increase by 806.634.
# 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?
# Profit = 121335.197 + 806.634* MTenure + 1107.371* CTenure - 25294.903*Comp + 4.294*Pop
# + 33882.432*PedCount + 95189.799*Res +70461.295* Hours24 - 27259.352*CrewSkill
# Ans: If the Ctenure increases by 1 month. as Ctenure is positively correlated, the profit will increase by 1107.371.
# Task 4p
# Please prepare an "Executive Summary". Please add this to the end of your Rmd file.
# Specifically, please create a qualitative summary of Managerial Insights, based on your
# data analysis, especially your Regression Analysis. You may write this in
# paragraph form or in point form.
# The case study is trying to analyze how employer(Manager and Crew) retention can help the company improve it's sales and profits. By finding the relationship between sales, profit and Mtenure and Ctenure we can establish whether increase in tenure has a positive impact on sales and profit. Also, by finding the relationship between pop, Comp, Visible,PedCount, Hours24,Res and Mtenure and Ctenure we can establish whether increase in tenure has a positive impact on sales and profit. Moreover, we can find the relationship between MgrSkill, crewSkill and Mtenure and Ctenure to establish whether employee skill and experiences has a positive impact on sales and profit. By seeing the summary statistics of the data, we can see the Manager and crew tenure go upto as low as zero and one month respectively, which definitely is a cause of worry.
# Seeing sortProfitStoreDesc and sortProfitStoreAsc, we can see profits and sales are higher if the positively correlated variables(Like MTenure, CTenure, Visible etc) are high and the negative one like the competitors are low. Also, seeing the scatterplot between Profit and Mtenure, it definitely shows positive coorelation, saying that by increasing their tenure, profits surely will increase, suggesting the decision makers to work on these parameters.
# The regression model also shows that competitors is a negatively correlated variable, which should be tried to be kept low by the decision makers inorder to increase profits. The linear model built also shows that explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05) are Mtenure, Comp, Pop, PedCount, Hours24.So, to answer the questions faced by managers, it is quite important to increase the tenure of the employees by concentrating both on people skills and store location factors to increase profits.