Community supermarket receiving - free-goods booking audit; match the buy-10-get-1 cartons and the on-invoice freebies against the receiving records, find out whether the free goods ever entered the system, and where the invoiced-but-unbooked difference went

You are a receiving auditor for a community
supermarket. Supplier free goods (buy-10-get-1,
on-delivery bonuses, rebate cartons) are one
line on the slip - but they are money exactly
like paying goods, because the supply price
already carries their cost. I will give you
two datasets; audit booking completeness for
free goods.

I will provide:
1. Delivery slips (a period, including free
   lines): date / supplier / item / paid
   quantity / free quantity (marked "free"
   or annotated in the quantity column)
2. System booking records (same period):
   date / supplier / item / booked quantity /
   booking type (purchase / free goods /
   other)
3. (Optional) free-goods sales or stock
   records for the same period. If absent,
   say so and skip what needs them

Three classes (criteria fixed):
A Booked: free lines on the slip matched to
  booking records (item, quantity, and date
  within tolerance)
B On slip, not booked: a free line on the
  slip with no matching booking - either the
  goods arrived and were never entered, or
  they never arrived at all
C Booked, not on slip: a free-goods booking
  with no free line on any slip - possibly a
  verbal deal booked later, possibly someone
  using the "free goods" type to adjust
  stock

Output:
- One master table: date / supplier / item /
  free qty on slip / booked qty / class
- B and C aggregated by supplier: difference
  in units, value at supply price (I will
  provide a supply-price column; without it,
  mark "value to estimate" - never invent a
  unit price)
- One verification line per B difference, in
  order: does that day's receiving record
  show the batch; did the free goods ride
  with the main load or arrive separately;
  what is this supplier's running difference
  this month
- One verification line per C difference:
  who booked it, which delivery it maps to,
  is there written vendor confirmation
- Close with a supplier ranking: by B-class
  cumulative value, descending; only the top
  [top N] are worth confronting first

Constraints:
- The audit does not guess where free goods
  went; no "taken" or "kept by staff" wording
  - a mismatch is a mismatch; destinations
  come from verification
- Near-identical item names you cannot
  confirm as one item ("cola 330ml" versus
  "cola 500ml") go to a "needs human
  confirmation" side table - never merge
  them for me
- Date tolerance is fixed: matches within
  [date tolerance] days count; beyond that,
  no match
- Categories without a supply price get
  "value to estimate" - no market-price
  substitutions
- No opening paragraph; start with the table

Why it is written this way

Free-goods booking gets ignored in stores because the ledger never shows a loss - every paying unit is accounted for. What goes missing is "things that should exist and don't": the supply price carries the free goods' cost, the buy-10-get-1 terms were negotiated, and whether they arrived or got booked has never once been reconciled. This audit's whole value is lifting the word "free" off the slip and treating it as business: free goods and purchases are two halves of one payment, and auditing only the purchases reconciles only half the account.

The most overlooked class is C, "booked but not on slip": most people only check "on slip, unbooked" (goods that vanished). The reverse matters just as much - it may be a legitimate policy booking, or someone using the free-goods type to paper over a count variance. The audit doesn't decide which; it surfaces the line and asks "who booked it, against which delivery" - ask the right question and the answer produces itself.

Value conversion is locked to supply price with "to estimate" as the fallback, to block two model habits: computing at retail (free goods' cost sits in the supply price; retail math inflates the problem) and inventing a "market price." An audit's power is credibility, and one fabricated unit price contaminates the whole table. For the same reason the supplier ranking takes only the top N: confronting suppliers costs time, so the fire concentrates on the biggest differences - the ranking exists to decide who to see first, not to produce a full listing nobody finishes reading.

Near-identical names quarantined in a side table and dates governed by a tolerance parameter both answer to dirty real-world data: "cola 330ml x24" on the slip and "cola cans" in the system are usually one item, but if the audit merges them for me and gets it wrong once, that supplier's entire difference is wrong. Tolerance is a parameter rather than a constant because reconciliation rhythms differ - a depot on weekly cycles may legitimately be days apart, a local vendor should match same-day.

Variables