Zircon Helps Hyder Consulting and Highways Agency Avert Major Reporting Pileup

Operating Systems: Windows
Languages: VBA
Methodologies: Agile, SCRUM
Databases: Postgres
Other: Microsoft Excel
Zircon recently worked with Hyder Consulting to redesign a crucial spreadsheet for the Highways Agency (HA). Despite a number of difficulties and a very tight time frame Zircon successfully delivered a solution, enabling the HA to create higher quality reports with greater ease and efficiency.
Background to the Project
The HA is an executive agency of the Department for Transport (DfT) and responsible for managing, maintaining and improving the strategic road network in England. Their primary functions are managing traffic, tackling congestion, providing information to road users and improving safety and journey time reliability, whilst minimising the adverse impact on the environment. The DfT has a clear set of goals and the HA rigorously monitors its performance against specific targets then creates regular reports to demonstrate progress.

Due to internal restructuring within the HA the management of a key spreadsheet was being passed from one department to another. This spreadsheet was used to create regular reports relating to roadside incidents and demonstrate how well the HA team had handled these situations.

This handover was fraught with problems, but it also provided an opportunity to improve the efficiency of the process and the quality of the output. Hyder Consulting is retained by the HA to give strategic advice, so the project was passed to them.

“This was personally the first time I’d worked with them and they met the brief perfectly…can’t fault them really!”

Thomas Ueber

IT Consultant, Hyder

The Challenge
Thomas Ueber, an IT consultant within Hyder explains that “The existing model ran in a very big excel spreadsheet, with 23 worksheets. It was fed with information from various disparate systems and different departments within the HA. However, they’d implemented some new systems, they were going to add an extra set of statistics, they needed to report on a number of additional features, on top of which the model was being handed over to a different department…so it needed updating.”

The job had to be done in just four weeks, so getting everything right first time was an imperative. “The biggest challenge,” Thomas continues, “was actually getting the knowledge out of them. Because the information came from different systems and from different departments, there wasn’t a single feed of data. The model was being populated from about 10 different sources, then all that information was being pulled into a high level view of how the division had performed over recent weeks. All that work was essentially being done by one person – they were populating the data worksheets and calculating all the averages to produce the high level performance figures.

We needed to understand how they had been doing that, but this process was not well documented and the majority of the knowledge was just in their head! It had all evolved over time, much of the data was being entered manually, and the only notes were mostly handwritten jottings.

On top of that we also had to import all the new figures they needed, and make everything function smoothly. Finally we needed to create a document to explain to the new department how the updated model worked and how to run it…to provide them with what was essentially an operators manual.”

This was compounded by the fact that the Hyder team were already very busy and had no spare capacity.

The Solution
Thomas explains that “We’d worked with Zircon on a few projects in the past, they’re on our preferred suppliers list, and we knew they were perfectly capable of handling this…so we had no hesitation in calling them.”

Thomas worked with a small team from Zircon to gather all the necessary information from the key people within the HA and fully understand what was required. “Once we knew how they had been operating the model, and what was needed going forward, Zircon went off and created the new spreadsheet. It was a ground-up reproduction to incorporate the new features alongside the existing ones. Zircon also produced the documentation to allow the handover to a new department that had no awareness of how it actually worked.”

“We had absolutely no problems whatsoever, which is remarkable, given the set of challenges we faced.”

Thomas Ueber

IT Consultant, Hyder

The Results
The tight deadline meant that Zircon had to get the job completed swiftly, and that it had to be absolutely right first time. “The sample data outputted from their system in the new format was only received a week before the final report needed to be run for that period,” explains Thomas.

“There was only a week’s worth of time for potential bug fixing so it was very tight in terms of risk. Zircon tested the new system, but I did additional quality assurance tests to make sure everything met the clients requirements…which I’m delighted to say it did! The outputs from that report are going to senior people in Whitehall so it would have been very embarrassing if there was a problem with late delivery or incorrect figures.”

Conclusion
When asked to comment on Zircon’s performance Thomas replies that “This was personally the first time I’d worked with them and they met the brief perfectly…can’t fault them really! Everything went very smoothly. We had absolutely no problem whatsoever, which is remarkable, given the set of challenges we faced. It was generally a very successful project that went extremely smoothly.”

Similar Projects

Lets Talk Software

Looking for a team to support your next software venture in Highways?  Zircon is there to help you ensure project success, contact the team today.