Discount & Price Override Tracker Spreadsheet | Sales Margin Leakage Excel Google Sheets Template | Pricing Discipline Tool

🏷 DISCOUNT & PRICE OVERRIDE TRACKER — EXCEL AND GOOGLE SHEETS

6.9% off the price. 21.9% off the profit.

Whatever the goods cost you, they still cost you that. A discount does not
reduce the cost by a penny, so every penny of it comes out of what was left.

Ten per cent off a price with a thirty per cent margin is a third of the profit
on that order, gone in one sentence. And getting it back through volume takes
far more sales than anybody guesses.

---

EIGHT LINKED TABS

· Start Here — what each tab expects and where to type.
· Setup — two numbers, and two lists you name yourself.
· Discounts — one row per order that went out below list.
· By Who — who gives it away, and how much of it.
· Why — the reasons, and the one nobody wants to read.
· Over The Limit — what went beyond what anybody was allowed.
· What It Would Take — the tab this file exists for.
· Overview — the whole year on one screen.

---

FROM THE WORKED YEAR INSIDE THE FILE

Everything sold, at list price $6,800,000
Orders that went out below list 620
THE DISCOUNT $468,657
As a share of everything sold 6.9%
SHARE OF GROSS PROFIT, GIVEN AWAY 21.9%

Gross margin at list 31.5%
Gross margin now 26.4%
SALES NEEDED TO STAND STILL $8,104,576
WHICH IS THIS MUCH MORE VOLUME 28.0%

---

THE READY RECKONER

At your own margin, this is what any discount costs in extra volume. It is
worked out before you have logged a single order.

Give away 2.5% → 8.6% more volume needed
Give away 5.0% → 18.9%
Give away 10.0% → 46.5%
Give away 15.0% → 90.9%
Give away 20.0% → 173.9%
Give away 25.0% → 384.6%

Nobody guesses these right. They are always two or three times worse than people
expect, and they are the one table everybody who can put a price down should
have seen.

---

$110,841 GIVEN FOR NO REASON AT ALL

Against every reason in Setup there is one honest question: did it have to be
given? Had to, Could have argued, or No reason at all.

In the worked year 48.8% had to be given and 51.2% did not — $240,123 of
arguable or unexplained discount in a single year.

Keep a row for "they have always had it" and one for "no reason recorded".
Between them they are usually the biggest line in the file.

---

34% OF THEM WENT PAST SOMEBODY'S LIMIT

Against every name there is a written limit: what they are actually allowed to
give away. Put in the written one, not the one everybody uses.

The file checks every order against it and adds up what went beyond: $78,160,
which is 4.7% more gross profit if everybody had simply stopped where they were
supposed to.

Forty-eight per cent of it went through one name. That is a conversation, and a
much shorter one than a new pricing policy.

---

EIGHT PEOPLE, AND ONE OF THEM GAVE AWAY $129,539

Two people on the same counter, on the same limit, with a percentage point
between them. A director with no limit at all who gives away more than anybody
in the field.

Every row gets a sentence, not a league table: no limit, and it shows · careful
with it, and always has been · gives away more, on average, than they are
allowed to.

---

WHAT YOU ACTUALLY TYPE

Six things per discounted order: the customer, who gave it, why, the date, what
it was worth at list, and what they actually paid.

Only the orders that went out below list. Everything sold at full price stays
exactly where it is.

---

WHAT YOU GET

Two workbooks and a guide. One workbook has the year above already filled in, so
you can see what every column expects before you touch anything. The second is
the same file completely blank — same eight tabs, same formulas — ready for your
own orders from the first row. The guide is a plain-English PDF.

---

EXCEL AND GOOGLE SHEETS

Built as .xlsx. Excel 2016 or newer on Windows or Mac, and Google Sheets through
File → Import → Upload. LibreOffice too. No macros, no add-ons, no account to
create, nothing to install, no subscription. You download a file and it is yours.

---

QUESTIONS PEOPLE ACTUALLY ASK

Our margin varies a lot by product.
Then run a file per product group rather than one for everything. 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.
Use whatever you would have charged a customer with no arrangement. That is your
list price, whether or not it is printed anywhere.

Nobody has a formal discount limit.
Then put in the number you would be uncomfortable seeing exceeded, and read the
Over The Limit tab as a proposal rather than a report.

Is this only for distributors?
No. Anywhere somebody can decide the price: a merchant, a printer, a garage, an
agency, a wholesaler, a manufacturer, a hire company.

Does it tell me who to blame?
It puts a name and a reason on every row, which is not the same thing. Most
people find the reasons more useful than the names.

---

WHAT THIS IS NOT

Not a pricing system, a CRM or a sales ledger, and it approves nothing. Not
accounting or bookkeeping software. Not connected to anything — nothing syncs,
nothing logs in. Not legal or commercial advice.

It will not stop anybody saying yes. It will tell you what saying yes cost you
last year, by name and by reason, and how much more you would have to sell to
get it back.

---

Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the
buyer protection are all handled by Etsy. I never ask for access to anything —
no password, no pricing system, no customer list. You download a file; that is
the whole transaction.

The example business, its people and its figures are invented. The arithmetic
behind every figure is not.