Excel solution for managing time

[ChildOKing]

Perhaps this is a bit more than a spreadsheet can do, but here's my situation...

I have a team of about 25 supervisors, who head 500 employees. I'm trying to analyze the implications of starting an optional four 10-hour day work shifts for 8 of the supervisors, leaving the others on 8-hour work days. Our coverage goal is to have 14 or less associates per supervisor at all times during the day. Our ours of operation are from 6:45 am until 8:00 pm. So... is there a way to set up a spreadsheet that will automatically look at each supervisor's shift, determine how many supervisors would be in the building at each half-hour interval of the day, taking into account that those who would be working 10-hour shifts would each have a different day off each week... So far all I've been able to do is attempt to look at this on paper, by hand, but even that gets quite complicated. Does anyone have any idea how to approach automating this analysis? Sorry if I've confused you - I've been there for quite some time!

Thanks in advance.

 

[sweater_vests_rock]

ChildOfKing.

I'm trying to understand what you're saying.

1) How long do associates work (I presume you don't make them work 13 hour days!) Do they come in at the same time as their supervisor? Help me understand their flow.

2) Do the supervisors currently work 5, take 2 off, work 5, take 2 off, etc? Will the 4-day supervisors do the same (work 4, take 3 off, etc.)

 

[ChildOKing]

Sorry about that, let me try to explain a bit better...

I currently have a spreadsheet that takes the number of associates in the building at each half-hour interval of the workday, and divides that by the number of supervisors to let me know the level of supervisor coverage. The problem comes in when changing some of the supervisors' shifts to the ten-hour days (more on that below...). Because at that point, each of the ten-hour supervisors will have a certain day off every week (for the sake of simplicity we'll say there will be 5 ten-hour shifts instead of my original statement in my first post) - so that will have an impact on the actual number of supervisors in the building since some will be there longer than others and one will be off each day of the week. That's where I run into a problem - coming up with a solution that will adjust the associates-to-supervisor ratios based on this information.

To answer your questions, no 13-hour days but yes, the supervisors presently work Mon - Fri, eight-hour days. Because I have the information that I mentioned in the spreadsheet above, I'm hoping the schedules of each individual associate won't be needed, since we already know the present-day ratio of associates to supervisor at each half-hour of the day.

I hope that helped clarify what I'm needing... Thanks!

 

[sweater_vests_rock]

ChildOfKing:

To calculate these coverage ratios, it seems to me you need to know the number of supervisors and associates working each half-hour. If you can get this data, you can set up a spreadsheet like below, which will at least give you a table to understand the data better. Unfortunately, it doesn't (yet) talk about how to meet your criteria because I don't have the info (and possibly intelligence) to automate that.

Here is what I'm thinking

Row 1, Col 1 = Time
Row 1, Col 2 = # of Associates
Row 1, Col 3-27 = Supervisors (S1, S2, ..., S25)
Row 1, Col 28 = Coverage Ratio

Row 2-28, Col 1 = 30 Min Intervals (645-714, 715-745, ...1945-2000)
Row 2-28, Col 2 = # of Associates working over this interval (A1, A2, ..., A25)
Row 2-28, Col 3-27 = WORK/OFF if Sx is working (S1 = "WORK" if S1 is working, S1 = "OFF" if S1 is not working)
Row 2-28, Col 28 = Coverage Ratio (= Ax / countif(S1:S25, "WORK"))

Looking at Col 28 then, you should see the coverage ratio as it is now for a single day (call it Monday).

To understand how changing the shift length will affect ONE day, you can simply change the 5 supervisors' (let them be S1-S5) WORK/OFF value -- so change all entries of S1 to "OFF" and lengthen S2-S5 "WORK" entries by 6 rows. The coverage ratio will automatically adjust to reflect these changes.

Hope that helps. Ben.