1 Reply Latest reply on Dec 6, 2010 10:57 AM by Davej1608

    The spreadsheet infestation--will it ever be exterminated?


      John Westerveld's recent blog post entitled "How are spreadsheets like cockroaches?" has attracted a lot of attention on the LinkedIn Group "Business Forecasting and Planning Innovation." The corresponding discussion thread has received so many comments, I wanted to share it with the community.


      The essential theme of John's post is:


      The idea is that despite many attempts on behalf of IT to get key corporate data out of spreadsheets and into dedicated systems, spreadsheets keep coming back.


      Obviously, this hit home with quite a few folks. Below are the comments generated on the aforementioned LinkedIn group. Please use the comments section to chime in with your Excel story--have the cockroaches taken up residence in your business?



      Eric Smith • I invariably end up going to the data warehouse to get information, because that way I can incorporate information out of the system that isn't taken into account in the standard reporting that is programmed in our system. I like to think I'm smarter than that standard report...I think that is a lot of the reason why spreadsheets will always proliferate. Errors do crop in, and eventually I'll move on to another assignment leaving a spreadsheet or database process that no one else understands well but me.

      The industry really needs ERP products from which non-experts can easily create reports. In my case (and many other cases), our ERP system is home-grown, and what we really need is a real ERP system.


      Dawn DiLorenzo • I just posted a blog article on our website yesterday regarding Excel spreadsheets and inventory planning! Check it out (three more posts in the series to come so subscribe to our RSS feed for updates!). http://info.valogix.com/



      John Westerveld • Eric, I couldn't agree more. The use of spreadsheets that you describe is very typical. ERP systems are anything but nimble. As we say around here, ERP systems are poured in concrete. Once set up, you need a jackhammer to change anything. In a previous life, I was in IT supporting our ERP system (not home grown). We still had tons of spreadsheets because in order to get a custom report run, you needed to submit a request to IT, wait for it to bubble to the top, talk to the programmer, wait for them to create the report, then wait for them to fix the report (because they always got it wrong first time out). By the time you got the report, the requirements have changed or you've forgotten what the report is for in the first place! Don't despair though, there are tools that will allow you, the typical user to get at ERP data and view it any way you need.



      Bey Gonzales • I completely agree! Spreadsheets allow for flexibility and timeliness. Data warehouses are great for collecting, filtering and downloading data. Spreadsheets are great at helping turn that data into information you can act on. In the hands of a skilled analyst, you can sift gold from raw data in a fraction of the time that it would take IT to build you a custom report that probably will need to be changed when the boss' boss decides he wants to see the info from a different perspective. Spreadsheets these days have very powerful data and charting tools, but of course it's imperative that the analyst is well-trained on good spreadsheet habits (organized documentation, error checks, etc.) for this all to work.



      Earl Butler • I think a large part of the problem is that when organizations are going to Deploy ERP, ABC, BPM, etc. solutions they approach it from the mindset of if we buy it we can plug and play but each implementation is quasi-unique in that stakeholders, end users may have different needs and you need to look at the requirements of the various stakeholders and make assessments about how value add or not certain functionality is to the company. Excel is the workhorse of most finance organization primarily due to if flexibility when working with dataset. Like cockroaches they are everywhere.... In all my experience I've only seen one implemented solution that really addressed the need of the various stakeholders. During the end times MS-Excel will still be crunching some model. LOL



      Eric Smith • Part of the problem too is that the type of people who work on this stuff full time are the type of people who like to tinker with the data using tools like Excel spreadsheets. I hate to say it but many would rather analyze the data to death rather than reach a conclusion so that they can move on to making an improvement.



      John Westerveld • Earl, I think you've hit the nail on the head. A large part of the reason people use Excel is that ERP (and other business systems) aren't flexible enough to accomplish what they need to do. The interesting thing is that even if at the time of deployment, the system exactly met the requirements of the organization, we would still get Excel. Why? Because business needs change and ERP systems don't (easily).



      John Westerveld • Eric, I know exactly what you are talking about. We call it "paralysis by analysis".



      Scott McCalla • I thought at first the analogy of spreadsheets being like "cockroaches" would be negative. I am glad that the replies have been fairly positive in regards to spreadsheets as a tool. Which is all they are. An ERP system is just a tool as well. So is a data warehouse. My experience has been either the ERP tool doesn't have the capability/data, or the company will not allow "power-users" the access they need to the specific data. I once heard an IT colleague say, "Now that we are installing <ERP System>, we can have all the users get rid of all the #$%# spreadsheets. She didn't realize then (but might now) that the whole idea of eliminating spreadsheets is not going to happen just because you replace an old tool with a new one.



      Tom Dannemiller • The last ERP implementation I was involved with, we built the "spreadsheet reality" into it. We did not invest in report design or writing tools beyond the GAAP required reports and a small group of "dashboard outputs" that we needed as common tools to use in measuring business performance.

      We focused instead on the data sets needed by the stakeholders and making that information easily available to them to be dumped and manipulated on an ad hoc basis. As the group required other standard reporting tools. We could use off the shelf report writing tools to capture and replace the excel spreadsheets as needed

      90% of the reports we run are needed once, to answer a specific question. Excel is the perfect tool for that.



      Dave Velzy • If attacked by a swarm of annoying bugs, I'd get them under control. But, take a lesson from organic farming and learn to live in harmony with balanced ecology. Teach your staff to use the right method for the right job.

      If I want to make a chart, total a set of numbers, hand off a list or do anything with data. I'm going to use a spreadsheet. It's faster than paper and easier to distribute.
      Spreadsheets facilitate talking in numbers better than any other tool you can name.

      To think ERP, or BI designers and IT departments can imagine all of the questions which must be answered is madness. Unless an ERP/BI tool is as easy to use as the common spreadsheet and as ubiquitously deployed any dreams of locking out spreadsheets are just goofy. You don't lock up pencils because people can't spell. LOL


      Link spreadsheets to the data warehouse. Train staff to validate answers. Go Fast.



      John Westerveld • There are a lot of good ideas here. It sounds like (as with cockroaches) we will likely never eradicate the spreadsheet, and per Dave's comment; we should live in harmony with the critters. So what is the right balance? I think the risk with spreadsheets is when they become a replacement for the business system. Spreadsheets are less of an issue when they are used to run a quick analysis or to chart output from an ERP system. There is still risk in that the people creating these models might not have the knowledge or the structure that a trained IT professional might have and as such these tools that you are using to base your decisions on could be producing erroneous results. The answer may be wrong, but you are likely only making a single decision based on it so the impact is limited. This risk is elevated when the spreadsheet becomes the system of record, especially if it feeds other systems. At this point, many decisions could be made based on erroneous data. To quote back from my original post, "80-90% of spreadsheets contain serious errors." Where do you draw the line? What decision process do you use to say when spreadsheet data needs to come back under the business system umbrella?



      Scott McCalla • In a "perfect business", you would have only one "true source of information", which comes from the smartest, most experience and best trained person/people in your organization, and it would always be that way. I say to that "good luck". On the other end of the spectrum, you have a poorly programmed and utilized ERP system, with IT personnel who only knows and cares about IT, business personnel who doesn't communicate well with IT, and everyone with their own spreadsheets to do their job. When the demand for information comes (which is constant), you get what you get. And those spreadsheets are what you get - with all the errors.

      Your ERP system is only going to be as good as how your IT and business people communicate and make the system. It has to be flexible, it has to reflect the business, and it has to change as the business changes. It is an on-going process that does not end. From the CEO to the part-time file clerk, everyone has to have input and the sharing of ideas has to happen.

      I believe one of the biggest issues is having one department being "in charge" of the system, especially if it is IT. I have nothing against IT at all. I just believe that unless you have true collaboration, you end up with inefficiencies, lost opportunities, and inaccessible information. And this over time will trend you towards the poor end of the spectrum. What I see is one department controls the ERP system and the data, and everyone must go through them to get what they need. And they are busy. And you can't get what you need when you need it, if at all.

      The litmus test is this: Can you and the others in your company get the information you need directly from the system on a regular basis, without waiting on others to create/run reports, or essentially do your work for you? If you cannot honestly answer yes, then you probably do something outside the system, like create your own spreadsheet.



      Tom Dannemiller • John W. pinpointed the key issue in his last post. You can run big risks when spreadsheets become part of the system of record used to run the business.

      Spreadsheets typically have a life cycle, like any other product. Most of them are created to answer an ad hoc question and die there. Some prove themselves more useful and are shared among many people. At some point they become "Gospel" and are used to run the business.

      At this point it is worth the investment to get IT involved and create a a standard report that can be accessed by all. I may still be in spreadsheet form, as I find that no matter how a report is designed I end up wanting the data sorted in a different way. The key to the standard report is to get some objective review of how the report is set up to eliminate errors and automate the extraction/manipulation process to eliminate the errors that come from that.

      This process has the advantage of reducing the risk of error while only using limited IT resources where there is a good return.

      Scott McCalla • Tom, I agree, except this will not be an isolated report. And my response below is not directed at you personally. It's directed at the issue.

      First, take a look at what it will take for IT to create the report to the specifications needed (discussion time, programming time, etc) and to make it available to all. Then find out how many other spreadsheets are being used, and repeat. It's already a lot of time. Then, have someone in IT available to work with users in the future when more ad-hoc reports need to be made. And repeat again.

      I point this out because many/most companies have cut back on IT. In many cases they have pulled back to call centers which are staffed by a single intern, they electronically assign tasks to very small IT departments, and ad-hoc reporting falls to the bottom of the priority list, next to maintaining the entire system (server farm, network, etc), rolling out the next software upgrades, or just putting out fires. By the time the user gets their ad-hoc report, they may not need it anymore, as the business has changed or they have been replaced due to not being able to do their job (a bit extreme, but you get where I am going). Or, they found another way (and kept their job).

      The answer: Those who need the information must be able to get the information.

      This is the reason why we ditched the legacy system and spent millions on the ERP system. And unfortunately the ERP system didn't deliver - or we created policy to negate the answer.

      You know this because of the spreadsheets - the symptom of the problem, not the problem itself. Just like cockroaches aren't the problem - they are the symptom of something else that is wrong.

      We hire the best, the brightest, the finest, to do their job. But we lock them out of the information, having them stand in line so IT can provide it. It starts with the ERP systems being limited, and then company policies constricting the business. I believe there needs to be security. I believe there needs to be restrictions. But I believe the pendulum has swung too far in the direction of restricted access for all, making information more difficult to get, when it should be more accessible for our best, our brightest, and our finest.

      Simply put, if I need information to do my job, I do not want to rely on IT to take my request, do a analysis on whether the return is good enough for them, and then get back to me on their time to tell me either I've been rejected or they will schedule time in the future to meet the request. I'll use whatever tool I have, including my own spreadsheet.

      Fix the real problem, and the spreadsheets (cockroaches) will likely go away.


      Tom Dannemiller • Scott, I am sorry if I was not clear in my post. I would expect that the number of spreadsheet based reports that were actually deemed vital to run the business would be very small as a percentage of the total. Thus these would be possible to use limited IT resources well.

      I am interested to know what you believe the "real problem" is. I think the reality is that business conditions change rapidly, rendering ERP system as installed partially obsolete before they fully go live. This is not a "fixable" problem, as I see it but more a condition that we are challenged to manage our way through.

      I lived in a 125 yr old house in a very dodgy neighborhood in college. Those cockroaches were not going anywhere. The best we could do is manage and control their effects.


      Scott Richardson • Well I must admit that I regularly see spreadsheets pop up in planning and finance even where they have a well established business intelligence, enterprise planning and data warehouse systems.

      But then I don’t necessarily think that is a bad thing, I’m rather pragmatic about it. In my view it depends on what information the user needs, how quickly they need it, the type of decision they plan to make and who the plan will impact. If you need quick what-if analysis on a relatively small data set for your own department, then a spreadsheet is a great starter. If however the planning process requires input from multiple contributors, needs revision tracking, or the information needs to be distributed for wider review, then a more comprehensive enterprise planning (EP) system is a better approach. Again even these comprehensive EP systems (tools I’ve used such as Adaytum, Applix TM1, Cognos Finance – all now acquired by IBM), offer all the power and efficiency of tracked submissions, full access to corporate data, OLAP analysis capabilities, combined with all the capabilities with Excel, so you do get the best of both worlds.

      I also agree there needs to be a balance between security and access, but I don’t really think it’s a good use of time for a finance manager to be creating queries against a large ERP system, get the techies to do that, but give finance access to all the data in they need in an easy to use format. The best systems I’ve seen are those lead by business but put together by both the business and IT.
      In the end spreadsheets, like cockroaches, will never disappear, but they do need to be kept under control.


      John S. • The Data Warehouse Institute wrote a 2008 article that details the pros and cons of some of the issues listed above. http://www.hardmetrics.com/dl/TDWI_Spreadmarts_BP_web_Q407.pdf

      Excel is like driving a car... everyone thinks that they are above average, but clearly xxx are wrong. There are three types of people in the world; those on this side, those on the other side, and those that can't count.

      To me the real cockroach is user error. I sit back and laugh when people argue over which spreadsheet is correct... it's like my baby is uglier than yours!

      I won't defend my assertion here, but please read this research article from the University of Hawaii called "What We Know About Spreadsheet Errors"
      http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm and come to your own conclusion.