Microsoft Excel and Google Sheets make finding the break-even point very easy with the Goal Seek function. You can use this function to set the value of any formula to a number you want by changing the value of one of the source cells for the formula.
For this example, I’ll use a very simple formula for profit.
The formula for the profit cell is:
(Volume X Revenue Per Unit) – (Volume X Variable Cost Per Unit) – Fixed Costs
That formula restated using the cell references above is: =(B6*B3)-(B6*B5)-B4
You could use algebra to restate this formula to solve for the break-even point (i.e., when profit equals zero). The faster and easier way is to use Goal Seek.
Where to Find Goal Seek
To access Goal Seek, select the data ribbon and then click on “What-If Analysis” to see Goal Seek.
For Google Sheets fans, you can add Goal Seek functionality to Google Sheets. To do so, select the Extensions menu, then “Add-Ons,” and then “Get add-ons.”
Search for “Goal Seek.” You will see a Goal Seek add-on by Google that you can download and install.
Goal Seek will now be listed under your Extensions menu. The examples in this article are all from Excel, but the functionality is very similar in Google Sheets.
Using Goal Seek to Find a Break-Even Point
When you select Goal Seek from the Excel menu, the Goal Seek dialog box opens:
In this example, I’m:
- Setting cell B7 (Profit)
- To a value of 0 (Breakeven)
- By changing cell B6 (Volume)
After clicking the OK button, it gives me a break-even volume of 25.
This shows how you can perform your own break-even analysis in Excel. You could also use online calculators. The U.S. Small Business Administration (SBA) has a great break-even point calculator that walks you through the concepts and calculation of break-even. I have a free price change analysis workbook that calculates the break-even sales and price.
Using Goal Seek to Change Many Formulas
I’ll show a more advanced example. I’ll be applying it to a new location analysis workbook. To learn more about the location analysis workbook, click here. In the contribution statement below, what if I wanted to break even in September? I would want to:
- Set the Total Net Contribution Before Taxes (cell J32 of the Contribution Statement on the left)
- To zero
- By changing the number of bags of beans sold (cell K8 of the Gross Profit worksheet on the right)
The Goal Seek box with those cells and amounts looks like this:
After running Goal Seek, it says that 8,722 bags need to be sold to break even in September.
This shows you that a number that’s connected by multiple formulas to a final formula can still be modeled via Goal Seek. Changing the number of bags changes many numbers on the GrossProfit worksheet. That changes the sales and cost of goods on the contribution worksheet. To the right is what the Gross Profit worksheet looks like after Goal Seek.
These examples also show why it’s a good idea to isolate your key assumptions or variables in a cell and then use links and formulas in the calculations. You can use Goal Seek or make other changes to those key variables, and the impact will flow through the remainder of the analysis.
Uses for Break-Even Analysis
Break-even analysis has been one of the most useful tools I’ve used in my career. I’ve used it for setting prices, designing products, and deciding whether to open new locations. I explain this more in my article on How to Move from Complexity to Clarity with a Break-Even Analysis.
I also show uses of break-even analysis in my course on analyzing new locations and marginal profitability analysis.
For more info, check out these topics pages: