The Solver is an add-in for Microsoft Excel which is used for the optimization and simulation of business and engineering models. It solves complex linear and non linear problems and can also be used in conjunction with VBA to automate tasks.
How To Enable The Solver Add-in
The add-in is included by default in MS Excel but kept disabled. In order to enable it, click the File Menu and choose Options.
Now, in the Excel Options dialogue box, select Add-Ins from the left sidebar and then hit the GO button next to Manage Excel Add-ins at the bottom.
What you have to do now is check the Solver Add-in option and hit OK to enable it.
How To Access The Solver Add-in
Solver is located under the Data tab as shown in the screenshot below.
How To Use Solver
Once you click the Solver option, it loads the dialogue box as shown below. Here you specify the parameters to run the solver.
These parameters will vary depending upon your problem, but lets see what are the most common and mandatory parameters and how they work so that you can utilize the solver in your daily life problems when needed. Followings are the three main parts of the solver which you should understand before applying the solver.
1. Target Cell
The target cell is the cell which will represent the objective or goal. Lets suppose a scenario in which the production manager of a firm would presumably want to maximize the profitability of the Product during each month. The cell that measures profitability would be the target cell.
2. Changing Cells
Changing cells are those cells, that can change or adjust to optimize the target cell. The production manager can adjust the amount produced for each product during a month. The cells in which these amounts are recorded are the changing cells.
Constraints are restrictions/limitations that you apply on the changing cells. For example in the above scenario, the product manager can’t use more of any available resource (for example, raw material and labor) than the amount of the available resource.
That’s it, specify all of the above mentioned parameters as per your spreadsheet. Set Objectives represents the target cell, By changing Variable Cells represents the Changing cells and you can add the constraint by hitting the Add button.
Once done with all the configuration, hit the Solve button to run the solver on your Excel spreadsheet.