a) Objectives:

To calibrate the pricing strategy of retailers by understanding the drivers of demand for organic and conventional produce.

Organic produce has gained immense popularity in last few years. With Avocados as example we examine following business problems for Organic and Conventional fresh produce:

• Descriptive: What are the underlying factors affecting prices and sales between organic and conventional avocados?

• Causal: What is the best pricing strategy for retailers between organic and conventional?

More specifically:

b) Dataset:

The dataset taken from Kaggle was sourced from Haas Avocado Board website in May 2018 and compiled into a single csv file.

The data contains weekly retail scan data for national retail volume (units) and price. Retail scan data comes directly from retailers’ cash registers based on actual retail sales of Hass avocados. Starting in 2013, the data reflects an expanded, multi-outlet retail data set. Multi-outlet reporting includes an aggregation of the following channels: grocery, mass, club, drug, dollar and military. The Average Price (of avocados) in the dataset is per pound. The Product Lookup codes (PLU’s) in the table are only for Hass avocados. Other varieties of avocados (e.g. greenskins) are not included in this table.

The dataset contains time series data from 2013 to 2018 with 18,249 rows.

The dataset has 14 columns, as described below:

· Date - The date of the observation

· AveragePrice - the average price per lb

· type - conventional or organic

· year - the year

· Region - the city or region of the observation

· Total Volume - Total lbs of avocados sold

· 4046 - Total lbs of avocados with PLU 4046 sold

· 4225 - Total lbs of avocados with PLU 4225 sold

· 4770 - Total lbs of avocados with PLU 4770 sold

· Total Bags – Total lbs of avocado bags sold

· Small Bags – Total lbs of avocado bags sold with PLU 4046

· Large Bags – Total lbs of avocado bags sold with PLU 4225

· XLarge Bags – Total lbs of avocado bags sold with PLU 4770

Load Packages

library(tidyverse)
library(broom)
library(knitr)
library(grid)
library(gridExtra)
library(lubridate)

Read in data

df <- read_csv("avocado.csv")

Observations from the data:

  1. There are a total of 53 regions & cities. Of this there are 8 regions which are West, California, Northeast, SouthCentral,GreatLakes, Plains, Southeast, and Midsouth. We also checked that the volume for the eight regions would add up to TotalUS. So, the cities add up to the 8 divisions (California, Southcentral etc.) and the 8 divisions adds up to TotalUS. (The regions are based on Haas Avocado Board, the original data source)

  2. The time series data has weekly numbers from 4th Jan 2013 to 25th Mar 2018.

  3. Total Volume = 4046 + 4225 + 4770 + Total Bags

  4. The unit for Price is $/per pound. The units for avocados are pounds (lbs).

  5. The average weekly volume of avocados for the period is 8,59,644, with a large range of 625,05,561.

  6. The column ‘Type’ contains two types ‘organic’ and ‘conventional’.
  7. All the Haas avocado data is collected weekly.

  8. The Product Lookup Codes 4046, 4225, 4770 refer to different size of Haas Avocado.

  • 4046 : Small/Medium Hass Avocado (~3-5oz avocado)

  • 4225 : Large Hass Avocado (~8-10oz avocado)

  • 4770 : Extra Large Hass Avocado (~10-15oz avocado)

Data Cleaning

Remove the first column (index) and year column (same information with date)

df <- df[, c(2:12, 14)]               # Get rid of X1(index) and year(same as Date)
df$type <-factor(df$type)             # Make type variable a factor
df <- arrange(df, Date, region)       # Reorder dataframe so it starts from the furthest date
unique(df$type)                  # 2 types of avocados
## [1] conventional organic     
## Levels: conventional organic
range(df$Date)                   # date is ranged from 2015/1/4 to 2018/3/25
## [1] "2015-01-04" "2018-03-25"
length(unique(df$region))        # 54 unique regions including 8 divisions and TotalUS
## [1] 54
RegionVector <- c("West", "California", "Northeast", "SouthCentral","GreatLakes", "Plains", "Southeast", "Midsouth", "TotalUS")

We now want to examine the affect of conventional produce on organic produce and vice versa. For this we need cross-sectional data across all weeks, so we create the final dataset as below:

i) Divide the dataset into Organic and Conventional

ii) Join to form a final dataset. 

Organic <- subset(df, type=="organic" & region %in% c("West", "California", "Northeast", "SouthCentral","GreatLakes", "Plains", "Southeast", "Midsouth", "TotalUS"))
Conventional <- subset(df, type=="conventional" & region %in% c("West", "California", "Northeast", "SouthCentral","GreatLakes", "Plains", "Southeast", "Midsouth", "TotalUS"))

Keep only the Date, price, volume of large avocado and region for both Organic and Conventional.

Rename columns AveragePrice and Total.Volume for organic and conventional as P_Organic, Q_Organic, P_Conventional and Q_Conventional respectively.

avocado <- left_join(Organic, Conventional, by = c("Date", "region"), suffix = c("_Oragnic", "_Conventional"))
# Keep only the Date, price, volume of large avocado and region for both Organic and Conventional
avocado <- avocado[,c(1, 2, 3, 13, 14, 12)]
names(avocado) <- c("Date", "P_Organic", "Q_Organic", "P_Conventional", "Q_Conventional", "Region")
# Remove TotalUS records
avocado_without_totalUS <- avocado %>%
  filter(Region != "TotalUS")

c) Exploratory Analysis:

EDA Plotting

For plotting purposes, we use blue for Organic, red for Conventional

ggplot(data = df%>%filter(region=="TotalUS"), aes(x = Date, y = AveragePrice, color = type)) +
  geom_line()

There’s a big decrease in Organic Price in the middle of 2015. However, the regional prices did not fall, TotalUS is just an aggregate of other regions. So, there is an error in TotalUS Organic price rows. We have to recalculate the average price from the average regional prices.

LowDates <- avocado %>%
  filter(Region == "TotalUS") %>%
  filter(P_Organic < 1.1) %>%
  select(Date) %>%
  as.matrix() %>%
  as.vector() %>%
  ymd()
LowDatesPQ <- avocado_without_totalUS %>%
  filter(Date %in% LowDates) %>%
  select(Date, P_Organic, Q_Organic) %>%
  mutate(PQ = P_Organic * Q_Organic) %>%
  group_by(Date) %>%
  summarise(TotalPQ = sum(PQ)) %>%
  .$TotalPQ 
LowDatesPQ
## [1] 1117723.3 1018157.4 1056236.6  992224.7 1021418.1 1100028.1
LowDatesAvgP <- vector(length = 6)
for (i in 1:length(LowDates)) {
  LowDatesAvgP[i] <- avocado_without_totalUS %>%
  filter(Date == LowDates[i]) %>%
  select(Date, P_Organic, Q_Organic) %>%
  mutate(PQ = P_Organic * Q_Organic) %>%
  mutate(Perc_PQ = PQ/LowDatesPQ[i]) %>%
  summarise(AVG_Price = sum(P_Organic * Perc_PQ)) %>%
  .$AVG_Price
}
LowDatesAvgP
## [1] 1.690302 1.701110 1.650188 1.738636 1.798959 1.776395
# Substitue the avg price of the six weeks 
avocado[avocado$Region == "TotalUS" & avocado$Date %in% LowDates, ]$P_Organic <- round(LowDatesAvgP, 2)
df[df$region == "TotalUS" & df$Date %in% LowDates & df$type == "organic", ]$AveragePrice <- round(LowDatesAvgP, 2)
avocado_without_totalUS <- avocado %>% filter(Region != "TotalUS")

Now, the graph will not have the sudden decrease in price and the graphs is good:

ggplot(data = df%>%filter(region=="TotalUS"), aes(x = Date, y = AveragePrice, color = type)) +
  geom_line()

Now, lets try to compare the prices and quantity of organic and conventional avocados.
We will also check if we have a Time Trend over the years in our data.

First, the price comparison:

ggplot(data = df%>%filter(region=="TotalUS"), aes(x = Date, y = AveragePrice, color = type)) +
  geom_line() +
  ggtitle("Price Comparison of Organic and Conventional avocaods in the U.S.") +
  ylab("Price") +
  geom_smooth(method = "lm") + 
  theme_minimal()
## `geom_smooth()` using formula 'y ~ x'

We see that organic prices are much higher than conventional prices. There is also a time trend, the prices are rising slightly over years.

Now, lets compare quantities:

ggplot(data = df %>% filter(region=="TotalUS"), aes(x = Date, y = `Total Volume`, color = type)) +
  geom_line() +
  scale_y_continuous(labels = scales::comma) + 
  ylab("Total Volume (lbs)") +
  ggtitle("Quantity Comparison of Organic and Conventional avocaods in the U.S.") +
  theme_minimal()

The sales of conventional avocados are much higher. Due to the scale of y axis the changes in organic quantity over time are not clear. We will plot them seperately:

Organic Quantities:

ggplot(data = avocado %>% filter(Region == "TotalUS"), aes(x = Date, y = Q_Organic)) +
  geom_line(color = "#00BFC4") +
  scale_y_continuous(labels = scales::comma) + 
  ylab("Total Volume (lbs)") +
  ggtitle("Quantity of Organic avocaods in the U.S.") +
  theme_minimal() +
  geom_smooth(method = "lm")
## `geom_smooth()` using formula 'y ~ x'

The sales of organic avocados are clearly rising over the years. This is expected as see a increase consumer awareness about the benefits of organic produce.

Conventional quantities:

ggplot(data = avocado %>% filter(Region == "TotalUS"), aes(x = Date, y = Q_Conventional)) +
  geom_line(color = "#F8766D") +
  scale_y_continuous(labels = scales::comma) + 
  ylab("Total Volume (lbs)") +
  ggtitle("Quantity of Conventional avocaods in the U.S.") +
  theme_minimal() +
  geom_smooth(method = "lm")
## `geom_smooth()` using formula 'y ~ x'

Though there is increase in Avocado sales over the years, the slope of increase is much steaper in organic Avocados.

Through all these graphs we have already noticed the seasonal variation in the sales. Let us plot a graph which explains some of these sudden spikes in sales.

We see a peak in avocado sales of conventional produce around super bowl and Cinco de Mayo every year. These are expected as the Super Bowl Sunday is the biggest day for avocado consumption in the States.

What is also interesting is that these spikes are only seem in conventional sales, which is to be expected as the F&B industry stocks up on avocados in the run up to Super Bowl with the ever increasing consumption of guacamole.

Super bowl dates: 2015/2/1, 2016/2/7, 2017/2/5, 2018/2/4 Cinco de Mayo: 2015/5/3,2016/5/8, 2017/5/6

ggplot(data = avocado%>%filter(Region == "TotalUS"), aes(x = Date, y = Q_Conventional)) + 
        geom_line(color = "#F8766D") +
        ggtitle("Quantity for Conventional avocados in TotalUS") +
        geom_point(data = avocado%>%filter(Date == "2015/02/01" & Region == "TotalUS"))+
        geom_text(data = avocado%>%filter(Date == "2015/02/01" & Region == "TotalUS"), label = "Super Bowl XLIX", vjust = - 1, hjust = 0.4, size = 3)+
        geom_point(data = avocado%>%filter(Date == "2015/05/03" & Region == "TotalUS"), color = "blue")+
        geom_text(data = avocado%>%filter(Date == "2015/05/03" & Region == "TotalUS"), label = "Cinco de Mayo", vjust = - 1, hjust = 0.25, size = 3, color = "blue")+
        geom_point(data = avocado%>%filter(Date == "2016/02/07" & Region == "TotalUS"))+
        geom_text(data = avocado%>%filter(Date == "2016/02/07" & Region == "TotalUS"), label = "Super Bowl 50", vjust = - 0.7, hjust = 0.5, size = 3)+
        geom_point(data = avocado%>%filter(Date == "2016/05/08" & Region == "TotalUS"), color = "blue")+
        geom_text(data = avocado%>%filter(Date == "2016/05/08" & Region == "TotalUS"), label = "Cinco de Mayo", vjust = - 1, hjust = 0.25, size = 3, color = "blue")+
        geom_point(data = avocado%>%filter(Date == "2017/02/05" & Region == "TotalUS"))+
        geom_text(data = avocado%>%filter(Date == "2017/02/05" & Region == "TotalUS"), label = "Super Bowl LI", vjust = - 0.7, hjust = 0.7, size = 3)+
        geom_point(data = avocado%>%filter(Date == "2017/05/07" & Region == "TotalUS"), color = "blue")+
        geom_text(data = avocado%>%filter(Date == "2017/05/07" & Region == "TotalUS"), label = "Cinco de Mayo", vjust = - 1, hjust = 0.25, size = 3, color = "blue")+
        geom_text(data = avocado%>%filter(Date == "2018/02/04" & Region == "TotalUS"), label = "Super Bowl LII", vjust = - 0.7, hjust = 0.7, size = 3)+
        geom_point(data = avocado%>%filter(Date == "2018/02/04" & Region == "TotalUS"))+
        ylab("Quantity (lbs)") + 
        scale_y_continuous(labels = scales::comma) +
        stat_smooth(method = "lm") +
        theme_minimal()
## `geom_smooth()` using formula 'y ~ x'

There is no pronounced “Super Bowl effect” for Organic avocado sales, which is in line with expectation, as the consumers are mostly individuals and additionally organic avocados have significant markup compared to conventional avocados.

Variations across regions/cities:

df %>%
  filter(region %in% c("Seattle", "SanFrancisco", "Pittsburgh")) %>%
  filter(type == "organic") %>%
  ggplot(aes(x = Date, y = AveragePrice, color = region)) +
  geom_line()

As we see there is huge difference in prices across cities.

Following code contains a lot of other charts for prices and quantities for organic and conventional avocados across regions and time. We have not included them in report (include = FALSE) to avoid confusion. Please feel free to include them for further visual details.

Descriptive plots for demand model

As we want to infer the relationship between price and quantity(demand), we plot a log transformed demand model with log(P_Organic) and log(Q_Organic). After transforming, we observe a linear relationship between price and quantity, as see below.

ggplot(data = avocado_without_totalUS, aes(x = log(P_Organic), y = log(Q_Organic)))+
  geom_point(color = "#00BFC4") +
  geom_smooth(method = "lm") +
  ggtitle("Demand Curve for Organic avocados") +
  theme_minimal()
## `geom_smooth()` using formula 'y ~ x'

ggplot(data = avocado_without_totalUS, aes(x = log(P_Conventional), y = log(Q_Conventional)))+
  geom_point(color = "#F8766D") +
  geom_smooth(method = "lm") +
  ggtitle("Demand Curve for Conventional avocados") +
  theme_minimal()
## `geom_smooth()` using formula 'y ~ x'

The linear relationship makes a multiplicative demand model suitable to see the effect of price on demand.

Variation in price:

ggplot(data = df%>%filter(region %in% RegionVector), aes(x = Date, y = AveragePrice, color = type)) +
  geom_line() +
  facet_grid(.~type) +
  theme_minimal() +
  theme(legend.position = "bottom") +
  ggtitle("Comparing Price Variations for Organic and Conventional avocados")

Lets now plot the variation in the price gap of organic and conventional avocados:

Mean_P_Organic <- mean(avocado_without_totalUS$P_Organic)
Mean_P_Organic
## [1] 1.609393
Mean_P_Conventional <- mean(avocado_without_totalUS$P_Conventional)
Mean_P_Conventional
## [1] 1.127744
median(avocado_without_totalUS$P_Organic)
## [1] 1.6
median(avocado_without_totalUS$P_Conventional)
## [1] 1.1
sd(avocado_without_totalUS$P_Organic)
## [1] 0.2719577
sd(avocado_without_totalUS$P_Conventional)
## [1] 0.2335419
Mean_Q_Organic <- mean(avocado_without_totalUS$Q_Organic)
Mean_Q_Organic
## [1] 120945.7
Mean_Q_Conventional <- mean(avocado_without_totalUS$Q_Conventional)
Mean_Q_Conventional
## [1] 4216880
# Price Gap
mean(avocado_without_totalUS$P_Organic - avocado_without_totalUS$P_Conventional)
## [1] 0.4816494
median(avocado_without_totalUS$P_Organic - avocado_without_totalUS$P_Conventional)
## [1] 0.49
sd(avocado_without_totalUS$P_Organic - avocado_without_totalUS$P_Conventional)
## [1] 0.2230205
# Histogram of Price Gap
ggplot(data = avocado_without_totalUS, aes(x = P_Organic - P_Conventional)) +
  geom_histogram() +
  xlab("Price Gap")
## `stat_bin()` using `bins = 30`. Pick better value with `binwidth`.

There is enough variation in prices across time and regions/cities to estimate cross price elasticities between Organic and Conventional Avocados using a multiplicative demand model.

========================================================================================================

d) In-depth Analysis

Model Building

We want to examine the price elasticity of demand for conventional and organic avocados for the eight regions, and compare how one affects the other. For this using the log linear (multiplicative) demand model is appropriate to check the own price elasticity and cross price elasticity of demand.

I. The first model is a simple demand model with Q_Organic as the demand variable and prices of organic and conventional as independent variables

\[ log(QOrganic) = a + b11 \cdot \textrm{log}(POrganic) + b12 \cdot \textrm{log}(PConventional)+ e \] Now, we use the same demand model with Q_Conventional as the demand variable.

\[ log(QConventional) = a + b21 \cdot \textrm{log}(POrganic) + b22 \cdot \textrm{log}(PConventional)+ e \]

Model1 <- lm(log(Q_Organic) ~ log(P_Organic) + log(P_Conventional), data = avocado_without_totalUS)
Model2 <- lm(log(Q_Conventional) ~ log(P_Organic) + log(P_Conventional), data = avocado_without_totalUS)

On running the regression, we see a = 12.21, b11 = -1.69 and b12 = 0.92 as the estimates for organic demand.

As per this model, the own price elasticity is high and the cross price elasticity with conventional avocados is also high, therefore, as per this basic model the demand for organic avocados is quite elastic.

As an additional side note, we see that R-squared value is 0.14, we need to consider this when comparing other models to this simple model.

Now looking at regression model for conventional demand, we see a = 15.28, b21 = -0.014 and b22 = -1.104

As per this model, the own price elasticity is high, though the cross price elasticity is low, therefore, as per this model conventional avocados have elastic demand, but, organic prices do not affect their demand.

There are other confounding variables that need to be considered before concluding which model is the best fit. In this current model, there is no controlling for regions. We are doing this analysis at a regional level, so that could be an important variable to assess demand.

II. Controlling for regions

As the model is being assessed at regional level, there would be variations in the consumption of avocados between different regions. Now we add region to the model. As it is a categorical variable we add it as factor(region).

\[ log(QOrganic) = a + b11 \cdot \textrm{log}(POrganic) + b12 \cdot \textrm{log}(PConventional)+ factor(Region) + e \]

\[ log(QConventional) = a + b21 \cdot \textrm{log}(POrganic) + b22 \cdot \textrm{log}(PConventional)+ factor(region) + e \]

Model5 <- lm(log(Q_Organic) ~ log(P_Organic) + log(P_Conventional) + factor(Region), data = avocado_without_totalUS)
Model6 <- lm(log(Q_Conventional) ~ log(P_Organic) + log(P_Conventional) + factor(Region), data = avocado_without_totalUS)

On running the new regression models for both Organic & conventional demand, we see the below:

a = 12.72, b11 = -1.9 and b12 = 1.49 as the estimates for organic demand.

a = 15.69, b21 = -0.12 and b22 = -0.62 as the estimates for conventional demand.

Now, we see that for organic demand, the own price and cross price elasticities have increased when controlling for region. This means that region has an affect on the demand for organic avocados and differs between regions. This ties into what is the expected effect of geography on demand. We see a large lift in R-squared in this model to 0.71.

For conventional demand, the own price and cross price elasticities have decreased further when controlling for region. The low own price elasticity seems to suggest that the demand is relatively inelastic to own price changes in conventional. This would also mean that region has no affect on the demand for conventional avocados. This seems to suggest geography has no affect on demand. Though the R-squared has improved to 0.86 with this model. We need to consider that there could be omitted variable bias in this model despite the lift in R-squared.

We have not controlled seasonality in the model. There could be an affect of seasonal variation on demand.

III Controlling for Region & month (Seasonality)

To look for seasonality we need to see if there is affect of time of year(i.e. month) on demand. We include month as a factor as it is seasonality we are controlling for.

\[ log(QOrganic) = a + b11 \cdot \textrm{log}(POrganic) + b12 \cdot \textrm{log}(PConventional)+ factor(Region)+ factor(month) + e \]

\[ log(QConventional) = a + b21 \cdot \textrm{log}(POrganic) + b22 \cdot \textrm{log}(PConventional)+ factor(region) + factor(month) + e \]

# Extract month from Date
avocado_without_totalUS$month <- month(avocado_without_totalUS$Date)
avocado_without_totalUS
## # A tibble: 1,352 x 7
##    Date       P_Organic Q_Organic P_Conventional Q_Conventional Region     month
##    <date>         <dbl>     <dbl>          <dbl>          <dbl> <chr>      <dbl>
##  1 2015-01-04      1.24   142350.           0.93       5777335. California     1
##  2 2015-01-04      1.54    61615.           1.02       3382800. GreatLakes     1
##  3 2015-01-04      1.56    58065.           1.1        2578275. Midsouth       1
##  4 2015-01-04      1.88    48280.           1.09       3759283. Northeast      1
##  5 2015-01-04      1.69    34190.           1.01       1683795. Plains         1
##  6 2015-01-04      1.35    53495.           0.77       5144267. SouthCent…     1
##  7 2015-01-04      1.75    27366.           0.98       3204112. Southeast      1
##  8 2015-01-04      1.4    187548.           0.89       5794411. West           1
##  9 2015-01-11      1.1    158111.           0.92       6024932. California     1
## 10 2015-01-11      1.69    58062.           1.1        3067638. GreatLakes     1
## # … with 1,342 more rows

Use Month instead of Date

Model9 <- lm(log(Q_Organic) ~ log(P_Organic) + log(P_Conventional) + factor(Region) + factor(month), data = avocado_without_totalUS)
Model10 <- lm(log(Q_Conventional) ~ log(P_Organic) + log(P_Conventional) + factor(Region) + factor(month), data = avocado_without_totalUS)

On running the new regression models for both Organic & conventional demand, we see the below:

b11 = -1.77 and b12 = 1.73 as the estimates for organic demand.

b21 = -0.04 and b22 = -0.5 as the estimates for conventional demand.

Upon controlling for month, for organic demand the own price elasticity has decreased slightly and the cross price elasticity has increased to 1.73. This seems to suggest that when factoring seasonality, conventional prices on have an even larger affect on organic produce demand.

However, for conventional demand we see a further decrease in own price elasticity to -0.5 and the cross price elasticity is negligible. We will need to examine if it reasonable to assume that changing own prices will have such little impact on demand.

We need to check if there is an affect of year on demand, whether it is changing significantly across years and not just month.

IV Controlling for year (Time trend)

We want to check if there are variations across years that we are not capturing in our model. Therefore, we use year as a factor and add to the model as below

\[ log(QOrganic) = a + b11 \cdot \textrm{log}(POrganic) + b12 \cdot \textrm{log}(PConventional)+ factor(Region)+ factor(month) + factor(year) e \]

\[ log(QConventional) = a + b21 \cdot \textrm{log}(POrganic) + b22 \cdot \textrm{log}(PConventional)+ factor(region) + factor(month) + factor(year) + e \]

# Extract year from Date
avocado_without_totalUS$year <- year(avocado_without_totalUS$Date)
avocado_without_totalUS
## # A tibble: 1,352 x 8
##    Date       P_Organic Q_Organic P_Conventional Q_Conventional Region month
##    <date>         <dbl>     <dbl>          <dbl>          <dbl> <chr>  <dbl>
##  1 2015-01-04      1.24   142350.           0.93       5777335. Calif…     1
##  2 2015-01-04      1.54    61615.           1.02       3382800. Great…     1
##  3 2015-01-04      1.56    58065.           1.1        2578275. Midso…     1
##  4 2015-01-04      1.88    48280.           1.09       3759283. North…     1
##  5 2015-01-04      1.69    34190.           1.01       1683795. Plains     1
##  6 2015-01-04      1.35    53495.           0.77       5144267. South…     1
##  7 2015-01-04      1.75    27366.           0.98       3204112. South…     1
##  8 2015-01-04      1.4    187548.           0.89       5794411. West       1
##  9 2015-01-11      1.1    158111.           0.92       6024932. Calif…     1
## 10 2015-01-11      1.69    58062.           1.1        3067638. Great…     1
## # … with 1,342 more rows, and 1 more variable: year <dbl>
Model11 <- lm(log(Q_Organic) ~ log(P_Organic) + log(P_Conventional) + factor(Region) + factor(month) + factor(year), data = avocado_without_totalUS)
Model12 <- lm(log(Q_Conventional) ~ log(P_Organic) + log(P_Conventional) + factor(Region) + factor(month) + factor(year), data = avocado_without_totalUS)
Model13 <- lm(log(Q_Organic) ~ log(P_Organic) + log(P_Conventional) + factor(Region) + factor(month) + factor(year), data = avocado_without_totalUS%>%filter(year %in% c(2016, 2017)))
Model14 <- lm(log(Q_Conventional) ~ log(P_Organic) + log(P_Conventional) + factor(Region) + factor(month) + factor(year), data = avocado_without_totalUS%>%filter(year %in% c(2016, 2017)))

On running the new regression models for both Organic & conventional demand, we see the below:

b11 = -1.52 and b12 = 0.59 as the estimates for organic demand.

b21 = -0.05 and b22 = -0.99 as the estimates for conventional demand.

Upon adding year, in the organic demand model, we see a dramatic change in cross price elasticity with conventional, it has reduced 0.59. This seems to suggest that when controlling for variations across years, while there is still a sizable affect of conventional price on organic demand, it is not as high as previously estimated in earlier model. Organic demand is affect by both organic prices and conventional prices, when the prices of organic avocado produce go up the consumers simply stop buying avocados.

For conventional demand model, we see a significant increase in own price elasticity when we controlled for year. The cross price elasticity is low as we observed consistently across models. This model suggests that increasing the price of conventional avocados will only moderately impact sales and prices of organic avocados have no affect on conventional sales.

We choose the 6th Model as our final model because all the estimates for elasticities are significant.

Summary of the seven models we ran in the analysis:

Model 1: Just Prices
Model 2: Prices + Regions
Model 3: Prices + Seasonality
Model 4: Prices + Regions + Time trend
Model 5: Prices + Regions + Seasonality
Model 6: Prices + Regions + Seasonality + Time trend
Model 7: Prices + Regions + Seasonality + Time trend (only for 2016 and 2017)

Model gamma11 gamma12 gamma21 gamma22
1st -1.69 0.92 -0.01 -1.1
2nd -1.33 0.23 0.15 1.42
3rd -1.9 1.49 -0.12 -0.62
4th -1.59 0.6 -0.009* -0.95
5th -1.77 1.73 -0.04* -0.5
6th -1.52 0.59 0.05 -0.99
7th -1.39 0.24 0.07 -1.04
  • represents insignificant values

We choose the 6th Model as our final model because all the estimates for elasticities are significant.

# Using coefficients from the 6th Model
gamma11 <- summary(Model11)$coefficients[2,1]
gamma12 <- summary(Model11)$coefficients[3,1]
gamma21 <- summary(Model12)$coefficients[2,1]
gamma22 <- summary(Model12)$coefficients[3,1]
gamma11
## [1] -1.523275
gamma12
## [1] 0.5855533
gamma21
## [1] 0.05247006
gamma22
## [1] -0.9972395

From the coefficients, we can see that the own-price elasticity for Organic avocados is -1.5, meaning that Organic avocado buyers are sensitive to price changes. However, they don’t usually switch to buying Conventional avocados because the price of Organic avocados go up. The own-price elasticity for Conventional avocados is -1, suggesting that Conventional avocado buyer are also sensitive to price changes. Moreover, they barely switch to buying Organic avocados since the cross-price elasticity is 0.05.

Profitability Analysis

Cost Assumptions

  1. Production Cost for Conventional avocados are $0.23/lb while production cost for Organic avocados are $0.37/lb.
  2. Farmers will mark up $0.15/lb for all avocados.
  3. Logistic Cost is 26% of Production Cost.
  4. Wastage is 12% at retail level.
  5. Operational cost 20% of sales.

Cost assumption links: https://edis.ifas.ufl.edu/pdffiles/FE/FE83700.pdf
http://publications.dyson.cornell.edu/docs/smartMarketing/pdfs/SmrtMkgMar2016-IV.pdf
https://www.zestlabs.com/solving-problem-fresh-food-waste/

Cost Conventional Organic
Production $ 0.23 $ 0.37
Retail Margin $ 0.15 $ 0.15
Logistics $ 0.099 $ 0.14
Wastage $ 0.057 $ 0.08
Operation $ 0.23 $ 0.32
Total $ 0.76 $ 1.01 
# Logistics Cost for Conventional Avocados
(0.23+0.15) * 0.26
## [1] 0.0988
# Wastage Cost for Conventional Avocados
(0.23 + 0.15 + (0.23+0.15) * 0.26) * 0.12
## [1] 0.057456
# Logistics Cost for Organic Avocados
(0.37+0.15) * 0.26
## [1] 0.1352
# Wastage Cost for Organic Avocados
(0.37 + 0.15 + (0.37+0.15) * 0.26) * 0.12
## [1] 0.078624
# Total Cost for Conventional Avocados
Cost_Conventional <- 0.23 + 0.15 + (0.23+0.15) * 0.26 + (0.23 + 0.15 + (0.23+0.15) * 0.26) * 0.12 + 0.2 * Mean_P_Conventional
Cost_Conventional
## [1] 0.7618048
# Total Cost for Organic Avocados
Cost_Organic <- 0.37 + 0.15 + (0.37+0.15) * 0.26 + (0.37 + 0.15 + (0.37+0.15) * 0.26) * 0.12 + 0.2 * Mean_P_Organic
Cost_Organic
## [1] 1.055703

The profit function:

CalculateProfit <- function(Q, P, C, r=0) {
  profit = Q * (P * (1-r) - C)
  return(sum(profit))
}

Current scenario average profit:

Mean_Profit_Conventional <- CalculateProfit(Mean_Q_Conventional, Mean_P_Conventional, Cost_Conventional)
Mean_Profit_Conventional 
## [1] 1543122
Mean_Profit_Organic <- CalculateProfit(Mean_Q_Organic, Mean_P_Organic, Cost_Organic)
Mean_Profit_Organic
## [1] 66966.52
Mean_Total_Profit <- Mean_Profit_Conventional + Mean_Profit_Organic
Mean_Total_Profit
## [1] 1610088

Scenario Analysis

CalculateProfitChange <- function(dO, dC, ReturnValue = TRUE) {
  Ratio_O <- (1+dO)^gamma11*(1+dC)^gamma12
  Ratio_C <- (1+dO)^gamma21*(1+dC)^gamma22
  New_Q_O <- Mean_Q_Organic * Ratio_O
  New_Q_C <- Mean_Q_Conventional * Ratio_C
  New_P_O <- Mean_P_Organic * (1+dO)
  New_P_C <- Mean_P_Conventional * (1+dC)
  New_Profit_O <- CalculateProfit(New_Q_O, New_P_O, Cost_Organic)
  New_Profit_C <- CalculateProfit(New_Q_C, New_P_C, Cost_Conventional)
  New_Total_Profit <- New_Profit_O + New_Profit_C
  if (ReturnValue == TRUE) {
    return(New_Total_Profit)
  } else if (ReturnValue == FALSE)  {
    return(New_Total_Profit > Mean_Total_Profit)
  } else if (ReturnValue == "All") {
    return((New_Total_Profit - Mean_Total_Profit)/Mean_Total_Profit)
  }
}

Calculating profits at different scenarios:

round(CalculateProfitChange(0.1, 0.1, "All"), 3)
## [1] 0.195
round(CalculateProfitChange(-0.1, -0.1, "All"), 3)
## [1] -0.235
round(CalculateProfitChange(0.1, -0.1, "All"), 3)
## [1] -0.216
round(CalculateProfitChange(-0.1, 0.1, "All"), 4)
## [1] 0.1704
round(CalculateProfitChange(0.1, 0, "All"), 3)
## [1] 0.01
round(CalculateProfitChange(0, 0.1, "All"), 3)
## [1] 0.184
round(CalculateProfitChange(-0.1, 0, "All"), 3)
## [1] -0.012
round(CalculateProfitChange(0, -0.1, "All"), 3)
## [1] -0.224
round(CalculateProfitChange(0, 0, "All"), 3)
## [1] 0
Scenario Analysis C: - 10% C: + 0% C: + 10%
O: - 10% -23.5% -1.2% 17.0%
O: + 0% -22.4% 0% 18.4%
O: + 10% -21.6% 1.0% 19.5%

e) Summary and Recommendations

Price Elasticities:

Own Price elasticity ( Conventional Avocados): 0.99
Inelastic demand indicating a relatively stable product. No need to drop prices, focus on brand building.

Cross Price elasticity (demand for Conventional influenced by price of Organic): 0.05
Conventional demand is not affected by Organic prices. Organic as a trend has a long

Own Price elasticity ( Organic Avocados): 1.52
Elastic demand indicating strong sensitivity to price changes

Cross Price elasticity (demand for Organic influenced by price of Conventional): 0.59
Organic Avocado demand is influenced by Conventional prices. Drop in conventional prices will cause cannibalization. Double blow effect of drop in prices of conventional avocados.

Looking at the profitability table, we suggest rising the prices of conventional avocados by 10% (10% rise is within observable variation) and keeping the prices of organic constant. The profit difference is very small compared to this scenario if both prices are increases by 10%. Since organic produce has elastic own price elasticities, keeping the price same makes sense. Dropping the prices of conventional avocados is not a good idea.

Other observations:
Prices and sales vary greatly through seasons and location.
Organic produce is still a long way from being mainstream.
Organic produce is very sensitive to own and cross prices despite a consistent rise in demand.
Conventional produce has stable demand so there is some room to increase prices. This will also benefit Organic demand which has better profit margin.

The analysis assumed that since 80% of avocado sales are Hass variety, the model is a fair approximation. We only incorporated Large size avocados as they account for majority of sales. The cost assumptions are based on the fact that more than 80% avocados are produced outside US.