8 FAQs about Contact Center Planning with Excel

Excel is still the tool of choice for many contact center 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 centers.

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 a result of long-standing habit. Most of the time, however, it's because of a lack of management buy-in to migrate to WFM software.

In the meantime, here’s a selection of helpful FAQs on planning with Excel, to make your life as a planner a little bit 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, consider using 15 to 30 minute intervals.

Next, check your initial schedule and compare scheduled staff with staff needed, and pay attention to periods of over-/understaffing. Ideally, you should try to avoid scheduling breaks during understaffed periods to help minimize the impact on service level.

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?

Generally, it's good practice to use the amount 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.

As an example, let’s assume that one day you run into a situation where the volume of calls offered equals 100, and 20% of them are abandoned after a very short period of time. With an assumed abandon rate of 5%, we’d recommend using the actual number of calls 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 re-occur 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?

Create rows or columns, whichever you prefer, for each call driver (e.g. product launches, marketing campaigns, seasonal trends, holidays, or external factors such as traffic or weather forecasts). This will help simplify your work, especially when using long-term planning models, 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?

Unfortunately, this is easier said than done. If you're going to forecast three 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 analyze past behavior of both high-touch and low-touch customers to get a better understanding of their contact behavior. 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 center is planning to extend its operating hours. How do I create a forecast if there's no historical data for these time periods available?

Begin by looking at ACD data to find out if there's an indication of calls attempted. 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 to stay later. After a week or two, 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.

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, so 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 historic mean average 
• Omit deviated values using distribution from a normal day or week
• Remove anything more than one standard deviation from the mean
• Analyze the reasons for contacts (especially peaks and spikes) in order to identify an outlier and quantify it. Use those figures to modify weekly numbers.