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