How Can I Schedule Breaks and Lunches In An Excel Report?

Scheduling lunches and breaks effectively is crucial for meeting inbound call demand and preventing long wait times. But how do you balance staffing needs during these periods?

Danny asked our Community of industry professions for insights on how to schedule breaks and lunches in an Excel report:

My centre is ramping up from 20 agents to 100 agents for a 1 day special launch. We don’t 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.

WFM Software is Best for 100 Agents – For a One Off You Could Use Excel

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.

Creating a Staffing Schedule in Excel

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. At the bottom of the 100 rows you add in how many people are there for each timeslot. You then have a staffing profile. You then just need to compare that with expected call volume coming out of an Erlang calculator.

Use our free Erlang calculator or you can use this free Excel version

So if you decide to go ahead with Excel, here’s a simplified process:

1. List all agents in rows and split the day into time slots (in columns, typically 30-minute increments).
2. Assign breaks and lunches in intervals where call volumes are typically lower. Be mindful of staggered times to maintain consistent coverage.
3. Add up staff availability per time slot at the bottom of your Excel sheet to ensure you have the required number of agents available at any given time.

This manual process can be time-consuming, but it will work for a one-day event.

I’ve put together a simple spreadsheet to get you started

Contributed by: Jonty

Optimizing Coverage While Scheduling Breaks and Lunches

To optimize coverage during peak hours, consider adjusting shift start times or staggering break times to avoid overlap during busy periods. When planning shifts, aim for the following:

Use Shrinkage Calculations

Factor in shrinkage (such as breaks, lunch, or absenteeism) when creating your staffing plan. For example, with a 10% shrinkage rate, you’ll need extra agents to maintain SLA.

Shift Durations

Schedule shifts of varying lengths to ensure coverage during peak times. Shorter shifts can be more flexible for covering gaps created by break times.

Erlang Calculations

Use an Erlang calculator to predict call volumes and adjust the number of agents needed at any given time.

Contributed by: Karl

Consider Long-Term Growth

If your contact centre continues to grow beyond 100 agents, a Workforce Management (WFM) system becomes essential.

These systems offer automated scheduling, forecasting, and real-time monitoring, ensuring you meet SLAs more efficiently. While Excel can work for a smaller or one-time need, transitioning to WFM will save significant time and ensure better accuracy.

Contributed by: Clive

How to Calculate the Number of Shifts Required for Optimal Coverage with 0% and 10% Shrinkage

I’m looking for assistance in calculating the number of shifts needed to ensure optimal coverage for my team under two scenarios: 0% shrinkage and 10% shrinkage. Below are the details of the staffing requirements, shift durations, and break times.

A. Shift Coverage with 0% Shrinkage

Could you please provide the number of shifts required to achieve optimal coverage with 0% shrinkage based on the following table?

B. Shift Coverage with 10% Shrinkage

Additionally, could you provide the number of shifts needed to cover the same schedule, but with an estimated 10% shrinkage applied to all scheduled agents?

C. Explanation of Shift Decisions

For both scenarios (0% and 10% shrinkage), please share your reasoning behind the shift quantities you recommend.

Staffing Requirements and Shifts Table:

Contributed by: Tawfik

You Need to Create A Schedule

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.

Contributed by: Jonty

If you are looking for more advice to improve your call centre staffing, read these articles next:

• How to Calculate Call Centre Agent Utilisation – the Formula
• How to Calculate Occupancy in the Contact Centre
• What is Call Centre Shrinkage and How to Calculate It?

Author: Jonty Pearce
Reviewed by: Robyn Coppell