Solver Tutorial  Step by Step  Product Mix Example
Product Mix Example
This part of our Solver Tutorial takes you step by step through the process of creating a Solver model, using a Product Mix example. We'll first show you how to define the problem and write out formulas for the objective and constraints. Then we'll walk you through two ways to define and solve your model  in an Excel spreadsheet, or in a Visual Basic program.
The Example Problem
Imagine that you manage a factory that produces four different types of wood paneling. Each type of paneling is made by gluing and pressing together a different mixture of pine and oak chips. The following table summarizes the required amount of gluing, pressing, and mixture of wood chips required to produce a pallet of 50 units of each type of paneling:

Resources Required per Pallet of Paneling Type



Tahoe

Pacific

Savannah

Aspen

Glue (quarts)

50

50

100

50

Pressing (hours)

5

15

10

5

Pine chips (pounds)

500

400

300

200

Oak chips (pounds)

500

750

250

500

In the next production cycle, you have 5,800 quarts of glue; 730 hours of pressing capacity; 29,200 pounds of pine chips; and 60,500 pounds of oak chips available. Further assume that each pallet of Tahoe, Pacific, Savannah, and Aspen panels can be sold for profits of $450, $1,150, $800, and $400, respectively.
Writing the Formulas
Before we implement this problem statement in either Excel or Visual Basic, let's write out formulas corresponding to the verbal description above. If we temporarily use the symbol X_{1} for the number of Tahoe pallets produced, X_{2} for the number of Pacific pallets produced, and X_{3} for the number of Savannah pallets produced, and X_{4} for the number of Aspen pallets produced, the objective (calculating total profit) is:
Maximize: 450 X_{1} + 1150 X_{2} + 800 X_{3} + 400 X_{4}
A pallet of each type of panel requires a certain amount of glue, pressing, pine chips, and oak chips. The amount of resources used (calculated by the left hand side of each constraint) depends on the mix of products built, and we have a limited amount of each type of resource available (corresponding to the constraint right hand side values). The constraints for this problem are expressed as follows:
Subject to:
50 X_{1} + 50 X_{2} + 100 X_{3} + 50 X_{4} <= 5800 (Glue)
5 X_{1} + 15 X_{2} + 10 X_{3} + 5 X_{4} <= 730 (Pressing)
500 X_{1} + 400 X_{2} + 300 X_{3} + 200 X_{4} <= 29200 (Pine chips)
500 X_{1} + 750 X_{2} + 250 X_{3} + 500 X_{4} <= 60500 (Oak chips)
Since the number of products built cannot be negative, we'll also have nonnegativity conditions on the variables:
X_{1}, X_{2}, X_{3}, X_{4} >= 0.
Now, we'll take you step by step through implementing and solving this optimization model using Excel's builtin Solver or Risk Solver Platform, and in Visual Basic .NET (or another language) using Solver Platform SDK. Just click the link you want to follow first.
<< Back to: Tutorial Start Next: Solving the Problem in Excel >
Next: Solving the Problem Using VB.Net >