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
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.
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.
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.
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
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.