Asset Integrity and Corrective Action Plans
It's not merely enough to inspect equipment — we need to ensure that these recommendations get to the right people and that these people get the support they need to ensure that we take the correct actions in order to address every finding.
From identifying what needs to be inspected, to scoping the work correctly, to managing all our expected deliverables, to distributing them to the correct stakeholders, to tracking the phases of project management, to providing assurance and validating evidence — there is an entire lifecycle behind every inspection report that goes beyond just receiving a PDF in an email.
And each and every step is vital to ensure that we've taken every measure we can to preserve the integrity of our assets.
Engineering Equipment Database
Some companies spend billions on their assets — yet all their technical details end up scattered on ancient and forgotten spreadsheets littered across the organisation.
Every company should have a Single Source of Truth for the technical details of its equipment. And to build that database, we need to develop a continuous process of collating information from all across the business, in varying levels of detail and accuracy, all while ensuring that we are making use of the best information at our disposal.
Just having an Excel table of the latest data isn't enough — we need a history of what we know, when we knew it, how accurate and up-to-date it is, as well as what data we still need to find out.
Your assets have a lifecycle, and so should their accompanying data.
Asset Verification
Every company needs to continuously verify that all their assets are still where they expect them to be. But how do we keep track of this constantly changing database of tens of thousands of assets, which could be spread all over the country?
We could ask the accounting team for their Asset Register, or the maintenance team for their Equipment Master Data, or the sales team for their list of Customers.
To ensure that we've covered every possible asset, we should use modern ETL tools to collate, consolidate, clean-up, and build a data model. We then analyse and query this model in order to provide assurance that every programme and process we put in place provides full coverage of all our responsibilities.
Maintenance Schedules + Supply Constraints
As companies and industries mature, the compliance and regulatory demands naturally increase. We find ourselves needing to more frequently remove equipment from service to inspect and maintain, all while operating under the constraint that we minimise interruption to systems that were never designed and built with these demands in mind.
When these project plans extend for months and sometimes years, we need to spread out the disruption to the business, while at the same time mitigating the risk of potentially delaying inspections. We need to schedule our plans concurrently, sequentially, and overlapping in an optimal arrangement.
And this is not a once-off plan set in stone — we need to continuously evaluate to make sure that we are still following their best solution.
Background
Excel has been with us for over three decades, and it isn't going away anytime soon.
Processes and workflows have a tendency to develop a life of their own, and what was once cutting edge can, left unchecked, slowly but surely accumulate unforeseen repetitive steps and tedious workarounds to keep working.
The computer is a tool that should be adapted and molded to assist and serve the user — the user should not have to bend and break to tolerate the computer's quirks.
Being able to develop tailor-made VBA tools can reduce the pain points and friction of these processes, allowing your subject matter experts to spend less time fighting against software, and more time gathering insights and understanding from their data.
Problem
A process delivers documents to a user in a format and structure that needs to be changed before the next step. For the last 10 years they've been following a rigid process, step-by-step, from the person before them. This takes time, and can lead to mistakes if they are interrupted or distracted. And it needs to be done every time the report is run.
Users love keeping lists in Excel to track when last they've done reports. But the dates in that list never speak to where those reports are are actually sitting. What if someone made a typo in the date? What if they updated the wrong row? What if someone deleted the report?
One of the benefits of Excel is how rapidly users can create solutions to new business demands. But the average end user does not know or want to care about things like normalisation and data cleanliness. They just want to be able to update a table in Excel, and have it populate a graph with the correct results.
Solution
Moving to a culture of automating reports can take many years. Until you are fully leveraging BI, you could at least automate the Excel steps using VBA. Automation removes room for error, because the computer will always perform the exact steps, in the exact order, tireless and vigilantly.
Being able to export a list of files from a SharePoint library is powerful, but it on its own it does not help the end user. What they want is their existing "database" to be updated automatically, without having to fuss over another spreadsheet someone else is giving them.
Features like Data Validation and Conditional Formatting are incredibly useful, but they take time to set up, and when users don't make regular use of them, they can become a chore. Making tools that allow users to tell the computer what they want instead of having to learn the computer's jargon means they'll be more likely to produce spreadsheets with quality data.