The request came in that July, quoted verbatim: "Can you add a rebate calculation module?" We went to watch how finance actually worked, came back, and didn't add a module.
How They Worked Before
Over 500 stores, with rebates calculated monthly by category and tier. The system could export two detail sheets: shipments and payments received. Three people in finance copied both into one workbook, matched them by store number with VLOOKUP, then checked the differences by hand, highlighting anything that didn't reconcile. Two days a month, eyes glazed over.
The problem wasn't that the system couldn't compute it — it was that the exports were unusable: shipments one row per document, payments one row per voucher, a dozen-plus rows per store. Even a pivot table couldn't handle the drag.
We Changed the Export, Not the Feature Set
We added a "rebate flat table" export: one sheet, a fixed 9 columns — store ID, store name, month, category, shipment amount, payment received, rebate due, rebate paid, variance. One row per store per category per month; 500-plus stores times a few categories made 2,000-plus rows a month.
On the SQL side it was a few LEFT JOINs plus GROUP BY — under 40 lines. Nulls were written explicitly as 0, never left as NULL, so the pivot table wouldn't treat them as a separate bucket.
Half an Hour Teaching Pivot Tables
What actually did the work was that half hour. No user manual — we sat next to her and let her drag it herself: store name into Rows, month into Columns, variance sum into Values, then a category into the filter. When she was done, opening the rows where variance wasn't 0 gave the list to investigate for the month.
She wrapped her VLOOKUP in IFERROR so #N/A displayed as "payment missing," saying it let her tell at a glance whether money hadn't arrived or the math was wrong. That column was her idea, not ours.
The next day she sent back an xlsx with the template reworked: conditional formatting on the variance column, red above 500. We tried it — more comfortable to use than the version we'd made.
A Small Takeaway
We never built the "module" out any further. What finance wanted was those two hours back each month, not another menu in the system. There are moments you're tempted to write a beautiful calculation page — but watching her drag a pivot table twice made it obvious that wasn't needed.
We still occasionally show that template as an example when delivering other projects. She named the file herself: "use this at month-end."
