Optimal Replacement Cycles of Highway Operations Equipment (2018)

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.