1 / 8

Optimizing Transportation Costs in Excel Using the Solver Tool

Learn how to effectively set up and solve transportation optimization problems in Excel using the Solver add-in from the Analysis ToolPak. This guide walks you through the initial setup, including how to enable the Analysis ToolPak, define constraints for garages and locations, and formulate the cost function based on bus allocations. With practical examples and clear instructions, you will understand how to leverage Solver settings to find the optimal solution for minimizing transportation costs.

karik
Télécharger la présentation

Optimizing Transportation Costs in Excel Using the Solver Tool

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Excel for Optimization by Michael Nichols

  2. Initial Setup The Analysis ToolPack on the Microsoft Office CD includes the Solver. Select the Tools menu and choose “Add Ins”. Check the box corresponding to the Analysis ToolPack.

  3. Transportation Example

  4. Problem Setup Each Garage has a cost associated with sending to each Location. Each Location has a minimum number of needed buses.

  5. Problem Setup (cont.) Each Garage has the total number of buses being sent as a constraint. Each location has the total number of buses allocated, also as a constraint. This will be the area that can be modified by the solver to find the solution.

  6. Problem Setup (cont.) Each Time entry is a sum of the Buses sent multiplied by the cost to send to each location. Ex: “=(LocA*Garage1)+(LocA*Garage2)+(LocA*Garage3)” Total is a sum of the times.

  7. Solver Settings Select “Solver” from the Tools menu. In this section, you will be able to enter your constraints as well as select the method used to find your solution.

  8. Problem solution

More Related