Managing $3.7 trillion of project spend. Using a spreadsheet?

A study recently showed that the world must spend, on average, $3.7 trillion on infrastructure, annually, between now and 2035. This investment in our transport, power, water and communications infrastructure is needed to support expected growth in GDP, according to the McKinsey 2017 Study. Yet the track record of delivery of large capital projects is poor with figures suggesting that, on average, such projects are delivered a year late, 30% over budget.

The total budgets of some projects are larger than annual revenues of FTSE100 companies, yet their organisation of people, processes and technology is often far less sophisticated. There are growing calls for large projects to be organised more like companies in the right own right, starting with ensuring the project is directed by someone with CEO-level qualities and skills.

Taking this further, decision making within the project must be based on readily available and actionable data. This data should include the overall performance of the project, with key KPIs being its tracking to plan in terms of budget, time and quality. Here we see common challenges and some of these stem from the uneasy existence of a large project within the context of the overall organisation.

Typically, the project will be using the corporate ERP solution to manage the procurement of goods and services from third parties. There may be a separate timekeeping solution to keep track of internal manpower costs. In some cases, such systems will have been designed with the needs of corporate functions, such as Finance and Procurement, in mind. They may have ignored the needs of managing capital projects, and be focused on the need to report historical financial information, or to implement procurement controls.

Those needs will include planning and tracking progress of the project, ideally using Earned Value Management. The lack of corporate systems to support that, lead to many large projects developing their own homegrown solutions to manage that. In terms of scheduling it is typical to see solutions like Oracle Primavera used effectively in projects of all sizes and the skills often reside within projects to set this up and make it work.

However, the challenge comes in merging the cost, schedule and scope information and managing it efficiently in a joined-up manner, in a way that adheres to the Earned Value Management principles and approach. Projects typically gravitate towards spreadsheet solutions to perform this – the skills to do so existing within the project finance team. However, these solutions suffer from the common issues that arise when spreadsheets are pushed beyond the limits of their original intentions:

  • Agility. Even the best built spreadsheets are rigid in the face of typical project changes, such as the need to modify the Work Breakdown structure, or to add a new cash flow calculation method.

  • Size and performance. For larger projects the spreadsheet’s constraints in terms of size and the speed of calculations supported by the host PC can often render those spreadsheets inefficient and ineffective.

  • Human error. There have been several recent examples of how simple spreadsheet errors have caused financial and reputational damage to high profile organisations. The sort of rigour applied to testing a corporate IT solution is rarely in place on project, or departmental, spreadsheets.

  • Integration. There are no simple mechanisms for integrating spreadsheets with other corporate IT solutions. The result is a manually intensive process to download and format data from other IT systems, and copy them into spreadsheet models.

  • Multiple users. Spreadsheets are not well suited to collaboration and the need to provide access to multiple users concurrently, restricting access to data and functionality depending on the user’s profile.

  • Different versions. Creating version snapshots and alternative scenario versions in spreadsheets often means copying the whole spreadsheet model. Variance reporting over different scenarios is then problematic and changes to the model must be replicated across different spreadsheet models.

The result of spreadsheet solutions then is an inflexible tool to track project performance, requiring a great deal of manual effort to maintain. This does not serve the purpose of our Project Director – to make business decisions on the back of timely and actionable information.

All too many large projects continue to use spreadsheets. The stakeholders in these large capital projects – whether they be company shareholders or taxpayers in the case of government projects – should be rightly concerned.