WallStreetMojo

WallStreetMojo

WallStreetMojo

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

Break-Even Analysis in Excel

Excel Break-Even Analysis

In simple terms, the break-even point is where our total cost is equal to the total revenue earned, so we can call this situation as “no profit no loss”. In graphics, the break-even point is where revenue total and total cost curves meet.

All the business models estimate their revenue and expenditure for the year, quarter, and month to know what is their break-even point in the business. In this article, we will show you how to calculate the “Break-Even” Analysis in excel.

Break Even Point

So Break Even is Revenue – Total Costs = 0

In economics, we call the break-even point as “the point of indifference,” and this analysis informs the management to know what is the minimum revenue required to cover its expenses.

The calculation of Break-Even points is different for the industry to industry. If the company is a Product selling company, then the number of units sold will be considered, and if the company is a Services Company, then it will be a different strategy.

Break-Even Analysis in Excel

How to do Break-Even Analysis in Excel?

You can download this Break Even Analysis Excel Template here – Break Even Analysis Excel Template

Example #1 – Using Goal Seek Tool

Company A has prepared 58 electronic devices; it has incurred a total fixed cost of 8 Per Unit and a variable cost of 6 per unit. Now this company doesn’t know at what price they need to sell these 58 devices to achieve the break-even point.

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

Construct this scenario in an excel worksheet; if you don’t know how to construct, then follow image formulas.

Break-Even Analysis Example 1

Now to find the break-even point, our profit amount should be zero.

  • Open the GOAL SEEK option from the DATA tab under What-If-Analysis in excel.

Break-Even Analysis Example 1-1

  • Now we can see below the Goal Seek window.

Break-Even Analysis Example 1-2

  • Set Cell will which cell we need to modify the value; in this case, we need to modify the Profit cell, so select B10 cell.

Break-Even Analysis Example 1-3

  • To Value means the Set Cell value should be what??? In this case, we need the Profit cell to be zero, so enter the value as 0.

Break-Even Analysis Example 1-4

  • The last option is By Changing cell, i.e., By changing which cell we need the profit value as 0. In this case, we need to find what should be the selling price to achieve the Break-Even point. So select the B7 cell.

Break-Even Analysis Example 1-5

  • Now click on Ok, Goal Seek will find the “Selling Price” required to achieve the Break-Even Point.

Break-Even Analysis Example 1-6

So, Company A has to sell the electronic devices at 14 per unit to achieve the Break-Even point.

Similarly, using the same Goal Seek analysis, you can find the selling price required to achieve a profit of 5000.

  • For this again, open the Goal Seek window and select the Profit Cell for Set cell option, and To Value will be 5000, and By Changing cell will be Selling Price cell, i.e., B7 cell.

Break-Even Analysis Example 1-7

  • This should give the required Selling Price to achieve 5000 profit.

Break-Even Analysis Example 1-8

So in order to achieve a profit of 4000, Company A has to sell at 114 per unit!!!.

Example #2 – Construct a Break-Even Table

Ms. Alisa is an entrepreneur who produces notebooks, and her costing model is as follows.

  • Fixed Cost = 4000
  • Variable Cost = 8 Per Unit
  • Selling Price = 25 Per Unit.

Now she wants to know how many units she needs to produce to achieve the Break-Even Point. Let’s help Ms. Alisa through our excel break-even analysis.

  • Now construct a table like below.

Break-Even Table Example 2

  • For Fixed Cost, Simply give cell link to B2 and make it an absolute reference.

Break-Even Table Example 2-1

  • For Variable, also enter the formula as No. of Units * Variable Cost Per Unit.

Break-Even Table Example 2-2

  • For Total cost, add “Fixed + Variable.”

Break-Even Table Example 2-3

  • For Revenue arrive the formula as No. of Units * Selling Price Per Unit.

Break-Even Table Example 2-4

  • For-Profit enter the formula as Revenue – Total Cost.

Break-Even Table Example 2-5

  • Now drag the formula of all the cells to 10 rows.

Break-Even Table Example 2-6

  • Now for No. of Units start filing from 100 to 300.

Break-Even Table Example 2-7

From the G13th cell, Profit is showing positive numbers, so to achieve Break-Even Point, Ms. Alisa needs to produce approximately 236 units.

  • Now for the same data, let’s create Break-Even Chart. Select the Total Cost, Revenue, and Profit column for this.

Break-Even Table Example 2-8

  • Insert line chart with markers.

Break-Even Table Example 2-9

So the point where the curves of “Revenue & Total Cost” intersects is called the Break-Even Point.

Things to Remember

  • Break-Even is the point where the business stands at no profit, no loss scenario.
  • Break-Even analysis is best suited for the production industry.
  • To find break-even in units formula is FC / (SP – VC).
  • The intersection point of both revenue & total cost curves is called a break-even point.

Recommended Articles

This has been a guide to Break-Even Analysis in Excel. Here we discuss how to do Break-Even Analysis using goal seek tool and construct a break-even table along with examples and downloadable templates. You may also look at these useful functions in excel –

  • Breakeven Analysis Examples
  • Break-Even Point in Accounting
  • BEP Formula
  • Break-Even Sales Calculation
22 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 Analysis Excel Template

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