Break-Even Analysis in Excel: A Worked Study Example

By Shady

A break-even model is easier to understand when you can explain every input and check the result by hand. In this tutorial, you will build a small Excel model, calculate a break-even quantity and test how a change in cost affects it.

The business and figures below are fictional teaching examples. They are not financial, tax or investment advice. The model is deliberately simple: one product, a constant selling price and variable cost per unit, and fixed costs that remain unchanged within the activity range studied.

Start with the question and assumptions

A fictional workshop sells one type of notebook for $15 each. Its variable cost is $9 per notebook, and its fixed operating costs are $1,200 per month. Assume that all notebooks produced are sold, there are no inventory changes, and the model excludes interest and tax.

How many notebooks must it sell each month for operating profit to equal zero? Use one currency and one time period throughout. Mixing monthly fixed costs with annual sales quantities would produce a result that looks tidy but has no useful meaning.

Understand the calculation before opening Excel

Each notebook contributes $15 − $9 = $6 towards fixed costs and then operating profit. Dividing $1,200 by $6 gives a break-even quantity of 200 notebooks.

Check it: revenue at 200 units is $3,000. Variable costs are $1,800 and fixed costs are $1,200. Total costs therefore equal revenue, leaving operating profit of zero.

The standard relationship is fixed costs divided by contribution margin per unit. For a short textbook explanation of the formula and its assumptions, see OpenStax’s break-even chapter.

Build a small input section

Open a blank worksheet. Enter labels in column A and the following values or formulas in column B. Currency formatting changes appearance; keep the underlying inputs numeric.

The expected results are $6 in B5, 200 in B6 and B7, and $3,000 in B8. Some Excel regional settings use semicolons instead of commas as function separators. Use the separator expected by your installation.

Before using B6, check that B5 is positive. If price equals variable cost, the formula divides by zero; if price is lower, selling more units cannot cover positive fixed costs under these assumptions. A negative calculated threshold would not be a meaningful break-even target.

Create a table you can audit

In row 11, enter these headings: A11 Units, B11 Revenue, C11 Variable costs, D11 Total costs, E11 Operating profit. In A12 to A16, enter 0, 100, 200, 300 and 400.

Enter these formulas in row 12, then copy them down to row 16:

• B12: =A12*$B$2
• C12: =A12*$B$3
• D12: =C12+$B$4
• E12: =B12-D12

A12 changes to A13 as you copy down, while $B$2 stays fixed on the price input. The dollar signs lock the column and row reference. Microsoft explains relative and absolute references in its Excel documentation.

At zero units, operating profit should be −$1,200. At 200 units it should be zero. At 300 units it should be $600. These three checks help expose a missing fixed cost, incorrect reference or sign error.

Make the chart tell the same story

Create an XY scatter chart with straight lines using Units as the horizontal values and Revenue and Total costs as two series. Label both axes and identify the currency on the vertical axis.

The revenue line should start at zero. The total-cost line should start at $1,200. They should meet at 200 units and $3,000. If the chart disagrees with the table, inspect the selected ranges and series rather than adjusting the drawing to look right.

Test a change and explain it

Change B3 from 9 to 10. Contribution becomes $5 per notebook, so the exact break-even quantity rises to 240. The workshop now needs more sales to cover the same fixed costs because each sale contributes less.

Restore B3 to 9, then change B4 to 1,250. Exact break-even quantity becomes approximately 208.33 units. If notebooks can only be sold whole, 209 units are needed to reach non-negative operating profit. At 209 units, profit is $4; rounding up does not imply profit is exactly zero.

Check your understanding

For a fresh fictional case, use a price of $20, variable cost of $12 and monthly fixed costs of $960. Calculate contribution, break-even units and profit at 150 units before changing the sheet.

Answers: contribution is $8; break-even is 120 units; profit at 150 units is $240. Explain each result in a sentence. A spreadsheet is most useful when you can defend its assumptions and spot an impossible answer.

Continue with Management Accounting, or select its free first lesson in the resource library. For broader costing concepts, read Cost Accounting Made Practical.