What is Break-Even Point?
The break-even point is the level of sales or production where the total revenue equals the total cost, meaning that there is no profit or loss. It is an important indicator of the financial viability and performance of a business or a project. By calculating the break-even point, we can determine how many units we need to sell or produce to cover all of our fixed and variable costs, and how much we need to charge for each unit to achieve that level.
How to Calculate Break-Even Point in Excel?
There are different methods to calculate the break-even point in Excel, depending on the data and the format we have. In this article, we will use the following simple formula:
Break-Even Point = Fixed Cost / (Selling Price Per Unit – Cost Per Unit)
This formula assumes that the fixed cost, the selling price per unit, and the cost per unit are constant and known. The fixed cost is the cost that does not change with the level of sales or production, such as rent, salaries, insurance, etc. The selling price per unit is the amount of money we receive for each unit we sell. The cost per unit is the amount of money we spend to produce or acquire each unit, such as raw materials, labor, packaging, etc.
To apply this formula in Excel, we need to enter the values of the fixed cost, the selling price per unit, and the cost per unit in separate cells, and then use a formula cell to calculate the break-even point. For example, suppose we have the following data in cells A1 to C1:
Table
| Fixed Cost | Selling Price Per Unit | Cost Per Unit |
|---|---|---|
| 10000 | 50 | 30 |
Then, we can enter the following formula in cell D1 to get the break-even point:
= A1 / (B1 – C1)
The result is 500, which means that we need to sell or produce 500 units to break even.
How to Use a Table to Display the Break-Even Point in Excel?
We can also use a table to display the break-even point in Excel, along with other information such as the total revenue, the total cost, and the profit or loss for different levels of sales or production. To create a table, we need to follow these steps:
- Create a column with the labels “Units Sold”, “Total Revenue”, “Total Cost”, “Profit or Loss”, and “Break-Even Point” in cells A1 to E1.
- Enter some values for the units sold in column A, starting from cell A2. For example, we can enter 0, 100, 200, …, 1000 in cells A2 to A12.
- Enter the following formula in cell B2 to calculate the total revenue for the corresponding units sold, and then copy it down to the rest of the column:
= A2 * $B$1
This formula multiplies the units sold by the selling price per unit, which is in cell B1. The dollar signs make the reference absolute, so that it does not change when we copy the formula. 4. Enter the following formula in cell C2 to calculate the total cost for the corresponding units sold, and then copy it down to the rest of the column:
= A2 * $C$1 + $A$1
This formula multiplies the units sold by the cost per unit, which is in cell C1, and then adds the fixed cost, which is in cell A1. The dollar signs make the references absolute, so that they do not change when we copy the formula. 5. Enter the following formula in cell D2 to calculate the profit or loss for the corresponding units sold, and then copy it down to the rest of the column:
= B2 – C2
This formula subtracts the total cost from the total revenue, which gives us the profit or loss. 6. Enter the following formula in cell E2 to indicate the break-even point for the corresponding units sold, and then copy it down to the rest of the column:
= IF(D2 = 0, “Yes”, “No”)
This formula checks if the profit or loss is zero, which means that the break-even point is reached, and then displays “Yes” or “No” accordingly.
The table should look like this:
Table
| Units Sold | Total Revenue | Total Cost | Profit or Loss | Break-Even Point |
|---|---|---|---|---|
| 0 | 0 | 10000 | -10000 | No |
| 100 | 5000 | 13000 | -8000 | No |
| 200 | 10000 | 16000 | -6000 | No |
| 300 | 15000 | 19000 | -4000 | No |
| 400 | 20000 | 22000 | -2000 | No |
| 500 | 25000 | 25000 | 0 | Yes |
| 600 | 30000 | 28000 | 2000 | No |
| 700 | 35000 | 31000 | 4000 | No |
| 800 | 40000 | 34000 | 6000 | No |
| 900 | 45000 | 37000 | 8000 | No |
| 1000 | 50000 | 40000 | 10000 | No |
We can see that the break-even point is 500 units, as we calculated before, and that the profit or loss changes with the level of sales or production.
How to Use a Chart to Visualize the Break-Even Point in Excel?
We can also use a chart to visualize the break-even point in Excel, along with the total revenue and the total cost curves. To create a chart, we need to follow these steps:
- Select the data in columns A, B, and C, from row 1 to row 12.
- Go to the Insert tab and click on the Line Chart icon. Choose the first option, which is a 2-D line chart.
- A chart will appear on the worksheet, showing the total revenue and the total cost curves as two lines, with the units sold on the horizontal axis and the amount of money on the vertical axis.
- To add the break-even point to the chart, we need to create a new series with the values 0 and 500 for the units sold, and the corresponding values for the total revenue and the total cost, which are 25000. To do this, we can enter these values in cells G1 to H3, as shown below:
Table
| Units Sold | Amount |
|---|---|
| 0 | 25000 |
| 500 | 25000 |
- Then, right-click on the chart and select Select Data. In the Select Data Source window, click on the Add button under the Legend Entries (Series) section. In the Edit Series window, enter “Break-Even Point” as the Series name, select the range G1:G3 as the Series X values, and select the range H1:H3 as the Series Y values. Click OK to close the Edit Series window, and then click OK again to close the Select Data Source window.
- A new series will appear on the chart, showing the break-even point as a horizontal line at 25000, intersecting with the total revenue and the total cost curves at 500 units.
- To format the chart, we can do the following:
- Add a chart title by clicking on the Chart Elements icon (the plus sign) on the upper right corner of the chart, and checking the Chart Title option. Then, click on the chart title and enter “Break-Even Point Analysis in Excel” as the text.
- Add axis titles by clicking on the Chart Elements icon again, and checking the Axis Titles option. Then, click on the horizontal axis title and enter “Units Sold” as the text, and click on the vertical axis title and enter “Amount” as the text.
- Change the color and the style of the break-even point series by right-clicking on the horizontal line and selecting Format Data Series. In the Format Data Series pane, go to the Fill & Line section, and choose a different color and a dashed line style for the series.
- Add data labels to the break-even point series by clicking on the Chart Elements icon again, and checking the Data Labels option. Then, right-click on the data labels and select Format Data Labels. In the Format Data Labels pane, go to the Label Options section, and check the Value From Cells option. Then, select the range G1:G3 as the Custom Value. This will show the units sold at the break-even point on the chart.
We can see that the break-even point is where the total revenue and the total cost curves intersect, and that the profit or loss is the difference between the two curves.
Here is the break-even point analysis chart:
