Jon DiPietro is Domesticating IT

For the second time in the last three months, I had a grateful client report back to me that a new report commissioned out of my software product uncovered errors in their former spreadsheet-based billing system.  In this case, they would have under-billed a client by several hundred dollars for the quarter.  Considering that this spreadsheet had been in use for years and copied to other spreadsheets for billing other customers, one wonders what the total cost was.

Spreadsheets are like high blood pressure: the silent killer of businesses.  Of course, this means every business except yours and mine. It’s all those other users who have error-riddled spreadsheets with mistyped formulas and errant cell references.  I have every confidence that yours and mine fall into the ten percent of all spreadsheets that contain zero errors.

90% of All Spreadsheets Have Errors

Raymond Panko has been researching and documenting spreadsheet errors since 1997.  In 2009, he conducted seven field audits of operational spreadsheets and found that an astonishing 88% of them contained errors.  At first blush, this is hard to swallow.  But what if I were to tell you that one in every twenty cells with a formula is flawed?  That doesn’t seem so hard to believe.  If you then allow that the typical operational spreadsheet averages about a hundred cells with formulas, then it suddenly becomes quite plausible.

Another field audit of fifty operational spreadsheets by Stephen Powell at Dartmouth College found that only three of them (94% percent) were error-free.  In this study, six main types of errors were reported:

1. Hard-coded numbers in formulas where cell references were intended
2. Formulas that contain incorrect cell references
3. Logic errors caused by using the wrong function or incorrectly implementing the function
4. Copy/paste errors
5. Inaccurate calculations due to cells with omitted data
6. Data input error

These sorts of errors are known to cost businesses billions of dollars every year.  The European Spreadsheet Risks Interest Group published a list of 89 “horror stories” encountered in their own evaluations.

But I’m sure yours and mine are perfectly fine.

What’s the Point?

Databases.  Most operational spreadsheets I’ve come across are a case of trying to hammer a nail with a banana.  They are situations that are best suited by a bona fide database back end and user interface.  The fundamental problem with a spreadsheet is that the data and business logic are not separated from the user.  One inadvertent keystroke can wipe out a carefully crafted formula written five years ago by a statistical guru no longer employed by the company.  Fault tolerance is another issue, as is data type checking and many other standard features of a properly designed databases that will protect both the data and the formulas.

I’m particularly sensitive to this topic after responding to Gary Mintchell’s recent blog post, “You should all learn SQL.”  In it, he noted the proliferation of databases and subsequent need for more non-IT personnel to learn SQL.  As this discussion points out, however, maybe the proliferation is not quite fast enough.

I’d love to hear your thoughts and, particularly, any spreadsheet horror stories you’d like to share.

Continue Reading

Or perhaps more importantly, should you? I want to explore the very common situation of a manager or small business owner who does not “speak geek” and needs to outsource a software or web development project. I was recently asked by someone in such a position what the “best way to talk to a web developer” would be, since their requirements discussions were ending up in what he described as “Babylonic confusion,” hoping to find a book or course he could take in order to be able to better communicate his needs to the developer.

I don’t know that anyone has found a real live Babel Fish yet, so you’d better figure out another way to get your point across to the geek(s).

It’s Not Me, It’s You

At a moment like this, it’s time go and get yourself a new developer. The project is doomed to fail. While it is your responsibility as a project manager to clearly articulate your requirements, it should only have to be done so in your own comfort zone and business terminology and not involve learning a new language. Here are some inherent problems with taking this approach:

• First, this assumes there is a single language to be learned. There are typically multiple technologies involved in any project (e.g. database, server operating system, programming language(s), scripting language(s), hosting platform, external API’s, etc…) and hoping to become conversant in all of them is going to take an incredible effort at best and is futile at worst.
• Second, merely speaking the language is not enough and could, in fact, exacerbate the problem. The reason is that there is such a thing as “knowing enough to be dangerous”. Without experience in architecting a solution, talking about the technical aspects of a solution is premature. It would be like specifying what type of tires you want on a new car before even deciding whether it will be a sports car, sedan, or SUV!
• Third, this leads to the real possibility that you will end up with what you asked for and not what you wanted. This is quite common in software development. It’s something like asking for a “kick ass” sports car and then getting a car with a mechanical arm on the front of the car with a boot mounted to it. It may be what you asked for, but it’s not what you wanted.

Tell Me What You Want, Then I’ll Tell You What You Need

I’ve sat down with users many times in requirements meetings and asked them what they want the system to do. Often, they find it very difficult to answer because there have not been any boundaries or parameters established for them. It’s like when my wife asks me, “What do you want for dinner tonight?” When I’m feeling particularly sarcastic, I’ll say something inflammatory like “How about Peking duck with an orange glaze and chocolate soufflé for dessert?” and then run for cover. What I’m actually saying is, “What are my choices? What are the parameters? Are you going shopping or do we need to find something in the fridge? If so, what do we have? What about take out?” You get the picture.

When you alter this scenario and instead put a prototype or screen shots in front of users, then you’ll get bags and bags of feedback. That’s because the parameters have been set and they can visualize the inputs, the manipulations, and the outcomes. In the end, that’s what information technology is; stuff goes in to a box, something happens to the stuff, and new stuff comes out the other end. It’s all about defining the inputs, the manipulations and the outputs. But that still doesn’t answer the fundamental question here. How do you deal with a contract developer who doesn’t get it? I’ve already said you go and get yourself a new one. But how do you make sure the new one will work out differently?

Tell Me Something I Don’t Know

Developing a software application is a lot like building a house; it’s the design, architect, build process. You’re the designer and ideally you’d like to find the architect and builder in the form of one person. As a last resort, you are better off hiring both if you can’t find one person to do both jobs. And remember this – you’re a designer, not an architect and certainly not a builder!

The bottom line is to try to find someone with experience in the business arena in which you operate. If you’re the owner of a small chain of sandwich shops, don’t just hire someone who’s developed a web site before, try to find someone who’s developed a web site for a restaurant. And not just one restaurant; a chain. Experience is golden here and there is one rule of thumb that I also mentioned in an earlier blog (Yes, Virginia, You Need a Web Site). When or if you find a developer who can tell you something non-technical about your business that you didn’t know, then you know you’ve got yourself a winner.

Continue Reading