After working through this case study, you will be able to
generate daily demand time series per item from receipt data,
derive the parameters of the newsvendor model (price, costs, salvage value, demand distribution) from real data and plausible assumptions,
determine the optimal production quantity using the normal distribution and the empirical distribution,
take demand segments (weekday, season) into account and compare different planning rules in a backtest,
recognise the problem of censored demand (sell-outs), and
weigh economic and ecological objectives (food waste) against each other.
2 Initial situation
A family-run bakery in France bakes baguettes, croissants, pains au chocolat and other baked goods every morning. The goods are sold on the same day; whatever is left over in the evening can no longer be sold at the regular price. It is donated, given away as animal feed or offered cheaply via an app against food waste. If, on the other hand, the goods sell out early, sales are lost and regular customers go to the competition.
So far, the owner has set the baking quantities based on experience, usually as a fixed quantity per weekday. She would like to know whether data-based planning can increase profit and at the same time reduce the amount of waste. For this purpose, she provides her point-of-sale data from January 2021 to September 2022.
The file Bakery sales.csv contains around 234,000 sales line items from 1 Jan 2021 to 30 Sep 2022 with the following columns:
Column
Meaning
date, time
Date and time of sale
ticket_number
Receipt number (one receipt can contain several line items)
article
Item name (e.g. TRADITIONAL BAGUETTE, CROISSANT, PAIN AU CHOCOLAT, BAGUETTE, COUPE, BANETTE)
Quantity
Quantity (note: also negative values, i.e. cancellations and returns)
unit_price
Unit price as text, e.g. "1,20 €" (remove decimal comma and currency symbol)
ImportantNotes on data preparation
Clean unit_price and check items with empty or placeholder names.
Handle negative quantities in a justified way (e.g. netting against the sale on the same receipt).
Check on which days the bakery is closed (rest days, holidays). Such days do not represent a demand of 0.
Prices change over time (inflation in 2022). For each period, use the price valid at that time.
3.2 Cost assumptions
The bakery has estimated the variable production costs (raw materials, energy, variable labour share) as follows:
Item
Variable costs 2021 [€/unit]
Variable costs 2022 [€/unit]
Salvage value [€/unit]
TRADITIONAL BAGUETTE
0.40
0.48
0.10 (breadcrumbs, animal feed)
CROISSANT
0.38
0.45
0.30 (sale via app in the evening)
PAIN AU CHOCOLAT
0.42
0.50
0.30 (sale via app in the evening)
Take the selling prices from the data. For one further item of your choice, make your own justified assumptions.
4 Tasks
4.1 Part A – From receipts to a demand time series
A1. Prepare the data and generate a daily sales time series for the three items in the cost table. Document all cleaning steps.
A2. Analyse weekday effects, seasonality and trend, and examine the distribution of daily demand (histogram, QQ plot). Which segmentation, e.g. by weekday, do you consider sensible, and is the normal distribution assumption justified?
4.2 Part B – Classic newsvendor model
B1. For the TRADITIONAL BAGUETTE, using the 2021 data, determine the underage cost \(c^u\), the overage cost \(c^o\) and the critical ratio \(CR = c^u/(c^u+c^o)\).
B2. Calculate the optimal baking quantity \(q^*\) and the expected profit (a) assuming a normal distribution for all days together (newsvendor_normal()), (b) assuming a normal distribution per weekday and (c) using the empirical CR quantile per weekday.
B3. Repeat the calculation for the croissant. Why does the optimal service level differ so strongly from that of the baguette?
4.3 Part C – Backtest
C1. Calibrate the following planning rules with the 2021 data and test them on January to September 2022:
Rule of thumb: fixed quantity per weekday = 2021 mean + 10%,
Newsvendor with normal distribution per weekday (B2b),
Newsvendor with empirical distribution per weekday (B2c).
For each rule, calculate the realised profit, the shortages, the amount of waste and the α service level achieved. Optional: develop your own, better rule.
4.4 Part D – Recommendation and reflection
D1. Censored demand. The point-of-sale data show sales, not demand. Develop a simple indicator for sell-out days (e.g. time of the last sale) and estimate how often censoring occurs and in which direction it biases your \(q^*\).
D2. Sustainability. Determine the profit-maximising baking quantity if at most 5% of production may be left over, and quantify the profit forgone compared with the unconstrained optimum.
D3. Recommendation. Formulate a practical recommendation for the owner, e.g. as a table “baking quantity per weekday”.
D4. Reflection. Answer the following questions in your presentation. They form the starting point for the subsequent discussion:
Why does the newsvendor model perform better or worse than the rule of thumb in the backtest? What knowledge is contained in the owner’s “experience” that the model does not know?
The costs and salvage values are assumptions and do not come from the data. How strongly does your recommendation depend on them?
What does it mean for data-driven decisions in general that often only what was sold is observed, and not what was demanded?
Profit or less food waste: How should a company decide this trade-off, and who should bear the “costs of sustainability”?
Would your results be transferable to a bakery in another country, for example with a broader range and different shopping habits?
5 Appendix: R starter code
Code
library(tidyverse)source("../R/scm_functions.R") # newsvendor_normal()raw <-read_csv("Bakery sales.csv")bakery <- raw |>mutate(date =as.Date(date),unit_price =parse_number(unit_price, locale =locale(decimal_mark =",")),article =str_trim(article) )tagesabsatz <- bakery |>filter(article %in%c("TRADITIONAL BAGUETTE", "CROISSANT", "PAIN AU CHOCOLAT")) |>group_by(date, article) |>summarise(menge =sum(Quantity), preis =median(unit_price), .groups ="drop") |>mutate(wochentag = lubridate::wday(date, label =TRUE, week_start =1),jahr = lubridate::year(date))# Example: newsvendor for the baguette 2021 (all days)b21 <- tagesabsatz |>filter(article =="TRADITIONAL BAGUETTE", jahr ==2021)newsvendor_normal(mu =mean(b21$menge), sigma =sd(b21$menge),p =median(b21$preis), c =0.40, s =0.10)# Empirical quantileCR <- (median(b21$preis) -0.40) / (median(b21$preis) -0.10)quantile(b21$menge, CR, type =1)# Realised profit of a quantity q given demand Dgewinn <-function(q, D, p, c, s) p *pmin(q, D) + s *pmax(q - D, 0) - c * q