Solver Tutorial - Step by Step - Product Mix Example - in Excel

Product Mix Example - in Excel

Follow these steps to define and solve the Product Mix problem in an Excel spreadsheet:

• Creating an Excel Worksheet
• Using the Solver Dialogs
• Finding and Using the Solution

Creating an Excel Worksheet

Assuming that you have Microsoft Excel, and either the standard Excel Solver or a Premium Solver product installed, the next step is to create a worksheet where the formulas for the objective function and the constraints are calculated. In the worksheet below, 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.  (Click on the worksheet for a full-size image.)

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:B15, 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 B11:B15 in the Constraint edit box (the right hand side); the default relation <= is OK.  (Click on the dialog to see it full size.)

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 entered the problem, the Solver Parameters dialog looks like this (Click on the dialog to see it full size.)  This dialog appears for the Premium Solver Platform; the dialogs for the standard Excel Solver and the Lotus 1-2-3 Solver are similar, but they lack the Solver Engine dropdown list and the Variables button.

Finding and Using the Solution

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 here (Click on the dialog to see it full size).  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 worksheet containing an Answer Report, like the one below, and inserts it to the left of the problem worksheet in the Excel workbook.  (Click on the Answer Report for a full size image.)

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've successfully set up and solved a simple optimization problem using Microsoft Excel.  If you like, you can see how to set up and solve the same Product Mix problem in a Visual Basic program that calls our Solver DLL.  If you haven't yet read the other parts of the tutorial, you may want to return to the Tutorial Start and read the overviews "What are Solvers Good For?", "How Do I Define a Model?" and "What Makes a Model Hard to Solve?"

This was an example of a linear programming problem.  Other types of optimization problems may involve quadratic programming, mixed-integer programming, constraint programming, smooth nonlinear optimization, global optimization, and nonsmooth optimization.  To learn more, click on Optimization Problem Types.  For a more advanced explanation of linearity and sparsity in optimization problems, continue with our Advanced Tutorial.

Next:  Creating a Visual Basic Program >

Next:  Advanced Tutorial >

Optimization Problem Types >

 

< Back to Tutorial Start

To Learn More: For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.

User type Please Select Commercial User Consultant Corp Developer Govt/Military Professor Student VAR/ISV Firm Email address Name First Last Company University Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.