Hey All,
I’m not sure where to begin but I’d like to try and tackle some ‘optimisation’ in excel? I’m not unfamiliar with the concept but have never had to implement it.
The challenge is;
Every day I need to assign/roster desks to call centre staff. I have a list of people working, what times their shifts are, what ‘zone’ of the office they need to sit in, and a list of desks for that zone
My considerations/constraints are;
- Some individual people have desk preferences which I want to cater to. I have this info stored in a table.
- If someone doesn’t have a preference, we try with best endeavours to keep them at the same desk they last sat at, or at least close by. This is also stored in a table.
- There are smaller groups/subsets of people within the bigger group that I need to sit in a certain area. E.g. group X always sit at desk #’s 80-100
- A desk can (and should) be used multiple times per day, but shifts on that desk cannot overlap. E.g. shift 1 0700-1400, shift 2 1400-1800 on the same desk is perfectly fine, but if shift 2 started at 1330 it would not work.
- The desk number assigned must be a valid desk number for that area of the building. (e.g. desk IN a list)
- Some desks that are in use from overnight shifts the night before can not be used until they are free. Basically another version of desk overlap above. This info will be stored in a table.
At the moment I am doing this ‘manually’. I have a whole lot of formulas to check that the above are taken in to consideration, but ultimately its me ‘assigning’ the desk and checking at the end that all the considerations are still met.
The way I’m thinking about it at the moment, I could assign a table of costs to the above constraints. E.g. desk preference is met, cost of zero otherwise cost of 100, etc. There would be a bit of set up and I haven’t put too much thought in to it but I reckon I could generate some cost tables and return cost for a desk selection for a person based on the above.
Could I use excels solver to help with this with the goal being to minimise total ‘cost’ and if so a helping hand/point in the right direction would be really appreciated, or is there a better or different method I should try?
Thanks for your help
