The True Cost of Spreadsheets

Rob wrote a white paper called Spreadsheet Addiction: The True Cost to the CPG Industry. In it, he lists the reasons why Excel is so popular and widely used and he lists the reasons why it costs more than you think. I’d excerpt some of that text, but I don’t know how to select text in a pdf that opens in my browser and I don’t care to learn. So you’ll have to go read it for yourself. It’s only five pages.

When I worked for a commercial general contractor in the construction industry, I was fortunate to be able to use Timberline accounting software. It was written by accounts specifically for general contractors. Similarly, Dexter+Chaney makes software that subcontractors rave about. When a company has to change their processes to accommodate either of these packages, they’re probably doing themselves a favor.

When I moved to a manufacturing company, one of my first objectives was to dump Quickbooks and find the Timberline of the manufacturing industry. What I found was a bunch of applications that did about 75% of what I wanted them to do. I already had Quickbooks, which does about 50%. So the value proposition was paying more than $20,000 for the next 75%. The remaining 25% would still have to be done outside of the package. Needless to say, five years hence I’m still using Quickbooks.

Outside of a few industries, it’s very difficult to find software that meets all of your needs. Property management commpanies, attorneys, public accountants, and doctors are all examples where you probably can find such a package. But manufacturing, like most other industries, is just too diverse for any company to produce great software; great for your business, that is.

That leaves custom applications that bolt on to your multi-thousand dollar package. Small companies certainly don’t have the resources to write their own in-house .NET-backed-by-SQLServer application. And it’s not just small companies. Many people reading this blog make their living because companies large and small don’t have the resources to do it or haven’t set those priorities for their IT departments.

That leaves accountants, engineers, production managers, and owners to do the job. What do they use? You guessed it: Excel and Access.

So let’s stop trying to convince people to eschew these fine programs. Rather, let’s educate them in how to develop responsibly by using best practices and understanding the risks and limitations. Now I’ll step off my soapbox and hear what you have to say about it.

Posted in Uncategorized

16 thoughts on “The True Cost of Spreadsheets

  1. Using the latest Acrobat Reader should be sufficient to copy text from a pdf file (don’t forget to use the selection tool ;) ).

    Both you and Rob have some good points though.

  2. “… let’s educate them in how to develop responsibly …”
    [rant] I am riding on what is probably the worst project of my decade.
    The “Project Manager” doesn’t believe in written goals, objectives, doesn’t feel that sticking to deliverables and deadlines is an option, doesn’t know Excel, and admits it … The five VPs whose workbooks I am merging don’t trust range names or the F3/F5 function keys, can’t read formulae (so every formula has to be spelled out atomically from left to right with each component occupying a cell, with +-*/ in the intervening cells (Thanks Rob Van Gelder) and we’ll worry about parentheses, VLOOKUP and AVERAGE when we get to it ….
    I spent the first two months of this three-week project proposing training. Nope. We’ll have time for that after we get this project finished.
    I like to think that I am a professional with standards, but after two months of warning and notification I have now settled down into hours of back-breaking and largely un-necessary clerical work, interspersed with the delivery of a mammoth invoice every two weeks. [/rant]

    There are times when clients just don’t WANT to be told about fourth gear; to them first gear is already so much better than walking from Toronto to Ottawa (look it up) each fortnight …

  3. There are basically two reasons why a business will purchase and implement a packaged solution: (a) the solution is so good compared to the alternative manual process (e.g. email, payroll) or (b) it’s needed from a command’n’control perspective (e.g. Accounts and sometimes CRM).

    For everything else, packaged solutions are anything from a nice’to’have to a complete waste of money. This condescending nonsense on the “cost of excel” is too common place, does the author really think that owners and managers of businesses are not aware of the “cost” associated with spreadsheet production?

    I believe the majority are, but they’re equally aware of the hidden costs associated with the willy-nilly proliferation of IT-dependent so-called “solutions”.

    Tom

  4. It’s an odd report, I’m not sure what the point of it is? Yes spreadsheet cost money, no one would disagree with that. Yes there are better ways to do things, no one would disagree with that. After reading this what are you ment to do?

    All other software has costs to:

    >>….It describes how the biggest reason folks cling to spreadsheets is that the >>alternative requires change. It might require a new piece of technology, extra work, or >>worse – the involvement of the IT organization.

    Is new technology free? extra work is that free, involvement of IT, is that free…

    >>Simply summarized, people fear change >>therefore they reject it.

    People may fear change (I think it more likely they dislike lack of control, but..) but clearly there are cost with no Excel related software solutions to.

    If the point is to highlight the cost of using Excel then fine, but if the inference of that is that its cheaper to use a other software solutions then the case is not closed, the cost of the other software is possible just as big, or more…

    I can see that this paper helps anyone much?

    Any I missing the point?

  5. My own two (ok, maybe three) cents:

    You said it yourself, Dick: risks and limitations.

    Building a spreadsheet is easy. Any old schmuck with Excel can slap one together. Building a spreadsheet application is more complex. But, in the grand scheme of software development, it is relatively easy. The platform is relatively forgiving, the VBE will assist you along the way (e.g. Intellisense), implementation of any SS apps are simple enough as Excel is highly portable. You don’t need to run it through a compiler program, you don’t need a special deployment methodology, you don’t need (in most cases) new technology to make it work.

    But it is not a secure environment (yes, yes…passwords, protected sheets, etc, fine, but it’s not TRULY secure), it is not easy to update to a new version of the SS app if there are multiple deployments, and any SS app is subject to the stability of Excel itself (i.e., a bug in MS Excel is a bug in every single SS app you’ve ever built, whether those apps hits that bug or not).

    And then there is the other point you made: develop responsibly.

    How many SS apps are developed by non-developers, simply because they have a little bit of VBA knowledge? Or, perhaps in a better and more applicable example, how many true SS developers are given the appropriate amount of time to develop properly? There is always an assumption that, because something is being built in Excel, it will take less time than any other build, simply because it’s Excel. And while we all know this is not the case, I’m hard pressed to convince an SVP to wait on a deliverable.

    And, depending on the industry (such as my own), some processes are so enormous that it would take multiple spreadsheet apps across multiple departments to manage what could otherwise be managed within a single enterprise application.

    But there is one other cost component missing from this discussion (both in the post and in the white paper): capitalization.

    An in-house SS app construction project is rarely if ever going to be capitalized. Whereas, when a dedicated multi-thousand-dollar (or in our case, multi-million-dollar) system is purchased, that system, the licensing fees, the implementation costs, any managed services fees–these can all be capitalized. This is a great benefit to a company trying to control its G/L.

    Now, all this being said, I’m a huge fan of SS apps, and I’ll agree that they get a bad rep. Many is the time I’ve heard someone in my own organization say, “we’ve got to get off Excel!” without understanding just what we’ve been able to accomplish in Excel. I had a third-party consultant say to me literally this morning that he was surprised at how much we’ve been able to do and accomplish using just Excel and Access. I love Excel and how flexible it is, and how much you really can do with it (and we’ve don e a lot).

    But to really move our business in the direction we have decided to go, we need to move off Excel.

  6. Re memory. There are still enough people who began working before the PC era (when MOST PEOPLE got a PC on their desk at work, so more like 1985 than 1981) who remember IT/IS back when it was MIS/DP. If you think the US Republican Party invented the Culture of NO you never asked an MIS/DP manager for anything. We know in-house centralized application development and delivery is far more often than not a crock and/or a money sink.

    So unless there’s a really good 3rd party application or your department is allowed to contract consultants, you’re stuck with some departmental application development. Since most companies don’t even provide the average desk worker with Access, that worker has either Excel or the Notepad/WSH/VBScript ‘stack’ as the ONLY automation tools available.

    Excel is used so much in large part because it’s the only tool most business PC users have (and are allowed to have).

    There’s considerable need for training. Back in the late 1980s both Lotus 123 and Excel came with tutorials on extra product diskettes as well as hardcopy tutorial guides. Maybe current economics requires the guides to be PDFs rather than hardcopy, but it there any reason other than saving a fraction of a penny per SKU not to provide tutorials anymore? Maybe MSFT has deals with training providers not to curtail the training market.

    Unfortunately most managers either believe they understand Excel or are as paranoid as Chris describes above. Either way training ain’t going to happen because the former don’t believe they need it (and if they can learn Excel on their own anyone else can too) and the latter wouldn’t want to make use of anything which might come out of such training.

  7. From a manufacturing perspective, many MRP/ERP Solutions are available for larger industries, however, few can be afforded by small and medium sized companies. Unfortunately, the larger MRP / ERP based systems typically require an equally sized network / server system to run them and also require full time IT staff to support them. The shear size and complexity of these solutions is also a deterrent for successful implementation and integration across the entire organization.

    My experience suggests that despite the rigors that the software vendors may have taken, I always find myself resorting to a spreadsheet of some kind. Why? The simple answer is that most companies focus on the financial, purchasing, and inventory management modules and seldom have time to flesh out implementation of the remaining support systems / modules. As such, many installations are haphazard in medium sized organizations and are virtually rendered useless.

    Scheduling is one of those areas that I have yet, in my experience, to find a solution that truly supports operations in real time. Spreadsheets to support daily operations can be served up fairly quickly to support most small and medium sized operations. If a company is using an MRP / ERP system, it is a relatively simple matter to create a connection or generate a CSV file from the system that can be loaded into a spreadsheet.

    For the small and medium sized manufacturers, customization can be a relatively simple chore, however, it is always easier when the “users” of the system know at least a little about Excel. I have been fortunate to work with companies that recognize the capabilities of Excel, however, as suggested in the comments above, few seem to be willing to learn it.

    This is good for business as long as the solutions are viable and cost effective. To some degree, my customers are akin to car owners. They understand the capabilities, value, and purpose of Excel but really don’t want to know how it works. To them, a person who can setup a spreadsheet, program, or develop an application is the equivalent of the Master Mechanic. They pay for the service and move on with their work. Turn the key and drive.

    To the same degree, I prefer it this way as well as it offers a great deal of autonomy. As pointed out in John Walkenbach’s Power Programming book, there are different levels of users. We recognize that Excel may not be for everyone, however, there is always one in the crowd that seems to be the resident expert.

  8. How about some software to trap typos and misplaced whole sentences in PDF documents?

    I think the heart of the subject is that a lot of companies are indirectly committed to Excel for large parts of their operations. They need to find out exactly how far they are committed to Excel, even if they don’t have the means to migrate away. There is a lot to be said for users of data being comfortable and familiar with the way the data is presented. Losing a laptop of spreadsheets may only be one of hundreds of security risks/business risks being carried every day.

    There again, the paper also proves what a large market is available to Excel experts, such as those contributing to this blog. (Myself excluded).

  9. The real problem is that the programmer(IT) and the Business knowledge/process experts are two different people.

    So the Business people never get “Exactly” what they want from the IT people.

    This leads the Business people to rebel. Excel+VBA give them hope and the ease of use convinces them that they can do everything using Excel and that they now dont have to depend on IT anymore.

    The “Cell Wars” begin.

    If the Head of IT is stronger/more influential than the Business Head(s) then the Org buys SAP/ORACLE etc.. else they live with spreadsheet hell

  10. I’m outside of that industry, but I’ll offer:

    Yes. Excel is an abused resource. But as people have said, it’s because people know it, it’s accessible, and it is capable of serving an immediate need. When it serves the immediate need acceptably, why does Manager Mike need to go out and spend eleventy billion dollars on specialized software? “We’ve always done that in excel, the folks down in [insert department] seem fine with that”. It’s all true.

    Although, I think it’s worth noting: We, of all people, understand that a skilled excel mind can make excel do just about anything. From a half ass database to a BI tool to a calculator. So if there’s a problem, the above average users and the uber users are part are at least part of the environment that facilitates the problem.

  11. “So let’s stop trying to convince people to eschew these fine programs. Rather, let’s educate them in how to develop responsibly by using best practices and understanding the risks and limitations.”

    I couldn’t agree more. Excel is a great, flexible tool but it has to be used properly.

    With most systems you’ll still need Excel for ‘the last mile’ in for example reporting and analysis. It would be better if software vendors embraced Excel instead of trying to offer something similar that’s ‘not quite it’. At my current job the ERP system uses Excel as one of the reporting options. You can define a query and then an Excel sheet is generated with a permanent link to the data in the ERP system. You can then add pivot tables, graphs, calculations etc. and save it back into the ERP system. Then you can manage who will be allowed to access it, view it and/or edit it.

    Now that Excel is an important part of Microsoft’s BI platform (with PowerPivot and SharePoint) I think perceptions of Excel might change for the better in the next couple of years. It might actually end up being considered as a ‘serious’ reporting and analysis tool.

  12. Security is a separate issue. Not much difference between loss of sensitive information in XLS files vs MDB, CSV or PDF files. When laptops go missing, it doesn’t matter what applications or file formats were used to store sensitive information on those laptops.

    Whole drive encryption supplemented with file-open passwords provides minimal security. On the fly (OTF) single file encryption has huge holes. In my own experience, OTF defaults to leaving anything in temp directories unencrypted, and it can’t handle compressed files/files stored in compressed folders.

    As for spreadsheet hell, I lived through a short-lived enlightened age at a former employer in the mid-1990s where different departments’ spreadsheet experts reviewed other departments’ spreadsheets because IT had neither the bodies nor the experience to do it but recognized some form of outside review was useful. But then came some financial distress, hiring freeze, and the department experts had no more time for such reviews.

    It’s wise to be skeptical of the value of any committee, but departmental application development committees with some IT representation (no more than 2 people) might be a partial answer.

  13. Excel is a great tool, and our team simply will not be able to do any work without it. We, just like everyone else, went down the path of plugging holes in the $$$,$$$ systems with Excel and Access. And we quickly learned why it was a bad idea, even though it was the only option. I have to chase a missing Active X on someone PC about once a week, Access keeps popping “disk or network error” alerts almost daily when local Access process tries to use networked files, our IT wouldn’t touch any of this with a barge pole, etc. etc.
    But these problems don’t scare me that much, there is a bigger and uglier one. It is rare, but sometimes we manage to convince the powers to spend on IT development. What happens next? IT demands business cases and detailed functional requirements. People without any skills or specialist knowledge to write those go on and write them. IT, without much knowledge of the underlying business, goes on and builds the thing as specified in those documents. Soon everybody realises that something was left out, something was misinterpreted by IT, something nobody expected to happen, and so on.
    That is where Excel and Access can prove their worth. It is relatively easy to quickly built a prototype in Access, Excel and a bit of VBA, and use it for a while to see how the whole thing works, make changes here and there, etc. Almost always there is something that comes up and no-one expected it, or thought about it. And it makes life easier to show IT what the thing is supposed to do, rather than write about it. There is only one problem: when prototype is built, refined and ready to be, well, the prototype of the new system – the powers don’t understand why we need this new system. “Don’t we already have one that works?” If you are asking the same question – read the 1st paragraph in this post and the rest of this thread.

  14. Excel has so many advantages over all other applications
    1 Its already on every desktop
    2 It handles both text (I hate leading zeroes) and values
    3 It has VBA built-in (very few application give you such a powerful easy to learn and use programming tool)
    4 It can extract data from most data bases
    5 It has charting / graphic abilities
    6 It has PivotTables and the new PowerPivot
    7 Most users have a basic knowledge of Excel (it take a two day course just to learn how to stuff up in Access)

    These make Excel a great low end ERP solution for small to medium businesses.

    Accounting systems generally have cumbersome reporting interfaces and its easier to dump data into Excel and create the reports you want.

    All you need are developers who build in validations, error checking and can build good interfaces for users.

    (BTW a good read for building + testing interfaces is Rocket Surgery Made Easy by Steve Krug)

  15. @Neale – mostly I agree but . . .
    2 – Excel isn’t unique in this, most scripting languages easily and automatically handle text/number conversions;
    4 – anything which can use ODBC can fetch data from nearly all RDBMSs;
    7 – define ‘basic’.

    It’s very easy to become an intermediate Excel user, but it’s no faster (maybe slower) to become an advanced user than for other systems. Too many people believe they’re more advanced than they truly are, and Excel’s (spreadsheets generally) paradigm lulls too many users into false feelings of confidence in the correctness of their models. There’s a fair body of academic literature on software and spreadsheet errors. Spreadsheet formula errors are 10 times more frequent then source code errors in other programming paradigms. Maybe an open question whether that’s the case for spreadsheet experts, but beyond doubt that nonexperts on average have a lot of errors in their models.

    Finding true developers who work frequently with spreadsheets is difficult. My definition of ‘developer’ includes regression testing and version control. Very few people who work mostly with spreadsheets or end-user databases employ these software engineering disciplines. Finally, people who are good at handling the calculation and data flow/referencing details are seldom also good at designing UIs. Usually you’d need 2 or 3 people with complementary skills, but spreadsheets (Excel no different than any others) are horrible for multiple developer teams.

  16. In my company (a $35 billion organization), what is important today will not be important 6 months from now. And what is important six months from now will be forgotten in a year. Even the priorities that return (which is most of them) will return in a slightly different form with different reporting requirements.

    Thus, Excel, with its unlimited flexibility, is the best tool out there to deal with these “flavors of the day”. Excel may be fueling this short attention span..nevertheless, it won’t be disappearing in my lifetime.

Leave a Reply

Your email address will not be published. Required fields are marked *