Build a Product Margin Report

Revenue reports say what sold; margin reports say what was worth selling. A product with healthy list prices can lose money once discounts, returns and shipping are counted, and a big customer can be the least profitable one. The data for a margin report is usually all in Excel exports — sales lines from the order system, standard costs from the ERP, freight invoices from the carrier — but combining them correctly takes care: costs change during the year, so each sale must be costed at the cost that applied on its date; freight arrives as one invoice per shipment and must be shared across the products in it; and returns must reduce both revenue and cost. This page builds the report with pandas and writes it to Excel. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Four inputs: sales_lines.xlsx (order, date, customer, SKU, quantity, list price and line discount) for August and September 2026; standard_costs.xlsx with each product's standard cost and the date it took effect — two products got more expensive on 1 September — plus unit weight; freight.xlsx with the carrier charge per order; and returns.xlsx with returned quantities.

Step 1 — Net revenue after discounts and returns

Start with what the customer actually pays for what they keep:

import pandas as pd

lines = pd.read_excel("sales_lines.xlsx")
returns = pd.read_excel("returns.xlsx")

lines = lines.merge(returns, on=["Order", "SKU"], how="left").fillna({"Qty returned": 0})
lines["Net qty"] = lines["Qty"] - lines["Qty returned"]
lines["Net price"] = (lines["List price"] * (1 - lines["Discount %"] / 100)).round(2)
lines["Revenue"] = (lines["Net qty"] * lines["Net price"]).round(2)
print(lines[["Order", "SKU", "Qty", "Qty returned", "Net price", "Revenue"]].head(8).to_string(index=False))
print("revenue:", round(lines["Revenue"].sum(), 2), "| discounts given:",
      round((lines["Net qty"] * lines["List price"]).sum() - lines["Revenue"].sum(), 2))

Returns are matched on order and SKU and reduce the quantity before anything is priced or costed, so a returned drill takes out both its revenue and, in the next step, its cost. The total discount given is worth printing on its own: it is often the single biggest lever on margin, and seeing it as one number tends to start a useful conversation with the sales team.

Step 2 — The standard cost in force on each sale date

Costs changed on 1 September, so August sales must use the old cost and September sales the new one. merge_asof picks, for each sale, the most recent cost record on or before its date:

costs = pd.read_excel("standard_costs.xlsx")
lines = pd.merge_asof(lines.sort_values("Date"), costs.sort_values("Effective from"),
                      left_on="Date", right_on="Effective from", by="SKU", direction="backward")
assert lines["Std cost"].notna().all(), "sale before any cost record"
lines["COGS"] = (lines["Net qty"] * lines["Std cost"]).round(2)
print(lines[["Date", "SKU", "Std cost"]].drop_duplicates(["SKU", "Std cost"]).sort_values(["SKU", "Date"]).to_string(index=False))

Drills sold in August are costed at 71.00 and those sold in September at 78.00; hoses move from 13.50 to 16.00; saws, whose cost did not change, use 52.00 throughout. Using a single current cost for the whole period would understate August's margin, overstate the change between the months, and make it impossible to tell whether margin fell because prices dropped or costs rose. The assertion catches a sale dated before a product's first cost record. As-of joins are explained in Match Rows by Nearest Date with merge_asof.

Step 3 — Allocate freight by weight

Freight is invoiced per order, but the margin report is per product. Share each order's freight across its lines in proportion to their shipped weight — a heavy saw carries more of the cost than a light hose:

freight = pd.read_excel("freight.xlsx")
lines["Weight"] = lines["Qty"] * lines["Weight kg"]                  # what was shipped, before returns
lines["Order weight"] = lines.groupby("Order")["Weight"].transform("sum")
lines = lines.merge(freight, on="Order", how="left", validate="many_to_one")
lines["Freight alloc"] = (lines["Freight"] * lines["Weight"] / lines["Order weight"]).round(2)
check = lines.groupby("Order")["Freight alloc"].sum().round(2) - freight.set_index("Order")["Freight"]
print("allocated = invoiced per order:", (check.abs() <= 0.02).all(), "| total freight:", lines["Freight alloc"].sum())

groupby(...).transform("sum") puts each order's total weight on every one of its lines, so the share is a simple division. Allocation uses the weight shipped, before returns, because the freight was paid on what left the warehouse. The check confirms every order's allocated freight adds back to its invoice within rounding. Weight is one reasonable driver; value or volume are others, and the same three lines of code work with any of them — Allocate Shared Costs Across Departments discusses choosing a driver.

Step 4 — Margin by product, customer and line

With revenue, cost of goods and freight on every line, margins are group sums:

lines["Margin"] = lines["Revenue"] - lines["COGS"] - lines["Freight alloc"]

def margin_table(by):
    g = lines.groupby(by)[["Revenue", "COGS", "Freight alloc", "Margin"]].sum()
    g["Margin %"] = (g["Margin"] / g["Revenue"] * 100).round(1)
    return g.sort_values("Margin %")

by_product = margin_table("SKU")
by_customer = margin_table("Customer")
print(by_product.round(2).to_string())
print(by_customer.round(2).to_string())
losers = lines[lines["Margin"] < 0][["Order", "Date", "Customer", "SKU", "Net qty", "Discount %", "Margin"]]
print(losers.to_string(index=False))

Margin percentage is computed from the summed margin and revenue of each group — never by averaging line percentages. The tables tell a clear story: hoses are the weakest product at 7.2%, and Zenith Logistics, by far the biggest customer by revenue, earns the lowest margin at 17.1%, because it buys hoses at 25–30% off and its orders are heavy to ship. The loss-making line is the one to take to the sales manager: Zenith's 28 September hose order, at 30% off, loses €51 once the higher September cost and its share of freight are counted.

Step 5 — Why did margin change? Price versus cost

Margin per unit moved between August and September. Split the change for each product into the part caused by selling prices and the part caused by costs:

lines["Month"] = lines["Date"].dt.to_period("M").astype(str)
unit = lines.groupby(["SKU", "Month"]).apply(
    lambda g: pd.Series({"Price": g["Revenue"].sum() / g["Net qty"].sum(),
                         "Cost": (g["COGS"].sum() + g["Freight alloc"].sum()) / g["Net qty"].sum(),
                         "Qty": g["Net qty"].sum()}), include_groups=False).unstack("Month")
bridge = pd.DataFrame({
    "Margin/unit Aug": unit["Price"]["2026-08"] - unit["Cost"]["2026-08"],
    "Price effect": unit["Price"]["2026-09"] - unit["Price"]["2026-08"],
    "Cost effect": -(unit["Cost"]["2026-09"] - unit["Cost"]["2026-08"]),
})
bridge["Margin/unit Sep"] = bridge["Margin/unit Aug"] + bridge["Price effect"] + bridge["Cost effect"]
print(bridge.round(2).to_string())

Per unit, the change in margin is exactly the change in price minus the change in cost, so the two effects add up to the difference between the months. For drills, the price effect is negative — September's big Zenith order carried a 20% discount — and the cost effect is negative too, from the standard cost rise. Hoses show both effects at once and end September earning five cents a unit. Separating the two answers the question a margin report always provokes: is it our pricing or our costs?

Step 6 — Write the report

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, PatternFill

with pd.ExcelWriter("product_margin_2026-09.xlsx", engine="openpyxl") as xw:
    by_product.round(2).to_excel(xw, sheet_name="By product")
    by_customer.round(2).to_excel(xw, sheet_name="By customer")
    bridge.round(2).to_excel(xw, sheet_name="Price vs cost")
    losers.to_excel(xw, sheet_name="Loss-making lines", index=False)
    lines.drop(columns=["Effective from", "Order weight"]).to_excel(xw, sheet_name="Lines", index=False)
    for name in ("By product", "By customer"):
        ws = xw.sheets[name]
        last = ws.max_row
        ws.conditional_formatting.add(f"F2:F{last}", CellIsRule(operator="lessThan", formula=["15"],
                                      fill=PatternFill("solid", fgColor="FFC7CE")))
        ws.conditional_formatting.add(f"F2:F{last}", CellIsRule(operator="greaterThanOrEqual", formula=["30"],
                                      fill=PatternFill("solid", fgColor="C6EFCE")))
    for ws in xw.book.worksheets:
        for c in ws[1]:
            c.font = Font(bold=True)
        ws.column_dimensions["A"].width = 18
print("total margin:", round(lines["Margin"].sum(), 2), "on revenue", round(lines["Revenue"].sum(), 2))

Margin percentages under 15% are shaded red and those of 30% or more green, so weak products and customers stand out on each summary sheet. The Lines sheet keeps every computed column — net price, the cost used, allocated freight — so anyone can trace a figure in the summary back to individual sales. For a monthly pack, the headline margin feeds into Build a Monthly KPI Scorecard with Targets.

Common pitfalls

• One current cost for every sale. Cost each sale at the standard cost in force on its date.
• Ignoring returns. Reduce quantity before computing both revenue and cost.
• Freight left at order level. Allocate it to lines with a driver such as weight.
• Averaging margin percentages. Divide summed margin by summed revenue.
• Unexplained margin changes. Separate price effects from cost effects.

Conclusion

A product margin report combines four sources line by line: net revenue after discounts and returns, the standard cost in force on each sale date via merge_asof, freight allocated across each order's lines by weight, and the resulting margin. Group it by product and customer, list loss-making lines, split month-on-month changes into price and cost effects, and write it to Excel with colour-coded margins and the full line detail for audit.

Frequently asked questions

How do I calculate product margin from sales data in Python? Compute net revenue per line after discounts and returns, subtract cost of goods at standard cost and any allocated freight, then group by product and divide summed margin by summed revenue.

How do I use the cost that applied on each sale date? Use pandas merge_asof between the sales lines and the dated cost table, matching by SKU with direction backward, so each sale takes the latest cost effective on or before its date.

How do I allocate freight to products? Share each order's freight across its lines in proportion to a driver such as shipped weight, using groupby transform to get each order's total weight.

How do I explain a change in margin? Per unit, the change in margin equals the change in net price minus the change in unit cost, so report the price effect and the cost effect separately.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Calculate FIFO Inventory Valuation with Python — actual rather than standard costs.
• Compare Supplier Quotes with Python — attacking the cost side.
• Build a Sales Commission Report — paying on margin rather than revenue.