Page 676 - Excel 2013 All-in-One For Dummies

658 Hide and Goal Seeking
To use the Goal Seek command, simply select the cell containing the for- mula that will return the result that you are seeking (referred to as the set cell), indicate what value you want this formula to return, and then indicate the location of the input value that Excel can change to return the desired result. Figures 1-10 and 1-11 illustrate how you can use the Goal Seek com- mand to find how much sales must increase to realize first quarter income of $200,000 (given certain growth, cost of goods sold, and expense assump- tions).
To find out how much sales must increase to return a net income of $200,000 in the first quarter, you first select cell B7, which contains the formula that calculates the first quarter income before you choose Goal Seek from the What-If Analysis button’s drop-down menu on the Ribbon’s Data tab or press Alt+AWG. This action opens the Goal Seek dialog box, similar to the one shown in Figure 1-10. 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 select the To Value text box and enter 200000 as the goal. Then, you select the By Changing Cell text box and select cell B3 in the worksheet (the cell that contains the first quarter sales).
Figure 1-10:
Using goal seeking
to find out how much sales must increase to reach
a target income.
Figure 1-11 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 $250,000 to $500,000 which, in turn, returns $200,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. (If this were not the case, the Step and Pause buttons in the dialog box would become active, and you could have Excel perform further iterations to try to narrow and ultimately eliminate the gap between the target and current values.)
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. If you change