Break-Even Point in Excel

Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

Excel Break-Even Point

Break-Even Point (BEP) in Excel is the first landmark every business wants to achieve to sustain itself in the market. So, even when you work for other companies as an Analyst, they may want you to find the Excel break-even point of business.

Now, we will see what precisely the break-even point is meant for. For example, your monthly expenditure is 15,000, including all the rental, communications, food, beverages, etc. But, on the other hand, your per day salary is 1,000. So, when you earn 15,000, it becomes a break-even point for you. Anything made after the break-even point will be considered a profit.

So in business terms, if the amount of profit and expenses are equal, that is called the break-even point. So in simple terms, the break-even point is where a project’s cash inflow should equal the project’s cash outflow.

The cost could come in multiple ways, and we classify that into Fixed CostFixed CostFixed Cost refers to the cost or expense that is not affected by any decrease or increase in the number of units produced or sold over a short-term horizon. It is the type of cost which is not dependent on the business activity.read more, Variable Cost, and other miscellaneous costs. Now, we will see some real-time examples of finding the break-even point in Excel analysis.

Break-Even-Point-in-Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Break-Even Point in Excel (wallstreetmojo.com)

How to Calculate Break-Even Points in Excel?

You can download this Break-Even Point Excel Template here – Break-Even Point Excel Template

Example #1

Ms. Sujit has undertaken a project of producing two-wheeler tires for two years. She has to spend ₹150 for each tire to make one tire. Her fixed cost per month is around ₹35,000, and another miscellaneous cost is ₹5,000 per month. She wants to sell each tire at ₹250 per tire.

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

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

Break-Even Point Example 1

To find the break-even point, Ms. Suji must put in some formula to find the total cost.

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

Break-Even Point Example 1-1

Step 2: To find the sales value, we must enter one more formula, i.e., Units * Sale Value.

Break-Even Point Example 1-2

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

Break-Even Point Example 1-3

We need to find the number of tires Ms. Suji wants to produce to zero the break-even point value.

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

Break-Even Point Example 1-4

We got a break-even point of -20,000. Suppose we keep entering until we get the break-even point amount as 0. It will take a lot of time. But, we can use the “Goal Seek” tool to identify the number of units required to achieve the break-even point.

Break-Even Point Example 1-5
  • For the first option of Goal Seek (Set cell), choose the cell BEP cell i.e., B10.
Break-Even Point Example 1-6
  • The second option of “Goal Seek” is “To value.” We must enter zero because the “Set cell“(BEP cell) value should equal zero, which is our goal.
Break-Even Point Example 1-7

The final option is “By changing cell,”i.e., by changing the cell, we want to make the BEP cell (Set cell) value zero (To value).

  • So, by finding the “Units Required” cell, we need to achieve the goal of BEP = 0, so we select cell B7.
Break-Even Point Example 1-8
  • Now, click on “OK. As a result, “Goal Seek” will calculate to set the Excel break-even point cell to zero.
Break-Even Point Example 1-9

“Goal Seek” has found the “Units Required” to get the “BEP” as zero. So, Ms. Suji must produce 400 tires in a month to achieve the Break-Even PointBreak-Even PointThe break-even point (BEP) formula denotes the point at which a project becomes profitable. It is determined by dividing the total fixed costs of production by the contribution margin per unit of product manufactured. Break-Even Point in Units = Fixed Costs/Contribution Margin read more.

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2

Mr. Suji must know what units are needed for the different variable costs per unit. For this, we must create a table like the one below.

Break-Even Point Example 2
  • For E3 cells, give a link to the BEP cell and B10 cell.
Break-Even Point Example 2-1
  • Now, we must select the newly created table, as shown below.
Example 2-2
Example 2-3
  • Now, we can see the below option.
Example 2-4
Example 2-5

We have selected the B4 cell because we have put different “Variable Cost Per Unit” row-wise in the newly created table. Accordingly, we have chosen the “Variable Cost Per Unit” as the “Row input cell.”

  • For the “Column input cell,” we must choose the “Units Required” cell because unit data are shown in columns in the newly created table.
Example 2-6
Example 2-7

So, now we will look at the green-colored cells in the table. For example, if the variable cost per unit is 144, Ms. Suji must produce 380 tires monthly. Similarly, if the price is 150, she must make 400 tires. Finally, if the cost price is 155, then she must produce 422 tires in a month to achieve BEP.

Things to Remember

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

This article is a guide to Break-Even Point in Excel. Here, we discuss how to calculate break-even point in Excel, examples, and a downloadable Excel template. You may also look at these useful functions in Excel: –