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 X1 for the number of Tahoe pallets produced, X2 for the number of Pacific pallets produced, and X3 for the number of Savannah pallets produced, and X4 for the number of Aspen pallets produced, the objective (calculating total profit) is:

         Maximize: 450 X1 + 1150 X2 + 800 X3 + 400 X4

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 X1 + 50 X2 + 100 X3 + 50 X4 <= 5800 (Glue)
           5 X1 + 15 X2 + 10 X3 + 5 X4 <= 730 (Pressing)
         500 X1 + 400 X2 + 300 X3 + 200 X4 <= 29200 (Pine chips)
         500 X1 + 750 X2 + 250 X3 + 500 X4 <= 60500 (Oak chips)

Since the number of products built cannot be negative, we'll also have non-negativity conditions on the variables:

         X1, X2, X3, X4 >= 0.

Now, we'll take you step by step through implementing and solving this optimization model using Excel's built-in 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.