Depletion allowance reconciliation, step by step

Depletion allowance reconciliation is the monthly job of proving that a distributor's DA invoice matches the deals your brand approved. Here is how area managers do it by hand, where the time goes, and what to automate first.

If you are new to the term, start with what a depletion allowance is. This guide assumes you have two things in front of you: the distributor's invoice for the month and the list of approved deals for that distributor in your trade spend platform. We use Tradeparency as the example because it is where many brands key these numbers, but the steps apply to any system that holds deal lines.

What "reconciled" actually means

A DA invoice is reconciled when three things are true:

1. Every invoice row is on a deal line you approved, at the price and DA rate on that deal.
2. The cases on each deal line add up to what the invoice shows for that product, price and rate.
3. The total your platform calculates equals the total the distributor billed, or every dollar of difference is explained.

Point three is the one that matters to finance. Points one and two are how you get there.

The manual process

Step 1. Pull the deal list for this distributor

Export or open the deal lines for the distributor and month. In Tradeparency this is the DA export, one row per deal line with brand, varietal, pack, deal description, price to retailer, DA and discount. Our Tradeparency DA export guide explains each column. Note the order of the lines: that is the order you will key in.

Step 2. Open the invoice and find the columns that matter

Find product, pack, price to retailer, DA rate and cases. Headers differ between distributors and can change from one month to the next at the same distributor, so check them every time. If the invoice has a total row, note it; it is your target number.

Step 3. Collapse the invoice into totals

An invoice often has one row per store, product and promotion. Build a pivot table with product, price and DA as rows and sum of cases as values. Hundreds or thousands of rows usually collapse to somewhere between a few dozen and a hundred product, price and DA combinations. Check that the pivot's grand total of cases times DA equals the invoice total before going further.

Step 4. Match each deal line to an invoice total

Walk the deal list top to bottom. For each line, find the pivot row with the same product, pack, price and DA. This is where most of the time goes, because the two sides rarely use the same names:

• The invoice says "Tea"; the platform says "Iced Tea".
• The invoice lists a 6/4 pack and a loose 24 of the same flavor, and your deal list has both a 24-4 and a 24-1 line at the same price.
• Variety packs are split by flavor on the invoice but sit on one variety pack line in the platform.
• A rate on the invoice is a few cents off the deal (3.50 against 3.55).

Each of these is a judgement call. Our guide to common DA invoice errors covers how to spot and handle each one.

Step 5. Key the case totals

Enter the cases on each deal line. Keep the pivot and the platform side by side, and mark each pivot row as used once it is on a line. A pivot row used twice is a double count; a pivot row never used is money on the invoice that no deal explains.

Step 6. Compare totals and explain the gap

Compare the platform's calculated total with the invoice total. If they differ, the difference is almost always one of three things:

• Unplaced rows: invoice rows you did not put on any line. List them with product, price, DA and cases.
• Rate differences: cases placed on a line whose DA differs from the invoice's DA. The dollar gap is cases times the rate difference.
• Keying errors: a transposed number or a total typed on the wrong line.

Step 7. Resolve, then approve

Decide what to do with each unexplained item: ask the distributor to correct a row, set up a missing deal, or accept a rate difference. Keep a short note of every decision, because the same question usually comes back next month.

Where the time goes

The area manager in our case study spent about three hours per distributor sheet doing this by hand. The time is rarely spent on arithmetic. It goes to:

• Rebuilding the pivot because the distributor changed its layout.
• Searching the pivot for each deal line, one line at a time, in a different order from the platform.
• Working out which of two similar products a line means, and remembering what you decided last month.
• Hunting for the last few hundred dollars when the totals do not tie.

What to automate first

If you build your own spreadsheet process, automate in this order: the pivot (step 3), then a lookup from deal line to pivot row (step 4), then a list of pivot rows not used by any line (step 6). That last one is the single most useful check, because it turns "the totals are off" into a short list of rows to look at.

Depletion Reconciler does all three. You drop the Tradeparency DA export and the invoice into the page; it reads invoice columns by meaning rather than position, groups cases by product, price and DA, and fills every deal line in Tradeparency's own order. Where a match is genuinely ambiguous, it asks once instead of guessing, and it remembers the answer for that distributor next month. The totals check lists every invoice row not on a line and every rate difference. Files are read in your browser and never uploaded.

It works alongside Tradeparency: you still key and approve in Tradeparency, just without the searching. For a real example, read our case study of an area manager with two distributors, and keep the month-end DA checklist next to your screen.

Tradeparency is a trademark of its owner. Depletion Reconciler is an independent tool and is not affiliated with or endorsed by Tradeparency or Andavi Solutions.