Calculating staff levels

[William]

I am trying to use Excel to determine my staffing needs for a given work day.
Here is what I have for data:

80 hours of work needs to be done per day.
I have one shift that works 11 hours (this is total hours available to work,
I already took out breaks, lunch, etc.), the maximum I can have on this shift
in a day is 4 people.
I have another shift that works 8 hours (I already took out breaks, lunch,
etc.).

I would like to know how many staff I need on the 11 hour shift and how many
on the 8 hour shift to do 80 hours of work per day.

I would also like to be able to change the 80 hours to whatever I want (as
the work increases) and have the formulas automatically recalculate (so I
don't have to change the 80 in formulas, etc.)

If it was one shift of 8 hours it would be obvious to me, I would need 10
people. But having these odd shifts of 11 hours and 8 hours and limited
people on the 11 hour shift is throwing me and don't know how to do it in
Excel as one formula or best way to break this down. I don't want to just
assume all 4 people will work 11 hours. I would like to be able to get to the
data in multiple ways. For example, it would be nice if I could reduce the 11
hour shift to just 2 people, but then how many 8 hour shifters would I need
to increase to cover the work.

 

[Joel]

It would be nice to be able to just push a button and get the results you are
looking for. This is a complex problems that the Greeks weren't able ot
solve 2000 years ago and requires complicated programs to get the best
results. These problems are called packing problems. The Greeks going into
battle had to pack up the chariots with supplies and need to know the best
way to load up the chariots when they had different size items to put in the
chariots. You aee they were having problem with putting too much in each
chariot and then having the chariot move too slow or tipping over.

You have the same problem with trying to get the most amount of work done
with the least amout or labors. My best suggestion is to buy a software
package that solves these type problems.

 

[Peter T]

Unless I'm missing something this looks pretty straightforward -
My understanding is:
between 0-4 people can work 11 hour shifts
unlimited number of people can work 8 hour shifts

type numbers in A1, A4, B1:F1 as follows
A1: 11
A4 8
B1:F1 0 to 4

paste these formulas in B2:B5
=$A$1*B1
=80-B2
=IF(MOD(B3,$A$4),INT(B3/$A$4)+1,B3/$A$4)
=$A$4*B4-B3

copy B1:B5 to B1:F5

you should end up with these in A1:F5

11 0 1 2 3 4
~ 0 11 22 33 44
~ 80 69 58 47 36
8 10 9 8 6 5
~ 0 3 6 1 4


row 5 shows wasted hours

The most efficient is 8x10,
Next most efficient is 11x3 + 8x6 giving just one wasted hour

Rearrange and with labels to suit

Regards,
Peter T

PS done in a hurry - pls double check I haven't made any obvious mistakes!

 

[Peter T]

I forgot you said you want to be able to change the 80 as hours increase.
In A3 type 80 (this will be the total hours input cell)
B3 =$A$3-B2
copy B3 to B3:F3

Change 80 to 100 and F5 should show zero remainder (wasted) hours with 11x4
+ 8x7

Peter T

 

[William]

Thank you very much, that was very helpful.

Unless I'm missing something this looks pretty straightforward -
My understanding is:
between 0-4 people can work 11 hour shifts
unlimited number of people can work 8 hour shifts

type numbers in A1, A4, B1:F1 as follows
A1: 11
A4 8
B1:F1 0 to 4

paste these formulas in B2:B5
=$A$1*B1
=80-B2
=IF(MOD(B3,$A$4),INT(B3/$A$4)+1,B3/$A$4)
=$A$4*B4-B3

copy B1:B5 to B1:F5

you should end up with these in A1:F5

11 0 1 2 3 4
~ 0 11 22 33 44
~ 80 69 58 47 36
8 10 9 8 6 5
~ 0 3 6 1 4


row 5 shows wasted hours

The most efficient is 8x10,
Next most efficient is 11x3 + 8x6 giving just one wasted hour

Rearrange and with labels to suit

Regards,
Peter T

PS done in a hurry - pls double check I haven't made any obvious mistakes!

 

[William]

Peter, I noticed you said unlimited number of people can work 8 hours. What
if the maximum number of people I have is 10 (which can be a max of 4 on the
11 hour shift, so 6 would be available for the 8 hour. If I go 3 on 11, 7
available for 8 hour shift, etc.)

How would I change your formulas to reflect this?? My main goal is to tell
me what my staffing level should be based on the hours of work needed and the
two shifts. However, it would benefit me to know with the staff I have (10)
what can be done and what is left over for them to do other things.

Thanks in advance.

 

[Peter T]

Maybe add another row
A6, 10 qty of available staff
B6, =B1+B4
copy B6 to B6:F6
this row shows the qty of staff not used on the day, say available for other
duties. If the figure is -ve you don't have enough staff.

You say you have 10 staff, of whom 4 can work 8 or 11 hours and 6 who can
work 8 hours only. Clearly you are limited to maximum 4x11 + 6x8 = 92 hours


You'll probably want to rearrange and adapt what I've suggested to make more
logical for your needs. Include some =IF() formulas perhaps.

Regards,
Peter T

 

[Martin Brown]

Peter, I noticed you said unlimited number of people can work 8 hours. What
if the maximum number of people I have is 10 (which can be a max of 4 on the
11 hour shift, so 6 would be available for the 8 hour. If I go 3 on 11, 7
available for 8 hour shift, etc.)

How would I change your formulas to reflect this?? My main goal is to tell
me what my staffing level should be based on the hours of work needed and the
two shifts. However, it would benefit me to know with the staff I have (10)
what can be done and what is left over for them to do other things.

In some ways having a fixed number of staff to employ makes it easier.
It is obvious that with them all on shift 1 you get 10x8 = 80
(and can close shift 2 - saving heat/light costs)

It is also obvious that for each one you move to the 11 hour shift #2
you get an extra 3 hours work from the poor devil.

So for N people on shift #2 you get total man hours 80+3N.

So the maximum range of outputs is 80, 83, 86, 89, 92 with 10 employed
and a maximum of 4 allowed to move shifts.

Algebraically it is easier.

N employees
N-M on shift 1 working P hours = (N-M)*P
M on shift 2 working Q hours = M*Q

TOTAL = (N-M)*P+M*Q = N*P + M*(Q-P)

Regards,
Martin Brown

 

[William]

Thanks everyone for the help, much appreciated.

In some ways having a fixed number of staff to employ makes it easier.
It is obvious that with them all on shift 1 you get 10x8 = 80
(and can close shift 2 - saving heat/light costs)

It is also obvious that for each one you move to the 11 hour shift #2
you get an extra 3 hours work from the poor devil.

So for N people on shift #2 you get total man hours 80+3N.

So the maximum range of outputs is 80, 83, 86, 89, 92 with 10 employed
and a maximum of 4 allowed to move shifts.

Algebraically it is easier.

N employees
N-M on shift 1 working P hours = (N-M)*P
M on shift 2 working Q hours = M*Q

TOTAL = (N-M)*P+M*Q = N*P + M*(Q-P)

Regards,
Martin Brown