Excel is Costing You Millions

By Ric Kosiba, Interactive Intelligence

As I sat down to write this, I received an email with a LinkedIn post from Mark Ballance entitled, “Still Using Excel for Planning?” discussing, well, what I am writing here. If you Google “Excel Blunders” you get 246,000 hits. “Excel mistakes” gets 15,800,000 hits, and “Huge Excel Mistakes” gets 496,000. So it seems that, not only am I not the first guy to think of writing about this, but Microsoft Excel is well known for being trouble.

At the same time, Excel is by and large the industry standard for almost every financial plan or analysis. Our contact center industry is no exception. While the vast majority of call center operations use workforce management to manage daily activities, most companies, small and large, still use Excel to build their hiring plans and their complex budgets.

The contact center industry has an additional complexity that most company’s financial spreadsheets don’t. At the heart of a contact center planning analysis are almost always several models of the contact center operation that simulate performance and determine the best capacity plan. Our operations are complex, and the models that support the staffing and resource decisions we make must mimic this complexity.

For these reasons, the contact center planning spreadsheets are by definition more complex than your typical financial spreadsheet. What could go wrong?

An Easy Blunder

Here is a pretty typical contact center planning spreadsheet equation, and it has a big error. Can you find it?

=IF(IF(B8<(‘Input Sheet’!D12+1),’Input
Sheet’!E12,IF(B8<(‘Input Sheet’!D13+1),’Input
Sheet’!E13,IF(B8<(‘Input Sheet’!D14+1),’Input
Sheet’!E14,IF(B8<(‘Input Sheet’!D15+1),’Input
Sheet’!E15,IF(B8<(‘Input Sheet’!D16+1),’Input
Sheet’!E16,’Input Sheet’!E17)))))<’Input Sheet’!D18/
B8,’Input Sheet’!D18/B8,(IF(B8<(‘Input
Sheet’!D12+1),’Input Sheet’!E12,IF(B8<(‘Input
Sheet’!D13+1),’Input Sheet’!E13,IF(B8<(‘Input
Sheet’!D14+1),’Input Sheet’!E14,IF(B8<(‘Input
Sheet’!D15+1),’Input Sheet’!E15,IF(B8<(‘Input
Sheet’!D16+1),’Input Sheet’!E16,’Input Sheet’!E17)))))))

OK. I don’t know if there is an error in this cell, and neither do you. But here is the important part: neither does the person who uses this spreadsheet! Yesterday, I listened to a planner tell our team that he “only has eight  spreadsheets” to do his planning, but each one has 80 tabs or so. He also is a terrific analyst.

Blunders happen often. When planning with spreadsheets, the issue is the opposite of what my dad told me about golf: the problem with spreadsheets is the tool, not the craftsmen. Every planning spreadsheet is so complex that it is difficult to de-bug. It is difficult to ensure quality analytics. It is easy to type a smidge of wrong information into the spreadsheet and to develop a whopper of a wrong answer.

The internet is full of fun, yet pathetic stories of how companies lost millions of dollars via a spreadsheet blunder. And our spreadsheets are more complex than theirs.

Complexity Forces Simplification

A blunder is a one-time mistake (that someone caught). Probably the bigger issue is the repetitive mistakes that we make without ever realizing.

A repetitive mistake that we make, because we have a complex problem (contact center planning) and a complicated technology (Excel), is that we are forced to simplify the problem  in order to get any reasonable answers out of our technology. We usually do not know that our simplifications introduce their own error. Here are a few simplifications that are very common:

  • Many companies simplify their model of the contact center network to in order to determine staffing. For example, most companies use either an Erlang C equation or a workload equation. Both are well-known to be  inaccurate and Erlang is notorious for overstaffing. We’ve calculated Erlang C error to be upwards of 20 percent.
  • Many spreadsheets combine different locations into “one big center” to simplify staff planning. This simplification ignores the reality that different call centers perform very differently (e.g., differences in absenteeism or handle times). For example, this simplification forces us to hire into the wrong locations (and this costs us money).
  • Many spreadsheets flat-line and combine important separate metrics like the various shrinkage items. Often, planners do not have time to forecast individual line items (e.g., attrition, absenteeism, sick time) and so they combine and flat-line their values. This ensures that a call center is almost always wrongly staffed.

  • Every spreadsheet requires the analysts to make important decisions that are best left up to algorithms. Hiring, overtime and undertime allocation, and planning for controllable shrinkage are best suited for optimization techniques like integer programming. Determining a hiring schedule is a complex problem that can cost a company many millions if done wrong.

Inflexibility of Spreadsheets

This sounds a bit off—the most flexible technology ever written is inflexible? Let me try and convince you. Because of the simplifications we are required to make in order to produce planning analyses, contact center planning spreadsheets are notoriously inflexible, in their current form. When we add a contact type or channel, when we combine two separate staff groups, when we prioritize one call type over another, we are forced to recreate the spreadsheet from scratch, or shoehorn the new configuration into the current spreadsheet.

This introduces the possibility of serious error. A combination of the complexity of the operation and the complicated nature of spreadsheet technology requires simplifications that are hard to change. Maybe I should have said
“because spreadsheets are not robust, they are inflexible.”

Dangers of Inherited Spreadsheets

I tell this story a lot. Ten years ago or so, I was working though a planning spreadsheet with an analyst, trying to understand their equations. I saw an equation that looked something like this:

StaffRequired=ErlangC (Volume, AHT, ServiceGoal)+15

I, of course, asked “what is the plus 15?” The analyst looked at the spreadsheet and turned white as a ghost. His reply was “I don’t know.” We looked at the other staff groups and found little fudge factors sprinkled throughout the planning spreadsheet amounting to several millions of dollars of additional annual staffing costs. The worst part of the story was that this analyst had inherited the planning function and spreadsheet many years earlier.

This is pretty typical. With complexity and complicated technologies comes knowledge that is lost every time the technology is passed to the next user. Maybe those fudge factors were for some specific what-if. Maybe they were an attempt to calibrate the models. But certainly, they amounted to millions of dollars lost in subsequent years.

Time-Consuming Spreadsheets

In the days when I was developing analyses for a large reservations center, I was particularly proud of the accuracy of my spreadsheets. I used a model that I calibrated against reality and I built some pretty impressive validation charts. I was somewhat brash about it.

So of course the VP of Reservations had some questions of my super accurate spreadsheets. One Monday, I received a request for a what-if analysis given an expected explosion in calls at our peak traveling season. I got to work.  I plugged the range of expected call volumes, and rebuilt my hiring plans using the algorithms. I came up with some very interesting analyses, even around changing our servicing goals at peak season. That Friday I flew to his office to present my very cool analyses to the big boss and guess what? He had put together his own back-of-the-envelope plans and sent them to HR for execution. I was too late.

That seems to be a common problem with complicated spreadsheets—they take too long to provide useful information and analyses.

Lack of Confidence and Planning

We are left with processes that are slow, cumbersome, error-prone, inaccurate, and costly. This adds up to a process that both planners and executives distrust, and it results in analyses that are not completely believed. It means many what-ifs aren’t asked, many problems are ignored, and other problems are solved only with the executive’s gut-feel. It is no way to run a business. There are planning systems that are engineered that can help us.

There are many reasons that planning spreadsheets are hated and many blunders that happen—even if I am not the first to say so.

Ric Kosiba, Ph.D. is a charter member of SWPP and vice president of Interactive Intelligence’s Decisions Group. He can be reached at Ric.Kosiba@InIn.com or (410) 224-9883.