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.

  • This isn't really a computer hardware or software problem, it's a business problem. You need to figure out how you want to allocate the workload. Once you do that and try to implement it in Excel, people here can help you with Excel problems you encounter. – fixer1234 Nov 19 '15 at 1:00

Alright so what you are explaining is very similar to an age old problem in the manufacturing and woodworking industry’s. It’s called the stock cutting problem and has been around for quite some time. Basically the problem is, I have to cut certain qty’s and lengths of say wood out of certain lengths of wood. What’s the best way to divvy up my cutting so that I have the least amount wasted material in the end. There’s a few differences of course, in your case you are looking to divvy up workloads instead of cutting lengths but the principle is essentially the same. The problem is actually quite complex, if you google Cutting optimization in excel you'll see some entry's but really I haven't found an excel based answer yet that's easy to implement.

That all being said seeing as this is SuperUser not just an excel only forum I would recommend you look into some other software that’s designed for stock cutting calculation and just input your info instead. For example if it asks how much “stock” material you have, you would have 7 pieces at 220 units (this allows for the fluctuations you spoke about) and one piece at 125 units. Then it asks for what cuts you need, you would enter your 20 different teams total client numbers. For example if team one has 90 clients, then that’s 1 cut at 90 units, team 2 has 75 clients then that’s another cut at 75 units and so on.

Now doing it this way will get you maximized use of your “stock” so sometimes it would calculate in a way that would leave one worker with a much smaller load but you can manually fix that if need be. Check out the screen shot I have here:

Image Example of How the stock cutter would work (Click on image for larger view)

That shows two stock types in the bottom left corner, shows the types of "cuts" I want or in your case the size of the teams in the top left column and on the right side shows how the program has divided everything.

There are many programs that do this, what I used is: Cutting Optimization Pro

Hopefully something I've written here will help you.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.