Theoretical Vs Actual Food Cost Calculation

Detecting Supplier Price Creep

This page shows a food-tech developer how to catch the supplier increases that no single invoice trips: a series of small, individually-forgivable price rises that compound into a material cost problem over a quarter. It is the implementation companion to supplier price variance tracking; read that for the temporal price ledger and quantity-weighted variance, then follow the steps here to detect slow drift that per-invoice jump ceilings are designed to ignore.

Creep is a trend problem, not a threshold problem. A 1.5% rise every two weeks never triggers a per-invoice alert, yet it is a 40%+ annualized increase. Detecting it needs a smoothed trend and a slope, not a point comparison.

Prerequisites and Data Contract

  • Python 3.11+, pandas 2.x, numpy.
  • The temporal price ledger from the parent guide: (ingredient_sku, unit_cost, effective_date), prices as Decimal.
  • Recent purchased quantities to weight impact, in canonical units.
  • Output is a per-SKU creep signal: a smoothed slope, a sustained-drift flag, and an annualized cost impact for ranking.
Supplier price creep detection flow A per-SKU price series is smoothed to remove invoice-to-invoice noise. A rolling linear slope is fitted over a trailing window. A sustained positive slope across the window flags the SKU as creeping. Flagged SKUs are ranked by their annualized cost impact so the most material creep surfaces first. Price series per SKU Smooth + slope rolling window Flag sustained positive drift Rank by impact annualized

Step-by-Step Implementation

Step 1 — Build a smoothed price series per SKU

Smooth invoice-to-invoice noise with an EWMA so the slope reflects trend, not a single spot buy.

import pandas as pd


def smooth_prices(ledger: pd.DataFrame, alpha: float = 0.3) -> pd.DataFrame:
    df = ledger.sort_values(["ingredient_sku", "effective_date"]).copy()
    df["unit_cost_f"] = df["unit_cost"].astype(float)      # smoothing tolerates float
    df["smoothed"] = (
        df.groupby("ingredient_sku")["unit_cost_f"]
        .transform(lambda s: s.ewm(alpha=alpha, adjust=False).mean())
    )
    return df

Step 2 — Fit a rolling slope

A positive slope over a trailing window is the creep signal. Express it as a fractional change per day so SKUs are comparable.

import numpy as np
import pandas as pd


def rolling_slope(df: pd.DataFrame, window: int = 8) -> pd.DataFrame:
    def slope(series: pd.Series) -> float:
        if series.notna().sum() < window:
            return np.nan
        y = series.values
        x = np.arange(len(y))
        # normalized slope: fractional change per step
        return np.polyfit(x, y, 1)[0] / max(y.mean(), 1e-9)

    df = df.copy()
    df["creep_slope"] = (
        df.groupby("ingredient_sku")["smoothed"]
        .transform(lambda s: s.rolling(window).apply(slope, raw=False))
    )
    return df

Step 3 — Flag sustained drift and rank by impact

A slope that stays positive across the window is creep; rank flagged SKUs by annualized cost so procurement acts on the material ones first.

from decimal import Decimal

import pandas as pd


def rank_creep(df: pd.DataFrame, purchased: pd.DataFrame,
               min_slope: float = 0.002) -> pd.DataFrame:
    latest = df.groupby("ingredient_sku", as_index=False).last()
    latest["is_creeping"] = latest["creep_slope"] > min_slope
    flagged = latest[latest["is_creeping"]].merge(purchased, on="ingredient_sku", how="left")
    flagged["annualized_impact"] = flagged.apply(
        lambda r: (Decimal(str(r["creep_slope"])) * Decimal("365")
                   * Decimal(str(r["unit_cost"])) * Decimal(str(r["annual_qty"]))),
        axis=1,
    )
    return flagged.sort_values("annualized_impact", ascending=False)

Verification and Validation

  • Synthetic creep. Feed a series rising 1% per invoice; the slope must be positive and the SKU flagged, even though no single step trips a jump ceiling.
  • Noise rejection. Feed a flat series with random spikes; the smoothed slope must stay near zero and the SKU must not flag.
  • Ranking sanity. Confirm a small creep on a high-volume staple outranks a large creep on a rarely-bought item — impact, not slope magnitude, drives the ranking.
  • No overlap with jump quarantine. Confirm creep detection catches what the per-invoice ceiling ignores, and vice versa; the two are complementary.

Gotchas and Edge Cases

  • Seasonality mistaken for creep. Produce prices rise and fall seasonally; a rising window can look like creep. Compare against a same-season baseline or deseasonalize before fitting the slope, or you will alert every autumn.
  • Too-short window. A slope over three invoices is noise. Require a minimum observation count in the window before trusting the slope, and leave short series unflagged rather than guessing.
  • Float in the impact math. Smoothing and slope tolerate float, but the annualized cost impact must switch to Decimal so the ranking figure reconciles with procurement.
  • Step changes vs creep. A one-time contracted increase is a step, not creep, and the per-invoice variance already captures it. Detrend or treat a single large step separately so it does not masquerade as gradual drift.

For library specifics, see the official pandas documentation and NumPy documentation.