A free price waterfall analysis template in Excel, from list price to pocket price
One account's waterfall step by step, then the whole company summed over 458 account lines: two discounts on the invoice, seven leakages behind it, the pocket price band, the segments and the salespeople: ten sheets, no macros.
The file follows Ravensworth Adhesives, a fictional manufacturer selling three product families to 180 accounts. At list, the year's 790,000 units are worth 69.88 million. The invoice shows 60.25 million, 86.2% of list; after rebates, cash discounts, freight, co-op marketing, growth bonuses, returns and promotions, the company actually pockets 56.12 million, 80.3% of list. That is 13.76 million given away, 13.8 points on the invoice and 5.9 behind it, against an operating profit of 5.74 million. One distributor's structural adhesives start at 148 a unit and end at a pocket price of 112.51.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Price_Waterfall_Analysis.xlsx (248 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from Pricing Strategy. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 10 paperbacks by Julian R. Sterling on tax and incentives, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Ten sheets. Blue cells are the published rules and each account's own terms; every money column is a live formula.
Read Me
What the workbook rebuilds, the chapters it serves, and the cell to change first.
Assumptions
List prices, variable costs and volumes for the three families, the published discount and rebate rules, and the pressure factor that sets how hard buyers fight on each family.
Accounts
The 180 accounts, one row each: segment, salesperson, units by family and the account's own terms.
Lines
The 458 account-family lines, each with its full waterfall from list to contribution.
DIS-024
One account's waterfall, step by step, and the same account's three different prices across the three families.
Company
The pocket profit and loss account, the nine leakages ranked, and the reported P&L the board sees beside it.
Segments, Band and By Salesperson
Pocket price by segment, the pocket price band by family and inside each segment, and who actually gives the discount.
Checks
Every figure the book prints against the live sum of the lines: 468 checks pass.
What the worked case returns
Measure
Value
Revenue at list
69,880,000
Invoiced revenue
60,253,525 (86.2%)
Pocket revenue
56,124,433 (80.3%)
Give-away on the invoice
9,626,475
Give-away behind the invoice
4,129,092
Largest leakage: negotiated discount
6,469,305 (9.3 pts)
Operating profit
5,739,363
DIS-024: list to invoice to pocket
148.00, 129.03, 112.51
DIS-024: contribution per unit taken by discounting
52.4%
No page of a reported profit and loss account adds the nine leakages up. Rebates sit below the sales line, freight in distribution costs, co-op in marketing, returns in credit notes. Summed on one page, they are 2.4 times the company's operating profit, and on DIS-024 the discounting takes more than half of the contribution the list price was built to earn.
How to use it on your own business
Enter your own list prices, variable costs and discount rules on Assumptions.
Replace the accounts and lines with your own customers and their terms.
Read one account's waterfall first, then the company sheet: the ranking of the leakages is where to start.
Read the pocket price band: accounts of the same size paying very different pocket prices is the finding.
Read By Salesperson last: it shows who gives the negotiated discount, not who is asked for it.
Questions people ask about it
Is this price waterfall template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. Each line is a chain of rounded formulas from list price to contribution, and every company figure is a sum over the lines.
What is a pocket price?
The price a seller actually keeps after every discount, rebate, allowance and credit, on and off the invoice. Here list is 100, invoice 86.2 and pocket 80.3.
Can I use it on my own business?
Yes, by replacing the accounts and rules. The company and every figure in it are fictional.
The rest of the files
This template is one of the four companion workbooks for Pricing Strategy: the others compute the break-even volume of a discount, bridge price, volume and mix, and give a blank calculator for your own book of business. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.