Who's bored and likes a spreadsheet/calculation question? My brain is tired.

[Avondklok]

I have a team of 18 people. They will be paid 40 hours per week each and can work 8 hours per day. They need to cover a help desk 24/7. Most of the work is done in normal business hours 8 - 6. I need approx. 2 people to cover overnight 6pm - 8am each night. 3 people on a Saturday and 2 on a Sunday 8-6. The rest would be working weekday office hours. There is a day time rate and a 24/7 rate that covers nights and weekends.

Now fortunately I don't have to work out the shift rota, or care which people are doing what hours. This is a financial exercise. What I have to calculate is how much this would cost per week, and build a spreadsheet to show this. In the simplest possible way. Do I just calculate the number of day rate hours and the number of 24/7 hours per week and try to work out the relative proportions of staff working, or is there another way?

I'd do three columns.
First column, list the shift e.g 8 - 5 pm
Second column, list or calculate the number of hours in that shift
Third column, list the $ rate per hour (standard or weekday)
Sumproduct of the second and third columns gives the cost

Given the day shift and night shifts don’t divide by 8 you have an issue here because you’ll presumably need people working within and outside your daytime hours. So you can’t calculate the costs until you have the rota worked out.

Do you need them to overlap for handovers as well? What about annual leave?

I won't ever know the rota, the service provider is responsible for covering the hours paid for. I don't care how they do it, but we pay for the hours worked. Holiday I will take into account in the annual calculation as I know how much holiday they get. So I pay eg 47 weeks per year per head. Holiday pay is included in hourly rate I hasten to add.

No over lap necessary. I don't have to match the working hours to the shift. I just need to estimate overall what it will cost per week at least roughly. I can't do it exactly I know. In terms of comparing one company with another.

So I would do:

2147 (nights) = 196 hours

2101 (Sundays) = 20 hours

3101 Saturdays = 30 hours

That gives you the total 24/7 rate hours, so 246

Then if you have 18 people working 40 hours a week that's 720 hours, of which 246 will be at 24/7 rate. So the remaining 474 hours will be at the day rate, so an average of 9 and a bit people online on weekday day times.

Then you just multiply up with the hourly rates.

In that case your way is probably the easiest.

Number of hours at day rate and number at night/weekend rate.

You have 18 people x 40 hours of work a week total (assuming you don't need to factor in anything about holidays) = 720 hours total

You have the following at the higher rate:
Saturday 3 people x 10 hours = 30 hours
Sunday 2 people x 10 hours = 20 hours
Nights 7 nights x 2 people x 14 hours = 196 hours

Total at higher rate = 246 hours

Total at day time rate = 720-246= 474 hours

40 hours x 18 people = 720 hours total.

Night shift = 14 hours x 7 nights x 2 people) = 196 hours paid at the 24/7 rate.

Weekends:
Saturday = 10 hours x 3 people = 30 hours at 24/7 rate.
Sunday = 10 hours x 2 people = 20 hours at 24/7 rate.

Sub Total 246 hours at 24/7 rate.

Remainder
720 - 246 = 474 hours at standard rate.

Yay! I think you have it. So now I just have to put this in a formula where I can change the number of people in each "slot"?

Exactly right. I'd probably also make the hours in a slot a variable too, so if things change you can rework it easily.

Yes. I have done that too!

Thank you so much! My head was just not grasping it tonight.