Solver

The Goal Seek option allows us to easily find a value that meets the set goal. However, this is an option of modest possibilities. To find the optimal calculated solution, by setting the target value and additional parameters, use the Excel Add-in named Solver. It is a complex tool, with many possibilities, which comes up to the target by applying one of the methods of Operational Research.

In order to use Solver, we need to add it first. Since it’s not a standard part of Excel, it’s one of add-ins, we’ll do this by going to the Backstage View, clicking on the File tab, selecting Options, and then clicking on the Add-ins. In the drop-down menu, select the Excel Add-ins option, and then click the Go button. A dialog window opens where you need to select the Solver Add-in option and click the OK button. After that, the Solver icon will appear in the Data ribbon. To illustrate how it works, a simple example was made, with a table summarizing the product of the quantities sold and the prices of certain items.

After clicking on the Solver icon in the Data ribbon, a dialog window will open where we set the criteria for running the calculation. First, in the Set Objective field, enter the address of the cell where the target value is located. It should be a cell containing the formula affected by the variables found in some other cells in the table. Then we define a criterion which can be Max (maximum value), Min (minimal value) or Value Of (set value). In this case we’ll set the goal value, it is necessary to enter it in the field to the right of the name of this criterion. Then, in the By Changing Variable Cells field, we enter the range of cells where variables are located, whose value will change to reach the target value.

Finally, we add restrictions by clicking the Add button. A dialog box will open where we first enter the address of the cell and the condition, then the value (eg $C$3,<=,550). If we want to add a restriction and continue to add more constraints, we will click on the Add button. If we want to finish entering the limit, we will click the OK button and the dialog box will close, returning us to the main window for entering the calculation criteria.

Under the Add button in the main window there are 2 buttons: Change and Delete. If we clicked on any of the restrictions, in the list of created constraints, and clicked on Change, a window would be opened, where we can modify it. By clicking Delete, the selected restriction is deleted. By clicking the Reset All button, we reset all the criteria, and clicking Load/Save opens a window that allows us to save a model or upload the one we previously saved.

Under the list of restrictions, there is the option Make Unconstrained Variables Non-Negative. By selecting this option, we assign a condition that all those cells for which we have not imposed constraits must not be negative numbers. Then we choose the method of performing the calculation. Excel offers three options: GRG Nonlinear (Generalized Reduced Gradient – Nonlinear), Simplex LP and Evolutionary. These are three methods of operational research, and detailed explanations on how they are implemented within Solver can be found on the following link. By clicking on Options, we can additionally make calculation settings. Clicking on the Solve button begins its execution.

If Solver does not find a solution, a dialog box will appear with appropriate message. In most cases, after finding the solution, a message will appear that offers two options: Keep Solver Solution and Restore Original Values. By clicking on the Outline Reports option and selecting one of the reports (Answer, Sensitivity, Limits), after the confirmation worksheets will be automatically created describing how Solver has found the optimal solution. By clicking on the Return To Solver Parameters Dialog option, after confirming the solution, we return to the main window to adjust the limits or otherwise change the calculation parameters. If this option is not selected after confirmation, clicking the OK button closes the main window of the Solver.

The Save Scenario is an option that opens a dialog box where we will be asked to enter the name of the scenario. In this way, by entering various parameters and constraints, we can create a scenarios and later use them to simulate various business situations.