Spreadsheets in Forecasting and Scheduling

By William Durr

December 2008

Workforce management software systems completely automate the process of forecasting and scheduling and have been commercially available for twenty years.  Larger call centers have invested in these solutions, but small and medium centers have traditionally been troubled by the cost.  A common solution for small and medium-sized centers has been to ease the pain of manual forecasting and scheduling by using a spreadsheet.

Overwhelmingly, the use of spreadsheets appears in the forecasting function.  Given good spreadsheet skills, a person with a basic understanding of the process briefly described here can fashion a utilitarian forecasting tool.  Typically, forecasting spreadsheet tools employ multiple worksheets that are linked to eliminate some of the tedium and time it takes to acquire, transcribe, and enter actual call arrival and average handle time by the half-hour.  Daily data is entered on one worksheet while weekly and monthly totals are rolled up on other worksheets.

The use of spreadsheets in the scheduling function is less prevalent because scheduling is a much tougher problem.  Forecasts are derived from facts recorded by the ACD.  Schedules, on the other hand, are fluid constructs bounded by a variety of rules, best practices, and common sense.  Most spreadsheet-based scheduling systems rely upon fixed schedules, as do manual systems.  Fundamentally, the scheduling process is not a simple arithmetic process that can be automated inside a spreadsheet.  Instead, the scheduling process is more like a spatial jigsaw puzzle.

Imagine that the various work shifts are LEGO blocks.  Most of the LEGO blocks are of a certain length equivalent to an eight-hour shift, as depicted in the graph.

The call arrivals for the day show a curve with a mid-morning peak and a mid-afternoon peak.  The scheduling task is to arrange the LEGO blocks so they fill in the space below the curve.  The objective is to avoid gaps below and above the call arrival curve.  This is not something that a spreadsheet accomplishes well.  In fact, spreadsheets and optimized scheduling - where start times, breaks, and lunches are dynamically determined based on computer matching of demand with agent availability - are by nature inimical.

Problems with Manual and Spreadsheet Approaches: We've already touched on a few of the issues associated with manual and spreadsheet-based forecasting and scheduling.  Specifically, these approaches suffer from:

• Difficulty in acquiring the data: ACD systems generate large amounts of data that must be painstakingly transcribed from the reports onto paper-based manual systems or rekeyed into spreadsheets.  This takes time and effort, competing with other obligatory tasks for attention.

• Difficulty in spotting trends: There are long-term trends, particularly in the forecasting process, that have profound influence upon the accuracy of the forecast.  It is challenging to identify these trends in manual and spreadsheet systems, since this entails the comprehension of data that spans many weeks and months. 

• Tedious, time-consuming methods: Rekeying and manipulating the data and performing arithmetic operations repeatedly without error is tedious and takes time. 

• Fails in skill-based routing environments: When multiskilled agents appear, the Erlang C function that is central to all manual and spreadsheet-based approaches no longer produces accurate agent requirements and significantly overstaffs.

• Tracking schedule adherence: A schedule isn't effective unless people follow it.  This is called schedule adherence.  In order to meet service level objectives, agents must follow their schedules.  One agent can make a big difference in small centers.  Not following the schedule has ripple effects throughout the day and creates conditions where it is impossible to achieve service goals.

• Difficulty analyzing options: Manual and spreadsheet approaches almost never afford the opportunity to easily consider options and alternatives. 

• Problems meeting staff desires: The only way to meet staff desires is to capitulate.  This is why most manual and spreadsheet approaches to scheduling rely upon fixed schedules that the agent essentially accepts.

• Overstaffing and understaffing: The result of these issues is a compromised schedule that is not likely aligned with the call forecast.  This means that the center will experience periods of overstaffing and understaffing in the same day, leading to needless expense and caller frustration.

Benefits of Workforce Management Software: Most small and medium-sized centers realize significant returns on their investment from workforce management software in the following areas:

• Reduction in administrative time: Using a forecasting and scheduling tool can reduce administrative time spent developing forecasts and schedules by up to 90 percent.

• Reduction in excessive agent idle time: A huge payback can be quickly realized when agent schedules are more closely aligned with the call demand.

• Reduction in agent turnover: With a better match between workload and workforce, the center experiences less service level volatility, leading to a more predictable work experience for the agents. 

• Reduction in shrinkage: Forecasting and scheduling software enables sound management by first helping to accurately measure the sources of agent shrinkage and then providing tools that minimize its occurrence.

• Improved agent productivity: With more time available to frontline management, productivity improvements from focused coaching are realized.

• Improved service levels: A better match between workload and workforce means that service levels often improve without the addition of paid agent labor hours.

• Less service level volatility: Less volatility in service levels means that the caller experience is more consistent, leading to improved client satisfaction and loyalty.

• Reduced telecom costs: Improved service levels and less service level volatility mean that fewer callers are in queue for excessive lengths of time.

Conclusion: However your contact center defines success, accurate forecasting, and scheduling of agent resources are critically important.  While forecasting and scheduling processes are conceptually easily understood, execution can be problematic.  Manual approaches have many problems that can contribute to excessive costs and unrealized opportunities.  Attempts to streamline calculations have led to spreadsheet-based approaches that focus on forecasts and agent requirements.  While they speed and simplify calculations, spreadsheets are fundamentally not well suited for solving the scheduling part of the application.

It is ironic that while small and medium centers typically are harder to manage than larger centers, they are more likely to use manual and spreadsheet-based forecasting and scheduling systems.  Given the budget associated with agent labor, centers should consider the benefits provided by an automated solution.  These offerings provide distinct advantages over manual and spreadsheet-based forecasting and scheduling, including the ability to easily create accurate forecasts that drive the generation of optimized schedules and help ensure that service levels are met with minimum agent labor. 

William Durr is principal global solutions consultant for Verint Witness Actionable Solutions.  Bill is a frequent speaker at industry events, where he addresses a variety of topics relevant to the contact center industry.  Recurring themes include managing for productivity and quality, the impact of workforce optimization, and the external forces driving contact center evolution.