Why a rebate reads $0 and nothing errors: six silent causes

A rebate line that reads $0 rarely draws attention. The calculation ran, the period closed, nothing turned red, and the row looks like a quiet quarter. Often it was not. The calculation ran correctly over a set of purchases that turned out to be empty, or against a condition the setup could never satisfy, and money both sides agreed to goes unclaimed because nobody had a reason to look.

In our work building rebate calculation software, “why is this $0” is the question we have had to answer most often. Nearly every case traces to one of six causes, listed here in the order to check them: each is cheaper to rule out than the next. A spreadsheet fails in exactly the same ways.

Two free checks first

Check how fresh the purchase data is. A rebate figure reflects purchases as of the last time the calculation ran or the extract was pulled. Invoices loaded since then are not in it.

Then write the rebate down as one sentence: which purchase record it measures, for which products, at which facilities, against what threshold, on what condition. Each clause is one of the checks below. If your calculation leaves a note per period saying what it looked for and what it found, read that first.

1. The rebate measures the wrong purchase record

The same commerce leaves several records: the purchase order, the receipt, the invoice, and the traced sales a distributor reports having sold you on a manufacturer’s behalf. Some programs pay on a procedure log instead. A rebate term measures exactly one of these. Point it at the wrong one and it finds nothing, in every period, with no error.

Two shapes recur. The first is a manufacturer rebate on products you buy through a distributor: your invoices carry the distributor’s name, so a filter on the manufacturer returns nothing, while the purchases sit in the distributor’s tracing report. The second is an administrative fee program pointed at the fee statement instead of the purchases the fee accrues on.

The check is a count. Total the contract suppliers’ rows and dollars in each source for the period and put them side by side. Conclude that data is missing, and ask for a load, only after ruling out this cause and the two scope causes below.

2. A product tag or category that no product carries

Many rebates apply to a subset of the contract, picked out by a tag or category label on the item list. A label that matches nothing is not an error anywhere. An include rule whose label no product carries measures $0. An exclude rule whose label matches nothing excludes nothing, so the base is wider than the agreement says and the figure runs high while looking healthy.

Separate the near miss from the unmapped label. “IV Solution” against “IV Solutions”, a trailing space, or different capitals will defeat an exact-match lookup, and the space is invisible on screen. Count products carrying the exact label. If the count is zero, search for the label as a fragment. Fragment hits mean a spelling to correct. No hits mean the products were never tagged, or the rule needs a different scope.

3. A facility scope that resolves to nothing

Agreements name facilities. Purchase records carry ship-to codes, cost centers or purchasing locations. A facility scope works only through the mapping between them, and it breaks in three ways:

• A named facility has no purchasing locations mapped to it.
• The name in the agreement matches no facility record, for instance an acquired hospital listed under its former name.
• On a program paid per procedure, facilities match on where the case happened, not where the product was bought. A hospital with no procedure locations recorded matches no cases.

In all three the fix is the facility record, not the rebate rule. Do not remove the facility filter to make a number appear. A scope that quietly widens to every site books money the agreement does not cover, which is worse than a zero.

4. A gating commitment that is unmet, unattested or not yet calculated

“Earn 2%, provided you maintain the commitment” means a failed commitment moves the whole period’s rebate from earned to lost. That is the clause working. The question is which of three situations you are in:

• Missed. The commitment was measured and fell short. If the commitment is annual, later purchases can still bring the year into compliance, so read closed quarters again when they do.
• Never attested. Some commitments cannot be measured from purchase data: a signed participation form, a standardization declaration, good standing under the agreement. They are met when a person records that they are, and read unmet until then. In our experience this is the most frequent reason a rebate is lost in every period.
• Not yet calculated. The rebate ran before the commitment did. A gate should withhold in that case, not assume the condition was met. Calculate commitments first, then rebates.

In a spreadsheet this is a gate formula pointing at a blank cell. Treat blank as “not met”.

5. A ladder that is never reached

The rebate measures the right purchases and they still land on no tier, for one of four reasons.

Packs counted as one. Quantity is recorded as invoiced, so a box of 25 is one unit. If the agreement writes its threshold in eaches, multiply by pack size first. Take an illustrative program on a wound closure category: 10,000 eaches per contract year earns 5% of purchases, and the year’s purchases total $400,000.

Invoice line	Quantity as invoiced	Eaches per pack	Eaches
Box of 25, 300 boxes	300	25	7,500
Box of 10, 200 boxes	200	10	2,000
Singles, 1,000 units	1,000	1	1,000
Total	1,500		10,500

Counted as invoiced, the year reads 1,500 units, misses 10,000, and earns $0. Counted in eaches it reads 10,500, qualifies, and earns 5% of $400,000, which is $20,000. Same purchases, same dollars. The fix is product data: a base unit and an eaches-per-pack figure on every item in scope, because an item with no pack size counts one for one. If the agreement counts cases, leave the count alone.

Escalated thresholds. Some agreements raise the tier thresholds each contract year in line with the supplier’s price adjustment. A $2,000,000 threshold with a 4% annual adjustment is $2,080,000 in year two and $2,163,200 in year three. Year-two spend of $2,050,000 clears the written figure and misses the real one.

A capped top tier. If the top band is entered with a maximum, spend above it falls off the table and earns nothing, not the top rate.

A gap between bands. Spend that lands between one band and the next earns nothing. Check that the bands meet.

6. A market share that cannot be read, or reads against the wrong market

A share divides what you bought from the supplier by what you bought in the whole market. The denominator is a definition somebody chose, and it fails silently in both directions.

If the share cannot be read at all, because the commitment has not been calculated or is really a spend commitment typed as a percentage, nothing should book. A missing share is not 0% paid at the bottom rate, and it is not “met”.

If it can be read, look at the market. Take an illustrative 80% share commitment on endoscopic clips with $200,000 of on-contract purchases in the period.

The market is defined as	Market total	Share	Reading
Only the contract’s own product list	$200,000	100%	Passes whatever you buy
The whole purchasing category	$20,000,000	1%	Cannot pass in practice
The clips’ own product class, all makers	$240,000	83%	What the agreement meant

Classification codes make the too-wide error easy to commit. UNSPSC, as its Wikipedia entry describes, is a four-level hierarchy coded as eight digits, two per level: segment, family, class, commodity. Match the market on two digits too few and a product class becomes the whole family it sits in, with every other class in that family counted as competing purchases.

The too-narrow error is not a $0, but it is the same mistake. A market holding only the supplier’s own products divides a number by itself and reads 100% in every period, so everything gated on it pays unconditionally, and nobody investigates a rebate that is paying. A quieter variant: competitor items were listed by manufacturer part number, but the match runs on catalog number, so those rows match nothing.

Whatever you change, apply the same definition to both halves of the fraction, then read the share again.

Two cases where $0 is the right answer

Interim periods of a year-end settlement. A true-up measures the whole contract year and settles once, in the final period. Quarterly rows reading $0 beside a year-end figure are the structure working as written.

A rebate outside its own window. A term with its own date range, or one tied to particular pay periods, is not calculated elsewhere. There is no earning and no missed opportunity in those periods.

Why the zero should stay visible

All six causes share a design choice worth keeping: when something the calculation needs is missing, fail closed. A missing mapping, pack size, signature or market books $0 and gets flagged. It does not widen a scope or assume a condition was met. In a spreadsheet, the habit to drop is wrapping every lookup in an error handler that returns 0, which turns a loud failure into a quiet one.

A visible zero is fixable. A confident wrong number is found by the supplier, whose own calculation will disagree with yours after finance has accrued the money. None of the six involves the supplier doing anything wrong. Every cause sits on the buyer’s side: a mapping, a label, a pack size, a signature. Software can run these checks on every calculation; a spreadsheet needs them on a calendar.

A test to run this month

1. List every rebate term that earned $0 in two or more consecutive closed periods.
2. Strike the expected zeros: settlement interims and terms outside their window.
3. Work each remaining term through the six checks in order, stop at the first failure, and record the cause.
4. Separately, list every share that reads exactly 100%, or under a third of its commitment, and open the denominator for each.
5. After a fix, recalculate closed periods too, and check how far back the agreement lets a corrected claim reach.

Two rules of thumb for triage. A zero in every period is setup; a zero in one period is buying. A miss by a few points is behavior; a miss by an order of magnitude is a definition.