Stop using spreadsheets for rail infrastructure planning

Why you need more sophisticated software for your next major project

For anyone embarking on a project that involves managing data, one of the first things they’re likely to do is open up a spreadsheet. Teams and organisations large and small, across every industry, rely on spreadsheets to manage key business processes and project plans. They have become our default planning tool, and we rarely stop to question whether we’re using the right tool for the job.

Spreadsheets can be incredibly useful for a wide range of day-to-day tasks, and it’s unlikely that any business would look to dismiss them completely. However, when it comes to planning a major rail infrastructure project, it is clear that choosing a more sophisticated application can bring huge benefits.

Selecting the right tool for the East Coast Upgrade

When the Digital Railway team at Network Rail started planning and preparing the business case for the £1.2 billion East Coast Upgrade project in 2018, they knew that spreadsheet models would be unlikely to meet their needs.

The project involved costing the removal and replacement of 10,000 assets over 241 route kilometers, the installation of ETCS, and planning for the disruption of 79 train services. For the seven-year project to take place successfully, the team needed a tool that would reflect its complexity.

Rail BI was the solution. Rail BI enabled the team to quickly carry out cost estimations, manage assets, plan and compare scenarios, and, most importantly, bring all of these data points together to form meaningful and actionable business cases and reports.

The problem with spreadsheets

You might be wondering why spreadsheets were considered insufficient for supporting a project like the East Coast Upgrade. After all, spreadsheets have been commonplace in project planning for many years, and they offer a high degree of flexibility.

But there are important reasons to reconsider this approach, and to question how useful spreadsheets really are when it comes to project management at this level.

Risk of human error

One of the biggest problems with spreadsheets is the fact that they are extremely susceptible to human error. Research has found that, on average, 88 per cent of spreadsheets contain errors – and the larger the spreadsheet, the more errors there are.

JP Morgan’s infamous 2012 ‘London Whale’ scandal, which resulted in a loss of $6 billion, was in part due to a spreadsheet error. It was also revealed in August that a human error in a spreadsheet caused the delay of a major NHS infrastructure project. And recently, 16,000 Covid-19 test results were lost in England due to the limited capacity of an Excel spreadsheet.

As projects scale and spreadsheets become more complex, the opportunity for error grows and grows. The amount of data you need to analyse will grow. The amount of people accessing and contributing to the data will grow. And the extent of damage caused by just one broken calculation or misplaced decimal point will grow.

Lack of troubleshooting and support

You might think that testing and troubleshooting is enough to overcome the issues of human error, but spotting problems within spreadsheets can be incredibly difficult and time consuming. Identifying a formulaic error or retrieving original data sources can be tough even in small workbooks, but as your spreadsheets become bigger and more complex, it becomes a far more painstaking process.

When other kinds of technical problems arise, it can also be difficult to get the support you need. If you’re using a major platform such as Microsoft Excel or Google Sheets, you’re unlikely to pick up the phone or submit a support ticket online if something goes wrong. In contrast, with tools like Rail BI, there is specialised customer support on hand for users – a significant asset when you’re working on high-value, time-sensitive projects.

Difficult to draw meaningful conclusions

Errors aside, sprawling spreadsheets often contain misleading or unnecessary information, while lacking broader context. Research has found that spreadsheets are often misinterpreted, and that users place too much confidence in their spreadsheet data when making decisions.

Cleaning and combining data at this level is also difficult when it is confined to spreadsheets, and it becomes even more complicated to keep project data under control when multiple people are making updates. Identifying who made changes, when, and, more importantly, why, can be a time-consuming process that leads to errors in both analysis and communication.

When it comes to summarising and drawing meaningful conclusions from your spreadsheets for a major project, pulling data from a range of different sources to create clear reports is no easy task. Even if you do manage to keep your spreadsheets free of errors, generating reports is a manual process which can be difficult to replicate across different areas of the project – the opposite of what you want when it comes to scenario planning and comparing options.

For these reasons, the Digital Railway team recognised that spreadsheets were simply insufficient when it came to decision-making for a capital-intensive infrastructure project like the East Coast Upgrade.

What’s the alternative?

Using a tool like Rail BI enabled the Digital Railway team to have confidence in the accuracy of their data. With input validation, automated tests, and suggestion mechanics, there was little room for error. While spreadsheets are useful for simple data and calculations, they rapidly become unwieldy as datasets become larger and more complex.

Rail BI, on the other hand, connected a number of disparate data sources which allowed the team to have a full understanding of the railway network. This was especially useful for things like asset management, as users could see clearly the relationship between individual assets and view supporting information for a complete picture.

In addition, the team had the peace of mind of knowing their data was secure and backed up by industry-leading services, and as Rail BI is a cloud-based solution, there was no risk of their data being compromised through security breaches or other IT issues.