A Product Mix Example

[Dividing Line Image]

This page illustrates the process of creating a Solver model, using a well-worn example based on the Product Mix sheet in the SOLVSAMP.XLS workbook included with Microsoft Excel, and in the SOLVSAMP.123 workbook included with Lotus 1-2-3 97. (The version in the SOLVSAMP workbook is somewhat more complex and requires the nonlinear GRG Solver.) We'll take you through the steps of defining your problem, writing out formulas for the objective function and constraints, creating a worksheet, defining the problem elements through the Solver dialogs, running the Solver to find the optimal solution, and producing an Answer Report.

Setting Up a Model

To set up an optimization model as a spreadsheet, you will follow these essential steps:

Reserve a cell to hold the value of each decision variable.
Pick a cell to represent the objective function, and enter a formula that calculates the objective function value in this cell.
Pick other cells and use them to enter the formulas that calculate the left hand sides of the constraints.
The constraint right hand sides can be entered as numbers in other cells, or entered directly in the Solver's Add Constraint dialog box.

Within this overall structure, you have a great deal of flexibility in how you lay out and format the cells which represent variables and constraints, and which formulas and built-in functions you use. For example, the formulas needed for a linear programming problem can always be specified with the SUMPRODUCT function, or with the DOTPRODUCT function included with each of the enhanced solver products. If the model is easily expressed in vector-matrix form, you may want to use defined names for the vectors and built-in functions such as MMULT (in Microsoft Excel) to compute the constraints.

A Sample Model

Imagine that you are managing a factory which is building three products: TV sets, stereos and speakers. Each product is assembled from parts in inventory, and there are five types of parts: Chassis, picture tubes, speaker cones, power supplies and electronics units. Your goal is to produce the mix of products which will maximize profits, given the inventory of products on hand.

To assemble a TV set, you need 1 chassis, 1 picture tube, 2 speaker cones, 1 power supply and 2 sets of electronics. To make a stereo, you need 1 chassis, 2 speaker cones, 1 power supply and 1 set of electronics. And to build a speaker, all you need is 1 speaker cone and 1 set of electronics.

You can sell TV sets for a gross profit of $75 each, stereos for a profit of $50 each, and speaker cones for $35 each. The inventory you have on hand consists of 450 chassis, 250 picture tubes, 800 speaker cones, 450 power supplies and 600 sets of electronics.

Writing the Formulas

Before looking at a spreadsheet, let's write out formulas corresponding to the verbal description above. If we temporarily use the symbol x1 for the number of TV sets assembled, x2 for the number of stereos, and x3 for the number of speakers, our total profit is:

Maximize 75 x1 + 50 x2 + 35 x3 (Profit)

Building each product requires a certain number of parts of each type. For example, TV sets and stereos each require one chassis, but speakers don't use one. The number of parts used depends on the mix of products built (the left hand side of each constraint), and we have a limited number of parts of each type on hand (the corresponding constraint right hand side):

Subject to:
1 x1 + 1 x2 + 0 x3 <= 450 (Chassis)
1 x1 + 0 x2 + 0 x3 <= 250 (Picture tubes)
2 x1 + 2 x2 + 1 x3 <= 800 (Speaker cones)
1 x1 + 1 x2 + 0 x3 <= 450 (Power supplies)
2 x1 + 1 x2 + 1 x3 <= 600 (Electronics)

Notice that we've expressed each constraint in a uniform way: Where we aren't using any parts of a particular type, we just use a quantity of 0 parts. This will make it easier to enter and copy the constraint formulas in the spreadsheet. We can write the actual spreadsheet formulas for the objective and the constraint left hand sides using the SUMPRODUCT function. This is very readable, and it's also in the form required to take advantage of fast problem setup in Frontline's enhanced solvers.

Since the number of products built cannot be negative, we'll also have constraints x1, x2, x3 >= 0.

The Worksheet (Shown in Excel)

The next step is to create a worksheet where the formulas for the objective function and the constraints are calculated. In the above worksheet, we have reserved cells D9, E9 and F9 to hold our decision variables x1, x2 and x3: The number of TV sets, stereos and speakers to build. Notice that the gross profit for each assembled product ($75, $50 and $35) is shown in cells D17, E17 and F17. This allows us to compute the objective function in cell D18 as:

SUMPRODUCT(D17:F17,D9:F9)

In the table area D11:F15, we've entered the number of parts of each type needed to assemble each finished product. For example, the 2 at D13 means that we need 2 speaker cones to build a TV set. These numbers come directly from the formulas for the constraints shown earlier. With these values in place, we can enter a single formula at cell C11:

SUMPRODUCT(D11:F11,$D$9:$F$9)

to compute the total number of chassis we will use for any given number of TV sets, stereos and speakers. Then we can copy this formula to cells C12:C15 to compute the total number of parts used of all the other types. (The dollar signs in $D$9:$F$9 specify that this cell range stays constant, while the cell range D11:F11 becomes D12:F12, D13:F13, etc. in the copied formulas.)

In cells B11:F15, we've entered the number of parts we have in inventory of each type. This allows us to express the constraints shown earlier as:

C11:C15 <= B11:B15

This is a shorthand for C11 <= B11, C12 <= B12, and so on through C15 <= B15. We can enter this form directly in the Solver dialogs. We will also enter the constraint:

D9:F9 >= 0

to require the decision variables to have non-negative values.

Using the Solver Dialogs

To let the Solver know which cells on the worksheet represent the decision variables, constraints and objective function, we select Tools Solver... in Excel, or Range Analyze Solver... in 1-2-3, which displays the Solver Parameters dialog. In the Set Target Cell edit box, we type or click on cell D18, the objective function. In the By Changing Cells edit box, we type D9:F9 or select these cells with the mouse. To add the constraints, we click on the Add button, select cells C11:C15 in the Cell Reference edit box (the left hand side), and select cells C11:B15 in the Constraint edit box (the right hand side); the default relation <= is OK:

We choose the Add button again (either from the dialog above, or from the main Solver Parameters dialog) to define the non-negativity constraint on the decision variables. When we've completely specified the problem, the Solver Parameters dialog looks like the one below:

Solving the Problem

To find the optimal solution, we simply click on the Solve button. After a moment, the Solver returns the optimal solution: 200 in cells D9 and E9, and 0 in cell F9. This means that we should build 200 TV sets, 200 stereos and no speakers, to earn a total profit of $25,000 (shown at cell D18). The message "Solver found a solution" appears in the Solver Results dialog, as shown below:

We now click on "Answer" in the Reports list box to produce an Answer Report, and click OK to keep the optimal solution values in cells D9:F9. After a moment, the Solver creates another sheet containing an Answer Report, like the one below:

This report shows the original and final values of the objective function (target cell) and the decision variables (adjustable cells), as well as the status of each constraint at the optimal solution. Notice that the constraints on speaker cones and electronics are "binding" and have a "slack" value of 0: We used up all of the parts of these two types in building 200 TV sets and 200 stereos, but there were 50 units of each of the other parts left over. If we could obtain additional speaker cones and electronics, we could further increase total profits, but extra units of the other parts would not help in the short run.

If you've gotten to this point, congratulations! You have successfully set up and solved a simple Solver problem. You can return to the Tutorial to learn more about the theory underlying the Solver and the relative difficulty of different problem types. Or, to learn more about good practices in Solver model design, consult Hints for building readable, maintainable models (25K).