Need help with a work problem (almost like a logic game using excel)

[aphex]

My boss is out of the office for the rest of the week and its up to me to assign the next monthly workload (aka 'the drop') while she's gone. I would like to create an excel spreadsheet to make the divvying up as easy as possible.

THE PROBLEM: There are 9 people on a team that need an even distribution of 1000 cases with 6 different scenarios (ex/ 100 of Scenario A, 300 Scenario B, and so on to equal 1000 total). I have to take into account the varying amounts of each scenario, as well as vacation time for two of the team members to lower their share accordingly. Any idea how to best accomplish this in Excel?

I created the following quick setup to help illustrate what i'm trying to accomplish.

 

[PsiStar]

There are examples of scheduling ppl via Excel on MS web site.

 

[rgwalt]

Is variety a requirement? In other words, can you give all of A to one person?

Is one unit of A equal in time/effort to one unit of B? (and so on)

You basically have 8.25 people for the month to get 1000 units of work done. So each full time person needs to do 121.2 units of work (approximately), with the part time people scaled accordingly. That gives you an even amount. Now, assuming you want an even distribution of cases among people, it should be relatively easy to set up the formula in Excel. You would want to distribute each person's work units among each scenario in proportion to how many work units required by that scenario. For example, person 1 needs to do 121.2 units of work in the month. They would spend 15% of their time on scenario A, or 18.18.

If you have a different distribution strategy, it becomes more difficult to do in Excel. I could do it in Fortran though...

 

[aphex]

Is variety a requirement? In other words, can you give all of A to one person?

Is one unit of A equal in time/effort to one unit of B? (and so on)

You basically have 8.25 people for the month to get 1000 units of work done. So each full time person needs to do 121.2 units of work (approximately), with the part time people scaled accordingly. That gives you an even amount. Now, assuming you want an even distribution of cases among people, it should be relatively easy to set up the formula in Excel. You would want to distribute each person's work units among each scenario in proportion to how many work units required by that scenario. For example, person 1 needs to do 121.2 units of work in the month. They would spend 15% of their time on scenario A, or 18.18.

If you have a different distribution strategy, it becomes more difficult to do in Excel. I could do it in Fortran though...

Variety is a requirement, the distribution has to be equal per scenario.

 

[aphex]

I came up with the following (the break down of the numbers is at the bottom) - you can't see the formulas but its all based on the number i assign to the TOTAL in each Scenario.

Does this seem right?

EDIT: When i add up each scenario, it looks like i'm losing some cases by lowering the number of decimal points. Hmmmm, any way to account for this? (Ex/ Scenario A only adds up to 147 cases distributed)

 

[coxmaster]

Do it in quarters. Lemme see if i can explain this as well as it seems to work in my head.

You essentially have 33/4 people (this accounts for the people not working full amounts)


For Scenario A
150/33=4.54

So anybody working full shift would work 4.54*4=18.2ish
The -25% person would have 4.54*3=13.62
-50% would have 4.54*2=9.08

That covers a total of 150, depending on how you decide to round stuff.


Did that make sense? Or was i just rambling

 

[aphex]

Do it in quarters. Lemme see if i can explain this as well as it seems to work in my head.

You essentially have 33/4 people (this accounts for the people not working full amounts)


For Scenario A
150/33=4.54

So anybody working full shift would work 4.54*4=18.2ish
The -25% person would have 4.54*3=13.62
-50% would have 4.54*2=9.08

That covers a total of 150, depending on how you decide to round stuff.


Did that make sense? Or was i just rambling

Ahhh, thats perfect!!! I like your way better!

 

[FleshLight]

Goalseek

/thread

 

[coxmaster]

Goalseek

/thread

Ah damn, I completely forgot about that!

Goalseek would have worked wonders!