Tangible Capital Assets Project

Inventory and Valuation of Existing Bridge Files

Introduction

Most Alberta municipalities have bridge structures on roads under their jurisdiction. Bridge structures (major, standard and culverts) must meet provincial standards and are referred to as ‘bridge files’; each is assigned a unique bridge file number. Provincial grants are normally provided to offset part of the cost to construct and repair these structures. Municipalities have control and management of the bridges and should record them as tangible capital assets (TCA).

Alberta Transportation (AT) requires regular bridge inspections. Municipalities provide AT with much of this information as part of the bridge inspection process. The data is recorded and maintained in the Transportation Infrastructure Management System data base (TIMS).

Alberta Municipal Affairs (AMA) has arranged with AT to provide information for municipalities which will assist municipalities in creating bridge file inventory files and establishing an estimated historical value for each. The purpose of this document is to provide instructions on how the TIMS information can be used to develop a bridge inventory that will meet the tangible capital asset requirements. Municipalities will also have information on their bridge files from bridge inspection reports, construction and funding data which should be used in conjunction with the information provided by AT.

AT Bridge File Information

Bridge file information downloaded from TIMS is posted on the tangible capital assets project web site at in the Inventory and Valuation section. There are three separate spreadsheets which are briefly described as follows:

AT Bridge Data - Data is provided for each bridge file grouped by municipality with information on the bridge type and description, location, years in service, remaining life and costs. There are two categories of data; essential and optional. This information will be updated and posted on the MEnet website at the beginning of each month.

Code Tables - the data file provides a description of the short version codes used in the other data files.

Asset MSCA - The Province of Alberta recorded and valued their infrastructure for TCA purposes several years ago. AT valued most of the bridge files on local roads in the 1999 bridge file inventory using a combination of actual cost and estimated cost. The ‘Year of Construction’ in Column F of this file will be used in the valuation calculations.

Detailed information on the AT Bridge Data can be found in Appendix One.

Recording the Bridge File Data

Collecting and recording information for each of the bridge files should be done in four steps:

1. Develop a complete inventory of the municipality’s bridge files.

2. Assemble the data for each bridge file.

3. Determine the historical cost for each bridge file.

4. Determine the useful life and accumulated amortization.

The recommended approach to this process is to first record all of the necessary information in a spreadsheet. When this information is determined to be sufficient for TCA purposes the data can be transferred to the TCA financial records. It is not necessary to record all of the information provided in the AT data records because engineering and public works staff has this information. Municipalities implementing an asset management system will probably use most of the information provided.

An Excel workbook can be downloaded from It includes five worksheet templates including:

• AT Bridge Data

• Bridge File List

• Bridge File Data

• Bridge File Historical Cost

• Useful Life and Amortization

The instructions below will reference these templates. The workbook is intended to provide a consistent approach for inventorying and valuing the bridge files and to provide documentation which will be useful for audit purposes.

1. AT Bridge File Data Access

The AT bridge file data and the workbook can be accessed on the Municipal Excellence website at Click on:

  • Tangible Capital Assets Home/TCA Toolkit (side bar)/Inventory & Valuation (side bar)
  • AT Bridge Data, Code Tables, Asset MSCA, Bridge File workbook

2. Bridge File Inventory

It is important to establish the complete inventory of bridge files before the detailed data is recorded. First, this will verify your inventory in a manageable file before you begin to obtain the bridge information. Second, this step should provide a good source of information for the auditor verifying that all of the bridge files have been inventoried; however, it is important that you document your verification process:

(a)Open the ‘AT Bridge Data’ file on MEnet.

(b)Save a copy of this file on your hard drive or network. You will be using the information from the ‘Essential’ tab initially and the ‘Optional’ tab later in the process.

(c)Locate the lines of data specific to your municipality from the ‘AT Bridge’ file ‘Essential’ on MEnet and cut and paste that information to your Bridge File Data worksheet.

(d)The data copied is sorted by the ‘structure type’; resort the data in your Bridge File workbook by the ‘bridge file number’ in ascending order.

(e)Copy the first four columns of data from the ‘AT Bridge Data’ worksheet (Columns A, B, C, D) to the ‘Bridge File List’ worksheet (Columns A, B, C, D). Note: The most important information required is the bridge file number and location but information regarding the structure may also be beneficial.

(f)Determine the total number of bridge files and record it on this worksheet. Note: There may be some lines with the same bridge file number due to, for example, bank extension work for the same bridge file.

(g)Print out this list and verify that it is complete by comparing it to the bridge file records of your municipality. There are at least two methods of comparison and both should be used:

  1. Compare the list to the records of each bridge file kept by your municipality.
  2. Have your bridge inspector compare this list to the list used for bridge inspection purposes.

Columns E & F on the ‘Bridge File List’ worksheet can be used to record the above verification steps and confirmation that each bridge file exists.

(h)Document any discrepancy noting what research was conducted to verify whether the file exists or does not exist. Files on the list that cannot be verified and files for which you have records but are not on the list should be kept separately for further reference.

(i)The finalized inventory list of the bridge files should be signed and dated for future reference by the auditor.

3. Bridge File Data Records

Now that you have verified your inventory, the next step is to identify the data you want to record for each bridge file. Appendix One lists the data for each bridge file provided by AT.

  1. Determine the data to be recorded in the TCA record for each bridge file. The minimum information should include (column is indicated):
  • File number - A
  • Structure type - C
  • Location/description - D
  • Structure status - E
  • Years in service - F
  • First in service year - G
  • Number of spans pipes - H
  • Nominal bridge length - I
  • Wear surface type description - N
  • Legal land location - O
  • Structure name - P
  • Located on - Q
  • Located over - R
  • Last inspection date - W
  • Watercourse name - AD
  • Deck material description - AE

Note: Data regarding year of construction and additional bridge specifications will also be required and will be added in Step 4.

  1. Use the worksheet labelled ‘Bridge File Data’ and set up column headings for each data field that you have decided to record in the same sequence as the ‘AT Bridge Data’ file. Note: The column headings set up in the worksheet template are those suggested in the previous step.
  1. Copy the data you want to record from the ‘AT Bridge Data’ worksheet to the ‘Bridge File Data’ worksheet. You may need to adjust the width of some of the columns to be able to read all of the data transferred.
  1. Review each of the data fields to ensure that all data fields that should have information are populated and test some of the data by comparing it with municipal records for accuracy. Use other sources of bridge file information to complete missing information as well.

4. Bridge File Historical Cost

Determining the historical cost for each bridge file will probably take the most time. The ideal situation would be if each municipality had the actual cost for each bridge file in their records but it is unlikely that this will be the case. AT – Bridge Branch used to look after constructing and maintaining the bridge infrastructure and the municipality was not directly involved in these projects until less than twenty years ago. Therefore, information regarding historical cost will probably need to come from external sources for many of the bridge files.

The AT Bridge Data includes replacement cost information. AT calculates replacement cost based on the replacement structure which may have different specifications than the existing structure, may include other costs or may be replaced with a completely different structure. Therefore, the AT replacement cost is not appropriate to estimate the cost of the existing structure. AT valued bridge infrastructure in 1999, including bridges under municipal jurisdiction, but there were too many variables to consider using this information for TCA purposes.

AT has provided assistance with this issue by calculating the average bridge construction costs for each major bridge type in each region and these average costs will be used for estimating the bridge historical costs. These average cost units are in the following table which will be referenced in the steps to calculate the historical cost.

AverageBridge Construction Costs

Major Standard Culvert

RegionYear Cost/sq m2 Year Cost/sq m2 Year Cost/sq m2

South 2006$3,5562006 $2,8452007 $1,713

Central 2006$3,6992006 $2,9592007 $2,597

North/Central 2007$4,7732007 $3,8182007 $2,704

Peace 2007$5,4242007 $4,3392007 $3,063

Note: Please note that the costs in the above table are either in 2006 or 2007 dollars. This will need to be considered when the estimated construction costs are discounted to determine the historical cost.

The following steps should be taken to determine the bridge file historical cost:

  1. Use the ‘Bridge File Historical Cost’ worksheet.
  1. Transfer the cost data from the ‘AT Bridge Data’ worksheet and ‘Asset MSCA Data’ on MEnet. This step will require extra care and some data re-organization because the ‘Asset MSCA Data’ may not have all of the bridge files and the purpose of this worksheet is to have all of the available information on the same bridge file line for proper analysis.
  1. Copy Column A ‘file number’ of the ‘AT Bridge Data’ worksheet to Column A of the ‘Bridge File Historical Cost’ worksheet.
  2. Copy the following columns of the ‘AT Bridge Data’ worksheet to the ‘Bridge File Historical Cost’ worksheet as follows:

AT DatatoHistorical Cost

Structure TypeColumn CColumn B

First Year in ServiceColumn GColumn I

Number of SpansColumn HColumn V

NominalBridge LengthColumn IColumn M

Total Clear RoadwayColumn JColumn N

Maximum Barrel DiameterColumn KColumn U

Maximum Pipe LengthColumn LColumn W

Make sure that the same number of lines of data has been copied to match with the number of bridge files in Column A.

  1. In the “Bridge File Historical Cost’ worksheet, copy Column B – Structure Type, to Column L. This will provide easier reference to the type of structure when calculating the historical cost.
  2. For the Bridge Files for your municipality, copy Column A ‘file number’ and Column F ‘construction year’ of the ‘Asset MSCA Data’ file on MWNet.ab.ca to Column J and K, respectively, of the ‘Bridge File Historical Cost’ worksheet.
  3. Cut and paste Columns J and K to match the bridge file numbers in Column J with Column A. Remember that the ‘Asset MCSA Data’ file was created using data in 1999 and, therefore, may not have all of the current bridge file data.
  4. Make format adjustments to make sure that all of the data in each cell can be easily read because this worksheet will be used as the valuation document for future use and reference.
  1. Use actual cost information for each bridge file if possible. Record the actual cost of construction for specific bridge files that may be in the municipal records in Column G and the year of construction in Columns H and E. If it can be determined that this actual cost amount represents the total cost of constructing that specific bridge file, use that amount. Record the actual cost amount in Column C and record ‘A’ in Column D. Proceed to the next step to estimate the historical cost if the actual cost cannot be determined from municipal records.
  1. Calculating the replacement cost in 2006 or 2007 dollars:

Average bridge construction costs per square meter for each type of structure provided above will be used to estimate the historical cost. The formulas for calculating these estimated construction costs are as follows:

Major and Standard

Nominal bridge length X Total clear roadway X Cost per m2

Culvert

Maximum barrel diameter/1,000 X Number of spans X Maximum pipe length X Cost per m2

Note: These formulas are in the ‘Bridge File Historical Cost’ worksheet.

Note: Some culvert bridge files do not have the nominal barrel diameter in the AT data. In these situations, an additional calculation is required to determine the nominal barrel diameter which is the average of Length Span 1 and Rise Pipe 1; (Length Span 1 + Rise Pipe 1)/2

  1. Major Bridges – Calculate the construction cost

Record the ‘major’ bridge cost per m2 and the year of the cost for your region from the table 'Average Bridge Construction Costs' in Columns O and P of the ‘Bridge File Historical Cost’ worksheet for each of the ‘major’ bridge structures indicated in Column L.

  1. Standard Bridges – Calculate the construction cost

Record the ‘standard’ bridge cost per m2 and the year of the cost for your region from the table 'Average Bridge Construction Costs' in Columns O and P of the ‘Bridge File Historical Cost’ worksheet for each of the ‘standard’ bridge structures indicated in Column L.

  1. Culverts – Calculate the construction cost
  1. Record the ‘culvert’ bridge cost per m2 and the year of the cost for your region from the table 'Average Bridge Construction Costs' in Columns X and Y of the ‘Bridge File Historical Cost’ worksheet for each of the ‘culvert’ bridge structures indicated in Column L.
  2. Calculate the ‘maximum barrel diameter’ if required.

If there is no value for the ‘maximum barrel diameter’ for some bridge files:

  • Go to the AT Bridge Data file, ‘Optional’ tab on the MEnet website and manually record the following data for those bridge files to the ‘Bridge File Historical Cost’ worksheet: (Note: Do not copy the columns of data because the lines do not match resulting in calculation errors.)

Length Span 1, Column G to Column S

Rise Pipe 1, Column N to Column T

Note: Disregard any information that may be in Length Spans 2 – 5 and Rise Pipes 2 – 5.

  • Record the following formula in the empty cells for ‘maximum bridge diameter’, Column U, for culvert bridge files:

= (SRow# + TRow#)/2

  1. Discounting the construction cost to determine the estimated historical cost
  1. The estimated construction cost has been calculated in 2006 or 2007 dollars as noted in Columns P and Y of the ‘Bridge File Historical Cost’ worksheet. These costs need to be discounted to the year the bridge was constructed.
  1. Use the ‘Deflated Replacement Cost’ calculator in the AMA toolkit on MENet.ab.ca to discount the construction cost to the year of construction. A separate calculation will need to be done for each bridge file for which the construction cost has been estimated. The data to use should be:
  • Year of Construction – Column K to Acquisition Year - Column D
  • Construction Cost – Columns Q or Z to Replacement Cost Estimated – Column E
  • Year Estimated – Columns P or Y to Year Estimated – Column F
  • Calgary or Edmonton Non-residential Building Construction Price Index

Note: It is important to have good documentation; therefore, save a copy of this calculator and name it ‘Bridge File Deflation Calculations’ for a permanent record.

  1. For each bridge file with estimated costs, record the discounted construction cost in the ‘Historical Cost’ Column C, record ‘E’ in Column D and record the Year of Construction in Column E.
  1. Columns A to E of the ‘Bridge File Historical Cost’ worksheet provide the summary of your calculations. It may be beneficial to have experienced public works personnel and, if necessary, consulting engineers to review this worksheet.

5. Bridge File Useful Life, Remaining Life and Accumulated Amortization