Allocation problem

[netoine]

Hello guys,

I have to determine how to best allocate the trainers from my company to the venues so that they cover the fewest possible miles.

We have 4 trainers to allocate to 8 venues. One venue (New York with 3 rooms) requires 1.7 full time equivalent annually (FTE, so 1.7 trainer basically) whilst the other ones are smaller and only require the trainer to be here part time (0.3 FTE for Barcelona). It means that the same trainer can go to different venues.
The other constraint: one trainer can't have more than 1FTE.

Please find attached a file exposing the problem. The names of the venues have been changed but the distances are accurate. I've tried to experiment with solver but must say I'm not good enough to use it so need your help.

That would be awesome if you could tell me a way I can dispatch the trainers with the constraint given here.

Thanks in advance!

[Kaper]

to have a full coverage - it is an extension to "travelling salesman problem" - one of quite time-intensive computational problems.
But if we take some assumptions - we could use excel's solver for it.
1) if time requiired at site is >1 FTE then some trainers are "permanently" assigned to the site (NY in your case requires 1.74, so assign 4th trainer only to NY and remaining 0.74 FTE remains in main pool).
2) one trainer covers all needs of the site (after <1 of FTEs cprrection/assignment)
3) We do not play with sequence (so if a trainer is assigned Belize Canmore and Paris always travels in top-down sequence like Belize - Canmore - Paris , not (for instance) Belize - Paris - Canmore.

See attached file with solver set-up

[netoine]

Thanks a lot Kaper!! Really helpful.
Have a great day

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.