Model the Impact of a Price Increase

When costs rise, a business has to decide whether to raise its prices, by how much, and on what. The worry is always the same: put prices up and some customers buy less or go elsewhere. The question that settles most of these discussions is surprisingly specific — how much volume could we lose and still make the same gross profit? — and the answer is often larger than people expect, because a price increase goes straight to margin while the lost units take their cost with them. This page builds a price increase model with pandas from a year of sales by product and customer: it applies candidate increases per product group, estimates lost volume from simple elasticity assumptions, leaves alone customers whose contracts fix their prices for part of the year, calculates the break-even volume loss for each product, and compares scenarios side by side in a workbook whose assumptions can be changed. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Three inputs: products with their group, current price and unit cost; customers, three of whom have contracts that fix their prices until 30 June 2027; and last year's volumes per customer and product. The new prices would take effect on 1 January 2027, and the model covers the year 2027.

Step 1 — The baseline

import pandas as pd

products = pd.read_excel("products.xlsx").set_index("Product")
customers = pd.read_excel("customers.xlsx", parse_dates=["Contract until"]).set_index("Customer")
vol = pd.read_excel("annual_volumes.xlsx").join(products, on="Product").join(customers, on="Customer")

vol["Revenue"] = vol["Annual units"] * vol["Price"]
vol["Gross profit"] = vol["Annual units"] * (vol["Price"] - vol["Unit cost"])
base = vol.groupby("Group")[["Revenue", "Gross profit"]].sum()
base["Margin %"] = (base["Gross profit"] / base["Revenue"] * 100).round(1)
print(base.round(0).to_string())
print("total:", round(vol["Revenue"].sum()), round(vol["Gross profit"].sum()))

The baseline assumes next year's volumes repeat last year's at today's prices: revenue and gross profit — revenue less the cost of the goods sold — by product group. Gross margins differ a lot between the groups, from around 30% on power tools to over half on protective equipment, and that difference matters for everything that follows. Across the three groups the business turns over £1.07 million with £445,000 of gross profit, a blended margin of about 41%. The lower a product's margin, the less volume it can afford to lose before a price increase stops paying for itself.

Step 2 — The break-even volume loss

products["Margin"] = (products["Price"] - products["Unit cost"]) / products["Price"]
for pct in (0.03, 0.05, 0.08):
    products[f"Break-even loss at +{pct:.0%}"] = (pct / (products["Margin"] + pct)).round(3)
print(products[["Group", "Margin"] + [c for c in products.columns if c.startswith("Break-even")]].round(3).to_string())

For a price increase of p (as a fraction of the old price) on a product with gross margin m, gross profit is unchanged if volume falls by p ÷ (m + p). The formula comes from setting old gross profit equal to new: the new margin per unit is m + p in units of the old price, so volume can shrink by that ratio and still earn the same total. On a product with a 30% margin, a 5% increase breaks even at a volume loss of 14%; on one with a 55% margin, the same increase breaks even at 8% loss. Those are thresholds, not forecasts — they say how many customers would have to walk away before the increase was a mistake — and they are often the most useful numbers in a pricing discussion, because they turn a vague fear into a specific question about customers: would more than one in seven of our drill buyers really walk away over five per cent?

Step 3 — Scenarios with elasticity and contract locks

ELASTICITY = {"Fixings": -0.8, "Power tools": -1.6, "PPE": -0.5}    # % volume change per 1% price change
START = pd.Timestamp("2027-01-01")
YEAR_END = pd.Timestamp("2027-12-31")

def scenario(increases):
    v = vol.copy()
    v["Increase"] = v["Group"].map(increases).fillna(0)
    locked_days = (v["Contract until"].clip(upper=YEAR_END) - START).dt.days.clip(lower=0).fillna(0) + v["Contract until"].notna()
    v["Share at new price"] = 1 - locked_days / 365
    v["Volume change"] = v["Increase"] * v["Group"].map(ELASTICITY) * v["Share at new price"]
    v["New units"] = v["Annual units"] * (1 + v["Volume change"])
    v["New price"] = v["Price"] * (1 + v["Increase"] * v["Share at new price"])       # average price over the year
    v["Revenue"] = v["New units"] * v["New price"]
    v["Gross profit"] = v["New units"] * (v["New price"] - v["Unit cost"])
    return v.groupby("Group")[["Revenue", "Gross profit"]].sum()

SCENARIOS = {"Flat 5%": {"Fixings": 0.05, "Power tools": 0.05, "PPE": 0.05},
             "Targeted": {"Fixings": 0.06, "Power tools": 0.02, "PPE": 0.08},
             "Flat 8%": {"Fixings": 0.08, "Power tools": 0.08, "PPE": 0.08}}
results = {"Baseline": base[["Revenue", "Gross profit"]]} | {name: scenario(inc) for name, inc in SCENARIOS.items()}
summary = pd.DataFrame({name: r.sum() for name, r in results.items()}).T.round(0)
summary["GP change"] = summary["Gross profit"] - summary.loc["Baseline", "Gross profit"]
print(summary.to_string())

Each scenario is a set of increases by product group. Lost volume comes from an elasticity per group — the percentage change in volume for a one per cent change in price. The numbers here are assumptions to be agreed with the sales team: commodity fixings are fairly insensitive, power tools face online competition and are sensitive, safety equipment is a small, necessary purchase that customers rarely shop around for. A linear elasticity is a simplification, reasonable for increases of a few per cent and unreliable for large ones. Customers whose contracts fix prices until June pay the old price for half the year, so both their increase and their volume response are scaled by the share of the year at the new price. The scenarios can then be compared on gross profit, which is what the increase is for. The targeted scenario — higher increases where customers are less sensitive and lower ones where they are more — earns £30,635 more gross profit than the baseline, slightly more than a flat 5% at a similar average increase, and it is the only scenario in which revenue also grows. A flat 8% earns the most gross profit on paper, £42,911 more, but it does so by shrinking revenue and relies most heavily on the elasticity assumptions holding at a larger increase, which is exactly where a linear elasticity is least trustworthy.

Step 4 — An Excel model people can change

from openpyxl import load_workbook

path = "price_increase_model_2027.xlsx"
with pd.ExcelWriter(path) as xw:
    summary.to_excel(xw, sheet_name="Scenarios")
    pd.DataFrame(SCENARIOS).T.to_excel(xw, sheet_name="Increases")
    pd.Series(ELASTICITY, name="Elasticity").to_frame().to_excel(xw, sheet_name="Elasticity")
    products.to_excel(xw, sheet_name="Break-even")
    pd.concat({n: r for n, r in results.items()}, names=["Scenario"]).round(0).to_excel(xw, sheet_name="By group")
wb = load_workbook(path)
for name in ("Increases", "Break-even"):
    for row in wb[name].iter_rows(min_row=2, min_col=2):
        for c in row:
            if isinstance(c.value, float) and abs(c.value) < 1:
                c.number_format = "0.0%"
wb.save(path)
print(wb.sheetnames)

The workbook keeps the assumptions — the increases per scenario and the elasticities — on their own sheets next to the results, so a reader can see exactly what each scenario assumed. For a model that sales and finance will argue over, the next step is to read the assumptions back from those sheets at the start of the script, so a changed elasticity in the workbook and a rerun give a new set of results without anyone touching the code. Once a scenario is agreed, the new prices go into the dated price list with their effective date, as in Update a Price List with Effective Dates.

Common pitfalls

• Ignoring the break-even volume loss. It is the clearest single number for the decision.
• One elasticity for everything. Customers react differently by product group; set it per group.
• Treating elasticities as facts. They are assumptions; show them and test a range.
• Forgetting contract price locks. Customers on fixed prices keep the old price until their contract ends.
• Comparing scenarios on revenue. Compare on gross profit; revenue can rise while profit falls.

Conclusion

To model a price increase, start from a baseline of revenue and gross profit by product group, calculate each product's break-even volume loss as p ÷ (m + p), and run scenarios that apply increases by group, reduce volume through agreed elasticity assumptions, and keep contract customers on their old prices until their contracts end. Comparing scenarios on gross profit, with the assumptions visible next to the results in Excel, gives sales and finance a shared basis for the decision.

Frequently asked questions

How much volume can I lose after a price increase? For an increase p on a product with gross margin m, gross profit stays the same if volume falls by p ÷ (m + p).

What is price elasticity in a pricing model? The percentage change in volume for a one per cent change in price; a value of −1.5 means a 2% increase loses 3% of volume.

How do I handle customers on fixed-price contracts? Keep them at the old price until the contract ends and apply the increase only to the rest of the year.

Should price scenarios be compared on revenue or profit? On gross profit, because the purpose of an increase is to protect margin, and revenue can rise while profit falls.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Price-Volume-Mix Analysis — measuring the effect afterwards.
• Build a Break-Even Analysis in Excel with Python — the same idea for fixed costs.
• Build a Discount Leakage Report — protecting price after the increase.