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

- Within an excel worksheet and from the menu bar, go to
**“File”**and then click**“Options”**. - In
**“Excel Options”**window, click**“Add-Ins”**and then select**“Excel Add-ins”**in the**“Manage”**field right at the bottom and click**“Go”**.

- 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. - 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

**Objective Function:**This is the your target that you’re trying to achieve. For e.g. maximize profits or minimize cost etc.**Variable Cell:**Cell which contains variables. Variables is the element of the data which is changed in order to achieve our objective functions.**Constraints:**Constraints are the limiting factors which restrict us to achieve certain objectives.**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:

- The desire to maximize profit is the
**Objective Function** **Raw Material**,**labor hours**and**minimum quantities of Men’s and Women’s clothing line**are the**constraints****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 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:

- In the menu bar click
**“Data”**menu. - In Data tab, click the
**“Solver”**option in the**“Analysis”**section. - In
**“Set Objective”**field, enter the cell number where your objective function is. In our case it is cell:**H16** - 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.** - 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** - 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)**

- 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**. - Click the
**Solve**button to execute Solver Add-in.

- 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.**

- 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.** - 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. - 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.
- If you want to save the scenario click the
**Save Scenario**button. - 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.

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

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.

Hi Lukayl,

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

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.

Pingback: IRR, XIRR and MIRR Formulas in Microsoft Excel 2013 - The Usual Stuff