Theoretical Vs Actual Food Cost Calculation

Variance Mapping Methodologies

Variance mapping is the discipline of proving why the food that left inventory does not match the food the recipes say should have left inventory — and doing it precisely enough that a machine, not a controller with a spreadsheet, can route each dollar of discrepancy to its cause. Within the broader Theoretical vs Actual Food Cost Calculation framework, this guide isolates one sub-problem: how to take a clean theoretical baseline and a stream of actual depletion, subtract them safely, and turn the raw delta into a classified variance that an operator can act on. Multi-unit operators, food-tech developers, and culinary managers all read the same number differently — one sees a margin leak, one sees a data bug, one sees a line cook over-portioning — so the mapping engine’s job is to remove the ambiguity. At its core sits a discrete, repeatable sync pattern: unit-of-measure (UOM) normalization, followed by bill-of-materials (BOM) to point-of-sale (POS) reconciliation, followed by deterministic classification into named, routable buckets.

Concept Definition and Data Contract

Variance mapping consumes two prepared inputs and emits exactly one classified output per ingredient per period. Keeping that contract narrow is what makes the pipeline auditable.

Theoretical usage — the quantity the recipes say should have been consumed, produced upstream by the theoretical cost engine described in calculating theoretical food cost from BOMs. Each row arrives keyed by (ingredient_id, location_id, period_start, period_end) with a theoretical_usage weight in a canonical base unit (grams or milliliters). Nothing about cost travels on this row; mapping works in physical quantities and prices the delta only at the end.
Actual depletion — the quantity that physically left the building, derived from perpetual inventory movement or invoice receipts, keyed the same way. This is opening_count + received − closing_count in the same canonical base unit.
Optional signal flags — POS modifier events (comps, voids, 86’d items) and inventory cycle-count confirmations, which raise classification confidence but must never be required for the pipeline to produce a result.

The single output is a ReconciliationRecord: theoretical_usage, actual_usage, a signed raw_variance, and a category drawn from a closed enum. Two schema constraints are load-bearing. First, every quantity is a Decimal, never a float — a mapping run touches thousands of line items per location per day, and IEEE-754 drift accumulates into phantom variance that is indistinguishable from real shrinkage. Second, both inputs must already be expressed in the same canonical unit; the reconciliation stage does not perform conversion. That responsibility belongs to the normalization stage, and conflating the two is the most common source of silent corruption in this domain.

Architecture Decision Rationale

The central design choice is a three-stage deterministic pipeline — normalize, reconcile, classify — rather than a single fused calculation or a machine-learned anomaly detector. Three alternatives were considered and rejected.

A fused subtract-and-flag approach (compute theoretical − actual, flag anything over a fixed dollar threshold) is trivial to build and impossible to trust: without normalization it compares grams to pounds, and without classification it tells an operator that something is wrong but never what, which is the only thing that drives action. A statistical / ML anomaly detector trained on historical variance is seductive but non-deterministic — the same inputs can produce different verdicts across model versions, which destroys the audit trail a finance function requires and makes a disputed variance unarguable. A per-recipe reconciliation (map variance at the finished-dish level instead of the ingredient level) hides the signal: a dish can land on-cost while its expensive protein is over-portioned and its cheap starch is under-portioned, the two errors cancelling in the aggregate.

Ingredient-level, deterministic mapping wins because it is explainable and reproducible: given the same two input rows and the same tolerance band, the pipeline always returns the same category, and every category maps to a concrete downstream owner. Normalization is separated from reconciliation because the two failure modes are different — a bad conversion constant is a reference-data problem, a misaligned time window is a scheduling problem — and fusing them makes both undiagnosable. Classification is separated from reconciliation because tolerance bands and routing rules change on a business cadence (seasonality, menu engineering) far faster than the arithmetic ever does.

Phase 1 — Deterministic UOM Normalization

The pipeline begins by collapsing three unit vocabularies into one canonical base unit: recipe BOM lines (grams of trimmed protein, milliliters of reduced sauce), purchasing records (pounds, gallons, cases), and POS modifier logs (each, portion). Culinary teams speak in prepared units; procurement speaks in bulk units; the two only reconcile after a conversion layer applies yield factors, trim percentages, and density constants. That conversion is where the yield factor calculation frameworks that translate raw purchase weights into usable portions plug directly into the mapping engine, and it depends in turn on a clean join between sales items and ingredients supplied by the POS taxonomy mapping layer. Get this stage wrong and every downstream delta is noise.

Normalization must be executed in fixed-point arithmetic and sourced from a centralized, version-locked reference table — conversion constants are validated against vendor specification sheets and frozen per fiscal period so that a mid-period supplier change cannot silently rewrite history.

from __future__ import annotations

from dataclasses import dataclass
from decimal import Decimal, getcontext, ROUND_HALF_UP

getcontext().prec = 28
getcontext().rounding = ROUND_HALF_UP


@dataclass(frozen=True)
class UOMConversion:
    """A version-locked conversion from a source unit to the canonical base unit."""

    ingredient_id: str
    source_uom: str
    target_uom: str          # canonical base unit, e.g. "g" or "ml"
    density_factor: Decimal  # source-unit -> base-unit mass/volume constant
    trim_pct: Decimal        # fraction lost to trim/prep, 0 <= trim_pct < 1
    yield_factor: Decimal    # cooking/reduction yield, > 0


def normalize_uom(raw_qty: Decimal, conversion: UOMConversion) -> Decimal:
    """Convert a raw quantity into the canonical base unit.

    Order is significant: convert units first, remove trim loss, then apply
    the cooking yield. Guards make the two divide-by-zero-class errors
    (negative trim, non-positive yield) loud instead of silent.
    """
    if not (Decimal("0") <= conversion.trim_pct < Decimal("1")):
        raise ValueError(f"trim_pct out of range for {conversion.ingredient_id}")
    if conversion.yield_factor <= Decimal("0"):
        raise ValueError(f"yield_factor must be positive for {conversion.ingredient_id}")

    if conversion.source_uom == conversion.target_uom:
        return raw_qty

    in_base = raw_qty * conversion.density_factor
    after_trim = in_base * (Decimal("1") - conversion.trim_pct)
    return after_trim * conversion.yield_factor

The guards are not defensive decoration: a trim_pct of 1 silently zeroes an ingredient, and a yield_factor of 0 is a divide-by-zero waiting to happen the moment the constant is inverted elsewhere in the stack. Failing fast here quarantines the reference-data error instead of laundering it into a plausible-looking variance downstream.

Phase 2 — Reconciliation and Variance Classification

With both inputs in the canonical unit, the reconciliation stage executes over a strict temporal boundary. Sales data, inventory snapshots, and invoice timestamps must be aligned to a single UTC offset before aggregation; a window that is even an hour misaligned bleeds one shift’s sales against another shift’s counts and manufactures variance that no amount of downstream analysis can resolve. Within the aligned window the engine aggregates POS-projected theoretical consumption, subtracts actual movement, and produces a signed raw_variance.

Raw variance is necessary but not actionable — a signed number carries no owner. Classification converts it into one of a closed set of buckets: within_tolerance, portion_drift, prep_waste, spoilage, or unrecorded_comp. The rules are deterministic and ordered so that high-confidence signals dominate heuristics: an explicit POS comp flag outranks any inference from the sign of the delta. The tolerance band itself is not a constant baked into this stage — it is governed separately by the threshold tuning for alerts layer, and portion-related tolerances inherit directly from the specs set in portion size standardization, which is what lets the engine tell intentional comping apart from a line cook’s execution drift.

from enum import Enum
from typing import Optional


class VarianceCategory(Enum):
    PORTION_DRIFT = "portion_drift"
    PREP_WASTE = "prep_waste"
    SPOILAGE = "spoilage"
    UNRECORDED_COMP = "unrecorded_comp"
    WITHIN_TOLERANCE = "within_tolerance"


@dataclass
class ReconciliationRecord:
    ingredient_id: str
    theoretical_usage: Decimal
    actual_usage: Decimal
    raw_variance: Decimal
    category: Optional[VarianceCategory] = None


def classify_variance(
    raw_var: Decimal,
    tolerance_threshold: Decimal,
    pos_modifier_flag: bool = False,
    inventory_cycle_match: bool = True,
) -> VarianceCategory:
    """Deterministic, ordered classification. Explicit flags beat inference."""
    if abs(raw_var) <= tolerance_threshold:
        return VarianceCategory.WITHIN_TOLERANCE
    if pos_modifier_flag:
        return VarianceCategory.UNRECORDED_COMP
    if raw_var < Decimal("0") and inventory_cycle_match:
        return VarianceCategory.SPOILAGE
    if raw_var < Decimal("0"):
        return VarianceCategory.PREP_WASTE
    return VarianceCategory.PORTION_DRIFT

Phase 3 — Reconciliation Run and Downstream Handoff

The two stages compose into a single pass that reads prepared sales rows and inventory movements, normalizes, reconciles, classifies, and emits the record set. The consumer of that set — an alerting job, a materialized reporting view, or a work-queue publisher — never re-traverses the recipe graph; it reads the flat ReconciliationRecord stream and acts on the category.

from typing import Iterable, Mapping, Sequence


def run_reconciliation(
    sales_rows: Sequence[Mapping[str, object]],
    inventory_movements: Mapping[str, Decimal],
    uom_map: Mapping[str, UOMConversion],
    tolerance: Decimal = Decimal("0.05"),
) -> list[ReconciliationRecord]:
    """Normalize -> reconcile -> classify for one aligned period window."""
    records: list[ReconciliationRecord] = []
    for row in sales_rows:
        ing_id = str(row["ingredient_id"])
        conversion = uom_map.get(ing_id)
        if conversion is None:
            # Unmapped ingredient: route to quarantine, never to variance.
            continue

        sold_qty = Decimal(str(row["qty"]))
        theoretical = normalize_uom(sold_qty, conversion)
        actual = inventory_movements.get(ing_id, Decimal("0"))
        raw_var = theoretical - actual

        records.append(
            ReconciliationRecord(
                ingredient_id=ing_id,
                theoretical_usage=theoretical,
                actual_usage=actual,
                raw_variance=raw_var,
                category=classify_variance(
                    raw_var,
                    tolerance,
                    pos_modifier_flag=bool(row.get("is_comp", False)),
                    inventory_cycle_match=bool(row.get("cycle_verified", True)),
                ),
            )
        )
    return records

Routing is where classification earns its keep. A consistent negative variance mapped to a high-yield protein is published as a ticket into the waste tracking and routing systems so a culinary manager can audit prep logs and adjust par levels; a portion_drift verdict is routed back to line-level coaching against the standardized spec rather than to procurement. For a fleet of locations, this handoff is best fanned out through the async batch processing workflow so a slow location never blocks the rest, and each location’s records land keyed to its own cost center via the multi-location cost center architecture.

Production Hardening

A reconciliation run is a scheduled batch job that must be safe to re-run, so idempotency comes first: key every emitted record on (ingredient_id, location_id, period_start) and upsert on that key. A retried run after a partial failure then overwrites rather than doubles, and an at-least-once queue redelivery cannot inflate reported variance. Where records are published to a downstream consumer, carry the same key so the consumer can deduplicate independently.

Memory is bounded by processing one location-period at a time and streaming sales_rows rather than materializing a fleet-wide list; the reference uom_map is the only structure that stays resident, and it is small. If a vectorized implementation is preferred for a large location, do the join and subtraction as a single merge/groupby over Decimal-typed columns — never a row-by-row loop — and quantize once at the boundary.

Unit-normalization hooks belong at ingestion, not scattered through the reconciliation body: every quantity crosses exactly one normalize_uom boundary and is canonical from that point forward. Structured logging with a per-run correlation id lets an operator trace a disputed variance from the emitted record back through the exact conversion constants and window bounds that produced it. Finally, when primary reconciliation cannot run — a missing POS extract, a delayed invoice ingest — activate a rolling seven-day-average fallback to preserve pipeline continuity, but stamp the record’s source so a fallback is never silently trusted as a live reading, and raise the gap for manual review.

Failure Modes and Troubleshooting

Symptom	Root cause	Resolution
Every ingredient shows large variance	Grams compared against pounds — normalization skipped or misconfigured	Assert both inputs are in the canonical unit before subtraction; audit the `uom_map` coverage.
Variance appears and vanishes across reruns	Misaligned time window bleeding one shift into another	Align sales, counts, and invoices to a single UTC offset before aggregating.
Costs drift by fractions of a cent	Float arithmetic somewhere in the path	Use `Decimal` end to end; quantize once at each boundary.
A comp is classified as spoilage	Inference ran ahead of the explicit POS flag	Keep classification ordered — `pos_modifier_flag` must be checked before sign-based rules.
An ingredient silently produces no record	Missing `uom_map` entry dropped the row	Route unmapped ingredients to a quarantine queue and alert; never let them vanish.
Reported variance doubles after an outage	At-least-once redelivery without a dedupe key	Upsert on `(ingredient_id, location_id, period_start)`; confirm exactly-once consumer config.
Dish looks on-cost but margin leaks	Aggregation hid offsetting ingredient errors	Map at the ingredient level, not the finished-dish level.

The through-line of every failure is the same: a variance number is only trustworthy when its two inputs were canonicalized, time-aligned, and computed in fixed point before they were subtracted, and only actionable when the resulting delta is classified deterministically into a bucket that names its owner. Hold those invariants and variance mapping stops being a monthly forensic exercise and becomes a live control surface — one that couples clean theoretical baselines with governed thresholds and routed remediation, moving multi-unit operators from reactive cost auditing to proactive margin management. For the fixed-point arithmetic standards this pipeline depends on, consult the official Python decimal documentation.