Case Studies in Cross-Asset, Multi-Objective Resource Allocation (2019)

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.

63 This section details two tools developed to support agencies implementing cross-asset resource allocation approaches: a spreadsheet tool that implements the NCHRP Report 806 framework outlined in Chapter 2, and a web-based tool that demonstrates implementation of a web service for automating DEA analysis and helping visualize cross-asset allocation results. Cross-Asset Resource Allocation Spreadsheet Tool This section provides step-by-step guidance for using the Cross-Asset Resource Allocation Spreadsheet Tool (CARATS). This tool was initially developed and included in NCHRP Report 806 and was subsequently revised for the current project. The chapter covers detailed instructions on data requirements for the tool, using the tool, and interpreting the results. One worked example is also provided illustrating the use of the tool. CARATS allows users to input performance measures, project data, and measure weights. It then provides a ranking of projects based on the scores across different performance areas and the relative importance of the performance areas for the agency. The tool also optimizes the allocation of funds based on specific requirements and restraints on the budget. The tool can be found on the AASHTO web site at http://caratview.com. Note that documentation in this report may be superseded by updated tool documentation on the AASHTO site. System Requirements This tool is an Excel-based, spreadsheet tool that works with Microsoft Excel 7 or later versions. Macros must be enabled in Excel for the tool to run. When macros are enabled, a warning message will appear on the heading when the spreadsheet is opened. When prompted with such a warning the user should click “Enable Content.” Tool Components The tool is composed of the following eight worksheets: • Home worksheet provides an introduction to the tool and pointers to navigate to a particular worksheet. • Performance Measures, Project Impacts and weighting sheets (Weighting & Custom Weighting) are the main input worksheets. • Scaling worksheet can be either used with default setting of the linear curve obtained from project inputs, or can be modified per the agency’s requirements. • Scoring and Optimization worksheets present the major outputs of the tool. C H A P T E R 5 Tools for Supporting Cross-Asset Resource Allocation

64 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Using the Tool This section explains the input requirements, flow of the tool, and a step-by-step process of using the tool. Explanatory screenshots of the tool are shown throughout. The tool includes various default and calculated parameter inputs that users can override at their discretion. These parameters are explained on their associated worksheets. Cells within the tool are color coded to inform the user which cells are input and that contain formulas and should not be edited. User input is required only in the white-colored cells. In some cases, white-colored cells contain the default values calculated by the tool and can be overridden. Cells with colors other than white represent different result properties discussed further below. Input Requirements In order to use the tool, the following data are required: 1. List of performance measures. 2. Objective information for each measure: a. Whether the performance measure should be maximized or minimized and b. Whether the agency wants the performance measure values in format of percentage or total. 3. Project information: a. Project cost, b. Value of each performance measure before and after implementation of each project, and c. Investment area of each project. Tool Flowchart Users can navigate the tool using the flowchart presented in Figure 5-1. Navigate through worksheets using the tabs at the bottom of the spreadsheet. If there are any changes in the input, follow the flowchart to get to the appropriate results. Input performance measures and project data in the Performance Measures and Project Impacts worksheets, respectively, then either fill out the pairwise priority weight survey to identify weights of different measures in the Weighting worksheet or input the pre-decided weights in the Custom Weighting worksheet. Once the weights are added, use the Scaling worksheet to scale the project impacts on different measures. The button on the Scaling work- sheet results in scaling the inputs on a scale of 0–1 for all measures. The scaling can be changed by adding the appropriate scaling parameters in the scaling worksheet. Users can see the ranks Figure 5-1. Flow chart for tool navigation.

Tools for Supporting Cross-Asset Resource Allocation 65 in the Scoring worksheet and can obtain optimization results by adding inputs of user-based constraints in the Optimization worksheet. Home Figure 5-2 presents the home screen of the tool. This screen displays the tool and project information. It provides navigation buttons to access different components of the tool. The note explains the basic limit on number of projects that can be prioritized using this tool: a maximum of 200 projects at a time. The note also provides a reminder to enable macros in the spreadsheet. This worksheet is for reference and navigation purposes and does not need any user input. Figure 5-2. Home worksheet. Performance Measures The performance measures worksheet requires user input. In this worksheet, the user inputs details on performance measures that are used by the tool to prioritize different projects. The following steps describe the process of adding, removing, and editing performance measures and populating the model with the final performance measure list. 1. Add a Project-Level Performance Measure: a. Input the name of the performance measure in the first white cell. b. Once the name is entered, select the type of objective for the added measure. The drop- down in the objective cell has two options: “Maximize” and “Minimize.” Select the option that best fits the performance measure. This input is used in subsequent steps of the tool to prioritize projects that either maximize or minimize the given measure value. Figure 5-3 shows the performance measure worksheet and the objective drop down box.

66 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation c. After selecting an objective, select the preferred format of performance measure transfor- mation (total or percentage) from the dropdown menu in the cell. This selection indicates whether the user wants to maximize/minimize the total value of the performance measure or the percentage of the performance measure. d. Once these three sections (name, objective, and program level) are selected, click the Add Measure button. The performance measure will then appear in the list below the input section of the worksheet. The measure should be listed in one of the following formats shown in Table 5-1. Project-Level Performance Measure Program Objective <Measure Name> Minimize Total <Measure Name> <Measure Name> Maximize Total <Measure Name> <Measure Name> Minimize % <Measure Name> <Measure Name> Maximize % <Measure Name> Table 5-1. Performance measure formats. Figure 5-3. Adding a performance measure and objective. 2. Remove a Performance Measure: a. In the cell labeled Select Project-Level Performance Measure for Removal, select the measure to be removed using the dropdown list. The dropdown contains all the added measures. Once the measure is selected, click the Remove Measure button. Figure 5-4 presents a screenshot of the process. Observe that the particular measure is removed from the list in the section Performance Measures for Analysis. 3. Edit Performance Measure: In case the user wants to change the objective or the transforma- tion level for an added measure, there are two ways to edit the measure: a. (Recommended): Remove the measure following Step 2 above and add the measure with corrected format following Step 1 above. b. Edit the measure in the Performance Measures for Analysis section. i. To edit the name of the measure, simply change the name in the Program-Level Performance Measure column by typing the revision. Be sure to also edit the name in the Program Objectives column. ii. For editing the objective (Maximize or Minimize) or the transformation level format (Total or %) for a measure, edit the Program Objective column. Be careful not to create a spelling error as it could lead to an error in the tool functions.

Tools for Supporting Cross-Asset Resource Allocation 67 4. Prepare Decision Model: a. Once the details of the performance measures in the Performance Measures for Analysis list are verified as accurate, click the Prepare Decision Model button to populate the tool with these performance measures. It is important to note that if there is a need to change the performance measures after going through other worksheets in the tool, follow Steps 1, 2, or 3 as per the requirement and again press Prepare Decision Model button to incorporate the changes in the tool. This will delete any user input in the other worksheets entered before hitting this button and will populate the worksheet with redefined performance measures. Project Impacts The purpose of the Project Impact worksheet is to allow the user to input proposed project details and their impacts on each performance measure. In this worksheet, the user adds details on the projects that will be prioritized using this tool. This worksheet requires the following input: 1. Project Names: Enter the names of the projects in the white cells in the first column. Make sure to have a different name for each project. 2. Investment Areas: Enter the investment area for each project in the second column. Examples of investment areas could be regions (Urban, Sub-urban, Rural) or investment areas (Maintenance, Expansion, Safety, etc.). 3. Cost: Input the cost of each project in the third column. Make sure to keep the units of cost consistent across the projects. 4. Performance Measure Impacts: The next few columns require input on the impact of the project on the performance measures. For each performance measure, there are two columns named No Build and Build. a. No Build: In the No Build column for each measure, enter the value of the measure if the particular project is not built. b. Build: In the Build column for each measure, enter the value of the measure if the particular project is built. If only the absolute impact of a project is known, the other section (Build or No Build) can be kept at zero value. For example, if for any project only the net positive impact on a given measure is known, the value in the no-build section for the project can be entered as zero and the build section should include the net positive impact value. Figure 5-4. Removing a performance measure.

68 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-5. Project impacts worksheet with sample data. Figure 5-5 shows the Project Impacts worksheet with data input. Keep in mind that this data is for demonstrative purpose only. Weighting The Weighting worksheet guides the user in identifying the relative weights of different performance measures. The methodology used to identify weights is a pairwise comparison that prompts the user to select the relative importance of two measures. Note that if the user already has weights for the measures, these can be entered in the Custom Weighting worksheet. The instructions for the Custom Weighting worksheet are in the next section. If the user does not have pre-selected weights for the performance measures, the relative rating scale is one method that can be used to identify the weights. Figure 5-6 presents a screen- shot of the rating scale for three measures. The scale includes a pairwise rating system, for which each pair of measures can be rated based on their relative importance. The scale varies from <Measure 1> being extremely more important than <Measure 2> to <Measure 2> being extremely more important than <Measure 1>. The worksheet includes a scale for each possible pair between the performance measures. To calculate the weights: 1. Click the desired relative level of importance for each performance measure pair. 2. Once all pairs have been rated, click the Calculate button on the right of the scale list. The performance measure and pairwise priority weights are then populated in the blue cells on the right side of the worksheet.

Tools for Supporting Cross-Asset Resource Allocation 69 Custom Weighting If the user has pre-defined custom weights for the performance measures, the Custom Weighting worksheet is used to enter the weights. Figure 5-7 presents a screenshot of the Custom Weighting worksheet. The weights of each measure should be entered in the white cells corresponding to the measure. Make sure that the sum of the weights adds up to 1. Figure 5-6. Pairwise comparison survey. Figure 5-7. Custom weighting worksheet with example weights.

70 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Scaling The Scaling worksheet is used to normalize the performance impacts of projects on each measure. By default, the project impact values of each measure are scaled linearly from 0 to 1. Two columns under each measure are labeled: Rating and Value. The Rating column is populated with the range of values entered in the Build and No Build columns of the Project Impacts worksheet. The Value column is populated with the normalized value for each rating, where the values range between 0 and 1. The scaling is done in the direction of the performance measure objective as selected in the Performance Measure worksheet. For example, if the measure objective is to minimize the performance measure, the lowest value of impacts for that measure in the Project Impacts worksheet will be given a value of 1 and the highest value of impacts for the measure will be given a value of 0 in the Scaling worksheet. The user can override the normalization if the agency has better data explaining the relationship of the measure value with its performance. The following process is used to operate this tool: 1. To use the tool’s default scaling process, click the Generate Scales button. The white cells are automatically populated with the ratings and values for each measure. The scaled graph for each measure is presented below the table as a helpful visualization. 2. To apply a different normalization to the project impact values, the rating and value columns can be changed directly. However, be sure to keep the range of each value column between 0 and 1. Figure 5-8 indicates a non-linear normalization for Measure 2 and tool-default linear normalization for Measures 1, 3, and 4. Figure 5-8. Scaling worksheet.

Tools for Supporting Cross-Asset Resource Allocation 71 Scoring The Scoring worksheet is a results worksheet that presents the ranking of projects based on different measures. The table has three project information columns, two overall score columns and one column for every performance measure score. The first three columns, Project Name, Investment Area, and Cost provide details of each project in the respective fields. The fourth column, Overall Score, provides the ranking of the projects based on overall scores generated by the tool. The fifth column, Overall Score/Cost, provides the results of score/cost analysis for all the projects. The next columns present the scores of projects on each performance measure separately. On clicking the Generate Score button, the scores of projects are generated. The table is by default arranged in descending order of the overall score. The Overall Score/Cost column can be used as benefit–cost ratios for analysis of the projects. The table can be arranged based on any other column as per user’s choice. All the scores are color coded on a color pallet of Green to Red, where green represents higher scores and Red represents lower scores. Figure 5-9 presents the Scoring worksheet with scores of the example dataset. Figure 5-9. Scoring worksheet. Optimization The Optimization worksheet is a results worksheet that presents the results of project opti- mization based on inputs from previous worksheets and financial constraints. In performing an optimization, the system formulates the project selection problem as a knapsack problem and seeks to determine the specific projects to perform to maximize the score obtained for all projects subject to budget constraints. Note the underlying analytical approach used in performing the optimization is identical to that utilized in the prototype tool included in NCHRP Report 806 and documented in that report. The steps for optimizing are as follows: 1. Click the Optimization button. a. If a solution is found, a message box will appear with a message – “Finished! Solution Found.” Proceed with step 2. b. If the solution is not found, a message box will appear with a message – “A solution could not be found, please modify constraints and try again.” This message indicates that conflicting performance measures are entered and no solution can satisfy the model. In this case, the constraints and project impacts need to be revised. This will require revision in inputs in the Performance Measures worksheet and/or Project Impacts worksheet.

72 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Follow the flowchart [input] to redevelop the model. Figure 5-10 presents the optimiza- tion worksheet with example dataset. Once the solution is found as per Step 1(a), the results and further steps are explained below: 2. The white cell under Overall Budget is the default budget calculated as 2⁄3 of the total possible cost of all projects. This budget value can be overridden if desired. If a new budget value is entered, click the Optimization button again to reevaluate the worksheet with the new budget constraints. Make sure to input the budget in the same units as the cost input of the projects (e.g., if the cost is in $000s, enter the budget in $000s). 3. The cell under Current Allocation gives the current total fund allocation of all the proj- ects that are suggested to be built as per the optimization results. The green or red color of the cell indicates whether the current allocation is under or over the allocated budget, respectively. 4. The Program Score explains the overall performance of the program with the obtained optimization results. The program score will be 100% if all the projects can be built with the provided budget in the optimization tool. 5. The next table (Figure 5-11) presents the allocation of funds based on investment area: a. The Investment Area column lists the different investment areas. These are automatically populated when the optimization button is pressed. Figure 5-10. Optimization worksheet with optimization output.

Tools for Supporting Cross-Asset Resource Allocation 73 Figure 5-11. Resource allocation based on investment areas. b. The Minimum Allocation column is set to 0 by default. This can be overridden to provide a minimum budget to any specific investment area. Once the minimum alloca- tion is changed, click the Optimization button again to obtain new optimization results incorporating the change. c. The Maximum Allocation column shows the total cost of projects in each investment area by default. This field can also be overridden if there is an upper limit of funds in any given area. Once the maximum allocation is changed, click the Optimization button to obtain new optimization results incorporating the change. d. The Current Allocation column is the output presenting the total allocation of resources in each region. The column is color coded. The green or red color of the cell indicates whether the current allocation is under or over the investment area’s maximum allocation, respectively. e. The Current % Allocation column is the output presenting the percentage of overall budget that is allocated to the given investment area. 6. The next table (Figure 5-12) presents a detailed view of the optimization results. It shows, based on financial constraints, which projects should be built: a. The first three columns, Project Name, Investment Area, and Cost provide details of the projects. b. The fourth column, Funding Status, presents the optimization results showing either “Build” or “No Build.” The projects with Build in this column in green are suggested Figure 5-12. Optimization results.

74 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation to be built. The projects with No Build in this column in red are suggested not to build. The projects to be built are used for the current allocation calculations. c. The fifth column is labeled Must Fund. This column allows the user to indicate particu- lar projects that must be included in the list of funded projects. Each cell in this column has a dropdown element—Must Fund. If any project is converted to Must Fund using this column, click the Optimization button to incorporate this change into the tool results. d. The last column in this table, Overall Score, shows the overall score of the projects as calculated in the Scoring worksheet. 7. The last table in this worksheet (Figure 5-13) presents the resource allocation based on performance measures: a. The first column, Program Objective, presents the list of performance measures. This is automatically populated. b. The Lower Bound and Upper Bound columns are populated with the lowest and the highest possible value of the measure. By default, these cells are populated with the mini- mum and maximum values of the measures from the Project Impact input worksheet. These columns can be overridden by the user if a specific target on any given measure is desired. If any change is made in these columns, click the Optimization button to revise the optimization results incorporating these changes. c. The Current Performance column presents the current performance of the program in each measure. Figure 5-13. Resource allocation based on performance measures. Worked Example This section includes one worked example illustrating the use of the CARAT tool. The example uses illustrative data and is intended to help users understand the tool and how it might be applied in practice. Data Overview The worked example is of a hypothetical transportation system. The example data input includes four performance measure examples with all four possible combinations of their data formats: 1. Annual Toll-Fare Revenue: Maximize the total annual toll-fare revenue. 2. Lane Miles of Good or Better Pavements: Maximize the percentage of lane miles of good or better pavements. 3. Fatal and Serious Injury Crashes: Minimize the total fatal & serious injury crashes. 4. Structurally Deficient Deck Area: Minimize the percentage of structurally deficient deck area. The project impact data input has 20 projects, named Project 1 to Project 20. They are assigned to five different investment areas, named Region 1 to Region 5. The cost of each project ranges from 50 units to 110 units, with each unit assumed to be $1,000. The cost unit can be as per the range of project costs suited by the agency. Note that in practice, the cost per unit may be

Tools for Supporting Cross-Asset Resource Allocation 75 higher, and thus the range of project costs may be much greater, varying by one or more orders of magnitude. It is assumed that all these projects impact the four measures consistent with what is intuitive. For example, all projects increase the annual toll-fare revenue and decrease fatal and serious injury crashes. The extent of impact on each performance measure might depend on the type of project. In reality, it is possible that a project focused on one of the measures can negatively impact the other measures, but for this example, the projects are considered to have positive impact on all measures. Step 1: Enter Performance Measures The first step is to add the identified performance measures in the Performance Measures worksheet and prepare the tool with these measures. First, if the tool already has some other performance measures added, remove all those measures from the list. To do so, select the measure to remove from the dropdown next to Select Project-Level Performance Measure for Removal cell, and click the Remove Measure button. Do so for all the performance measures. Figure 5-14 shows three pre-existing perfor- mance measures that need to be removed, and the dropdown to be used to remove the measures. Figure 5-14. Removing a measure. Once all extra performance measures are removed from the tool, the Performance Measures for Analysis table should be clear. Now, input the name of the measure in the white cell next to Add a Project-Level Performance Measure cell. Next, select an objective for the measure, i.e., select from the dropdown whether to maximize or minimize the measure. Then select the level of transformation required, i.e., select from percentage or total from the dropdown next to Program-level Transformation cell. After having the three inputs, click the Add Measure button. The measure should be visible in the table for Performance Measures for Analysis. Follow the steps for all four measures. Once all measures are added, the screen looks like Figure 5-15. Once the inputs are verified, click the Prepare Decision Model button to populate the tool with these inputs. This will direct the tool to the Project Impacts worksheet.

76 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Step 2: Enter Project Data The second step is to input project details and their impacts on different performance mea- sures in the Project Impacts worksheet. Add the project names, cost, and their investment areas in the first three columns. As explained in the previous section, the input has 20 projects, named Project 1 to Project 20. They are ran- domly allotted 5 different investment areas, named Region 1 to Region 5. The cost of each project ranges from 50 units to 110 units. For realistic terms, each unit of cost can be assumed to be $1,000. As performance measures 1 and 2 are expected to be maximized, it can be anticipated that the projects will have a positive impact on these measures. It can be seen that the project build impacts are higher in value than project no-build impact values for these two measures. For performance measurements 3 and 4, the expected direction of change is negative. Hence, the input data for project build impacts are lower than the project no-build impacts. This is under the assumption that all projects have positive impacts on all the measures. Project impact data is found in Table 5-2 through Table 5-6. Figure 5-16 illustrates the Project Impacts worksheet populated with all the data. Figure 5-15. Performance measures. Project 5 Region 5 53 Project 6 Region 1 70 Project 7 Region 2 154 Project 8 Region 3 356 Project 9 Region 4 352 Project 10 Region 5 134 Project Name Investment Area Cost Project 1 Region 1 105 Project 2 Region 2 20 Project 3 Region 3 193 Project 4 Region 4 439 Project Name Investment Area Cost Project 11 Region 1 109 Project 12 Region 2 113 Project 13 Region 3 105 Project 14 Region 4 60 Project 15 Region 5 14 Project 16 Region 1 415 Project 17 Region 2 60 Project 18 Region 3 37 Project 19 Region 4 198 Project 20 Region 5 15 Table 5-2. Project impacts sample data—investment area and cost.

Tools for Supporting Cross-Asset Resource Allocation 77 Project Name Fatal & Serious Injury Crashes No Build Build Project 1 10 0 Project 2 10 0 Project 3 9 7 Project 4 7 0 Project 5 9 2 Project 6 1 1 Project 7 1 1 Project 8 8 6 Project 9 1 1 Project 10 10 0 Project Name Fatal & Serious Injury Crashes No Build Build Project 11 8 8 Project 12 5 0 Project 13 7 1 Project 14 7 0 Project 15 8 2 Project 16 5 2 Project 17 9 2 Project 18 6 0 Project 19 8 1 Project 20 7 1 Table 5-3. Project impacts sample data—fatal & serious injury crashes. Project Name Lane Miles of Good or Better No Build Build Project 1 0 19 Project 2 0 12 Project 3 0 10 Project 4 0 12 Project 5 0 16 Project 6 0 19 Project 7 0 20 Project 8 0 0 Project 9 0 0 Project 10 0 0 Project Name Lane Miles of Good or Better No Build Build Project 11 0 17 Project 12 0 0 Project 13 0 0 Project 14 0 0 Project 15 0 13 Project 16 0 0 Project 17 0 3 Project 18 0 0 Project 19 0 0 Project 20 0 0 Table 5-4. Project Impacts sample data—lane miles of good or better. Project Name Structurally Deficient Deck Area No Build Build Project 1 130863 0 Project 2 137694 0 Project 3 145132 0 Project 4 149161 0 Project 5 141715 0 Project 6 0 0 Project 7 0 0 Project 8 132468 0 Project 9 134800 0 Project 10 123755 0 Project Name Structurally Deficient Deck Area No Build Build Project 11 0 0 Project 12 129650 0 Project 13 140653 0 Project 14 139322 0 Project 15 0 0 Project 16 0 0 Project 17 25669 0 Project 18 45698 0 Project 19 26097 0 Project 20 0 0 Table 5-5. Project impacts sample data—structurally deficient deck area.

78 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Project Name Annual Toll-Fare Revenue No Build Build Project 1 0 137694 Project 2 0 145132 Project 3 0 132468 Project 4 0 130863 Project 5 0 0 Project 6 0 149161 Project 7 0 139322 Project 8 0 129650 Project 9 0 140653 Project 10 0 0 Project Name Annual Toll-Fare Revenue No Build Build Project 11 0 141715 Project 12 0 123755 Project 13 0 0 Project 14 0 0 Project 15 0 26097 Project 16 0 134800 Project 17 0 25669 Project 18 0 45698 Project 19 0 0 Project 20 0 0 Table 5-6. Project impacts sample data—annual toll-fare revenue. Figure 5-16. Project impacts.

Tools for Supporting Cross-Asset Resource Allocation 79 Step 3: Add Weights The third step is to input relative weights of the performance measures. This can be done in two possible ways: if the agency does not have pre-defined weights for the measures, it can use the pairwise priority weights survey in the Weighting worksheet to get the tool-generated weights for the measures. If the agency has pre-defined weights for the measures, input the weights in the Custom Weighting worksheet. For the example, all the measures are weighted equally at 25% in the Custom Weighting worksheet. The same results will be obtained if the survey is filled with all measures prioritized equally. Figure 5-17 and Figure 5-18 illustrate the obtained weights using both of the methods. Figure 5-17. Pairwise comparison survey. Figure 5-18. Custom weights.

80 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Step 4: Scale the Impacts The fourth step is to scale the impact values for different measures for the overall analysis in the Scaling worksheet. This is done to normalize the different units of performance measures in the inputs. In this example, all four measures have different units and hence need to be normalized to be compared together. The tool, by default, normalizes all inputs from 0–1 on a linear scale when the Scaling button is clicked. If the agency considers a particular measure’s impact to follow a different curve, the corresponding scale can be overridden. In this example, the tool scaling is done first to obtain the linear scaled values for all measures. The linear flow can be seen in the charts below the scale table. The scale of one measure is then overridden to follow a different random curve to illustrate the process. Either the endpoints of the scale, or the intermittent points describing the curve are entered in the table. Figure 5-19 illustrates the populated scaling worksheet. Figure 5-19. Scaling worksheet.

Tools for Supporting Cross-Asset Resource Allocation 81 Step 5: Generate Scores The fifth step is to click the Scoring button on the Scoring worksheet. This prompts the tool to calculate the overall score, overall score/cost, and scores for each performance measure for each project and rank the projects based on overall score. If any change in project data, weights, or scales is performed, new scores should be generated to reflect the change in inputs. Figure 5-20 shows the results on the scoring worksheet. Figure 5-20. Scoring worksheet.

82 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-21. Resource Allocation based on performance measures. Step 6: Optimize The final step is to optimize the results based on different constraints in the Optimization worksheet. All the possible scenarios are illustrated in the example. a. Use the default overall budget, i.e., 2⁄3 of the sum of all project costs. On clicking the Optimize button, the results obtained are indicated in Figure 5-21.

Tools for Supporting Cross-Asset Resource Allocation 83 Figure 5-22. Application of must fund constraint. b. Change one of the not funded projects to must fund in the Must Fund column and click Optimize. Figure 5-22 shows the results in which that specific project is changed to be funded and the overall program score changes.

84 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-23. Change in maximum allocation of one investment area. c. Change the maximum allocation of Region 1 to be less than its current allocation and click Optimize. Figure 5-23 shows the changed results where the current allocation of Region 1 changed to the maximum limit.

Tools for Supporting Cross-Asset Resource Allocation 85 Figure 5-24. Change in minimum allocation of one investment area. d. Change the minimum allocation of Region 3 to be higher than its current allocation and click Optimize. Figure 5-24 shows the changed results.

86 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-25. Change in Upper Bound of one program objective. e. Change the upper bound of performance measure total fatal & serious injuries to be less than current performance and click Optimize. Figure 5-25 shows the changed results.

Tools for Supporting Cross-Asset Resource Allocation 87 Figure 5-26. Change in lower bound of one program objective. f. Change the Lower Bound of % lane miles of Good and Better pavements to be higher than the current performance and click Optimize. Figure 5-26 shows the changed results.

88 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-27. Very Low overall budget with must-fund constraints. g. Change the overall budget to a very low amount and click Optimize. Figure 5-27 shows the changed results where the current allocation is in red as it is higher than the overall budget, but as one of the projects are constrained to “Must Fund,” the tool funds the project anyway.

Tools for Supporting Cross-Asset Resource Allocation 89 Figure 5-28. Very low overall budget with no must fund constraints. h. Clear the Must Fund restriction and click Optimize. Figure 5-28 shows the changed result where none of the projects are funded and hence the tool gives a message to change param- eters in order to find a solution.

90 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-29. Very high overall budget. i. Change the overall budget to a very high amount and click Optimize. Figure 5-29 shows the results where all projects are funded as they all have positive scores and fit in the overall budget Cross-Asset Resource Allocation Web Tool This chapter provides step-by-step guidance for using the Cross-Asset Resource Allocation Web Tool (CARAT). The chapter covers detailed instructions on data requirements for the tool, using the tool, and interpreting the results. A demonstrative example is used to illustrate the process along with the explanation. CARAT allows users to input project data, cost and performance measures. The web tool differs from the spreadsheet tool in that it utilizes DEA to determine the relative efficiency of each project. The results of the analysis are the relative efficiency and whether or not the project is selected for funding given a specified budget. The web tool demonstrates use of a web service for performing DEA that can be accessed by third-party systems. CARAT also provides visualiza- tions of the data and the analysis results. The tool is hosted at www.caratview.com.

Tools for Supporting Cross-Asset Resource Allocation 91 Tool Components The tool is composed of the following sections: • CARAT Home Page: Provides a basic outline of the tool and navigation buttons for other pages. • Load: Allows user to import project input data file. • Edit: User can review and edit the imported data and run the analysis for a given budget. • Visualize: Provides different charts for visualization of the analysis result. • About: Provides a brief description of the project and the tool. • Contact Us: Allows the user to contact the tool developers. The next few sections will go over each of these tool pages in detail. CARAT Home Page Figure 5-30 presents the home page of the tool, which provides navigation buttons to access different components of the tool. A “getting started” note is presented to give a basic outline of the methodology as well. This worksheet is for reference and navigation purposes and does not need any user input. Figure 5-30. CARAT home page. Load Page Figure 5-31 shows the Load page of CARAT. The load page allows the user to import the project data for analysis. The import file needs to be in comma separated values (CSV) format. The import file also needs to follow a specific table format, which is presented in Table 5-7. Use the name of the objective as the heading in the objective columns. To upload a CSV file, press the Upload button and select the file. Then press the Submit button.

92 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-31. CARAT load page. Project ID Description Objective 1 Score Objective 2 Score … … Objective N Score Project Cost 1 Road Widening 0.5 0.2 .. .. 0.7 $100,000 2 Intersection Improvements 0.8 0.4 .. .. 0.2 $200,500 Table 5-7. Example input table format. Edit Page Figure 5-32 shows the Edit page with the uploaded data. Notice that the columns toward the right, Relative Efficiency and Selected, are initially empty. These two columns are the result columns populated once the analysis is complete. By clicking on any of the projects, the details of the project open in a sub-window that can be edited and saved for further analysis. Figure 5-33 illustrates this for one of the projects in the example dataset. The top right corner cell of the table in the headers row has an add button that can be used to add any new project in the database. The last column has a delete button that can be used to remove a particular project from the analysis. Once the project details are reviewed, click the Analyze button on the bottom left of the page. A small sub-window will open up, enter the budget in millions and click Submit. For the example dataset, a budget of 200 billion dollars is added. Figure 5-34 illustrates the process. This process calls a service that performs DEA on the list of projects and returns the relative efficiency and whether or not the project is selected for funding based on the available budget. Figure 5-35 shows the page after the analysis is complete and the Relative Efficiency and Selected columns are populated. Once the analysis is complete, the dataset is sorted in descending order of relative efficiency values. The order can be changed by clicking on the column headers. If the table shows a “1” in the Selected column, then that project is selected for funding based on its relative efficiency and the available budget. The projects with a value of “0” in the selected column are not selected within the budget constraints. At the bottom of the Edit page, the visualize button directs the user to the Visualize page and the export project button exports the results in CSV format.

Tools for Supporting Cross-Asset Resource Allocation 93 Figure 5-32. CARAT edit page. Figure 5-33. Editing or adding projects.

94 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-34. Entering a budget. Figure 5-35. Relative efficiency and selected columns populated after analysis.

Tools for Supporting Cross-Asset Resource Allocation 95 Visualize Page Figure 5-36 shows the Visualize page of CARAT. This page provides access to different graphi- cal representations of the analysis results. The dropdown menu offers four different visualiza- tions from which to choose. Selecting any one of them and clicking on Submit opens a new tab with the particular visualization chart. Projects and Goals Flow Diagram The Projects and Goals Flow Diagram (also called a Sankey) shows the various contribu- tions to different goals by varying the widths of lines in the diagram. The example illustrating projects and their impacts on different goals is shown below in Figure 5-37. On the left side of Figure 5-36. CARAT visualize page. Figure 5-37. Projects and goals flow diagram.

96 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation the diagram are the different projects, and on the right side are the different goals or objectives. The proportion of the impact of each project on each goal is visualized by the width of the flow arrow from the project to the goal. The value of the impact from a particular project can be viewed by hovering the mouse over any of the lines. Project Efficiency and Cost The Project Efficiency and Cost visualization is a type of “parallel coordinates” diagram, where each variable is given its own axis and all the axes are placed in parallel to each other. Each axis can have a different scale, as each variable works off a different unit of measurement. Values are plotted as a series of lines that connect across all the axes. This means that each line is a collection of points placed on each axis, that have all been connected together. In CARAT, the Project Efficiency and Cost visualization has three axes: Project ID, Relative Efficiency, and Cost. Each line originates from the project ID and connects to the relative efficiency score and its cost. Figure 5-38 presents the parallel chart of the example data output. Figure 5-38. Project efficiency and cost.

Tools for Supporting Cross-Asset Resource Allocation 97 Projects and Goals Percentage Contribution The Projects and Goals Percentage Contribution visualization is a bar chart that presents the contribution of each goal to the project’s total score, shown as a percentage. The bars are coded for each measure and each stacked bar represents one project. Figure 5-39 illustrates the bar chart for the example result. Figure 5-39. Projects and goals percentage contribution.

98 Case Studies in Implementing Cross-Asset, Multi-Objective Resource Allocation Figure 5-40. Projects and goals actual contribution. Projects and Goals Actual Contribution The Projects and Goals Actual Contribution visualization presents the actual contribution of each goal to a project’s total score. This chart can be used to identify which projects have the highest impact on any given measure along with the distribution of impacts for every project. Figure 5-40 shows and example of this visualization.