Reasons to Stop Relying on Spreadsheets in Project Management
It’s not hard to see why spreadsheets are heavily used in the project management industry. They are free or inexpensive, easy to use, involve minimal training, and often do not require strict project processes and procedures to be put in place. Spreadsheets offer quick adoption and are a cheap tool for managing small projects, but the biggest question you need to ask is whether you can trust Excel spreadsheets with your most important asset.
When managing a multimillion dollar project or even a huge portfolio of projects in the billion dollar arena, are you entirely confident in the project data it offers and do you have absolute control? How much effort is expended each month maintaining spreadsheets and feeding a reporting cycle? Oftentimes, these spreadsheets started off easy, but with time they grow drastically and become difficult (if not impossible) to maintain and manage.
Risks of Using Spreadsheets in Project Management
When it comes to project management, Excel spreadsheets are one of the most popular tools on the market. While MS Excel and Google Sheets are flexible enough to create highly specific functions from scratch, they come loaded with an inherent risk of error and tend to become complicated and unmanageable as projects grow.
- Inevitability of errors
- Limited by project size
- No safeguard or data validation
- Lack of security
- Resource dependency (single point of failure)
- Corporate workaround
Spreadsheets & Their Inevitability of Errors
Study after study has provided evidence that most spreadsheets are likely to contain errors within their data or formulae. According to Dr. Raymond Panko’s What We Know About Spreadsheet Errors, “All in all, the research done to date in spreadsheet development presents a very disturbing picture. Every study that has attempted to measure errors, without exception, has found them at rates that would be unacceptable for any organization… With such high cell error rates, most large spreadsheets will have multiple errors, and even relatively small ‘scratchpad’ spreadsheets will have a significant probability of error.”
While mistaking a single digit may not seem like a major error, it can create significant consequences in project management; throwing off an estimate or forecast by even a fraction of a percent can potentially affect the final results of a decision. Meaning that a wrong choice may be made, risking the entire success of the project.
Outlined in the impact of errors in operational spreadsheets are six types of potential errors in spreadsheets:
- Logic Error: A formula is used incorrectly, leading to an incorrect result
- Reference Error: A formula contains one or more incorrect references to other cells.
- Placing Numbers in a Formula: One or more numbers appear in formulas.
- Copy/Paste Error: A formula is wrong due to inaccurate use of copy/paste.
- Data Input Error: An incorrect data input is used.
- Omission Error: A formula is wrong because one or more of its input cells are blank.
Any of these errors could be fatal to the outcome of a project as vital statistics and key performance indicators may become inaccurate.
Spreadsheets are Limited by Project Size
While it may be possible to manage simple projects using Excel, it does not scale well to larger projects. Using spreadsheets, cost managers can develop project cost worksheets, import actual costs from accounting, and then manually enter progress information. While this is somewhat practical on one simple project, it does not scale effectively. The more information being entered, the more complicated a spreadsheet becomes.
As a project increases in size, more people will be needed to work on the project, meaning more people will need a hand in the spreadsheet. What this usually means is the spreadsheet will start being emailed from one person to another with multiple versions being created and no one ever being certain they have the latest, most up-to-date version.
Larger projects also come with larger risks. Errors become far worse when the numbers you are working with get higher. Take for example accidentally missing a “0” on an $1000 estimate. You end up putting $100 in the spreadsheet, a $900 mistake. Not great, but not the end of the world. Now think about an $100,000 estimate. Miss a “0” and you are looking at a $90,000 mistake. It starts to look a lot more like the end of the world.
Spreadsheets Lack Safeguarding, Reliability & Authenticity
The discussion of spreadsheets and their lack of reliability is not a new topic. It has been a recognized issue since the dawn of Excel.
Some studies have found that as many as 88% of spreadsheets have errors. Spreadsheet problems and errors are so vast that there are organizations and groups dedicated to eradicating them, like the European Spreadsheet Risks Interest Group.
Even with a second set of eyes reviewing the data as a safeguard, mistakes are often still made. According to Strategy @Risk, which develops models for support of decision-making under uncertainty, “Code inspection experiments show that even experienced users have a hard time finding errors succeeding in only finding 54% on average.” That leaves 46% of errors wreaking havoc on your data.
Source data will often come from a number of different systems. If and when this data somehow ends up in a spreadsheet, how does management know the numbers are the same as the official numbers? Unfortunately, there is no easy way to check the accuracy of data being entered from disparate systems.
In Turning Spreadsheets Into Corporate Data by Bill Inmon, he references that “At the root of every value found in spreadsheet data is a person who placed the data there. Only that person knows if the data on the spreadsheet is accurate or not.” It is never a good idea to place blind trust in the accuracy of data – this leaves you open to risk of errors or potentially fraudulent data entry.
Spreadsheets Lack Security
A topic at the forefront of all our minds is security and safety of our data. Not only is a spreadsheet easy to manipulate, hack into, and corrupt, but it is also possible to lose vital data. How often is your data backed up? Who is watching to make sure that data is not corrupted? What is the process that you have in place if you realize that your data is corrupted? How much rework will have to be done to restore the necessary information?
Ron Ben Natan references real-life examples of such security issues in Implementing Database Security and Auditingsaying, “There have been many real cases in which more damage was done using spreadsheets than you could imagine. A well-known case involves a major financial institution that, because of a flawed change control process, allowed the introduction of an error that resulted in a $1-billion financial statement error. Another true example is a trader who committed fraud by changing spreadsheet macros and updating data in a database that was not being audited for changes.”
Spreadsheets often Equal Resource Dependency
Let’s say you have an Excel guru who has put together a macro-enabled spreadsheet that will help you manage your project. That’s great! But now what happens if that person retires or moves to a different department? You are resource dependent and are likely going to be left with an unusable spreadsheet. This allows for a single point of failure.
When there is no effective option in place to manage a project, end users may look for creative workarounds to solve their needs.
According to Peter Schroer, Founder of Aras Corporation, an engineering and manufacturing software and management organization, “IT can’t deliver, so what do end users do? They create the workaround, aka a spreadsheet. It’s the corporate cure all. When something can’t be done in the enterprise business system, we just create a spreadsheet for it. Untold numbers of them store vital IP and create the perfect storm for data integrity and compliance risks.”
While workarounds have their benefits, they are far from an ideal solution. Workarounds should only be used as a temporary solution before a permanent solution is found, but often organizations end up using the workaround far longer than they should. As time passes, more problems will likely arise resulting in the need for more workarounds. It becomes a vicious cycle until your project is ultimately being managed by a system of complex workarounds.
Shifting Away from Spreadsheets in Project Management
Whether it is on a single project, a program, or across a portfolio of capital projects for the enterprise, an integrated project controls strategy can give you a competitive advantage while producing bottom-line results to your organization.
Today’s executives and stakeholders demand transparency to the progress of your project and insist on accurate forecasts. Having a central data repository that is automatically updated for reporting can save time. Critical decisions are made on the accuracy of this information and we have already shown that managing project cost and risk with spreadsheets is not an effective option. It creates silos of information that introduce inaccuracy, process friction, and unnecessary overhead that degrades margins. Using a dedicated project management system solves many of the problems associated with managing projects using spreadsheets.
Reasons to Use Project Management Software over Spreadsheets
- Data Validation
- A Reliable Audit Trail
- Minimize or Eliminate Data Silos
- Project Scalability
- Ease of Use
- Defined Roles & Responsibilities
- Efficient Reporting
Data validation is integral to the proper management of a project. According to Philip Moncrief in Project Management: The Secrets of Success, “You can never make up the time lost from having to correct wrong data…project data needs to be analyzed and validated.”
As mentioned earlier, human error is one of the most concerning problems with spreadsheets and a mistake can affect your entire project. Moncrief adds that “At the start of every project, the basic project needs to be analyzed and validated. It is a lot easier, less expensive and less disruptive to do it first rather than later.” Cost management tools, like ARES PRISM, offer data validation to ensure that all data is entered correctly the first time.
Maintaining a complete audit trail for your project work is imperative at all times. Data is continually changing over the course of a project and using Excel you have no method to see what changes have been made, nor who approved them. This makes it nearly impossible to discover where or when a mistake was made, as well as who approved a change or why. With a cost management system, you can monitor changes and see when they were made and by whom. This means you are always able to backtrack through your data to confirm accuracy.
Describing how Excel spreadsheets just might be the most dangerous piece of software out there, Alex Hern says, “The very ease of use that Excel offers — allowing people with no programming experience to knock together what are, in effect, relatively advanced applets — also makes it dangerous to use in most sensitive situations. There’s no debug, no audit trail and no way to test why a spreadsheet returns the value it does.”
Another item to consider is the integrity of prior period data. In a spreadsheet, you can easily change it because there is no audit trail. So, if someone on the team does not like a number, they can go back fifteen periods and change it. However, with a cost management system, period close functions prevent you from changing historical information which increases data integrity.
Minimize or Eliminate Data Silos
In a project controls environment, integration of project, program and portfolio data is essential to make key business decisions. Spreadsheets leave data trapped in silos leaving projects vulnerable to errors.
Doug Rose explains in Data Science: Create Teams That Ask the Right Questions and Deliver Real Value, that “The problem with silos is that they make it very difficult to have a holistic view of your data…data is the key material for finding insights and creating new organizational knowledge. To be data-driven, you need to have free access to data.”
With an integrated project management system, data is pulled from various sources and brought into a central data warehouse. Integration gives project managers the ability to gain a deeper understanding of how the project is progressing and ultimately then make better decisions to move the project in the best direction.
Ideally, every project should be managed in a consistent manner. Consistent processes, consistent reporting, consistent calculations. This can be difficult to achieve when projects are being managed with spreadsheets.
Frank Toney discusses this further in The Superior Project Organization: Global Competency Standards and Best Practices saying, “Bench markers report that an area of emphasis for project organizations is the development of standardized methodologies…Many companies dedicate considerable time to this process, although the easiest approach is to utilize off-the-shelf packages.”
Off-the-shelf project management software, helps organizations to standardize their processes and create reports in the same format with the same calculations every time. Toney adds, “Consistency of reports and reporting templates between projects, divisions, and global units is important. Dissimilar reporting formats increase the difficulty in understanding the data and information, reading time is increased, and combining or rolling up the data into the master report is more cumbersome.”
The bigger the project, the more complex the spreadsheet. This increase in complexity limits how scalable the solution is. Project management tools are capable of managing projects of any size without altering the processes used. According to the IT Governance Institute’s Approach to Spreadsheets, “When assessing the likelihood of error arising from a spreadsheet, organizations should consider the spreadsheet’s complexity, the number of users and the frequency of changes made to the spreadsheet.”
Ease of Use
While at first glance spreadsheets seem simple to use, increased project size increases complexity. This complexity means that the spreadsheet becomes almost harder and harder to use and often the creator is the only one that understands how the spreadsheet functions. Ease of use is also important for the adoption of software, if using a tool becomes overly complicated, team members will be less willing to use it. Ease of use is a major factor in software adoption.
Defined Roles & Responsibilities
With a cost management system, there is less time spent gathering and formatting spreadsheets which allows project managers to have more time to conduct data analysis. No more chasing down progress data from the field or formatting and summarizing spreadsheets for executives. Instead, cost management professionals can focus on the core nature of their job which is to advise and execute a cost management strategy on a project. After all, they are much more than project report writers.
Even if you are able to track project data using a spreadsheet, the real breaking point is the time consuming, labor intensive reporting cycles. You take your spreadsheet, create a chart, create a table, add it to your PowerPoint slide, present it at a meeting. Now try multiplying that by a multi-billion-dollar portfolio of projects. With a cost management system, you have one data repository (instead of separate spreadsheets that need to be later combined), multiple users can make changes to the data and it is tracked with an audit trail, and reports can be easily generated each month at the push of a button. Many project controls software solutions also provide live snapshots of the project. This up-to-date data means trends can be recognized faster and risks can be avoided before becoming problems.
Using Excel also means that reports will likely not follow a consistent format. Consistent reports make it easier for project managers to quickly understand the data and make decisions accordingly. A project management software, like ARES PRISM, makes all the difference when it comes to clear and consistent reporting.