Even distribution of work contents with different frequencies

[ccyilmaz]

Hello everyone,

Since I'm struggling with an excel problem for long i decided to ask pro's about their opinions and suggestions. Any help is highly appreciated.

I try to organize frequent tasks so that each week gets similar share of the whole bunch. The year is divided in 52 weeks and there are in total 80 tasks with their own frequencies to be carried out. For instance a certain control needs to be done 8 times a year in equal intervals. The frequencies differ from every week to once a year. They also have their own durations variing from 0,5 hour to 8 hours. The model i try to build will be optimizing the personnel capacity need each week. The deviation of total duration for each week should not be big from each other.

What i did is:

I created a matrix with weeks in columns and tasks in rows. After the 52. week i added a column which returns the total necessary time for each task for the whole year by multiplying the frequency with the single duration. By using solver i tried to minimize the standard deviation of the sum of each weeks total duration of tasks. However i couldn't manage to make an optimization that way.

Anybody having a clue how i can make the optimization?

 

[oldbrewer]

a once a year task MIGHT be carried out in december and then january but that would be silly. Are the task durations really important since a year is 8760 hours. Do you work say 8 to 6 or 24 hours per day. I would tend to sort out the tasks that consume the biggest hours first...

 

[oldbrewer]

TASK	FREQ	HOURS	freq*hours			TASK	FREQ	HOURS	freq*hours		every		1	2	3	4	5	6	7	8
T1	4	4	16			T18	8	5	40		46		11/02/2016	28/03/2016	13/05/2016	28/06/2016	13/08/2016	28/09/2016	13/11/2016	29/12/2016
T2	6	6	36			T2	6	6	36		61		26/02/2016	27/04/2016	27/06/2016	27/08/2016	27/10/2016	27/12/2016
T3	3	7	21			T4	8	4	32		46		11/02/2016	28/03/2016	13/05/2016	28/06/2016	13/08/2016	28/09/2016	13/11/2016	29/12/2016
T4	8	4	32			T12	4	7	28		91		27/03/2016	26/06/2016	25/09/2016	25/12/2016
T5	4	6	24			T5	4	6	24		91		27/03/2016	26/06/2016	25/09/2016	25/12/2016
T6	2	3	6			T3	3	7	21		122		27/04/2016	27/08/2016	27/12/2016
T7	1	8	8			T8	4	5	20		91		27/03/2016	26/06/2016	25/09/2016	25/12/2016
T8	4	5	20			T15	4	5	20		91		27/03/2016	26/06/2016	25/09/2016	25/12/2016
T9	6	1	6			T10	3	6	18		122		27/04/2016	27/08/2016	27/12/2016
T10	3	6	18			T1	4	4	16		91		27/03/2016	26/06/2016	25/09/2016	25/12/2016
T11	8	1	8			T17	3	4	12		122		27/04/2016	27/08/2016	27/12/2016
T12	4	7	28			T20	2	6	12		183	2	27/06/2016	27/12/2016
T13	2	2	4			T7	1	8	8		365	1	26/12/2016
T14	1	6	6			T11	8	1	8		46		11/02/2016	28/03/2016	13/05/2016	28/06/2016	13/08/2016	28/09/2016	13/11/2016	29/12/2016
T15	4	5	20			T19	4	2	8		91		27/03/2016	26/06/2016	25/09/2016	25/12/2016
T16	6	1	6			T6	2	3	6		183	2	27/06/2016	27/12/2016
T17	3	4	12			T9	6	1	6		61		26/02/2016	27/04/2016	27/06/2016	27/08/2016	27/10/2016	27/12/2016
T18	8	5	40			T14	1	6	6		365	1	26/12/2016
T19	4	2	8			T16	6	1	6		61		26/02/2016	27/04/2016	27/06/2016	27/08/2016	27/10/2016	27/12/2016
T20	2	6	12			T13	2	2	4		183	2	27/06/2016	27/12/2016
			01/01/2016
							easy to set up something like this plot on a scatter chart and tinker with it….
							eg the 1 2 and 3 frequencies can fill gaps after setting the bigger ones
							second table to the right is clearly first table sorted

<tbody>
</tbody>

 

[ccyilmaz]

Dear oldbrewer,

thanks for the suggestion. In this solution i am not sure if i get the same work load for each week.

I prepared following excel table showing a full matrix of tasks distributed throughout the whole year. My problem here is that the work load for each week is so much variing that in some weeks i have not enough manpower to complete them and in some weeks i need only 0,5 hour manpower capacity. The solution i seek should be arranging the tasks so that i get (in best case 1003 hours / 52 weeks = 19,3 hours) a not so much variing manpower need btw. the weeks.

				weeks
TASK	How many times annually a task should be done?	Duration of each task	Total time needed annually to complete the task	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52
1	13	24	312	24,0				24,0				24,0				24,0				24,0				24,0				24,0				24,0				24,0				24,0				24,0				24,0				24,0
2	2	80	160	80,0																										80,0
3	7	8	56	8,0								8,0								8,0								8,0								8,0								8,0								8,0
4	26	2	52	2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0
5	26	2	52	2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0		2,0
6	13	4	52	4,0				4,0				4,0				4,0				4,0				4,0				4,0				4,0				4,0				4,0				4,0				4,0				4,0
7	52	0,5	26	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5	0,5
8	3	8	24	8,0																	8,0																		8,0
9	2	10	20	10,0																										10,0
10	13	1,5	20	1,5				1,5				1,5				1,5				1,5				1,5				1,5				1,5				1,5				1,5				1,5				1,5				1,5
11	2	8	16	8,0																										8,0
12	4	4	16	4,0													4,0													4,0													4,0
13	4	4	16	4,0													4,0													4,0													4,0
14	1	16	16	16,0
15	2	8	16	8,0																										8,0
16	7	2	14	2,0								2,0								2,0								2,0								2,0								2,0								2,0
17	13	1	13	1,0				1,0				1,0				1,0				1,0				1,0				1,0				1,0				1,0				1,0				1,0				1,0				1,0
18	4	2,5	10	2,5													2,5													2,5													2,5
19	4	2	8	2,0													2,0													2,0													2,0
20	2	4	8	4,0																										4,0
21	2	4	8	4,0																										4,0
22	1	8	8	8,0
23	7	1	7	1,0								1,0								1,0								1,0								1,0								1,0								1,0
24	7	1	7	1,0								1,0								1,0								1,0								1,0								1,0								1,0
25	7	1	7	1,0								1,0								1,0								1,0								1,0								1,0								1,0
26	7	1	7	1,0								1,0								1,0								1,0								1,0								1,0								1,0
27	13	0,5	7	0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5
28	13	0,5	7	0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5
29	13	0,5	7	0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5				0,5
30	2	3	6	3,0																										3,0
31	2	2,5	5	2,5																										2,5
32	2	2,5	5	2,5																										2,5
33	4	1	4	1,0													1,0													1,0													1,0
34	7	0,5	4	0,5								0,5								0,5								0,5								0,5								0,5								0,5
35	2	1	2	1,0																										1,0
36	4	0,5	2	0,5													0,5													0,5													0,5
37	4	0,5	2	0,5													0,5													0,5													0,5
38	3	0,5	2	0,5
39	2	0,5	1	0,5																	0,5																		0,5
40	1	1	1	1,0
			1003
		Weekly needed man hours	223	1	5	1	37	1	5	1	51	1	5	1	37	15	5	1	51	9	5	1	37	1	5	1	51	1	142	1	37	1	5	1	51	1	5	9	37	1	5	15	51	1	5	1	37	1	5	1	51	1	5	1

<tbody>
</tbody><colgroup><col><col><col><col><col span="52"></colgroup>