Forecasting consumable demand from grant burn rate

Problem statement #

You need a Python forecast that turns a lab’s historical consumption and a grant’s burn-rate into a reorder point bounded by the award’s remaining period of performance — so a reagent order placed six weeks before an award closes is sized to what the study will actually consume before the end date, never a speculative stockpile that a federal auditor would flag as unallowable under 2 CFR 200.

This task sits under Inventory Threshold Tuning, one of the operational layers of Equipment Calibration & Lab Inventory Tracking. The parent guide computes a lead-time reorder point clamped to a storage cap; this page adds a second, time-bounded constraint — the award end date — so the same consumable is never ordered in a quantity the grant cannot legitimately consume. Where the tuning engine answers “how low can stock fall before we reorder,” this forecast answers “is there enough performance period left to justify the order at all.”

Prerequisites #

• Python 3.10+ (uses datetime.now(timezone.utc), type hints, and hashlib.sha256).
• Libraries: pandas>=2.0 for the usage-history rollup, pydantic>=2.5 for the forecast payload, and SQLAlchemy>=2.0 for the audit ledger. Install with pip install "pandas>=2.0" "pydantic>=2.5" "SQLAlchemy>=2.0".
• Environment variables (never hard-code credentials, per Security Boundary Configuration):
  • INV_DB_URL — the SQLAlchemy connection string for the forecast ledger.
• Policy config: a version-controlled award registry giving each grant its period-of-performance end date and remaining direct-cost budget, kept alongside your University Policy Mapping Frameworks. The consumption history itself is sourced upstream from Equipment Usage Logging Systems.

Step-by-step implementation #

The forecast is a pipeline: roll up usage history into a daily consumption rate, compute the grant’s burn-rate against its remaining budget and time, project cumulative demand out to the award end date, derive a reorder point from lead-time demand, and finally clamp the recommended order to what the remaining performance period can allowably consume. The last step is the compliance gate — it flags any order that would leave unused stock at closeout.

Figure: consumption rate and burn-rate project demand to the award end date; the allowability gate blocks any order that outlives the performance period.

Step 1 — Roll up usage history into a daily consumption rate #

Load the raw usage events and reduce them to a stable daily rate. A trailing window smooths day-to-day noise, but a pure mean over a long window hides the seasonal and semester-driven variance that is common in teaching labs — so the function reports both the mean rate and a recent-window rate, and the forecast later uses the higher of the two to stay conservative.

import pandas as pd


def daily_consumption_rate(events: pd.DataFrame, item_id: str, window_days: int = 90) -> dict[str, float]:
    """Reduce usage events to a smoothed daily rate for one consumable.

    Reports both a long-window mean and a recent-window rate; forecasting on
    the larger of the two avoids under-ordering when usage is trending up
    (e.g. a new semester's teaching sections coming online).
    """
    item = events.loc[events["item_id"] == item_id].copy()
    item["date"] = pd.to_datetime(item["used_at"], utc=True).dt.floor("D")
    daily = item.groupby("date")["quantity"].sum()

    full_mean = float(daily.mean()) if not daily.empty else 0.0
    recent = daily.last(f"{window_days}D")
    recent_mean = float(recent.mean()) if not recent.empty else full_mean
    return {"mean_rate": full_mean, "recent_rate": recent_mean}

Step 2 — Compute the burn-rate and the remaining performance window #

The grant’s burn-rate is spend velocity: dollars consumed per day against the remaining direct-cost budget. The performance window is the calendar distance from today to the award end date. Both are bounded — a negative or zero remaining window means the award is at or past closeout and no new consumable order is allowable.

from datetime import datetime, timezone


def performance_window(award: dict) -> dict[str, float]:
    """Days and dollars remaining in the award's period of performance.

    2 CFR 200 obligation rules: costs must be incurred within the period of
    performance, so an order is only allowable if remaining_days > 0 and the
    projected consumption completes before the end date.
    """
    now = datetime.now(timezone.utc)
    remaining_days = (award["period_end"] - now).days
    remaining_budget = float(award["direct_cost_budget"] - award["direct_cost_spent"])
    burn_rate = (remaining_budget / remaining_days) if remaining_days > 0 else 0.0
    return {
        "remaining_days": remaining_days,
        "remaining_budget": remaining_budget,
        "daily_burn_rate": burn_rate,
    }

Step 3 — Project demand to the award end and derive a reorder point #

Cumulative demand to closeout is the conservative daily rate multiplied by the remaining days. The lead-time reorder point is the rate multiplied by the vendor lead time — the level at which an order must be placed to avoid a stockout. Both are needed: the reorder point decides when, the projected end-demand decides how much is allowable.

def project_demand(rate: dict[str, float], window: dict[str, float], lead_time_days: int) -> dict[str, float]:
    """Project end-of-award demand and the lead-time reorder point."""
    conservative_rate = max(rate["mean_rate"], rate["recent_rate"])
    remaining_days = max(window["remaining_days"], 0)

    demand_to_end = conservative_rate * remaining_days
    reorder_point = conservative_rate * lead_time_days
    return {
        "conservative_rate": conservative_rate,
        "demand_to_award_end": demand_to_end,
        "reorder_point": reorder_point,
    }

Step 4 — Clamp the order to allowable demand and record the decision #

The allowability gate is the compliance boundary. The recommended order quantity may never exceed the demand the award can still consume before its end date, minus what is already on hand. An order that would leave stock unused at closeout is not silently trimmed to zero — it is flagged for a compliance officer, because the correct remedy (split the purchase across awards, shorten the order, or seek a variance) is a human decision.

import hashlib
import uuid
from sqlalchemy.orm import Session


def recommend_order(
    item_id: str,
    on_hand: float,
    proj: dict[str, float],
    window: dict[str, float],
    award_id: str,
    session: Session,
) -> dict:
    """Clamp the order to allowable end-of-award demand; flag stockpiling."""
    allowable_ceiling = max(proj["demand_to_award_end"] - on_hand, 0.0)
    naive_order = max(proj["reorder_point"] - on_hand, 0.0)
    recommended = min(naive_order, allowable_ceiling)

    # A naive order that outruns what the award can consume is an unallowable
    # stockpile under 2 CFR 200.403 (necessary and reasonable). Flag, don't trim.
    status = "flagged_unallowable" if naive_order > allowable_ceiling + 1e-9 else "allowable"

    decision = {
        "audit_id": str(uuid.uuid4()),
        "item_id": item_id,
        "award_id": award_id,
        "recommended_qty": round(recommended, 4),
        "allowable_ceiling": round(allowable_ceiling, 4),
        "remaining_days": window["remaining_days"],
        "status": status,
    }
    fingerprint = hashlib.sha256(
        f"{item_id}|{award_id}|{decision['recommended_qty']}|{window['remaining_days']}".encode()
    ).hexdigest()

    session.add(ForecastLedger(fingerprint=fingerprint, **decision))
    session.commit()
    return decision | {"fingerprint": fingerprint}

Allowable recommendations flow to the same grant-tagged procurement export the tuning engine emits, and the flagged ones cross-reference the award budget through Indirect Cost Recovery Reconciliation, which owns the cost-center reconciliation an auditor will trace.

Schema and field reference #

Verification #

1. Rate isolation: run daily_consumption_rate against a fixture with a known trend and confirm recent_rate exceeds mean_rate when usage is rising, so the conservative rate is the recent one.
2. Window boundary: set period_end in the past and confirm performance_window returns remaining_days <= 0, burn_rate == 0, and that recommend_order yields allowable_ceiling == 0 — no order is recommended past closeout.
3. Allowability clamp: construct a case where the lead-time reorder need exceeds end-of-award demand and confirm status == "flagged_unallowable" and recommended_qty == allowable_ceiling.
4. Reproduce the fingerprint: recompute the SHA-256 over item_id|award_id|recommended_qty|remaining_days and confirm it matches the stored ledger row.

Troubleshooting #

• End-of-grant stockpiling slips through. If flagged orders are still reaching the ERP, the allowability gate is being applied after the purchase order is generated rather than before. Compute allowable_ceiling from demand_to_award_end and gate the recommendation, never post-filter the PO. A quantity that a study cannot consume before period_end is unallowable under 2 CFR 200.403 regardless of budget headroom.
• Lead time outlasts the remaining performance period. When vendor_lead_time_days exceeds remaining_days, an order placed today could not be received and consumed in time. Treat that as its own flag: if remaining_days < lead_time_days, the recommendation is flagged_unallowable even when budget remains, because the goods cannot be put to allowable use within the award.
• Seasonal usage inflates the forecast. A summer shutdown or a semester break drags the long-window mean down while a mid-semester spike drags the recent rate up. Reporting both rates and forecasting on the maximum keeps orders from starving during ramp-up, but a persistent seasonal pattern should feed a seasonal decomposition rather than a flat mean — flag any item whose recent rate exceeds twice its long-window mean for manual review.

Frequently asked questions #

Related #

• Up to the parent topic: Inventory Threshold Tuning
• Setting Dynamic Reorder Thresholds for Lab Consumables — the lead-time reorder point this forecast bounds
• Indirect Cost Recovery Reconciliation — the cost-center reconciliation for flagged orders
• Equipment Usage Logging Systems — the consumption history the forecast reads