National Academies Press: OpenBook

Optimal Replacement Cycles of Highway Operations Equipment (2018)

Chapter: Chapter 4 - Importing Agency Equipment Data into the Tool

« Previous: Chapter 3 - Tool Setup and Configuration
Page 95
Suggested Citation:"Chapter 4 - Importing Agency Equipment Data into the Tool." National Academies of Sciences, Engineering, and Medicine. 2018. Optimal Replacement Cycles of Highway Operations Equipment. Washington, DC: The National Academies Press. doi: 10.17226/25036.
×
Page 95
Page 96
Suggested Citation:"Chapter 4 - Importing Agency Equipment Data into the Tool." National Academies of Sciences, Engineering, and Medicine. 2018. Optimal Replacement Cycles of Highway Operations Equipment. Washington, DC: The National Academies Press. doi: 10.17226/25036.
×
Page 96
Page 97
Suggested Citation:"Chapter 4 - Importing Agency Equipment Data into the Tool." National Academies of Sciences, Engineering, and Medicine. 2018. Optimal Replacement Cycles of Highway Operations Equipment. Washington, DC: The National Academies Press. doi: 10.17226/25036.
×
Page 97

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

95 Importing Agency Equipment Data into the Tool Historical cost data and other equipment information are needed to determine optimal life cycles and make replacement decisions. This chapter describes how to import agency data into the tool for performing cost and replacement analysis. 4.1 Quick Tips The tool will operate most effectively if maintenance costs are broken into separate cost categories for parts, labor, and com- mercial. If the agency does not separate cost by these categories, then combine all maintenance costs under the parts category. VERY IMPORTANT: Always review the data downloaded from the agency’s systems for errors and questionable data before entering it into the tool. Scrub the data so that any errors are corrected. If errors cannot be corrected, the unit in question should be eliminated from the cost analysis. Determine if there are enough units in a particular class to perform a class-level LCCA. If there is a small number of units in a class, it is unlikely that an optimal life cycle can be calculated reliably. The tool needs multiple pieces of equip- ment with ages spread across the expected life of the equip- ment to produce acceptable results. • One example is that a state may have two bridge snooper trucks, one that is 3 years old and one that is 15 years old. Calculating a class-average optimal life cycle with these two units would not be meaningful. • Another example is that of the class of ½-ton pickup previ- ously described in Chapter 3. There are only seven units in Class 1236. Again, it is not likely that this small sample size can generate a reliable class average LCCA. 4.2 Extracting Data from Agency Systems The first step for entering the agency’s historical data into the tool is to extract the required equipment data from the agency’s existing systems and download it into an Excel file, external to the optimization tool. Two types of data are required: 1. LTD data to perform class-level LCCA, in which all units in a class are analyzed to determine a class aver- age optimal life. 2. Annual cost data for each year of a unit’s life to perform unit-level LCCA for individual units of equipment. Highway agencies have different formats and systems for tracking equipment data. To make the tool universally appli- cable to all agencies, the user can do the data extraction externally from the optimization tool. Because this process depends on the agency’s specific information systems, assis- tance may be required from the system administrator for each of the systems from which data were obtained. These individuals may be in the information technology or finance sections of the agency. Once the extracted data have been downloaded into an Excel file, the data must be formatted, as discussed in Section 4.3. Before entering the data into the tool, validate that the downloaded data is error free. The data should be complete, correct, and consistent in format. Erroneous data and extreme outliers should be corrected or eliminated. The replacement guide provides several examples of common data errors and illustrates various tests for determining data accuracy. 4.3 Uploading Data to the Tool After downloading the data into an Excel file and reviewing for accuracy, use the following process to upload the data to the tool. 1. In Excel, go to the tool folder, C:/Equipment Replacement Optimization Tool, and open the data entry sheet, which is named “01DataEntry.xlsx.” See Figure 12. C H A P T E R 4

96 2. When opening the data entry sheet, all cells will be empty. Row 1, Columns A through K (see Figure 13), are locked and cannot be changed. Enter the data in the exact format shown in Figure 13, which shows sample data for tandem dump trucks. Include all equipment units in the classes for which optimal life cycles will be determined. Note: Before uploading the data to the tool, the equip- ment data must be downloaded from the agency’s existing systems or sources and must be formatted in an Excel file as shown in Figure 13. 3. The data format requirements for the data entry sheet are as follows: Equipment Number (Column A)—Required. The agency’s existing equipment ID number; must be unique for each unit. Any alphanumeric form can be used. Agency Class Code (Column B)—Required. The agency class for each type of equipment; must exactly match the agency class information in the configuration file. Description (Column C)—Optional. A free-form descrip- tion of the equipment class; use the agency’s existing class description titles. In-Service Year (Column D)—Required. The four-digit year when the unit was first placed into service. The data type for this column must be number or general format and not a date format. Miles or Hours (Column E)—Required. The current LTD miles or hours on the unit. Parts, Labor, and Commercial Costs (Columns F through H)—Required. LTD costs for each cost compo- nent. The tool applies the direct and indirect overhead factors from the configuration file to the labor cost. If the agency does not separate maintenance and repair cost by labor, parts, and commercial, enter the combined cost into Parts. Fuel (Column I)—Optional. The LTD fuel costs in dol- lars. If the agency tracks fuel in gallons, convert gallons into dollars by using an average or current fuel price. If the agency does not have fuel costs, leave this column blank and the tool will ignore it. However, fuel costs are required for the tool to compute the full cost of equipment operations. Downtime Hours (Column J)—Optional. The tool provides for entering downtime hours if the data are Figure 12. Tool folder selection. Figure 13. Sample data entry.

97 available. If the agency does not have reliable downtime data, leave this column blank and the tool will ignore it. However, downtime data are required for the tool to compute the full cost of equipment operations. Purchase Cost (Column K)—Optional. The original purchase cost of the unit, which may include the cost for equipment preparation and outfitting. Subgroups within a class may be analyzed separately if desired. For instance, to analyze one manufacturer at a time, input only the vehicle of that make. When doing this type of analysis, always ensure there are enough units within the subgroup to provide a valid sample. Ensure also that the class codes in the configuration file are exactly the same as the class codes in the data entry sheet.

Next: Chapter 5 - Performing Life Cycle Cost Analysis »
Optimal Replacement Cycles of Highway Operations Equipment Get This Book
×
MyNAP members save 10% online.
Login or Register to save!
Download Free PDF

TRB's National Cooperative Highway Research Program (NCHRP) Research Report 879: Optimal Replacement Cycles of Highway Operations Equipment acts as a handbook on equipment replacement concepts and an instruction manual for making cost-effective replacement decisions. The research report presents a process for determining replacement needs for highway operations equipment, identifying candidate equipment units for replacement, and preparing an annual equipment replacement program. The products include a guidance document and an Excel-based replacement optimization tool to support the equipment replacement process and facilitate its implementation.

Disclaimer - This software is offered as is, without warranty or promise of support of any kind either expressed or implied. Under no circumstance will the National Academy of Sciences, Engineering, and Medicine or the Transportation Research Board (collectively "TRB") be liable for any loss or damage caused by the installation or operation of this product. TRB makes no representation or warranty of any kind, expressed or implied, in fact or in law, including without limitation, the warranty of merchantability or the warranty of fitness for a particular purpose, and shall not in any case be liable for any consequential or special damages.

  1. ×

    Welcome to OpenBook!

    You're looking at OpenBook, NAP.edu's online reading room since 1999. Based on feedback from you, our users, we've made some improvements that make it easier than ever to read thousands of publications on our website.

    Do you want to take a quick tour of the OpenBook's features?

    No Thanks Take a Tour »
  2. ×

    Show this book's table of contents, where you can jump to any chapter by name.

    « Back Next »
  3. ×

    ...or use these buttons to go back to the previous chapter or skip to the next one.

    « Back Next »
  4. ×

    Jump up to the previous page or down to the next one. Also, you can type in a page number and press Enter to go directly to that page in the book.

    « Back Next »
  5. ×

    To search the entire text of this book, type in your search term here and press Enter.

    « Back Next »
  6. ×

    Share a link to this book page on your preferred social network or via email.

    « Back Next »
  7. ×

    View our suggested citation for this chapter.

    « Back Next »
  8. ×

    Ready to take your reading offline? Click here to buy this book in print or download it as a free PDF, if available.

    « Back Next »
Stay Connected!