Microsoft Office Tutorials and References
In Depth Information
USING EXCEL SOLVER
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
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
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,