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 Excel 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 the excel solver 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

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.

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 H16 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(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 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: 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.
    Solver Add in - Add Parameters
  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.
    Excel Solver Add in - After Solution
  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 commenting below if you find any mistake in this article.

I’d love to correct myself.

 

 

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

  • Pingback: How to Download, Install & Remove Excel Add Ins - The Usual Stuff

  • September 20, 2017 at 5:12 pm
    Permalink

    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.

    Reply
    • Saad
      September 24, 2017 at 7:02 pm
      Permalink

      Hi Lukayl,

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

      Reply
    • Saad
      October 21, 2017 at 10:51 am
      Permalink

      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.

      Reply

Leave a Reply

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

Sign up to our newsletter!