solver.com

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

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