Commercial Pricing & Revenue Operations
B2B Price Waterfall Pocket Price & Margin Formula
Audit on-invoice and off-invoice B2B price leakage (rebates, co-op allowances, early-pay discounts, and free freight) to calculate Pocket Price and Pocket Margin %.
Uncover hidden off-invoice margin leakage that ERP invoice reports miss completely!
Introduced by Michael Marn and Robert Rosiello (1992), the B2B Price Waterfall exposes why accounts that look profitable on ERP Invoice Margin often lose money in reality! Standard sales dashboards stop at Invoice_Price = List_Price - OnInvoice_Discounts. Yet after the invoice prints, four off-invoice buckets quietly drain cash: Annual Volume Rebates, Co-Op Marketing Allowances, Early-Payment Cash Discounts (e.g., 2/10 net 30), and Unrecovered Free Freight/Expediting — leaving true Pocket Price = Invoice_Price - (Rebate + CoOp + Cash_Disc + Freight_Leak) and Pocket Margin % of List = (Pocket_Price - Unit_COGS) / List_Price!
Check by hand with List_Price = 200.00 (B2), OnInvoice_Discount = 24.00 (B3 -> Invoice_Price = 176.00), OffInvoice_Rebate = 10.00 (B4), CoOp_Allowance = 6.00 (B5), EarlyPay_Discount = 4.00 (B6), Free_Freight_Cost = 8.00 (B7), and Unit_COGS = 110.00 (B8): ERP Invoice Gross Margin looks healthy at (176 - 110) / 176 = 0.3750 (37.50%, $66/unit)! Subtracting 28.00 of off-invoice leakage reveals true Pocket Price = 148.00, Pocket Profit = 38.00/unit (-42.42% below $66!), Total Price Leakage = (200 - 148) / 200 = 0.2600 (26.00%), and Pocket Margin % of List = 38 / 200 = 0.1900 (19.00%)!
| Price Waterfall Stage (List = 200, COGS = 110) | Formula | Verified Output |
|---|---|---|
| ERP Invoice Price (After On-Invoice 24) | List - OnInvoice_Disc | 176.00 (Invoice GM = 37.50%) |
| Hidden Off-Invoice Leakage Sum | Rebate + CoOp + CashDisc + Freight | 28.00 (14.00% of List) |
| Realized Pocket Price | Invoice_Price - OffInvoice_Leakage | 148.00 (26.00% Total Leakage) |
| Pocket Margin ($ and % of List) | (Pocket_Price - COGS) / List | 38.00 | 0.1900 (19.00%) |
Realized Pocket Price After On- and Off-Invoice Leakage Formula
Validate List_Price > 0 and non-negative discount/cost buckets.
=LET(p_ls, B2, d_on, B3, d_rb, B4, d_co, B5, d_cs, B6, d_fr, B7, IF(OR(p_ls <= 0, d_on < 0, d_rb < 0, d_co < 0, d_cs < 0, d_fr < 0), "Invalid Waterfall Input", ROUND(p_ls - d_on - d_rb - d_co - d_cs - d_fr, 2)))Pocket Margin % of List Price Formula
Anchor waterfall denominators consistently to List_Price so sales reps cannot game margin percentages by inflating on-invoice discounts.
=IF(OR(ISTEXT(E3), B8 < 0), "Invalid Waterfall Input", ROUND((E3 - B8) / B2, 4))Try the example
Use the original values or change them to test your understanding.