Companion files
Discounts, Price Increases, Price-Volume-Mix and What One Point of Price Is Actually Worth
These are the four Excel workbooks that go with the book. Every figure the book prints is reproduced in them by a live formula rather than a typed constant — change a negotiated discount, a rebate rate, an elasticity or a cost to serve, and every dependent number moves. Each one ends with a Checks sheet setting the printed figure beside the computed one: 76,254 formulas and 1,177 controls in all, every one green. If a control ever reads FAIL, the workbook is wrong, not the book.
Free to download. No sign-up, no email address, nothing to fill in.
Everything described below is inside it, with the read-me.
Chapters 1, 4, 5 and 6
Two kinds of value are typed into this file and no others. The Assumptions sheet carries the three list prices, the three variable costs, the two on-invoice rates, the seven off-invoice rates and the commission rate. The Accounts sheet carries what a customer file would carry: for each of the 180 accounts, its units by family, its average order, its orders a year, its headline negotiated discount, its allowance rates and whether it takes the cash discount. A workbook that claimed to derive a customer file from nothing would be lying to you.
Everything after that is a formula. The 458-row line sheet is built from the two, every company total is summed from those lines rather than typed, and the 56,124,433.13 of pocket revenue is the result of 458 additions. On top sit the DIS-024 cascade per unit and for the year, from a list price of 148.00 through an invoice price of 129.03 to a pocket price of 112.51; the company cascade from 69,880,000 at list down to operating profit; the nine leakages ranked by size; the four segments; the pocket price band by segment and family with the minimum, the quartiles, the maximum and the correlation with volume; and the direct industrial accounts by salesperson with the variance split that Chapter 6 turns on.
Change one negotiated discount and the band moves, the segment moves, the company total moves, and the check on 56,124,433.13 fails. That is the intended behaviour.
Chapters 2, 3, 7, 8 and 17
This is the one to open with a real decision in front of you. It computes the four levers from inputs: a point of price worth 549,193.63 against a point of volume worth 151,393.63, which is where the ratio of 3.6 points of volume to one of price comes from. It computes the break-even volume of a cut as d divided by the quantity m less d, and of a rise as d divided by the quantity m plus d, live, with the table at Ravensworth’s margin of 27.0 per cent, the table across margins, and the table by family. A 5 per cent cut needs 22.8 per cent more volume; a 5 per cent rise can lose 15.6 per cent.
It carries the rebate cliff of DIR-075 at 9,360 units, including both thresholds: the point at which the last units become free to the customer, and the point at which they stop covering their own variable cost. Those two are not pasted. A Solve sheet finds them by bisection, twenty-four passes unrolled one to a column, and reports CONVERGED when the interval closes. There is no circular reference and no iterative calculation anywhere in these files.
It also prices payment terms, with the annualized cost of an early-payment discount as a formula rather than a constant — 2/10 net 30 at 37.2 per cent a year — and it builds the authority matrix of Chapter 17 with the break-even volume beside each band, so the authority is derived from the margin rather than asserted.
Chapters 10 to 14
The one to take to a review meeting. It carries the price increase of Chapter 10 with the rate obtained and the months at the new price as inputs by segment, returning the realized rate and the net position against cost inflation: 5.0 per cent announced, 2.3 realized, 1,291,961.25 against a cost increase of 1,630,980.00 and a net of -339,018.75.
It carries the match-or-hold comparison of Chapter 11 with the elasticity as an input cell and the full elasticity table beneath it, so a reader who believes the demand response is stronger than the one this book declares can say so in a cell and read the consequence rather than argue about it. It carries the five-effect price-volume-mix bridge of Chapter 12 — volume 774,292.16, mix -329,158.64, price -1,089,991.39, cost -825,193.32, commission 21,533.04 — with the check line that must show zero.
And it carries the three-year paths of Chapter 13, at the announced rate and at the rate actually realized, and the deal table of Chapter 14 with the win-probability parameters as inputs, showing the company’s optimum discount and the salesperson’s optimum under both commission plans at the same time.
Chapters 9, 15 and 16
This one is not a reproduction. It is the calculator the book argues for, built for a book of business that is not Ravensworth’s. Enter your list prices, your own nine leakage rates, your variable cost, your commission rate, your fixed costs and your two cost-to-serve rates, and it returns your pocket margin, what one point of price is worth to you in points of volume, and your break-even volume for a cut and for a rise.
Paste a list of your accounts with their units and their pocket prices and it returns your band, your 25th-percentile floor by segment, the money a lift to that floor would produce at unchanged volume, and the volume those accounts could lose before the gain is gone. It also solves for the multiple at which your own segment ranking inverts, by bisection across twenty-eight unrolled passes rather than by scanning a table of round multiples.
It expects three product families and four segments, because that is the shape of the worked example it arrives with; a business with nine families and six channels has rows to add on the input sheet and on the floor grid, and the read-me says where. It ships filled in with the Ravensworth figures so the Checks sheet can prove it agrees with the book before you trust it with anything of your own. Clear the example and the checks fail. That is how you know the file is yours now rather than the book’s.
| Blue text | an input — change it and everything recomputes |
| Yellow fill | an assumption that decides the answer rather than merely feeding it |
| Black text | a formula — do not overtype these |
| Green text | a link to another sheet |
| Checks sheet | the printed figure beside the computed one, the difference, a PASS or a FAIL, and the tolerance being applied |
A workbook that agrees with a book proves nothing on its own — the author wrote both. What the Checks sheets do is different: they force the model to reproduce a number that was printed before the model existed, from a formula rather than from the number itself. And they are built to be breakable. Move one input and some of them go red: 120 of 468 checked figures in the waterfall move when DIS-024’s negotiated discount is taken from 11.0 to 17.5 per cent. A workbook where nothing moved would be a workbook of pasted numbers.
Rounding to the cent is applied at exactly the points the book applies it, which is after each deduction rather than at the end, because a workbook that composes in full precision produces figures that are defensible, different, and wrong against the printed page.
Nineteen check rows carry a variance below half a cent. In every case the published column holds a rounded figure while the live cell carries the unrounded value behind it, and nine of the nineteen are published to three or four decimals rather than two. Each of the nineteen is shown side by side with its variance rather than hidden, and none of them is a disagreement about the answer.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers. They use no macros and no add-ins, so nothing needs to be enabled or trusted. There is no circular reference anywhere in them: the two places where the arithmetic loops back on itself are unrolled one pass to a column on a Solve sheet, with a cell that reads CONVERGED. If your spreadsheet asks to update links on opening, decline — there are none.
The other books with companion files. The full list of titles is on the author page.