Solver Tutorial - Defining a Model
How Do I Define a Model?
A solver deals with numbers, so you'll need to quantify the various elements of your model -- the decision variables, the objective, and any constraints -- and their relationships.
Start with the decision variables. They usually measure the amounts of resources used (such as money to be allocated to some purpose) or the level of various activities to be performed (such as the number of products to be manufactured, or the number of gallons of a chemical to be blended).
For example, if you are shipping products from 3 different plants to 5 different warehouses (as shown below) there are 3 x 5 = 15 different paths along which products could be shipped. So, you would likely have 15 decision variables, each one representing the amount of product shipped from a particular plant to a particular warehouse.
Now, suppose each plant produces 4 different product types and you want to determine the optimal shipping plan in each of the next 6 months. This might lead to 3 x 4 x 5 x 6 = 360 decision variables. This simple example illustrates how a model can become large rather quickly. So part of the art of modeling is deciding how much detail is really required in a given situation.
Once you've defined the decision variables, the next step is to define the objective, which is normally some function that depends on the decision variables. As a simple example, suppose you were planning how many units to manufacture of three different types of TV sets: Plasma, LCD, and LED. Your objective might be to maximize profit, so assume that each Plasma TV yields a profit of $75, each LCD $50, and each LED $35. Then your objective (computing profit) could be expressed as:
75 x Number of Plasma sets + 50 x Number of LCD sets + 35 x Number of LED sets
On a spreadsheet where the number of Plasma, LCD and LED TV sets are in cells D9, E9 and F9, respectively, the formula for the objective cell could be:
= 75*D9 + 50*E9 + 35*F9
However, the values 75, 50, and 35 (known as the objective function coefficients) should probably be entered in individual cells in the spreadsheet, say, cells D8, E8, and F8, respectively. In that case, the formula for the objective would be:
= D8*D9 + E8*E9 + F8*F9
In some cases, an optimization model is defined completely by its decision variables and objective. However, most optimizations problems involve constraints that restrict the values the decision variables can assume and, in turn, the objective value that can be achieved.