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

Using the Solver 665
When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.
Saving and loading a model problem
The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must choose the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.
When you click the Load/Save button, Excel opens the Load/Save Model dialog box, containing a Select Model Area text box. This text box contains the cell references for a range large enough to hold all the problem’s param- eters, starting with the active cell. To save the problem’s parameters in this range, click OK. If this range includes cells with existing data, you need to modify the cell reference in this text box before you choose OK to prevent Excel from replacing the existing data.
After you click OK, Excel copies the problem’s parameters in the specified range. These values are then saved as part of the worksheet the next time you save the workbook. To reuse these problem parameters when solving a problem, you simply need to open the Solver Options dialog box, click the Load/Save button to open the Load/Save Model dialog box, click the Load button, and then select the range containing the saved problem parameters. When you click OK in the Load Model dialog box, Excel loads the parameters from this cell range into the appropriate text boxes in the Solver Parameters dialog box. You can then close the Solver Options dialog box by clicking OK, and you can solve the problem by using these parameters by clicking the Solve command button.
Remember that you can use the Reset All button whenever you want to clear all the parameters defined for the previous problem and return the Solver options to their defaults.
Creating Solver reports
You can create three different types of reports with the Solver:
✦ Answer report: Lists the target cell and changing cells with their original and final values, along with the constraints used in solving the problem.
✦ Sensitivity report: Indicates how sensitive an optimal solution is to changes in the formulas that calculate the target cell and constraints.
Book VII Chapter 1
Performing What-If Scenarios