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 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:  decision variables, constraints, and the objective -- and their relationships.

Decision Variables

Start with the decision variables. They usually measure the amounts of resources, such as money, to be allocated to some purpose, or the level of some activity, such as the number of products to be manufactured, the number of pounds or gallons of a chemical to be blended, etc.

If you are shipping goods from, say, 3 different plants to 5 different warehouses, there are 3 x 5 = 15 different routes along which products could be shipped.  So, you might have 15 variables, each one measuring the number of products shipped along that route. You might also have 4 different product types, and you might want to plan shipments in each of the next 6 months.  So this might lead to 3 x 4 x 5 x 6 = 360 variables.  This illustrates how a model can become large rather quickly!  Part of the art of modeling is deciding how much detail is really required; for example, you might not need to explicitly measure shipments of different product types.

Objective Function

Once you've defined the decision variables, the next step is to define the objective, which is normally some function that depends on the variables.  For example, suppose you were planning how many units to manufacture of three products: TV sets, stereos, and speakers.  Your objective might be to maximize profit, so assume that each TV set yields a profit of $75, each stereo $50, and each speaker $35.  Then your objective function might be:

75 * TVsets + 50 * Stereos + 35 * Speakers

On a spreadsheet where the number of TV sets, stereos and speakers are in cells D9, E9 and F9 respectively, the formula would be:

= 75*D9 + 50*E9 + 35*F9

You'd be finished at this point, if the model did not require any constraints.  For example, in a curve-fitting application, the objective is to minimize the sum of squared differences between each actual data value, or observation, and the corresponding predicted value.  This sum has a minimum value of zero, which occurs only when the actual and predicted values are all identical.  If you asked a solver to minimize this objective function, you would not need any constraints.

In most models, however, constraints play a key role in determining what values can be assumed by the decision variables, and what sort of objective value can be attained.

Next: Defining Constraints >

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