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 how many units 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 is therefore equal to your total revenue, resulting in the “break-even” point.

Calculate Break-Even Analysis with Goal Seek

Say you’ve set up a worksheet with the number of units you are going to sell, the fixed expenses that occur regardless of how many units you 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 you sell 10 units as shown above, you have a loss of $1,000.

To calculate the break event point, you need to find out how many units you need to sell in order for the net profit to be zero.

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

 

 

2. You then need to set three values:

• Set cell – e.g., F3 – this cell has to contain a formula and is the cell whose value changes to the amount specified. In the example above, there is profit formula which works out the profit obtained based on the variables you have entered into the worksheet (i.e., cost per unit, fixed expenses, and sales price per unit).
• To value – this is the value you want the Set cell to be. In this case, you want the break-even point so this (the Net Profit) is zero.
• By changing cell – this is the cell that is going to change – i.e., how many units you need to sell (B3)

 

 

3. Click OK to start the Goal Seek analysis. Since there are multiple variables, it may take a while to obtain an answer!

 

 

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

Calculate Break-Even Analysis Point in Google Sheets

In Google Sheets, you 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.

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

 

 

2. Goal Seek opens 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 is placed (i.e., B3 – the number of units to be sold changes).

 

 

3. You can amend the Options to set the maximum iterations or the tolerance or time limit values.

 

 

4. Click Solve to solve the problem.

5. Once Goal Seek has found a solution, a Goal Seek Complete message pops up, and the result is displayed on the screen.