Microsoft Excel Solver Add-In for What-If Analysis

What is Microsoft Excel Solver Add-In?

Microsoft Excel Solver add-in is a great tool to perform complicated “What-If” analysis within no time.

It helps in simplifying the whole process of identifying the optimal solution to a problem which would take considerable time and painstaking effort, just within a few clicks.

If you are familiar with applying different values to a particular scenario in order to figure out the optimum or the closest result depicting the actual results, you must appreciate the complexity this whole process brings.  The major reason is that you have to take care and keep track of the numbers of decision variables, identify the constraints, make sure that your resources do not exceed those constraints. Once everything is set, use the trial and error method to identify an optimal solution.

Change just one variable and your whole model starts showing a totally different picture.

In Excel Solver Add-In, all you have to do is define your data, set objective functions, identify constraints in the scenario manager and then let excel take care of the rest. That’s it.

In this article, I will discuss in detail how you can use the Excel Solver Add-In to perform a variable analysis and find the optimal solution. Besides, we will also discuss how you can add solver add-in in excel, which excel versions provide this add-in, how to define your parameters e.t.c. In short, I will mention all the necessary details to get you up and running with the excel solver add-in. Which Versions of Excel Provide the Solver Add-In?

The Solver Add-in is available since the 97-2003 versions of Microsoft Excel. So any excel’s version from 97 and beyond (including Excel 2016) will have this tool.

Since I use Excel 2013, I will tell you how to get solver on excel 2013. However, the process of adding it is more or less the same in other versions of excel as well.

Excel already has Solver Add-in within itself. You just have to activate it. So in order to activate Solver in Excel 2013, follow the below steps:

1. Within an excel worksheet and from the menu bar, go to “File” and then click “Options”. 2. In “Excel Options” window, click “Add-Ins” and then select “Excel Add-ins” in the “Manage” field right at the bottom and click “Go”. 3.  A small pop up window will show the Add-ins available in your version of excel. Search for the solver add-in and checkmark the option to activate it. Click “Ok” button to confirm your action. 4. To confirm that the Solver Add-in active click “Data” in the menu bar. In the “Analysis” group, you will see a new option of “Solver” which means that the add-in is now active. Once the Solver add-in is up and live, let’s now first get acquainted with the terminologies that will come in handy to use this add-in effectively.

Must-Know Terminologies to Use the Excel Solver Add-in

1. Scenario Manager: is a window where you input all your decision variables.
2. Objective Function: This is your target that you’re trying to achieve. For e.g. maximize profits or minimize cost etc.
3. Variable Cell: Cell which contains variables. A Variable is the element of the data which is changed in order to achieve our objective functions.
4. Constraints: Constraints are the limiting factors which restrict us to achieve certain objectives.
5. Solving Method: MS-Excel provides three ways to solve a particular problem. Which method to choose depends on the nature of the problem that we are facing. For e.g.
• Simplex LP method is used for linear problems
• GRG Non-Linear problem is used for non-linear problems
• Evolutionary problem is used for non-smooth problems.

Note: What all these solving methods mean is currently out of the scope of this article and I will discuss them later in some other article.

Now, In order to understand how solver add-in works, let’s solve a product-mix example with two constraints:

A garments factory (which produces a clothing line for men, women, and children) wants to maximize its profits. The problem it faces is that it has a limited quantity of raw material and labor hours. Now, it has to decide which product line (Men’s, Women’s Children’s clothing) will generate maximum profit.  In other words, how much quantity of each product line it should produce so that the profit it earns is maximum.

Numerical details of the above problem is:

• Maximum quantity of Raw Material (RM) is 5,000 units
• Maximum Labor Hours (LH) available are 10,000 hours
• Cost of one unit of RM is \$3.00
• Cost of one LH is \$4.00
• Men’s clothing consume 3 units of RM and 6 LH
• Women’s clothing consumes 3.5 units of RM and 7 units of LH
• Children’s clothing consumes 1 unit of RM and 4 units of LH
• Selling Price of Men’s clothing is \$45.00
• Selling Price of Women’s clothing is \$48.00
• Selling Price of Children’s clothing is \$40.00

Just to make our problem more interesting and add couple of more constraints, let’s introduce couple of more constraints. These are:

• Minimum quantity of Men’s clothing should be 50 units.
• Minimum quantity of Women’s clothing should be 100 units.

Over here:

1. The desire to maximize profit is the Objective Function
2. Raw Material, labor hours and minimum quantities of Men’s and Women’s clothing line are the constraints
3. Current Quantities being produced of different clothing line are our variables.

Once we’ve understood the problem, set our objectives and defined our constraints, let’s put some numbers to the above example and solve it in Excel. As you can see in the image, we have already entered the numbers for the starting point so that Solver Add-in has something to work on. In excel:

• Cell H16 is our Objective Function as we want to maximize this number. One important point to remember is that our objective function cell should always be a formula. In our case, the formula is: SUM(B16:D16)
• Cells B2 to D2 are our Variables. The solver add-in will change the numbers in this cell to achieve our objective in cell H16.
• Cells H5 to H8 are our Constraints. The RM and LH cannot exceed more than 5,000 and 10,000 respectively. Similarly, we need to produce at least 50 units of Men’s clothing and 100 units of Women’s clothing.

Now, let’s make the solver add-in to work its magic:

2. In the Data tab, click the “Solver” option in the “Analysis” section.
3. In “Set Objective” field, enter the cell number where your objective function is. In our case, it is cell: H16
4. In To field, select if you want to “Maximize”, “Minimize” or set the Objective Function to a specific value by selecting Max, Min or Value Of respectively. Since we want to maximize, we will select Max.
5. Identify variables by mentioning the cell ranges which contain your variables in By Changing Variable Cells field. In our case, these are cells B2 to D2
6. Now, set the constraints in the Subject to the Constraints field. In our case the constraints are:
• 50 or more units of Men’s clothing (\$B\$2 >= \$H\$7)
• 100 or more units of Women’s clothing (\$C\$2 >= \$H\$8)
• Zero or more units of Children’s clothing (\$D\$2 >= 0)
• Total RM used should not exceed more than 5,000 units (\$B\$8:\$D\$8 < \$H\$5)
• Total LH worked should not exceed more than 10,000 hours (\$B\$9:\$D\$9 < \$H\$6)
7. Once the constraints are set, select the method through which you want to solve this problem in Select a Solving Method field. In our case, we will select Simplex LP.
8. Click the Solve button to execute Solver Add-in. 9. As soon as the Solve button is clicked, the values in the excel spreadsheet will be changed according to what the Solver Add-in deems the optimal solution is. In our case, the Objective Function cell now displays \$48,800 as the Total Profit. This means that the maximum profit that we can achieve by adjusting the quantities of different clothing lines while staying within our constraints limit is \$48,800. 10. That’s not it. Solver has also modified the values of our variables. Now, in order to achieve maximum profit we need to produce 50 units of Men’s clothing, 100 units of Women’s clothing and 2,250 units of Children’s clothing.
11. In the Solver Results window, you can either keep the modified results generated by Solver or revert to the original values by selecting either Keep Solver Solution or Restore Original Values option.
12. Moreover, you can also generate the reports by selecting the type of reports in the Reports pane of Solver Results window. These reports will be generated in new tabs.
13. If you want to save the scenario click the Save Scenario button.
14. Click the OK button to close the Solver Result window.

Final Thoughts…!

So that’s how the Solver Add-in works. However, don’t restrict yourselves to just the above scenario. Think of new scenarios for yourself and implement the scenario on excel solver add-in and see if that works.

Why just stick to the profit maximization scenario? Go ahead and test scenarios based on Cost Minimization. Similarly, you can also see how the add-in works if you want a specific value as your objective function.

Do let me know if this tutorial helped you in wrapping your head around the Microsoft Excel Solver Add-in. Don’t hesitate to comment below if you find any mistake in this article.

I’d love to correct myself.

7 thoughts on “Microsoft Excel Solver Add-In for What-If Analysis”

• September 20, 2017 at 5:12 pm

Hi,
I think you didn’t set a crucial step when setting up this simplex solver: The total number of raw materials and the total number of labour hour used weren’t added up to meet the constraints.
What you did was actually calculates individual category clothing under the constraints, not the aggregation one. Because your profit is added up of the three categories, and yet if one adds up the total used materials (B8:D8) and labour hours (B9:D9), one would find out that the numbers would have been 12500 (5000+5000+2500) and 30000 (10000+10000+10000) respectively, which obviously exceed the constraint numbers.
I have calculated the aggregation scenario and the optimal result is to product the Child clothing exclusively as it is the most profitable, with the number being 2500 units. The bottleneck for this example is actually the labour hour, as the raw material is only consumed half, when the hours are used up.

• September 24, 2017 at 7:02 pm

Hi Lukayl,

Thanks for your feedback. I’ll check your input and will update if needed.

• October 21, 2017 at 10:51 am

Hi Lukayl,

I’ve modified the article after reviewing your input. I believe, now it represents Excel Solver Add In in the best possible manner.
I value and appreciate your input in this regard.
Thanks for contributing.

• August 14, 2018 at 8:33 pm

Hi,

If I need to convert the existing solver calculation with any other tool, then I should have understanding of existing calculation.

Regards,
Deepak

• 