Will Solver do the job?

[Mark]

Just a question.......Work related.

Basically my job involves scheduling deliveries to customers at various
sites from various distribution points. The drivers work for a set number of
hours on their shift.

Will Solver be the tool to build myself a spreadsheet where I can enter into
it delivery points, distribution points, time each delivery takes. And get
solver to provide me with the best distribution point for each customer and
also which deliveries a particular driver can make within his 12 hour day?

 

[GD]

Not sure about solver as i've never used it but i use Whats Best! 9.0 for a
similar routing optimisation

 

[Dan]

This is a classic Operations Research problem. Yes, Solver will work unless
you overwhelm it with scores of routes, destinations, drivers.

 

[Mark]

It would involve about 150 - 200 delivery points 7 supply points and 42
driver shifts

 

[Shane Devenshire]

Hi,

Solver is a fairly complex tool, you should take a look at the SOLVSAMP.XLS
file which is probably on your computer.

One point, Solver does not necessarily give you the best solution, it gives
you A solution which meets your criteria. That is, sometimes more than one
solution work but Solver only returns one, not the whole collection.

 

[Mike Middleton]

Mark -

In addition to the examples in the SolvSamp workbook (available on your hard
drive if you have installed Solver from the CD), there are more examples
available on the Solver web site:

Distribution Examples:
http://www.solver.com/discenter.htm

Scheduling Examples:
http://www.solver.com/schcenter.htm

Larger problems may exceed the capacity of standard Solver (200 adjustable
cells), but Frontline Systems provide more advanced versions of Solver.

- Mike

http://www.MikeMiddleton.com

 

[James Silverton]

Mike wrote on Tue, 10 Mar 2009 11:32:32 -0700:

In addition to the examples in the SolvSamp workbook
(available on your hard drive if you have installed Solver
from the CD), there are more examples available on the Solver web
site:

Distribution Examples:
http://www.solver.com/discenter.htm

Scheduling Examples:
http://www.solver.com/schcenter.htm

Larger problems may exceed the capacity of standard Solver
(200 adjustable cells), but Frontline Systems provide more
advanced versions of Solver.

I take it that Solver has not solved the "Travelling salesman" problem
while I was not looking? Isn't there a Fields medal going?
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Mike Middleton]

James Silverton -

I take it that Solver has not solved the "Travelling salesman" problem
while I was not looking? <

As far as I know, the main difficulty with solving the Traveling Salesman
Problem (TSP) is that for an n-city problem there are (n-1)! possible tours.
Thus, a 17-city TSP has 21 trillion possible routes. So, the problem can
take a long time to solve.

It's easy to set up the TSP in a spreadsheet, and it's in most management
science and operations research textbooks. Those textbooks usually include
Premium Solver for Education, which has an "alldifferent" constraint and an
Evolutionaly Solver that are not in the standard Solver shipped with Excel.
The TSP can be set up and solved using standard Solver, but the alldifferent
constraint simplifies the spreadsheet formulation.

On the www.solver.com web site, if you search locally for TSP, you'll see
several examples, including one that says "The classic Traveling Salesman
Problem (TSP) is easy to solve using the "alldifferent" constraint supported
by all Solver Engines in the Solver Platform SDK."

- Mike

http://www.MikeMiddleton.com