In this article, we’re going to use a simple example to introduce you to **Solver** technique and then in next article, we’ll present some complex examples to explain what this feature can do for you.

**Read More:** Excel Solver – A Step by Step Complete How-to-use Guide.

The following figure shows a worksheet. It is a set up to calculate the profit for three products.

Table of Contents

## Our Hypothetical Problem

Column B displays the number of units of each product. Profit per unit for each product is shown in Column C, and Column D contains formulas. These formulas calculate the total profit for each product by multiplying the units by the profit per unit.

You don’t need to be a business graduate to realize that the highest profit comes from Product C. Therefore, to maximize total profit from production, the production management can produce only Product C. But if things were so straightforward, you wouldn’t need tools like **Solver**. This company has some constraints that must be met to produce products:

- The combined production capacity is 300 units per day.
- The company needs 50 units of Product A to fill an existing order.
- The company needs 40 units of Product B to fill an expected order.
- The market for Product C is relatively limited. So the company is not interested in producing more than 40 units of this product per day.

**Read More: Resource Allocation Model in Excel Using Solver**

These four constraints make it a real problem and a bit more challenging. In fact, it’s a perfect problem for using **Solver** technique.

## The procedure of using Excel Solver

Before going more detail, here’s the basic procedure for using **Solver**:

- First of all, set up the worksheet with values and formulas. Make sure that you have formatted cells correctly; for example, maximum time you can’t produce partial units of your products, so format those cells to contain numbers with no decimal values.
- Choose
**Data ➪ Analysis ➪ Solver**. The**Solver Parameters**dialog box will appear. - Specify the target cell. Target cell also is known as objective.
- Specify the range that contains the changing cells.
- Specify the constraints.
- If necessary, change the
**Solver**options. - Let
**Solver**solve the problem.

## Tackling this example

To solve this example using Solver, choose **Data ➪ Analysis ➪ Solver**. The **Solver Parameters** dialog box will appear. Set up this dialog box in the following steps to solve our problem.

**Note:**Click this link if Excel doesn’t show the Solver command.

In this example, the target cell is D6. D6 cell calculates the total profit for three products.

- Enter D6 in the
**Set Objective**field of the**Solver Parameters**dialog box. - We’re going to maximize the value of cell D6, so select the
**Max**option button. - Specify the changing cells in the
**By Changing Variable Cells**field. Our changing cells are B3: B5. - In this step, we’ll specify the constraints on the problem. The constraints are added one by one and appear in the
**Subject to the Constraints**list. To add a constraint, click the**Add**button. The**Add Constraint**dialog box appears as shown in the following figure. This dialog box has three parts: a Cell Reference, an operator, and a Constraint value. - To set the first constraint (our first constraint is: the total production capacity is 300 units), enter B6 as the Cell Reference, choose equal (=) from the drop-down list of operators, and enter 300 as the Constraint value.

- Enter D6 in the

- Click
**Add**, and enter the remaining constraints. The following table summarizes the constraints for this problem.

- Click

**Read More: Minimizing shipping costs in Excel using Solver**

### TABLE: Constraints Summary

Constraint | Expressed As |
---|---|

Capacity is 300 units | B6=300 |

At least 50 units of Product A | B3>=50 |

At least 40 units of Product B | B4>=40 |

No more than 40 units of Product C | B5<=40 |

- When we’ve finished entering our last constraint, we’ve clicked OK and we’re returned to the
**Solver Parameters**dialog box. This dialog box now lists the four constraints. - Now select Simplex LP solving method from the Select a Solving Method drop-down list.

- When we’ve finished entering our last constraint, we’ve clicked OK and we’re returned to the

- Click the
**Solve**button to start the solution process. Excel will watch you the progress onscreen and soon announces that it has found a solution. The**Solver Results**dialog box is shown in the following figure.

At this point, we have the following options in our hand:

- You can keep the solution that Solver has found.
- You can restore the original changing cell values.
- You can create any or all of the three reports that show what Solver did.
- Click the
**Save Scenario**button to save the solution as a scenario so that**Scenario Manager**can use it.

From the **Reports** section of the **Solver Results** dialog box, you can select any or all of three optional reports to produce reports. After selecting a report (or all), click **OK**. Excel will create each report on a new worksheet, with an appropriate name. The following figure shows an **Answer Report**.

Observe the **Constraints** section of the report. You will find that three of the four constraints are *binding*, which means that these constraints were satisfied at their limit.

With this simple example, we have understood how **Solver** works. Sometimes, you can solve this type of problem manually, but this is not always the case. For a complex problem, you’ve to depend on Solve technique of Excel.

**Caution: **You can’t undo any changes that **Solver** has made to your workbook.

*Read More: What is Solver in Excel*

## More about Excel Solver

We’re going to discuss the **Solver Options** dialog box in this section. Using this dialog box, you can control many aspects of the solution process. You can also load and save model specifications in a worksheet range using this dialog box.

Usually, you’ll want to save a model only when you will use more than one set of Solver parameters with your worksheet. Excel saves the first Solver model automatically with your worksheet using hidden names. If you save additional models, Excel stores the information in the form of formulas that correspond to the specifications. (The last cell in the saved range is an array formula that holds the options settings.)

It may happen that Solver will report it can’t find a solution, even when you know that one solution should exist. You can change one or more of the Solver options and try again. When you click the Options button in the Solver Parameters dialog box, the Solver Options dialog box shown in the following figure appears.

Here is a brief description of Solver’s options:

**Constraint Precision:**Specify how close the Cell Reference and Constraint formulas must be to satisfy a constraint. Specifying less precision will make Excel solve the problem more quickly.**Use Automatic Scaling:**It is used when the problem deals with large differences in magnitude— when you attempt to maximize a percentage, for example, by varying cells that are very large.**Show Iteration Results:**By selecting this checkbox, Solver is instructed to pause and display the results after each iteration.**Ignore Integer Constraints:**If you select this check box, Solver will ignore constraints that mention that a particular cell must be an integer. Using this option may permit Solver to find a solution that can’t be found otherwise.**Max Time:**Mention the maximum amount of time (in seconds) that you want Solver to spend on a single problem. If Solver reports that it exceeded the time limit, you can increase the amount of time that it will spend to search for a solution.**Iterations:**Enter the maximum number of trial solutions that you want Solver to try to solve the problem.**Max Subproblems:**It is used to solve complex problems. Specify the maximum number of sub-problems that may be solved by the Evolutionary algorithm.**Max Feasible Solutions:**It is used for complex problems. Specify the maximum number of feasible solutions that may be solved by the Evolutionary algorithm.

**Note:**The other two tabs in the Options dialog box contain additional options used by the GRG Nonlinear and Evolutionary Algorithms.

Happy Excelling 🙂

## Download Working File

Download the working file from the link below:

Hi

how would i use this to solve the following problem? i have to cut parts that vary in length from 0.9m to 5.6m.

i have a history of about 2000 orders, over 6 months. i want to stock only 2 sheet sizes. i wish to determine the optimum 2 sizes that i should stock based on the historic ordering information given that i can only put parts together on the one sheet if they are ordered within 7 days of each other

Hi

This is very helpful for me. Thanks.