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.  Follow either or both sets of links below to learn more.

• Essential Steps
• A Sample Model
• Writing the Formulas
   
• Creating an Excel Worksheet
• Using the Solver Dialogs
• Finding and Using the Solution
   
• Creating a Visual Basic Program
• Using the Solver DLL Functions
• Finding and Using the Solution

Essential Steps

To define an optimization model, you'll follow these essential steps:

1. Choose a spreadsheet cell, or a variable in your program, to hold the value of each decision variable in your model.
   
2. Create a spreadsheet formula in a cell, or an assignment statement in a program function, that calculates the objective function in your model.
   
3. Similarly, create formulas in cells, or assignment statements in a program function, to calculate the left hand sides of your constraints.
   
4. Use the dialogs in Excel, or function calls in your program, to tell the Solver about your decision variables, objective and constraint calculations, and desired bounds on constraints and variables.
   
5. Click Solve in Excel, or call optimize() in your program, to find the optimal solution.

Within this overall structure, you have a great deal of flexibility, either in a spreadsheet or in a custom program, in how you choose cells or variables to hold your model's decision variables and constraints, and which formulas and built-in functions you use.  Since decision variables and constraints usually come in groups, you'll want to use cell ranges in your spreadsheet, or arrays in your program to represent them.

A Sample Model

Imagine that you are managing a factory that 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.

From this description, we can see that the decision variables are the number of products to build, and the objective function will be (gross) profit.  Assume that 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.

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.  The parts you have on hand are 450 chassis, 250 picture tubes, 800 speaker cones, 450 power supplies and 600 sets of electronics.  This defines the constraints in this problem:  You can build only a limited number of products from the parts on hand.  

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

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 define the constraints in cell ranges in Excel or in arrays in Visual Basic, and calculate their values using a function like SUMPRODUCT in Excel or a simple FOR loop in VB.

Since the number of products built cannot be negative, we'll also have lower bounds on the variables: x1, x2, x3 >= 0.

Now, we'll take you step by step through implementing this Solver model, in either Excel using the Premium Solver or in Visual Basic (or another language) using the Solver DLL.  Click the link you want to follow first!

Next: Creating an Excel Worksheet >

Next: Creating a Visual Basic Program >

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