Break-Even Point in Excel

Excel Break-Even Point

Break-Even Point (BEP) in excel is the first landmark every business wants to achieve to sustain in the market. Even when you work for other companies as an analyst they may want you to find the Excel Break-Even Point of business.

Ok, now we will see what exactly the break-even point meant for.  For example, your monthly expenditure is 15000, including all the rental, communications, food, and beverages, etc.. and your per day salary is 1000, so the moment you earn 15000, it becomes a Break-Even Point for you and anything earned after BEP will be considered as Profit.

So in business terms, if the amount of profit and expenses are equal, that is called BEP. In simple terms, BEP is the point where the cash inflow of a project should equal the cash outflow of the project.

The cost could come in multiple ways, and we classify that into “Fixed Cost, Variable Cost, and Other Miscellaneous Cost.” Now we will see some of the real-time examples of finding the break-even point in excel analysis.

How to Calculate Break-Even Point in Excel?

Example #1

Ms. Sujit has undertaken a project of producing two-wheeler tires for a period of two years. To produce 1 tire, she has to spend Rs. 150 for each tire, and her fixed cost per month is around Rs. 35,000 /- and another miscellaneous cost is Rs. 5,000/- per month. She wants to sell each tire at Rs. 250 per tire.

Now she wants to know what should be the per month production to achieve the break-even point for her business.

Now from this information, we need to enter all these details given above to the worksheet area. Below I have listed the same.

Now to find the break-even point, Ms. Suji has to put some formula to find the total cost.

Step 1: Enter the formula as Total Cost = (Fixed + Other) + (Variable * Units).

Step 2: Now enter one more formula to find the Sales Value i.e., Units * Sale Value.

Step 3: Now enter the formula for BEP i.e., Sale Value – Total Cost.

Now we need to find what is the number tires Ms. Suji wants to produce to make the BEP value zero.

Step 4: We can find this by manually entering numbers in the Units Required cell. For example, now I will enter the value as 200 and see what the BEP is.

BEP is -20000; if you keep entering like this till you get the BEP amount as 0, it will take so much of time for you, but make use of the “Goal Seek” tool to identify the number of units required to achieve the BEP.

• Open the Goal Seek tool from the DATA tab.

• For the first option of Goal Seek (Set cell), choose the cell BEP cell i.e., B10.

• The second option of Goal Seek is To Value for this enter zero because the Set Cell (BEP cell) value should be equal to zero, and that is our goal.

No final option is By changing cell i.e., by changing which cell you want to make the BEP cell (Set Cell) value to be zero (To Value).

• So, by finding the Units Required cell, we need to achieve the goal of BEP = 0, so select the cell B7.

• Now click on OK, Goal Seek will do its calculation to set the excel break-even point cell to zero.

Her you go “Goal Seek” has found the “Units Required” to get the BEP as zero. So, Ms. Suji has to produce a minimum of 400 tires in a month to achieve the Break-Even Point.

Say thanks to “Goal Seek” Ms. Sujit!!!!

Example #2

Now for the same example, Mr. Suji wants to know what should be the units required at the different variable costs per unit. For this, create a table like the below one.

• Now for E3 cells, give a link of BEP cell i.e., B10 cell.

• Now select the newly created table, as shown below.

• Go to DATA tab What-If-Analysis in excel >>> Data Table.

• Now you can see the below option.

• For ROW INPUT CELL, choose Variable Cost Per Unit cell i.e., B4 cell.

The reason why we have selected B4 cell because in the newly created table, we have put different Variable Cost per unit row-wise, so accordingly, we have selected the variable cost per unit as Row input cell.

• Now for the column input cell, choose the Units Required cell because in the newly created table, units data shown in columns.

• Click on Ok; we should get the data table in excel like the below one.

So now look at the green-colored cells in the table, so if the variable cost per unit is 144, then Ms. Suji has to produce 380 tires in a month and similarly if the cost price is 150, she has to produce 400 tires, and if the cost price is 155, then she has to produce 422 tires in a month to achieve BEP.

Things to Remember

• BEP is a position of no profit & no loss.
• You need to consider all sorts of costs before you calculate the break-even point in excel.
• The moment cost and revenue are equal are considered as BEP.

Recommended Articles

This has been a guide to Break-Even Point in Excel. Here we discuss how to calculate the break-even point in excel along with examples and a downloadable excel template. You may also look at these useful functions in excel –

• Create a Break-Even Chart
• How to do Break-Even Analysis?
• Examples of Breakeven Analysis
• Break-Even Sales