Using Goal Seek

Goal Seek is another useful what-if analysis tool built into Excel. With Goal Seek, Excel can help you 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 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 should adjust in order to reach the goal.

Take the following small worksheet as an example.

• In the formula bar, you can see that the formula for total profit is the Total Income (B4) minus the total cost (B7). It should be noted that cells B4 and B7 themselves contain formulas. The formula in cell B4 is =B2*B3 (units sold multiplied by the price per unit). The formula in cell B7 is =(B5*B2)+B6 (materials per unit multiplied by units sold) plus the labor to make the units.

The following image shows the worksheet with some sample data.

• Clearly, at current production levels, the company is losing money. How would you figure out how many units to manufacture to break even or make a profit? One solution is to use goal seek to find a breakeven point based on changes to a cell that is referenced (directly or indirectly) by the profit formula (in cell B8).

1. To begin, select cell B8 (the cell with the profit formula) and then invoke the Goal Seek dialog by choosing Data → What-If Analysis → Goal Seek: 
   
2.  When the dialog box appears, B8 is already entered in the “Set cell” box because that is the cell that you selected just prior to invoking Goal Seek: 
   
3.  In the “To value” box enter 0 since a profit of 0 is the breakeven point). In the “By changing cell” field enter B2 (the number of units required).When everything is set up, the Goal Seek dialog looks like the following: 
   
4. When you click OK, Goal Seek will find a value for cell B2 (units sold) that will make the profit formula (B8) equal to 0 (breakeven).
   

The Goal Seek Status dialog reports that a solution has been found, and you can see the value 300 in cell B2. This means that if all other variables remain unchanged, you must sell 300 units to break even. At this point, you can click Cancel to restore the original worksheet values, or click OK to enter the goal seek solution values into the worksheet.

You can just as easily use goal seek to find the price per unit, materials per unit, or labor that would make the worksheet break even. You could also set a specific level of profit for your goal by entering a value of your choice in the “To value” field of the Goal Seek dialog.

The following example image shows how goal seek evaluates the price per unit required to make 1000 dollars in profit with 150 units sold.