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.