Case Study 4: Risk Pooling in E-Commerce

Central or regional warehouses for a Brazilian online marketplace (Olist data)

NoteOverview

Topic	Location and product pooling, centralisation of inventories
Link to the lecture	Chapter 6 of the course reader: safety stock, service level, square-root law, influence of correlation
Type	Data-based case study with real order data (Brazilian E-Commerce Public Dataset by Olist, Kaggle)
Group size	3–6 people
Deliverable	Presentation (30 min) + discussion (15 min), slide deck (PDF) and reproducible R/Quarto code

1 Learning objectives

After working through this case study, you will be able to

• derive the key figures relevant to inventory planning (mean, forecast error, correlation) from real order data,
• calculate safety stocks for central, regional and hybrid warehouse structures,
• test the square-root law empirically and explain deviations through correlations and regions of unequal size,
• distinguish between location pooling and product pooling, and
• weigh the pooling advantage against transport costs and delivery times.

2 Initial situation

Olist is a Brazilian online marketplace through which small and medium-sized merchants offer their products on large sales platforms. So far, the merchants ship themselves, most of them from the state of São Paulo. Customers in the north and north-east of Brazil therefore often wait two to three weeks for their order and pay significantly higher freight costs.

Management is therefore considering setting up its own fulfilment network: the merchants store their goods with Olist, and Olist takes over storage and shipping. For the product categories with the highest revenue, three structures are under discussion:

• Structure Z (central): one fulfilment centre in Cajamar (greater São Paulo area) supplies all of Brazil.
• Structure R (regional): five regional warehouses, one per macro-region: Cajamar (Sudeste), Curitiba (Sul), Goiânia (Centro-Oeste), Recife (Nordeste) and Belém (Norte).
• Structure H (hybrid): two warehouses: Cajamar for Sudeste, Sul and Centro-Oeste, and Recife for Nordeste and Norte.

The Brazilian perspective makes the case particularly instructive: the regions differ greatly in size, purchasing power and accessibility, and demand is very unevenly distributed.

3 Data

3.1 Dataset

Brazilian E-Commerce Public Dataset by Olist (Kaggle, licence CC BY-NC-SA 4.0, not a competition): https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce

The dataset contains around 100,000 real, anonymised orders from 2016 to 2018. For the case study you need five of the nine tables:

File	Relevant columns
olist_orders_dataset.csv	order_id, customer_id, order_status, order_purchase_timestamp, order_delivered_customer_date
olist_order_items_dataset.csv	order_id, product_id, seller_id, price, freight_value (one row per item)
olist_customers_dataset.csv	customer_id, customer_state (state, e.g. SP, BA)
olist_products_dataset.csv	product_id, product_category_name (Portuguese)
product_category_name_translation.csv	Translation of the category names into English

The assignment of the 27 states to the five macro-regions is included in the starter code.

ImportantNotes on data preparation

• Use the number of items ordered (rows in order_items) per week, region and product category as demand. Exclude cancelled and unavailable orders.
• The data are only complete from the beginning of 2017 and end in August 2018. Restrict the analysis to the weeks from 2 Jan 2017 to 20 Aug 2018 (86 weeks).
• The business grows strongly in 2017, and there is a clear spike in November (Black Friday). Raw standard deviations are therefore not a suitable measure of uncertainty.
• Concentrate on the 10 largest product categories.

3.2 Planning parameters

Parameter	Value
Scaling up	The dataset is an extract of the business. Scale quantities up to the planned fulfilment volume using a factor of 50. Assume that the coefficient of variation of the forecast errors remains the same.
Target service level (α)	95%
Replenishment lead time merchant → warehouse	2 weeks (Recife and Belém: 3 weeks)
Value of goods	60% of the selling price (price)
Inventory holding cost rate	25% p.a. of the value of goods
Freight to the customer	Central supply: average freight_value of the respective region according to the data. Supply from a warehouse in the customer’s own region: freight level of the Sudeste region.
Replenishment of a regional warehouse	BRL 4 per item (full truckloads from São Paulo)
Fixed costs per warehouse (pro rata for the categories considered)	Cajamar: BRL 2.0 million/year, each additional warehouse: BRL 0.9 million/year

4 Tasks

4.1 Part A – Data preparation and exploration

A1. Join the tables and generate the weekly item demand per region for the 10 largest categories. Describe the distribution of demand across the regions, the trend and special effects. Also present the actual delivery times (order to delivery) and freight costs per region.

A2. For each category and region, determine the forecast errors of a simple forecast, e.g. a moving average of the last 8 weeks. Explain why the forecast error and not the raw variability is decisive for the safety stock.

4.2 Part B – Location pooling

B1. For each category, calculate the safety stock \(SB = z_\alpha\,\sigma\sqrt{L}\) in structures Z, R and H, namely (a) assuming independent regional demands (square-root law; note the regions of unequal size) and (b) using the empirical covariance matrix of the forecast errors, \(\sigma_{\text{pool}} = \sqrt{\mathbf 1^\top \Sigma\,\mathbf 1}\).

B2. Compare (a) and (b). Why is the pooling effect considerably smaller than the square-root law with \(\sqrt{5}\) promises? Distinguish between the influence of correlation and that of unequal region sizes.

B3. Compare the annual total costs of the three structures: holding costs of the safety stocks, freight to the customer, replenishment and fixed costs. Which structure is the cheapest? At what fixed costs per regional warehouse does the decision tip (break-even)?

4.3 Part C – Product pooling and selective stocking

C1. Compare the coefficient of variation of the forecast errors of a single category with that of all 10 categories combined. Use the result for selective stocking: Which categories should be stocked in Recife (structure H), and which should only be stored centrally in Cajamar? Take volume, variability, value of goods and freight costs into account.

4.4 Part D – Recommendation and reflection

D1. Recommendation. Formulate a recommendation to Olist’s management. In addition to costs, take delivery time into account as a competitive factor.

D2. Reflection. Answer the following questions in your presentation. They form the starting point for the subsequent discussion:

1. How well did the square-root law describe the real data? What do you learn from this about applying textbook formulas to real data?
2. In this case, the pooling advantage is probably small compared with freight and delivery time. What does this mean for the role of inventory arguments in network decisions?
3. Which data preparation decisions (time period, categories, forecasting method) and which assumptions (scaling factor, constant coefficient of variation) have the strongest influence on your result?
4. The data show orders, not the demand that would have arisen with shorter delivery times. How could a regional warehouse in the north-east change demand itself, and why is this effect missing from your calculation?
5. Which characteristics of a large emerging economy such as Brazil (infrastructure, taxes between states, differences in purchasing power) play a role in the location question that the pooling model does not capture?

5 Literature

• Olist (2018): Brazilian E-Commerce Public Dataset by Olist. Kaggle, https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce.
• Eppen, G. D. (1979): Effects of Centralization on Expected Costs in a Multi-Location Newsboy Problem. Management Science 25(5), 498–501.
• Course reader for the lecture, Chapter 6.

6 Appendix: R starter code

library(tidyverse)
source("../R/scm_functions.R")   # pooling_safety_stock()

pfad   <- "olist/"   # folder containing the unzipped CSV files
orders <- read_csv(paste0(pfad, "olist_orders_dataset.csv"))
items  <- read_csv(paste0(pfad, "olist_order_items_dataset.csv"))
cust   <- read_csv(paste0(pfad, "olist_customers_dataset.csv"))
prod   <- read_csv(paste0(pfad, "olist_products_dataset.csv"))
transl <- read_csv(paste0(pfad, "product_category_name_translation.csv"))
names(transl)[1] <- "product_category_name"   # removes a BOM character if present

regionen <- tibble(
  customer_state = c("AC","AP","AM","PA","RO","RR","TO",
                     "AL","BA","CE","MA","PB","PE","PI","RN","SE",
                     "DF","GO","MT","MS",
                     "ES","MG","RJ","SP",
                     "PR","RS","SC"),
  region = c(rep("Norte", 7), rep("Nordeste", 9), rep("Centro-Oeste", 4),
             rep("Sudeste", 4), rep("Sul", 3))
)

daten <- items |>
  inner_join(orders, by = "order_id") |>
  filter(!order_status %in% c("canceled", "unavailable")) |>
  inner_join(cust, by = "customer_id") |>
  left_join(prod, by = "product_id") |>
  left_join(transl, by = "product_category_name") |>
  left_join(regionen, by = "customer_state") |>
  mutate(kategorie = product_category_name_english,
         woche = floor_date(as_date(order_purchase_timestamp), "week", week_start = 1)) |>
  filter(woche >= as.Date("2017-01-02"), woche <= as.Date("2018-08-20"))

top10 <- daten |> count(kategorie, sort = TRUE) |> slice_head(n = 10) |> pull(kategorie)

wochen <- daten |>
  filter(kategorie %in% top10) |>
  count(kategorie, region, woche, name = "menge") |>
  complete(kategorie, region, woche, fill = list(menge = 0))

# Example: forecast errors (moving average over 8 weeks) for one category
fehler <- wochen |>
  filter(kategorie == "health_beauty") |>
  group_by(region) |> arrange(woche) |>
  mutate(prognose = lag(zoo::rollmeanr(menge, 8, fill = NA)),
         fehler   = menge - prognose) |>
  ungroup() |> drop_na(fehler) |>
  select(woche, region, fehler) |>
  pivot_wider(names_from = region, values_from = fehler)

Sigma <- cov(as.matrix(fehler[, -1]))
z <- qnorm(0.95); L <- 2
c(SB_regional = sum(z * sqrt(diag(Sigma)) * sqrt(L)),
  SB_zentral  = z * sqrt(sum(Sigma)) * sqrt(L))