In this article, you will learn about **break even analysis** in Excel. It covers everything about break-even analysis. Here, you will also learn about the break-even formula and how to create a template for break-even analysis.

Break-even is one of the most essential parameters when you are starting a business. A break-even analysis is a way to figure out how much money you need to make from selling a product or service to cover all your expenses. It helps you find the point where you start making a profit instead of losing money.

**Download Practice Workbook**

You can download the practice workbook from here.

**Table of Contents**Expand

## What Is Break-Even Analysis?

Break-even analysis shows the number of sales or units that need to be sold to achieve zero profit. This means when you will be able to cover all the expenses without having any profit or loss.

## Break-Even Analysis Formula

The break-even point satisfies the following formula.

**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:** The units that need to be sold to reach the break-even point. The formula forÂ Break Even Units is:

**Break-Even Units = Total Fixed Cost / Contribution Margin**

Here,

**Contribution Margin = Selling Price â€“ Variable Cost Per Unit**

**2. Break-Even Sales:** This is the sales at the break-even point. The formula for break-even sales is:

**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. The formula is:

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

## How to Do Break-Even Analysis in Excel

### 1. Use Generic Formula to Calculate Break-Even Point

- Select the cell where you want the
**Units Sold**at the break-even point >> write the following formula.

`=C5/(C6-C4)`

- Press
**Enter**to get the result.

### 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**>> select**Goal Seek**.

- Select the cell that contains
**Profit**as**Set cell**>> select**To value**as**0**>> select the cell that contains**Units Sold**as**By changing cell**>> select**OK**.

**Goal Seek Status**dialog box will appear >> select**OK**.

- Finally, you will see that you have got the
**Units Sold**at the break-even point.

### 3. Use Data Table Feature to Find Break Even Point in Excel

- Find out the
**Revenue**,**Total Cost**, and**Profit**by following the steps from the previous method.

- Create a table like the following image.

- Select the first cell of the table >> write the following formula to get the profit here.

`=C10`

- Press
**Enter**.

- Select the table >> go to the
**Data**tab >> select**What-If Analysis**>> select**Data Table**.

- The
**Data Table**feature will appear >> select**Variable Cost Per Unit**as the**Row input cell**>> select**Units Sold**as the**Column input cell**>> select**OK**.

- Finally, 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.

### 4. Perform Break Even Analysis in Excel with Charts

- Select the cell where you want the
**Revenue**>> write the following formula.

`=$C$6*B9`

- Press
**Enter**>> drag the**Fill Handle**down to copy the formula to the other cells.

- Select the cell where you want the
**Total Cost**>> write the following formula.

`=$C$5+(B9*$C$4)`

- Press
**Enter**>> drag the**Fill Handle**down to copy the formula to the other cells.

- Select the cell where you want the
**Profit**>> write the following formula.

`=C9-D9`

- Press
**Enter**>> drag the**Fill Handle**down to copy the formula to the other cells.

- Select the data table you created for the chart >> go to the
**Insert**tab >> select Insert**Line or Area Chart**>> select**Line**.

- Finally, you will see the break-even point in the chart.

## Break Even Analysis Template in Excel

### Step-01: Create Section for Product Details

- Create a section for
**Product Details**>> fill up the section with information about the product.

### Step-02: Calculate Fixed Cost

- Create a section for
**Fixed Costs**>> fill that section with the necessary information.

- Select the cell where you want the
**Total Fixed Costs**>> write the following formula.

`=SUM(C9:C14)`

- Press
**Enter**.

### Step-03: Find Total Variable Cost Per Unit

- Create a section for
**Variable Costs**>> fill that section with the necessary information.

- Select the cell where you want the
**Total Variable Cost Per Unit**>> write the following formula.

`=SUM(C19:C21)+(C6*C24)`

- Press
**Enter**.

### Step-04: Create Break-Even Summary Section

- Create a section for
**Break-Even Summary**.

- Select the cell where you want the
**Total Fixed Cost**>> write the following formula.

`=C15`

- Press
**Enter**.

- Select the cell where you want the
**Total Variable Cost Per Unit**>> write the following formula.

`=C25`

- Press
**Enter**.

- Select the cell where you want the
**Contribution Margin**>> write the following formula.

`=C6-G7`

- Press
**Enter**.

- Select the cell where you want the
**Contribution Margin Ratio**>> write the following formula.

`=G8/C6`

- Press
**Enter**.

- Select the cell where you want the
**Break-Even Units**>> write the following formula.

`=G6/G8`

- Press
**Enter**.

- Select the cell where you want the
**Break-Even Sales**>> write the following formula.

`=G10*C6`

- Press
**Enter**.

### Step-05: Create Profit/Loss Table

- Create a table for Profit/Loss.

- Select the cell where you want the
**Total Cost**>> write the following formula.

`=$C$15+($C$25*D14)`

- Press
**Enter**>> drag the**Fill Handle**to copy the formula.

- Select the cell where you want the
**Total Revenue**>> write the following formula.

`=D14*$C$6`

- Press
**Enter**>> drag the**Fill Handle**to copy the formula.

- Select the cell where you want the
**Profit/Loss**>> write the following formula.

`=F14-E14`

- Press
**Enter**>> drag the**Fill Handle**to copy the formula.

### Step-06: Final Output

- In the following image, you can see the final 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 is no profit or loss.

## Conclusion

In the end, we can say that this article covers almost everything there is to know about break-even analysis in Excel. I hope you will find this article helpful. If you have any queries or any suggestions you want to share, feel free to comment in the comment section below.

## Break Even Analysis in Excel: Knowledge Hub

- How to Calculate Break Even Sales with Formula in Excel
- Mortgage Break-Even Analysis in Excel
- 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 Do NPV Break Even Analysis in Excel
- How to Make a Break-Even Chart in Excel
- How to Calculate Break Even Analysis with Formula in Excel

**<< Go Back To ****Excel For Finance** **| Learn Excel**