Supplier Price Variance Tracking

Not every food-cost overage is a kitchen problem — sometimes the kitchen executed perfectly and the supplier simply raised the price. This guide, part of the theoretical vs actual food cost calculation domain, isolates the sub-problem of measuring the supplier-price component of variance: how much of a period’s cost change is attributable to what ingredients cost, independent of how much was used. The failure this eliminates is misattribution — blaming portioning or waste for a variance that was actually a vendor price increase, which sends an operator chasing the line cooks when the real fix is a procurement conversation. It produces the supplier_price_cost component that cost variance attribution models subtract as a measured cause.

Concept Definition and Data Contract

Supplier price variance is the classic price-times-quantity decomposition applied to procurement: the cost change caused by price movement, holding quantity at what was actually purchased. The input contract is a temporal price ledger — (supplier_id, ingredient_sku, unit_cost, effective_date) — plus the period’s purchased quantities in canonical units from unit conversion and canonicalization. The output contract is a supplier_price_cost per (location_id, ingredient_sku), signed, in Decimal.

The baseline is the load-bearing choice: price variance is always relative to something. A prior-period price isolates recent movement; a contracted price isolates deviation from an agreement. The pipeline supports either, but the baseline must be explicit and versioned, or the variance means nothing.

Architecture Decision Rationale

Price-quantity separation over blended averages. Isolating the price effect requires holding quantity fixed. Blending price and quantity into a single average cost hides whether a cost rose because the vendor charged more or because the kitchen used more — exactly the distinction this layer exists to draw. Weighting the price delta by actual purchased quantity gives a clean, isolated price component.

Temporal ledger over a mutable current price. A single “current price” cell cannot answer “what did this cost last quarter?” The price ledger stores every price with an effective date, so a variance can be computed as-of any period and a retroactive invoice correction mints a new dated row rather than rewriting history. This mirrors the temporal versioning in the core architecture.

Quarantine implausible jumps. A 400% overnight price spike is far more likely a unit error on the invoice (price per case booked as price per unit) than a real market move. Feeding it into the variance produces a huge false attribution; quarantining it triggers a review. The detection of slow, sub-threshold increases is worked in detecting supplier price creep.

Phase 1 — The Temporal Price Ledger

The first phase ingests invoice prices into a dated ledger and resolves the effective price as-of a date.

from __future__ import annotations

from datetime import date
from decimal import Decimal

import pandas as pd


def effective_price(ledger: pd.DataFrame, sku: str, as_of: date) -> Decimal:
    """Most recent price effective on or before as_of."""
    rows = ledger[
        (ledger["ingredient_sku"] == sku) & (ledger["effective_date"] <= as_of)
    ].sort_values("effective_date")
    if rows.empty:
        raise LookupError(f"no price for {sku} as of {as_of}")
    return Decimal(str(rows["unit_cost"].iloc[-1]))

Phase 2 — Index Against Baseline and Quarantine Jumps

Each current price is compared to its baseline; implausible deltas are quarantined before they reach the variance.

from decimal import Decimal

import pandas as pd


def index_prices(current: pd.DataFrame, baseline: pd.DataFrame,
                 jump_ceiling: Decimal = Decimal("2.0")) -> tuple[pd.DataFrame, pd.DataFrame]:
    df = current.merge(baseline, on="ingredient_sku", suffixes=("_cur", "_base"))
    df["price_delta"] = df["unit_cost_cur"] - df["unit_cost_base"]
    df["rel_change"] = df.apply(
        lambda r: (r["price_delta"] / r["unit_cost_base"])
        if r["unit_cost_base"] > 0 else Decimal("0"),
        axis=1,
    )
    suspect = df["rel_change"].abs() > jump_ceiling
    return df[~suspect].copy(), df[suspect].assign(reason="implausible_price_jump")

Phase 3 — Quantity-Weight and Reconcile

The clean price deltas are weighted by purchased quantity to produce the cost variance, exactly.

from decimal import Decimal

import pandas as pd


def weight_variance(indexed: pd.DataFrame, purchased: pd.DataFrame) -> pd.DataFrame:
    df = indexed.merge(purchased, on=["location_id", "ingredient_sku"], how="left")
    df["purchased_qty"] = df["purchased_qty"].fillna(Decimal("0"))
    df["supplier_price_cost"] = df.apply(
        lambda r: Decimal(str(r["price_delta"])) * Decimal(str(r["purchased_qty"])),
        axis=1,
    )
    return df[["location_id", "ingredient_sku", "price_delta",
               "purchased_qty", "supplier_price_cost"]]

The supplier_price_cost is signed — a negative delta (the vendor got cheaper) reduces the variance — and it is exactly the term the attribution model subtracts before inferring prep and portioning causes. Because it is quantity-weighted at actual purchased volume, it reconciles cleanly against the procurement ledger.

Production Hardening

• Explicit, versioned baseline. Store the baseline choice (prior period or contract) with the run; a variance without a stated baseline is uninterpretable.
• Temporal price ledger. Keep every price dated so any period is computable as-of and corrections append rather than overwrite.
• Quarantine implausible jumps. Route price changes beyond a ceiling to review; an invoice unit error must not become a false variance.
• Decimal weighting. Compute the price-times-quantity term in Decimal so the component reconciles against procurement to the cent.
• Signed component. Preserve the sign so favorable price moves offset unfavorable ones in the attribution total.

Failure Modes and Troubleshooting

FAQ

Related

• Up one level: Theoretical vs Actual Food Cost Calculation
• Detecting Supplier Price Creep
• Cost Variance Attribution Models
• Multi-Location Cost Center Architecture
• Threshold Tuning for Alerts

For library specifics, see the official pandas documentation and Python decimal documentation.