8 Questions to Ask When Contact Centre Planning With Excel

Ravi Shivnani of injixo puts forward some key questions on planning with Excel to make the life of a resource planner easier.

Excel is still the tool of choice for many contact centre planners when it comes to staff planning. It’s often used to create workload forecasts and staff rosters, especially by small to medium-sized contact centres.

Despite a vast choice of workforce management (WFM) solutions available on the market, many businesses still rely on their spreadsheets to get the job done.

Be it in coexistence with a professional WFM tool or simply as result of long-standing habit.

Most of the time, however, it’s also because of a lack of management buy-in to migrate to a WFM software. Reason enough for us to put together a selection of a few helpful FAQs on planning with Excel to make your life as a planner easier.

1. How Can I Schedule Breaks, Meetings, or Training Within a Shift?

Make sure to break down your forecast into intervals. To schedule breaks, you’d typically consider 15 to 30 minutes intervals as break length.

Next, check your initial schedule and compare scheduled staff with staff needed and pay attention to:

• periods of overstaffing
• periods of  understaffing

Preferably, you’d want to schedule breaks especially in less short-staffed periods as the impact on your service level will be much lower.

2. What’s the Better Practice to Follow in Forecasting Call Volume – Calls Offered or Calls Handled? Also, Do I Need to Factor in Abandoned Calls?

In general terms, it’s always a good practice to use the number of calls offered per interval to project future workload. However, when you experience extreme abandon rates, you’d want to consider adjusting this number to a value closer to the regular run rate (calls handled) for your forecast.

Let’s assume that the typical abandon rate is around 5%. One day you’d run into a situation where the volume of calls offered equals 100 while 20% of these calls are abandoned after a very short period of time.

In that case, we’d recommend to reduce this figure to the number of calls actually handled by your agents in order to get a more accurate prediction of handled future workload.

However, always remember to save the original data. A temporary issue on one day could recur and turn into a trend in a few weeks’ time.

3. How Do I Forecast Calls for a Busy Season, Considering Increased Contact Volumes Over the Years?

You’ve most likely collected historical data in the past showing the patterns of call volume and handle time during holiday seasons or a particular marketing campaign.

Make sure to leverage those insights and use them as a baseline to fuel your forecast. Also, take a look at the impact that particular season has had on your staffing.

Based on that, you can make manual adjustments to your projection or even increase the forecasted workload by the percentage of deviation to your projections under ‘normal conditions’ that you’ve seen in the past.

4. How Can I Forecast Abandoned Calls When I’m Not Using a WFM tool?

Given that you’d be using an Erlang calculator that supports the calculation of abandoned calls, you can simply enter the number of calls offered, AHT and staff available to get there. Keep in mind, though, the results are only indications.

Unlike some of the more sophisticated WFM solutions around, basic Erlang C calculators fall short on providing intelligent simulations while taking static scenarios instead.

Remember, this will merely provide approximate equations in the add-on.

5. How Can I Calculate Calls to a Particular Call Driver/Event in Excel?

First, create rows or columns – whichever you prefer – for each call driver (such as product launches and marketing campaigns, seasonal trends and holidays, external factors such as traffic or weather forecast, etc.).

In long-term planning models, this will especially simplify your work as you can adjust different call drivers in one place to automatically update the number of calls for the required time intervals and adjust your volume calculation going forward.

6. How Can I Create a Formula That Works Out Anticipated Call Volumes to Be Expected From New (More Demanding) Customers? And How Does That Work With Volumes Coming From Older (Less Needy) Customers?

Sorry to break it to you, but this will be everything but a short equation. Because if you’re going to forecast 3 or more months into the future, you’re not going to get past a large data table. Perhaps you might want to look into a pivot table or filter by customer to get a grip on the large quantity of data.

Data brings intelligence and enables you to analyse past behaviour of both high-touch and low-touch customers to get a better understanding of their contact behaviour.

Also, it can give you an impression of how the data might be affected by different factors such as seasonality, marketing campaigns, etc.

7. My Contact Centre Is Planning to Extend Its Operating Hours. How Do I Create a Forecast if There’s no Historical Data for These Time Periods?

Begin by looking at ACD data to find out if there’s an indication of calls attempted available. That way you’ll come to know whether there’s any latent demand. Next, extrapolate the volume in a graph and stick to the trend during the last couple of hours.

Once in place, consider overstaffing slightly, for a simple reason – it’s always easier to request staff to come in earlier than to have to ask them stay later. A week or two in and you’ll know whether your assumptions are trending in the right direction.

Keep in mind that this is a completely new scenario for your business and the best way to go about it is to keep testing and take the trial and error route for the sake of learning.

8. How Do I Get Rid of Deviations of My Data When It Comes to Forecast and Actuals?

Here are a few best practices that can help you overcome the challenge of a mismatch in your data. Sometimes there’s not a single answer to the problem. Consider multiple tactics and check the results.

• Remove the top and bottom 25% (outliers) and check the impact on the data distribution.
• Forget about trimming calls, and use weighted average AHT in calculations instead.
• Consider a mean average historically – that should do the job.
• Omit deviated values using distribution from a normal day or week.
• Remove anything more than 1 standard deviation from the mean.
• Analyse the reasons for contacts (especially peaks and spikes) in order to identify an outlier and quantify it. Use those figures to modify weekly numbers.

---

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.