Goal Seek is another useful what-if analysis tool provided by Excel. With Goal Seek, Excel will find a value for a specified cell, that makes a given worksheet formula equal to a value that you define.

In other words, you can set a formula to a value (goal) that you would like to attain, and then specify one of the cells that the formula references, as a cell that Excel can adjust in order to reach the goal.

Take the following worksheet as an example.

The worksheet clearly shows a negative profit (loss) for the current worksheet values. We can use goal seek to find a break even point based on changes to a cell that is referenced (directly or indirectly) by the profit formula in cell B8. The profit formula is total income - total expenses, or B4 - B7. Cell B4 contains the number of units sold. This will be the cell that we will adjust to break even.

First, select cell B8, (the cell with the profit formula), by clicking on it, and then Invoke Goal Seek by choosing Tools->Goal Seek from the menu bar.

Cell B8 is entered into the Set Cell text field, because that is the cell that you selected just before invoking goal seek. In the To value text field type 0, and in the By changing cell field enter B2 by typing it in directly or clicking with your mouse. The resulting goal seek box should look like the following.

If you click the OK button, Goal Seek will find a value for Cell B2 (Units sold) that will make the profit equal to 0 (break even).

The Goal Seek Status box reports that a solution has been found. You can see the value 299.52 in cell B2. This means that if all other variables remain unchanged, you must sell 299.52 units to break even. Clicking the cancel button will restore the original worksheet values, and clicking OK will enter the goal seek solution values into the worksheet.

You can just as easily use goal seek to find the price per unit, or the cost per unit that would make the worksheet break even.