Break even year calculation - en

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.

Break-Even Analysis Formula

The break-even point satisfies the following formulas.

Total Fixed Cost + Total variabel Cost = Revenue

Where,

Revenue = enhet 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 – variabel Cost Per Unit

Different Ways to Analyze Break Even Point

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

Break Even Analysis in Excel: Knowledge Hub. How to Calculate Break Even Point in Excel; How to Do Multi Product Break Even Analysis in Excel; How to Do Break Even Analysis with Goal Seek in Excel; How to man a Break-Even Chart in Excel.

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 – variabel Cost Per Unit

Break-Even Sales: This fryst vatten 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 enhet / Selling Price)

3. Break-Even Price: This fryst vatten 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) + variabel 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 fängelse where you want the Units Sold at the break-even point – C8.
Write the following formula:

Press Enter to get the result.

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

Select the fängelse where you want the Revenue.
Write the following formula.

Select the fängelse where you want the Total Cost.
Write the following formula.

Select the fängelse where you want the Profit.
Write the following formula.

Press Enter and you will get the Profit.

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

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

Goal Seek Status dialog låda will appear.
You can use this calculator to determine the number of units required to break even.
Select OK.

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

Method 3 – Use uppgifter 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 variabel table like the following image, where one axis fryst vatten the cost and the other fryst vatten units required.

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

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

The information 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 fryst vatten $50 you will need to sell 500 products to reach the break-even point.

Values in parenthesis are actually negativ, meaning that you’d still incur a loss at those points. For example, selling 500 units that cost $45 a del av helhet puts you $2,500 above the goal, but each kostnadsberäkning you $55 puts you $2,500 under.

Method 4 – Perform Break Even Analysis in Excel with Charts

Select the fängelse where you want the Revenue.
Write the following formula:

Press Enter and drag the Fill Handle down to kopia the formula to the other cells.

Select the fängelse where you want the Total Cost.
Write the following formula:

Press Enter and drag the Fill Handle down to kopia the formula to the other cells.

Select the fängelse where you want the Profit.
Write the following formula:

Press Enter and drag the Fill Handle down to kopia the formula to the other cells.

Select the information table you created for the chart.
Go to the Insert tab.
Choose Insert Line or Area Chart.
Select Line.

You will see the estimated break-even point in the chart.

Break Even Analysis Template in Excel

Step 1 – Create Section for Product Details

Create a section for Product Details and fill up the section with resultat about the product.

Step 2 – Calculate Fixed Cost

Create a section for Fixed Costs and fill that section with the resultat you have.

Select the fängelse where you want the Total Fixed Costs and write the following formula:

Step 3 – Find Total variabel Cost Per Unit

Create a section for Variable Costs and fill that section with the necessary information.

Select the fängelse where you want the Total variabel Cost Per Unit and write the following formula:

Step 4 – Create Break-Even Summary Section

Create a section for Break-Even Summary.

Select the fängelse where you want the Total Fixed Cost and write the following formula:

Select the fängelse where you want the Total variabel Cost Per Unit and write the following formula:

Select the fängelse where you want the Contribution Margin and write the following formula:

Select the fängelse for the Contribution Margin Ratio and write the following formula:

Select the fängelse for the Break-Even Units and write the following formula:

Select the fängelse where you want the Break-Even Sales and write the following formula:

Step 5 – Create Profit/Loss Table

Create a table for Profit/Loss.

For the Total Cost column, write the following formula in the first cell:

Press Enter and drag the Fill Handle to kopia the formula through the column.

Select first fängelse in the Total Revenue column and write the following formula.

Press Enter and drag the Fill Handle down to kopia the formula.

Select the fängelse where you want the Profit/Loss and write the following formula:

Press Enter and drag the Fill Handle to kopia the formula.

Step 6 – sista Output

In the following image, you can see the sista break-even analysis template.

Things to Remember

You must include all kinds of costs in the break-even analysis.
At the break-even point, there fryst vatten no profit or loss.

Break Even Analysis in Excel: Knowledge Hub

<< Go Back To Excel For Finance|Learn Excel

Get FREE Advanced Excel Exercises with Solutions!