Using Solver

For example, let's analyze sales of three products. Column B consist 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:

In order to maximize total profit, 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.    Select Tools -> Solver... (Solver is an add-in, if you can't found it, see How to manage Add-Ins) to open the Solver Parameters dialog box:

   3.    In the Solver Parameters dialog box:

• Specify the target cell in the Set Target Cell 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 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 another 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.
• Click the Save Scenario button to save the solution as a scenario, so that the Scenario Manager can use it.