Can excel solver do this or do I need another way to solve this?

[rongil20]

I want the lowest numbers of workers to deliver the following

Constraints are

Workers need to work a min of 4 hours and max of 6 hours
Workers need at least 20 hours a week

It takes all workers 30 mins to reach the warehouse and prep the items. It then takes 30 mins to travel from the warehouse to the customer. Each delivery takes 30 mins
Let's also assume there are no returns journeys.

Workers can start a shift at 8am

Please let me know if you have any other questions

Table of the delivery schedule below

deliveries.xlsx
	O	P	Q	R	S	T	U	V
6			Day of Week
7			Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
8	Time	09:00-09:30	11	8	6	8	6	1
9		09:30-10:00	3	2	5	3	3	1
10		10:00-10:30	4	2	3	1	2	1
11		10:30-11:00	3	1	2	0	2	1
12		11:00-11:30	2	3	3	3	5	1
13		11:30-12:00	3	2	4	2	1	1
14		12:00-12:30	3	3	3	2	3	1
15		12:30-13:00	2	1	1	1	3	0
16		13:00-13:30	3	1	1	3	1	1
17		13:30-14:00	2	1	2	2	3	0
18		14:00-14:30	1	2	2	1	1	0
19		14:30-15:00	2	1	2	1	2	0
20		15:00-15:30	2	2	2	1	2	0
21		15:30-16:00	2	1	1	1	3	0
22		16:00-16:30	2	1	1	1	1	0
23		16:30-17:00	0	1	0	1	0	0
24		17:00-17:30	1	1	0	1	0	0
25		17:30-18:00	0	0	1	0	0	0
Sheet1

Cell Formulas
Range	Formula
Q8:V25	Q8	=D8*200

 

[offthelip]

I suspect you can build a series of equations that will solve your problem but you haven’t given enough information at the moment.

According to your rules a worker has between 3 and 5 hours actually making deliveries a day , if each delivery takes 30 minutes means they make 6 to 10 deliveries a day

Since workers must work 20 hours a weeks with a maximum of 6 hours, this means they must work on at least 4 days ( 3 * 6 = 18). So they work on 4 , 5 or 6 days which means a minimum of 24 deliveries and a maximum of 60

You have given time slots for all of the deliveries however you haven’t told us how to interpret these in allocating them to workers. I could assume that with 11 deliveries between 9 and 9:30 on Monday and each one taking 30 minutes that this would require 11 workers, however there are only 35 deliveries to make during the rest of the day so that is insufficient to employ them all ( 11 * 6=66), so there is no solution that fits your rules. So how do you want to interpret your schedule? Can those 11 be delivered at any time that day, in other words do we interpret it as 46 deliveries on Monday.

It is possible there may be more than one solution, also it is possible there may not be a solution

 

[offthelip]

A further thought:
If t = total deliveries / week

The minimum number of workers must be roundup(t/ 60) and the maximum rounddown( t/24)

 

[rongil20]

I suspect you can build a series of equations that will solve your problem but you haven’t given enough information at the moment.

According to your rules a worker has between 3 and 5 hours actually making deliveries a day , if each delivery takes 30 minutes means they make 6 to 10 deliveries a day

Since workers must work 20 hours a weeks with a maximum of 6 hours, this means they must work on at least 4 days ( 3 * 6 = 18). So they work on 4 , 5 or 6 days which means a minimum of 24 deliveries and a maximum of 60

You have given time slots for all of the deliveries however you haven’t told us how to interpret these in allocating them to workers. I could assume that with 11 deliveries between 9 and 9:30 on Monday and each one taking 30 minutes that this would require 11 workers, however there are only 35 deliveries to make during the rest of the day so that is insufficient to employ them all ( 11 * 6=66), so there is no solution that fits your rules. So how do you want to interpret your schedule? Can those 11 be delivered at any time that day, in other words do we interpret it as 46 deliveries on Monday.

It is possible there may be more than one solution, also it is possible there may not be a solution

Thanks for replying.

So those 11 deliveries need to be made by 9am.

I see your point though, and the actual constraint surrounding the 20 hours rule says this

“Our aim is to give workers at least 20 hours work a week”

So this constraint seems like a nice to have rather than a must have.

Also I noticed that I said they have to work 20 hours a week initially when I should have said at least 20 hours so I will edit the original post. Apologies for that.

I hope the relaxed 20 hour constraint helps solve this

 

[offthelip]

According to you rules you have got quite a number of constraints coming in, since a worker effectively starts delivering at 8:30 the maximum number of deliveries he can make by 9:30 is 2, so with 11 deliveries to make by 9:30 you need at least 6 workers. the total number of deliveries on Monday is 46. which given the maximum any one can do is 10 and the minimum is 6 means between 5 and 7 workers are needed, so this narrows it down to 6 or 7. However I can see that you have deliveries scheduled for the afternoon which will be after the finish time of the 8am shift which means you must have 7 workers on Monday.
these are exactly the sort of constraints I am thinking of utilising to whittle down the possible combinations such that it is easy to test each one.

 

[rongil20]

According to you rules you have got quite a number of constraints coming in, since a worker effectively starts delivering at 8:30 the maximum number of deliveries he can make by 9:30 is 2, so with 11 deliveries to make by 9:30 you need at least 6 workers. the total number of deliveries on Monday is 46. which given the maximum any one can do is 10 and the minimum is 6 means between 5 and 7 workers are needed, so this narrows it down to 6 or 7. However I can see that you have deliveries scheduled for the afternoon which will be after the finish time of the 8am shift which means you must have 7 workers on Monday.
these are exactly the sort of constraints I am thinking of utilising to whittle down the possible combinations such that it is easy to test each one.

Thanks, I actually read the 11 at 9am differently though. I think we need 11 workers for the very first delivery slot and then a lot then will be sitting idle until their 4hour shift ends.

Would you know how to set up the sheet to solve this?

 

[yabi100]

Dear Rongil20
I came to this post through similar post section. I have the same question and I am wondering if you have solved your question with solver or not? Althogh the post is little old, but let me know if ypu were able to slove your question with solver.