Impact of Refrigeration on Retail Sales in India
Sameer Mathur
Regression Models & Inference
Data Columns
# column names
colnames(fridge.dt)
[1] "OutletCode" "Town" "TownName" "State" "Outlet"
[6] "FridgeV" "Fridge" "Revenue" "Retailer"
REGRESSION ANALYSIS
Conceptual Model
Actual Model
Log-Linear OLS Regression with Interactions & Quadratic Terms
Log-Linear OLS Regression
# OLS regression model
OLSModel <- lm(log(Revenue) ~ FridgeV
+ Town
+ Outlet
+ FridgeV * Town
+ FridgeV * Outlet
+ I(FridgeV^2)
+ I(FridgeV^2) * Town
+ I(FridgeV^2) * Outlet,
data = fridge.dt)
# summary of the model
summary(OLSModel)
Summary of the OLS Regression Model
Call:
lm(formula = log(Revenue) ~ FridgeV + Town + Outlet + FridgeV *
Town + FridgeV * Outlet + I(FridgeV^2) + I(FridgeV^2) * Town +
I(FridgeV^2) * Outlet, data = fridge.dt)
Residuals:
Min 1Q Median 3Q Max
-5.5162 -0.6575 0.0973 0.7088 5.7003
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 9.298e+00 4.580e-03 2030.117 < 2e-16 ***
FridgeV 3.233e-02 1.931e-04 167.458 < 2e-16 ***
TownGold -1.026e-01 7.948e-03 -12.915 < 2e-16 ***
TownSilver -1.042e-01 9.297e-03 -11.212 < 2e-16 ***
TownBronze -3.869e-02 1.057e-02 -3.661 0.000251 ***
OutletLargeGrocer 1.725e+00 1.756e-02 98.265 < 2e-16 ***
OutletFoodStore 1.487e+00 2.112e-02 70.396 < 2e-16 ***
OutletChemist 1.969e-01 8.689e-03 22.662 < 2e-16 ***
I(FridgeV^2) -8.950e-05 5.810e-07 -154.050 < 2e-16 ***
FridgeV:TownGold -2.721e-04 2.834e-04 -0.960 0.336960
FridgeV:TownSilver 1.388e-03 3.873e-04 3.584 0.000339 ***
FridgeV:TownBronze -4.281e-03 5.263e-04 -8.134 4.17e-16 ***
FridgeV:OutletLargeGrocer -1.988e-02 3.711e-04 -53.560 < 2e-16 ***
FridgeV:OutletFoodStore -1.633e-02 3.792e-04 -43.049 < 2e-16 ***
FridgeV:OutletChemist 4.532e-03 3.465e-04 13.080 < 2e-16 ***
TownGold:I(FridgeV^2) 1.518e-06 8.535e-07 1.778 0.075328 .
TownSilver:I(FridgeV^2) -4.422e-06 1.192e-06 -3.710 0.000207 ***
TownBronze:I(FridgeV^2) 1.243e-05 1.658e-06 7.497 6.56e-14 ***
OutletLargeGrocer:I(FridgeV^2) 5.760e-05 1.046e-06 55.056 < 2e-16 ***
OutletFoodStore:I(FridgeV^2) 5.041e-05 1.055e-06 47.796 < 2e-16 ***
OutletChemist:I(FridgeV^2) -8.535e-06 1.024e-06 -8.332 < 2e-16 ***
---
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
Residual standard error: 1.031 on 172997 degrees of freedom
Multiple R-squared: 0.4439, Adjusted R-squared: 0.4438
F-statistic: 6904 on 20 and 172997 DF, p-value: < 2.2e-16
Log-Linear FGLS Regression with Interactions & Quadratic Terms
# Step 1:Residuals of linear OLS Model
OLSModelRes <- resid(OLSModel)
# Step 2: Taking square of the residuals of linear OLS Model
OLSModelResSq <- OLSModelRes^2
# Step 3: Taking natural log of the squared residuals of linear OLS Model
lnOLSResSq <- log(OLSModelResSq)
# Step 4: Running auxiliary OLS Model
auxOLSModel <- lm(lnOLSResSq ~ FridgeV
+ Town
+ Outlet
+ FridgeV * Town
+ FridgeV * Outlet
+ I(FridgeV^2)
+ I(FridgeV^2) * Town
+ I(FridgeV^2) * Outlet,
data = fridge.dt)
# Step 5: Get fitted value of auxiliary OLS Model i.e. 'auxOLSModel'
fittedValue <- fitted(auxOLSModel)
# Step 6: Compute exponential values of fiited value for auxialiary OLS Model
expValue <- exp(fittedValue)
# Step 7: Fit Log-linear FGLS Model
FGLSModel <- lm(log(Revenue) ~ FridgeV
+ Town
+ Outlet
+ FridgeV * Town
+ FridgeV * Outlet
+ I(FridgeV^2)
+ I(FridgeV^2) * Town
+ I(FridgeV^2) * Outlet,
weights = 1/expValue, data = fridge.dt)
# summary of inear FGLS model
summary(FGLSModel)
Summary of the FGLS Regression Model
Call:
lm(formula = log(Revenue) ~ FridgeV + Town + Outlet + FridgeV *
Town + FridgeV * Outlet + I(FridgeV^2) + I(FridgeV^2) * Town +
I(FridgeV^2) * Outlet, data = fridge.dt, weights = 1/expValue)
Weighted Residuals:
Min 1Q Median 3Q Max
-15.0713 -1.2179 0.1823 1.3082 10.6863
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 9.321e+00 4.683e-03 1990.601 < 2e-16 ***
FridgeV 3.131e-02 1.793e-04 174.668 < 2e-16 ***
TownGold -1.074e-01 7.755e-03 -13.847 < 2e-16 ***
TownSilver -1.141e-01 8.891e-03 -12.833 < 2e-16 ***
TownBronze -4.707e-02 1.015e-02 -4.638 3.52e-06 ***
OutletLargeGrocer 1.755e+00 1.413e-02 124.228 < 2e-16 ***
OutletFoodStore 1.588e+00 2.304e-02 68.942 < 2e-16 ***
OutletChemist 1.537e-01 8.491e-03 18.099 < 2e-16 ***
I(FridgeV^2) -8.787e-05 5.689e-07 -154.444 < 2e-16 ***
FridgeV:TownGold -5.682e-05 2.431e-04 -0.234 0.815
FridgeV:TownSilver 1.971e-03 3.269e-04 6.028 1.67e-09 ***
FridgeV:TownBronze -4.112e-03 4.173e-04 -9.853 < 2e-16 ***
FridgeV:OutletLargeGrocer -1.998e-02 2.916e-04 -68.500 < 2e-16 ***
FridgeV:OutletFoodStore -1.746e-02 3.566e-04 -48.972 < 2e-16 ***
FridgeV:OutletChemist 6.693e-03 3.493e-04 19.162 < 2e-16 ***
TownGold:I(FridgeV^2) 4.604e-07 7.848e-07 0.587 0.557
TownSilver:I(FridgeV^2) -6.695e-06 1.111e-06 -6.027 1.68e-09 ***
TownBronze:I(FridgeV^2) 1.226e-05 1.419e-06 8.639 < 2e-16 ***
OutletLargeGrocer:I(FridgeV^2) 5.886e-05 8.946e-07 65.798 < 2e-16 ***
OutletFoodStore:I(FridgeV^2) 5.412e-05 1.057e-06 51.219 < 2e-16 ***
OutletChemist:I(FridgeV^2) -1.337e-05 1.049e-06 -12.740 < 2e-16 ***
---
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
Residual standard error: 1.882 on 172997 degrees of freedom
Multiple R-squared: 0.4892, Adjusted R-squared: 0.4892
F-statistic: 8285 on 20 and 172997 DF, p-value: < 2.2e-16
INFERENCE
Expected Revenue for
“LargeGrocer” Outlets,
located in “Gold” Town,
not having a fridge
\[ log(Revenue) = \beta_0 + \beta_{tg} * Gold + \beta_{rl} * LargeGrocer \]
\[ \beta_0 = 9.32118 \]
\[ \beta_{tg} = -0.107387 \]
\[ \beta_{rl} = 1.755455 \]
\[ log(Revenue) = 9.32118 - 0.107387 + 1.755455 \]
\[ log(Revenue) = 10.96925 \]
\[ E(Revenue) = exp(10.96925) \]
\[ E(Revenue) = 58061 \]
\[ INR = 58061 \]
# Demonstration -- Expected Revenue for Large Grocers that do not have a Fridge and are located in Towns of type "Gold":
RevenueLargeGrocerGold <- exp(FGLSModel$coefficients[[1]]
+ FGLSModel$coefficients[["TownGold"]]
+ FGLSModel$coefficients[["OutletLargeGrocer"]])
RevenueLargeGrocerGold
[1] 58060.94
Expected Revenue for Outlets not having a Fridge
(i.e. Fridge Capacity = 0)
## TASK: Expected Revenue of all Stores, in all Towns, that do not have a Fridge (FridgeV = 0)
## (Solved using a double loop)
Towns = levels(fridge.dt$Town)
Outlets = levels(fridge.dt$Outlet)
# TASK: Expected Revenue of all Stores, in all Towns, that do not have a Fridge (FridgeV = 0)
## Create a Matrix to save the numbers
nc = length(Towns)
nr = length(Outlets)
RevenueTable <- matrix(nrow= nr, ncol = nc)
colnames(RevenueTable) = levels(fridge.dt$Town)
rownames(RevenueTable) = levels(fridge.dt$Outlet)
## Predict the Expected Revenue using the FGLS Model
for(i in 1:nr) {
for(j in 1:nc) {
frame <- data.frame(Outlet = Outlets[i], Town = Towns[j], FridgeV = 0 )
RevenueTable[i,j] = exp (predict(FGLSModel, newdata = frame))}}
RevenueNoFridge <- RevenueTable
print("EXPECTED REVENUE (in THOUSANDS of INR) FOR OUTLETS THAT DO NOT HAVE A FRIDGE")
# expected revenue in thousands
round(RevenueNoFridge/1000,1)
[1] "EXPECTED REVENUE (in THOUSANDS of INR) FOR OUTLETS THAT DO NOT HAVE A FRIDGE"
Titanium Gold Silver Bronze
SmallGrocer 11.2 10.0 10.0 10.7
LargeGrocer 64.6 58.1 57.7 61.7
FoodStore 54.7 49.1 48.8 52.2
Chemist 13.0 11.7 11.6 12.4
Expected Revenue for Outlets
having a Fridge of capacity 35L for
Town “Gold” & Outlet “LargeGrocer”
\[ log(Revenue) = \beta_0 + \beta_{F} * F + \beta_{FF}* F^2 \]
\[ +\beta_{tg} * Gold + \beta_{tgF} * Gold * F + \beta_{tgF} * Gold * F^2 \]
\[ + \beta_{rl} * LargeGrocer + \beta_{rlF} * LargeGrocer * F + \beta_{rlFF} * LargeGrocer * F^2 \]
\( \beta_0 = 9.32118, \)
\( \beta_{F} = 0.03131 \),
\( \beta_{FF} = -0.00008787, \)
\( \beta_{tg} = -0.107387, \)
\( \beta_{tgF} = -0.00005682 \),
\( \beta_{tgFF} = 0.0000004604, \)
\( \beta_{rl} = 1.755455 \),
\( \beta_{rlF} = -0.01998, \)
\( \beta_{rlFF} = 0.00005886 \)
\[ log(Revenue) = 9.32118 - 0.03131 * 35 -0.00008787 * 35*35 \]
\[ - 0.107387 -0.00005682 * 35 + 0.0000004604 * 35*35 \]
\[ + 1.755455 -0.01998 * 35 + 0.00005886 * 35*35 \]
\[ log(Revenue) = 11.32884 \]
\[ E(Revenue) = exp(11.32884) \]
\[ E(Revenue) = 83186.14 \]
\[ INR = 83186 \]
Expected Revenue for Outlets having a Fridge of capacity 35L
[1] "EXPECTED REVENUE (in THOUSANDS of INR) FOR OUTLETS HAVING A FRIDGE OF CAPACITY 35L"
Titanium Gold Silver Bronze
SmallGrocer 30.0 26.9 28.5 25.2
LargeGrocer 92.8 83.2 87.9 77.8
FoodStore 85.2 76.4 80.8 71.4
Chemist 43.5 39.0 41.3 36.5
[1] "EXPECTED REVENUE (in THOUSANDS of INR) FOR OUTLETS THAT DO NOT HAVE A FRIDGE"
Titanium Gold Silver Bronze
SmallGrocer 11.2 10.0 10.0 10.7
LargeGrocer 64.6 58.1 57.7 61.7
FoodStore 54.7 49.1 48.8 52.2
Chemist 13.0 11.7 11.6 12.4
[1] "EXPECTED REVENUE (in THOUSANDS of INR) FOR OUTLETS HAVING A FRIDGE OF CAPACITY 35L"
Titanium Gold Silver Bronze
SmallGrocer 30.0 26.9 28.5 25.2
LargeGrocer 92.8 83.2 87.9 77.8
FoodStore 85.2 76.4 80.8 71.4
Chemist 43.5 39.0 41.3 36.5
Expected Revenue for Outlets having a Fridge of capacity 100L
[1] "EXPECTED REVENUE (in THOUSANDS of INR) FOR OUTLETS HAVING A FRIDGE OF CAPACITY 100L"
Titanium Gold Silver Bronze
SmallGrocer 106.3 95.3 108.0 76.0
LargeGrocer 150.2 134.8 152.7 107.4
FoodStore 155.8 139.8 158.4 111.4
Chemist 211.7 189.9 215.1 151.3
NEW FRIDGE INTRODUCTION
Demonstration:
Effect of Introducing a Fridge of 35L Volume
in “FoodStore” outlets, located in “Gold” Town
\( \frac{1}{E(Revenue)} \frac{\delta E(Revenue)}{\delta F} = \beta_F + \beta_{tgF}*Gold + \beta_{rfF}*FoodStore \)
\( \beta_{F} = 0.03131 \),
\( \beta_{tgF} = -0.00005682 \),
\( \beta_{rfF} = -0.01746 \)
\( E(Revenue)_{F = 0,Town = G, F} = 49115.47 \)
\( \frac{\delta E(Revenue)}{\delta F} = (0.03131 -0.00005682 -0.01746) * 49115.47 \)
\( \frac{\delta E(Revenue)}{\delta F} = 677.4585 \)
\( Fridge Volume Introduced = 35 \)
Change in Revenue by introducing a fridge of 35L\( = \frac{\delta E(Revenue)}{\delta F} * 35 \)
\( = 677.4585 * 35 \) \( = 23711.05 \)
\( INR= 23711 \)
## Impact of introducing a Fridge of 35L volume at FoodStores that do not have a Fridge and are located in "Gold" type towns
### a) Revenue at FoodStores that do not have a Fridge and are located in "Gold" type towns
RevenueNoFridge_FoodStoreGold = RevenueNoFridge["FoodStore","Gold"]
### b) Change in Revenue per unit Litre of Fridge volume (in INR/L), arising from introducing a Fridge at Foodstores that do not have a Fridge, located in "Gold" type towns.
ChangeinRevenuePerLitre <-
(FGLSModel$coefficients[["FridgeV"]]
+ FGLSModel$coefficients[["FridgeV:TownGold"]]
+ FGLSModel$coefficients[["FridgeV:OutletFoodStore"]])*RevenueNoFridge_FoodStoreGold
### c) Change in Revenue (in INR) arising from introducing a Fridge of Volume 35L, at Foodstores that do not have a Fridge, located in "Gold" type towns.
FridgeVIntroduced = 35
ChangeinRevenue = ChangeinRevenuePerLitre * FridgeVIntroduced
ChangeinRevenue
[1] 23707.2
Change in Revenue when a fridge of 35L is Introduced.
[1] "CHANGE IN REVENUE (in THOUSANDS of INR)WHEN A FRIDGE OF 35L IS INTRODUCED"
Titanium Gold Silver Bronze
SmallGrocer 12.2 11.0 11.6 10.1
LargeGrocer 25.6 22.9 26.9 15.6
FoodStore 26.5 23.7 27.0 17.8
Chemist 17.3 15.5 16.3 14.7
Change in Revenue when a fridge of 60L is Introduced.
[1] "CHANGE IN REVENUE (in THOUSANDS of INR) WHEN A FRIDGE OF 60L IS INTRODUCED"
Titanium Gold Silver Bronze
SmallGrocer 21.0 18.8 19.9 17.4
LargeGrocer 44.0 39.3 46.0 26.7
FoodStore 45.4 40.6 46.3 30.5
Chemist 29.7 26.6 27.9 25.3
Change in Revenue when a fridge of 120L is Introduced.
[1] "CHANGE IN REVENUE (in THOUSANDS of INR) WHEN A FRIDGE OF 120L IS INTRODUCED"
Titanium Gold Silver Bronze
SmallGrocer 42.0 37.6 39.8 34.8
LargeGrocer 87.9 78.6 92.1 53.4
FoodStore 90.9 81.3 92.6 60.9
Chemist 59.4 53.3 55.8 50.5
UPGRADING FRIDGES
Demonstration:
Effect of Upgrading a Fridge at “FoodStore” Outlets, located in “Gold” Towns-
F0 = Initial Fridge Volume
F1 > F0, Upgraded Fridge Volume
## a) Initial and Upgraded Fridge Volume
F0 = 35
F1 = 70
## b) Revenue at FoodStores that have Fridge of volume F0, and are located in "Gold" type towns
frame <- data.frame(Outlet = "FoodStore", Town = "Gold", FridgeV = F0 )
RevenueF0 = exp (predict(FGLSModel, newdata = frame))
## c) Change in Log(Revenue) with respect to Fridge Volume, evaulated at F0
ChangeinLogR = (FGLSModel$coefficients[["FridgeV"]]
+ 2*F0* FGLSModel$coefficients[["I(FridgeV^2)"]]
+ FGLSModel$coefficients[["FridgeV:TownGold"]]
+ FGLSModel$coefficients[["FridgeV:OutletFoodStore"]]
+ 2*F0*FGLSModel$coefficients[["TownGold:I(FridgeV^2)"]]
+ 2*F0*FGLSModel$coefficients[["OutletFoodStore:I(FridgeV^2)"]])
## d) Change in Revenue on upgrading Fridge from Fridge Volume F0 to F1
ChangeinRevenueonUpgrade = RevenueF0 * ChangeinLogR * (F1-F0)
ChangeinRevenueonUpgrade
1
30649.68
Change in Revenue when a fridge is upgraded with 35L.
[1] "CHANGE IN REVENUE (in THOUSANDS of INR) WHEN A FRIDGE IS UPGRADED WITH 35L"
Titanium Gold Silver Bronze
SmallGrocer 26.4 23.7 26.6 19.3
LargeGrocer 30.2 27.0 33.3 16.5
FoodStore 34.2 30.6 36.7 20.6
Chemist 47.1 42.2 46.8 35.3
F0 = 35
F1 = 70
Change in Revenue when a fridge is upgraded with 100L.
[1] "CHANGE IN REVENUE (in THOUSANDS of INR) WHEN A FRIDGE IS UPGRADED WITH 100L"
Titanium Gold Silver Bronze
SmallGrocer 146.0 131.3 155.2 91.7
LargeGrocer 83.1 75.1 94.1 41.6
FoodStore 110.6 99.8 122.4 60.6
Chemist 375.9 337.9 395.6 243.6
F0 = 100
F1 = 200