WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Break-Even Point in Excel

Break-Even Point in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

Break-Even Point in Excel

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Break-Even Point Example 1

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).

Break-Even Point Example 1-1

Step 2: Now enter one more formula to find the Sales Value 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

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.

Break-Even Point Example 1-4

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.

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 for this enter zero because the Set Cell (BEP cell) value should be equal to zero, and that is our goal.

Break-Even Point Example 1-7

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.

Break-Even Point Example 1-8

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

Break-Even Point Example 1-9

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.

Break-Even Point Example 2

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

Break-Even Point Example 2-1

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

Example 2-2

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

Example 2-3

  • Now you can see the below option.

Example 2-4

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

Example 2-5

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.

Example 2-6

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

Example 2-7

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
12 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Break-Even Point Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More