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.
1. Master these 6 core functions
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:
- Date and time formats that are built-in; you don’t need to create your own
- The TEXT function, which consistently determines numeric values from cells with disparate formatting
- SUMIFS, COUNTIFS and the various LOOKUP functions to sift through the mountains of data you’ll be processing
- Pivot tables, which enable quicker analysis when amalgamating values
- Solver, for basic optimization
- Keyboard shortcuts, which can save you hours of manual effort every month
2. Get the Ultimate Guide to Excel Forecasting
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.
Are you looking to create and optimize your call center workload forecast in Excel?
The guide shows proven ways to collect and analyze historical data, predict future workload, apply business intelligence and report on accuracy.
3. Use this Erlang calculator
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.
4. Apply these four scheduling methods
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:
- Create working time templates or ‘day models’ and use formulas to determine the intervals in which the employee is present.
- To minimize complication, don’t attempt to consolidate planning for all skills or activities onto one sheet. Have one sheet per activity.
- Use some simple formulas to reveal your coverage (under/over - staffing) by interval and across the day and week. Use conditional formatting and charts to reveal how to improve matters.
-
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.
5. Shared documents are your friend
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.
6. You can manage in real-time - manually
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.
7. Don't re-invent the wheel
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.
8. Keep it safe
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.
9. Excel lives on
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:
- Good WFM systems let you export reports. Excel is brilliant for producing quick and dirty custom reports, quickly responding to the demands of the operation or clients.
- Good WFM systems have an API. You should be able to use Excel to access data using the API.
- Good WFM systems let you import forecasts, for example, to plan for a new product line or a new client if your business is outsourcing. You’ll almost certainly have to normalize the raw data before importing and again Excel comes into its own.
Did you find the article interesting and would like to share it with your colleagues? Download the article as a PDF.
