Break-Even Chart in Excel: Build One in 10 Minutes

A break-even chart takes 10–15 minutes to build in Excel — if you know the right data structure and chart type. Two lines, one intersection — and you can see exactly how many units you need to sell before you stop losing money.

But most entrepreneurs either do this math in their heads or skip it entirely. Both are bad ideas. Mental math drifts, and skipping the number turns every business decision into a coin flip. Excel is cheap, visual, and anyone on your team can check it.

# How to Calculate the Break-Even Point: Formula with Example

BEP (units) = Fixed Costs / (Price − Variable Cost per unit)

That’s the core formula. Everything else is a variation of it.

Let’s work through a concrete example. Say you manufacture notebooks. Fixed costs (rent, admin salary, internet) — UAH 50,000/month (roughly $1,250). Selling price per notebook — UAH 250 ($6.25). Variable cost per notebook (paper, cover, packaging) — UAH 100 ($2.50).

Contribution margin per unit: 250 − 100 = UAH 150 ($3.75).

BEP = 50,000 / 150 = 334 notebooks per month.

Sell fewer than 334 — you’re losing money. Sell more — you’re profitable. That’s not an abstraction; it’s the floor your sales plan should never drop below.

# BEP in Revenue Terms

Sometimes units aren’t the most useful measure. Here’s how to convert to money:

BEP (revenue) = Fixed Costs / Contribution Margin Ratio

Contribution margin ratio = (Price − VC) / Price = 150 / 250 = 0.6

BEP (revenue) = 50,000 / 0.6 = UAH 83,333 ($2,083) per month.

You need both figures. Units tell you how much to produce. Revenue tells you what to show investors.

# How to Build a Data Table for Your Break-Even Chart

The chart runs on data. And here’s where most people make their first mistake — they try to build a chart “from memory” without a proper table. Don’t do that.

The table structure is straightforward. Three columns:

Column A — units sold. Start at 0, step by 50–100 units, go up to roughly twice your calculated BEP.

Column B — revenue. Excel formula: =A2*$G$2 (where G2 = selling price). Drag down.

Column C — total costs. Formula: =$G$3 + A2*$G$4 (G3 = fixed costs, G4 = variable cost per unit). Drag down.

That’s your table. It’s also the entire foundation of the chart.

# Put Your Input Parameters in a Separate Block

This isn’t optional — it’s the difference between a working model and a one-time calculation. Create a dedicated input zone on the sheet:

• G1 = “Price”
• G2 = 250
• G3 = “Fixed Costs”
• G4 = 50000
• G5 = “Variable Cost/unit”
• G6 = 100

When any parameter changes, the entire chart updates automatically. You change one cell — everything recalculates. No digging through formulas.

# Step-by-Step: Building the Break-Even Chart in Excel

Table’s ready. Now the chart itself.

Step 1. Select all three data columns (A, B, C) — including headers.

Step 2. Go to Insert → Charts → Line → choose “Line with Markers” or plain “Line.” Do NOT pick a bar chart or pie chart — they’re useless here.

Step 3. Excel will draw a two-line chart. By default, the X-axis shows row numbers, not units sold. Fix this.

Step 4. Right-click the chart → Select Data → under “Horizontal (Category) Axis Labels,” click Edit → select the range from Column A (units sold).

Step 5. Label your data series. Double-click each line → “Format Data Series” → confirm the lines are named “Revenue” and “Total Costs.”

Step 6. Add a chart title — “Break-Even Chart.” Label the axes: X = “Units Sold,” Y = “Amount (UAH).”

Step 7. Find the intersection visually. That’s your BEP. You can add a vertical dashed line via Insert → Shapes, but that’s cosmetic at this point.

Realistically — this takes about 7–8 minutes once your data is ready. Another 2–3 minutes for formatting.

# How to Label the Break-Even Point on the Chart

Not required, but useful. Add an extra data series — a single point at coordinates (334; 83,500). Set its chart type to “Scatter.” In the data label options, choose “Value from cells” and point it to a cell containing the text “BEP: 334 units.”

# Excel Template: What’s Inside and How to Use It

A good BEP template isn’t just a table with formulas. It has structure:

1. “Parameters” sheet — input block for FC, VC, and price. No formulas in these cells — just numbers you type.
2. “Calculation” sheet — auto-generated units/revenue/costs table, 20–30 rows.
3. “Chart” sheet — the diagram, which updates whenever parameters change.
4. “Scenarios” sheet — three columns for optimistic, base, and pessimistic forecasts side by side.

If you’ve worked with budget modeling in Excel, the principle is identical: inputs separate, calculations separate, visualization separate.

To use the template from scratch — replace three numbers in the parameters block. Excel handles the rest.

# How to Read the Chart and Act on the Results

Chart’s built. Two lines cross — now what?

Left of the intersection — loss zone. Revenue (blue line) is below costs (orange line). Right of it — profit zone. The further right you operate, the wider your margin.

But the BEP number alone doesn’t tell you whether you’re in good or bad shape. You have to compare it against your actual or projected sales volume.

If your average monthly volume is 500 units and your BEP is 334, you’re operating with a ~33% safety buffer. Solid. If you’re selling 360 units with a BEP of 334 — you’re on the edge. One slow month and you’re in the red.

# Margin of Safety

The formula: (Actual Sales − BEP) / Actual Sales × 100%

At 500 units sold with BEP of 334: (500 − 334) / 500 × 100% = 33.2%.

This is the margin of safety. A healthy range is 20–30% or above. Below 15% — you’re in fragile territory.

So ask yourself: is my current volume comfortably above BEP, or am I one bad month away from a loss?

# Common Mistakes When Calculating BEP in Excel

There aren’t many ways to get this wrong — but each mistake distorts the picture significantly.

Mistake 1: Mixing fixed and variable costs. Production workers’ wages are variable (they scale with output). An accountant’s salary is fixed. Confuse them and your BEP is wrong from the start. Always ask: does this cost change when volume changes?

Mistake 2: Ignoring VAT. If you’re VAT-registered, the tax cuts into your revenue. Calculate BEP on net revenue (excluding VAT) — otherwise you’ll understate your break-even point.

Mistake 3: Starting the table at 100 units instead of 0. If Column A starts at 100, the cost line won’t show fixed costs at zero volume — and that’s a critical point. It shows exactly how much you lose if you sell nothing at all.

Mistake 4: Using an unweighted average price across products. If you sell multiple products with different margins, a simple average price produces a misleading BEP. Either calculate BEP per product separately, or use a weighted contribution margin.

And — the most common mistake of all — recalculating BEP only once a year. Supplier prices went up? Recalculate. Rent increased? Recalculate. That’s exactly what the Excel model is for.

# Break-Even in Real Business Scenarios: Three Examples

# Example 1: Coffee Shop

Fixed costs: UAH 80,000/month (~$2,000) — rent, 2 staff salaries, utilities. Average transaction: UAH 120 ($3.00). Variable cost per cup: UAH 40 ($1.00). BEP = 80,000 / (120 − 40) = 1,000 cups per month — about 33 cups per day.

A typical neighborhood café in Kyiv pulls 60–80 cups daily. The margin of safety is comfortable.

# Example 2: Online Course

This one’s interesting. Fixed costs are minimal — platform subscription, ads, UAH 15,000/month (~$375). Variable cost per student: essentially nothing (maybe a certificate and some support time), UAH 200 ($5). Course price: UAH 3,000 ($75). BEP = 15,000 / (3,000 − 200) = 6 students per month.

That’s why online education is so attractive economically. The break-even point is reachable even with a modest marketing budget.

# Example 3: Clothing Manufacturer

Fixed costs: UAH 200,000/month (~$5,000). Variable cost per item: UAH 800 ($20). Price: UAH 1,500 ($37.50). BEP = 200,000 / (1,500 − 800) = 286 units per month.

That’s a real production target. If the workshop capacity is 400 units/month, the margin of safety is 29% — acceptable. But if capacity maxes out at 300 units, the business is operating near breakeven and any disruption — a supplier delay, a slow sales week — turns the month unprofitable.

A break-even chart isn’t a complex financial model. It’s 10 minutes in Excel and a clear answer to one question: how much do I need to sell to stop losing money? Once you have that answer, you can actually start thinking about profit.

Save the template, plug in your numbers — and you’ll have a working tool worth revisiting every time something material changes. Price, rent, supplier costs — all of it shifts your break-even point. Better to see it on a chart than discover it on your bank statement.

Read next: how to connect BEP with cash flow budgeting — the P&L budget vs cash flow budget comparison is the logical next step in building a financial model for a small business.