Calculate Break-Even Analysis Point in Excel & Google Sheets

This tutorial demonstrates how to calculate the break-even point in Excel and Google Sheets.

 

 

Calculating a break even analysis enables you to calculate what you need to sell  (i.e., the number of units of something) in order for your expenses to equal to the sales price and therefore give you a profit of zero.  Your total cost will therefore be equal to your total revenue thus resulting in the ‘break even’ point.

Calculate Break Even Analysis with Goal Seek

In the example we will use, we have setup a worksheet with  the no of units we are going to sell, the fixed expenses that occur regardless of how many units we sell, the variable expense (cost) per unit to produce/supply the unit and finally the sale price of the unit.

The net profit is worked out by multiplying the sales price per unit ($300 per unit x 10 units) and taking away the Fixed Expenses ($1,500) and the variable expenses ($250 per unit x 10 units).

If we sell 10 units as shown above, we will have a loss of $1,000.

To calculate the break event point, we need to find out how many units we need to sell in order for the Net Profit to be zero.

With cell F3 highlighted, in the Ribbon, select Data > What-if Analysis > Goal Seek.

We then need to set 3 values:

Set cell – e.g., F3 –  this cell has to contain a formula and is the cell whose value will change to the amount specified.  In the example above, there is profit formula which works out the profit obtained based on the variables we have entered into the worksheet (i.e., cost per unit, fixed expenses and sales price per unit).

To value – this is the value that we wish to change the Set cell to show – in this case we want the break even point so this is zero.

By changing cell – this is the cell that is going to change – i.e., how many units we need to sell (B3)

Click on OK to start the Goal Seek analysis. Due to the fact that we have a few variable, it make take a while to obtain an answer!

Once it has found a solution, click OK.  In this case, we need to sell 30 units in order to break even!

Calculate Break-Even Analysis Point in Google Sheets

In Google Sheets, we have to install the Goal Seek add-on.  If you don’t already have this add-on  installed, see: How to Add Goal Seek to Google Sheets.

With the Google Sheet in which you wish to use Goal Seek, In the Menu, select Add-ons > Goal Seek > Open.

Goal Seek will open on the right hand side of your screen.   Set the cell that contains the formula (e.g., F3) and then in the To Value field, enter the value you wish it to change to (i.e., 0).  Finally,  select the cell where the result will be placed (i.e., B3 – the number of units to be sold will change).

You can amend the Options if you need to set the maximum iterations or the tolerance or time limit values.

 

Click Solve to solve the problem.

Once Goal-Seek has found a solution, a pop up will be shown and the result will be displayed on the screen.