Supply Chain Analytics · Apex Consumer Goods

1,105 orders every night.
Some of them lie.

Signal in the Noise — turning messy distribution data into decisions you can defend.

Scroll to explore

This is what the data actually looks like.

Every night, 1,105 in-transit orders flow out of SAP. The extract arrives dirty — two date formats in one column, the same status spelled three ways, phantom SKU codes, missing weights, impossible quantities. Hover over the highlighted cells to discover what's hiding in plain sight.

Problems found0/15

=QUERY(SAP_Extract, "SELECT * WHERE SAP_Status = 'In Transit'")

#	Order_ID	SKU	Description	Qty	Weight_kg	Dispatch_Date	SAP_Status	Destination
1	SO-50312	LAC-1013	Leche Entera 1L	703	8.32	2026-05-31	In Transit	CD-MER
2	SO-50624	XXX-8415	—	768	—	03-Jun-2026	EN TRANSITO	CD-TOL
3	SO-50817	XXX-8945	—	906	—	02-Jun-2026	in transit	CD-TOL
4	SO-50095	PRO-1012	Temporada Pack 6	-25	4.10	2026-06-03	In Transit	CD-CDMX
5	SO-50709	ABA-1017	Frijol 1kg	0	1.80	02-Jun-2026	In Transit	CD-SAL
6	SO-50058	BEB-1002	REFRESCO COLA 1L	1980	NULL	2026-06-03	In Transit	CD-CHI
7	SO-50776	BEB-1016	Refresco Cola 2.5L	2683	NULL	2026-06-02	Delivered	CD-LEO
8	SO-50491	PRO-1005	Temporada Pack 3	1577	11.02	31-May-2026	In Transit	CD-TIJ
9	SO-51008	LAC-1016	Yoghurt Bebible 250ml	153	10.9	21-May-2026	In Transit	CD-MOR
10	SO-50000	te helado 1l	te helado 1l	1036	12.51	2026-06-03	In Transit	CD-LEO

Showing 10 of 1,105 rowsSource: SAP_Transit_Extract_2026-06

This is 10 rows. The real extract has 1,105 — including 12 orphan codes that match no product on file. Every one of these problems — mixed dates, orphans, nulls, inconsistent text — compounds across the full dataset. Every decision downstream inherits these errors, so the cleaning isn't optional. It's the foundation.

Four stages. One pipeline.

Every night, the same four stages run in the same order. Each one is built to surface truth and discard noise — and no step is optional, because the next one inherits whatever the last one missed.

Ingest

Raw data lands

1,105 in-transit orders pulled nightly from SAP. Three plants, 15 distribution centers, 100 SKUs across 5 categories. The data arrives as-is — messy, inconsistent, and full of silent errors.

Order_IDSKUDispatch_DateWeight_kgSAP_Status

=IMPORTDATA(SAP_Extract)

Clean

Find & fix the lies

Normalize the two date formats to ISO. Quarantine orphan SKU codes that match no master entry. Impute missing weights from historical averages. Flag negatives, zeros, and nulls. The invisible work that makes everything downstream trustworthy.

Date_StdSKU_ValidWeight_CleanQty_AbsFlag

=IF(ISBLANK(Weight_kg), AVERAGE(Hist_Weight), Weight_kg)

Analyze

Detect what matters

Four exception detectors run in parallel: inefficient loads (truck < 75% full), FEFO-risk dairy orders, ghost shipments stuck in transit, and orphan codes with no master match. Statistical safety stock at 95% service level (Z = 1.65).

Exception_TypeThresholdCountSeverityAction

=IF(MAX(Peso/24000, Pallets/33) < 0.75, "Ineficiente", "OK")

Decide

Allocate with fairness

When supply falls short, the Fair Share algorithm distributes proportionally across all 15 DCs — no DC gets zero, no DC gets everything. The dashboard presents actionable decisions, not just data.

DCDemandCoverageAllocationΔ_Units

=ROUND(DC_Demand * Coverage_Ratio, 0)

By the time an order reaches the fourth stage, it has been parsed, validated, weighed, and stress-tested against every other order competing for the same truck. Nothing reaches a decision until it has earned the right to be trusted.

The invisible work.

Before a single order can be counted, it has to be made true. This is the unglamorous, detail-heavy layer where the extract stops lying — two real problems from the Apex data, and how each one was run to ground.

Before → After

Dispatch_Date03-Jun-2026Text-month — ISO elsewhere

Dispatch_Date_Std2026-06-03ISO 8601 — one format

Weight_kgNULLNo weight → invisible row

Weight_kg_Clean1.05Imputed from SKU historical avg

Qty-25Negative — return? error?

Qty_Abs25Absolute value + flagged for review

SKUXXX-8415XXX- = SAP capture error

SKU_StatusORPHANQuarantined — excluded from allocation

Lesson 01

The dates that lied

Dispatch_Date arrived in two formats at once. Most rows were ISO — "2026-05-31" — but a stubborn minority were text-month: "31-May-2026". No flag marked which was which. Parse the whole column as ISO and the text-month rows fail silently, dropping out of every transit-time calculation and making the network look faster than it really is. Parse it the other way and the ISO rows break instead. The fix was disciplined, not clever: detect the format row by row, normalize everything to ISO, and reject anything that still refused to parse so nothing failed in silence. A short Power Query step — and every duration built on top of it became trustworthy.

Lesson 02

The phantom SKUs

Twelve orders pointed at SKU codes that matched nothing in the product master — and all twelve shared the same XXX- prefix. That prefix is the tell: it's what SAP writes when the capture step fails, so these weren't products, they were data-entry ghosts. They couldn't be weighed, costed, or allocated. The easy move is to delete them and move on. But deleting also erases a real signal: twelve orders' worth of demand that someone genuinely tried to place. So they're quarantined, not buried — pulled out of the allocation math, listed on a reconciliation report, and handed back to the team that owns SAP capture. A broken row still tells you something; the job is to flag it, not hide it.

These two stories are why the cleaning layer exists as a separate stage. It's not preprocessing — it's quality assurance. The model is only as honest as the data it accepts.

Four detectors. Zero false comfort.

The cleaned data feeds four parallel exception detectors. Each one surfaces a specific risk that the raw data hides. Together, they caught 469 actionable flags across 1,105 orders.

Inefficient Loads

361trucks below 75% fill

Truck fill rate < 75% (24,000 kg or 33 pallets, whichever fills first)

Formula=IF(MAX(Peso/24000, Pallets/33) < 0.75, "Ineficiente", "OK")

Business rationale

Every truck that leaves a plant half-empty is wasted freight cost. At 361 out of 1,105, nearly a third of all shipments are burning money. The detector flags these so logistics can consolidate.

Example

SO-50182: 8,200 kg on a 24,000 kg truck → 34% fill rate. The shipment could carry 3× more. Flagged as inefficient.

FEFO Risk

45dairy orders at risk

Dairy product with < 70% shelf life remaining at estimated arrival

Formula=IF(AND(Cat="Dairy", Shelf_Pct<0.70), "FEFO Risk", "OK")

Business rationale

Retail partners reject dairy with insufficient shelf life. A product that arrives too close to expiry is refused at the DC — becoming waste instead of revenue. Early detection lets the team reroute or expedite before the clock runs out.

Example

SO-51008 (LAC-1016, Yoghurt Bebible) to CD-MOR: ~13 days In Transit on a short-shelf-life dairy line. Flagged for priority routing before the DC can refuse it.

Ghost Shipments

51orders stuck "In Transit"

SAP_Status = "In Transit" for > 5 consecutive days

Formula=IF(AND(SAP_Status="In Transit", Days_Transit>5), "Ghost", "OK")

Business rationale

A shipment that's been in transit for over 5 days is either lost, delivered but not scanned, or stuck at a checkpoint. These ghost orders distort inventory calculations — the system thinks supply is coming, but it never arrives.

Example

SO-50431 to CD-VER: 9 days In Transit, no arrival scan. The destination DC is planning for inventory that may never show up.

Orphan Orders

12SKUs not in master

SKU code has no match in the product master catalog

Formula=IF(ISNA(VLOOKUP(SKU, Master, 1, 0)), "Orphan", "OK")

Business rationale

An order referencing a nonexistent product can't be allocated, weighed, or costed. As Lesson 02 showed, these XXX- codes are SAP capture errors — but they still represent demand someone tried to place. The detector quarantines them for reconciliation, never silently dropping them.

Example

XXX-8415 to CD-TOL: no match in master and no weight on file. Quarantined for SAP capture review, not auto-excluded.

total exception flags across 1,105 orders

From noise to signal.

The messy extract became a decision-ready dashboard. Every number is defensible. Every exception is explained. Every allocation is fair.

Orders analyzed nightly

Exception flags surfaced

SKU shortages auto-detected

DCs allocated fairly

Before

✕Two date formats mixed in one column

✕12 orphan SKUs — ignored or manually patched

✕No visibility into truck fill rates

✕Ghost shipments distorting inventory counts

✕Shortage allocation by gut feel

After

✓Standardized ISO dates — one format, zero ambiguity

✓Orphan codes quarantined for SAP capture review

✓361 inefficient loads flagged automatically

✓51 ghost shipments surfaced for follow-up

✓Fair Share proportional allocation — math, not politics

The Dashboard

Exception Summary

All four detector outputs in one view — filterable by type, severity, and DC.

Fair Share Allocator

Live allocation table for every SKU in shortage, with coverage ratios and deficit tracking.

Transit Monitor

Ghost shipment tracker with days-in-transit aging and escalation flags.

Load Optimizer

Fill-rate analysis across all active shipments, with consolidation suggestions.

What changes by morning.

The planners no longer open a spreadsheet of 1,105 raw rows. They open a short list of decisions: which trucks to consolidate, which dairy to expedite, which codes to reconcile, and exactly how to split what's scarce. The noise became a shortlist of things worth acting on — and every number on it traces back to a row that was made true.

Inputs you can trust

Every order is parsed, validated, and reconciled before it ever reaches a calculation.

Safety stock that holds

Coverage set statistically at a 95% service level — not by gut, and defensible line by line.

Decisions, not dashboards

Each exception arrives with the action attached: consolidate, expedite, reconcile, reroute.

Fairness under scarcity

When supply falls short, every DC is cut by the same ratio — an allocation no one can dispute.

View on GitHub Download Excel Model

←Back to perezfajardo.com

Apex Consumer Goods is a fictional company created for this case study. All data is synthetic.

1,105 orders every night. Some of them lie.

This is what the data actually looks like.

Four stages. One pipeline.

Ingest

Clean

Analyze

Decide

The invisible work.

Before → After

The dates that lied

The phantom SKUs

Four detectors. Zero false comfort.

Inefficient Loads

FEFO Risk

Ghost Shipments

Orphan Orders

When there isn't enough.

From noise to signal.

The Dashboard

Exception Summary

Fair Share Allocator

Transit Monitor

Load Optimizer

What changes by morning.

Inputs you can trust

Safety stock that holds

Decisions, not dashboards

Fairness under scarcity

1,105 orders every night.
Some of them lie.