Data Ingestion Recipe Parsing Workflows

Reconciling Theoretical vs Actual Inventory Depletion

This page walks a food-tech developer through computing theoretical depletion — how much of each ingredient sales should have consumed — and reconciling it against the actual stock movement measured between two counts. It is the implementation companion to inventory snapshot reconciliation; read that for the reconciliation contract and signed-shrinkage convention, then follow the steps here to build the depletion computation that feeds it.

The task hinges on one join done correctly: expanding each sold menu item through its recipe into ingredient quantities, in canonical units, so theoretical depletion and measured movement are directly comparable.

Prerequisites and Data Contract

  • Python 3.11+, pandas 2.x.
  • A pos_sales frame of (menu_item_id, units_sold) for the period, a recipe_bom of (menu_item_id, ingredient_sku, canonical_qty_per_unit), and a stock_movement frame of measured (ingredient_sku, actual_depletion_canonical) from opening/closing counts and receipts.
  • All quantities canonical (grams/ml) via unit conversion and canonicalization; output is a per-SKU depletion_variance.
Theoretical versus actual depletion reconciliation POS sales expand through the recipe bill of materials into a theoretical depletion quantity per ingredient. That theoretical depletion is joined against the actual depletion measured from stock movement. The signed difference is the depletion variance per SKU, expressed in canonical units. POS sales Expand BOM theoretical qty Stock movement actual qty Join + difference signed Depletion variance

Step-by-Step Implementation

Step 1 — Expand sales into theoretical depletion

One vectorized merge multiplies units sold by per-unit ingredient quantity, then sums per SKU.

from decimal import Decimal

import pandas as pd


def theoretical_depletion(sales: pd.DataFrame, bom: pd.DataFrame) -> pd.DataFrame:
    merged = sales.merge(bom, on="menu_item_id", how="left", validate="m:m")
    merged["qty"] = merged.apply(
        lambda r: Decimal(str(r["units_sold"])) * Decimal(str(r["canonical_qty_per_unit"])),
        axis=1,
    )
    return merged.groupby("ingredient_sku", as_index=False)["qty"].sum().rename(
        columns={"qty": "theoretical_depletion"}
    )

Step 2 — Difference against measured actual depletion

from decimal import Decimal

import pandas as pd


def depletion_variance(theoretical: pd.DataFrame, actual: pd.DataFrame) -> pd.DataFrame:
    df = theoretical.merge(actual, on="ingredient_sku", how="outer").fillna(Decimal("0"))
    df["depletion_variance"] = df["actual_depletion_canonical"] - df["theoretical_depletion"]
    return df

Step 3 — Flag the material variances

Rank by cost impact, not raw quantity, so a small variance on an expensive protein outranks a large one on flour.

from decimal import Decimal

import pandas as pd


def rank_by_cost(variance: pd.DataFrame, unit_costs: pd.DataFrame) -> pd.DataFrame:
    df = variance.merge(unit_costs, on="ingredient_sku", how="left")
    df["variance_cost"] = df.apply(
        lambda r: r["depletion_variance"] * Decimal(str(r["unit_cost"])), axis=1
    )
    return df.reindex(df["variance_cost"].abs().sort_values(ascending=False).index)

Verification and Validation

  • Zero-variance baseline. Feed sales whose theoretical depletion exactly equals measured movement; every depletion_variance must be 0, confirming units and joins agree.
  • Unit consistency. Assert both theoretical_depletion and actual_depletion_canonical are in the same canonical unit before differencing — a mismatch produces uniform, implausible variance.
  • Reconcile to cost. Sum variance_cost and confirm it equals the reconciliation ledger’s cost impact for the period.
  • Coverage. An outer join must surface a SKU that sold but showed no movement (possible feed gap) and one that moved but never sold (possible unlogged waste).

Gotchas and Edge Cases

  • validate="m:m" masking a bad BOM. A menu item joined to duplicate BOM rows silently doubles depletion. Confirm the BOM has one row per (menu_item_id, ingredient_sku) or aggregate it first.
  • Yield double-counting. If canonical_qty_per_unit already folds in yield (edible portion), do not apply a yield factor again here, or theoretical depletion understates actual by the yield ratio.
  • Fractional-cent cost drift. Keep depletion_variance and variance_cost in Decimal; ranking by a float cost impact reorders ties unpredictably across runs.
  • Period misalignment. Sales and stock movement must cover the exact same window. A sales feed that includes an extra day inflates theoretical depletion against a shorter measured movement.

For library specifics, see the official pandas documentation on merge validation.