Distribute workload to meet the target for multiple criteria for each staff

Looking for some help as I am stuck trying to solve this allocation problem. Have tried to search but can't seem to find a solution (with the introduction of the 4th criteria).

I am trying to allocate out a list of cases (~100 - 200) such that the sum of the load for each staff, for each criteria is as close to the respective targets as possible.

The 4 criteria are (1) Sub Task, (2) Complex Lvl 1, (3) Complex Lvl 2, (4) Checks Required. All are equally important, but if really need to prioritize, the total assigned value for criteria 1, 2, 3 should be as close to target as possible (deviation of 1-2 is still ok). Complexity level determines bulk of the effort. It may not be possible to distribute evenly for criteria 4 as is not uncommon to see a wide range of numbers (e.g. a couple of case with 200 or 400 checks, while the rest as below 50). Nonetheless, other than 1-2 staff bearing the additional load, the rest of staff of the staff should have similar load.

All cases must be assigned out.

Table 1: Data

Objective : Populate Assign Staff column

Case No.	Sub Task	Complex Lvl 1	Complex Lvl 2	Checks Required	Assign Staff
1	1	1	0	8	A
2	1	1	0	9
3	1	0	1	150	A
4	1	0	1	5
5	1	0	1	12
6	1	0	1	2
7	1	1	0	18
8	2	2	0	12
9	1	1	0	19
10	2	1	1	88	B
11	1	0	1	41
12	1	1	0	6	A
13	2	2	0	27	B
14	1	0	1	2	A
15	1	0	1	4

Table 2: Staff list and their respective targets for each criteria

Sum of all the cases assigned to a staff

		Sub Task Count	Complex Lvl 1	Complex Lvl 2	Checks Required
Staff	Avail	Target	Assigned	Target	Assigned	Target	Assigned	Target	Assigned
A	1	5	4	3	2	3	2	116	166
B	1	5	4	3	3	3	1	116	115
C	1	5	0	3	0	3	0	116	0
D	0.5	3	0	2	0	1	0	58	0

So in the above assignment so far, B's portfolio is ok, as his assigned score is close to target.

Really appreciate any help, suggestions.

Workflow

Macros

Accepted answers

AngelosPachis

Hi @Joon ,

Understood, it sounds like a problem that can be solved with an iterative macro. However, I would suggest you have a look in a tool called "optimization" tool.

What this tool will allow you to do, is maximize the number of cases assigned to each member of staff, given certain constraints. In our instance, those constraints are Sub-Task count, Complex Lvl1 and Complex Lvl 2 (Criteria 4 is optional, so we can include it as is, exclude it totally or use a slightly increased value of the max permissible value it can take).

How this tool works :

First of all, you have to define the equation that you need to maximize; Probably that's the number of cases, because nobody said no to some extra work, right? So assuming that case 1 is denoted as c1 and case 2 as c2 and so on, the equation we want to maximize for each member of staff is:

c1+c2+c3+c3+c4+....+c15 = Total number of cases assigned to Staff A ----> Max

This equation above is called the objective function. You will notice that it contains all cases (c1 to c15) because essentially, we can assign any of them to staff A. Then, when a case is assigned to staff A, that will take the value of 1, else if it is not assigned, it will take a value of 0. So our variables (c1, c2 ...) in the objective function can only take values of either 0 or 1, so they are of a binary type.

For example, assuming that staff member A is assigned 3 cases in total (cases 1, 3 and 5) then the objective function will become:

1+0+1+0+1+0+(zero for all other cases) = 3.

All the different variables, their weight to the objective function and their type (binary) shall be fed from the "O" input anchor, so for staff A

Now assigning each case to an employee, comes with some constraints; each case have a SubTask, ComplexLvl1 and Complex Lvl2 weights, that when added up for each case, should not exceed the maximum allowable values for each employee.

So, assuming that SubTask is (S), the constrain equation for SubTask would be:

S1c1+S2c2+S3c3+S4c4+.....+S15c15 <= 5 (for employee A, subtask should not exceed 5)

Again, when a case is assigned to employee A, the variable for case (c1,c2,c3...) will take the value of 1 else the value of 0. The weights for SubTask for each case (S1,S2,S3,...) have been provided from you and should be fed in the optimization tool through the A input anchor, in the following format

So following the example given earlier, if cases 1, 3 and 5 are selected for employee A, then the SubTask constraint equation will become:

1*1 + 1*0 + 1*1+1*0 +1*1+1*0 +..... +1*0 = 3 < =5 ✔

So this constraint is met and probably we can fit some more cases for employee A. The same process goes for ComplexLvl1 and ComplexLvl2, the general equations of which will be the following for employee A

CO1c1+CO2c2+CO3c3+CO4c4+.....+CO15c15 <= 3

CT1c1+CT2c2+CT3c3+CT4c4+.....+CT15c15 <= 3

If you would like, you can add an extra constrain for Checks required, which for employee A should not exceed 116.

The final step is to provide the maximum values to the model for employee member A, and those should be fed through input anchor B

Screenshot 2021-01-16 092215.jpg

The model will run and out of the "S" output anchor, you will get which cases should be picked for employee A, to maximise the number of cases assigned to him given the constraints we have defined above. So now, employee A can be assigned with cases c2, c5, c9, c11 and c12 before one of the constraints has been met. That's a total of 5 cases, and hence the objective value is 5.

Screenshot 2021-01-16 092545.jpg

If you need more info, those will come out of the D output anchor. Let's see how that looks

Focusing on the Important constraints part, we can see that given the cases assigned to employee A :

SubTask = 5, which is less than or equal to the max permissible value of 5

ComplexLvl1 = 3, which is less than or equal to the max permissible value of 3

ComplexLvl2 = 2, which is less than or equal to the max permissible value of 3, with an extra capacity of 1

ChecksRequired = 87, which is less than or equal to the max permissible value of 116, with an extra capacity of 29.

That last constraint as you said is not as important, so you can go on an increase the max permissible value or remove it totally if you like.

Now moving onwards to employee B, you can manually remove variables and constraints for cases c2, c5, c9, c11, c12 and re-run the tool. This can be automated with a macro, but it's good to have control over the whole process before automating.

Thought this way would be more efficient than trial and error methods in excel, plus the fundamentals of this method lie in mathematical optimization.

This has been by far my longest post in the community, so please take the time to read it and come back if you have any questions. You can find the workflow I used as an example attached.

Regards,

Angelos

optimization.yxmd

Joon

Hi Angelos

I'm not quite sure how to go about modifying some of the constraints.

1) How to enter the constraints if it is a range, with a lower and upper bound? (E.g. Target for Sub task is 5, but the team accept results between 4-6. The same goes for the complexity constraints.)

I tried to enter in the following in the table to anchor B for Checks required, but encounter error msg

2) In a typical work list, it will always contain 1 or 2 outlier cases (i.e. where Checks required for the case runs into 200-500). I try to assign these out first. Currently I find the (lowest) max permissible limit for Check Required via trial and error. Is it possible to set-up to find the combination that meet the constraints for Sub Task, Complex Lvl 1, Complex Lvl 2 and also with the lowest no. of Checks Required and must include the outlier (c3 in this case)?

Appreciate your advice.

Thanks and regards

Joon

All comments

AngelosPachis

Hi @Joon ,

I've read your post and I can't stop thinking of this solution. Is your ultimate goal to maximise the total number of cases per employee (and then eventually assign all cases?)

Joon

Hi AngelosPachis

Not quite, I think (especially when it comes to criteria 4). The emphasis from the team is to make it equitable as far as possible.

I try to illustrate with a sample (manual) assignment (see attached .xls)

So far, this set of assignment look somewhat accept as criteria 1, 2, 3 are very close to target. But the no. of checks required assigned to B and C are not even (difference of 30), even though their targets are the same. So the team will take the list and reshuffle as again in an attempt to make it as equitable as possible for B, C (and maybe even D).

Hope the above makes sense and answer your question.

Sample Assignment.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703