I'm working on a way to distribute caseloads to workers such that each individual worker does not exceed their caseload cap. I have eight workers: one has a cap of 125, and the rest have a cap of 250. Each worker is assigned teams which have varied numbers of clients per team, but for argument's sake we can say that we have 20 teams and they each have total clients between 50 and 130.
I'm trying to figure out the best way to allocate these teams such that each worker carries approximately equal caseloads that are slightly under their max caseload--so for the one worker who is only allotted 125, ideally she would be around 110 clients and the other workers would hover around 220 to allow for caseload fluctuations.
Is there a magical way for Excel to evaluate the teams and distribute them into mostly equal groups? I know the having different rules for the groups complicates things, so we could even just assign the one worker's caseload and just distribute the other groups according to the ~220 average and that would help out a lot. I know I can work it out on paper by trial and error and just moving things around, but I wondered of there was a quicker, automated method.
Thanks for your help.