Microsoft Office Tutorials and References
In Depth Information
Making the Case with Scenario Manager
To find out how much sales must increase to return a net income of $225,000
in the first quarter, select cell B7, which contains the formula that
calculates the forecast for the first quarter of 2014 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 in
Figure 8-5. 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 225000 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.
Figure 8-6 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 $432,692.31 which, in turn,
returns $225,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.
Making the Case with Scenario Manager
Excel’s Scenario Manager option on the What-If Analysis button’s drop-down
menu on the Data tab of the Ribbon enables you to create and save sets of
different input values that produce different calculated results, named
scenarios (such as Best Case, Worst Case, and Most Likely Case). Because these
scenarios are saved as part of the workbook, you can use their values to play
what-if simply by opening the Scenario Manager and having Excel show the
scenario in the worksheet.
Search JabSto ::




Custom Search