Solver in Excel

What is Solver?

Microsoft Excel facilitates an add-in programming tool known as Solver that uses operational research techniques to find the optimal solutions for objective problems. This tool operates with a group of cells (also known as decision variables), or only variable cells for calculating the formulas used in the cells. The Solver is used when you have a value in mind for your dependent variable, or you want to maximize the dependent value or minimize it (which is formula based). You want to change the values of the input variables to achieve that value. It is precisely the opposite of ‘What-If Analysis’. In other words, in ‘What-If Analysis’, we change the values of the input variables to see the impact on the dependent variable, over here, it’s the reverse of it.

Do note that Solver may not always be able to give you a solution as it may not be able to find one which achieves all the constraints (conditions) set by you.

Advantages of Solver

1. The solver is a handy tool and can lead to minimizing the cost of inventory or maximizing the production of a product.
2. Solver searches for optimal solutions for different types of decision-based problems.
3. This method calculates the max or min value of a cell by changing the values of the corresponding cells.
4. It satisfies all the restrictions on cells and generates the desired output for the objective cell.
5. It helps you to change the amount of your predictable budget and evaluates the effect on your expected profit.
6. It enhances the profit of the company or even to increase/decrease the number of alerts being generated by various risk strategies in a bank! 

Solver Parameters

Let’s load the Solver Add-in, and step by step look at its different parameters.

1. Set Objective: This objective box is used to enter the cell reference for the objective cell wherein the specified cell must contain a formula. The objective can be maximized, minimized, or can be set to some user-defined value. 

• To: This parameter is used to set the value for the objective cell.
• MAX- To set the value of the objective cell to large.
• MIN- To set the value of the objective cell to small.
• VALUE- To set the value of the objective cell to the particular value specified by the user in the value bar (present at the right corner).
• By Changing Variable Cells: This box parameter is used to enter the reference or name for each cell range decision variable. The data in the variable cells can be changed to achieve the output. The non-adjacent references are separated by using commas. The user can specify up to 200 variable cells.

• Subject To The Constraints: This box parameter enables the user to enter the constraints that you want to apply. Solver Constraints are the restrictions or limitations of the feasible solutions of problems, and these are the conditions that must be satisfied by the solver. The subject constraints can be further added or edited by the following options:
• Add- This option allows the user to add the constraints by selecting the cell reference edit box (present on the left side) and the Constraint edit box (present on the right side).
• Change- This option is used to make some changes to the existing subject constraints.
• Delete- If you want to delete any of your constraints, click on the Delete option.
• Reset All- This option is used to delete all the available constraints and resets it to afresh.
• Load/Save- If any of the constraints is used frequently so instead of writing it again and again you can load or save it for future use. Thus, it benefits in saving your time.

• Select a Solving Method: This parameter is used to specify the Excel solver problem type. The user can choose one of the following methods:
• GRG Nonlinear (Generalized Reduced Gradient Nonlinear)- This algorithm is used for functions which consists of at least one of the smooth non-linear constraints.
• LP Simplex (Linear Programming)- This method is used to solve the problems carrying linear relationships.
• Evolutionary- This approach is used for non-smooth difficult problems wherein it is difficult to evaluate the direction in which a function is increasing or decreasing.
• Options: To use your own customized way of solving the problem, the ‘options’ parameter is used. It the help of this, you change the approach of the Solver to find a solution.  
• Solve: Once you have entered all the parameters, click on the solve button. The excel solver will search for all the optional solutions of the specified problem and will display the result.
• Close: This button is used to close the Solver parameters dialog box.

Below are the steps to use Solver –

What if in the previous example where ABC bought a car had in his mind that he will not be paying more than $1,400 installment per month – to achieve this, either he will buy a car which is less costly or he will pay more down-payment or he will increase the term of the loan or he will look for a better deal on the interest rates.

Let’s evaluate the step-step procedure for loading Solver add-in:

1. For the Excel versions 2010 and above, the solver is not directly available. To enable the option:
2. Click on File-> Options.

• The Excel options dialog box will appear. Select the ‘Add-ins’ option. In the bottom, next to the manage bar select Excel Add-in and click on the ‘GO’ option.

• The Add-ins dialog box appears. Tick on the Solver Add-in check box. Click on Ok. This will enable the solver option on your Excel Ribbon toolbar.

• Enter the data in a sheet and go to ‘Data’ tab. Click on ‘Solver’ in the ‘Analyze’ section.

• In ‘Set Objective’ click on the cell which you want to maximize, minimize or set to a particular value
• In ‘By Changing Variable Cells’, you will have to enter all the input cells which you want to be changed – for ex: for a factory which produces 3 different products, we can change the number of units produced for each product to maximize profit
• For ‘Subject to Constraints’, we can add constraints like overall production of the facility is less than a number 450 or each product production needs to be above a specified limit
• Click on ‘Solve’.

• Excel will give you the results but if it won’t be able to meet all the constraints, it will give you a prompt stating the same

On Row 18, Solver has changed the number of units for each product which were earlier 50 (each) on Row 4 to maximize the profit.