How to Use Solver in Excel?
A solver is a mathematical tool present in MS-Excel that is used to perform calculations by working under some constraints/conditions and then calculates the solution for the problem. It works on the objective cell by changing the variable cells by using sum constraints.
Solver is present in MS- Excel but for using it we need to activate it. For activating the solver tool we need to do the following steps:
Step 1: Go to File and select options. The following dialog box will appear.
Step 2: Now select the Add-ins option and click on Go and finally click on OK.
Step 3: After clicking OK, Select Solver Add-in and press OK. Now solver will be activated in Excel.
Step 4: Now solver will appear in data section like this.
Now let’s understand how to use solver with the help of an example.
Excel Solver Example (linear programming problem)
This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.
Problem: You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand.
Goal: Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meet the demand of each customer.
Source data: Here is what our transportation optimization problem looks like:
Recommended by LinkedIn
Formulating the model
To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:
1. What decisions are to be made? We want to calculate the optimal quantity of goods to deliver to each customer from each warehouse. These are Variable cells (B7:E8).
2. What are the constraints? The supplies available at each warehouse (I7:I8) cannot be exceeded, and the quantity ordered by each customer (B10:E10) should be delivered. These are Constrained cells.
3. What is the goal? The minimal total cost of shipping. And this is our Objective cell (C12).
The next thing for you to do is to calculate the total quantity shipped from each warehouse (G7:G8), and the total goods received by each customer (B9:E9). You can do this with simple Sum formulas demonstrated in the below screenshot. Also, insert the SUMPRODUCT formula in C12 to calculate the total cost of shipping:
To make our transportation optimization model easier to understand, create the following named ranges:
The last thing left for you to do is configure the Excel Solver parameters:
Please pay attention that we've chosen the Simplex LP solving method in this example because we are dealing with the linear programming problem. If you are not sure what kind of problem yours is, you can leave the default GRG Nonlinear solving method.
Solution
Click the Solve button at the bottom of the Solver Parameters window, and you will get your answer. In this example, the Excel Solver add-in calculated the optimal quantity of goods to deliver to each customer from each warehouse with the minimal total cost of shipping:
