Don’t forget to share this post

If you’re starting a business or expanding an existing one, you need to know how to perform a break-even analysis before you make a decision. A break-even analysis helps you determine when a new business, product, or service will become profitable. In other words, you can decide whether it’s worth starting the new project.

In this article, you will learn about break-even analysis, when it’s useful, and the limitations of this method. You will also learn how to calculate the break-even point (BEP) and how to use Google Sheets for break-even analysis through examples. If you’d like to learn more about financial analysis, check out our related article on Cost-Volume-Profit Analysis: CVP Formula and Examples.

What is Break-Even Analysis?

As the name suggests, this type of analysis is used to determine when you will break even - in other words, when you’ve covered all your costs. The break-even point (BEP) refers to the moment when you neither lose nor make money: your profits equal your losses.

This is an important financial concept, particularly if you're starting a new business or considering expanding your current business. Using this method, you can figure out how many units of a new product you need to sell or how much profit a new channel needs to make to reach the break-even point.

Break-even analysis can help you make smarter pricing decisions, set specific sales objectives, and keep track of all costs, including fixed ones. Additionally, including this analysis in the proposal will add credibility to your business plan, which can help you get funding.

Break-Even Analysis Limitations

However, there are some limitations to this method that you need to keep in mind. The model is very simplistic in terms of the variables considered, which are completely static. In other words, there’s no accounting for real-life circumstances, like having multiple products with different prices and costs. Additionally, the model assumes that one variable can change at a time, so you’ll probably need to run multiple scenarios.

Since this method doesn’t consider the effects of demand, competitors, or time, it’s important to remember that it’s only an estimate. In the next section, you will learn about the formula and how to adjust it for different situations.

Budget Forecasting and How to Make a Budget Forecast
Budget Forecasting and How to Make a Budget Forecast

Discover what a budget forecast is, how it differs from a budget, why it is crucial, and the basics of creating one for your business from scratch.

READ MORE

What is the Break-Even Analysis Formula?

If you want to know how many units you need to sell to break even, use this formula:

break-even_quantity = fixed_costs / (price_per_unit - variable_cost_per_unit)

If you’d prefer your answer in dollars, use this formula instead:

break_even_in_dollars = fixed_costs / contribution_margin_ratio

To use the formula above, you’ll need to calculate the contribution margin ratio with this formula:

contribution margin ratio = (unit_price - variable_cost_per_unit) / unit_price

How to Calculate the Break-Even Point?

There are three main components to break-even analysis:

  • Fixed costs
  • Variable costs
  • Average price

It’s very important to ensure that all costs are taken into account, both fixed and variable. However, it is also easy to forget about some of the fixed costs, especially if they don’t contribute to the product in an obvious way.

  1. 1. Add up all your fixed costs and ensure you’re not overlooking any expenses.
  2. 2. Add up all of your variable costs per unit.
  3. 3. Determine the average price for the product.
  4. 4. Use the formula with the values from the previous steps.

Break-Even Analysis Examples

Follow the instructions below to calculate the break-even point for new product sales. I will use Google Sheets for the examples below, but you can easily do the same in Excel.

How to Calculate the Break-Even Point for Sales in Units?

Imagine you’re thinking of selling a new product. Fixed costs are $200, and it costs you $4.99 to make a single unit, which you will sell for $9.99.

  1. 1. Add the fixed costs, variable costs, and price to your spreadsheet.
Break Even Analysis How to Calculate the Break Even Point Add Variables
Break-Even Analysis: How to Calculate the Break-Even Point - Add Variables
  1. 2. In a separate cell, add the formula using the cell references from the previous step.
Break Even Analysis How to Calculate the Break Even Point Add Unit BEP Formula
Break-Even Analysis: How to Calculate the Break-Even Point - Add Unit BEP Formula
  1. 3. That’s it. You need to sell 40 units to break even.
Break Even Analysis How to Calculate the Break Even Point BEP in Units
Break-Even Analysis: How to Calculate the Break-Even Point - BEP in Units

How To Perform What-If Analysis in Google Sheets

The What-If Analysis is a very important concept in financial modeling. Here’s how to perform a What-If Analysis in Google Sheets.

READ MORE
How To Perform What If Analysis in Google Sheets

How to Calculate the Break-Even Point for Sales in Dollars?

If you want your answer in dollars instead of units, you need the formula that uses the contribution ratio. I’ll use the same values as in the previous example, so fixed costs are $200, variable cost per unit is $4.99, and sales price per unit is $9.99.

  1. 1. Add the fixed costs, variable costs, and price to your spreadsheet.
Break Even Analysis How to Calculate the Break Even Point Add Variables 2
Break-Even Analysis: How to Calculate the Break-Even Point - Add Variables
  1. 2. Calculate the contribution margin ratio as shown below.
Break Even Analysis How to Calculate the Break Even Point Calculate Contribution Margin Ratio
Break-Even Analysis: How to Calculate the Break-Even Point - Calculate Contribution Margin Ratio
  1. 3. In a separate cell, add the formula using cell references to the values obtained in the previous steps.
Break Even Analysis How to Calculate the Break Even Point Add Dollar BEP Formula
Break-Even Analysis: How to Calculate the Break-Even Point - Add Dollar BEP Formula
  1. 4. That’s it. You need to sell $399.60 to break even.
Break Even Analysis How to Calculate the Break Even Point Add Dollar BEP Formula 2
Break-Even Analysis: How to Calculate the Break-Even Point - Add Dollar BEP Formula

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

As you have seen, break-even analysis is a very useful financial planning tool. Whether you’re thinking of starting a business, adding a new product to your mix, or adding a new sales channel, break-even analysis can help you make smarter decisions.

You now know about the benefits and limitations of break-even analysis and how to use the break-even point formula to calculate the BEP in units and in dollars. You also know how to calculate the break-even point in Google Sheets, so you can quickly run through different scenarios by changing the variables.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Sep 7 2022, Updated Nov 11 2022