Page 260 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 243
Chapter 18: Performing What-If Analysis 243
As soon as you select the Solve button, Excel closes the Solver Parameters and
then beeps before opening the Solver Results dialog box. In this case, Solver was
able to find a solution to the problem by balancing changes to the COGS and
Expenses that would result in the desired target net income of $650,000.
The Solver Results dialog box gives you a choice between retaining the adjusted
values in the Sales Forecast for 2007 table (Keep Solver Solution) and restoring
its original values (Restore Original Values). You can also save the solution as a
scenario.
6. Select the Save Scenario button to open the Save Scenario dialog box and then
type Optimal as the name in Scenario Name text box before you select OK.
As soon as Excel closes the Save Scenario button, you are returned to the Solver
Results dialog box.
7. Select the Restore Original Values option button in the Solver Results dialog box
and then select OK.
Because you saved the Solver’s solution with the scenario name, Optimal, you
can restore these values to the Sales Forecast table simply by opening the
Scenario Manager dialog box and then selecting its name before you select the
Show button.
8. Open the Solver Parameters dialog box again.
Note that the Solver Parameters dialog box retains your earlier settings for the
target cell, value, and changing cells in their respective used in obtaining the first
solution.
9. Select the Add button to the right of the Subject to the Constraints list box to
open the Add Constraint dialog box and then click the COGS cell, H5, in the work-
sheet to insert $H$5 in the Cell Reference text box. Accept the default <= compar-
ative operator. Click the insertion point in the Constraint text box and type 15%
before you select its Add button.
Excel clears the Cell Reference and Constraint text boxes so that you can define
a new constraint.
10. This time, click the Expenses cell, H7, in the worksheet to insert $H$7 in the Cell
Reference text box, and then click the insertion point in the Constraint text box
and type 12% before you select the OK button.
As soon as you select OK, Excel closes the Add Constraint dialog box and
returns you to the Solver Parameters dialog box, where the following compara-
tive expressions now appear in the Subject to Constraints list box:
• COGS<=15%
• Expenses<=12%
11. Select the Solve button.
This time, when the Solver Results dialog box appears, it indicates that the
Solver could not find a feasible solution. When the cost of goods sold and the
expenses are kept down to these lower percentages, the projected net income
must necessarily exceed the target of $650,000 (note that the Solver has actually
returned $757,375 to the Total_Income cell, G8, using these percentages).

12. Select the Restore Original Values option button in the Solver Results dialog box
and then select OK.
13. Position the cell cursor in cell A1 and then save your work in a new workbook
named Solved18-5.xls in your Chapter 18 folder in the My Practice Spreadsheets
folder. Close the workbook file as you exit Excel.