Microsoft Office Tutorials and References
In Depth Information
USING EXCEL SOLVER
When taking values from the Constants section to calculate formulas, you almost always should
use absolute cell references (\$) because you will copy the formulas down the columns.
￿
Trucks
Cell H16 contains the number of trucks selected to ship the merchandise. Cell H16 is a
Changing Cell, which means Solver will determine the best number of trucks to use and place
the number in this cell. For now, you should
seed
the cell with a value of 1.
￿
Volume for Trucks
Cell I16 contains the number of trucks selected, multiplied by the capacity
of a truck. The capacity value is taken from the Constants section. The formula for this cell is
¼
H16*\$C\$5. Cell H16 is the number of trucks selected, and cell \$C\$5 is the volume capacity of
the truck in cubic feet.
￿
Tractor-Trailers
Cell J16 contains the number of tractor-trailers selected to ship the merchandise.
Cell J16 is a Changing Cell, which means Solver will determine the best number of tractor-trailers
to use and place the number in this cell. For now, you should
seed
the cell with a value of 1.
￿
Volume for Tractor-Trailers
Cell K16 contains the number of tractor-trailers selected,
multiplied by the capacity of a tractor-trailer. The capacity value is taken from the Constants
section. The formula for this cell is
J16*\$C\$6. Cell J16 is the number of tractor-trailers
selected, and cell \$C\$6 is the cubic feet capacity of the tractor-trailer.
¼
￿
Total Vehicle Capacity
Cell L16 contains the sum of the Volume for Trucks and the Volume for
Tractor-Trailers. The formula for this cell is
K16. You need to know the Total Vehicle
Capacity to make sure that you have enough capacity to ship the Volume Required. This value
will be one of your constraints in Solver.
I16
¼
þ
￿
% of Vehicle Capacity Utilized
Cell M16 contains the Volume Required divided by the Total
Vehicle Capacity. The formula for this cell is
G16/L16; after entering the formula, click the %
button in the Number group. Although this information is not required to minimize shipping
costs, it is useful for managers to know how much space was filled in the selected vehicles.
Alternatively, you could run Solver to determine the highest space utilization on the vehicles
rather than the lowest cost. Note that you cannot use more than 100% of the available space on
the trucks.
¼
￿
Shipping Cost
Cell N16 contains the following calculation:
Mileage to destination store
Number of trucks selected
Cost per mile for trucks
Mileage
þ
to destination store
Number of tractor-trailers selected
Cost per mile for tractor-trailers
The formula for this cell is
J16*C16*\$D\$6. Note that absolute cell references
for the cost-per-mile values are taken from the Constants section.
H16*C16*\$D\$5
¼
þ
If you entered the formulas correctly in row 16, your table should look like Figure D-10.
FIGURE D-10
Vehicle Loading and Cost sections with formulas entered in the first row

Search JabSto ::

Custom Search