Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Solver Tutorial - Defining Constraints

Defining Constraints

Constraints reflect real-world limits on production capacity, market demand, available funds, and so on.  To define a constraint, you first compute a value based on the decision variables.  Then you place a limit (<=, = or >=) on this computed value.

General Constraints.  For example, if A1:A5 contains the percentage of funds to be invested in each of 5 stocks, you might use B1 to calculate =SUM(A1:A5), and then define a constraint B1 = 1 to say that the percentages allocated must sum up to 100%.

Bounds on Variables.  Of course, you can also place a limit directly on a decision variable, such as A1 <= 100.  Upper and lower bounds on the variables are efficiently handled by most optimizers and are very useful in many problems.

Policy Constraints  

Some constraints are determined by policies that you or your organization may set.  For example, in an investment portfolio optimization, you might have a limit on the maximum percentage of funds to be invested in any one stock, or one industry group.

Physical Constraints  

Many constraints are determined by the physical nature of the problem.  For example, if your decision variables measure the number of products of different types that you plan to manufacture, producing a negative number of products would make no sense.  This type of non-negativity constraint is very common.  Although it may be obvious to you, constraints such as A1 >= 0 must be stated explicitly, because the solver has no other way to know that negative values are disallowed.

As another example of a physically determined constraint, suppose you are modeling product shipments in and out of a warehouse over time.  You'll probably need a balance constraint, which specifies that, in each time period, the beginning inventory plus the products received minus the products shipped out equals the ending inventory -- and hence the beginning inventory for the next period.

Integer Constraints

Advanced optimization software also allows you to specify constraints that require decision variables to assume only integer (whole number) values at the solution.  If you are scheduling a fleet of trucks, for example, a solution that called for a fraction of a truck to travel a certain route would not be useful.  Integer constraints normally can be applied only to decision variables, not to quantities calculated from them.

A particularly useful type of integer constraint specifies that a variable must have an integer value with a lower bound of 0, and upper bound of 1.  This forces the variable to be either 0 or 1 -- nothing in between -- at the final solution.  Hence, it can be used to model "yes/no" decisions.

For example, you might use a 0-1 or binary integer variable to represent a decision on whether to lease a new machine.  Your model might then calculate a fixed lease cost per month, but also a lower cost per item processed with the machine, if it is used.  A solver can help determine whether leasing the machine will yield higher or lower profits.

Next: Interpreting Solutions >

< Back to How Do I Define a Model?

<< 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
Email address
Name
First Last
Company
University
Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.