Skip to content
pt

Data analysis case · Beverage warehouse

8M cases of beer and kegs moved over 27 months (Jun/17 to Aug/26) by a real public warehouse. On top of that volume, a simulated and calibrated damage layer shows which reasons, stages and packaging explain the loss, and what to fix first.

Simulated damage · real volumes

Code on GitHub

One-page summary →

Estimated loss per month
R$210.2K
Of cases moved
0.84%
Damaged cases in the period
67,426
Items analyzed
9,708

One-minute summary

3 findings, 3 actions

  • 46%

    of the loss comes from 3 combinations

    Handling breakage at picking, pallet fall at loading and handling breakage at loading. Across simulations it ranges 43%–50%; it depends mostly on how much damage happens at picking.

  • 80%

    of the cost is long neck

    Glass is 51% of the volume and is damaged 2.4× as often as cans. The most robust finding: 76%–84% across every assumption range.

  • +46%

    at picking, mix-adjusted

    The raw rate shows +31% and makes the other stages look better, but that is just glass being replaced by cans (adjusted: −1%). The simulation plants +45%, and the method recovers it.

01Real data

Three facts come straight from Montgomery, with no assumptions. The key one: cans went from 38% to 54% of the volume between 2017 and 2026, which changes how any damage rate should be read.

Packaging mix by year

% of cases moved in each year with data

  • Long neck (glass)
  • Can
  • Keg
  • Other
2017
53%
38%
2018
53%
39%
2019
52%
41%
2020
49%
45%
2026
41%
54%

Volume concentration

2.5%

234 of 9,537 items (class A) make up 80% of cases moved.

Reversals by package

Reversals are rare (0.013% of cases), but kegs show them in 26 of 27 months.

  • Keg
    0.152%26 months with reversals
  • Long neck (glass)
    0.015%23 months with reversals
  • Can
    0.008%19 months with reversals
  • Other
    0.003%9 months with reversals

As the mix shifts to cans, which break less, the raw damage rate falls on its own. Any damage target must be mix-adjusted.

02Loss Pareto

Each bar is one reason at one stage, sorted by cost. 9 combinations add up to 80% of the loss; the largest, handling breakage at picking, accounts for 27% on its own.

Damage cost by reason × stage

Label: % of cost · cumulative %. The dashed line marks the 80% cut-off.

Handling breakage at picking26.8% · 27%Pallet fall at loading10.0% · 37%Handling breakage at loading9.0% · 46%Pallet fall at picking8.1% · 54%Over-stacking in storage7.8% · 62%Inbound transit damage at the dock7.5% · 69%Forklift collision in storage5.6% · 75%Crushed packaging at picking4.8% · 80%Expiry in storage3.7% · 83%Pallet fall at the dock3.6% · 87%Forklift collision at picking3.1% · 90%Crushed packaging at loading2.6% · 93%Leak at loading2.2% · 95%Leak in storage2.0% · 97%Crushed packaging at the dock1.5% · 99%Leak at the dock1.5% · 100%

Handling breakage at picking comes before anything else: it is the largest single item and it is growing.

03Packaging × stage

Long neck is 51% of cases moved and 80% of the damage cost. Picking is the most expensive stage for every package type.

Volume × cost by package

% of cases moved vs % of damage cost

Long neck (glass)

Volume
51%
Cost
80%

Can

Volume
42%
Cost
14%

Keg

Volume
2%
Cost
2%

Other

Volume
6%
Cost
4%

Damage rate by package and stage

% of that package's moved cases damaged at each stage

DockStoragePickingLoading
Long neck (glass)0.17%R$644K0.22%R$863.8K0.50%R$1.9M0.28%R$1.1M
Can0.07%R$117.9K0.10%R$153.6K0.21%R$338.7K0.12%R$185.9K
Keg0.02%R$8.8K0.04%R$22.3K0.10%R$54.2K0.05%R$26.5K
Other0.11%R$36.3K0.15%R$46.6K0.30%R$97.9K0.17%R$53.8K

Glass-specific pallet protection and handling (corner boards, extra stretch film, plastic crates for split cases) target 80% of the cost.

04Monthly cost

Loss follows the volume moved. The source has long gaps (2018, 2020 to 2026): the chart keeps each continuous block separate and never connects distant months.

Damage cost per month

BRL per month · dashed lines mark months missing from the source

R$0R$100KR$200KR$300KJun/17Jan/19Jun/26

With the rate steady near 0.84%, monthly cost is volume × rate × price: lowering the rate is the only lever that does not mean selling less.

05Trend

The raw rate shows picking at +31% and the other stages falling. But the mix shifted to cans: holding the first window's mix fixed, picking rises +46% and the other stages stay flat. The increase was planted in the simulation (+45%) to test the method, and it recovers it.

Damage rate by stage, per window

Change vs the first window (W1 = 0%)

  • Picking
  • Dock · Storage · Loading
−20%0%+20%+40%+60%W1 · Jun/17–Feb/18W2 · Jan–Nov/19W3 · 2020 (4 months)W4 · Jun–Aug/26Dock −3%Storage +3%Loading −3%Picking +46%

Track the mix-adjusted rate: the raw one mistakes a packaging shift for a process improvement.

06ABC curve

234 items (class A) make up 80% of the volume and 80% of the damage cost. Damage tracking turnover is a simulation assumption; the curve's value is telling you where to start.

Volume × damage cost by ABC class

A: 80% of volume · B: up to 95% · C: the rest

A234 items · 0.84%

Volume
80%
Cost
80%

B588 items · 0.86%

Volume
15%
Cost
16%

C8,715 items · 0.73%

Volume
5%
Cost
5%

The 10 items with the highest damage cost

Cost in the period and main reason

  1. 01

    CORONA EXTRA LOOSE NR - 12OZ

    Long neck (glass)· Handling breakage

    R$323.7K

    Rate 0.93%

  2. 02

    CORONA EXTRA 2/12 NR - 12OZ

    Long neck (glass)· Handling breakage

    R$323K

    Rate 1.20%

  3. 03

    HEINEKEN 2/12 NR - 12OZ

    Long neck (glass)· Handling breakage

    R$273K

    Rate 1.63%

  4. 04

    MODELO ESPECIAL 24 LOOSE NR - 12OZ

    Long neck (glass)· Handling breakage

    R$199.4K

    Rate 1.44%

  5. 05

    HEINEKEN LOOSE NR - 12OZ

    Long neck (glass)· Handling breakage

    R$169.9K

    Rate 0.87%

  6. 06

    CORONA EXTRA 18PK NR - 12OZ

    Long neck (glass)· Handling breakage

    R$153.5K

    Rate 1.47%

  7. 07

    HEINEKEN 4/6 NR - 12OZ

    Long neck (glass)· Handling breakage

    R$152.8K

    Rate 1.41%

  8. 08

    STELLA ARTOIS 2/12 NR - 11.2OZ

    Long neck (glass)· Handling breakage

    R$108.5K

    Rate 1.39%

  9. 09

    FLYING DOG THE TRUTH 4/6NR - 12OZ

    Long neck (glass)· Handling breakage

    R$86.5K

    Rate 2.84%

  10. 10

    CORONA EXTRA 4/6 NR - 12OZ

    Long neck (glass)· Handling breakage

    R$85.8K

    Rate 0.62%

Priority for protection and cycle counting: start with class A glass items, where volume and fragility add up.

07Robustness

120 simulations with assumptions drawn from plausible ranges: glass damaged 1.4× to 2.4× the base rate, picking taking 30% to 50% of the damage and a 0% to 60% increase planted in it. Each bar is a finding's p5–p95 range; the dot is the v1 value.

Range of each finding across simulations

Bar: assumptions varying (p5–p95) · dashed band: no planted increase

  • Assumptions varying
  • No planted increase
  • v1 value
40%50%60%70%80%90%Long neck (% of cost)76% a 84%Top 3 combinations (% of cost)43% a 50%
−20%0%+20%+40%+60%Picking, mix-adjusted (change)+2% a +53%Picking, raw rate (change)−8% a +40%Other stages, adjusted (mean change)−5% a +4%

What each finding depends on

Rank correlation between the drawn assumption and the finding (−1 to 1)

How much more glass breaksShare of damage at pickingIncrease planted at picking
Long neck (% of cost)+0.89−0.14+0.14
Top 3 combinations (% of cost)+0.18+0.92+0.29
Picking, mix-adjusted (change)+0.12+0.08+0.97
Picking, raw rate (change)0.00+0.08+0.96
Other stages, adjusted (mean change)−0.05−0.14+0.09

Robust: long neck ≈ 80% of cost. Assumption-driven: the top 3 and the size of the increase. The trend method recovers the planted increase with a median error of -1.3 p.p.

08Simulator

Pick the stage, the package and how much to cut. The baseline is the period's monthly average of damaged cases; prices per case are assumptions and can be edited.

Average price per case (assumption)

Estimated savings

R$26,964per month

R$323,568 per year · 319 fewer damaged cases per month

Current loss in this slice: R$89,880 per month (1,065 cases)

Where this slice's loss is

Monthly cost by stage, with the saved share

  • Saved
  • Remaining loss
  • Dock
    R$29.9K
  • Storage
    R$40.2K
  • Picking
    R$89.9K
  • Loading
    R$50.2K

Cutting picking damage by 30% saves R$26,964 a month; the same cut across all long neck saves R$50,371.

09What to do first

Each action comes from a finding and shows its estimated savings, assuming a 30% reduction.

  1. 01

    Picking: handling and split cases

    Handling training, plastic crates for split glass cases and a review of picking pace, where the loss is growing.

    Estimated impact: R$26,964 per month

  2. 02

    Long neck: pallet protection

    Corner boards and extra stretch film on glass pallets, and no mixing of cans and glass when building loads.

    Estimated impact: R$50,371 per month

  3. 03

    Loading and storage: standards

    A load-securing standard at loading and a stacking limit marked at each location, against pallet falls and over-stacking.

    Estimated impact: R$26,874 per month on the top 3 combinations

  4. 04

    Metric: mix-adjusted rate

    Track damage with the packaging mix held fixed. The raw rate improves on its own when sales shift to cans and hides the worsening at picking.

    Estimated impact: A target the mix cannot fool

10Method

A reproducible end-to-end pipeline, with tests and the SQL behind every chart one click away.

  1. 01

    Extraction

    Montgomery County (US): Warehouse and Retail Sales, open monthly data.

  2. 02

    Cleaning

    Beer and kegs, packaging from the description, returns kept apart.

  3. 03

    Simulation

    Damage with a fixed seed and stated assumptions, below the FAO ceiling.

  4. 04

    SQL

    SQLite with CTEs and window functions: one query per question.

  5. 05

    Site

    Versioned JSONs, static Next.js, contract tests in CI.

Decisions

  • Real volume, simulated damage

    There is no open SKU-level damage data. Volume comes from a real warehouse; damage is simulated and labeled as such.

  • Calibration

    Base rate of 0.6% of cases, multiplied by package and stage. FAO measures 13.2% food loss between harvest and retail (2021); packaged beverages lose far less.

  • Gaps respected

    The source has 27 months of data between 2017 and 2026. Trends compare continuous blocks, never distant months as if they were consecutive.

  • Prices in BRL

    The source has no prices. Each package has an average price per case, editable in the simulator.

Where each number comes from

  • Volumes, packaging mix, ABC curve, reversalsReal data
  • Average price per case (BRL)Assumption
  • Base rate, how much each package breaks, share per stage, increase at pickingAssumption
  • Damage by reason and stage, costs, Pareto, findingsDerived from simulation

Limits

  • Damage and cost figures are illustrative: they show the method, not any company's reality.
  • Packaging is inferred from the description; 9,708 items are classified automatically.
  • No company data goes into the repository or the site.

Stack

  • Python
  • pandas
  • NumPy
  • SQL
  • SQLite
  • pytest
  • ruff
  • GitHub Actions
  • Next.js
  • TypeScript
  • D3
  • Motion
  • Tailwind CSS

Reproduce

$ pip install -r requirements.txt
$ python src/extract.py && python src/transform.py
$ python src/simulate.py && python src/export_web.py
$ pytest -q
$ cd web && npm install && npm run dev

About

From the warehouse floor to data

I'm Alexandre Lopes, a Warehouse Apprentice at a beverage distributor. This project turns what I see on the floor (damage at picking, falling pallets, broken glass) into questions, SQL and actions.