SC ReconciliationReconciliation Automation
  1. Home
  2. Matching
  3. Setting Quantity and Price Tolerance Windows
  4. Choosing Absolute vs Percentage Tolerance Floors

Choosing Absolute vs Percentage Tolerance Floors

↑ Part of Setting Quantity and Price Tolerance Windows.

A single tolerance rule cannot serve a catalog that spans thirty-cent labels and eighty-thousand-dollar capital assemblies. Configure a percentage-only band and the label tier collapses to sub-cent tolerances that flag every rounding artifact as an exception; configure a flat absolute floor generous enough to keep that noise down and the same number, applied without a ceiling, becomes an enormous relative window on a low-order-quantity capital line — enough to wave through a five-figure discrepancy without a second look. This page is narrowly about that one decision: absolute floor, percentage band, or the combined gate that takes whichever number binds tighter at the low end and whichever number binds looser-but-capped at the high end, so the same configuration behaves correctly across the whole value distribution instead of only the tier it was tuned against.

Operational Trigger Signals

Move from a single-mode floor to the combined gate when your own exception telemetry shows one of these measurable conditions, rather than switching pre-emptively:

  1. Low-value-tier exception share exceeds 25%. More than a quarter of PRICE_OVER / PRICE_UNDER exceptions originate from SKUs under a fixed low-value threshold (commonly $5–$10 per line), and the large majority clear on review as invoice rounding, not real variance. That is a percentage-only band producing sub-cent noise.
  2. Audit sampling finds a large-dollar auto-approval inside a flat floor. An internal or SOX control sample turns up a matched line where the price delta cleared only because it sat under a flat absolute number that was never checked against the line’s own value — a floor sized for cheap SKUs quietly covering an expensive one.
  3. Catalog value ratio exceeds roughly 1000:1. When the cheapest and most expensive SKUs on the same tolerance rule differ by three or more orders of magnitude, no single flat number and no single percentage rate can be correctly sized for both ends simultaneously.
  4. Low-order-quantity, high-unit-price lines dominate a supplier’s exception profile. Capital equipment and precision-component purchase orders are typically placed in single-digit quantities; a flat unit-count or flat dollar floor calibrated against high-volume bulk SKUs is, on these lines, a blank check rather than a tolerance.

If none of these hold and your catalog sits inside a narrow value band, a single absolute or percentage window — as covered in Setting Quantity and Price Tolerance Windows — is simpler to operate and audit. Add the combined gate only when the value spread actually punishes a flat rule.

Step-by-Step Implementation

Build the floor as three independently-calibrated numbers per tier, then combine them with a single deterministic function rather than branching logic scattered through the pipeline:

  1. Segment SKUs by value tier. Join the reconciliation frame to catalog cost and bucket lines (e.g., under $5, $5–$500, $500–$10,000, over $10,000) so each tier’s exception history can be inspected independently before you touch a single threshold.
  2. Set the absolute floor from the cheapest tier’s noise. Pull 60–90 days of clean, review-cleared exceptions on your lowest-value SKUs and set abs_floor just above the widest recurring rounding artifact — freight apportionment and multi-pack unit-price division are the usual sources.
  3. Set the percentage rate from mid-catalog variance. For the tier where dollar and percentage tolerances roughly agree, derive pct_rate from observed price variance the same way Configuring Dynamic Price Tolerance Thresholds derives its baseline band — this rate is what should govern the bulk of your catalog.
  4. Set the absolute cap from audit risk appetite, not convenience. abs_cap is a policy decision, not a statistic: it is the largest dollar variance a line can auto-clear regardless of how generous the percentage looks on an expensive SKU. Set it with finance sign-off, and treat any change as a reviewable diff.
  5. Implement the combined gate as one pure function. Compute all three components in Decimal, apply the min/max composition below, and log the binding rule per line so the audit trail records why a line auto-reconciled, not just that it did.
  6. Backtest against the exception log before promotion. Replay a shadow run and confirm the crossing points land where step 1’s tier boundaries predicted; a floor or cap that never binds on real data is miscalibrated, not merely unused.

The acceptance test composes the floor and the cap as a single expression. Given a price delta δ\delta, a line value vv, an absolute floor τfloor\tau_{\text{floor}}, and a percentage rate π\pi, the baseline combined test is:

accept=∣δ∣≤max⁡ ⁣(τfloor,  πv)\text{accept} = |\delta| \le \max\!\bigl(\tau_{\text{floor}},\; \pi v\bigr)

This alone fixes the cheap-SKU noise problem: below the crossing point where πv<τfloor\pi v < \tau_{\text{floor}}, the flat floor takes over and stops rejecting sub-cent rounding. It does not yet fix the expensive-SKU exposure problem, because πv\pi v grows without bound as vv grows. Adding the cap with min closes that gap — the effective tolerance a line receives is:

τeff=max⁡ ⁣(τfloor, min⁡(πv, τcap)),accept=∣δ∣≤τeff\tau_{\text{eff}} = \max\!\Bigl(\tau_{\text{floor}},\ \min\bigl(\pi v,\ \tau_{\text{cap}}\bigr)\Bigr), \qquad \text{accept} = |\delta| \le \tau_{\text{eff}}

Three regions fall out of this expression automatically: at low vv, τfloor\tau_{\text{floor}} dominates and the window stays flat; at moderate vv, πv\pi v sits between the two bounds and the window scales linearly with value; at high vv, τcap\tau_{\text{cap}} dominates and the window flattens again, bounding the dollar exposure a single line can clear without review.

Effective tolerance vs SKU line value, showing where the floor, percentage, and cap each bind The horizontal axis is SKU line value from ten dollars to one hundred thousand dollars across five tick marks. The vertical axis is the resulting dollar tolerance. A solid line shows the combined gate's effective tolerance: flat at the absolute floor for cheap SKUs, rising diagonally as the percentage rate binds across mid-catalog SKUs, then flat again at the absolute cap for expensive SKUs. A dashed line shows a pure percentage-only band with no floor or cap: it dips below the solid floor segment at the cheap end, which is the sub-cent noise problem, and climbs above the solid cap segment at the expensive end, which is the unbounded auto-approval problem the cap exists to prevent. $10 $100 $1K $10K $100K SKU line value Tolerance ($) floor binds pct · value binds cap binds cheap SKUs mid-catalog capital equipment uncapped pct-only: unbounded exposure

The dashed line in the diagram is a pure percentage-only rule with the same rate as the mid-catalog segment — no floor, no cap. It sits below the solid curve on the left, which is the sub-cent-noise failure, and climbs above it on the right, which is the auto-approval failure: at $80,000 a 2% band is $1,600, comfortably wide enough to absorb a genuine overcharge that a $500 cap would have routed to review.

Implement the gate itself in Decimal so the comparison never trips on binary-float rounding at a crossing point:

PYTHON
import logging
from dataclasses import dataclass
from decimal import Decimal
from typing import Optional

import pandas as pd

logger = logging.getLogger("recon.match.tolerance.floor")

FOUR_PLACES = Decimal("0.0001")


@dataclass(frozen=True)
class FloorConfig:
    """Version-controlled absolute/percentage/cap parameters for one commodity tier."""
    abs_floor: Decimal            # minimum dollar tolerance regardless of value
    pct_rate: Decimal             # fraction of line value, e.g. Decimal("0.02")
    abs_cap: Optional[Decimal] = None  # hard ceiling; None disables capping


def _as_decimal(value: object) -> Decimal:
    """Route every numeric through str() first so floats never enter Decimal directly."""
    return Decimal(str(value)).quantize(FOUR_PLACES)


def effective_tolerance(value: Decimal, cfg: FloorConfig) -> tuple[Decimal, str]:
    """Return (tau_eff, binding_rule) for max(abs_floor, min(pct*value, abs_cap))."""
    pct_amount = (value * cfg.pct_rate).quantize(FOUR_PLACES)
    if cfg.abs_cap is not None and pct_amount > cfg.abs_cap:
        return cfg.abs_cap, "cap"
    if pct_amount > cfg.abs_floor:
        return pct_amount, "pct"
    return cfg.abs_floor, "floor"


def evaluate_floor_gate(
    df: pd.DataFrame,
    cfg: FloorConfig,
    value_col: str = "line_value",
    delta_col: str = "price_delta",
) -> pd.DataFrame:
    """Flag each reconciliation line against the combined absolute/percentage floor.

    df expects [value_col, delta_col] as numeric or numeric-string columns.
    Adds 'tolerance_applied', 'binding_rule', and 'auto_reconcile' to the frame;
    no row is dropped so the residual stays available for exception routing.
    """
    out = df.reset_index(drop=True).copy()
    tolerances: list[Decimal] = []
    rules: list[str] = []
    accepts: list[bool] = []

    for value_raw, delta_raw in zip(out[value_col], out[delta_col]):
        value = _as_decimal(value_raw)
        delta = _as_decimal(delta_raw)
        tau_eff, rule = effective_tolerance(value, cfg)
        tolerances.append(tau_eff)
        rules.append(rule)
        accepts.append(abs(delta) <= tau_eff)

    out["tolerance_applied"] = [str(t) for t in tolerances]
    out["binding_rule"] = rules
    out["auto_reconcile"] = accepts

    binding_counts = pd.Series(rules).value_counts().to_dict()
    logger.info(
        "floor_gate rows=%d auto_reconcile=%d binding=%s abs_floor=%s pct_rate=%s abs_cap=%s",
        len(out),
        sum(accepts),
        binding_counts,
        cfg.abs_floor,
        cfg.pct_rate,
        cfg.abs_cap,
    )
    return out

Logging binding_rule alongside the pass/fail flag is the detail most single-mode implementations skip. It answers the question an auditor asks next — not just “did this line clear tolerance” but “which number let it clear” — and it is exactly the counter you check during calibration to confirm the floor and cap in step 6 above are binding on real traffic, not sitting unused because the crossing points were guessed wrong.

Configuration Reference

Calibrate per commodity tier, not once globally — a fastener tier and a capital-equipment tier should never share a FloorConfig, even if they happen to route through the same function.

SKU value tier Absolute-only behavior Percentage-only behavior Combined gate behavior
Under $5 (labels, fasteners) Fine if floor tuned here; a blank check if tuned for the capital tier instead 2% of $2 is $0.04 — narrower than invoice rounding, floods the queue abs_floor binds; small, deliberate window
$5–$500 (mid-catalog components) Roughly workable but arbitrary — no principled reason for the flat number Reasonable; percentage tracks real variance pct_rate · value binds; scales with the line
$500–$10,000 (subassemblies) Too tight if the floor was sized for the cheap tier — creates its own noise Still reasonable in isolation pct_rate · value binds
Over $10,000 (capital equipment, bulk-commodity lines) Unstable: too loose if the floor was sized generously, too tight if not Wide dollar band; can silently clear a real overcharge abs_cap binds; bounds the blind spot
Parameter Accepted values Notes
abs_floor $0.10 – $10.00 Derived from the cheapest tier’s clean rounding noise, not guessed
pct_rate 0.5% – 4% Derived from mid-catalog observed price variance, per commodity
abs_cap $50 – $2,000 Policy ceiling; requires finance sign-off, pinned to the batch audit record
binding_rule floor | pct | cap Logged per line; drives calibration review, never business logic

Debugging & Recovery

  • Crossing points that never move. If binding_rule is always floor or always cap for an entire tier, the boundary was mis-set for that segment’s actual value range — re-derive it from step 1’s tier data instead of nudging the constant.
  • Decimal mismatch at the exact boundary. A delta that equals tau_eff to the fourth decimal place should pass (<=, not <); confirm both value and delta are quantized to the same precision before comparison, or floating-point-adjacent string parsing will produce an off-by-one-cent false exception, the same class of failure covered for the wider tolerance evaluator in Setting Quantity and Price Tolerance Windows.
  • Cap binding on lines that should have failed. If abs_cap is clearing exceptions an analyst expected to see, the cap was set from convenience rather than audit risk appetite — treat any abs_cap change as a signed-off, version-controlled diff, the same discipline applied to volatility multipliers in Configuring Dynamic Price Tolerance Thresholds.
  • Currency-mixed catalogs. value and delta must already be in a single settlement currency before this gate runs; cross-currency lines belong to the FX buffer described in Multi-Currency Reconciliation Frameworks, not to a wider abs_cap.
  • Key linkage confused with tolerance. A line that never reaches this gate is a matching failure, not a tolerance failure — verify it cleared record linkage first, using the same exact-then-fuzzy discipline in When to Use Fuzzy Matching Over Exact PO Matching, before assuming the floor configuration is at fault.

FAQ

Why not just use one flat absolute floor and size it generously?

Because “generously” is relative to whichever SKU value you size it against, and a single flat number cannot be correctly sized against two ends of a value distribution at once. Size it for your cheapest tier and it is too tight for capital equipment, generating noise there instead. Size it for your capital-equipment tier and the same number becomes a wide-open window on cheap SKUs — a variance many multiples of the line’s own value clears without review. The combined gate exists specifically so you never have to pick one end to sacrifice.

What should the absolute cap be set to if I don’t have historical exception data yet?

Start conservative and derive it from acceptable dollar risk, not from a percentage of your largest expected line. A common starting point is the smallest dollar amount that would trigger a manual review under your existing approval-matrix policy for a standalone adjustment — reuse that number rather than inventing one, since it is already signed off. Tighten it further once the backtest in step 6 shows how often it actually binds; a cap that never binds on real traffic is set too loose to be doing any work.

Does the combined gate replace the quantity tolerance too, or only price?

The formula and code here are written for price, but the same max(floor, min(rate · basis, cap)) composition applies to quantity if you substitute ordered quantity for line value and a unit-count floor for the dollar floor — the failure mode is identical: a flat unit-count floor is negligible dollar exposure on a bulk fastener order and a large one on a low-order-quantity capital line. Run the two axes as separate FloorConfig instances rather than reusing one config for both, since their floors and caps are calibrated from unrelated data.