Using Solver

For example, let's analyze the sales of three products. Column B consists of the number of units of each product, column C shows the profit per unit for each product, and column D contains formulas that calculate the profit for each product:

To maximize total profit, the company has some constraints:

• The combined production capacity is 200 total 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 anticipated order.
• Because the market for Product C is relatively limited, the company doesn't want to produce more than 40 units of this product.

To solve this task, do the following:

   1.    Set up the worksheet with values and formulas. Make sure that you format cells logically; for example, if you cannot produce partial units of your products, format those cells to contain numbers with no decimal values.

   2.    On the Data tab, in the Analysis group, click Solver... (Solver is an add-in if you can't find it, see How to manage Add-Ins):

   3.    In the Solver Parameters dialog box:

• Specify the target cell in the Set Objective field. In this example, the target cell is D6 - the cell that calculates the total profit for three products.
• Because the objective is to maximize this cell, click the Max option.
• Specify changing cells in the By Changing Variable Cells box. In this example, changing cells are in the range B3:B5.
• Specify the constraints which can be added one at a time and appear in the box labeled Subject to the Constraints:

  To add a constraint, click the Add button. Excel displays the Add Constraint dialog box:

  

  This dialog box has three parts: a Cell Reference, an operator, and a Constraint value.

  To set the first constraint (that the total production capacity is 200 units), enter B6 as the Cell Reference, choose equal (=) from the drop-down list of operators, and enter 200 as the Constraint value.

  Add other constraints:

  

   4.    Change the Solver options, if necessary.

   5.    Click the Solve button to start the solution process. Excel soon announces that it has found a solution and open the Solver Results dialog box:

• Replace the original changing cell values with the values that Solver found.
• Restore the original changing cell values.
• Create any or all three reports that describe what Solver did (press Shift to select multiple reports from this list). Excel creates each report on a new worksheet with an appropriate name.

  To see reports, check the Outline Reports checkbox.

• Click the Save Scenario button to save the solution as a scenario so that the Scenario Manager can use it.

See also this tip in French: Utiliser le Solveur.