Part V: Analyzing Data with Excel
774
FIGURE 37.11This worksheet determines the least expensive way to ship products from warehouses to retail outlets.
This workbook is rather complicated, so each part is explained individually:l (^) Shipping Costs Table: This table, in range B2:E8, is a matrix that contains per-unit ship-
ping costs from each warehouse to each retail outlet. The cost to ship a unit from Los
Angeles to Denver, for example, is $58.
l Product needs of each retail store: This information appears in C12:C17. For example,
Denver needs 150 units, Houston needs 225, and so on. C18 contains a formula that cal-
culates the total needed.
l (^) Number to ship from: Range D12:F17 holds the adjustable cells that Solver varies. These
cells are all initialized with a value of 25 to give Solver a starting value. Column G con-
tains formulas that sum the number of units the company needs to ship to each retail
outlet.
l (^) Warehouse inventory: Row 21 contains the amount of inventory at each warehouse, and
row 22 contains formulas that subtract the amount shipped (row 18) from the inventory.
l (^) Calculated shipping costs: Row 24 contains formulas that calculate the shipping costs.
Cell D24 contains the following formula, which is copied to the two cells to the right of
Cell D24:
=SUMPRODUCT(C3:C8,D12:D17)