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.
107 Chapter 3 â Spreadsheet Tool A spreadsheet tool implementing the CPMs presented in Chapter 2 was developed as part of NCHRP Project 17-89. This chapter provides the general functionality of the tool, describes how to use the tool for HSM analysis, and describes how to interpret results. The spreadsheet tool provides predicted FI and PDO crash frequencies, the distribution of K, A, B, and C severities, and the distribution of various crash types. The calculations in the spreadsheet tool are intended to replicate the methodology presented in Chapter 2. No additions or extensions are included. 3.1 Analysis Steps Conducting a safety evaluation with the spreadsheet tool involves the steps presented below, which are considered to be the routine steps used each time a safety evaluation is undertaken. The analyst is encouraged to calibrate the models and distributions for application to sites in the local jurisdiction. Step 1âDefine Project Limits Step 1 defines the physical extent of the entire area being evaluated, which typically includes two or more sites that are physically connected to form a functioning freeway. The selection of project limits will depend on the purpose of the study, which may be limited to one specific site, a group of contiguous sites, or an entire facility (and its associated sites). If comparing design alternatives, the project limits should be the same for all alternatives. Step 2âDefine Study Period In Step 2, the spreadsheet tool predicts crashes for a 1-year time period based upon a single set of AADT values (i.e., AADT for 1 year) and a single set of geometric design and traffic control features (i.e., the roadway does not change during the study period). If a user desires to predict crashes for multiple years with different AADT values and/or different geometric design and traffic control features, crashes for each year should be computed using separate Segment worksheets or separate versions of the spreadsheet, and the results manually summed. Step 3âAcquire Traffic Volume Data During Step 3, AADT volumes are needed for each year of the evaluation period. For a past period, the AADT volume may be determined by using automated recorder data or estimated from a sample survey. For a future period, the AADT volume may be a forecast estimate based on appropriate land use planning and traffic volume forecasting models. For each freeway segment, the following values are required: AADT volume of the freeway segment, AADT volume of the nearest entrance ramp upstream of the segment, and AADT volume of the nearest exit ramp downstream of the segments. If exit and entrance ramps are more than half a mile from the freeway segment, collection of ramp AADT is not necessary. For each ramp entrance speed-change lane, two values are required: the AADT volume of the freeway segment and the AADT volume of the entrance ramp. For each ramp exit speed-change lane, only the AADT volume of the freeway segment is required. The AADT volume of the exit ramp is not needed. Step 4 â Acquire Observed Crash Data A decision is made whether the EB Method will be applied. If the EB Method will be applied, then the observed crash data are also acquired during Step 4. The proposed HSM text in Chapter 2 of this
108 document provides guidance on when the EB Method is applicable. If the EB Method is not applicable, then proceed to Step 5. Step 5âDivide Project into Individual Sites Using the segmentation criteria in Chapter 1 of this document, in Step 5 the project is divided into individual sitesâfreeway segments, ramp entrance speed-change lanes, or ramp exit speed-change lanes. Step 6âAcquire Geometric Design and Traffic Control Data The data needed to apply the CPMs are acquired for each site in Step 6. These data represent the geometric design elements, traffic control features, and traffic demand characteristics that have been found to have some relationship to safety. The data are needed for each site in the project limits. This data may reveal that a site, initially believed to be homogeneous, contains sufficient geometric variability to justify further subdivision into two or more sites. If this is the case, Step 5 and Step 6 are repeated. Step 7âAssign Observed Crashes If it was decided in Step 4 to use the EB Method, then the crash data acquired in Step 3 are assigned to the individual sites on the Summary worksheet and the site-specific EB Method is applied in Step 7. If AADT is different in years during which observed crashes occurred and the study year selected in Step 2, the site-specific EB Method may be manually applied outside the spreadsheet. If crashes cannot be assigned to individual sites and are only known to have occurred within the project as a whole, the project-level EB Method may be manually applied outside of the spreadsheet. The HSM (1) and HSM Supplement (2) describe the steps of the site-specific EB Method and the project-level EB Method. If the EB Method is not applied, then proceed to Step 8. Step 8âObtain and Review Results When data is entered into the spreadsheet, results are computed automatically in Step 8. All calculations are done with cell formulas, and there are no Microsoft (MS) Excel macros. The individual Segment worksheets display FI and PDO predicted crash frequency, as well as the distribution of crash severities and crash types. The Summary worksheet displays FI and PDO predicted crash frequency for each site being analyzed as well as FI and PDO expected crash frequency if applicable. Cells containing key results are highlighted in green. 3.2 Getting Started This section describes the spreadsheet tool. It consists of the following worksheets: ï· Welcome: includes a foreword, acknowledgements, and disclaimer. ï· Introduction: includes a brief overview of the spreadsheet tool. ï· Segment 1 through Segment 20: contains inputs and associated calculations of predictive models, severity distribution functions, and crash type distributions for sites, which may be a freeway segment, ramp entrance speed-change lane, or ramp exit speed-change lane. ï· LocalValues: contains inputs to override default calibration factors for predictive models, severity distribution functions, and crash type distributions. ï· Summary: summary of crash predictions computed on each of the Segment worksheet, inputs for historical crash data, and computed expected crash frequency using the EB Method. ï· Menus: defines the pull-down options on other worksheets.
109 To navigate among worksheets, click on the worksheet tabs at the bottom of the workbook window. For the first evaluation conducted by the analyst, the Welcome and the Introduction worksheets should be selected, and their respective content reviewed. Figure 21 shows part of the Introduction worksheet. For all subsequent evaluations, the analyst should proceed directly to the Segment worksheets. Cells in the spreadsheet not used for data entry are locked to prevent inadvertent changes. The password to unlock worksheets is provided on the Instructions worksheet. Figure 21. Introduction worksheet. 3.3 Site Data Entry This section describes the guidance for entering site data in three main worksheets: Segment worksheets, LocalValues worksheet, and Summary worksheet. 3.3.1 Segment Worksheets A total of 20 identical Segment worksheets are included in the spreadsheet tool, thus allowing analysts to evaluate a project with up to 20 sites in one workbook. A sample portion of a Segment worksheet is shown in Figure 22 to illustrate basic data input interface. The cells with a yellow or blue background are for user input. A drop-down list is provided for some cells. The cells with red markers contain notes with guidance for selecting input values. Farther down on the Segment worksheets (not pictured), the safety performance function and each adjustment factor are computed. All computations are performed with cell formulas; the spreadsheet does not contain MS Excel macros.
110 Figure 22. Sample input of Segment worksheet. Figure 23 shows the predicted crash frequency by crash type and severity result. This information is displayed in the bottom of each Segment worksheet. Green cells are used to identify cells with key results. Figure 23. Sample summary results of Segment worksheet. Head On Right Angle Rear End Sideswipe Same Dir. Other Animal Fixed Object Other Object Parked Vehicle Other K 0.0036 - - - - - - - - - - 0.000 A 0.0475 - - - - - - - - - - 0.000 B 0.3696 - - - - - - - - - - 0.000 C 0.5792 - - - - - - - - - - 0.000 PDO 1.0000 - - - - - - - - - - 0.000 Total 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 Head On Right Angle Rear End Sideswipe Same Dir. Other Animal Fixed Object Other Object Parked Vehicle Other K 0.0033 0.0000 0.0002 0.0010 0.0002 0.0000 0.0000 0.0002 0.0000 0.0000 0.0001 0.002 A 0.0281 0.0000 0.0013 0.0081 0.0013 0.0003 0.0000 0.0015 0.0001 0.0000 0.0005 0.013 B 0.2955 0.0000 0.0138 0.0853 0.0134 0.0032 0.0000 0.0162 0.0011 0.0000 0.0054 0.138 C 0.6731 0.0000 0.0315 0.1942 0.0306 0.0073 0.0000 0.0369 0.0025 0.0000 0.0123 0.315 PDO 1.0000 0.0000 0.1003 0.9219 0.1380 0.0052 0.0091 0.1159 0.0065 0.0052 0.0000 1.302 Total 0.000 0.147 1.210 0.183 0.016 0.009 0.171 0.010 0.005 0.018 1.770 Head On Right Angle Rear End Sideswipe Same Dir. Other Animal Fixed Object Other Object Parked Vehicle Other K 0.0032 - - - - - - - - - - 0.000 A 0.0403 - - - - - - - - - - 0.000 B 0.3053 - - - - - - - - - - 0.000 C 0.6511 - - - - - - - - - - 0.000 PDO 1.0000 - - - - - - - - - - 0.000 Total 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 Predicted Crash Frequency by Crash Type and Severity (crashes/year) - Exit Speed-Change Lane (EX) Severity Severity P(j) Multiple-Vehicle Crash Type Single-Vehicle Crash Type Total Predicted Crash Frequency by Crash Type and Severity (crashes/year) - Basic Freeway Segment (FS) Multiple-Vehicle Crash Type Single-Vehicle Crash Type Severity Severity P(j) Total Predicted Crash Frequency by Crash Type and Severity (crashes/year) - Entrance Speed-Change Lane (EN) Severity Severity P(j) Multiple-Vehicle Crash Type Single-Vehicle Crash Type Total
111 3.3.2 LocalValues Worksheet The predictive models, severity distribution functions, and crash type distributions were developed with data from specific jurisdictions and time periods. Local calibration will account for any differences between conditions at the jurisdictions used to estimate the models and those present at the sites being evaluated. The LocalValues worksheet contains input fields in orange cells to override default calibration factors, severity distribution functions, and crash type distributions. The input values are applied to all 20 of the segment worksheets. If orange cells are left blank, default values are automatically applied. Figure 24 shows the local calibration factor input options. If available, local calibration values should be used in the predictive models. Figure 24. Local calibration factors in LocalValues worksheet. The SDF is used to obtain an estimate of the proportion of FI crashes that are K, A, B, and C crashes according to the KABCO scale. This in turn is used to estimate the frequency of K, A, B, and C crashes. The SDF should be calibrated to local conditions. If it cannot be calibrated and local values of the severity distribution are available, they should be input in the LocalValues worksheet shown in Figure 25. Figure 25. Local severity distribution functions in LocalValues worksheet. Similarly, the default crash type distribution presented in Chapter 2 is included in the LocalValues worksheet. If local values of crash type distribution are available, their use is preferred, and they should be input in the LocalValues worksheet shown in Figure 26.
112 Figure 26. Local crash type distribution in LocalValues worksheet. 3.3.3 Summary Worksheet The Summary worksheet displays crash predictions computed in each of the 20 Segment worksheets. It also allows inputs for observed (historical) crash data. If these data are input, the worksheet computes the expected FI and PDO crash frequencies using the site-specific EB Method. If observed crash data are not entered, then predicted crash frequencies are computed and displayed. Figure 27 shows the Summary worksheet using sample data from a project with one site. Head On Right Angle Rear End deswipe Same D Other Animal Fixed Object Other Object Parked Vehicle Other BFS No 0.002 0.033 0.599 0.122 0.022 0.005 0.154 0.006 0.01 0.048 Yes 0.001 0.061 0.712 0.08 0.014 0.001 0.098 0.007 0.003 0.023 ENSCL No 0.019 0.037 0.606 0.094 0.019 0 0.122 0.014 0.019 0.07 Yes 0 0.1 0.616 0.097 0.023 0 0.117 0.008 0 0.039 EXSCL No 0.006 0.02 0.526 0.189 0.012 0 0.175 0.014 0.012 0.047 Yes 0 0.028 0.808 0.1 0.014 0 0.05 0 0 0 Head On Right Angle Rear End deswipe Same D Other Animal Fixed Object Other Object Parked Vehicle Other BFS No 0.002 0.027 0.538 0.19 0.023 0.022 0.156 0.017 0.006 0.019 Yes 0.001 0.053 0.699 0.139 0.01 0.004 0.075 0.007 0.003 0.009 ENSCL No 0.003 0.054 0.468 0.207 0.024 0.02 0.187 0.015 0.002 0.02 Yes 0 0.077 0.708 0.106 0.004 0.007 0.089 0.005 0.004 0 EXSCL No 0 0.03 0.499 0.214 0.028 0.027 0.15 0.016 0.007 0.029 Yes 0.003 0.06 0.718 0.118 0.01 0.01 0.076 0 0 0.005 Head On Right Angle Rear End deswipe Same D Other Animal Fixed Object Other Object Parked Vehicle Other BFS No Yes ENSCL No Yes EXSCL No Yes Head On Right Angle Rear End deswipe Same D Other Animal Fixed Object Other Object Parked Vehicle Other BFS No Yes ENSCL No Yes EXSCL No Yes Crash Type Distributions, PDO - Local Value Site Type PTSU Operation Multiple-Vehicle Crash Type Single-Vehicle Crash Type Crash Type Distributions, FI - Local Value Site Type PTSU Operation Multiple-Vehicle Crash Type Single-Vehicle Crash Type Site Type PTSU Operation Multiple-Vehicle Crash Type Single-Vehicle Crash Type Multiple-Vehicle Crash Type Single-Vehicle Crash TypePTSU OperationSite Type Crash Type Distributions, PDO Crash Type Distributions, FI
113 Figure 27. Sample summary worksheet. The EB method applied in the spreadsheet uses one set of data for each site. The AADT, geometry, and other inputs are the same in the âexistingâ period corresponding to the crash data and the âfutureâ period for which analysis is being conducted. If a user wishes to apply the EB method for sites at which there are changes in volume or geometry, the spreadsheet tool can be used to compute the predicted crash frequency for both scenarios. Alternatively, expected crash frequency may be computed by manually applying the EB method outside of the spreadsheet. 3.4 Results For each site, the predicted crash frequency by crash type and severity level is displayed at the bottom of the corresponding Segment worksheet. Predicted crash frequency (FI and PDO) for every site is displayed on the Summary worksheet and expected crash frequency (if applicable) is displayed as well. 3.5 References 1. American Association of State Highway and Transportation Officials (AASHTO). 2010. Highway Safety Manual. Washington D.C. 2. American Association of State Highway and Transportation Officials (AASHTO). 2014. Highway Safety Manual Supplement. Washington D.C. Perform Emperical Bayes (EB) analysis? Years of crash data used? Analyze? FI PDO FI PDO FI PDO FI PDO FI PDO Site 1 Yes 0.47 1.30 0.6601 0.6966 16 66 0.5188 0.2687 2.81 16.44 Site 2 No - - Site 3 No - - Site 4 No - - Site 5 No - - Site 6 No - - Site 7 No - - Site 8 No - - Site 9 No - - Site 10 No - - Site 11 No - - Site 12 No - - Site 13 No - - Site 14 No - - Site 15 No - - Site 16 No - - Site 17 No - - Site 18 No - - Site 19 No - - Site 20 No - - 0.47 1.30 - - 16 66 - - 2.81 16.44Project Totals: Overdispersion Parameter, k Observed Crashes, All Years Expected Average Crash Frequency Entrance Speed-Change Lane (EN Type Description 0 Predicted Average Crash Frequency Weighted Adjustment, w Summary of Sites and Predicted Crashes Summary and EB Analysis Observed and Expected Crashes Yes 3