How to Create a Break Even Chart in Excel for Your Business Expenses

Break-even analysis is a tool for evaluating the profit potential of a business model and for evaluating various pricing strategies. You can easily compile fixed costs, variable costs, and pricing options in Excel to determine the break even point for your product. This is the number of units that you need to sell at the price you set in order to break even.

Things You Should Know

Create a variable costs table in a new sheet that includes costs that change over time, such as shipping and commission.
Create a fixed costs table in a new sheet that includes costs that don't change over time, such as rent or insurance.
Enter your variable and fixed costs along with how many units you want to sell at a certain price to find your break even point.

Part 1

Part 1 of 5:

Adding a Variable Costs Table

Download Article

You'll be creating multiple sheets in this workbook to handle tracking all of your costs.
This will create a new blank worksheet.

Advertisement
This sheet will house the table that tracks all of your product's variable costs, such as shipping, commission, and other costs.
To create a basic variable costs table, enter "Description" into A1 and "Amount" into B1.
Below the "Description" header, enter the types of variable costs you'll be dealing with for your product.
You'll be filling out the actual costs later in the process.
7
Create a table from the data you've entered. Turning the data into a table will make it easy to plug into formulas later:
- Select all of the data, including the header row and the blank amounts, by clicking and dragging your mouse over all of the cells.
- Click the "Format as Table" button. You'll find this in the Home tab. If you're using Excel for Mac, click the Tables tab, click the "New" button, and then select "Insert Table with Headers."
- Check the "My table has headers" box. This will preserve the labels in the first row as header labels.
- Click the "Table Name" field in the upper-right corner and name it "VariableCosts."

Part 2

Part 2 of 5:

Adding a Fixed Costs Table

Download Article

This will create another blank worksheet.
This sheet will house all of the fixed costs for your product, such as rent, insurance, and other costs that don't change.
Like with the Variable Costs sheet, create a "Description" label in cell A1 and an "Amount" label in cell B1.
Fill out the first column with descriptions of your fixed costs, like "Rent."
You'll be filling out these costs after creating the rest of the spreadsheet.
6
Create a table from the data you've entered. Select everything you created on this sheet, including the headers:
- Click the "Format as Table" button in the Home tab.
- Check "My table has headers" to turn row 1 into headers for the table.
- Click the "Table Name" field and name the table "FixedCosts."

Part 3

Part 3 of 5:

Creating the Break Even Sheet

Download Article

This sheet will house your main BEP (Break Even Point) chart. You don't have to rename it to "BEP," but it will be easier to navigate your workbook if you do.
2
Create the layout for your break even sheet. For the purposes of this example, create your sheet using the following layout:
- A1: Sales - This is the label for the Sales section of the spreadsheet.
- B2: Price Per Unit - This will be the price you charge for each item you sell.
- B3: Units Sold - This will be the number of units you have sold at the specified price in a set timeframe.
- A4: Costs - This is the label for the Costs section of the spreadsheet.
- B5: Variable Costs - These are the costs of your product that you have control over (shipping, commission rates, etc.)
- B6: Fixed Costs - These are the costs of your product that you don't have control over (facility rent, insurance, etc.)
- A7: Revenue - This is the amount of money selling your products generates before costs are considered.
- B8: Unit Margin - This is the amount of money you make per unit after costs are considered.
- B9: Gross Margin - This is the total amount of money you make for all the units sold after costs.
- A10: BEP - This is the label for the Break Even Point section of the spreadsheet.
- B11: Units - This is the number of units you need to sell to match your cost outlay.
3
Change the numeric formats for the output and input cells. You'll need to change the numeric formats for certain cells in order for your data to appear correctly:
- Highlight C2, C5, C6, C8, and C9. Click the drop-down menu in the "Number" section of the Home tab and choose "Currency."
- Highlight C3 and C11. Click the drop-down menu and select "More number formats." Select "Number" and then set "Decimal places" to "0."
4
Create ranges to use in the formulas. Select and create the following ranges to make your formulas work. This will create variables that can be plugged into your formulas, allowing you to easily reference and update these values.
- Select B2:C3 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."
- Select B5:C6 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."
- Select B8:C9 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."
- Select B11:C11 and then click the "Formulas" tab. Click "Create from selection" and then click "OK."

Part 4

Part 4 of 5:

Entering Formulas

Download Article

1
Enter the variable costs formula. This will calculate the total variable costs for the number of items you sell. Click C5 and enter the following formula:
- =SUM(VariableCosts)*Units_Sold
2
Enter the fixed costs formula. This will calculate the total fixed costs for your product. Click C6 and enter the following formula:
- =SUM(FixedCosts)
3
Enter the unit margin formula. This will calculate the margin you make after variable costs have been considered. Click C8 and enter the following formula:
- =Price_Per_Unit-SUM(VariableCosts)
4
Enter the gross margin formula. This determines the total amount you make for all of the units you sell after variable costs. Click C9 and enter the following formula:
- =Unit_Margin*Units_Sold
5
Enter the BEP formula. This takes your fixed costs and compares them to your margins, informing you how many units you need to sell to break even. Click C11 and enter the following formula:
- =IFERROR(Fixed_Costs/Unit_Margin,0)

Part 5

Part 5 of 5:

Finding the Break Even Point

Download Article

1
Enter your business's variable costs. Return to the VariableCosts table and fill out all of the costs related to your product. The more accurate you are here, the more accurate your BEP calculation will be.
- Each cost in the VariableCosts table should be per unit sold.
Enter these costs into your Fixed Costs table. These are the costs of running your business, and should all be set to the same interval (for example, monthly costs).
In the BEP sheet, enter an initial estimated price per unit. You'll be able to adjust this as you perform the calculations
This is the number of units you intend to sell in the same timeframe as your Fixed Costs. For example, if your fixed costs includes monthly rent and insurance, the Units Sold will be the number of units sold in that same timeframe.
The Units output cell (C11) will display the number of units that you'll need to sell in your timeframe to break even. This number will change depending on the Price Per Unit as well as your Variable Costs and Fixed Costs tables.
Changing the Price Per Unit will change the number of units you need to break even. Try changing the price and see what happens with your BEP value. ^{[1]
X
Research source}

Community Q&A

Search

Add New Question

Question

What if I have multiple products? Also, what if I don't know my variable costs, but just want them to be a percentage of sales?

Community Answer

In this case, you can either do each separately and then add the end result together, or use an average.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 4 Helpful 4

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

Tips

Increasing your selling price will allow you to reach your break-even point with a lower sales volume, but it may also reduce your total sales.

Thanks
Helpful 0 Not Helpful 0

Submit a Tip

All tip submissions are carefully reviewed before being published

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!

Warnings

When entering your sales volume and your fixed expenses, make sure they are both based on the same period.

Thanks
Helpful 0 Not Helpful 2

[1]

Log in

How to Create a Break Even Chart in Excel for Your Business Expenses

Things You Should Know

Steps

Adding a Variable Costs Table

Adding a Fixed Costs Table

Creating the Break Even Sheet

Entering Formulas

Finding the Break Even Point

Community Q&A

Video

Tips

Warnings

You Might Also Like

References

About This Article

Is this article up to date?

Quizzes

You Might Also Like

Featured Articles

Trending Articles

Featured Articles

Featured Articles

Watch Articles

Trending Articles

Quizzes

Explore Categories