Case study 1
KPI reporting solution
Client scenario:
This East Midlands based provider of integrated solutions design,
install and maintain fire protection, access control,
security, intruder alert & CCTV solutions for retirement villages,
care homes, universities & public spaces.
Larger customers demand monthly reports breaking down first-time-fix
and service level met rates, across a team of 80+ technicians and hundreds of
jobs per month, into categories. Management also need to
track technician utilisation to ensure correct manning.
Challenge:
Their legacy job management application could output csv lists of service requests &
attended jobs but these were not assigned to the required reporting categories
and needed to be manually assessed to see if the relevant SLA had been met.
Technician time was not correctly recorded against planned inspection visits
as a protocol of creating a job per system rather than a job per site had
been followed with consequent overlapping times.
Producing reports was time consuming and error prone and the utilisation estimates
were regarded as unreliable by senior management.
Our approach:
An automated extraction process targeting the legacy job management
application was built; the extracted raw data held in a new Cloud SQL database
dedicated to reporting. After consultation with the executive team, agreed the KPI definitions
and the business logic was applied to the raw data within the database layer.
Microsoft Power BI reports focusing on first-time-fix,
technician utilisation and service level met were delivered with customer specific views.
An executive dashboard with highlights from these reports was created.
Results:
50 hours per month saved in preparing monthly reports (value around £15K per annum)
Technician utilisation up 5% through identifying better job scheduling strategies and recognising the potential to operate with fewer technicians whilst still meeting SLA targets (value around £300K per annum)
Improved customer relationships through increased transparency created by sharing real-time first time fix
and SLA met reports (dedicated customer views)
Case study 2
Project control solution
Client scenario:
This offshore wind turbine blade repair contractor, operating on
behalf of original equipment manufacturers (OEMs) and wind farm owners
(primarily energy companies), needs to accurately estimate the costs of potential
repair projects in order to respond to tenders, as well as track incurred costs and progress
as projects proceed.
Projects are site-centric, may run for many weeks and require 20 or more
sub-contract repair technicians. Weather delays are a significant factor
and need to be factored into costings.
Challenge:
Accurately estimating the number of on-site technician days for a project
in order to generate a cost for a tender response, using simple spreadsheets,
was time-consuming, error-prone and the financial risks of overrun were
not easy to evaluate.
Reliably tracking work-in-progress (WIP) was complicated
by a lack of precision (detail) in technician activity recording.
Our approach:
A hybrid rules plus statistical approach to estimating the expected number of
technician days but also providing a probabilistic range for the
estimate using simulation, was implemented.
Microsoft Power Apps was employed to create a mobile data entry application for
technician daily activity capture.
Power Apps was also employed for maintaining a table of technician labour costs
(hourly rates, Per Diems, mobilisation payments etc)
Power BI reports for WIP, labour costs incurred and projections of
expected completion date and cost versus budget were delivered.
Results:
100 hours per season saved in responding to
project tenders through automation of cost production.
(value around £5K per annum)
Profit margins on fixed price contracts increased by 23% through elimination of loss-making projects
(previously won by inadvertent under quoting) and more efficient manning plans.
(value around £80K per annum)
Case study 3
Workflow automation
Client scenario:
This market leader in the long-term storage of locomotive trains, that are
between leasing contracts, is required to undertake periodic inspections,
and provide condition reports to the asset owners. Each class of train
has a series of inspection types, each with a detailed protocol.
Inspections were recorded on paper forms and then reviewed by a
senior engineer who recommended any required remedial action.
Spreadsheets listing defects for each fleet were maintained & shared with
the asset owners.
Challenge:
This process was time consuming and errors accumulated in the spreadsheets
with the same defects being counted multiple times.
Defect records were not updated reliably when remedial work, undertaken by
a separate team, was completed.
The use of a spreadsheet as a database made it
difficult to see trends in high impact categories such as corrosion or mould.
Our approach:
A digital forms application which guided inspectors through the structured protocol,
including taking photographs (not previously done), was introduced. Each fleet and
inspection type was served by a distinct process. Processes were set up to guide remedial repair processes.
Data is now extracted by API and held in a dedicated reporting database.
An AI process was implemented, based on a retrieval-augmented-generation (RAG) protocol,
to recommend remedial work and write a summary on the overall condition of the train.
A PDF summary document, including photographs, is automatically generated and saved
in Cloud storage. Email alerts are automatically generated to the repair team.
Microsoft Power BI is employed to summarise activity, showing that the inspection schedule had been adhered to,
and to display trends in defect categories and ages.
Results:
Over 100 hours per month were saved
in processing inspections (value around £50K per annum)
Client satisfaction improved because of the greater transparency provided by real-time and more reliable BI reporting, leading to
new contract wins and existing contract extensions (value around £100K per annum)