Using Excel Goal Seek

Have you ever found yourself repeatedly changing a spreadsheet number to try to get the result you want? We all have done that over the years. Excel provides a simple tool to make these type of calculations quick and easy. Here is a sample income statement with a calculation of net income before taxes as a percent of revenues:

You think you can reschedule your workforce in a more efficient manner to achieve 10% net income as a percent of sales.

You could start replacing the $225,000 salaries expense with smaller numbers until you find the one that gives you the 10% figure, or you can use the Goal Seek feature of Excel.

To find Goal Seek, click the Data tab of the Excel ribbon. Locate the What If Analysis section of the Data Tools area. Goal Seek is the second choice in the What If Analysis group. When you choose Goal Seek, a new dialog box will pop up on your screen. You may move that dialog box to a convenient location on your screen.

The goal seek box needs three pieces of information. It wants to know the result you seek, the location of that result, and the cell to modify to make that happen. For our purposes, the goal we seek is 10% in cell B14. We want to change cell B7 to make that happen.

Note: The cell where the new result goes must be a formula and the cell to be changed must be related in some way to that formula. In this case, changing the salaries expense line will change the net income before taxes, one of the factors in the percent calculation.

The first entry in Goal Seek is the cell we would like to set to our goal. The Set Cell box should be highlighted and will include the current cell. Click on cell B14 and the Set Cell box should read $B$14. If not, delete what is in the box and try again.

Next, type the value you desire in the To Value box. In this case, 10% (.1 would work just as well).

Finally, click on the By Changing Cell box and click the salaries expense cell, B7. Click the OK button and your result will appear.

In this case, the salaries expense has been adjusted to 202,400. If you are happy with the result, click the OK button to keep your adjustments. If not, click the Cancel button and your spreadsheet will revert to its previous state.

Goal seek is a simple tool that can save time. It modifies the contents of one cell to achieve a new result in one other cell. If you need more complex calculations involving multiple cells, you can use the Solver feature of Excel, but that is a topic for another time.

Written by Steven Tuttle - sdt@unh.edu