TASK 2(a)

Please critically read the Harvard Business School case study Store24 (A): Managing Employee Retention, by Frances X. Frei and Dennis Campbell

This case provides a retailing context in which employee retention strategies are explored through analyzing detailed store-level data. 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 key issue of this case study is that the management is considering new strategies to increase manager crew rentention tenure as they believe it might aid to company’s profit. The first and foremost question that arises is that 1) Do increse in manager-crew tenure affect the profit? If so, 2) How much does increased tenure affect profits?
Further, the management wants to use the manager-crew tenure data they have been collecting for years to make informed decisions about investing in skill development programs, increasing salaries or giving bonuses. The next question which arises is 3) Does increased salaries and bonuses help to retain the employees? Or the management have to invest in skill development programs?
While site-location factors such as population, number of competitors and pedestrain access are traditionally considered as the primary drivers of store success, ‘people factors’, such as employee skill and experience are also crucial factors in optimizing given site’s performance. Hence, we come to the question 4) How much do site-location factors like competitors, population, visibility of the store front etc affect store’s growth?

TASK 2(b)

Play close attention to Exhibit 2 - Variable Names and Descriptions from the case. Understand what each variable means. Go back to the text of the case and relate each variable of Exhibit 2 to the different points of discussion presented in the case. Think about how might a dataset associated with Exhibit 2 help in answering your list of questions from TASK 2a.

TASK 2(c)

Download and review the Store24.csv data file associated with this case. You may open it in Excel for convenience.

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.

store24 <- read.csv(paste("Store24.csv",sep=""))
View(store24)
summary(store24)
##      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 2(d)

Three very important variables in this analysis are the store Profit, the management tenure (MTenure) and the crew tenure (CTenure).

Use R to measure the mean and standard deviation of Profit.

attach(store24)
mean(Profit)
## [1] 276313.6
sd(Profit)
## [1] 89404.08

Use R to measure the mean and standard deviation of MTenure.

mean(MTenure)
## [1] 45.29644
sd(MTenure)
## [1] 57.67155

Use R to measure the mean and standard deviation of CTenure.

mean(CTenure)
## [1] 13.9315
sd(CTenure)
## [1] 17.69752

TASK 2(e) - Sortinng 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

newdata <- mtcars[order(-mpg),] # sort by mpg (descending)

View(newdata)

detach(mtcars)

R has an inbuilt dataset called mtcars.

In the above code, the order() function helps us sort the data in mtcars, based on a data column called mpg. We can sort in ascending order or descending order. After sorting, notice how we can view the top 5 cars that have the highest and lowest mpg respectively.

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 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.

top10 <- store24[order(Profit),]
top10[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

Use R to print the {StoreID, Sales, Profit, MTenure, CTenure} of the bottom 10 least profitable stores.

bot10 <- store24[order(-Profit),]
bot10[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

TASK 2g - Scatter Plots

Use R to draw a scatter plot of Profit vs. MTenure.

library(car)
scatterplot(MTenure,Profit,smoother=loessLine,smooth = TRUE, span = 0.6,smoother.args=list(lty=2),spread=FALSE,store24, main="Scatterplot of Profit vs MTenure")
abline(lm(Profit~MTenure),col = "green")

TASK 2h - Scatter Plots (contd.)

Use R to draw a scatter plot of Profit vs. CTenure.

library(car)
scatterplot(CTenure,Profit,smoother=loessLine,smooth = TRUE, span = 0.5,smoother.args=list(lty=2),spread=FALSE,store24,main="Scatterplot of Profit vs CTenure")
abline(lm(Profit~CTenure),col = "green")

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)

cor_store <- cor(store24)
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.00

TASK 2j - Correlations

Use R to measure the correlation between Profit and MTenure. (Display the numbers up to 2 Decimal places)

cor(Profit,MTenure)
## [1] 0.4388692

Use R to measure the correlation between Profit and CTenure. (Display the numbers up to 2 Decimal places)

cor(Profit,CTenure)
## [1] 0.2576789

TASK 2k

Use R to construct the following Corrgram based on all variables in the dataset.

library(corrgram)
corrgram(store24,type=NULL,upper.panel = panel.pie, main="Corrgram of store variables")

TASK 2l - Pearson’s Correlation Tests

Run a Pearson’s Correlation test on the correlation between Profit and MTenure. 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.4388692

Run a Pearson’s Correlation test on the correlation between Profit and CTenure. What is the p-value?

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.2576789

TASK 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,data=store24)
summary(fit)
## 
## Call:
## lm(formula = Profit ~ MTenure + CTenure + Comp + Pop + PedCount + 
##     Res + Hours24 + Visibility, data = store24)
## 
## 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 2n

Based on TASK 2m, answer the following questions:

List the explanatory variable(s) whose beta-coefficients are statistically significant (p < 0.05).

summary(fit)$coef[summary(fit)$coef[,4] <= 0.05, 4]
##      MTenure      CTenure         Comp          Pop     PedCount 
## 9.715897e-08 2.839955e-02 1.938381e-05 1.489046e-02 3.664408e-04 
##          Res      Hours24 
## 2.262320e-02 1.993586e-03

List the explanatory variable(s) whose beta-coefficients are not statistically significant (p > 0.05).

summary(fit)$coef[summary(fit)$coef[,4] > 0.05, 4]
## (Intercept)  Visibility 
##   0.9096745   0.1694106

TASK 2m

Based on TASK 2m, answer the following questions:

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?

summary(fit)$coefficients["MTenure",1]
## [1] 760.9927

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?

summary(fit)$coefficients["CTenure",1]
## [1] 944.978

TASK 2n

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.

1. MTenure, CTenure, Comp, Pop, PedCount, Res & Hours24 variables financially make an impact on the profits and the manager need to invest more on these factors.
2. Visibility has p>0.05 and do not make any significant impact on the profits. Hence, the manager should invest less on this factor.
3. The most profitable store is Store 57. The least profitable store is Store 74. With these insights, the manager should try to maintain the momentum with store 57 and other most profitable stores. While, for Store 74 and other least profitting stores, the manager should try to invest in the factors that affect profits as mentioned in point number 1.
4. The correlation between Profit and MTenure is 0.4388 and correlation between Profit and CTenure is 0.2576. For every 100 rupees profit in the company, a manager has a 44 rupees worth of contribution at an average and a crew makes a 26 rupees worth of contribution. We can observe from TASK 2m, that retaining manager and employee for one more month can add significant value to the profits and therefore we can infer that retaining an employee would cost less for the company than losing them.