Microsoft Office Tutorials and References

In Depth Information

**USING EXCEL SOLVER**

Working the Model Manually

Now that you have a working model, you could manipulate the number of trucks and tractor-trailers manually

to obtain a solution to the shipping problem. You would need to observe the following rules (or constraints):

1. Assign enough Total Vehicle Capacity to meet the Volume Required for each destination. (In

other words, you cannot exceed 100% of Vehicle Capacity Utilized.)

2. The total number of trucks and tractor-trailers you assign cannot exceed the number available in

your fleet.

Try to assign your trucks and tractor-trailers to meet your shipping requirements, and note the total

shipping costs

you may get lucky and come up with an optimal solution. The tractor-trailers are more cost

efficient than the trucks, but the problem is complicated by the fact that you want to achieve the best

capacity utilization as well. In some instances, the trucks may be a better fit. Figure D-16 shows a sample solution

determined from working the problem manually.

—

FIGURE D-16

Manual attempt to solve the vehicle loading problem optimally

after all, you have not violated any of your constraints, and you

have a 94% average vehicle capacity utilization. But is it the most cost-effective solution for your company?

This is where Solver comes in.

This probably looks like a good solution

—

Setting up Solver Using the Solver Parameters Window

To access the Solver pane, click the Data tab on the Ribbon, then click Solver in the Analysis group on the far

right side of the Ribbon. The Solver Parameters window appears (see Figure D-17).

NOTE

Solver in Excel 2010 has changed significantly from earlier versions of Excel. It allows three different calculation methods,

and allows you to specify an amount of time and number of iterations to perform before Excel ends the calculation. Refer to

Microsoft Help for more information on Solver’s new capabilities.