Build a Discount Leakage Report

Most companies have a discount policy: a table that says how much discount a salesperson may give, depending on the customer's tier and the product group, with anything larger needing a manager's approval. Most companies also suspect the policy is not followed — that discounts drift upwards deal by deal, and that some customers pay far less than others for the same thing. The money lost this way is called discount leakage, and it is measurable from invoice data. This page builds a leakage report with pandas: each invoice line's discount compared with the approved maximum for its tier and product group, the excess valued in currency, approved exceptions separated from the rest, the leakage broken down by sales rep and customer, and the spread of discounts for the same product and tier — the clearest sign of inconsistent pricing. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Four inputs: invoice lines for the quarter with the unit price actually charged and a flag for lines where a manager approved an exception; customers with their tier and sales rep; products with their product group and list price; and the discount policy, the maximum discount for each combination of tier and product group.

Step 1 — Each line's discount against the policy

import pandas as pd

lines = pd.read_excel("invoice_lines.xlsx", keep_default_na=False)
customers = pd.read_excel("customers.xlsx")
products = pd.read_excel("products.xlsx")
policy = pd.read_excel("discount_policy.xlsx")

df = (lines.merge(customers, on="Customer", validate="many_to_one")
           .merge(products, on="Product", validate="many_to_one")
           .merge(policy, on=["Tier", "Group"], validate="many_to_one"))
df["Discount"] = 1 - df["Unit price"] / df["List price"]
df["Excess"] = (df["Discount"] - df["Max discount"]).clip(lower=0)
df["List value"] = df["List price"] * df["Qty"]
df["Leakage"] = (df["Excess"] * df["List value"]).round(2)
df["Category"] = "Within policy"
df.loc[(df["Excess"] > 0) & (df["Approved exception"] == "Y"), "Category"] = "Approved exception"
df.loc[(df["Excess"] > 0) & (df["Approved exception"] != "Y"), "Category"] = "Unapproved"

summary = df.groupby("Category").agg(lines=("Invoice", "size"), revenue=("List value", "sum"), leakage=("Leakage", "sum")).round(0)
print(summary.to_string())
print(f"unapproved leakage: {df.loc[df['Category'] == 'Unapproved', 'Leakage'].sum():,.0f} "
      f"({df.loc[df['Category'] == 'Unapproved', 'Leakage'].sum() / (df['List value'] * (1 - df['Discount'])).sum():.2%} of net revenue)")

Joining the invoice lines to customers, products and the policy gives every line its tier, product group, list price and allowed maximum, with validate making sure each join matches exactly one row — a product missing from the price list or a tier missing from the policy would otherwise duplicate or drop lines silently. The discount actually given is worked out from the price charged and the list price, rather than taken from a discount field, because invoice discount fields are often incomplete when salespeople simply type a lower price. The excess is how far the discount goes beyond the policy maximum, and multiplying it by the list value gives the leakage: the revenue that would have been collected had the discount stopped at the limit. Lines over the limit are split into approved exceptions — deals a manager signed off, which are a pricing decision — and unapproved ones, which are the real leakage. In this quarter 1,107 of the 1,200 lines are within policy, 30 are approved exceptions worth 20,376 in excess discount, and 63 are unapproved, worth 58,911 — 0.44% of net revenue. That sounds small until it is set against margin: every pound of leakage comes straight off profit, and at this rate it is roughly 236,000 a year.

Step 2 — Who and where

unapproved = df[df["Category"] == "Unapproved"]
by_rep = df.groupby("Rep").agg(lines=("Invoice", "size"), over_policy=("Excess", lambda s: (s > 0).mean()))
by_rep["Unapproved leakage"] = unapproved.groupby("Rep")["Leakage"].sum()
by_rep = by_rep.fillna(0).sort_values("Unapproved leakage", ascending=False).round(3)
print(by_rep.to_string())

by_customer = (unapproved.groupby(["Customer", "Tier", "Rep"])["Leakage"].sum()
               .sort_values(ascending=False).head(5).round(0))
print(by_customer.to_string())

Leakage is rarely spread evenly. Grouping by sales rep shows each person's share of lines over policy and the unapproved leakage on their accounts; here Dev discounts beyond policy on 22% of lines and Ben on 13%, against 2–3% for Ana and Caro, and between them Dev and Ben account for 51,000 of the 59,000 unapproved leakage. Four of the five customers with the most leakage are Dev's accounts, led by a bronze-tier customer whose unapproved discounts cost 7,026 in the quarter. The customer view lists the accounts where most of the money goes, which is where a pricing conversation will recover the most. Neither view is a verdict on its own — a rep with the largest accounts will naturally show more leakage in currency — which is why the share of lines over policy sits next to the amount.

Step 3 — How consistent is pricing?

spread = (df.groupby(["Product", "Tier"])["Discount"]
            .agg(lines="size", lowest="min", median="median", highest="max")
            .query("lines >= 10"))
spread["Range"] = spread["highest"] - spread["lowest"]
print(spread.sort_values("Range", ascending=False).head(6).round(3).to_string())

Even within policy, two customers of the same tier can pay very different prices for the same product. The spread of discounts for each product and tier — lowest, median, highest and the range between them — shows how consistent pricing really is. A wide range means the price depends more on who negotiated than on the policy, and it is the pattern customers notice when they compare notes. The widest here is product P12 for silver customers: across 26 lines the discount ranges from 8.8% to 28%, with a median of 13.2% — the same product, the same tier, and some customers paying a fifth less than others. Limiting the view to combinations with at least ten invoice lines keeps one-off deals from dominating it.

Step 4 — The report

with pd.ExcelWriter("discount_leakage_2026-Q3.xlsx") as xw:
    summary.reset_index().to_excel(xw, sheet_name="Summary", index=False)
    by_rep.reset_index().to_excel(xw, sheet_name="By rep", index=False)
    by_customer.reset_index().to_excel(xw, sheet_name="Top customers", index=False)
    spread.reset_index().to_excel(xw, sheet_name="Discount spread", index=False)
    unapproved.sort_values("Leakage", ascending=False)[
        ["Invoice", "Customer", "Rep", "Product", "Tier", "Group", "Qty", "List price", "Unit price",
         "Discount", "Max discount", "Leakage"]].to_excel(xw, sheet_name="Unapproved lines", index=False)
print("written")

The workbook opens with the summary — lines, revenue and leakage within policy, as approved exceptions and unapproved — followed by the rep and customer views, the discount spread and the list of unapproved lines with the discount given, the maximum allowed and the money involved, largest first. That list is what sales management works through. Presented this way the report is about recovering margin, not about blame, and the most effective follow-up is usually a change to the process — requiring approval in the quoting tool before a price below the limit can be entered — rather than a hunt through past invoices.

Looking at the approved exceptions too

Approved exceptions are not leakage, but they deserve their own review once a quarter. Each one was granted for a reason — to win a competitive deal, keep a large customer or clear old stock — and the report can check whether the reason held up: did the customer's volume grow after the extra discount, or did the discount simply become their new normal price? Exceptions that keep being renewed for the same customer are a sign that the customer belongs in a higher tier, or that the policy for that product group is out of date. Either way, the policy table is an input to this report, not a fixed truth, and the leakage numbers are a good prompt to revisit it with the sales and finance leads together. Changing a maximum in the table and rerunning the report shows immediately how much of today's leakage the new policy would have legitimised.

Common pitfalls

• Trusting the discount field. Derive the discount from the price charged and the list price.
• Silent join failures. Validate every join; a missing product or tier changes the result.
• Counting approved exceptions as leakage. Separate them; they are decisions, not leaks.
• Ranking reps by currency alone. Show the share of lines over policy as well.
• Ignoring dispersion within policy. A wide range for the same product and tier is a pricing problem too.

Conclusion

Discount leakage is the revenue lost to discounts beyond policy without approval. Join each invoice line to its customer tier, product group, list price and policy maximum, derive the discount from the price charged, value the excess against the list price, and split it into approved exceptions and unapproved leakage. Break the leakage down by rep and customer, measure the spread of discounts for each product and tier, and give sales management the list of unapproved lines.

Frequently asked questions

How do I calculate discount leakage? For each invoice line, take the discount given minus the policy maximum, ignore negative values, and multiply by the list value; sum the unapproved lines.

Why calculate the discount from prices instead of using the discount field? Salespeople often type a lower price instead of entering a discount, so the field understates the real discount.

How do I check discounts against a policy matrix in pandas? Merge the invoice lines with the policy table on tier and product group, with validate="many_to_one", and compare the discount with the maximum.

How do I measure pricing consistency? Group by product and customer tier and compare the lowest, median and highest discount given.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Build a Price-Volume-Mix Analysis — price effects on revenue.
• Build a Customer Profitability Report — what discounts do to margin by customer.
• Model the Impact of a Price Increase — the other side of pricing.