For many people setting out on the workforce planning journey, the first port of call is spreadsheet software like Microsoft Excel. And it’s amazing what a competent user can achieve with Excel. At injixo, we are all WFM nerds and many of us cut our teeth in planning using Excel. In this post, we share some top tips for getting the most out of Excel in workforce planning. Of course, other spreadsheet software is available, and when we mention ‘Excel’, the same guidance can be adapted for other software.
Excel comes with a library of over 400 functions. It includes powerful features like pivot tables and you can even introduce automation using macros. Where do you start? There’s no substitute for a good book. We swear by Excel for Dummies and its companion volume Excel VBA Programming For Dummies.
Specific features we recommend that you master include:
This guide includes a generic template that you can adapt to your specific needs. Best of all, the guide keeps the technical setup to the minimum. You don’t need to write any macros or create any pivot tables. You will learn how to use powerful functions like SUMIFS, WEEKDAY, FORECAST, GROWTH, STDEV, and more.
The next step in the WFM process is to convert forecast volume and average handling time (AHT) into the number of required staff. The standard way to do this for inbound calls is known as Erlang C. At first glance, the Erlang formula looks pretty scary but there is good news. injixo offers a free Erlang C calculator. It doesn't require you to download anything, and it even lets you calculate staffing for 15-minute intervals for a complete day - just select Multi Interval. If you download an Erlang calculator for Excel, you will need to enable macros, which may, in turn, require permission from your IT colleagues. That's because Erlang C is an algorithm, not a formula.
Online tools for calculating staffing for other channels are harder to find. Call Centre Helper does have a calculator for handling calls, emails and web-chats and while intriguing, it is at an experimental stage.
Once you’ve got your staffing requirements, the next step is to create schedules for your staff, to match required staffing with provided staffing as consistently as possible. In principle, creating schedules in Excel is straightforward. You construct a spreadsheet with one row per employee and one column per 15 or 30-minute interval, showing 1 when the employee is present or 0 when the employee is absent. If you want to schedule employees to perform different activities at different times, you’ll need to create activity codes rather than simply recording ones and zeroes. And you’ll need to do this for each day of the planning period. That is a lot of cells to manage, so here are a few tips:
The options for optimization with Excel are limited but we recommend that you check out Solver. There’s a useful article on the Microsoft website called Using Solver to schedule your workforce.
Once you’ve produced your schedules, you need to get them to the agents and team leaders. You could save the schedules as an Excel file or even a PDF and email them. A better idea is to store them in a shared folder and share a link. That way, there’s only ‘one version of the truth’ and your colleagues will always be looking at the current version.
Don’t forget to make the files read-only and password protected. And don’t update them once published, unless you want a riot on your hands. If you do need to make changes, inform team leaders and agents first.
So you’ve published your schedules to agents and team leaders. The next step is to manage on-the-day, handling unplanned spikes, absences and other deviations from plan. Conventional wisdom tells us that doing real-time management with a spreadsheet is tough. And it is. You will have to rely on the tracking capabilities of your ACD or other routing platform, keeping multiple windows open: Forecast and schedules in the spreadsheet; real-time call data and agent status date on ACD tracking screens. Historic adherence and conformance scores are vital ingredients of agent appraisals and 1:1s. With some manual copy/paste, it is possible to generate these reports. Call Centre Helper has published a manual adherence reporting tool.
It’s very satisfying to build your own planning spreadsheet from the ground up. But that can take a lot of time. There are plenty of sources of inspiration on the web. Just search in Google for what you need and you’re likely to find some ready-built components that will save you time. We recommend the Excel Forum and Call Centre Helper.
Doing workforce planning with a spreadsheet is a lot of work. The last thing you need is for that work to be lost. Save a backup each time you create a new plan. Password-protect the files to prevent your carefully created formulas and macros from becoming corrupted. When sharing files, make sure to give read-only access except to colleagues who you really trust.
Many planners who start with Excel eventually move on to a professional WFM application. But that doesn’t mean that you should never need or want to use a spreadsheet again. You need a WFM application that can live in harmony with spreadsheets. For example: