9      Asset Management Tools. 9-1

9.1       Using this Framework. 9-1

9.2       Overview. 9-1

9.3       Asset Register Form.. 9-3

9.4       Asset Register as a Dynamic Tool 9-6

9.5       Geographic Information System (GIS) 9-9

9.6       Other Asset-Related Systems/Tools. 9-11

9.7       Modelling Optimization. 9-14


9            Asset Management Tools

9.1       Using this Framework

This framework is intended for municipalities of all sizes and maturity levels. The use of the maturity diagrams within this framework can help municipalities identify their current levels of maturity for each AM area. In addition, the diagrams provide possible approaches for municipalities to undertake in order to move to a higher level of maturity over time. Adaptations of the following diagram are used throughout this document to summarize maturity levels according to the themes and questions explored in each chapter:

 

This document is intended to help municipalities make progress on their asset management planning. By enhancing the readers’ understanding of asset management maturity, they can more accurately determine their current, and work toward achieving the desired or appropriate, level of maturity for their municipality.

The asset management framework can be likened to a continuum, whereby municipalities should aim to implement the components described in a subsequent maturity level. For example, municipalities that are not practicing asset management should strive to meet components at the basic level, and likewise, municipalities that currently meet the basic or intermediate levels should strive to advance their practices to meet the components of the next level. However, it should be noted that during this self-assessment process a municipality may decide to skip over maturity levels (i.e. move from basic to advanced, skipping intermediate). This is perfectly acceptable. Further, not every municipality will need to strive for the highest level of maturity in every area. For example, it may not make sense for a small municipality to meet certain advanced level components.

Readers can use the following descriptions of the maturity levels to guide their assessment throughout the various sections of this framework:

Municipalities that are not undertaking the components described in a particular section of this framework should focus on meeting the basic level requirements outlined in the maturity level diagram.

At the basic level of maturity, a municipality is undertaking the components of asset management shown in blue and will take steps to advance their asset management by implementing the components described under the intermediate level heading.

At the intermediate level of maturity, a municipality is currently meeting the requirements shown in yellow and to advance their asset management will take steps to implement the components described under the advanced level heading.

At the advanced level of maturity, a municipality is currently meeting the requirements shown in green.

These maturity framework visuals are found throughout this document. Preceding all maturity level diagrams is a self-assessment question for the reader to consider to help determine where their municipality best fits within the framework.

9.2       Overview

In the context of this chapter, asset management tools refer to any tool that allows a municipality to more efficiently and accurately manage and execute actions throughout the course of asset management planning. These tools often support data management and modelling of asset lifecycle needs to ensure that available data is used effectively to make informed decisions.  They can vary greatly in complexity -- from simple spreadsheets to sophisticated software that can fulfill numerous functions within and beyond asset management. Each municipality will need to determine which tools are most appropriate for its asset management needs given its circumstances and desired asset management maturity level.  Some municipalities may have staff with adequate technical skills and time capacity to develop tools internally, while others may have to rely on commercially available off-the-shelf software to meet their needs. The purpose of this chapter is not to advocate for the use of certain tools versus others. Instead, this chapter attempts to highlight what these various tools can accomplish for municipalities and some of the specific tools that municipalities should consider using as they determine what is appropriate for asset management purposes.

Similar to general trends in the IT industry, many asset management software tools have migrated from desktop applications, maintained locally by a municipality’s IT staff/department, to cloud-based services. While these tools may not be physically located on a municipality’s premises, the data are still generated, maintained, and utilized by a municipality’s staff, which may lower the ongoing implementation resources and costs for a municipality. Whether a tool is a local or cloud-based system, it will ultimately make it easier for a municipality to effectively execute their asset management process.

Additionally, it is critical that any asset management tool used by the municipality has the ability to be versioned, indexed, and backed-up. Data loss disasters can still occur, but they can be avoided or mitigated with proper systems and controls in place. Any tool employed by a municipality should have the capability to perform these vital functions.

Infrastructure for Jobs and Prosperity (IJPA) Act and O. Reg 588/17 Requirements

While O.Reg 588/17 does not specify a need to use “tools” in a municipality’s AM process, there are a number of requirements within the regulation that may become less time and resource intensive if a municipality considered the use of various tools to assist in meeting the requirements.

9.3       Asset Register Form

Asset information is a key input into the asset management process. Therefore, a well-structured asset register that can be utilized by all relevant municipal staff and that ensures data integrity is foundational to good asset management.

In what form is the asset register kept?

Background

As discussed in Chapter 3, an asset register is a list of municipal capital assets and related attribute data (e.g. cost, condition, quantity, size, etc.) for each individual asset. The mechanism by which the asset register is housed can vary greatly in form -- from state-of-the-art integrated asset management programs, to spreadsheet solutions (e.g. Microsoft Excel), to contracting out the maintaining of asset data to a consultant. Further, it is common for municipalities to have more than one repository of asset data, and therefore different technologies may be in use at one municipality.

Municipalities must decide how they will develop and store these inventories, given the availability and usability of the various computer software and spreadsheet solutions. As part of the decision-making process, consideration should be given to either the integration of asset register(s) within a municipality, or a reconciliation of differences between alternative systems where they overlap. Having confidence that the asset register is accurate, timely, and complete is critical in ensuring optimal use of its information for asset management planning purposes.

Levels of Maturity

In what form is the asset register kept?

 

At the basic level of maturity, staff create an asset register with assistance from an external consultant. The complete register, or inventory, is likely housed externally with the consultant. The asset inventory is used to generate outputs that can be included in the development of a municipal asset management plan.

At the intermediate level of maturity, the development/maintenance of the register is completed by municipal staff and is housed internally in a database format (e.g. Microsoft Access or Excel spreadsheet).  Direct involvement by staff in the creation of the asset register ensures a better understanding of the resources required to keep an up-to-date inventory. The outputs from the register that feed into the asset management plan may be more customized to the needs of a municipality due to more direct involvement by staff.

At the advanced level of maturity, the development/maintenance of the register is co-ordinated between staff and the vendor/implementation partner of specialized asset management inventory software. By using the software, staff should be able to exert less effort to maintain an accurate register. Additionally, the software package should be able to generate specialized outputs that can be easily inserted into an asset management plan and other reporting needs. An asset register solution developed in-house can also demonstrate qualities associated with an advanced level of maturity. However, many municipalities may not have the internal capacity to develop solutions that would fully meet their own needs.

Types of Asset Register

There are generally two types of asset register: those housed in databases (e.g. spreadsheets), and those housed in specialized asset register software. Databases are often constructed within a municipality to host the asset register. Some pros and cons of using databases are:

Database Pros:

·       Relatively inexpensive to use with minimal training;

·       Freedom to structure the asset register in a desired format (within limits); and

·       A good approach when establishing an initial asset register before deciding on whether to proceed to formal software.

Database Cons:

·       These databases are often not designed to be updated by multiple users (not without significant manipulation);

·       Can require more effort to ensure that only accurate data is permitted; and

·       Very easy to make mistakes/errors, and difficult to control the editing/updating process.

Asset register software is an “off-the-shelf” tool that allows more seamless user control, better data integrity, and greater access to the latest technology. These software databases are purpose-built tools by software vendors that specialize in creating custom asset registers for municipalities. Some pros and cons of utilizing asset register software are:

Software Pros:

·       Designed to be used and updated by multiple users;

·       Data controls are natively built into the software, restricting editing capabilities and read capabilities (while data validation is possible in databases and spreadsheets, there is more effort required to set this up);

·       Editing process is automated, minimizing mistakes/errors;

·       Task of maintaining the asset data’s accuracy and completeness is more efficient than databases;

·       Reports tend to be generated automatically;

·       Some software includes other enhanced asset management capabilities, such as integration with other systems; and

·       Availability of updates to address issues and advancements.

Software Cons:

·       More expensive than databases (actual cost depends on the software used);

·       Software can have some freedom in how the asset register is setup, however there is usually a basic structure that must be adhered to; and

·       Training and implementing the software takes extra time and effort.

9.4       Asset Register as a Dynamic Tool

An ability of the asset register to function as a dynamic tool that is easy to update and seamlessly integrates with other systems further enhances the asset management process.

To what extent is the asset register used as a dynamic tool?

Background

The definition of dynamic is “constant change, activity, or progress”. The ability for an asset register to be dynamically updated is an important consideration. A static inventory provides a snapshot of the state of assets at a fixed point in time (i.e. the view of assets only as of when the register was created or last updated). A dynamic register allows for updates to easily be integrated, and calculated data to be updated instantaneously. This is not to say that a static inventory cannot be updated or is not recommended, but that it requires more manual effort to update all values for all assets over time.

Levels of Maturity

To what extent is the asset register used as a dynamic tool?

 

At the basic level of maturity, the asset register has little to no dynamic functionality. In other words, most of the work involved with updates to the inventory is manually entered throughout the register. This may lead to future problems due to time commitments necessary to maintain the asset inventory.

At the intermediate level of maturity, dynamic functions are fully integrated for a few asset classes or assets belonging to specific departments. When an asset class is dynamic, updates to specific variables will force updates to other parts of the system that are dependent on these variables. These dynamic functions may only be developed for certain departments or assets of the municipality, while other areas rely on static updates.

At the advanced level of maturity, all aspects of the asset register are dynamic, for all departments and assets. Updates to any metric will propagate to all other areas of the asset register that depend on that metric. Hence, the burden of ensuring that the asset register is maintained and reflective of the current state of infrastructure is minimized.

Dynamic Function Examples

The examples below should help readers visualize an asset register with dynamic functions It should be noted that while a broad cross-section of examples are provided, this is not an exhaustive list of all possible dynamic functions.

A dynamic function might:

·       Update all age-related metrics (e.g. service life remaining, age, age-based condition, etc.) upon receiving date of implementation and useful life parameters.

·       Automatically perform financial calculations (e.g. net book value, amortization, additions, disposals, betterments, etc.) necessary for PSAB 3150 reporting requirements for each fiscal cycle.

·       Automatically update replacement cost metrics, every year, based on capital inflation or when new benchmark cost data are input (e.g. cost per m² of local road).

·       Update complex calculations, such as asset risk, when updating condition data.

·       Read and store pertinent data from other systems. Data from another software tool may provide some of the updates to the asset register. A dynamic link between these systems ensures the register stays up-to-date and eliminates a point of human error, as duplication of work is prevented.

·       Update data from another system. In more sophisticated business cases, data from another data store may need to be updated from the asset register if the asset register is the main point-of-input for all inventory-related tasks. In these cases, special database/software instructions may propagate the updates from the register to other systems.

9.5       Geographic Information System (GIS)

GIS is a tool that can assist with both visually locating and presenting asset management decisions.

Is the asset data and decisions spatially mapped?

Background

Geographic Information System (GIS) is a tool that spatially maps assets such that the location of assets can be overlaid on a map of a municipality. Other information can be spatially mapped, such as the condition of the asset (i.e. red indicating a poor asset condition, green indicating a good asset condition).

Levels of Maturity

Are the asset data and decisions spatially mapped?

At the basic level of maturity, municipalities have partially mapped asset data. Municipalities at this level should ensure that the significant asset categories are included within the GIS system. Maps should also be used to show asset locations, and should be easily categorized by high risk, or low condition.

At the intermediate level of maturity, municipalities have fully mapped asset data. Municipalities at this level should ensure that all asset categories are included in the GIS system, and should establish a process that maintains consistent data between the GIS and AM systems.

At the advanced level of maturity, municipalities have fully mapped asset data with a direct link between the GIS and AM systems. Municipalities at this level should also develop their GIS and AM linkage to draw asset data from each other.

Geographic Information System (GIS)

As mentioned above, a GIS spatially maps assets such that the location of assets can be overlaid on a map of a municipality. Most assets owned by a municipality are fixed in terms of geography, be it roads, water and/or sewer mains, or facilities. Therefore, it can be easier to interpret the data when the assets are presented visually on a map. Some municipal assets (such as vehicles and equipment) do “travel”. However, these assets can be spatially mapped based on their “home” location.

A properly configured GIS file should allow all assets contained in the asset register to be tagged with geographical data so they can be tied to physical locations. Therefore, maps of the municipality can be drawn and assets can be visually represented where they actually reside.

Once these maps are created, the GIS allows for analytics to be performed that may reveal new insights into decision-making processes. The priority and timing of executing projects can be an important decision for municipalities to make, which can be made easier when utilizing a GIS. For example, it may be easier to make decisions developing strategies and plans when presented with GIS data that highlights the condition of all assets in a specific area, or highlights nearby assets. This can be useful because the timing of applying lifecycle management strategies to these assets can be clustered to promote efficiency. This capital forecast integration was discussed in Chapter 5 (non-infrastructure solutions).

The GIS is only as good as the data recorded within it. To accurately map these assets, the exact GPS locations must be recorded. Therefore, it may take a significant amount of resources to map out all assets in a municipality if this data is not already available.

Figure 9-1 (below) is an example of a GIS image depicting one area of a municipality (roads highlighted in red) in poor condition:

Figure 9‑1

Sample GIS Image – Condition

 

9.6       Other Asset-Related Systems/Tools

Other asset-related tools can provide additional information and/or clarity into the asset management process. These additional tools often augment the asset register to leverage different types of data, based on a municipality’s needs.

Are there other asset-related systems/tools utilized by your municipality and how connected are they to the asset register?

Background

Additional technological tools can be adopted by a municipality to expand functionality, or provide ease-of-use, when managing the asset register or the overall asset management process. These tools may already be implemented within a municipality with other primary functions outside of asset management. Examples of each are discussed in this section.

1.    Computerized Maintenance Management System (CMMS): CMMS allows a municipality to plan and track the maintenance, repair, and rehabilitation operations it performs on its assets on an ongoing basis.

2.    Financial reporting tools: Financial reporting tools are often used to produce the outputs necessary to complete legislated annual financial reporting requirements, and these systems can be integrated with an asset register to more easily generate these outputs.

Whatever tools a municipality ultimately decides to use, they all intend to enhance the ability of a municipality to manage its assets through the asset register.

Levels of Maturity

Are there other asset-related systems/tools utilized by your municipality and how connected are they to the asset register?

At the basic level of maturity, a municipality has at least one type of asset management tool. However, these tools are typically not integrated with the asset register (i.e. no dynamic linkages exist). The result is that the tools can inform staff on asset characteristics and forecasts, but all insights must be manually inputted into the asset register or the overall asset management process on a continual basis.

At the intermediate level of maturity, a municipality has some of the asset management tools in place, but only a few of these tools are linked to the asset register or asset management process.

At the advanced level of maturity, a municipality has implemented all types of software tools (e.g. CMMS and financial reporting) and has created dynamic linkages between the asset register and each distinct system. A municipality at the advanced level is therefore able to provide its Council and staff with up-to-date snapshots of its assets in unique and insightful ways without the burden of maintaining the asset register.

Computerized Maintenance Management System (CMMS)

As discussed above, a CMMS is a tool that allows a municipality to track the maintenance, repair, and rehabilitation operations that it performs on all its assets on an ongoing basis. A well integrated system might tie into a municipality’s existing asset register and other IT systems. This provides the municipality the ability to effectively plan and manage their assets on an ongoing basis, minimizes the chances that maintenance activities are overlooked, and helps staff coordinate operations in an efficient manner.

A CMMS allows “work orders” to be automatically generated based on asset condition or the existing risk data found in the asset register. The plans and schedules developed by the system can either be set by staff recommendations, maintenance schedules (as set forth in the lifecycle management strategy), or from reports/readings taken from the assets themselves.

A CMMS will keep an accurate historical record of any actions performed on all assets, which easily allows auditors to verify what has occurred to each asset. This data can be used by a municipality to estimate asset condition. In addition, a CMMS helps manage inventory, as it can document the amount of inventory that is warehoused and in the field. This can provide ease in the process of restocking assets that are frequently used (e.g. water meters).

CMMS’s can be a vital component of the ongoing process of updating the asset register. Inspections are common actions to maintain an accurate and up-to-date snapshot of an asset’s condition. A modern CMMS could be responsible ensuring contents of the asset register are updated. Additionally, with mobile technology, these updates could be performed ‘on location’ by maintenance staff, potentially increasing efficiency, mitigating data gaps, and reducing the potential for error.

A functional CMMS may look different for any given municipality, as the greatest value of such a system is determined by how easy it is to implement given municipal structure, existing IT environment, and the features it provides.

9.7       Modelling and Optimization

Modelling can assist with developing lifecycle management strategies that make sense to the municipality and optimize the allocation of scarce financial resources to provide the best possible service outcomes at the lowest cost.

Does your municipality have the ability to perform modelling optimization?

Background

Modelling uses tools to optimally allocate a municipality’s resources in their ongoing asset management practices. In simple terms, modelling optimization is using software that helps develop the lifecycle management strategy (e.g. long term forecast) in the most optimal way, given a set of instructions and parameters supplied by a municipality.

Ideally, optimization considers factors such as asset deterioration characteristics, treatment costs, treatment effects, and takes this and other criteria into account. Modelling tools should enable the optimization of the lifecycle management strategy so that the timing and extent of proposed lifecycle activities achieves service level targets at the lowest cost. Modelling optimization tools can assist municipalities determine where they should be spending limited resources to achieve the highest possible returns, whether measured by level of service, risk, or another metric.

Levels of Maturity

Does your municipality have the ability to perform modelling optimization?

At the basic level of maturity, municipalities hire outside consultants to perform modelling optimization. This process is generally performed by the consultant(s) who is preparing the asset management plan (or components of an asset management plan). For example, a road asset will require a study that optimizes roads needs, which should be used in the asset management plan.

At the intermediate level of maturity, municipalities use internal tools (e.g. spreadsheets) to perform modelling optimization. At this level, municipalities use the knowledge and experience of their staff to ‘fine tune’ their model and generate outputs, which should be used in the asset management plan.

At the advanced level of maturity, municipalities use purpose-built tools (e.g. asset management software) that apply advanced statistical techniques to perform the modeling optimizations. At this level, municipalities set criteria and parameters for the software to adhere to and with this information, the software will calculate the most efficient path.

Modelling Optimization Tools

Modelling optimization tools allocate a municipality’s resources in their ongoing asset management practices. Optimization tools, whether developed in house or in the form of commercially available software, should be able to easily interface with the asset register to perform this function (or already be imbedded into the asset register itself). Further, the outputs should easily integrate into existing municipal reporting practices.

Budgeting and other constraints can make it impossible to complete all required capital projects in any given year, which requires the use of selective criteria to determine what projects to undertake. Weighing the benefits of projects and factoring in levels of service further compounds the difficulty and complexity of creating strategies and plans. The next step of forecasting these models to ensure the municipality can provide the resources to maintain and provide specific service levels can be a difficult task to tackle manually.

Scenario modelling can be an effective approach for assessing and developing solutions to complex issues. Scenario modelling can reveal the optimal course of action that provides the greatest benefit to the municipality with the lowest risk. Since this method relies heavily on logic, there is a reasonable measure of accountability with this approach.

Effective use of the modelling tool requires a considerable amount of specialized knowledge. The inputs used in modeling tools should come from the raw register data as well as staff decisions. This is important to note since the process of verifying the results/outputs of the model without knowledge of the data sources/inputs is very difficult. Inputs are usually logical criterion, such as the various criterion associated with the decision-making process around asset maintenance, rehabilitation, or replacement.

For example:

·       When asset risk or condition falls under a set score;

·       When connected assets’ risk or condition falls under a set score (i.e. attempting to integrate roads, stormwater, water, and wastewater capital needs);

·       When maintenance levels reach a certain level; and/or

·       When customer complaints reach a certain level.

Through the use of well-defined decision criteria in combination with the right formulas and algorithms, a municipality can ensure that the asset management tool provides appropriate optimized outputs.