Excel Case Studies

High Output Track Renewals

ExCase1

Problem

The project remit was to analyse the surveyed Overhead Line geometry to determine existing non compliances, identify new or exacerbated existing non compliances as a result of incorporating the proposed track design and undertake design to minimise non compliances. These separate design activities were laborious, error prone and excessively time consuming resulting in extensive checking/rework and late delivery.

Solution

Utilising the assured business design spreadsheets an automated project interface was developed and embedded to execute the multistage analysis utilising single common data source. The tool automatically incorporated the track design, scheduled non compliances for all phases and automatically revised the overhead line vertical and lateral geometry design.

Benefits

Integration & automation of design processes reducing timescales by 80%
Reduced checking due to assured data concurrency
Automation allowed the project to utilise lower grade resource

Dropper Calculator

ExCase2

Problem

Droppers are the numerous vertical supports between the contact and catenary wires installed between overhead line structures. It is essential that each dropper is calculated, manufactured and installed to the correct length for optimised interface between the Overhead Line and pantograph. Traditional business tools only calculate a span per spreadsheet requiring manual data entry of track and overhead line geometry. This is time consuming and error prone, requiring extensive checking of all inputs.

Typical number of manual inputs per span = 7

Typical number of individual calculations per dropper = 4

Typical number of droppers per 1500m wire run = 150

Solution

Development of Dropper Calculator utilising existing approved and assured project data (Excel, XML or DGN) to calculate droppers for any number of wires runs accounting for standard and variable in-span features.

Number of manual inputs per wire run = 2 (Wire run features)

Benefits

Automation reduced timescales by 95%
Reduced checking due to assured data concurrency
Automation allowed the project to utilise lower grade resource

Bill of Materials Generator

ExCase3

Problem

Overhead Line material schedules are created for each structure location and take many forms and developed using various software platforms. This data is critical for accurate procurement and the exchange of data from designer to contractor is conventionally in a format which can not be utilised. Therefore the compiled schedule and disassembly of assembly is a manual task undertaken by the Contractor. Due to material lead times procurement is often commenced at risk and prior to 'Approved' status for the applicable design.

Solution

Bulk extraction of material allocation data from design documentation and breakdown of MK3B, Series2 and UKMS materials into agreed format. This removes the requirement for manual data processing and synergy between design and procurement data.

Benefits

Assured procurement data with data extraction/breakdown process
Eliminates requirements for manual data entry