Data Analysis [Part 5 of 5] – Using Solver in Excel

This is the fifth and final article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use Solver in Excel.

Download Example File

Watch Video – Using Solver in Excel

Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek.

How to Find Solver Addin in Excel

Solver add-in is disabled in Excel by default. Here are the steps to enable it:

Here are the steps to enable it:

• Go to File –> Options.
• In the Excel Options Dialogue Box, select Add-in the left pane.
• In the right pane, at the bottom, select Excel Add-ins from the drop down and click on Go..
• In the Add-ins dialogue box, you will see a list of available Add-ins. Select Solver Add-in and click OK.
• This will enable the Solver Add-in. It will now be available in the Data Tab under Analysis group.

Using Solver in Excel – Example

Solver gives you the desired result when you mention the dependent variables and the conditions/constraints.

For example, suppose I have a data set as shown below.

This example has manufacturing data for 3 widgets – Quantity, Price per Widget, and Overall Profit.

Objective: To get the maximum profit.

If you have an idea about manufacturing, you’d know you need to optimize the production to get the best output. While in theory you can manufacture unlimited quantities of the highest profit widget, there are always a lot of constraints under which you need to optimize the production.

Constraints: 

Here are a couple of constraints that you need to consider while trying to maximize the profit.

• At least 100 Quantity of Widget A should be made.
• At least 20 Quantity of Widget B should be made.
• At least 50 Quantity of Widget C should be made.
• A total of 350 widgets should be made.

This is a typical manufacturing optimization issue and you can be easily answer it using Solver in Excel.

Steps to Use Solver in Excel

• Once you have the solver add-in activated (as explained above in this article), Go to Data –> Analysis –> Solver.
• In the Solver Parameter dialogue box, use the following:
  1. Set Objective: $D$5 (this is the cell which has the desired value – in this case, it is overall profit).
  2. To: Max (since we want the maximum profit).
  3. By Changing Variable Cells: $B$2:$B$4 (variables that we want to optimize – in this case, it’s the quantity).
  4. Subject to the Constraints:
     • Here you need to specify the constraints. To add a constraint, click on Add. In the Add Constraint dialogue box, specify the Cell Reference, the condition and the Constraint Value (as shown below):
     • Repeat this process for all the constraints.
  5. Select a Solving Method: Select Simplex LP.
  6. Click Solve
     • In case solver finds a solution, this will open the Solver Result dialogue box. You can choose to keep solver solution (which you can see in your data set), or choose to revert back to the original values.
       • You can also Save this as one of the scenarios, that can be used in the Scenario Manager.
       • Along with this, you can also choose to create reports: Answer, Sensitivity, and Limits. Just select it and click OK. This will create different tabs with details one each for Answer, Sensitivity, and Limits (if you select only one or two, then that many tabs are created).

With this article, I have tried to introduce you to Solver. There is much more that can be done, and if you are into statistics, I would recommend you go and read more about it. Here are a couple of good articles that I could find online:

• Using Solver in Excel – MS Help.
• A handbook on using Solver in Excel (with examples)).

Try it Yourself.. Download the File

You May Also Like the Following Excel Tutorials:

• Calculating Standard Deviation in Excel.
• Making Histogram in Excel.
• Calculating Weighted Average in Excel.
• Calculating CAGR in Excel.
• How to Get Descriptive Statistics in Excel?
• How to Remove (Disable) Add-Ins in Excel