In this post, you’ll learn about Solver Add-in in Excel which is used find optimal solutions for decision making problems.
How to Load the Solver Add-in in Excel?
To load the Solver Add-in in Excel, follow the below steps.
- Go to File tab – > Options. When Excel Options dialog box opens up, Go to Add-ins – > Solver Add-in<Go.
- Add-ins dialog box opens up click on Solver Add-in check box and then on OK.
- After this, you can find the Solver tool in the Data tab under the Analyze group.
How to Formulate the Model for Solver in Excel?
- Firstly Identify the problem.
- Then identify the constraints, the objective and the restrictions.
- And finally figure out a mathematical formulation for the problem.
- For instance, let’s say we have a problem to minimize the cost of transportation from two places to four destinations. let’s say there are two companies.
- The formulation of the constraints, the demand shouldn’t exceed the available quantity.
Formulation of optimal solution, the objective is to minimize the total cost of transportation.
Firstly we need to figure out the quantity that is to be transported from the two companies and the total of the quantity delivered to the four destinations.
Solving the Model using Solver in Excel
To find the optimal solution and solve the model using Solver in Excel, follow the below steps.
- After figuring out the constraints and the objective function. Go to Data tab – > Analyze – > Solver.
- Solver Parameters dialog box opens up.
- Under the Set Objective field, set it according to the problem and the optimal solution expected from the objective function.
- In the To category select the options according to the objective of the problem.
Similarly select for By Changing Variable Cells.
- In the Subject to the Constraints field enter the constraints that you’ve formed. And also make sure to select the solving method you want.
Finally make sure you’ve made all the changes you want.
Then click on Solve.
- Click on OK, after selecting Answer under the Reports Category in the Solver Results dialog box. This will show you the result.
