Microsoft Office Tutorials and References
In Depth Information
USING EXCEL SOLVER
￿
In row 15, columns D, E, and F, enter
EB,
”“
CT,
and
TM,
respectively, as equipment
￿
In row 15, columns G through N, enter
Volume Required,
”“
Trucks,
”“
Volume for Trucks,
Tractor-Trailers,
”“
Volume for Tractor-Trailers,
”“
Total Vehicle Capacity,
”“
% of Vehicle
Capacity Utilized,
and
Shipping Cost,
￿
In rows 16 through 20, column B, enter the destination store locations.
￿
In rows 16 through 20, column C, enter the number of miles to the destination store locations.
￿
In rows 16 through 20, columns D through F, enter the number of exercise bikes (EB),
cross-trainers (CT), and treadmills (TM) to be shipped to each store location.
￿
Rows 16 through 20, columns G through N, will contain formulas or
later. Leave
them blank for now, but fill cells H16 through H20 and cells J16 through J20 with a light color to
indicate that they are the Changing Cells for Solver.
seed values
￿
In cell F21, enter
Totals:
to label the following cells in the row.
￿
Cells G21 through N21 will be used for column totals. Highlight cell N21 with a different light
color fill from the Changing Cells. Cell N21 is your Optimization Cell.
￿
In cell B22, enter
Fill Legend:
as a label.
￿
Fill cell C22 with the fill color you selected for the Changing Cells.
￿
In cells D22 and E22, enter
Changing Cells
as the label for the fill color. Merge and center the
label in the cells.
￿
In cell N22, enter
Total Cost
as the label for the value in cell N21.
￿
Fill cell C23 with the fill color you selected for the Optimization Cell.
￿
In cells D23 and E23, enter
Optimization Cell
as the label for the fill color. Merge and center
the label in the cells.
Figure D-7 illustrates a magnified section of the Distance/Demand table in case the numbers in Figure D-6