Worksheet: Build the waterfall from list to net-net

When terms are negotiated net and the list price is worked back afterwards, reported trade spend can be a fraction of the real figure. This sheet builds the waterfall for your ten biggest customers and twenty biggest products. It puts every term in a named bucket and reconciles the result once to the ledger. It builds the Competent step of the check:

A waterfall from list to net invoice to net-net is reported monthly by customer and product, with every term in a named bucket.

Step 1. Name the buckets

Every dollar of trade spend goes in one bucket. "Other" should be empty by month three.

Bucket	On or off invoice	Contractual, discretionary or one-off	What goes in it	Ledger account
e.g. Scan rebate	Off	Discretionary	Per unit sold in a promotion week	Promotional rebates
Permanent off-invoice discount
Promotional off-invoice (case deals)
Scan rebates
Volume and growth rebates
Co-op and marketing funds
Distribution centre and logistics allowances
Prompt-payment discount
Wholesaler and distributor allowances
Listing and new-line fees
Claims and deductions
Other

Step 2. The ten biggest customers, per $100 of list

Customer	List	On-invoice terms	Net invoice	Off-invoice terms	Net-net	Net-net as % of list	Same figure last year
e.g. Customer 1	100	14	86	17	69	69%	71%
1	100
2	100
3	100
4	100
5	100

Continue to ten.

Step 3. The twenty biggest products, per $100 of list

Product (pack)	List	On-invoice terms	Net invoice	Off-invoice terms	Net-net	Net-net as % of list	Largest bucket
1	100
2	100
3	100
4	100
5	100

Continue to twenty.

Step 4. Reconcile once to the ledger

Line	Waterfall total ($)	Ledger total ($)	Gap ($)	Explained by
Gross sales at list
On-invoice terms
Off-invoice terms paid
Off-invoice terms accrued, not yet claimed
Net-net revenue

A gap above 1% usually means a missing bucket, or a term booked to the wrong customer.

Step 5. Report it monthly

Month	Sent to	Customers and products covered	Biggest rate move, in one line

Done when: a waterfall from list to net invoice to net-net goes out monthly by customer and product. Every term sits in a named bucket, and the waterfall reconciles to the ledger.

Adapted from our own pricing and trade spend outline (PSP, 2013).