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 us 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.Â
The topics covered in this article are as follows:Â
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.
A solver is an add-in tool available in Excel. Now letâs take a look into how to add this tool.
Note: If you have Excel 2007, you need to click on the Microsoft Office button âÂ Options.
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 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.Â
Letâs learn how we can use a solver with the help of an example. The three main steps to be followed are:Â
We will be discussing these in detail along with a step-by-step tutorial.
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.
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.
To find how many units of each product are required so that the profit is maximum?
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.
To solve this problem, we use Solver in Excel. We will now perform the following steps to find an optimal solution.
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.
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.Â
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.
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:
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.
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:Â
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.
Selecting this checkbox automatically scales the results while solving the problem.
Selecting this checkbox shows the results for the iterations used while solving the problem.
Selecting this checkbox will ignore all the constraints specified for integers.Â
This specifies the percentage of integer optimality criteria used by the solver to solve a problem.
This specifies the maximum number of seconds that the Solver will require to find the solution.
This specifies the number of times that the Solver will recalculate the problem when finding the solution.
This specifies the maximum number of sub-problems allowed for the Solver.
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.Â
Gain expertise in the latest Business analytics tools and techniques with theÂ Business Analyst Master's Program. Enroll now!
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 Certification Course with Excel offered by Simplilearn.Â
Simplilearn is one of the worldâs leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.
Business Analytics with Excel
*Lifetime access to high-quality, self-paced e-learning content.
Explore CategoryBusiness Analytics Basics: A Beginnerâs Guide
Whatâs the Difference Between Data Analytics and Business Analytics
The Top 10 Skills You Need to Have to Be a Business Analyst
Business Analytics in 2021: A Comprehensive Trends Report
Microsoft Excel Basics: The Ribbon
The Best Guide On How To Become A Business Analyst