Microsoft Office Tutorials and References

In Depth Information

**USING EXCEL SOLVER**

To use Solver, you must set up a model of the problem, including the factors that can vary (the mix of

trucks and tractor-trailers) and the constraints on how much they can vary (the number of each vehicle

available). Your goal is to minimize the shipping cost; you will execute Solver to compute the best solution.

Setting Up a Spreadsheet Skeleton

CV Fitness makes three fitness machines: exercise bikes (EB), elliptical cross-trainers (CT), and treadmills

(TM). When packaged for shipment, their shipping volumes are 12, 15, and 22 cubic feet, respectively.

The finished machines are shipped via ground transportation to five stores in Philadelphia, Atlanta, Miami,

Chicago, and Los Angeles. Your vehicle fleet consists of 12 trucks and six tractor-trailers. Each truck has a

capacity of 1500 cubic feet, and each tractor-trailer has a capacity of 2350 cubic feet. The spreadsheet

includes the road distances from your plant in Memphis to each store, and each store

’

s demand for the three

fitness machines.

What is the best mix of trucks and tractor-trailers to send to each destination? You will learn how to use

Solver to determine the answer. The spreadsheet components are discussed in the following sections.

AT THE KEYBOARD

Start by saving your blank spreadsheet as CV Fitness.xlsx. Then enter the skeleton and formulas as directed

in the following sections.

Spreadsheet Title

Resize Column A, as illustrated in Figure D-5, to give your spreadsheet a small border on the left side. Enter

the spreadsheet title in cell B1. Assign the title a font of Calibri Bold and a font size of 14 points. Merge and

center cells B1 through F1 using the Merge and Center button in the Alignment group of the Home tab.

Constants Section

Your spreadsheet should have a section for values that will not change. Figure D-5 shows a skeleton of the

Constants section and the values you should enter. A discussion of the line items follows the figure.

FIGURE D-5

Spreadsheet title and Constants section

In column C, enter the Volume Cu. Ft., which is the cubic-foot capacity of the vehicles as well as

the shipping volume for each item of exercise equipment.

In column D, enter the Operating Cost per mi., which is the cost per mile driven for each type of

vehicle.

In column E, enter the Operating Cost per mi.-cu.ft. This value is actually a formula: the

operating cost per mile divided by the vehicle volume in cubic feet. Normally you do not put formulas

in the Constants section, but in this case it lets you see the relative cost efficiencies of each

vehicle. Assuming that both types of vehicles can be filled to capacity, the tractor-trailer is the

preferred vehicle for shipping cost efficiency.