# Break-Even Analysis in Excel: Formulas, Goal Seek, and a Data Table

Build a break-even model that answers four questions at once: how many units, at what revenue, what happens if costs rise, and what price you would need. Uses only built-in Excel features.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/break-even-analysis-in-excel-goal-seek
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## Break-Even Analysis in Excel

Break-even is the volume at which contribution exactly covers fixed costs. The formula is one division. The reason to build it in a spreadsheet rather than on paper is that the interesting questions are the ones that come after: what if the supplier raises prices, what if I discount, what price do I need to break even at half this volume.

Tool: [Try the break-even calculator](https://dothecalculation.com/calculators/break-even-calculator) — Get break-even units, revenue, and contribution margin without building the model first.

## Step 1: the model

**Inputs and calculations**
| Cell | Label | Value or formula |
| --- | --- | --- |
| B1 | Price per unit | 48.00 |
| B2 | Variable cost per unit | 29.50 |
| B3 | Fixed costs per month | 12500 |
| B5 | Contribution per unit | =B1-B2 |
| B6 | Contribution margin % | =B5/B1 |
| B7 | Break-even units | =B3/B5 |
| B8 | Break-even revenue | =B7*B1 |
| B9 | Units to hit a target profit | =(B3+B11)/B5 |
| B11 | Target monthly profit | 4000 |

**The two formulas that matter**

```
Break-even units = Fixed costs / (Price − Variable cost)    ·    Break-even revenue = Break-even units × Price
```
- Contribution per unit: 48.00 − 29.50 = 18.50
- Break-even: 12,500 / 18.50 = 675.7, so 676 units
- Break-even revenue: 676 × 48 = 32,448

> **Round up, never down** — Wrap the units formula in ROUNDUP: =ROUNDUP(B3/B5, 0). At 675 units you are still 13 dollars short. You cannot sell part of a unit into break-even.

## Step 2: getting fixed and variable right

The model is only as good as the split, and this is where most break-even analysis goes wrong. Variable costs change with each unit sold. Fixed costs do not change within the relevant range.

**Common classifications**
| Cost | Usually | Watch for |
| --- | --- | --- |
| Materials, cost of goods | Variable | Volume discounts make it step down |
| Payment processing | Variable | Roughly 3% of price, easy to forget |
| Outbound shipping | Variable | Free-shipping thresholds distort it |
| Sales commission | Variable | Only where it is a percentage of the sale |
| Rent, insurance, software | Fixed | Steps up when you outgrow the space |
| Salaried staff | Fixed | Becomes semi-variable with overtime |
| Hourly production labour | Variable | Fixed if the hours are guaranteed |

Semi-variable costs, such as a utility bill with a standing charge plus usage, should be split across both lines rather than forced into one. Putting the whole bill in fixed overstates break-even; putting it in variable understates it.

## Step 3: Goal Seek for the reverse questions

Goal Seek answers "what input produces this output" when you cannot rearrange the formula quickly. It lives under Data, What-If Analysis, Goal Seek.

**Three Goal Seek setups on the same model**
| Question | Set cell | To value | By changing |
| --- | --- | --- | --- |
| What price breaks even at 400 units? | B7 | 400 | B1 |
| What fixed cost can I carry at 500 units? | B7 | 500 | B3 |
| What variable cost do I need at 550 units? | B7 | 550 | B2 |

The first returns a price of $60.75. That is the more useful form of the question: not "what is my break-even" but "if I can only realistically sell 400, what would I have to charge", which is often the point at which a product idea gets abandoned or repriced.

> **Goal Seek overwrites the cell** — It writes the solved value into the input cell permanently. Copy the model to a second sheet before running it, or note the original value first, otherwise your baseline is gone.

## Step 4: a two-way data table for sensitivity

Goal Seek answers one question at a time. A data table answers a grid of them at once, showing break-even units across a range of prices and variable costs together.

- Put the formula =B7 in a corner cell, say D14.
- Across row 14 to the right of it, enter price values: 44, 46, 48, 50, 52.
- Down column D beneath it, enter variable cost values: 27, 28, 29.50, 31, 32.
- Select the whole block from D14 to the bottom right.
- Data, What-If Analysis, Data Table. Row input cell B1, column input cell B2.

**Break-even units across price and variable cost**
| Var. cost \ Price | $44 | $46 | $48 | $50 | $52 |
| --- | --- | --- | --- | --- | --- |
| $27.00 | 736 | 659 | 596 | 544 | 500 |
| $28.00 | 782 | 695 | 625 | 568 | 521 |
| $29.50 | 862 | 758 | 676 | 609 | 554 |
| $31.00 | 962 | 834 | 735 | 657 | 595 |
| $32.00 | 1,042 | 893 | 782 | 695 | 625 |

The grid makes the asymmetry obvious. A $2 price rise cuts break-even by about 70 units. A $1.50 increase in unit cost adds about 50. Because contribution is the denominator, small moves at either end produce large moves in required volume, and that sensitivity is the real output of the exercise.

## Step 5: the break-even chart

Build a small table of units from 0 to roughly twice break-even, in steps. Three columns: total revenue =units*$B$1, total cost =$B$3+units*$B$2, and profit =revenue-cost. Select the units column and the two totals, then insert a line chart.

The crossing point is break-even. The vertical distance between the lines at your expected volume is your profit, and the angle between them is your contribution margin, which is why a shallow angle means the business is fragile to a small drop in volume.

## Step 6: margin of safety

**Margin of safety**

```
Margin of safety % = (Expected units − Break-even units) / Expected units × 100
```
- Expecting 900 units against a 676 break-even: (900 − 676) / 900 = 24.9%
- Sales can fall by a quarter before you lose money.

This is the figure worth putting on a dashboard rather than break-even itself. A margin of safety under about 20% means a normal bad quarter takes you into a loss, and that is a warning you can act on before it happens.

Tool: [Try the profit margin calculator](https://dothecalculation.com/calculators/profit-margin-calculator) — Check the contribution margin behind your break-even and see what a discount does to it.

**What is the break-even formula?**

Break-even units = fixed costs divided by contribution per unit, where contribution is price minus variable cost per unit. Break-even revenue is that unit figure multiplied by the price.

**How do I use Goal Seek for break-even?**

Data, What-If Analysis, Goal Seek. Set the break-even cell to the volume you can realistically sell, and change the price cell. Excel solves for the price that makes it true. Copy the model first, because Goal Seek overwrites the input.

**Is payment processing a fixed or variable cost?**

Variable. It scales with each sale, usually around 3% of the price, and leaving it out of variable costs understates break-even by a meaningful margin on low-margin products.

**What is a good margin of safety?**

There is no universal figure, but below about 20% a routine drop in sales pushes you into a loss. Read it alongside how volatile your demand actually is rather than against a benchmark.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/break-even-analysis-in-excel-goal-seek). Quote freely with attribution and a link to this page._