Scheduling Breaks/ Lunches In An Excel Report

Scheduling Breaks/ Lunches In An Excel Report

Hello Experts,

My center is ramping up from 20 agents to 100 agents for a 1 day special launch. We dont use a WFM.

I was wondering if anyone had an excel report that will schedule all breaks/ lunches for 100 agents while meeting our SLA target.

Question asked by Danny

At 100 Agents I Would Suggest WFM Software

By the time that you reach 100 agents I would say that you are probably outside of the capabilities of a spreadsheet and should be considering a WFM system.

But if it is a one off then you could do it in a spreadsheet.

Basically you need 48 columns – 1 for every half hour and 100 rows (one for every advisor). When the advisor is present then you put a 1 in the appropriate box. When they are one break then you put in a 0. Al the bottom of the 100 rows you add in how many people are there for each timeslot. Yu then have a staffing profile. You then just need to compare that with expected call volume coming out of an Erlang calculator.

For an Erlang calculator

https://www.callcentretools.com/tools/erlang-calculator/

or for an Excel version

New Excel Based Erlang Calculator – with Maximum Occupancy

With thanks to Jonty

14 Staff Managing Lunch Breaks

I have a team of 14 advisors. Lunch breaks and dse breaks are challenging. Lunch breaks due to cover over the lunch can be busy. How can I make this work better.

With thanks to Wendy

How to Calculate the Quantity of Shifts Should Be Scheduled to Create the Best Coverage Situation With A Shrinkage of 0%.

A – Please add in Table B the quantity of shifts you would schedule to create the best coverage situation with a shrinkage of 0%.

B – Please add in Table B the quantity of shifts you would schedule to create the best coverage situation with an estimated shrinkage of 10% of all your scheduled heads.

C – Please share your reasons for your decisions in the Explanation field.

Time FTE Requirement Shift Duration Lunch Time A – Quantity B – Quantity

08:00 6 08:00 – 17:00 9h 1h

08:30 8 08:00 – 16:00 8h 1h

09:00 11 08:00 – 14:30 6.5h 0.5h

09:30 14 08:00 – 12:00 4h 0h

10:00 15 09:00 – 18:00 9h 1h

10:30 15 09:00 – 17:00 8h 1h

11:00 18 09:00 – 15:30 6.5h 0.5h

11:30 17 09:00 – 13:00 4h 0h

12:00 22 10:00 – 19:00 9h 1h

12:30 17 10:00 – 18:00 8h 1h

13:00 16 10:00 – 16:30 6.5h 0.5h

13:30 14 10:00 – 14:00 4h 0h

14:00 16 11:00 – 20:00 9h 1h

14:30 13 11:00 – 19:00 8h 1h

15:00 19 11:00 – 17:30 6.5h 0.5h

15:30 19 11:00 – 15:00 4h 0h

16:00 22 12:00 – 21:00 9h 1h

16:30 17 12:00 – 20:00 8h 1h

17:00 16 12:00 – 18:30 6.5h 0.5h

17:30 14 12:00 – 16:00 4h 0h

18:00 12 13:00 – 21:00 8h 1h

18:30 10 13:00 – 19:30 6.5h 0.5h

19:00 8 13:00 – 17:00 4h

19:30 7 14:30 – 21:00 6.5h 0.5h

20:00 7 14:00 – 18:00 4h 0h

20:30 6 15:00 – 19:00 4h 0h

16:00 – 20:00 4h 0h

17:00 – 21:00 4h 0h

With thanks to Tawfik

You Need to Create A Schedule

Hi Tawfik

The only way to do this is to create a schedule. You could do this in excel

You list the individual shifts in a column and then the times as the rows.

At the bottom you just add up the number of staff that you have.

So you use Time and FTE to make the demand requirement.

You then start your shifts at the individual cells.

To get a rough example you could look at the scheduling part of the Erlang calculator.

With thanks to Jonty

Author: Jonty Pearce
Reviewed by: Robyn Coppell