9 Top Tips for Workforce Planning in Excel

Chris Dealy of injixo shares his favourite advice for workforce planning in Excel.

1. First, Become a Power User

Sounds obvious, doesn’t it? 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 functions we recommend that you master include:

• Keyboard shortcuts, which can save your hours of manual effort every month
• 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

2. Don’t Reinvent 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 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. We’ll introduce some sources you can trust in the following sections.

3. Download the Ultimate Guide to Forecasting

The first step in the WFM process is forecasting. injixo has published the free Ultimate Guide to Forecasting with Excel. This shows you proven ways to collect and analyze historical data, predict future workload, apply business intelligence and report on accuracy.

The 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.

4. Use an 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.

Call Centre Helper offers a free Excel Erlang C calculator that you can download. Since Erlang C is an algorithm, not a formula, you will need to enable macros, which may, in turn, require permission from your IT colleagues.

Online tools for calculating staffing for other channels are harder to find. Call Centre Helper does have a calculator for handling calls, emails and webchats and, while intriguing, it is at an experimental stage.

It’s a good idea to do an independent test of your spreadsheet Erlang calculations. Check out the injixo Erlang app in the App Store or Google Play.

With this app, you can instantly find the required staffing by entering volume, AHT and your service level goal. You can even do reverse Erlang calculations – to find the service level you’ll achieve with a given volume, AHT and staffing level, for example.

5. Keep Scheduling Simple

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.

6. 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 export 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 if you don’t want a riot. If you do need to make changes, inform team leaders and agents first.

7. 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.

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 avoid 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.

---

This post has been re-published by kind permission of Peopleware - view the original article.

Call Centre Helper is not responsible for the content of these guest blog posts. The opinions expressed in this article are those of the author, and do not necessarily reflect those of Call Centre Helper.