Sometimes when doing what-if analysis in Excel 2016, you have a particular outcome in mind, such as a target sales amount or growth percentage. When you need to do this type of analysis, you use Excel's Goal Seek feature to find the input values needed to achieve the desired goal.
To use the Goal Seek feature located on the What-If Analysis button's drop-down menu, you need to select the cell containing the formula that will return the result you're seeking (referred to as the set cell in the Goal Seek dialog box). Then indicate the target value you want the formula to return as well as the location of the input value that Excel can change to reach this target.
The following figures illustrate how you can use the Goal Seek feature to find out how much sales must increase to realize first quarter net income of $525,000 (given certain growth, cost of goods sold, and expense assumptions) in a sales forecast table.
To find out how much sales must increase to return a net income of $525,000 in the first quarter, select cell B7, which contains the formula that calculates the forecast for the first quarter of 2017 before you click Data →What-If Analysis→Goal Seek on the Ribbon or press Alt+AWG.
This action opens the Goal Seek dialog box, similar to the one shown. Because cell B7 is the active cell when you open this dialog box, the Set Cell text box already contains the cell reference B7. You then click in the To Value text box and enter 525000 as the goal. Then, you click the By Changing Cell text box and click cell B3 in the worksheet (the cell that contains the first quarter sales) to enter the absolute cell address, $B$3, in this text box.
The next figure shows you the Goal Seek Status dialog box that appears when you click OK in the Goal Seek dialog box to have Excel go ahead and adjust the sales figure to reach your desired income figure. As this figure shows, Excel increases the sales in cell B3 from $450,000 to $772,058.82 which, in turn, returns $525,000 as the income in cell B7.
The Goal Seek Status dialog box informs you that goal seeking has found a solution and that the current value and target value are now the same. When this is not the case, the Step and Pause buttons in the dialog box become active, and you can have Excel perform further iterations to try to narrow and ultimately eliminate the gap between the target and current value.
If you want to keep the values entered in the worksheet as a result of goal seeking, click OK to close the Goal Seek Status dialog box. If you want to return to the original values, click the Cancel button instead.
To flip between the "after" and "before" values when you've closed the Goal Seek Status dialog box, click the Undo button or press Ctrl+Z to display the original values before goal seeking and click the Redo button or press Ctrl+Y to display the values engendered by the goal seeking solution.