Hi folks, for a job I applied for I was today asked to create a (complex) work schedule, due for tomorrow. In this schedule I have to plot clients A to G, assigned to employees 1 to 4. Some clients have preferences, and both clients and employees have limited time availability during the week from Monday to Saturday. Each client has a predefined time that he can use. For the optimization the distances between the clients and the employees homes also matter. The parameters therefor would be something like:
1) distance (to client, and from client to client)
2) preference (male/female, language Spanish yes/no)
3) availability (both client and employee)
4) hours (which the clients have to use per week)
5) breaks (employees get 10 minutes break every 4 hours, 30 minutes lunch with 5 hours or more worked)
How can I let Excel create the optimal work schedule, in which all preferences are met, the travel distance is minimised, and all hours are met?
1) distance (to client, and from client to client)
2) preference (male/female, language Spanish yes/no)
3) availability (both client and employee)
4) hours (which the clients have to use per week)
5) breaks (employees get 10 minutes break every 4 hours, 30 minutes lunch with 5 hours or more worked)
How can I let Excel create the optimal work schedule, in which all preferences are met, the travel distance is minimised, and all hours are met?
