Discount & Price Override Tracker Spreadsheet
This spreadsheet adds up what last year’s discounts came to, who gave them, why, and how much more you would have to sell to get the money back.
Whatever the goods cost you, they still cost you that: a discount takes nothing off the cost, so all of it comes out of what was left. Most businesses know roughly what they sell and almost none know what they gave away. This file counts only the orders that went out below list.
What is in it
- Discounts — one row per order that went out below list: the customer, who gave it, why, the date, what it was worth at list and what they actually paid.
- By Who — every name, what they gave away, and the written limit they were meant to be working to.
- Why — the reason on every row, with one honest question against it: did it have to be given, could it have been argued, or was there no reason at all?
- Over The Limit — every order checked against that written limit, and everything beyond it added up by name.
- What It Would Take — at your own margin, the extra volume any discount needs before it pays for itself. It works before you log a single order.
- Setup and Overview — two numbers, two lists you name yourself, and the whole year on one screen.
Who it is for
Anywhere somebody can decide the price on the day: a merchant, a distributor, a wholesaler, a printer, a garage, an agency, a manufacturer, a hire company. It suits a business with a list price and one gross margin that is roughly true across the sheet; where margins differ wildly, run a file per product group.
The worked example
In the worked year, 620 orders went out below list and the discount came to $468,657 — 6.9% of everything sold, and 21.9% of the gross profit.
$110,841 of it was recorded as having no reason at all, $78,160 went past a limit somebody had been given in writing, and standing still after all of it would take 28.0% more volume.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Buy it on Etsy — $9
Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the buyer protection are all handled by Etsy.
A look inside the file
Questions people actually ask
How much extra volume does a discount need to pay for itself?
Far more than anybody guesses, and the file works it out at your own margin before you log a single order. At the worked example's 31.5% list margin, giving away 5% needs 18.9% more volume, 10% needs 46.5% and 20% needs 173.9%. A discount does not reduce the cost of the goods by a penny, so all of it comes out of what was left: in the worked year 6.9% off the price was 21.9% of the gross profit.
Is this only for distributors?
No, it fits anywhere somebody can decide the price: a merchant, a printer, a garage, an agency, a wholesaler, a manufacturer, a hire company. If your margin varies a lot by product, run a file per product group rather than one for everything, because the arithmetic only works if the margin in Setup is roughly true for the orders in the sheet.
We do not have a list price, and nobody has a formal discount limit.
For list price, use whatever you would have charged a customer with no arrangement, and that is your list price whether or not it is printed anywhere. For limits, put in the number you would be uncomfortable seeing exceeded and read the Over The Limit tab as a proposal rather than a report. You only log the orders that went out below list, so everything sold at full price stays exactly where it is.
Which programs does it run in, and is there anything to install?
Excel, Google Sheets and LibreOffice, and there is nothing to install. It is built as .xlsx for Excel 2016 or newer on Windows or Mac, and imports into Google Sheets through File then Import then Upload. No macros, no add-ons, no account to create and no subscription.
Does it tell me who to blame?
No. It puts a name and a reason on every row, which is not the same thing, and most people find the reasons more useful than the names. Every person gets a sentence rather than a league table. It is not a pricing system, a CRM or a sales ledger, it approves nothing, and it will not stop anybody saying yes; it tells you what saying yes cost you last year.
Need it built around your business?
This file assumes a list price and one margin that holds roughly true across the sheet. If your pricing works another way — a job shop quoting every order from scratch, an agency discounting a monthly retainer rather than a line, a merchant with a different margin on every product group and a different limit on every counter — I can build the same arithmetic around how your prices are actually set.
All other templates are listed on the Excel & Google Sheets Templates page.