Break Even Analysis in Excel (Calculations and Template)

Let’s use the following dataset of sales and costs for a product to conduct a break even analysis.

Download Practice Workbook

You can download the practice workbook from here.

Beak Even Calculation.xlsx

Break-Even Analysis Formula

The break-even point satisfies the following formulas.

Total Fixed Cost + Total Variable Cost = Revenue

Where,

Revenue = Unit Price * Number of Units Sold

So, the number of units that need to be sold at the break-even point becomes.

Units Sold = Total Fixed Cost / Contribution Margin

Here,

Contribution Margin = Selling Price – Variable Cost Per Unit

Different Ways to Analyze Break Even Point

There are different ways to analyze the break-even point. They are:

1. Break-Even Units: How many units need to be sold at a predetermined price to reach the break-even point:

Break-Even Units = Total Fixed Cost / Contribution Margin

Here,

Contribution Margin = Selling Price – Variable Cost Per Unit

2. Break-Even Sales: This is how much you need to earn from sales to break even:

Break-Even Sales = Total Fixed Cost / Contribution Margin Ratio

Here,

Contribution Margin Ratio = 1 – (Variable Cost Per Unit / Selling Price)

3. Break-Even Price: This is the price of the product you will need for reaching the break-even point when you know the production volume:

Break-Even Price = (Total Fixed Costs / Production Volume) + Variable Cost Per Unit

How to Do Break-Even Analysis in Excel

Method 1 – Use Generic Formula to Calculate Break-Even Point

Let’s take the sample provided as the base for calculations
Select the cell where you want the Units Sold at the break-even point – C8.
Write the following formula:

=C5/(C6-C4)

Press Enter to get the result.

Method 2 – Apply Goal Seek Feature to Calculate Break Even Point in Excel

Select the cell where you want the Revenue.
Write the following formula.

=C6*C7

Press Enter.

Select the cell where you want the Total Cost.
Write the following formula.

=C5+(C4*C7)

Press Enter.

Select the cell where you want the Profit.
Write the following formula.

=C8-C9

Press Enter and you will get the Profit.

Go to the Data tab.
Select What-If Analysis.
Choose Goal Seek.

Select the cell that contains Profit in Set cell.
Put To value as 0.
For By changing cell, choose a cell for Units Sold.
Select OK.

Goal Seek Status dialog box will appear. Select OK.

The formula will change the Units Sold to reach the break-even point.

Method 3 – Use Data Table Feature to Find Break Even Point in Excel

Determine the Revenue, Total Cost, and Profit cells by following the steps from the previous method.

Create a variable table like the following image, where one axis is the cost and the other is units required.

Select the first cell of the new table at the intersection of the headers.
Write the following formula to get the profit here:

=C10

Press Enter.

Select the table.
Go to the Data tab and select What-If Analysis.
Choose Data Table.

The Data Table feature will appear.
Select Variable Cost Per Unit from the first table as the Row input cell.
Select Units Sold from the first table as the Column input cell.
Select OK.

Excell will fill in the table according to the listed variables in the headers.
You can see that when the Variable Cost Per Unit is $50 you will need to sell 500 products to reach the break-even point. Values in parenthesis are actually negative, meaning that you’d still incur a loss at those points. For example, selling 500 units that cost $45 a piece puts you $2,500 above the goal, but each costing you $55 puts you $2,500 under.