How to Perform What-If Analysis Using Microsoft Excel Solver Add-In?

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, something that could take considerable time and painstaking effort, to something solved within a few clicks.

If working with a large data set, some also consider adding the Excel plugin for conjoint analysis to cross-reference data to further optimize their workflow. Today, we are going to focus on the Excel solver add-in though.

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. 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, etc. 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 Microsoft 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 Microsoft Excel Solver Add-In to perform a variable analysis and find the optimal solution.

Moreover, 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, by the end of this article, you will have 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 will have this tool.  

How to Add Solver Add-in in Excel?

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.

Also Read: How to download, install and remove add-ins in Microsoft Excel

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: 

Solver Add-In (Product Mix) Example:

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 supply of raw material and labor hours and it has to decide which product line (Men’s, Women’s Children’s clothing) will generate maximum profit.

In other words, given in view the constraints, how much quantity of each product line it should produce to maximize profit (Objective Function).

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 let’s introduce couple of more constraints:

• 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:

1. In the menu bar click “Data” menu.
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.

Also Read: How to make a calendar in Google Sheets

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 mistakes in this article.

I’d love to correct myself.