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 which 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

Let's assume that for 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₁ for the number of Tahoe pallets produced, X₂ for the number of Pacific pallets produced, and X₃ for the number of Savannah pallets produced, and X₄ for the number of Aspen pallets produced, the objective (calculating total profit) is:

Maximize 450 X₁ + 1150 X₂ + 800 X₃ + 400 X₄

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: (These rules are entered into the solver one at a time)

50 X₁ + 50 X₂ + 100 X₃ + 50 X₄ <= 5,800 (Glue)
5 X₁ + 15 X₂ + 10 X₃ + 5 X₄ <= 730 (Pressing)
500 X₁ + 400 X₂ + 300 X₃ + 200 X₄ <= 29,200 (Pine Chips)
500 X₁ + 750 X₂ + 250 X₃ + 500 X₄ <= 60,500 (Oak Chips)

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

X₁, X₂, X₃, X₄ >= 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.

Next: Solving the Problem in Excel >

Next: Solving the Problem Using VB.net >

< Back to Optimization Tutorial Start