Excel solver is a built-in tool by Excel, which is used to solve linear program problems. You can actually use it to solve both linear and non-linear problems.
It’s a handy tool for making decisions regarding optimization problems. Optimization of these decisions will surely help you to maximize your profits.
Let’s take a look at how to use Excel solver for solving linear problems in this Excel solver tutorial:
First, you will have to enable Solver as a feature to Excel as it’s not enabled by default.
Click on File and navigate to the Options tab
In the options window, click on Add-ins
With the ExcelAdd-ins options selected from the dropdown menu, click on Go.
In the new window, select SolverAdd-in and then click OK.
Note: you might receive a message that Excel solver add-ins are not installed on your computer. Click on Install to do so.
After following these steps, Solver should appear under the Data tab in the Analysis group.
Before you can start using Solver, you have to have your formula ready.
In our example, we’re going to look at a carpentry shop that is looking to provide services, but in order to do so, it needs to purchase new equipment that costs $40,000 which is going to be paid in the form installments over 12 months.
The goal is to reduce – as much as possible – the cost per service provided by the carpentry shop during those 12 months.
So we created this using the numbers:
Cost of new equipment $40,000
Estimated No. of clients per month 50
Cost per service ?
No. of months to pay =B3/(B4*B5) = 12
Now, let’s see how we can optimize this problem and find a solution using Solver in Excel.
First, go to the Data tab, and under the Analysis Group click on Solver.
In the new window, you have to define 3 components in order to solve the problem:
What happens is that Excel tries to find the optimal solution for the “objective cell” by changing the data in the variable cell and trying to maximize or minimize the value of the objective cell. It does so by taking into account the constraints or limitations that we have defined in the appropriate cell.
This is the cell that contains the formula which tells us the goal and our objective for the problem. Is our aim to maximize, minimize or reach a specific value?
In the example we mentioned above, this cell is B7, which contains the formula =B3/(B4*B5) that results in number 12 as value.
This cell contains the variable data that will be changed by the Solver to achieve the objective that we explained above.
In the example we first mentioned, we have two variable cells:
Estimated No of clients (cell B4) which should be equal to or less than 50
Cost per service (cell B5) which is the value we want the Solver to calculate
Constraints are the limitations that we can put on the problems. These are the conditions that we specify which have to be met before an optimal solution is achieved.
In order to set a constraint, follow these steps:
Click on the Add button next to the SubjecttoConstraints box
In the new window, enter a constraint
Click on Add
You can keep adding other constraints
Click OK to finish
There are different relationships that you can define between the reference cell and the constraint.
If you want to delete a constraint, follow these steps:
Open the Solver Parameters window and click on the constraint
If you’re going to edit it, click change and then make the changes you need
If you would like to delete it, click on the delete button
Solving the problem
After following all these steps and finishing the required configuration, you can solve the problem. In order to do so, click on the Solve button at the bottom of the Solver Parameters windows, which we used before.
Solving the problem depends on the amount of data and the parameters which you previously configured. Because of the resource usage of the calculations, the solving could take a few seconds, minutes or even hours.
After the calculations and the processing has finished, the Solver will display the Results window. Select “keep Solver solution” and then click OK.
The results will be displayed in the worksheet.
In our example, in cell B5, we will see 66.67 dollars, which should be the minimum cost per service you will charge in order to pay for the equipment in the 12 months set beforehand.
Now let’s take a look at some Excel solver examples.
We are going to look at a simple transportation problem that is solved by a linear programming equation. This is a simplified version of a very complex equation that is solved every day by major companies around the world.
This is the problem: we want to minimize the cost of shipment products from two warehouses to four customers. The warehouses have limited supply, and the customers have limited demand.
Our goal is to limit shipment costs without going over our demand and supply limits (constraints).
Here is what our data looks like:
Setting up the model
In order to create the model, we have to answer these questions:
What decision do we have to make to reach the optimal quantities delivered from each warehouse to each customer?
What are our constraints or limitations? The supply and demand numbers are the constraints.
What is the goal? Our goal is to reduce the cost of shipping and get to the minimum point.
The next step is to calculate the total number of shipped products from the warehouse and the total number of products received by the customer. A SUM formula can do this.
In order to better understand the example, we’re going to name the ranges as so:
The last step is to do these configurations in the Solver program as before.
The objective is the Shipping cost which should be set to minimum
The variable cells the Products shipped
And the Constraints are according to the table above.
In the end, click the solve button like the example we followed before. The solution will be calculated and entered into the worksheet.
By this Excel solver tutorial, we would use the Solver to make better decisions regarding optimization problems.
Our experts will be glad to help you, If this article didn't answer your questions.
We believe this content can enhance our services. Yet, it's awaiting comprehensive review. Your suggestions for improvement are invaluable. Kindly report any issue or suggestion using the "Report an issue" button below. We value your input.
Contact us today at – and speak with our specialist.
BSUPERIOR SYSTEM LTD. provides custom software and technology solutions to small and medium sized businesses. We have been helping businesses to operate more efficiently, reduce money and time spend on redundant repetitive daily tasks.