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