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.
User Manual for the Utilization Prediction and Management Software P A R T I I I
C O N T E N T S 49 Chapter 1 Introduction 49 1.1 Background 49 1.2 About this User Manual 49 1.3 UPM Software Capabilities 50 Chapter 2 UPM Software Installation 51 Chapter 3 Distance Matrix Setup 53 Chapter 4 Data Preparation 53 4.1 Dataset Setup 53 4.2 Dataset Configuration 56 Chapter 5 Running UPM Software and Importing Agency Data 56 5.1 Run UPM Software 56 5.2 Import Distance Matrix into UPM Software 56 5.3 Import Equipment-Level Dataset into UPM Software 57 5.4 Import Region-Level Data into UPM Software 63 Chapter 6 Predict Utilization 66 Chapter 7 Utilization Management Processes 66 7.1 Determine Optimal Utilization Values 70 7.2 Define Scenarios 74 7.3 Generate Summary Report 76 Chapter 8 Use Case Example
49 Introduction 1.1 Background Because of the significant role of equipment fleet in the delivery of agency programs and projects and their signi- ficant contribution to capital investments, it is critical to determine the optimal utilization levels and management procedures for equipment fleet. This project has developed a set of utilization measurement and management models and utilization prediction and management (UPM) soft- ware to estimate optimal utilization levels. This user manual describes how to use the UPM software and extract optimal management reports. 1.2 About this User Manual This document describes the steps to follow in the UPM software to obtain fleet equipment utilization measure- ment and management reports. It is recommended to first review the Guide to understand how the statistical models and cost functions were developed and incorporated into the management models. This user manual explains the following: â¢ Installing the UPM software, â¢ Preparing the input data based on UPM software requirements, â¢ Importing equipment data into the UPM software, and â¢ Using UPM software to generate utilization prediction and management reports. 1.3 UPM Software Capabilities The UPM software has the following capabilities: 1. Predict the annual utilization; 2. Analyze the impact of each contributing factor on equip- ment utilization (generate sensitivity analysis graphs); 3. Determine optimal fleet size and utilization levels to find lowest equipment management costs; 4. Determine the number of equipment units to be pur- chased, relocated, or salvaged; 5. Identify candidate equipment units to be salvaged or relocated in a desired analysis area; and 6. Generate user-defined scenarios and compare them with the optimal scenario. C H A P T E R 1
50 Minimum system requirements to run the UPM software are as follows. â¢ Windows â Windows 10 (8u51 and above) â Windows 8.x (Desktop) â Windows 7 SP1 â Windows Vista SP2 â Windows Server 2008 R2 SP1 (64-bit) â Windows Server 2012 and 2012 R2 (64-bit) â RAM: 3072 MB â Disk space: 124 MB for JRE; 2 MB for Java Update â Processor: Minimum Pentium 2 266 MHz processor â¢ Mac OS X â Intel-based Mac running Mac OS X 10.8.3+, 10.9+ â Administrator privileges for installation The UPM software was developed in Java, which calls SQLite database manager (Mozilla Firefox add-ons) and Dynamic-link libraries of several solvers to run and solve the mathematical optimization framework. All files should be downloaded and kept in the same directory as the executable file (i.e., NCHRP13-05.bat) to avoid installation problems. Note: Mac users must run âNCHRP13-05.jarâ located in the â\binâ folder, while Windows users are recommended to run âNCHRP13-05.batâ to avoid memory issues. The user needs to copy the UPM software from the âNCHRP 13-05â folder to the desired directory (Figure 1). The computerâs desktop folder is assumed to be the main directory for the UPM software throughout this user manual. C H A P T E R 2 UPM Software Installation Figure 1. File location.
51 Distance Matrix Setup Users are required to update the distance matrix based on their agency needs before they use the UPM software. The impact of the distance inputs (among different regions) on the optimal utilization management solutions is discussed in the Guide. This chapter explains how the distance matrix should be changed to fit the userâs specification. In the UPM software directory, there is a âDistance Matricesâ folder (Figure 2) that contains an Excel file named âExample ProblemâDistance Matrix.xlsxâ for the example problem used in this project. By default, the Excel file shows the distances among different regions in a user-defined analysis area in miles (Figure 3). Distances can be modified and should be saved before exiting the Excel file. Users can create similar distance matrices for any desired analysis area. Agencies have a different organizational design to capture their equipment records. To make the software widely appli- cable for all agencies, users can modify the distance matrices based on their organizational design. Users can modify their current analysis areaâs distance matrix or create a new one with the following conditions. 1. The first row should have the same layout as default distance matrices where the user-defined name of the analysis area is written in column B; 2. The names of regions should be filled in the second row and the first column, where the intersection of any row (starting from the third row) and any column (starting from column B) represents the distance between the two regions; 3. The names of regions must be identical to those reported in equipment record inputs; otherwise, the software does not work properly. For example, if the name of a reported region for a piece of equipment is âBox Elder,â the exact name with the same format (i.e., capitalized words with a space between them) must be written in the distance matrix; 4. The number of regions in rows and columns must be identical to create a square matrix; and 5. All distances should be entered in the Excel file as real numbers with nonnegative values. C H A P T E R 3
52 Figure 2. Location of distance matrices. Figure 3. Screenshot of distance matrix for the example problem.
53 Data Preparation Equipment historical costs and usage data are required to help fleet managers determine the optimal utilization mea- sures that find lowest utilization costs. This chapter explains how to prepare and organize the data to make them readable by the UPM software. 4.1 Dataset Setup In the UPM software directory, there is an âInput Dataâ folder that contains the âExample ProblemâInput Data.xlsxâ file (Figure 4). In this Excel file, the first row should show the name of the defined analysis area under column B, and the second row represents the equipment specifications such as âUnit ID,â âNAFA Code,â and âEquipment Groupâ (all equipment specifications can be found in the âExample ProblemâInput Data.xlsxâ file) (Figure 5). The required format of the dataset file follows. â¢ Blank white cells require agenciesâ inputs. Each row repre- sents the information of one equipment unit in the analysis area. â¢ Gray cells are locked and cannot be modified. Note: The number and the order of columns MUST be identical to that in the template in âExample ProblemâInput Data.xlsxâ file (Figure 5); otherwise, the software does not work properly. Detailed information about the dataset columns is provided in the following section. 4.2 Dataset Configuration The required information for each equipment unit is kept in the following column format. 1. Unit ID (Column A)âRequired. This column indicates the exclusive equipment ID based on the agencyâs data recording system and can have any character (i.e., number and/or letter). 2. NAFA class code (Column B)âRequired. The NAFA classification system is a comprehensive and standard system to categorize equipment based on their class, group, service, and type. Each equipment unit in the fleet is presented with its NAFA class code. The code entered in this column should be one of the standard reported NAFA class codes in the dataset. 3. Equipment group (Column C)âOptional. This column stores the group category of each equipment unit (e.g., sedan, trailer, or truck). This is an informative column and can have any character (i.e., number and/or letter). 4. Equipment class (Column D)âOptional. The equipment classification based on the gross vehicle weight (GVW) and functionality is saved in this column. This is an informative column and can have any character. 5. User-defined classification code (Column E)âOptional. The equipment classification based on the user-defined classification is saved in this column. This is an infor- mative column and data can have any character. 6. User-defined equipment description (Column F)â Optional. The equipment description based on the description of each equipment unit defined by the user is saved in this column. This is an informative column and can have any character. 7. Report year (Column G)âRequired. This column indi- cates the year in which the data are collected. The input should be an integer. 8. Region (Column H)âRequired. This column shows the region where the equipment unit is located at the time of the report. Each word in the region name should start with a capital letter (e.g., Los Angeles, Whitman) and be identical to the regionâs name in the distance matrix. 9. In-service age (Column I)âRequired. This column shows the years an equipment unit is being operated. The input should be a real number. C H A P T E R 4
54 Figure 4. Equipment-level dataset location. Figure 5. Template of data entry.
55 10. Purchase cost (Column J)âRequired. This column shows the purchase cost at the time of purchasing an equip- ment unit, and it should be reported in real numbers. 11. Fleet size (Column K)âRequired. This column indicates the number of available equipment units with the same NAFA code in the fleet in a region. The data should be entered as an integer. 12. Annual fuel cost (Column L)âRequired. This column represents the fuel cost in the report year in real numbers. 13. Annual scheduled maintenance cost (Column M)â Required. This column shows the total scheduled main- tenance cost for an equipment unit in the report year. The data should be entered in real numbers. 14. Annual unscheduled repair cost (Column N)âRequired. This column represents the total unscheduled repair cost for an equipment unit in the report year. The data should be entered in real numbers. 15. Annual downtime hours (Column O)âRequired. This column shows the inoperative hours for an equipment unit in the report year. The data should be entered in real numbers. 16. Annual engine hours (Column P)âRequired. The engine hours for each equipment unit should be entered in real numbers in this column. 17. Annual mileage (Column Q)âRequired. The miles driven for each equipment unit should be entered in real numbers in this column. 18. Frequency of usage (Column R)âRequired. The number of days in a year the equipment is utilized should be entered in real numbers in this column. Note: If any input value is missing or unavailable, leave the cell blank or enter âNAâ in the Excel file. Please avoid using â0â or other characters.
56 Once the distance matrix and equipment dataset have been completed, users should import the information into the software. This chapter describes running the UPM soft- ware, importing the distance matrix file, and importing the equipment-level dataset or creating a region-level dataset using the UPM software. 5.1 Run UPM Software Users should open the software directory and double- click on the executable file (either âNCHRP13-05.batâ for Windows or â\bin\NCHRP13-05.jarâ for Mac operating systems) to run the UPM software (Figure 6). 5.2 Import Distance Matrix into UPM Software Once the UPMâs opening window appears, the software asks the users to type in a name for the analysis area and import the distance matrix file (Figure 7). Use the following steps to import the distance matrix. 1. Enter a name for the desired analysis area to perform the equipment utilization analyses. 2. Click on the Browse button and find the distance matrix file of the defined analysis area in the software directory. Click on Open to select the file (Figure 8). Note: The update procedure for distance matrix files is explained in Chapter 3. 3. Click on Enter to close the software opening window. This action opens the UPM softwareâs main window. 5.3 Import Equipment-Level Dataset into UPM Software Once an analysis area is defined and a distance matrix file is selected, the main window will open by clicking on the Enter button. Figure 9 presents the screenshot of the main window. The main window contains four tabs as described below. 1. Input tab: Provides users with data input capabilities. Data can be entered either manually or via the Automatic Equipment Specification Input panel. 2. Utilization Prediction tab: Provides users with the utili- zation prediction values of an equipment type based on the (a) equipment specifications entered in the Input tab and (b) developed utilization prediction models in this project. 3. Utilization Management tab: Delivers the optimal values of utilization and fleet size for an equipment type based on the equipment inventory (i.e., Example Problemâ Input Data.xlsx file), user preferences, and input data. 4. About tab: Summarizes the objectives of the project and lists the partner institutions involved in the project. The Input tab helps import the equipment dataset into the UPM software using either of the following panels: â¢ Automatic Equipment Specification Input (for importing the equipment-level dataset) or â¢ Manual Equipment Specification Input (for importing the average region-level dataset). The procedure for importing the equipment-level dataset using Automatic Equipment Specification Input follows. Users should click on Browse to select the prepared dataset (Figure 10). The steps for providing equipment-level data are explained in Chapter 4 (i.e., Data Preparation). After the Excel dataset is located, a message window displays âImport Data to Interface . . . ,â which shows that the data import is in progress (Figure 11). This step may take up to several minutes, depending on the size of the dataset. Note: The Input Data panel shows the dataset in a table where users can ensure that the imported data are correct. The C H A P T E R 5 Running UPM Software and Importing Agency Data
57 window confirming that the data are being processed for the next steps. Note: The duration of the data processing step depends on the size of the dataset and can range from a few seconds to several minutes. 5.4 Import Region-Level Data into UPM Software This option enables the users to enter available average values of equipment utilization for each region using the Manual Equipment Specifications Input panel. In this panel, the input data should be entered manually using text boxes and drop-down lists in the exact format described in Chapter 4 (instead of automatic data importing from an Excel dataset). All values should be averaged for a selected NAFA class code over a region. Users should first select the NAFA classification code to enter equipment data. Users can select the NAFA classification code directly using the drop- down list (1 in Figure 14) or search for it by clicking on the Search by Class button (2 in Figure 14). The Descriptions box shows the detailed classification of the selected equipment (3 in Figure 14). The Search by Class button opens another window where the user will be able to search for an equipment type by knowing its group and class (Figure 15). By clicking on the Search button, the Options box displays equipment assets that are matched with the selected items in the group and class drop-down lists. Users can select the desired equipment and go back to the main window (i.e., the Input tab) by click- ing on the Select button (Figure 15). Input Data label will be changed to Input DataâIndividual Equipment Entry to show that each row of the table has the information on one equipment unit (Figure 12). The imported data provide the information on each equipment unit separately, while the prediction and manage- ment models are developed based on average values for an equipment type in each region. Therefore, imported data should be processed before moving forward to other tabs. Users should click on the Process button after ensur- ing that their data are imported correctly (using the Input Data panel). Figure 13 shows a âProcessing . . .â message Figure 6. UPM software location. Figure 7. UPM software opening window.
58 Figure 8. Distance matrix file importing procedure. Figure 9. UPM main window.
59 Figure 10. The procedure of importing the equipment-level dataset into the UPM software. Figure 11. Screenshot of message window while data are being imported.
60 Figure 12. Screenshot of the main window when data are imported automatically. Figure 13. Screenshot of the main window when data are being processed.
61 Figure 14. Screenshot of the main window for manual equipment specification input. Figure 15. Screenshot of search by class. After selecting the equipment, users should enter the user-defined classification code, user-defined equipment description, report year, and region and then provide average values for the selected equipment type in the region for (box 1 in Figure 16): â¢ In-service age, â¢ Purchase cost, â¢ Fuel cost, â¢ Fleet size, â¢ Downtime hours, â¢ Annual mileage, â¢ Annual engine hours, â¢ Maintenance cost, â¢ Unscheduled repair cost, and â¢ Frequency of usage. Note: The average cost values should be entered in the textboxes. The Fleet Size should be entered as the total number of equipment units with the same NAFA class codes in a region. By clicking on the Add Equipment button (box 2 in Figure 16), the assigned values for the selected equipment will be added to the list of added equipment and shown in the Input Data panel (box 3 in Figure 16). If the entered data do not match the defined format (described in Section 4.2), an error message will be displayed to point out the fields with the wrong input. The panel label changes to Input Dataâ Average Region-Level to clarify that entered values for each equipment unit must be averaged in each region. Before the
62 equipment is added to the list, users should confirm that the information is correct. Figure 16 shows a screenshot of the main window while data are added manually. Users can save the added equipment dataset into an Excel file for future reference and start the processing step by clicking on the Save & Process button (box 4 in Figure 16). Figure 17 shows the Save a File window (that appears after clicking on the Save & Process button), where users can set the desired name and directory for their generated Excel dataset. Figure 16. Screenshot of the main window, manual data entry. Figure 17. Screenshot of the save window.
63 Predict Utilization After the input data are processed in the Input tab, the UPM software can estimate the equipment utilization in each report year in the Utilization Prediction tab. The UPM software extracts the average values of significant factors from the input data to calculate the utilization of the selected equipment. Users should go through the following steps in the Utiliza- tion Prediction tab to predict equipment utilization. 1. Select the desired equipment type from the NAFA Class Code drop-down list. 2. Choose the analysis year, from the Analysis Year drop- down list, to use the average input values of the given year in the prediction models. 3. Click on the Predict Utilization for the Selected Equipment button to display the utilization prediction results in a table, as shown in Figure 18. Columns 1 to 5 of the table show the selected NAFA class code and user-defined speci- fications (box 1 in Figure 18). Columns 6 to 10 present the analysis year and predicted utilization (e.g., annual mileage, annual engine hours, and frequency of usage), whichever is available for the selected equipment type in each region, based on the input data and statistical prediction models (box 2 in Figure 18). Note: Once users select another equipment type, their prediction results will be added to the end of the table (to the bottom of the existing rows), unless they click on the Clear Table button. 4. Click on the Generate Utilization Report button to export the displayed table along with input parameters/variables into an Excel file. Select the desired directory and name the Excel file; then click on the save button (Figure 19). A confirmation message will be displayed to indicate that the file is created successfully. Figure 20 shows the generated Excel report in step 4 for pickup trucks in the defined analysis area as an example. The explanation of each column follows. â Column A shows the row number in the generated Excel file, where each row represents the relevant infor- mation in one region. â Column B displays the NAFA class code. â Columns C and D provide a description of the selected NAFA class code. â Columns E and F provide the user-defined classifica- tion/description of the selected NAFA class code. â Column G includes the estimated annual mileage in a region. â Column H includes the estimated annual engine hours in a region. â Column I includes the estimated frequency of usage in a region. â Column J shows the selected analysis year in the Utiliza- tion Prediction tab. â Column K represents the name of each region. â Column L shows the fleet size based on input data. â Columns M through U show the average values of different contributing factors to utilization for each region based on the input data. Note: The predicted utilization is tagged by âNMAâ (no model available) for selected equipment in each region if the corresponding utilization prediction model is not available. Clicking on the Sensitivity Analysis button provides the users with the capability to analyze the sensitivity of the estimated utilization to each input parameter/variable. As shown in Figure 21, the average value drawn from the data will be displayed by selecting the sensitivity parameter. Furthermore, the sensitivity graph for the entered analysis interval will be generated by clicking on the Generate Sen- sitivity Graph button. Clicking on the Generate Sensitivity Analysis Report button exports the generated graph into an Excel file in the desired directory. C H A P T E R 6
64 Figure 18. Screenshot of utilization prediction tab. Figure 19. Export the utilization report into an Excel file.
65 Figure 20. Sample estimated utilization report. Figure 21. Screenshot of the sensitivity analysis window.
66 This chapter describes how users can find the optimal utilization values and compare them with defined scenarios for different equipment types. 7.1 Determine Optimal Utilization Values Use the following steps to generate the optimal utilization values based on the developed optimization models. Step 1. Select the Equipment and Analysis Year In the Utilization Management tab, select the desired equipment class from the NAFA Class Code drop-down list (1 in Figure 22). Select the analysis year from the Analysis Year drop-down list (2 in Figure 22). Step 2. Provide Required Information for the Utilization Optimization Click on the Input Parameters button that opens a window to enter average demand, purchase value, and maximum allowed utilization in each region (3 in Fig- ure 22). Figure 23 shows a screenshot of the Input Param- eters window. The default input parameters are provided as follows. â The default values for the demand are the average utili- zation in the selected analysis year. For regions with no usage, a value of zero is used. â Suggested purchase costs are averaged for regions in the selected analysis year. â Maximum allowed utilization shows the maximum utili zation that is allowed for an equipment unit in each region in a year. Here, the maximum utilization in a year should be reported in dedicated cells in front of each region. Default values for the maximum allowed utilization are set based on the average value of top 15% of highly used equipment in the input data in the selected analysis year. The Transportation Cost per Mile per Equipment represents the cost of relocating an equipment unit from one region to another region in dollars per mile. Click on the Browse button and find the transportation cost per mile per equipment matrix in the âTransportation Cost Matricesâ folder. Click on Open to select the file (Figure 24). Note: Users should change the values in the Input Parameters window based on their available data. After applying all changes, users should click on the Save button to save all inputs and close the Input Parameters window. Step 3. Run the Utilization Optimization Clicking on the Optimize Fleet Utilization button runs the utilization optimization model based on avail- able inputs. Before providing the optimal values, the Utilization Threshold window will open to ask users the under- and over utilization thresholds to respectively flag underutilized and overutilized equipment in the manage- ment report. Note: The software shows 20% as the default values where the users can change them to the desired values. For example, a piece of equipment will be considered over utilized when it is utilized at least 20% more than the optimal average utilization obtained from the optimization program. After setting the under- and overutilization thresholds, click on the Save & Process button to select the desired direc- tory to save the management report and start running the optimization. Figure 25 shows the steps of generating the management report. Before the Utilization Management tab is filled out with results, a message is displayed to inform users that the solution is found (Figure 26). The analysis area-level costs (1 in Figure 27) and region- level optimal values (2 in Figure 27) for the selected NAFA class codes and analysis year will also be shown in a box and a table in the software; see Figure 27. C H A P T E R 7 Utilization Management Processes
67 The Analysis Area-Level Cost panel includes the following costs (1 in Figure 27). â¢ Total Cost Before Optimization ($): Presents the total oper- ation cost of the selected NAFA class code in the analysis area based on the input data in the selected analysis year. â¢ Operation Cost if Do Nothing ($): Shows the operation cost of the selected NAFA class code in the analysis area when no optimization is performed (e.g., keeping the same fleet size in each region) in the report year. â¢ Total Cost After Optimization ($): Represents the (a) total operating cost in the analysis area after utilization opti- mization, (b) purchased cost, and (c) transportation cost. â¢ Operation Cost After Optimization ($): Displays the total operating cost in the analysis area after performing the utilization optimization. Note: When the current fleet composition does not satisfy the demand and maximum allowed utilization, the color of the Operation Cost if Do Nothing label will change to red to inform users about the infeasibility. Also, a note will be provided in the management report to inform the users. Figure 22. Screenshot of utilization management window. Figure 23. Screenshot of the input parameter window.
68 Figure 24. Transportation cost matrix importing procedure. Figure 25. Steps of generating management report.
69 Figure 26. Screenshot of utilization management window when a solution is found. Figure 27. Screenshot of software with obtained values from optimization.
70 The description of Region-Level Utilization, Fleet Size, and Cost shown in the table (box 2 in Figure 27) follows. â¢ Column 1 displays the region names. â¢ Column 2 presents the optimal utilization (on average for each region) based on the optimization results. â¢ Column 3 is the optimal fleet size in each region that satisfies the demand. â¢ Column 4 displays the operating cost (in dollars) in each region. â¢ Columns 5 and 6 show the required transportation and purchase costs (in dollars) to be spent in each region to reach the optimality and reduce the total costs. The created management report in the Excel file includes four tabs as follows. â¢ Management Report tab: Includes the optimal analysis area-level utilization, region-level utilization, and fleet size accompanied by purchased, transportation, and operation costs for each region (Figure 28). â¢ Detailed Costs tab: Reports detailed transportation costs between regions in addition to the purchased costs, salvaged costs, and total costs (Figure 29). â¢ Detailed Relocation Matrix tab: Indicates the number of relocated equipment between every two regions based on the optimization results (Figure 30). â¢ Equipment-Level Analysis tab: Includes the comparison of each equipment typeâs actual utilization with its optimal analysis area-level and region-level utilizations (Figure 31). 7.2 Define Scenarios UPM software provides users with the capability to define and analyze scenarios. Users can enter into the software the number of purchased and salvaged equipment units in each region along with the number of relocated equipment units Figure 28. Management report tab in the Excel report file. Figure 29. Detailed costs tab in the Excel report file. between every two regions. Then, the software updates the fleet size in the management framework and calculates the utilization levels and costs corresponding to the user-defined scenarios. Note: If the composition of input parameters is infea- sible, an error message will be shown to the user (e.g., see
71 Figure 32), and the software marks the infeasible solutions in red (Figure 33). For example, if a userâs input regarding the number of transferring-out or salvaging equipment from a region is more than the current fleet size, the software will show an error but record the results. To define a scenario, users are required to create a sce- nario and then import it into the UPM software. A scenario template is provided as an Excel file in the âUser-Defined Scenariosâ folder. Opening âExample ProblemâUser-defined Scenario.xlsxâ file allows defining a new scenario (Figure 34). The Excel file contains two sheets to capture the input data. In the Purchase-Salvage sheet, the number of purchased and salvaged equipment for each region should be defined. In the Relocation sheet, users can enter the desired number of relocated equipment units between every two regions. In the relocation matrix, the regions in the first row send equip- ment to regions located in the first column. All inputs should be entered as integers. Figure 35 shows a sample of a user- defined scenario in an Excel file. Note: The number and order of regions MUST be identical to the distance matrix. The layout of the distance matrix is explained in Chapter 3. After creating the input file of a scenario, the users should go back to the UPM software to import their generated input Excel file and get the analysis results. Therefore, users should (a) redo Step 1 and Step 2 of Section 7.1 and (b) click on Figure 30. Detailed relocation matrix tab in the Excel file. Figure 31. Equipment-level analysis in the Excel report file.
72 Figure 32. Screenshot of error message for an infeasible scenario. Figure 33. Screenshot of utilization management tab for an infeasible scenario.
73 Figure 34. Location of the template scenario file. (a) (b) Figure 35. Sample of user-defined scenario: (a) purchase and salvage and (b) relocation. the Load Defined Scenarios button to open the User-Defined Scenarios window and follow the steps listed below. 1. Assign a name for the scenario in Scenario Name textbox. 2. Click on Select to locate the saved scenario Excel file, and then click on Open (Figure 36). 3. Adjust the under- and overutilization thresholds as defined in Step 3 of Section 7.1. 4. Click on the Save & Process button to select the desired directory for saving the report and start the analysis (Figure 37). After a confirmation message (Figure 38), the software shows the analysis area-level and region-level values based on user-defined scenarios. The definitions of costs and columns are provided in Step 3 of Section 7.1.
74 7.3 Generate Summary Report To export the summary of all optimal and user-defined scenarios into an Excel file, users can click on the Generate Summary Report button. This process creates an Excel file containing reported values in a display table (Figure 39) along with the total number of purchased, salvaged, and relocated equipment in the analysis area and the operation cost (in dollars) before and after performing the optimization/ user-defined scenario. Note: All performed optimization and user-defined sce- narios will be available in the summary report, where each row represents the information of one scenario solution. Figure 36. Import the scenario Excel file into the software. Figure 37. Save the scenario report.
75 Figure 38. Screenshot of message for successfully creating a scenario. Figure 39. Sample of the summary report.
76 This chapter presents a use case example to illustrate the steps discussed in previous chapters. The input data is pro- vided in the âExample Problemsâ folder so that the users can follow the same steps and get identical results. Reviewing the UPM softwareâs user manual is recommended before going through the example. This example illustrates how to create user-defined scenarios and compare the results with the optimal solutions. The input data for truck tractors with NAFA code 8810 in 2018 is used in this example. The data for this case example is in the âExample Problemâ folder. Step 1: Define the Analysis Area Type in âExample Problemâ to define the analysis area in this step. Step 2: Update the Distance Matrix The distance matrix âExample ProblemâDistance Matrix.xlsxâ in the âDistance Matricesâ folder should be selected and imported into the software. Step 3: Import the Data âExample ProblemâInput Data.xlsxâ in the âExample Problemâ folder contains the input parameters for truck tractors with NAFA code 8810 in 2018. Users need to import and process the data in the âInputâ tab in the software. Step 4: Input Optimization Parameters After importing the data, the equipment class and analysis year should be specified in the âUtilization Managementâ tab. In this example, truck tractors with NAFA code 8810 in 2018 are selected. Step 5: Manage Fleet Utilization The default parameters are shown in Figure 40, and the default 20% thresholds for under- and overutilization are selected in this example. The transportation unit cost is imported from âExample ProblemâDistance Matrix.xlsxâ in the âTransportation Cost Matricesâ folder, and it is assumed to be $1.00 per mile. Figure 41 shows the optimal utilization management results for different regions in the analysis area. The total operating cost of truck tractors before the optimization is $163,112. The total operating cost is reduced to $142,260 after finding the optimal management plan. Step 6: Analyze User-Defined Scenarios Users can define scenarios using a template Excel file that is provided in the UPM software package. In this example, sample scenario files âScenario-1.xlsxâ and âScenario-2.xlsxâ in the âExample Problemâ folder are used. The template Excel file has two tabs: âPurchase/Salvageâ tab The number of purchased and salvaged pickup trucks in each region should be entered in the âPurchase/ Salvageâ tab, as shown in Figure 42. In the scenario defined in sample file âScenario-1.xlsx,â District 3 pur- chases one truck tractor and District 11 salvages a truck tractor (see Figure 42). âRelocationâ tab In the âRelocationâ tab, users define the number of truck tractors that need to be moved among regions. In this example, District 2 sends one truck tractor to District 4, and District 10 sends two truck tractors to District 8 (see Figure 43). Users need to load the predefined scenario into the soft- ware, as shown in Figure 44. The name of the scenario is defined as âScenario 1â in this example, and the âScenario-1. xlsxâ Excel file is uploaded to the software. The under- and overutilization thresholds are assumed to be the default value (20% for both over- and underutilization). After saving and processing the predefined scenario, the UPM software compares the total and operating costs of the user-defined scenario with the cost of the do-nothing scenario. In addition, detailed costs in each region are shown in a table in the software (see Figure 45). The defined scenarios increased âTotal Cost After Scenarioâ to $270,699 with an C H A P T E R 8 Use Case Example
77 operating cost of $168,812. Both costs are higher than those that were optimally found by the UPM software. The UPM software generates all outputs of optimization for the defined scenarios in an Excel file. The output of this example is saved as âScenario-1 Report.xlsxâ in the âExample Problemâ folder. Figure 46 shows the analysis area-level and region-level optimal annual mileage of truck tractors with NAFA code 8810. In addition, the utilization levels based on the predefined scenario are compared to the optimal values shown in red, yellow, and green, which indicate overutilization, underutilization, and acceptable utilization, respectively. In this example, District 3 utilizes truck tractors in an acceptable range. Since District 3 buys one equipment unit based on Scenario 1, the purchase cost is reported as $100,459, and the operating cost is decreased from $10,957 to $10,681. As shown in Figure 47, the âDetailed Costsâ tab reports transportation, purchase, and salvage costs. Moreover, the IDs of truck tractors that need to be relocated or salvaged are suggested. For instance, District 2 should relocate one truck tractor with ID 537396 to District 4. District 11 also needs to salvage the equipment ID 40568. Figure 40. Default parameters for utilization management of truck tractors. Figure 41. Optimal utilization plans for truck tractors with NAFA code 8810.
78 Figure 42. The number of purchased and salvaged equipment units in the user-defined scenario. Figure 43. Defining the relocation matrix in the user-defined scenarios. Figure 48 shows that two truck tractors with ID 5537151 and 336617 in District 7 are overutilized. District 12 only has one truck tractor, and its average utilization is 23,396 miles. More truck tractors are needed to lower the utilization level. As a result, District 12 buys a truck tractor instead of District 3. Also, one underutilized truck tractor is transferred from District 7 to District 12. The new scenario is defined in the âScenario-2.xlsxâ file in the folder âExample Problem.â In this scenario, District 12 buys one truck tractor, District 11 salvages one truck tractor, one truck tractor is moved from District 2 to 4, two are moved from District 10 to 8, and one is moved from District 7 to 12 (Figure 49). Scenario 2 is loaded into the software, as shown in Figure 50. The Excel output file is saved as âScenario-2 Report.xlsxâ in the âExample Problemâ folder. The result of Scenario 2 is shown in Figure 51. The total cost is $261,492, with the operation cost of $159,558. To compare the results of the optimization, Scenario 1, and Scenario 2, users need to click on the âGenerate Summary Reportâ button in the âUtilization Managementâ tab, as shown in Figure 51. The output Excel file is saved as âSum- mary Report.xlsxâ in the âExample Problemâ folder. Figure 52 shows that the total cost is the lowest when the optimal plan is utilized. In addition, Scenario 2 is more efficient than Scenario 1, with lower total cost and operation costs.
79 Figure 44. Loading the user-defined scenarios. Figure 45. The result of the user-defined scenario.
80 Figure 46. Management reports in the user-defined scenario. Figure 47. Detailed costs in the user-defined scenario.
81 Figure 48. Equipment-level analysis in the user-defined scenario. Figure 49. Defining a new user-defined scenario. Figure 50. Loading a new scenario into the software.
82 Figure 51. The result of the new scenario. Figure 52. Summary report of all scenarios.
Abbreviations and acronyms used without definitions in TRB publications: A4A Airlines for America AAAE American Association of Airport Executives AASHO American Association of State Highway Officials AASHTO American Association of State Highway and Transportation Officials ACIâNA Airports Council InternationalâNorth America ACRP Airport Cooperative Research Program ADA Americans with Disabilities Act APTA American Public Transportation Association ASCE American Society of Civil Engineers ASME American Society of Mechanical Engineers ASTM American Society for Testing and Materials ATA American Trucking Associations CTAA Community Transportation Association of America CTBSSP Commercial Truck and Bus Safety Synthesis Program DHS Department of Homeland Security DOE Department of Energy EPA Environmental Protection Agency FAA Federal Aviation Administration FAST Fixing Americaâs Surface Transportation Act (2015) FHWA Federal Highway Administration FMCSA Federal Motor Carrier Safety Administration FRA Federal Railroad Administration FTA Federal Transit Administration HMCRP Hazardous Materials Cooperative Research Program IEEE Institute of Electrical and Electronics Engineers ISTEA Intermodal Surface Transportation Efficiency Act of 1991 ITE Institute of Transportation Engineers MAP-21 Moving Ahead for Progress in the 21st Century Act (2012) NASA National Aeronautics and Space Administration NASAO National Association of State Aviation Officials NCFRP National Cooperative Freight Research Program NCHRP National Cooperative Highway Research Program NHTSA National Highway Traffic Safety Administration NTSB National Transportation Safety Board PHMSA Pipeline and Hazardous Materials Safety Administration RITA Research and Innovative Technology Administration SAE Society of Automotive Engineers SAFETEA-LU Safe, Accountable, Flexible, Efficient Transportation Equity Act: A Legacy for Users (2005) TCRP Transit Cooperative Research Program TDC Transit Development Corporation TEA-21 Transportation Equity Act for the 21st Century (1998) TRB Transportation Research Board TSA Transportation Security Administration U.S. DOT United States Department of Transportation
N O N -P R O F IT O R G . U .S . P O S TA G E P A ID C O LU M B IA , M D P E R M IT N O . 88 Transportation Research Board 500 Fifth Street, N W W ashington, D C 20001 AD D RESS SERVIC E REQ U ESTED ISBN 978-0-309-67366-2 9 7 8 0 3 0 9 6 7 3 6 6 2 9 0 0 0 0