Microsoft Office Tutorials and References

In Depth Information

**USING EXCEL SOLVER**

FIGURE D-18

Solver Parameters window with the Objective cell and Changing Cells entered

Note that Solver has added absolute cell references (the $ signs before the column and row designators)

for the cells you have specified. Solver will also add these references to the constraints you define. Solver

adds the references to preserve the links to the cells in case you revise the worksheet in the future. In fact,

you will make changes to the worksheet later in the tutorial.

Defining and Entering Constraints

For Solver to successfully determine the optimum solution for the shipping problem, you need to specify what

constraints or rules it must observe to calculate the solution. Without constraints, Solver theoretically might

calculate that the best solution is not to ship anything, resulting in a cost of zero. Furthermore, if you failed to

define variables as positive numbers, Solver would select

“

negative trucks

”

to maximize

“

negative costs.

”

Finally, the trucks are indivisible units

you cannot assign a fraction of a truck for a fraction of the cost, so

you must define your Changing Cells as integers to satisfy this constraint.

Aside from the preceding logical constraints, you have operational constraints as well. You cannot assign

more vehicles than you have in your fleet, and the vehicles you assign must have at least as much total

capacity as your shipping volume.

Before entering the constraints in the Solver Parameters window, it is generally a good idea to list them in

plain terms. You must enter the following constraints for this model:

—

All trucks and tractor-trailers in the Changing Cells must be integers greater than or equal to

zero.

The sums of trucks and tractor-trailers assigned (cells H21 and J21) must be less than or equal

to the available trucks and tractor-trailers (cells F5 and F6, respectively).

The Total Vehicle Capacity for the vehicles assigned to each store (cells L16 to L20) must be

greater than or equal to the Volume Required to be shipped to each store (cells G16 to G20,

respectively).