Modifications Improve Accuracy of Client’s Project Reporting Mechanism

Modifications Improve Accuracy of Client’s Project Reporting Mechanism

Zircon Improves Accuracy of Client’s Project Efficiency Reporting Mechanism

Operating Systems: Windows (XP)
Languages: VBA
Other: Microsoft Excel

 

Our client had conducted a study of potential ways that cost savings could be delivered through framework contracts. Main contractors would be prepared to reduce their rates, providing contracts are signed in advance for a defined number of years, based on the projected workload of upcoming projects.

The monitoring and reporting of efficiency savings for projects is handled through Efficiency Scorecard spreadsheets built in Microsoft Excel 2003. Separate scorecards are maintained for each regional area, which generates a significant amount of duplicate data that required manually updating on each spreadsheet. In order to ensure that these scorecards provide a complete and accurate project efficiency and reporting mechanism, a number of modifications were required.

Having experience of working with some of our client’s other tools, alongside previous experience of redesigning similar spreadsheet documents for some of our other clients, Zircon was in the perfect position to perform these modifications.

Zircon’s initial responsibility was to focus on removing the redundant information for the Scorecard document and updating the system reference data to acknowledge any changes made during this process. Upon completing this redesign activity Zircon was to perform an audit of all the calculations in the spreadsheet to confirm that they were correct and would refer back to the appropriate reference data.

Throughout the audit process each time a defect was identified and corrected a detailed log was made to ensure the client was fully aware of each change. In addition to the documentation of defects the client had asked Zircon to document every calculation performed by the spreadsheets, and the data they use, to assist our client with understanding and maintaining the altered document.

Prior to the conclusion of the first development stage a review of the upgraded Scorecard was carried out by the client so that Zircon could make any further alterations to ensure the tool operates as specified. Following the completion of the first phase the Efficiency Scorecard spreadsheet was delivered to the client with the data from each separate regional spreadsheet document collated into one place.

The second development phase found Zircon creating a reporting tool that would extract data from the redesigned Scorecard spreadsheet to display information on regional and national efficiency savings. The client had also asked Zircon to integrate a separate Lead Development Manager’s Efficiency Tracker spreadsheet into the Scorecard document so as to avoid the duplication of effort when populating data.

Similar Projects

Lets Talk Software

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

Timetable Data Modifications Sets Up Simulator to Improve Passenger Experience

Timetable Data Modifications Sets Up Simulator to Improve Passenger Experience

Zircon Makes Timetable Data Modifications For Simulator Set Up to Improve Passenger Experience

Operating Systems: Windows
Languages: VBA, XML
Other: Microsoft Excel
Our client is responsible for the operation and maintenance of London’s underground rail network. As part of a project to upgrade one of these underground lines our client was investigating the possibility of implementing an Automatic Train Regulation (ATR) facility, to be provided by a third party supplier.

ATR aims to provide passengers waiting at platforms with a regularly spaced service. This is achieved by allowing trains to vary their speed and the length of time spent dwelling at platforms, within a defined margin, so that each train arrives and departs at regular intervals without bunching up or spreading too far out.

In order to test the possibility of utilising an ATR facility our client had planned to use a Total Systems Simulator (TSS) that had been fed a complete data model of the lines timetable. Unfortunately for our client, the timetable data available at the time of the project did not cater for the requirements necessary to allow ATR to function.

Zircon were engaged by the client to develop a new timetable that would achieve the 33 Trains Per Hour (Tph) target desired by our client, whilst offering the required flexibility to allow ATR to regulate the service.

The solution proposed by Zircon involved taking an existing 33Tph timetable an modifying it in such a way that it would still be capable of achieving 33Tph, whilst coasting would be removed and the length of time spent dwelling at stations would be maximised.

Zircon designed a Microsoft Excel spreadsheet application that made use of VBA macros to read in the existing XML timetable data and place it into worksheets, where the coasting level and arrival time values would be modified in order to remove coasting. This modified data would then presented in a new worksheet. In addition to modifying the existing timetable data, the macros were used to verify and validate the new timesheet by drawing up charts to graphically represent the effect of any changes to arrival and departure times.

Once the timetable data had been successfully modified it was written out into a new XML file ready to be tested by the TSS system.

Similar Projects

Lets Talk Software

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

Zircon Helps Hyder Consulting and Highways Agency Avert Major Reporting Pileup

Zircon Helps Hyder Consulting and Highways Agency Avert Major Reporting Pileup

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.

Zircon’s Expertise and Agility Proves Invaluable to Invensys Rail

Zircon’s Expertise and Agility Proves Invaluable to Invensys Rail

Zircon’s Expertise and Agility Proves Invaluable to Invensys Rail

Operating Systems: Windows
Languages: C#
Frameworks: ASP.NET
Comms/Networking: Serial Communications, RS485
Other: Microsoft Excel, BS EN 50155
Zircon recently worked with Invensys Rail to develop an on-train data logger and decoder for the upgrade of London Underground’s Victoria Line. This enabled them to successfully get a new control and signalling system into service within a tight time frame.
Background To The Project
Invensys Rail was recently awarded the contract to provide a new signalling system and control centre on the Victoria Line of the London Underground. Part of this project involved developing and installing automatic train control equipment on the new rolling stock – an automatic train protection (ATP) unit and an Automatic Train Operation (ATO) unit.

Although there is a driver on the train who can handle all operations manually, these two units will do everything completely automatically – pulling away, braking, responding to signals, operating the doors, and so on. Given this fact, the system had to be totally reliable and dependable.

As with any new system there are bound to be teething problems, so rigorous testing is essential – any anomalies must be identified, rectified, then retested. This process involved the collection and analysis of huge quantities of data so that engineers could accurately observe exactly what was happening – this information was to be captured by an on-board logger.

“Zircon… overcame a variety of different technical problems to provide an excellent solution within a tight time frame.”

Andy Chapman

Automatic Train Control Project Engineer, Invensys Rail

The Challenge

A few months before the system went into service it became apparent that the logger, which was not supplied by Invensys Rail, was not performing as well as expected. All the elements of the Invensys signalling and control systems were successfully communicating with each other, but the information was simply not being recorded in a way that made for sufficiently detailed analysis.

Andy Chapman, Automatic Train Control (ATC) Project Engineer overseeing this aspect of the contract at Invensys Rail, explains that “we decided that a new logger was required. Whilst we had the people in-house to develop it, they were busy, and we wanted to be sure the task would be successfully tackled in the time frame. So we decided to outsource the job.”

The Solution

“To be honest, the list of possible companies capable of doing this work was pretty short,” continued Andy. “We regularly work with Zircon, and were confident they could do everything that was required.Instead of spending a month drawing up a detailed specification of what was required we took a more flexible approach – outlining the situation in general terms, then moving forward together on a day-by-day basis with constant discussions and updates.

This collaborative way of working saved an awful lot of time and proved very efficient – much better than giving them a huge document and then hoping they’d come back with a suitable solution in a few months.”

“This also allowed us to develop two solutions in tandem. The first was a quick and easy option, if you like, running on a standard laptop, just to get the system into service. It did everything that was asked of it, but long term it wasn’t suitable – laptops don’t last forever, and aren’t designed for use on trains.

So we also developed hardware that would run off the train’s power supply, rather than batteries, plus was smaller and more robust. Using the laptop solution got us into service on schedule, while giving us more time to develop and thoroughly test the unit we’d use for the long term.”

Further Developments

Every day three Victoria Line trains now carry these loggers. They generate a huge number of files which are downloaded every night and sent to the Invensys Rail office in Chippenham, so anomalous events can be identified and analysed, then improvements made.

Zircon were also asked to develop a decoding tool that made this task easier. “We get large text files from the trains” explains Andy. “We needed something that would import this data into Excel so we could investigate everything with less difficulty. We worked together on this, through a number of different iterations. The decoder we eventually came up with is working extremely well and we’re still using it today.”

“The decoder we eventually came up with is working extremely well and we’re still using it today.”

Andy Chapman

Automatic Train Control Project Engineer, Invensys Rail

The Results

Andy comments that “we found ourselves in an awkward situation that was not of our own making. Zircon not only overcame a variety of different technical problems to provide an excellent solution within a tight time frame – they also understood that a very flexible approach was required, given the situation and the fact that the scope of the project was continually changing. It was demanding for us, and for them.

Because we have developed a very close relationship with Zircon over the years we were able to collaborate successful and rise to some very awkward
challenges. The outcome is we got the system into service, safely and without taking risks, on time. That’s a great result.”

Similar Projects

Lets Talk Software

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