Break-Even Point in Excel

Excel Break-Even Point

Break-Even Point (BEP)Break-Even Point (BEP)Break-even analysis refers to the identifying of the point where the revenue of the company starts exceeding its total cost i.e., the point when the project or company under consideration will start generating the profits by the way of studying the relationship between the revenue of the company, its fixed cost, and the variable cost.read more 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 CostFixed Cost Fixed 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 Cost.” Now we will see some of the real-time examples of finding the break-even point in excel analysis.

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 Point 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 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 PointBreak-Even Point The 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!!!!

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 excelWhat-If-Analysis In Excel What-If Analysis in Excel is a tool for creating various models, scenarios, and data tables. It enables one to examine how a change in values influences the outcomes in the sheet. The three components of What-If analysis are Scenario Manager, Goal Seek in Excel, and Data Table in Excel.read more >>> Data Table.

Now you can see the below option.

For ROW INPUT CELL, choose Variable Cost Per UnitVariable Cost Per Unit Variable cost per unit refers to the cost of production of each unit produced, which changes when the output volume or the activity level changes. These are not committed costs as they occur only if there is production in the company.read more 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.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Break-Even Point in Excel

Excel Break-Even Point