Build a Break-Even Analysis in Excel with Python

Build a live break-even workbook from Python: contribution margin, break-even units and revenue and margin of safety as Excel formulas driven by input cells, a multi-product break-even based on the sales mix, a price-by-cost sensitivity grid, and a cost-volume-profit chart, all checked against the same figures computed in pandas.

Report layouts & templates Formulas in workbooks

"How many do we need to sell to cover our costs?" is one of the first questions asked about any product, price change or new location, and the break-even analysis that answers it is a few lines of arithmetic: fixed costs divided by the contribution each unit makes. The value of building it as a workbook is that people can then change the price, the unit cost or the fixed costs and see the answer move — so the arithmetic should live in Excel formulas, with Python laying out the model and checking it. This page builds that workbook with openpyxl: an inputs block, the single-product break-even, margin of safety, a break-even for several products sold in a mix, a sensitivity grid showing break-even volume across prices and unit costs, and a cost-volume-profit chart. LibreOffice recalculates the workbook so every formula can be compared with the same figures computed in pandas. It belongs to Building Finance and Operations Reports.

Prerequisites

Bash

pip install openpyxl pandas

LibreOffice (soffice) is used only to recalculate the workbook for the checks. The example is a small manufacturer of three products with shared fixed costs of 240,000 a year and a sales forecast of 26,000 units.

Step 1 — The figures, computed in pandas first

Compute the answers in Python before building any formulas, so the workbook has something to be checked against:

Python

import pandas as pd

FIXED = 240_000
products = pd.DataFrame({"Product": ["Standard", "Plus", "Pro"], "Price": [18.0, 29.0, 52.0],
                         "Variable cost": [11.0, 16.5, 27.0], "Forecast units": [14_000, 8_000, 4_000]})
products["Contribution"] = products["Price"] - products["Variable cost"]
products["CM ratio"] = products["Contribution"] / products["Price"]
products["Mix"] = products["Forecast units"] / products["Forecast units"].sum()

wacm = (products["Contribution"] * products["Mix"]).sum()          # weighted average contribution per unit
be_units = FIXED / wacm
products["Break-even units"] = be_units * products["Mix"]
be_revenue = (products["Break-even units"] * products["Price"]).sum()
forecast_revenue = (products["Forecast units"] * products["Price"]).sum()
margin_of_safety = (forecast_revenue - be_revenue) / forecast_revenue
print(products.round(3).to_string(index=False))
print(f"weighted contribution {wacm:.4f}; break-even {be_units:,.0f} units, revenue {be_revenue:,.0f}; margin of safety {margin_of_safety:.1%}")

Each product's contribution is what a unit leaves after its own variable costs, available to pay the fixed costs. With several products sharing fixed costs there is no single break-even until you fix the mix: here 54% Standard, 31% Plus and 15% Pro, as in the forecast. The weighted average contribution per unit sold in that mix is 11.46, so the business breaks even at about 20,940 units — 11,275 Standard, 6,443 Plus and 3,221 Pro — or 557,315 of revenue. Forecast revenue is 692,000, 19.5% above break-even, which is the margin of safety: how far sales can fall short before the business makes a loss.

Step 2 — The model sheet with live formulas

Write the inputs as values and everything else as formulas that refer to them. Inputs are shaded so readers know which cells they may change:

Python

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Model"
INPUT = PatternFill("solid", fgColor="FFF4CE")
ws["A1"], ws["A1"].font = "Break-even analysis", Font(size=14, bold=True)
ws["A3"], ws["B3"] = "Fixed costs (per year)", FIXED
ws["B3"].fill = INPUT
headers = ["Product", "Price", "Variable cost", "Forecast units", "Contribution", "CM ratio", "Mix", "Break-even units", "Break-even revenue"]
for col, h in enumerate(headers, start=1):
    ws.cell(5, col, h).font = Font(bold=True)
first, last = 6, 6 + len(products) - 1
for i, p in enumerate(products.itertuples(index=False), start=first):
    ws.cell(i, 1, p.Product)
    for col, value in zip((2, 3, 4), (p.Price, p._2, p._3)):
        ws.cell(i, col, value).fill = INPUT
    ws.cell(i, 5, f"=B{i}-C{i}")
    ws.cell(i, 6, f"=E{i}/B{i}")
    ws.cell(i, 7, f"=D{i}/SUM($D${first}:$D${last})")
    ws.cell(i, 8, f"=$B$11*G{i}")
    ws.cell(i, 9, f"=H{i}*B{i}")
summary = [("Weighted contribution per unit", f"=SUMPRODUCT(E{first}:E{last},G{first}:G{last})", "0.0000"),
           ("Break-even units (all products)", "=B3/B10", "#,##0"),
           ("Break-even revenue", f"=SUM(I{first}:I{last})", "#,##0"),
           ("Forecast revenue", f"=SUMPRODUCT(B{first}:B{last},D{first}:D{last})", "#,##0"),
           ("Margin of safety", "=(B13-B12)/B13", "0.0%"),
           ("Forecast profit", f"=SUMPRODUCT(E{first}:E{last},D{first}:D{last})-B3", "#,##0")]
for r, (label, formula, fmt) in enumerate(summary, start=10):
    ws.cell(r, 1, label).font = Font(bold=True)
    ws.cell(r, 2, formula).number_format = fmt
for col, fmt in zip("BCDEFGHI", ["0.00", "0.00", "#,##0", "0.00", "0.0%", "0.0%", "#,##0", "#,##0"]):
    for r in range(first, last + 1):
        ws[f"{col}{r}"].number_format = fmt
ws.column_dimensions["A"].width = 32
for col in "BCDEFGHI":
    ws.column_dimensions[col].width = 15

The break-even units in B11 divide fixed costs by the weighted contribution in B10, and each product's share in column H is that total times its mix. Change Pro's price in B8 and its contribution, the weighted average, every break-even figure and the margin of safety all follow. Laying out inputs and formulas this way is covered more generally in Run What-If Scenarios on an Excel Model with Python, which also shows how to drive such a model's inputs from Python.

Step 3 — A sensitivity grid and a CVP chart

A single break-even number hides how sensitive it is. A grid of break-even volumes across a range of prices and variable costs for the main product — each cell a formula — shows it at a glance; a cost-volume-profit chart shows where revenue overtakes total cost:

Python

from openpyxl.chart import Reference, ScatterChart, Series

sens = wb.create_sheet("Sensitivity")
sens["A1"] = "Break-even units (all products) by Standard price (across) and Standard variable cost (down)"
prices = [16, 17, 18, 19, 20]
costs = [10.0, 10.5, 11.0, 11.5, 12.0]
for j, price in enumerate(prices, start=2):
    sens.cell(3, j, price).font = Font(bold=True)
for i, cost in enumerate(costs, start=4):
    sens.cell(i, 1, cost).font = Font(bold=True)
    for j, _ in enumerate(prices, start=2):
        col = sens.cell(3, j).column_letter
        weighted = (f"(({col}$3-$A{i})*Model!$G$6"
                    f"+SUMPRODUCT(Model!$E$7:$E${last},Model!$G$7:$G${last}))")
        sens.cell(i, j, f"=Model!$B$3/{weighted}").number_format = "#,##0"

cvp = wb.create_sheet("CVP")
cvp.append(["Units", "Revenue", "Total cost", "Fixed cost"])
for n, units in enumerate(range(0, 40_001, 5_000), start=2):
    cvp.append([units, f"={units}*Model!$B$13/SUM(Model!$D$6:$D${last})",
                f"=Model!$B$3+{units}*(Model!$B$13/SUM(Model!$D$6:$D${last})-Model!$B$10)", "=Model!$B$3"])
chart = ScatterChart()
chart.title, chart.x_axis.title, chart.y_axis.title = "Cost-volume-profit", "Units sold (forecast mix)", "Amount"
xs = Reference(cvp, min_col=1, min_row=2, max_row=cvp.max_row)
for col in (2, 3, 4):
    s = Series(Reference(cvp, min_col=col, min_row=1, max_row=cvp.max_row), xs, title_from_data=True)
    s.marker.symbol = "none"
    chart.series.append(s)
chart.x_axis.delete = chart.y_axis.delete = False
chart.width, chart.height = 18, 9
cvp.add_chart(chart, "F2")
wb.save("break_even.xlsx")

Each sensitivity cell recomputes the weighted contribution with that cell's price and cost for Standard, keeps Plus and Pro as in the model, and divides fixed costs by it. On the chart, revenue and total cost are straight lines in volume at the forecast mix — average price per unit, and fixed cost plus average variable cost per unit — and they cross at the break-even volume; the flat fixed-cost line shows how much of total cost does not move with volume. The chart's axes need delete = False to appear in recent Excel versions.

Step 4 — Recalculate and check against pandas

Python

import subprocess
from openpyxl import load_workbook

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "break_even.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/break_even.xlsx", data_only=True)
m = calc["Model"]
checks = {"weighted contribution": (m["B10"].value, wacm), "break-even units": (m["B11"].value, be_units),
          "break-even revenue": (m["B12"].value, be_revenue), "margin of safety": (m["B14"].value, margin_of_safety)}
for name, (excel, python) in checks.items():
    print(f"{name:<22} Excel {excel:>12,.4f}   pandas {python:>12,.4f}   {'ok' if abs(excel - python) < 1e-6 else 'MISMATCH'}")
s = calc["Sensitivity"]
print("sensitivity at price 18, cost 11:", round(s["D6"].value), "| at price 16, cost 12:", round(s["B8"].value), "| at 20, 10:", round(s["F4"].value))

Every model figure matches the pandas calculation, and the sensitivity cell for today's price and cost (18 and 11) reproduces the model's 20,940 units. The corners of the grid show the spread: if Standard's price fell to 16 while its cost rose to 12, the business would need about 24,375 units to break even — leaving a thin margin below the 26,000 forecast — while a price of 20 with a cost of 10 brings break-even down to about 18,353. That range, not the single number, is what makes the analysis useful in a pricing discussion. The verification technique is described in Recalculate Excel Formulas Without Excel in Python.

Common pitfalls

Hard-coded results. Write formulas that refer to input cells so the model stays live.
Ignoring the mix. Several products have no break-even until the mix is fixed.
One number instead of a range. Add a sensitivity grid.
Unchecked formulas. Recalculate and compare with an independent calculation.
Hidden chart axes. Set delete = False on openpyxl chart axes.

Conclusion

Compute contribution, the mix-weighted contribution, break-even units and revenue and the margin of safety in pandas, then write the same model to Excel as shaded inputs and formulas that refer to them, add a sensitivity grid of break-even volumes across prices and costs and a cost-volume-profit chart, and recalculate with LibreOffice to confirm that every formula agrees with Python. The result is a model people can use to answer their own "what if" questions.

Frequently asked questions

How do I calculate break-even units in Python? Divide fixed costs by contribution per unit, which is price minus variable cost; for several products use the contribution weighted by the sales mix.

How do I calculate break-even for multiple products? Fix a sales mix, compute the weighted average contribution per unit, divide fixed costs by it for total units, and split those units by the mix.

What is the margin of safety? The share by which forecast sales exceed break-even sales, (forecast revenue − break-even revenue) ÷ forecast revenue.

How do I make the Excel model update when inputs change? Write the inputs as values and every calculation as a formula referring to them, rather than writing the computed results.

Up one level: Building Finance and Operations Reports — finance reports from Python.
Build a Product Margin Report — margins by product.
Build a Budget vs Actual Variance Report — comparing against plan.
Add a Combo Chart with a Secondary Axis in openpyxl — more chart options.

More on report layouts & templates