Spreadsheets are Putting Your Project and Portfolio Management at Risk

Doug Vincent
Doug Vincent
October 17, 2020

There is no doubt that the entire construction industry from owners, managers, contractors and suppliers, rely on spreadsheets. While spreadsheets are a powerful tool for statistical, engineering, and financial data management and analysis, the spreadsheet was not designed to be collaborative or customisable to specific industry use cases and as such is not an environment in which the planning and delivery of capital and construction portfolios can thrive.

The current world of users could not ever imagine one in which where they deliver capital and construction projects without excel. Hence many consider spreadsheets the industry standard for owners and managers, with staff in these organisations possessing at the very least a working knowledge of spreadsheets and hence then use spreadsheets for a variety of things.

However, just because there is a critical mass of users does not mean that spreadsheets should be used for the most important aspects of project and portfolio management. The efficient and effective management of capital and construction projects, particularly cost tracking, requires accurate, meaningful and real-time reporting. This involves multiple internal and external players, frequent updates, and the need to manipulate data and report. Key decision makers are no longer satisfied with the incomplete, inaccurate and confusing data represented by projects, programs and portfolios using spreadsheets. Spreadsheets can simply no longer represent the business cases to a strategic detail necessary to move projects or decisions forward and can therefore easily delaying or derailing plans.

I used to have our teams manage all of our projects, all of our documents, our costs, reporting, charting, etc, in Excel. We have this very complex spreadsheet or series of spreadsheets. One person manages it and It breaks all the time, I would never dare go in there.”

Just as Forbes reported to the finance industry in 2013 that “Excel Might be the most dangerous software on the planet”, the engineering and construction industry should be feeling the same way with 90% of all spreadsheets having errors. What’s worse, University of Hawaii has research that reports 99% of spreadsheets thank link more than 10 cells, 10 times, has errors. If you consider the complexity of spreadsheets produced to manage capital and construction portfolios today, the amount of risk being carried is astounding.

To help, we’ve identified six pitfalls for decision makers and program/project managers relying upon spreadsheets to track contracts, key contact admin tasks, project accounting and more.

1. Reputation risks — don’t tell your client you use spreadsheets!

Owners, clients and key decision makers are no longer satisfied with spreadsheets being the primary system for the management of complicated capital and construction projects and portfolios. The question being asked often by clients is;

“What systems are you proposing to handle this complicated portfolio of work?”

If your answer is spreadsheets, you’re setting yourself up for embarrassment and reputational damage. These days, everyone has such great software experiences in their personal lives that they are seeking the same high standard of system capability and design in a work context. Owners, clients and key decision makers are aware that great software is available and that anything else is unnecessary risk. Impress your clients and executive decision makers by identifying systems that can provided live dashboards, boost productivity and increase transparency without touching a single spreadsheet.

2. Clients and execs want data — Spreadsheets are a great way to NOT track your data

Spreadsheets are great information silos and record no time-series information, which is the exact opposite of what we need to create data-driven decision making. Owners and managers want to collect and analyse project and portfolio data to ensure decisions are less subjective and more data-driven. Spreadsheets isolate data in hundreds if not thousands of files and tabs spread across the organisation. With spreadsheets locked away in drives, this makes it difficult or impossible for interested parties to access the value of that data for analytics, insights, benchmarking and more. Organisations need to move off spreadsheets and into a bespoke software system to unlock the value of this data across the organisation to deliver never-before possible levels of data insights.

3. Spreadsheets are easy to mess up and break, putting reporting and decision making at risk

Good project and program reporting requires accurate financial information. From typos to SUMIFs and forgotten negative signs, it’s easy to miss errors and inaccuracies, especially when there are multiple linked or un-linked spreadsheets and workbooks (Read 10 horror stories of the costliest spreadsheet blunders here). As the data and complexity of spreadsheets grow over the life of a project or program, probability of an error increases rapidly as there are many calculations that depend on precedent cells.

The result is a lack of project controls that can cause unknown overruns in Purchase Orders or worse, such as completing mis-reporting cash flows and final forecast costs. Software can help prevent human error with in-built data entry validation and business rules to stop deviations and inconsistencies immediately, with follow-up calls to action to ensure the users are reminded of best practice. Every member of your team may not be sufficiently skilled to check every cell in a spreadsheet with the same level of diligence as the spreadsheet warrior that usually resides over the main file, hence if spreadsheets are all you’ve got best to implement time for stress testing your spreadsheet calculations. This could take hours or days depending on the size of your files!

4. Spreadsheets are like flying blind, offering zero transparency and little opportunities for value-add by senior decision makers

We’ve all been there and clicked on that spreadsheet cell to reveal a giant formula or macro that magically gives us the value in a cell. For key decision makers, they are left with little but to trust that the spreadsheet has been quality-assured and checked for errors across every formula and data point. It is also impossible to identify the make-up of a number by formula following, which becomes impossible as you navigate around hundreds of tabs and D5*C6 calculations. The lack of transparency in spreadsheets and inability for key decision makers to properly interrogate data is a significant risk to the financial reporting and capital allocation of project and portfolios. The result is cost over runs occur unknowingly and no value add into capital re-allocation across portfolios is occurring. Working in a system environment, key decision makers can drill down into data with never possible levels of interrogation to seek answers, have input and get confident in their cash allocation and re-allocations.

5. Spreadsheets are time-consuming and painful to manage or customise

Without proper organization, building and maintaining overly complicated spreadsheets slows down your project management. From the beginning, Project and program initiation/start up is not a turn-key process with spreadsheets, as teams build out their templates and project controls manually. This process can take weeks and is often never completed, with spreadsheets growing more complex and unwieldy over time as staff bolt on new columns, rows, tabs and formulas. For short projects, this can be appropriate, however for any level of regular management this process can reduce the velocity and tempo of your teams’ output and therefore project delivery.

“This ongoing or ‘whole of life’ cost of implementing spreadsheets as a project control for your team is a lot greater than you think”

Get more done with the same or less resourcing by moving into a bespoke software system that reduces start-up time to zero and automates key processes and procedures in the management of projects and portfolios.

6. Spreadsheets lack security

Since spreadsheets are commonly shared through email or data storage devices, this puts client data at risk of broad, unintended dissemination. There is no way to track or control this dissemination and there is no way to know if security breaches have indeed occurred. With many files shared across many users, spreadsheets lack visibility as there is no built-in audit trail and no way to record who made the changes or why. While it is possible to password protect worksheet or workbook elements, this doesn’t prevent willful abuse accessing confidential or sensitive data. There’s also the threat of losing critical information if spreadsheets or workbooks are hacked, infected by viruses, experience data corruption, or aren’t properly backed up or otherwise protected.

“For owner and managers, moving teams off spreadsheets and into centralised, secure cloud based software with detailed audit capabilities greatly mitigates the majority of data security risks”

Concluding, to reduce organisational risk and build an environment in which the delivery of capital and construction portfolios can thrive requires organisations to move off spreadsheets and into fit-for purpose software. The six pitfalls for decision makers and program/project managers relying upon spreadsheets should serve as a wake-up call that organisational data is currently rife with human error, limited visibility and confidence, complex and unwieldy files and high risk of security/audit issues. Readers will have no doubt realised one or more of the six pitfalls and if not, are overly exposed to their unnecessary risk.

“At Mastt we have a vision to eliminate the traditional problems of spreadsheets in project and program management to boost productivity and increase transparency without touching a single document or spreadsheet”

Mastt moves organisations into our secure cloud based system to provide a framework for best practise, automated data validation and complete transparency to eliminate the six pitfalls and start unlocking the value of data-driven decision making for owners.

To learn more about how Mastt can turn your capital and construction portfolio teams into an efficient, effective, data-driven organisation please feel free to contact us at mastt.com or email at hello@mastt.com.au

See how Capital Projects & Portfolios achieve the best for less