Solver in Excel
What is Solver?
Solver is an add-in programming tool supported by MS Excel. It is an optimization tool that uses operational research techniques to determine the optimal solutions and fetch the desired outcomes by altering the assumptions for objective problems. It is a type of the 'What-if-analysis' that is useful when the user wants to find out the "best" outcome for a given set of two or more assumptions.
The Excel Solver is essentially used for simulation and optimization of several business and engineering prototypes. It is also useful for solving linear programming models (linear optimization problems), and because of this, it is also known as a linear programming solver. Other than that, Solver is also popular for solving smooth and non-smooth linear problems.
Indeed, Microsoft Solver can't solve all possible problems, but it is crucially effective when the user deals with all types of optimization problems to make the best decision. For example, you can use MS Solver to maximize the ROD (return of investment), pick an optimal budget for your campaigns, calculate the profit generated by the salesperson and based on that figure out the number of the salesperson to hire, compose an optimal work schedule for your employees, etc.
Add Solver to your Excel worksheet
The Solver add-in is added with all versions of Microsoft Excel though it is not enabled by default. You need to manually add Solver to your Excel worksheet. To incorporate a Solver into your Excel worksheet, follow the below-given steps.
Where is Solver present in Excel?
The Solver add-ins programming tool is located on the Data tab, in the Analysis group.
Steps to use Solver in Excel
Before moving with the steps of the Excel Solver add-in, let's analyze the problem we will solve using Solver operational research techniques in our Excel worksheet. Below is an easy optimization problem.
Problem: Let's suppose you are the owner of a GYM and are planning to expand the services and exercises to attract more customers. For this, you need to purchase various new machines that cost $90,000 and you have to return the amount in installments in a span of 12 months.
Goal: Find out the minimal cost per customer to let you pay the budget for the new machines within the defined time frame.
To simplify the above problem, we have created a model in an Excel worksheet:
Now, let's solve the above problem in a step-by-step orientation:
Step 1: Run Excel Solver
Click on the Data tab located in the Analysis group. From the given options, click on the Solver button.
Step 2: Define the problem
It will open the Solver Parameters window. We need to set up the 3 main Solver components which are as follows:
Let's make it easy for you by explaining how Excel Solver solves any problem using the above parameters? It determines the optimal value (maximum, minimum, or any given value) for the formula we define in the Objective cell by adjusting the values we specify in the Variable cells and obeying the Constraints cells' limitations.
Let's briefly understand the 3 solver components:
The Objective cell, aka Target cell(named in Excel's earlier versions), contains a formula that describes the objective or goal for a given problem where the objective can be maximized, minimized, or achieved based on some target value.
Let's understand this using an example,
In our case, B10 is our objective cell, which will help us calculate the payment term, where the outcome of the formula, "=B6 / (B7 * B8)"should be equal to 12.
NOTE: Make sure that the objective cell is a formula, else it will throw an error at the time you will solve the problem.
Variable cells, aka Changing cells or Adjustable cells (named in Excel's earlier versions), hold variable data that can be altered to obtain the optimal objective. Excel Solver supports up to 200 variable cells.
Let's find the variable cells for this example. Below given are the cells whose values can be altered:
NOTE: Though in our case the variable cells were adjacent, in case the cells are non-adjacent, select the first variable cell, and then from your keyboard press the Ctrl key and select the non-adjacent variable cell(s). Or another method is to manually enter the cell references, separating them with commas.
The Constraints represent the restrictions or limitations of the potential solutions to the problem. In simple words, we can conclude that constraints are the conditions in Excel Solver that must be met.
The Solver tool enables the user to define the constraint by establishing a relationship between the referenced cell and the constraint. You can choose any of the given options to specify the constraint relationships.
In our case, we have entered two constraints which are as follows:
Step 3: Solve the problem
Now, as we have filled all the parameters, it's time to solve the problem.
In our case, you will notice the value of cell B8 will change to $ 150. This cost signifies the minimal costs per client that will enable you to pay for the expense of new machines in 12 months, given if at least 50 clients per month.
Save and Load Solver problems in Excel
Once solving a particular problem, you may want to save the values for your Variable cell values so you can observe or work on them later.
For example, we have calculated the cost per service for only 50 clients in the above model. But you may also want to try different numbers of project clients per month, calculate the minimal service cost, and evaluate the various service costs.
Therefore, Saving and Loading an Excel Solver scenario is important. Saving solver data signifies selecting a range of cells to save the data in, whereas Loading a Solver problem is giving Excel the range of cells where your problem is saved.
Saving the model
Follow the below given steps to save an Excel solver scenario:
Loading the saved model
Follow the below-given steps to load or restore a saved Excel solver scenario:
Next TopicExcel AVERAGE() Function