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 simplifying the whole process of identifying the optimal solution of a problem which would take considerable time and painstaking effort, just within few clicks.

If you are familiar with What-If analysis you must appreciate the complexity this process brings.  The major reason is that you have to take care and keep track of numbers of 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 totally different picture.

In the Solver Add-In, all you have to do is define your data, set objective functions, identify constraints 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 What-If 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 this add-in.

Which Versions of Excel Provide 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 you can install the add-in in excel 2013. However, the process of adding it is more or less 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”.Excel Solver Add in - File 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”.
    Excel Solver Add in - File Options manage add in
  3.  A small pop up window will show the Add-ins available in your version of excel. Search for the solver add-in and check mark the option to activate it. Click “Ok” button to confirm your action.Excel Solver Add in - Manage Add ins
  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.
    Excel Solver Add-in Confirmation

 

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. Objective Function: This is the your target that you’re trying to achieve. For e.g. maximize profits or minimize cost etc.
  2. Variable Cell: Cell which contains variables. Variables is the element of the data which is changed in order to achieve our objective functions.
  3. Constraints: Constraints are the limiting factors which restrict us to achieve certain objectives.
  4. 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 method mean is currently out of the scope of this article and I will discuss about 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 clothing line for men, women and children) wants to maximize its profits. The problem it faces is that it has 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

Over here:

  1. The desire to maximize profit is the Objective Function
  2. Raw Material and labor hours are the constraints
  3. Quantity of different clothing line is our variable.

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.

Excel Solver Add in Product Mix Example

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 G15 is our Objective Function as we want to maximize this number. One important point to remember is that the our objective function cell should always be a formula. In our case, the formula is: SUM(B15:D15)
  • Cells B2 to D2 are our Variables. The solver add-in will change the numbers in this cell to achieve our objective in cell G12.
  • Cells G5 and G6 are our Constraints. The RM and LH cannot exceed more than 5,000 and 10,000 respectively.

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

  1. In the menu bar click “Data” menu.
  2. In 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: G15
  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 range 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:
    • Zero or more quantities of Men’s clothing ($B$2 >= 0)
    • Zero or more quantities of Women’s clothing ($C$2 >= 0)
    • Zero or more quantities of Children’s clothing ($D$2 > 0)
    • Total RM used should not exceed more than 5,000 units ($B$8:$D$8 < $G$5)
    • Total LH worked should not exceed more than 10,000 hours ($B$9:$D$9 < $G$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.
    Solver Add in - Before Solve
  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 to be the optimal solution. In our case the Objective Function cell now displays $86,071.43 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 $86,071.43
    excel solver add in after solve
  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 1,667 units of Men’s clothing, 1,429 units of Women’s clothing and 2,500 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 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.

Additionally, do let me know if this tutorial helped you in wrapping your head around the Microsoft Excel’s Solver Add-in. Don’t hesitate commenting below if you find any mistake in this article.

I’d love to correct myself.

 

 

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign up to our newsletter!