Microsoft Excel is a popular data analytics tool that improves the user's productivity while working on data. When it comes to making important decisions such as budgeting, outsourcing, and adding or dropping a line of products, we need to consider the total costs. These decisions require accountants' knowledge to determine what actions need to be taken to maximize the outcome. Thus, helping business analysts solve business problems in the most efficient way. This is where Solver comes into action.
This article gives you a simple introduction to Solver in Excel that enables you to perform linear programming and arrive at the best possible outcomes.
What is Solver in Excel?
Solver in Excel is a tool that helps us solve decision problems by arriving at relevant optimal solutions. This Solver belongs to the set of ‘what-if’ analysis tools, that help us test various scenarios in Excel. They also determine how each scenario affects the outcome on the worksheet.
This topic might be a little challenging for beginners, but the step-by-step tutorial covered in this article will give you an insight into how the Solver tool can be used for decision making. But first, let's look at how to enable Solver in Excel.
How to Enable Solver in Excel?
A solver is an add-in tool available in Excel. Now let’s take a look into how to add this tool.
- If you have Excel versions: - 2010, 2013, 2016, or 2019 you have to first go to the File tab → OPTIONS, as shown.
Note: If you have Excel 2007, you need to click on the Microsoft Office button → Options.
- This will open up an Excel options window. Click on the Add-ins option and then click on Go.
- An Add-in window will open up with a variety of different tools that you can select. Check the Solver Add-in option from the list. Click on OK.
In the 2003 version of Excel, we can add Solver from the Tools menu to find Add-ins. Clicking on it will open up a list of add-ins from which you must select Solver and click on OK.
- You will now find the Solver in the Analyze section of the Data tab.
You can find the newly added Solver in the Tools menu for the 2003 version of Excel.
Moving forward, let’s understand how to use the Solver tool.
How to Use Solver in Excel?
Let’s learn how we can use a solver with the help of an example. The three main steps to be followed are:
- Formulate the Model
- Trial and Error
- Solve the Model
We will be discussing these in detail along with a step-by-step tutorial.
1. Formulate the Model
We will be using the following warehouse dataset to demonstrate Solver in Excel.
This dataset consists of columns having product names, the total number of units, sale price, the cost price of each unit, profit, and the total revenue. Cell B5 contains the total available units, while cell F5 contains the total revenue obtained.
Now, let’s look at the problem statement.
Problem Statement
The maximum number of units that can be ordered is 150. The profit obtained by selling each unit is specified in the table. We need to maximize the total profit obtained by selling the products: TV Set, Stereo, and Speakers.
Goal
To find how many units of each product are required so that the profit is maximum?
2. Trial and Error
With this concept, a user can easily analyze any trial solution.
Suppose we order 50 units of TV sets, 50 units of Stereo, and 50 units of Speakers where the maximum number of units that can be ordered is 150, we will produce total revenue of Rs 1,25,000. However, to maximize the profits, let's move forward and feed the necessary inputs into the solver tool.
3. Solve the Model
To solve this problem, we use Solver in Excel. We will now perform the following steps to find an optimal solution.
- Click on the Solver tool, which is now added to the Analyze section of the Data tab. This will open up the Solver Parameters tab.
- Next, we need to formulate the model. To do so, you need to specify the three main parameters, i.e.,
-
- Objective Cell
- Variable Cells
- Constrains
Excel finds the optimal value (Minimum, Maximum, Specified) for the formula present in the Objective cell. This is done by changing the values in Variable cells, depending on the constraints specified in the Constraint cells.
Now let’s map each parameter to the cells in the dataset by entering the cell values in the ‘Solver Parameters’ dialog box.
1. Objective Cells
The objective cell contains the formula which specifies the objective of the problem statement. Here, the cell F5 is specified as the objective cell. The objective can be to find the minimum, maximum, or exact value.
Here, the aim is to find the maximum value as we are maximizing the profit. So, we set the objective cell's value as cell F5 and select the MAX option.
2. Variable Cells
Variable cells consist of variable data that is modified by the Solver to achieve the objective.
These are the cells that contain variable data. The value of these cells must be changed to optimize profits. In this example, we specify the cells with the Number of units/Product as Variable cells, which are in the range B2: B4.
3. Constraints
Constraints are the restrictions that are given to the Solver. These restrictions are analogous to the real-world limitations on total product capacity, market demands, and so on. The solution provided by the Solver has to satisfy these constraints. To arrive at the optimal solution, these conditions must be met.
In this example, we can specify the following constraints:
- The number of units for each product must be greater than or equal to zero. Hence, we can set the constraint as $B$2 >= 0, $B$3 >= 0, and $B$4 >=0.
- The number of units to be sold cannot exceed the total units. So, $B$5 <=150.
To add these constraints, click on the Add option.
The ‘Add Constraint’ dialog box opens next. Now, enter the cell reference. Specify the required value of the constraint and click on Add.
After adding all the constraints, close the Add Constraint dialog box.
Let’s move forward and understand the options available under Solver in Excel that changes how the Solver finds solutions.
Solver Options
For most of the problems, it is not necessary to change the default Solver options. However, if you want to change a few options, follow the steps below:
- On the Solver parameters dialog box, click on Options.
- A dialog box called Solver Options appears.
- On the All Methods tab, choose one or more options:
1. Constraint Precision
Enter the degree of precision needed in the Constraint Precision Box. This specifies the precision of constraints. You may specify a lower value (0 to 1) to reduce the time it takes the Solver to return a solution.
2. Use Automatic Scaling
Selecting this checkbox automatically scales the results while solving the problem.
3. Show Iteration Results
Selecting this checkbox shows the results for the iterations used while solving the problem.
4. Ignore Integer Constraints
Selecting this checkbox will ignore all the constraints specified for integers.
5. Integer Optimality (%)
This specifies the percentage of integer optimality criteria used by the solver to solve a problem.
6. Max Time (seconds)
This specifies the maximum number of seconds that the Solver will require to find the solution.
7. Iterations
This specifies the number of times that the Solver will recalculate the problem when finding the solution.
8. Max Subproblems
This specifies the maximum number of sub-problems allowed for the Solver.
9. Max Feasible Solutions
This specifies the maximum number of feasible solutions that are needed.
Click on OK after changing the required options.
Finally, click on the Solve option on the Solver Parameters dialog box to find the optimal solution.
Excel will find a solution that maximizes profits. A dialog box called Solver Results will open that says, ‘Solver found a solution’. You can select whichever reports are required to produce the Solver report. Then, choose the option called ‘Keep Solver Results’ and click on OK.
The optimized solution will be displayed on your worksheet. Our Goal to optimize the solution that has been achieved and the Solver arrived at the solution below.
This optimal solution would be to order 100 units of TV Sets and 50 units of Stereo. This solution gives a maximum profit of Rs 1,50,000.
The following report will be generated. This report mentions the original and final values of the Objective Cell and Variable cells and the status of each constraint in the optimal solution.
This is the basics to understand how to use Solver in Excel, to arrive at optimal solutions for your decision problems.
Conclusion
In this article, you learned how to use the Solver in Excel to solve a decision problem by specifying the Objective cells, Variable cells, and Constraints with an example.
If you have any questions about this article, please mention it in the comments section, and our experts will get back to you at the earliest.
You can further enhance your Excel skills by enrolling in the Business Analytics for Strategic Decision Making with IIT Roorkee offered by Simplilearn.