Inventory alerting & yield-tracking playbook for perishable brewery inputs: numeric ROP/min‑max rules, lot‑aware rolling‑cover algorithm and escalation workflows

Most brewery inventory tooling treats hops and yeast like they're bolts in a bin. Set a reorder point, fire an email when you cross it, done. That model falls apart the moment you remember two things: your inputs expire, and they arrive in lots with different remaining shelf life. A pallet of Citra landing six weeks before its best-by is not the same asset as the same weight with eight months on the clock — even though your standard ROP logic counts them identically.

This is for the ops engineer or production manager who has to turn supplier rolling-cover advice into something the system actually computes. We're going to build the formulas, the lot-aware cover algorithm, the data model, the SQL to project days-of-cover, and the escalation rules. If you want the procurement-side thinking behind supplier tiers and contract clauses, that's covered separately in the hops and yeast procurement playbook. This is the implementation layer underneath it.

Why a flat reorder point quietly fails on perishables

A flat ROP makes two assumptions that aren't true for brewery inputs. First, that on-hand quantity is fungible. Second, that running out is the only risk. For perishables you've got the opposite problem too — over-ordering because the system told you to top up, then watching alpha acids degrade or a yeast slurry drift out of viability before you brew with it.

In practice this usually looks like a brewery that "never stocks out" of hops but writes off somewhere between 8–12% of its hop spend annually to degradation. The alerting worked perfectly. It just measured the wrong thing — kilograms, not usable kilograms within shelf life.

The second failure mode is lot blending at the alert level. Say you've got 40 kg of a hop across three POs: 10 kg expiring in three weeks, 12 kg in two months, 18 kg in five months. A naive days-of-cover calc divides 40 by daily usage and tells you you're fine. But if your next brew using it is four weeks out, 10 of those kilos are already effectively dead. Your real projected cover is built on 30 kg.

So the whole design goal here is: alert on usable cover, lot by lot, against the actual production schedule — not on a single summed number.

The combined demand / lead-time ROP, worked for three brewery sizes

Start with the standard reorder point built for variability in both demand and lead time. Safety stock needs to protect against both wobbling together:

ROP = (D × LT) + SS where SS = z × √( (LT × σD²) + (D² × σLT²) )

1. 1
   D = average daily usage of the input (kg/day for hops, L of slurry or # of pitches for yeast)
2. 2
   LT = average lead time in days
3. 3
   σ_D = standard deviation of daily usage
4. 4
   σ_LT = standard deviation of lead time
5. 5
   z = service-level factor (1.65 ≈ 95%, 2.05 ≈ 98%, 2.33 ≈ 99%)

Min = ROP. Max = ROP + the order quantity you'd normally cut a PO for (your EOQ or contract lot size). That's your min/max band.

Three realistic profiles for a single flagship hop — say Citra across a core IPA line.

Small brewery (~1,500 bbl/yr)

1. 1
   D = 2.1 kg/day, σ_D = 0.9
2. 2
   LT = 21 days, σ_LT = 5
3. 3
   z = 1.65 (95%)

SS = 1.65 × √( (21 × 0.9²) + (2.1² × 5²) ) = 1.65 × √( (21 × 0.81) + (4.41 × 25) ) = 1.65 × √( 17.01 + 110.25 ) = 1.65 × √127.26 = 1.65 × 11.28 ≈ 18.6 kg

ROP = (2.1 × 21) + 18.6 = 44.1 + 18.6 ≈ 62.7 kg → round to 63 kg If your contract lot is ~45 kg, Max ≈ 63 + 45 = 108 kg.

Medium brewery (~8,000 bbl/yr)

1. 1
   D = 9.5 kg/day, σ_D = 3.2
2. 2
   LT = 18 days, σ_LT = 6
3. 3
   z = 2.05 (98%)

SS = 2.05 × √( (18 × 3.2²) + (9.5² × 6²) ) = 2.05 × √( (18 × 10.24) + (90.25 × 36) ) = 2.05 × √( 184.3 + 3249 ) = 2.05 × √3433.3 = 2.05 × 58.6 ≈ 120.1 kg

ROP = (9.5 × 18) + 120.1 = 171 + 120.1 ≈ 291 kg Notice the safety stock is now the dominant term, driven almost entirely by σ_LT. At mid-size, your biggest exposure isn't how much beer you brew — it's how unreliable your supplier's delivery window is. Tightening LT variance buys you more than tightening forecast accuracy.

Large brewery (~30,000 bbl/yr)

1. 1
   D = 34 kg/day, σ_D = 7
2. 2
   LT = 14 days, σ_LT = 3 (contracted, more reliable)
3. 3
   z = 2.33 (99%)

SS = 2.33 × √( (14 × 7²) + (34² × 3²) ) = 2.33 × √( (14 × 49) + (1156 × 9) ) = 2.33 × √( 686 + 10404 ) = 2.33 × √11090 = 2.33 × 105.3 ≈ 245.4 kg

ROP = (34 × 14) + 245.4 = 476 + 245.4 ≈ 721 kg The pattern across the three sizes is worth sitting with. The small brewery's risk is dominated by lead-time swings on a thin usage base. The large brewery has scaled demand variance down through volume and contracted its lead time tighter, so even at 99% service, safety stock as a share of cycle stock is lower. If you only had budget to fix one input per tier, it'd be σLT for small and medium, and σD forecasting for large.

One thing people miss: these numbers assume on-hand is all usable. For perishables, ROP is the floor. The cover algorithm below is what keeps you from trusting an inflated on-hand number.

The lot-aware rolling-cover algorithm

Instead of asking "how many kg do we have," you walk each future brew in the schedule, consume from lots in shelf-life order, and see which brews can't be covered by inputs that are still in-spec on their brew date.

Think of it as a FEFO (first-expiring-first-out) simulation run against the production calendar.

1. 1
   Pull every open and on-hand lot for the SKU with its quantity, received date, best-by/expiry, and earliest usable date (yeast may have a QA hold window).
2. 2
   Pull every scheduled brew that consumes the SKU, with its brew date and required quantity.
3. 3
   Sort brews by date. For each brew, only allow consumption from lots whose expiry is on or after the brew date and whose usable date is on or before the brew date.
4. 4
   Within eligible lots, consume FEFO — soonest expiry first — to burn down short-life stock before it dies.
5. 5
   If a brew can't be fully covered by eligible stock, flag it with the shortfall and the latest date a PO must arrive to cover it.
6. 6
   After all scheduled brews are covered, any remaining lot quantity that expires before it could realistically be consumed gets flagged as projected waste — that's your over-order signal.

That sixth step is what most systems skip, and it's where the money is. It turns "you have plenty" into "you have plenty, but 14 kg of it is going to expire unused — don't reorder yet."

Pseudocode

function projectedCover(sku, today): lots = getUsableLots(sku) # qty, usablefrom, expiry brews = getScheduledBrews(sku) # brewdate, requiredqty sort brews by brewdate asc shortfalls = [] for brew in brews: need = brew.requiredqty eligible = lots.filter(l => l.expiry >= brew.brewdate and l.usablefrom <= brew.brewdate and l.qty > 0) sort eligible by expiry asc # FEFO for lot in eligible: take = min(lot.qty, need) lot.qty -= take need -= take if need == 0: break if need > 0: shortfalls.append({ brew: brew.brewdate, shortqty: need, pomustarriveby: brew.brewdate - inboundqcdays }) projectedwaste = sum(l.qty for l in lots if l.expiry < lastConsumableDate(l, brews)) return shortfalls, projectedwaste

inboundqcdays matters more than people expect. A yeast lot landing the morning of a brew is useless if you need 48 hours for a viability check. Bake that buffer into pomustarrive_by so your alert gives procurement real runway, not a same-day scramble.

Projected days-of-cover in SQL

If you want a lighter-weight signal than the full simulation — something to render on a dashboard and drive a first-tier alert — compute usable days-of-cover directly. The trick is summing only quantity from lots that are in-spec today and won't expire before average usage would consume them.

WITH usable AS ( SELECT i.skuid, SUM(l.qtyremaining) AS usableqty FROM inputlot l JOIN inputitem i ON i.skuid = l.skuid WHERE l.status = 'RELEASED' AND l.usablefrom <= CURRENTDATE AND l.expirydate > CURRENTDATE ), usage AS ( SELECT skuid, avgdailyusage FROM inputusagestats ) SELECT u.skuid, us.usableqty, usg.avgdailyusage, ROUND(us.usableqty / NULLIF(usg.avgdailyusage,0), 1) AS projecteddayscover FROM usable us JOIN usage usg ON usg.skuid = us.skuid JOIN inputitem u ON u.skuid = us.skuid;

Then your trigger compares against a cover band, not a hard kg floor: SELECT skuid, projecteddayscover FROM vdaysofcover WHERE projecteddayscover < (leadtimedays + safety_days);

safety_days here is just your safety stock expressed in days — SS ÷ D. Expressing everything in days of cover rather than kilograms is probably the single best move for a cross-SKU dashboard, because it normalizes across inputs. A brewer glancing at the board sees "Citra: 11 days, Mosaic: 4 days, US-05: 2 pitches" and instantly knows where the fire is.

A data model that doesn't fight you later

The reason most brewery systems can't do lot-aware cover is their schema stored a single quantity-on-hand per SKU. You cannot reconstruct lots after the fact. Model at the lot grain from day one.

input_item

1. 1
   sku_id, name, type (HOP / YEAST / FINING / ADJUNCT)
2. 2
   avgdailyusage, usagestddev, leadtimedays, leadtime_stddev
3. 3
   ztarget, eoqqty, uom

input_lot

1. 1
   lotid, skuid, po_id
2. 2
   qtyreceived, qtyremaining, uom
3. 3
   receiveddate, usablefrom, expiry_date
4. 4
   status (QUARANTINE / HOLD / RELEASED / CONSUMED / SCRAPPED)
5. 5
   storagezone, lasttempreading, tempexcursion_flag
6. 6
   coapass (bool), coanotes

purchase_order

1. 1
   poid, supplierid, sku_id
2. 2
   qtyordered, expectedarrivalstart, expectedarrival_end
3. 3
   status, confirmedshipdate

brew_demand

1. 1
   brewid, skuid, brewdate, requiredqty, pitch_rate (yeast only)

Two fields that earn their keep: expectedarrivalstart / expectedarrivalend on the PO — an arrival window, not a point date, because suppliers give you ranges — and usable_from on the lot, so QA holds and yeast propagation are first-class, not an afterthought. An API exposing GET /inputs/{sku}/cover that returns projected days, shortfalls, and projected waste gives every downstream alert one source of truth.

Yeast-specific rules that hops don't need

Yeast breaks the clean kilogram model in three ways, and each needs its own logic.

Pitch scaling by viability. You don't pitch a fixed volume; you pitch a cell count scaled to wort volume and gravity. If a slurry drops from ~95% viability to ~78%, the usable cell count drops and you need more slurry for the same pitch. Your demand per brew is therefore dynamic — recompute required_qty from current viability, not from a static recipe number. A lot that reads "plenty of volume" can still be short on live cells.

Backup strain logic. For house strains especially, the alert shouldn't just say "low." It should check whether a propagation can be started in time, and if not, whether an approved backup strain exists for that recipe. Store an ordered substitutestrains list per brewdemand so the alert can suggest "propagate now, or swap to backup X for this batch."

QA pass thresholds as a gate. A yeast lot stays QUARANTINE until it clears viability and contamination checks. Set hard thresholds — e.g., viability ≥ 90% for first-generation pitches, ≥ 85% for later generations, contamination counts below your lab's limit. The lot's usable_from doesn't get set until it passes. This is what stops the "we have yeast" lie when the yeast hasn't cleared QA yet.

A generation counter on the slurry matters too — most house strains get retired after a set number of repitches. Let the model flag "generation 7 of 8" so nobody discovers the limit mid-planning.

Cold-chain excursions and the quarantine flow

Temperature excursions are where perishable inventory alerting earns back its cost. A reefer that drifts, a cold room that fails overnight, a pallet that sat on a hot dock — any of these can degrade hops' alpha acids or crash yeast viability without changing a single quantity number in your system.

1. 1
   Detect. A temperature reading breaches threshold for the storage zone (e.g., yeast zone > 7°C for > 2 hours). Flag every lot in that zone with tempexcursionflag = true.
2. 2
   Auto-quarantine. Flagged lots flip to QUARANTINE and get pulled from usable cover immediately. Days-of-cover recalculates — which may itself trigger a shortage alert downstream. That cascade is the point. One failed cold room should instantly re-expose every brew that depended on its stock.
3. 3
   Assess. QA pulls samples. Hops get checked against expected alpha; yeast gets a viability/vitality read. Record the duration and peak temperature of the excursion against the lot.
4. 4
   Disposition. Pass → release with notes. Marginal → downgrade (e.g., usable for a less hop-forward batch, or pitched at higher volume). Fail → SCRAPPED, which writes a loss record and feeds your waste KPI.

The mistake is treating a temp alert as an informational email. It has to mutate inventory status automatically, because the whole point of tracking usable cover is that a cold-chain failure shows up immediately as a production risk — not as a facilities note someone reads tomorrow morning.

Alert thresholds and escalation SLAs

Tiered alerts beat a single threshold because not every shortage is equally urgent. A hop that's low but still weeks of cover away is a procurement nudge. A quarantined yeast lot two days before a scheduled pitch is a stop-the-line event.

Two design notes. First, the "Waste" tier is a downgrade alert — it should suppress or delay reorder triggers for that SKU, because topping up a bin that's about to expire unused compounds the loss. Second, escalations should carry context, not just a code. "Shortfall of 11 kg Citra for the Oct 14 IPA brew; PO must arrive by Oct 9; nearest supplier lead time 7 days" is actionable. "Low stock: Citra" is not.

A short real scenario

A regional brewery running around 6,500 bbl/yr had flat reorder points in a spreadsheet synced nightly. On paper they never ran short of hops. In practice they were scrapping somewhere around $9k–$12k of hops a year to degradation, and twice had to swap a yeast strain last-minute because a slurry failed viability the day before a pitch — which nobody caught until the brewer went to pitch.

They rebuilt the model at the lot grain, switched the dashboard to projected usable days-of-cover, and wired the yeast QA gate so lots couldn't count toward cover until they passed. The first month, the system flagged roughly 40 kg of hops heading for expiry before any scheduled brew would touch them — so they held two POs instead of auto-topping up. Over the following two quarters, hop write-offs dropped to the low single-digit thousands, and the two-days-out yeast surprises went away because shortfalls surfaced a week earlier, when a propagation could still be started.

Nothing exotic. They just stopped counting dead inventory as live and started measuring cover against the actual schedule.

Tuning the cover bands over time

The bands aren't set-and-forget. Review these KPIs quarterly and adjust z, safety_days, and arrival windows accordingly:

1. 1
   Usable fill rate — brews fully covered by in-spec stock ÷ total brews. Target high 90s.
2. 2
   Degradation write-off % — scrapped-for-expiry value ÷ input spend. If this creeps up, you're over-ordering or your bands are too fat.
3. 3
   Excursion-to-scrap conversion — share of flagged lots that end up scrapped. Rising means a storage or handling problem, not an inventory one.
4. 4
   Lead-time variance (σ_LT) per supplier — the input that most inflates your safety stock; track it to justify renegotiating delivery windows.
5. 5
   Yeast viability-at-pitch — median viability when you actually pitch. Drifting down means your QA holds or propagation timing need work.
6. 6
   Alert precision — share of Urgent/Critical alerts that were real. Too many false alarms and people start ignoring the board, which is the worst failure mode of all.

The honest trap with cover bands is over-tuning after one bad quarter. If a supplier blows a delivery window once, don't immediately pad safetydays for every SKU — check whether σLT actually shifted across several POs first. Alerting that reacts to noise trains your team to distrust it, and an ignored alert is worse than no alert.

Perishable inventory alerting for hops and yeast only works when the system refuses to count stock it can't actually use — the right lot, in spec, available on the day you brew. Get that one principle into your data model and your triggers, and the formulas and escalations above stop being theory and start quietly saving you the inventory you were writing off.