Let’s talk about spreadsheet hell. The troublesome representation of information, breaking data trails, ownership, security, costly management of time and resources, and efficiency all seem to plague us as we rely more and more on our “trusty” spreadsheets. Well, spreadsheet hell has a cure.
Real quick before we dive into getting you all out of spreadsheet hell, I’m going to tell you just a little bit about ProjecTools. We are a software and project management services company that was founded in 1994 as an oil and gas consulting company. We thrive by solving the problems that folks face when using spreadsheets as a project management tool. We understand spreadsheet hell and built our careers on getting people like you out of the many pits of spreadsheet hell.
To get started, we’re just going to go through some common pains that we feel every day. I’m sure we’ve all gotten a spreadsheet emailed to us. We’ve opened it up and we’ve been very confused as to what the data is representing. We have all seen bizarre column labeling and said, “What is this? What is this information? Why is this spreadsheet mostly useless to me?” That is a huge pain that we can all agree on.
Some other pains that we see pretty often are formatting woes. This is particularly bad because our preference for building spreadsheets can obscure our interpretation of the data on the spreadsheets. For example, if I was to open a spreadsheet I wouldn’t like to see text oriented vertically. That might obscure how I view the data. This how the flexibility of spreadsheets destroys their ability to impart information.
The next one we’ll move into is data trails. Often times when folks use spreadsheets to manage big projects. Usually there are a lot of spreadsheets that roll up into a couple middle management spreadsheets. Then those spreadsheets roll up to a master spreadsheet for the project. Sometimes people manage their business this way or even their department. This presents a problem, and that problem is the data links between the spreadsheets. They are fragile, and they break often. When they break it is a huge pain to go back and fix that.
Most of our clients come to ProjecTools having moved out of Excel, or wanting to move their project management duties out of Excel. Usually once you get to the level of our clients, you’re trying to figure out ways to get outside of Excel and the problems that it costs.
One particular client with this exact problem walked in and said, “We’ve been managing all of our projects, all of our documents, our costs, engineering commissioning, in Excel. We have this very complex spreadsheet or series of spreadsheets. One person manages it. It breaks all the time. We’re having issues with it.” We said, “Okay.” We got to know a little bit about how they were doing business. Determined that, yes we could help them. The next day we ask them, “Hey can you bring in an example of what you’ve been doing so we can make sure that we configure the right stuff into ProjecTools for your particular organization.” We walked into the meeting room the next morning and there was a stack, textbook size, college textbook size, of Excel documents that he had printed out. They were scattered all over the desk. We said, “Okay, what is this?” He said, “Well, this is the way we manage our projects.”
He had arrows drawn all over the place and this huge stack of papers, and it was very complex and convoluted. In fact, he was the project manager and he couldn’t explain exactly how the spreadsheet worked. We didn’t have enough information to set up ProjecTools correctly from that one meeting and a lot of other people had to come in and explain their processes. This just goes to illustrate how complex these projects, especially these larger projects, can be, and how Excel isn’t probably the ideal solution for managing them.
Those big project spreadsheets can get very cumbersome and cause some problems. Another common problem that we’ve probably all faced with spreadsheets is ownership. You probably sent a spreadsheet out, and got it back only to respond with, “Oh, you deleted which row? Why would you do that?” Which leads to the common sentiment of, “Don’t mess with my spreadsheet.” Especially when they get more complex you don’t want people messing with your spreadsheet because they break or disturb the integrity of your data.
Now, to combat this a little bit, Excel has added some rudimentary protection features. You can protect worksheets with passwords. You can protect the entire spreadsheet with passwords. You can set permissions to allow users to edit ranges. You can track changes. I wouldn’t exactly call these features robust, but they’re better than nothing. The bad news is that people don’t use these settings. There are a lot of spreadsheets with business critical information floating around that have none of these protections.
Finally, the last pain in the butt we’re going to talk about is security. You may or may not have encountered a situation where you emailed a spreadsheet and included an extra worksheet tab in there. Say you email a client a bid, and sheets 1 and 2 have the bid information, but sheet 3, which you forgot to delete, has all of your internal bid calculations in there. Client looks at sheet 3 and sees what your margins are and you’ve just given them some negotiating power over you.
Another security thing that I’m sure people run into is having spreadsheet files on desktops and thumb drives. You don’t want to be explaining which Starbucks you left your thumb drive at with these business critical spreadsheets on. Another thing is, “Just email me that spreadsheet.” We shoot around emails with spreadsheets all the time, and we shouldn’t be doing that. Emails aren’t secure. They get lost or forwarded to personal email accounts, or even the wrong person. It is really an unacceptable way to do business these days.
We’re here for a reason other than spreadsheets being a huge pain in the butt. They are very costly.
There are, in fact, some pretty big issues. In fact, this is data which was provided by an independent 3rd party analysis group called IPA, Independent Projects Analysis. What you see here is the results of a survey that they did across big offshore projects (FPSO projects in particular) in the oil and gas industry. They found major issues to the tune of $55 to $75 million of work scope and delays, 2 year delivery slippages, 18% schedule deviations the median, and 15% cost deviations were the median.
You add all that up and then you take into account that you only get up to the 74% production capability and you can quickly see that the project outlook you’ve modeled out doesn’t work in the real world where there are bottom lines, debt service, and livelihoods attached to the project.
It’s important to have the right systems in place so you can maximize the chance of getting a project correct and out on time. But a disclaimer. The truth is, I love Excel and I love spreadsheets. I’ve heard this batted around a couple of times, our prospective clients say, “What happens? How does it get to that point?” The first thing that happens when an engineer runs into a problem is that they open a spreadsheet. That’s fair. I do it all the time. We at ProjecTools use spreadsheets, but we at ProjecTools also have issues with spreadsheets. It’s very common. Excel is an incredibly powerful tool and everyone uses it. I get that.
All of our clients, in fact, report using ProjecTools to replace many Excel functions. The thing here is that Excel is good for a limited scope. When you start scale, in terms of project size, project cost, team members, etc., Excel quickly becomes more of a pain than a benefit. Excel problems are big. They’ve been around for a long time. Since Excel’s been out there people have been making mistakes in Excel. If it gets to a point that people like the folks making Dilbert are making cartoons about problems within Excel we can safely say that it’s a pretty big problem.
Spreadsheets are dangerous. We ran across an article that we found in the 2013 issue of Forbes: “Microsoft’s Excel might be the most dangerous software on the planet.” The highlights were that Excel was being use to move billions of dollars. It was the tool that they, J.P. Morgan, were using to decide yes or no on transactions and investments. There was a simple transcription error in the Excel formulas. That particular error cost several billions of dollars. We found so many disaster stories on the internet of people losing money because of spreadsheet errors. In fact, this has happened so many times in the financial industry that the SEC has actually spoken against using Excel to make decisions at certain levels in the financial industry.
Probably the most relevant thing that came out of that article wasn’t just that Excel causes problems. We know that. It was that the selection of integrated automated software could have avoided this. That’s kind of a big deal, and that’s something that we have seen at ProjecTools. That’s the reason that our clients come to us.
Excel’s used across a broad category and throughout every industry.
Marketwatch produced a report, and their stock lost a quarter of its value in a single day. The problem was traced back to Excel. Excel was being use to calculate the financial metrics. There was a single wrong number in a single cell. The error made the stock lose a quarter of its value in a day. A quarter. Everything that everyone had worked so hard for over the preceding years, a quarter of that was lost in a day. The employees of the company weren’t very happy to see their net worth decrease so much, and obviously the stockholders were terrified.
Once again, this is another one of those things that could have been avoided using software to help check errors and show you where things might be wrong, highlight certain things, or not even allow you to put in incorrect information in many cases. Using a smart system that puts things in their places and checks your work rather than spreadsheets obviously is the right way to go.
It doesn’t just happen in financial companies. Again, there’s an Excel snafu here for $24 million. TransAlta is a Canadian power generator firm. In 2003 the register reported that TransAlta lost $24 million in cash due to errors in bids. This was traced back to some simple cut and paste errors in some of the cells in an Excel spreadsheet. Once again, all of this could have been avoided by using the right software to help track those errors.
If you don’t really dig into Excel and the problems that it causes, you may not be aware of this. Often times, it is too late when people notice. It turns out a lot of other people have had these problems that there’s an entire conference and risk interest group that was founded in 1999. The most interesting thing that came out of this is that 50% of executives actually dismiss staff due specifically to spreadsheet errors. 50% of the executives that they surveyed have actually fired people based around spreadsheet errors. That’s a pretty big number. That means out of every company out there, 1 out of 2 have actually fired people based upon spreadsheet errors. That is a significant human cost.
Overconfidence in the numbers is another problem directly related to spreadsheet errors. You get some numbers and you say, “Okay, well these are right.” And you make decisions. We all have so much to do in our daily lives. They haven’t gotten any simpler. Only more complex so we tend to make quicker decisions. Excel can really hurt you doing that.
The interpretation of data is another interesting point this group brings up and this goes back to a little bit earlier in the article when we talked about labeling. Spreadsheets have no standards to have things labeled in a way that everyone can understand them. Two people can interpret data different ways and that isn’t good. Data should speak for itself.
Also, archiving. Archiving is very difficult to do correctly in Excel and relies upon people manually labeling things, and then inputting the data necessary to archive. We all like humans, but humans make a lot of errors, which is the number 1 cause of Excel spreadsheet mistakes.
Accuracy, Security, Visibility, Teamwork, and Collaboration
Accuracy really comes down to human error, fraud, and over confidence. Excel is really just a blank template. It’s a blank canvas. You can paint whatever picture you want on there. When you open it up it’s a nice pretty thing of white, blank, empty cells and you can mold it into whatever you want with data and formulas and formatting. If your folks that are making these spreadsheets don’t have the proper training, if the spreadsheets don’t have the proper auditing, you open yourself up to a whole lot of accuracy issues, which as we saw can impact the bottom line.
The next thing is security. The current business environment where we heavily value information security. You don’t buy an application unless they have a proven track record of security and demonstrated security measures. The question I’d ask is, does an Excel file that holds business critical data secure? The answer largely is no, they’re not secure. We talked a little bit earlier about the rudimentary security features that Excel offers. Again, they’re rudimentary and probably under utilized so spreadsheets aren’t secure. Then there’s the other aspect of sharing, which we briefly touched on. There are better sources on this than me personally, but spreadsheets aren’t shared securely. They’re often emailed, put on thumb drives, housed on your desktop or laptop, and available on shared files which have some level of security but ultimately these spreadsheets are only as secure as your building and a thumb drive.
The other thing about it is the lack of task management. If we’re circulating work outside of excel that needs to be recorded in excel, how does that happen? Doing squad checks, and approvals of design documents, how does one person know that they need to go in and look at one thing, or check off a comment, or make an addition, or pull something out of a design document? Unless there’s a manual process involved, there is none. The spreadsheet owner calls and says, “Hey, take a look at this.” That person does it.
Relying on manual processes is not something we’re particularly fond of. We don’t believe that’s an effective way to scale or be very efficient. The notification of updates, very closely related to the task management. If there’s been an update in an Excel spreadsheet how does everyone know? The only way you really know is you email everybody, or you give everyone a phone call, or you call a meeting. We’re not particularly fond of this either because this is, again, a manual process.
No understanding of changes made. This one is huge. If we have a spreadsheet circulating, and someone sees an error in it, and they go in and fix that error, very rarely if ever is it appropriately commented saying, “Hey. I saw this. I changed it for this reason. This is what it was. This is what I changed it to,” and then sent it to the next person so they could do the same thing. Then that person, if they see something different, has to make their comments about the changes. It’s very difficult to do and it’s not an ideal solution for that particular reason.
Visibility, and teamwork and collaboration are very closely linked. We went ahead and separated them out just to keep it less heavy. Spreadsheet visibility. The truth is that within spreadsheets, Excel has made some efforts to provide additional security to the spreadsheets to enable people to share and actually be more visible. The truth about that is though, is that people don’t use it that way. It’s a nice feature to have in there, and it’s a good practice to use those, but the reality of what we’ve seen is that it’s simply not used that way.
Spreadsheet teamwork and collaboration. Collaboration is a buzz word of this particular time. Everyone uses it. Everyone says it. There’s a reason behind that, and the reason is we need to collaborate more. We have so much information out there, and we have so many systems, and we’re trying to protect so much it becomes very difficult. For oil & gas and construction projects in particular there are people all over the world working on the same projects. People in various departments working on the same projects that need to access this information within that project to do their job. We really do believe, buzz words aside, this is the age of collaboration.
However, not everyone needs to be involved. This is kind of a catch-22. Not everyone needs access to all information within the project. Restricting that access appropriately is a very difficult thing to do. Use and role based permissions are very difficult to do within Excel, but they’re an absolute necessity for capital instruction projects, oil and gas projects, anything where there’s a ton of people working on it and what they’re working on is of incredible value. Everyone needs to know some information to do their job. This is the flip side of that. Maybe you don’t need to know everything, but you really need to know a couple of things. To that end, if you don’t have access to the information that you need to do your job you can’t do your job. The project doesn’t progress and no one wins in that particular scenario.
Moving past that to revision control. We’ve discussed that to some degree here. Revision control, changes, updates, archiving, these things are incredibly important and it’s something that is seriously lacking within Excel. I have, on more than one occasion, sent around a spreadsheet and said, “Hey, take a look at this and tell me what you think.” Then I get it back and it’s a completely different thing than what I sent out originally. Now, these are for very small things. They’re not for anything of major consequence, but when I get it back and it’s totally different than what I’ve sent, then we have to go back and figure out who changed what when they changed it, and make sense of the changes. That, even on a small scale, is a huge waste of time for me and my collaborators.
Single owner spreadsheets are the norm. We talked about this very basically in the visibility part. The idea is that it would be better to have more people all having access to the individual sets of information that they need, so that no one really owns it, so that no one owns the entire thing. Everyone owns their part of the particular spreadsheet, or data, or data set, but that’s not the way it’s used. That’s not the way Excel is used. In that example I gave at the very beginning, that potential client that came in and said, “Hey, here’s what we’re doing, and here’s how we’re doing it,” with this huge stack of Excel spreadsheets, there was one owner for that spreadsheet. That one owner had been building that spreadsheet. That was his job, to build and maintain that spreadsheet for 2 years. What happens then, and this leads very nicely into our next point, what happens when that person goes on vacation? A single owner spreadsheet creates what we call “corporate knowledge debt.” That is, let’s call that single owner Bob.
Bob leaves on vacation. He goes sailing in the Bahamas. He has no access to a cell phone or his Internet connection. We’re all jealous of Bob. When that happens, work that relies on that spreadsheet can very quickly slow down and stop, or let’s say you do what most companies would do and you turn it over to the person who knows the next most about it. Very easy for them to break said spreadsheet, throw everything into complete chaos. Bob can’t be reached. Progress grinds to a halt. Taking it a step further, what happens if Bob leaves the company? Bob gets a great offer to go work in Indonesia on a totally different project. He’s making a lot more money so obviously Bob takes the offer. Now you’re stuck, because who has access to the spreadsheet that knows anything about it? You have to train someone up on the spreadsheet, maybe even create an entirely new spreadsheet to manage that data.
If you have a system that’s behind the scenes that’s managing all of that, there is not one, single owner of the entire project, they only own their section in the project, then someone like Bob can come in, and manage his very small section of that project. Taking it one step further, let’s say Bob gets hit by a bus. Worst case scenario. What happens then when we can’t replace Bob? Then all of that knowledge is gone and there’s no way to retrieve it. That can put things in a tailspin as well. It is a dark prospect for Bob, but a completely avoidable data/process loss for Bob’s company.
The real takeaway is, if you’re not in spreadsheet hell, relying too heavily on Excel to do complex things can very quickly put you in spreadsheet hell.
Conclusion
To kind of wrap this up I have a couple suggestions. If you have to use Excel, make sure that your folks that are using Excel have the proper training. There are some good online universities, education places where folks can get some free training on Excel. I suggest the Khan Academy. They have a lot of good online courses. The other option is get industry specific software that eliminates heavy reliance on spreadsheets and will keep you out of spreadsheet hell altogether. ProjecTools, for instance. I told you a little bit about the application earlier. All of the products we offer are built on top of a single database that’s shared by all of these applications. It eliminates the rolling up of spreadsheets for reporting and the mess of linking those things together.
ProjecTools project management tools are really seamless and your project management will no longer involve hunting people down, offline communication, and those ugly productivity killers that we’re all trying to avoid. That would be my suggestion – get an application that’s built for your industry and limits spreadsheets to what they do best, work data sets, not managing projects.
If you would like to get started with ProjecTools, please follow this link – https://www.projectools.com/get-started/
