• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Breakeven Sales|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Breakeven Sales|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Breakeven Sales
sp_PrintTopic sp_TopicIcon
Breakeven Sales
Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
1
December 23, 2019 - 5:58 pm
sp_Permalink sp_Print

1) I have tried to compute the breakeven sales, but it appears to be incorrect
2) I have also tried to compute what the Net profit would be based on various sales values using data tables

I have attached sample data.

 

It would be appreciated if someone could kindly check my formula's and amend and advise where I went wrong

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
December 24, 2019 - 10:48 am
sp_Permalink sp_Print

Hi Howard,

Have you tried using Goal Seek? I presume your sales figure is 'Turnover'. When you use Goal Seek to find where net profit = 0 it returns a turnover figure of 436,000.

Mynda

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
3
December 24, 2019 - 4:53 pm
sp_Permalink sp_Print

Hi Mynda

 

Thanks for the reply

I can use Goal Seek to find what the Sales figure (turnover) is where Net profit = 0

 

i just cannot understand why my breakeven formula  being fixed costs/Gross Margin % does not give  me the correct Breakeven

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
December 25, 2019 - 9:08 am
sp_Permalink sp_Print

Hello,

The term ”break even” is for me how many units you need to sell to cover the costs. With this in mind you should use following formula:

Fixed cost / (Price per unit - Variable cost per unit) = Break even point in units

As I normally don’t work with queries like this I did a search on this topic and found several sites with good information, one of many stated this on their site.

Calculating your break-even point

There are a few basic formulas for determining a business’s break-even point. One is based on the number of units of product sold and the other is based on
points in sales dollars.

  • To calculate a break-even point based on units: Divide fixed costs by the revenue per unit minus the variable cost per unit. The fixed costs are those that do not change no matter how many units are sold. The revenue is the price for which you’re selling the product minus the variable costs, like labor and materials.

    Break-Even Point (Units) = Fixed Costs ÷ (Revenue per Unit – Variable Cost per Unit)

  • When determining a break-even point based on sales dollars: Divide the fixed costs by the contribution margin. The contribution margin is determined by subtracting the variable costs from the price of a product. This amount is then used to cover the fixed costs.

    Break-Even Point (sales dollars) = Fixed Costs ÷ Contribution Margin

    Contribution Margin = Price of Product – Variable Cost

Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
5
December 25, 2019 - 4:12 pm
sp_Permalink sp_Print

Thanks for your input Anders

 

My Gross profit (Gross Margin) is Sales - Variable costs
Contribution margin is Gross Profit (gross margin) /Sales
Breakeven = Fixed costs/Sales

However, when to set the Net Profit to zero, by changing the sales value, I get 436,000, yet my breakeven formula gicve me a value of 398,361

 

i have attached my sample data

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
December 26, 2019 - 9:58 am
sp_Permalink sp_Print

Hello,

It has passed many years now since I worked with such financial data, so please correct me in case I am overlooking some aspects. Based on your numbers in your sample file your total costs are 436,000 = 108,000 (fixed) + 328,000 (variable), so I would say that you have your financial break even there. But that does not tell anything of how many units you need to sell.

As I understand it you use the other calculations when determining the number of units you need to sell to cover all costs.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
December 27, 2019 - 10:34 am
sp_Permalink sp_Print

Hi Howard,

I think what we're all forgetting, me included, is that when you change the Sales value, so too does the Variable Costs value need to change, since it is directly correlated to the sales.

At 398,361 sales the variable costs reduces to 290,361, which is breakeven since it covers the 108,000 in fixed costs. i.e. the variable costs are 72.89% (1-27.11%) of sales.

So, your breakeven formula is correct.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Howard Cohen
Member
Members
Level 0
Forum Posts: 48
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
8
December 27, 2019 - 12:55 pm
sp_Permalink sp_Print

Hi Mynda

Thanks for your input

 

I certainly did forget that variable costs change as sales volume changes

 

When I make my variable costs to be Sales * (1-0.271111), my data table works out 100%

 

Regards

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Nada Perovic
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.