How to model process resource requirements in Excel?

December 7, 2017 6:27 PM

Every day, 1500 widgets arrive for packaging. They're not all the same, and they take different amounts of time to package. Historical data shows that by day 5, 75 per cent of widgets that arrived on day 1 have been packaged. By day 10, a further 20 per cent have been packaged. By day 30, the remaining 5 per cent of the widgets that arrived on day 1 have been packaged.

A person can package 200 widgets per day. How many people are needed to ensure there are never any widgets unpackaged for more than 30 days; what's the highest number of unpackaged widgets that will ever be in stock; and how did you work that out (for example, in Excel)?

What if you were happy for that '75 per cent packaged' mark to take 10 days to reach, or 20, but everything arriving on day 1 still had to be packaged by day 30?

Does this sort of problem (stuff is coming in continuously, you have flexibility about how long it takes to take care of it, but there's a hard boundary on how much stuff there can ever be to do at any one time) have a name?

posted by obiwanwasabi to Work & Money (14 answers total)

I'm a bit confused.

If you've got 1500 coming in every day, don't you just need enough staff to clear them on that day?

Because if you clear less than 1500 per day, and there are 1500 more arriving tomorrow, you're going to be in trouble as every day you'll fall further and further behind.

So in this case you need an average of 7.5 employees per day (at 200 units per day = 1500). Any less and you'll build up a backlog.
posted by robcorr at 6:50 PM on December 7, 2017 [3 favorites]

Best answer: The idea that a person can package 200 widgets per day conflicts with the notion that the widgets take varying amounts of time to package.
posted by jon1270 at 6:53 PM on December 7, 2017

Response by poster: I'm a bit confused.

I think I'm the one who's confused, so please, join me on the Confused Express and I'll try to do some more confused explaining.

If you've got 1500 coming in every day, don't you just need enough staff to clear them on that day?

If 7.5 people can clear it in a day, then 3.25 people can clear it in two days, and one and a bit people can clear it in four days, and so on. I don't actually need the stuff cleared that fast. I have thirty days.

The idea that a person can package 200 widgets per day conflicts with the notion that the widgets take varying amounts of time to package.

You're...absolutely right. I guess I mean that, each day I can see that each person has packaged around 200 widgets, though these are of different types.

I think I may be grossly overcomplicating this.
posted by obiwanwasabi at 7:10 PM on December 7, 2017

If you want to over complicate, I've seen this type of optimization problem modelled with linear programming. it sounds fun though!
posted by Valancy Rachel at 7:20 PM on December 7, 2017

Best answer: I don't actually need the stuff cleared that fast. I have thirty days.

But if you have another 1500 coming in every day, then you don't have thirty days, you have one day.
posted by robcorr at 7:24 PM on December 7, 2017 [1 favorite]

Response by poster: Yep, that's it. (A nice MeFite named clew also sent me a spreadsheet that shows exactly that, and also said you're right, which you are).

I have no idea why my brain followed such a tortuous route on this one. It's obvious now that somebody else has said it. Thanks for saving me a lot of time.
posted by obiwanwasabi at 7:28 PM on December 7, 2017

FWIW, it's not a silly thought process. If the deliveries were coming in less frequently (perhaps irregularly), or if each delivery had a different average time to process the contents, it would be well worth doing this exercise. It's only because the variables are so consistent that it turns out to be overkill - but real processes aren't always so neat.

:-)
posted by robcorr at 7:33 PM on December 7, 2017 [3 favorites]

Response by poster: It's not quite as neat as I've made it out to be, but it's neat enough that my 'let's just take a bit longer to box each widget and we'll totally save on packaging staff and there's a magic number of widgets on hand that we can carry without ever blowing the 30 day limit' idea seem pretty dense!
posted by obiwanwasabi at 8:02 PM on December 7, 2017

There also might be other ways of improving the workflow which would affect calculations, like “on Fridays we focus on all the 5-10 percenters still in stock.” That would improve the time from receipt to completion, but it would mean that there’d be more stock that needs to be stored come Monday, because Friday’s 75 percenters are still hanging around. Or you have staff who are 5-10 percenter specialists, who process fewer units per day but keep the turnaround numbers low. (I’m assuming that the 5-10 percenters are more difficult to package, but if that’s not the case it might be more of an inventory system improvement- requiring solution.
posted by tchemgrrl at 3:17 AM on December 8, 2017

Lots of similar themes and examples in The Goal.
posted by Wild_Eep at 5:19 AM on December 8, 2017

The name of the discipline where they study problems like this is Management Science, or perhaps the old school name, Operations Research. Off the top of my head, I don't recall a name for this particular type of process problem.

As a first step, noting that if the inflow is constant, the labor requirement will be constant is good, but I think the real world is probably more complicated. It's probably not exactly 1500 widgets every day, and probably the mix between the easy-to-package and the hard-to-package varies. Given that you have maybe 15,000 widgets in the shop at any one time, the processing must have some structure, e.g. separate processing lines.

I'm sure you give special attention to the gotta-do-this-week and gotta-do-today stuff, and it seems likely to me that the amounts vary, so some workers would be switched from assignment to assignment.

If I were to try to tackle this in Excel, I think I'd start with a sort of weekly labor budget approach. For each processing line, or widget type, or whatever is convenient, start with some columns for Monday with the #items in backlog, #items added, #items processed. You want enough info to be able to calculate the backlog for Tuesday. Continue for the rest of the week. You can fill out the cells with estimates and replace them with actuals as the week goes on. You could have a column for #workers, and use it to estimate the #processed. You would be able to see where a backlog is building up, or dwindling, and determine if you need to move some people around.
posted by SemiSalt at 9:16 AM on December 8, 2017

It'll depend a bit on your exact context, but this kind of problem looks a lot like the sorts of things dealt with in Queuing Theory (which itself is a topic that often falls under the larger umbrella of Operations Research).

Now, you've formulated the problem in a deterministic manner, i.e.: exactly 1,500 widgets arrive each day and each worker can process 200 widgets per day, in which case your question about building up backlogs is more or less moot. If inflow is less than outflow, then there isn't a backlog; if inflow is greater than outflow, the backlog will increase without end.

However, once you introduce some uncertainty into the system (e.g.: maybe some days 1,000 widgets arrive and others 1,700; or maybe sometimes a worker can process 110 widgets in a day but others 240), then the situation becomes more interesting. Namely, although on average your outflow may be greater than or equal to your inflow, there is some probability that you will build up some backlog. These are the types of questions that come up in the field of queuing theory.
posted by mhum at 2:18 PM on December 8, 2017 [2 favorites]

Response by poster: Thanks all. There is quite a bit of variability, but our management information is pretty poor so it's hard to detect whether these are predictable variations, and something we could adjust for with scheduling. Ditto with how much individuals can actually process - we're stuck with a cumbersome mean / 'typical case' stats at the moment. I think the best we can do for now is to resource to clear daily incoming widgets, then watch the daily stats to see if anybody has nothing to do.
posted by obiwanwasabi at 5:46 PM on December 10, 2017

As I look at it, there are three phases of management science. The first is simply capturing the data on what actually happening. The usual management reporting is a good first step. The second phase is the application of statistics and modeling, working with the data you have. The third is doing experiments to get better data. You have to start at the beginning with data capture which, as you describe, can be difficult. It's your chance to get creative!
posted by SemiSalt at 6:23 PM on December 10, 2017