{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Instructions","Benefit Cost Worksheet","Reference Lists","CRF Sources","Predictive Method","Service Life"],"not_rendered":{"charts":0,"images":14,"macros":1,"external_links":0,"defined_names":103,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":49,"n_cols":6,"rows_kept":49,"cut":false,"hidden":false,"merged":[[38,4,39,4],[40,4,42,4],[43,4,44,4]]}
[]
[null,"Opening the File"]
["0.","Open the file in the Microsoft Excel desktop application and ensure Macros are enabled. Note: The tool automation may not function in the online version of Excel, in non-Excel spreadsheet applications, or if macros are disabled."]
[]
[null,"General Data Entry"]
[{"$f":"ROWS(A$6:A6)&\".\"","v":"1."},"Enter the county name, project location, and details of proposed improvements."]
[{"$f":"ROWS(A$6:A7)&\".\"","v":"2."},"Record your name and the date."]
[{"$f":"ROWS(A$6:A8)&\".\"","v":"3."},"Specify the estimated improvement cost."]
[{"$f":"ROWS(A$6:A9)&\".\"","v":"4."},"If information on annual maintenance cost is available as dollars, enter it in the Annual Maintenance Cost cell. If information on maintenance is available as a percent of improvement cost, enter that into the Annual Maintenance cell. \nThe other cell will update to show the corresponding percent or dollar amount. "]
[{"$f":"ROWS(A$6:A10)&\".\"","v":"5."},"The default value for Inflation on Crash Costs is 4%, and the Iowa DOT is not anticipating a need for a change in this value. If there is a specific reason to change the default value for Inflation on Crash Costs, please provide the explanation on page 2 of the worksheet."]
[{"$f":"ROWS(A$6:A11)&\".\"","v":"6."},"The default value for the Discount Rate is 4%, and the Iowa DOT does not anticipate a need for a change in this value. If there is a specific reason to change the default value for Discount Rate, please provide the explanation on page 2 of the worksheet."]
[{"$f":"ROWS(A$6:A12)&\".\"","v":"7."},"Specify the Estimated Service Life for the improvements. Click the \"Service Life List\" button to view service lifes for different improvements. Note: choose the service life that most appropriately aligns with the majority of the improvements."]
[{"$f":"ROWS(A$6:A13)&\".\"","v":"8."},"Use the dropdown labeled Method Choice to pick a Method for the safety benefit-cost calculations. The form will display a section corresponding to the chosen method. Click the \"Learn More\" button in the method section header if desired."]
[]
[null,"CRF Method Section"]
[{"$f":"LOWER(ROMAN(ROWS(A$16:A16)))&\".\"","v":"i."},"The default value for Traffic Growth Factor is 1%, and the Iowa DOT is not anticipating a need for a change in this value. If there is a specific reason to change the default value for the Traffic Growth Factor, please provide the explanation on page 2 of the worksheet."]
[{"$f":"LOWER(ROMAN(ROWS(A$16:A17)))&\".\"","v":"ii."},"Select the number of Crash Reduction Factors to be used in the project - 1, 2, or 3 CRFs may be used.\nIf a CRF is not known, click the \"CRF Sources\" button for information on the recommended sources."]
[{"$f":"LOWER(ROMAN(ROWS(A$16:A18)))&\".\"","v":"iii."},"The default value for Years of Crash History is 5. If crash history data is not available for the default 5 year period, update the form with the number of years of available data, and include an explanation on page 2 of the worksheet. Please refer to the Iowa DOT Safety Analysis Guidelines for additional information on years of crash data required for crash analysis."]
[{"$f":"LOWER(ROMAN(ROWS(A$16:A19)))&\".\"","v":"iv."},"Provide each CRF value."]
[{"$f":"LOWER(ROMAN(ROWS(A$16:A20)))&\".\"","v":"v."},"Enter the number of crashes in each KABCO category over the history period. These values should be whole numbers. Crash data is available in ICAT: "]
[null,"https://icat.iowadot.gov/ "]
[{"$f":"LOWER(ROMAN(ROWS(A$16:A22)-1))&\".\"","v":"vi."},"Continue to page 2 of the worksheet and confirm all explanations for non-default values have been added."]
[]
[null,"Predictive Method Section"]
[{"$f":"UPPER(ROMAN(ROWS(A$25:A25)))&\".\"","v":"I."},"Using the results from a previously conducted Highway Safety Manual analysis, enter the total crashes for Existing Conditions and for the Alternative conditions. These values may have decimals."]
[{"$f":"UPPER(ROMAN(ROWS(A$25:A26)))&\".\"","v":"II."},"Use the first Location Characteristics dropdown menu to indicate whether the project is located in an urban or rural location."]
[{"$f":"UPPER(ROMAN(ROWS(A$25:A27)))&\".\"","v":"III."},"Use the second Location Characteristics dropdown menu to indicate whether the project contains only intersections, only segments, or both."]
[{"$f":"UPPER(ROMAN(ROWS(A$25:A28)))&\".\"","v":"IV."},"Continue to page 2 of the worksheet and confirm all explanations for non-default values have been added."]
[]
[null,"Justifications"]
[{"$f":"A13+1&\".\"","v":"9."},"On page 2 of the Benefit Cost Worksheet, check whether justifications are needed. Fill in any needed explanations."]
[null,"The header will indicate whether any custom value is in use.\nIf custom values are in use, the label will update to indicate what category is using a non-default value and the explanation area will turn dark gray, indicating the user should enter data there."]
[]
[null,"General"]
[null,"Save the file. To share the results, the Benefit Cost analysis can be printed or exported to PDF."]
[]
[null,"To use the workbook for a new project:"]
[null,null,null,null,"Cell Shading Color Code"]
[null,null,null,null,"View","White cells may contain calculations and labels for the worksheet, which are not editable."]
[null,null,null,null,null,"The final Benefit Cost calculations are emphasized with light gray backgrounds."]
[null,null,null,null,"Edit","Dark gray cells are interactive. The user should pick from available options or input appropriate data."]
[]
[null,null,null,null,null,"Some dark gray cells contain default values. The user can change these values if appropriate. The user will be prompted to provide an explanation for the change."]
[null,null,null,null,"Edit","The cell color will change from dark gray to indicate when a default value has been changed. This cell is still editable. "]
[null,null,null,null,null,"Clicking in the cell will display a message, indicating what the default value is. Restoring that value will return the cell to dark gray."]
[null,null,null,null,"View","Areas of the second page will turn dark gray when a default value is changed, because additional information is now required."]
["a.","Save any existing work, if desired. Then use Excel's File \"Save As\" option so the workbook will not overwrite that existing work."]
["b.","Locate the \"Restore Default Values\" button at the top of the Benefit Cost Worksheet, to the right of the data entry section."]
["c.","Click the button. A message box will display, asking for confirmation that all data should be erased. Restoring the default values cannot be undone. "]

{"sheet":"Benefit Cost Worksheet","index":1,"n_rows":91,"n_cols":22,"rows_kept":91,"cut":false,"hidden":false,"merged":[[25,7,25,11],[26,11,27,11],[1,3,1,5],[21,0,45,0],[47,0,64,0],[11,0,18,0],[66,0,72,0],[49,9,49,11],[50,9,50,11],[69,5,69,7],[68,5,68,7],[67,5,67,7],[0,9,0,10],[0,0,9,0],[5,5,6,7],[9,5,10,11],[9,3,9,4],[7,9,7,10],[5,9,5,10],[1,9,1,10],[2,3,3,10],[79,3,79,11],[81,3,81,11],[83,3,83,11],[88,3,88,11],[86,3,86,11]]}
["Project Information",null,"County:",null,null,null,null,null,"Prepared By:"]
[null,null,"Location:",null,null,null,null,null,"Date Prepared:"]
[null,null,"Proposed Improvements:"]
[]
[null,null,"Estimated Improvement Cost:"]
[null,null,"Inflation on Crash Costs:",0.04,null,"Annual Maintenance: \n(% of  Improvement Cost)",null,null,null,0.02]
[null,null,"Discount Rate:",0.04,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annuity"]
[null,null,"Estimated Service Life:",20,null,null,null,"Annual Maintenance Cost:",null,{"$f":"param_ImplementationCost*param_AnnualMaintPercentOfImplmtn","v":0},null,null,null,null,null,null,null,"Present Value of Annual Maint. Costs",{"$f":"param_AnnualMaintCost*((1-(1+param_DiscountRate)^(-param_ServiceLife))/param_DiscountRate)","v":0}]
[]
[null,null,"Method Choice:","Please Select",null,{"$f":"IF(param_Method=tag_CRF, desc_CRF,\n      IF(param_Method=tag_Pred, desc_Pred, desc_PlsSlt)\n)","v":"This area will describe the chosen method. Please use the dropdown to pick a method."}]
[]
["Crash Cost Escalation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Years Elapsed to Date Prepared",{"$f":"IF(NOT(ISBLANK(datePrepared)), yearPrepared - c_yearCrashCostData, 0)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Crash Type","Abbreviation","Crash Costs","Escalated Crash Costs","Raw Escalated Crash Costs","Rounding Unit"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Fatal (K)","K",{"$f":"INDEX(t_CrashCosts[Crash Costs], ROWS(S$14:S14))","v":3253349},{"$f":"ROUNDUP('Benefit Cost Worksheet'!$U14/'Benefit Cost Worksheet'!$V14,0)*'Benefit Cost Worksheet'!$V14","v":3300000},{"$f":"'Benefit Cost Worksheet'!$S14*(1+param_inflationCrashCosts)^(yearsElapsedCrashCosts)","v":3253349},100000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Serious Injury (A)","A",{"$f":"INDEX(t_CrashCosts[Crash Costs], ROWS(S$14:S15))","v":3253349},{"$f":"ROUNDUP('Benefit Cost Worksheet'!$U15/'Benefit Cost Worksheet'!$V15,0)*'Benefit Cost Worksheet'!$V15","v":3300000},{"$f":"'Benefit Cost Worksheet'!$S15*(1+param_inflationCrashCosts)^(yearsElapsedCrashCosts)","v":3253349},100000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Minor Injury (B)","B",{"$f":"INDEX(t_CrashCosts[Crash Costs], ROWS(S$14:S16))","v":217607},{"$f":"ROUNDUP('Benefit Cost Worksheet'!$U16/'Benefit Cost Worksheet'!$V16,0)*'Benefit Cost Worksheet'!$V16","v":220000},{"$f":"'Benefit Cost Worksheet'!$S16*(1+param_inflationCrashCosts)^(yearsElapsedCrashCosts)","v":217607},5000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Possible/Unknown Injury (C)","C",{"$f":"INDEX(t_CrashCosts[Crash Costs], ROWS(S$14:S17))","v":102564},{"$f":"ROUNDUP('Benefit Cost Worksheet'!$U17/'Benefit Cost Worksheet'!$V17,0)*'Benefit Cost Worksheet'!$V17","v":105000},{"$f":"'Benefit Cost Worksheet'!$S17*(1+param_inflationCrashCosts)^(yearsElapsedCrashCosts)","v":102564},5000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Property Damage Only (O)","O",{"$f":"INDEX(t_CrashCosts[Crash Costs], ROWS(S$14:S18))","v":12656},{"$f":"ROUNDUP('Benefit Cost Worksheet'!$U18/'Benefit Cost Worksheet'!$V18,0)*'Benefit Cost Worksheet'!$V18","v":15000},{"$f":"'Benefit Cost Worksheet'!$S18*(1+param_inflationCrashCosts)^(yearsElapsedCrashCosts)","v":12656},5000]
[]
[]
[null,null,"Crash Reduction Factor (CRF) Method"]
["Method: Crash Reduction Factor ",{"$f":"C22&\":\"","v":"Traffic Growth Factor::"},"Traffic Growth Factor:",0.01]
[null,{"$f":"C23&\":\"","v":"Number of CRFs::"},"Number of CRFs:"]
[null,{"$f":"C24&\":\"","v":"Years of Crash History::"},"Years of Crash History:",5]
[]
[null,null,{"$f":"\"Combined CRF (\" & IF(calc_isValid_DomResdMthd, \"Dominant Common Residuals Method)\", \"Multiplicative Method*)\")","v":"Combined CRF (Dominant Common Residuals Method)"},null,null,null,null,{"$f":"IF(calc_isValid_DomResdMthd, desc_ReasonForDomResd, desc_ReasonForMultMthd)","v":"Iowa DOT's preferred methodology for combining CRFs is Dominant Common Residuals, since it is more conservative than the other options."}]
[null,null,null,"CRF 1:",null,"CRF 2:",null,"CRF 3:",null,"Combined CRF",null,{"$f":"IF(L32, \"Please select the number of CRFs\", \"\")","v":""}]
[null,{"$f":"C28&\":\"","v":"CRF Value (%):"},"CRF Value (%)",null,null,null,null,null,null,{"$f":"(1-J32)*100","v":0}]
[null,null,"Equivalent CMF",{"$f":"1-(D28/100)","v":1},null,{"$f":"1-(F28/100)","v":1},null,{"$f":"1-(H28/100)","v":1},null,null,null,"Is Dom. Com. Resd. Valid?"]
[null,null,"Combined CMF, if # used (DR)",{"$f":"D29","v":1},null,{"$f":"POWER(PRODUCT(D29,F29),MIN(D29,F29))","v":1},null,{"$f":"POWER(PRODUCT(D29,F29,H29),MIN(D29,F29,H29))","v":1},null,{"$f":"IF(param_numCRFs=1, D30,\nIF(param_numCRFs=2, F30,\nIF(param_numCRFs=3, H30,\n1)))","v":1},null,{"$f":"IF(param_numCRFs=3, MIN(int_Crf_1, int_Crf_2, int_Crf_3)>=0,\n  IF(param_numCRFs=2, MIN(int_Crf_1, int_Crf_2)>=0,\n  MIN(int_Crf_1)>=0\n))","v":true}]
[null,null,"Combined CMF, if # used (Mult)",{"$f":"D29","v":1},null,{"$f":"D29*F29","v":1},null,{"$f":"D29*F29*H29","v":1},null,null,null,"Is data entered before # CRF picked?"]
[null,null,"Pick Dom Com. Resd. Or Mult. For CMF:",{"$f":"IF(MIN(int_Crf_1)>=0, D30, D31)","v":1},null,{"$f":"IF(MIN(int_Crf_1, int_Crf_2)>=0, F30, F31)","v":1},null,{"$f":"IF(MIN(int_Crf_1, int_Crf_2, int_Crf_3)>=0, H30, H31)","v":1},null,{"$f":"IF(param_numCRFs=1, D32,\nIF(param_numCRFs=2, F32,\nIF(param_numCRFs=3, H32,\n1)))","v":1},null,{"$f":"AND(NOT(ISBLANK(int_Crf_1)), ISBLANK(param_numCRFs))","v":false}]
[]
[null,null,"Crash Severity","Rounded Societal Costs",null,"Crash History (Total):",null,"CRF (from above)",null,"Crash History - Annual Avg",null,"Crashes Saved Annually"]
[null,"K","Fatal (K)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B35,t_EscalatedCrashCosts_severity, 0))","v":3300000},null,null,null,{"$f":"calc_combCRF","v":0},null,{"$f":"F35/param_CrashHistoryYears","v":0},null,{"$f":"a_HistoricAnnualCrashes*(a_inputCRF/100)","v":0}]
[null,"A","Serious Injury (A)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B36,t_EscalatedCrashCosts_severity, 0))","v":3300000},null,null,null,{"$f":"calc_combCRF","v":0},null,{"$f":"F36/param_CrashHistoryYears","v":0},null,{"$f":"a_HistoricAnnualCrashes*(a_inputCRF/100)","v":0},null,null,null,"when g = int"]
[null,"B","Minor Injury (B)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B37,t_EscalatedCrashCosts_severity, 0))","v":220000},null,null,null,{"$f":"calc_combCRF","v":0},null,{"$f":"F37/param_CrashHistoryYears","v":0},null,{"$f":"a_HistoricAnnualCrashes*(a_inputCRF/100)","v":0},null,null,null,"https://pages.stern.nyu.edu/~adamodar/New_Home_Page/PVPrimer/pvprimer.htm"]
[null,"C","Possible/Unknown Injury (C)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B38,t_EscalatedCrashCosts_severity, 0))","v":105000},null,null,null,{"$f":"calc_combCRF","v":0},null,{"$f":"F38/param_CrashHistoryYears","v":0},null,{"$f":"a_HistoricAnnualCrashes*(a_inputCRF/100)","v":0},null,null,null,"Growing Annuities"]
[null,"O","Property Damage Only (O)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B39,t_EscalatedCrashCosts_severity, 0))","v":15000},null,null,null,{"$f":"calc_combCRF","v":0},null,{"$f":"F39/param_CrashHistoryYears","v":0},null,{"$f":"a_HistoricAnnualCrashes*(a_inputCRF/100)","v":0},null,null,null,"The Process Of Discounting"]
[]
[null,null,"Crash Severity","Annual Societal Benefit",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Growing Annuity"]
[null,"K","Fatal (K)",{"$f":"D35*L35","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,"Benefit, first year",{"$f":"SUM(a_yrScBenefit_CRF)","v":0}]
[null,"A","Serious Injury (A)",{"$f":"D36*L36","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,"Present Value of grown benefit",{"$f":"IF(param_TrafficGrowthFactor = param_DiscountRate,\n          calc_yrScBenefit_CRF * param_ServiceLife,\n          calc_yrScBenefit_CRF/(param_DiscountRate-param_TrafficGrowthFactor)*(1-((1+param_TrafficGrowthFactor)/(1+param_DiscountRate))^param_ServiceLife)\n    )","v":0}]
[null,"B","Minor Injury (B)",0]
[null,"C","Possible/Unknown Injury (C)",0]
[null,"O","Property Damage Only (O)",0]
[]
["Method: Expected Crashes",null,"Predictive Method"]
[]
[null,null,"Existing Condition (All Crashes):",null,null,null,null,"Location Characteristics:",null,"Please Select"]
[null,null,"Alternative (All Crashes):",null,null,null,null,null,null,"Please Select"]
[null,null,"Percent Change in Crashes",{"$f":"IF(param_AllCrashes_ExpAlt*param_AllCrashes_ExpEC=0,\n           \"n/a\",\n           TEXT(\n              ABS((1-(param_AllCrashes_ExpAlt/param_AllCrashes_ExpEC))),\"0.00%\")\n          &IF(param_AllCrashes_ExpAlt<=param_AllCrashes_ExpEC,\n                \" reduction in crashes\",\n                \" increase in crashes\"))","v":"n/a"},null,null,null,null,null,{"$f":"c_dateCrashDistData","v":"Iowa Crash Distribution 2017-2021"}]
[null,null,"Crash Severity","Crash Distribution Based on Location Characteristics",null,"Existing Condition Expected Crashes - Service Life",null,"Alternative Expected Crashes - Service Life",null,"Crashes Saved Over Service Life",null,"Crashes Saved Annually"]
[null,"K","Fatal (K)",{"$f":"_xlfn.IFNA(INDEX(a_CrashSvtyDist, MATCH($B54,a_CrashSvtyDist_abbrv,0), MATCH(calc_LocationChrct, a_CrashSvtyDist_colHeader,0)),0)","v":0},null,{"$f":"param_AllCrashes_ExpEC*lookup_CrashDist","v":0},null,{"$f":"param_AllCrashes_ExpAlt*lookup_CrashDist","v":0},null,{"$f":"a_expectedCrashes_ex-a_expectedCrashes_alt","v":0},null,{"$f":"J54/param_ServiceLife","v":0}]
[null,"A","Serious Injury (A)",{"$f":"_xlfn.IFNA(INDEX(a_CrashSvtyDist, MATCH($B55,a_CrashSvtyDist_abbrv,0), MATCH(calc_LocationChrct, a_CrashSvtyDist_colHeader,0)),0)","v":0},null,{"$f":"param_AllCrashes_ExpEC*lookup_CrashDist","v":0},null,{"$f":"param_AllCrashes_ExpAlt*lookup_CrashDist","v":0},null,{"$f":"a_expectedCrashes_ex-a_expectedCrashes_alt","v":0},null,{"$f":"J55/param_ServiceLife","v":0}]
[null,"B","Minor Injury (B)",{"$f":"_xlfn.IFNA(INDEX(a_CrashSvtyDist, MATCH($B56,a_CrashSvtyDist_abbrv,0), MATCH(calc_LocationChrct, a_CrashSvtyDist_colHeader,0)),0)","v":0},null,{"$f":"param_AllCrashes_ExpEC*lookup_CrashDist","v":0},null,{"$f":"param_AllCrashes_ExpAlt*lookup_CrashDist","v":0},null,{"$f":"a_expectedCrashes_ex-a_expectedCrashes_alt","v":0},null,{"$f":"J56/param_ServiceLife","v":0},null,null,null,null,"https://financeformulas.net/Present_Value_of_Annuity.html"]
[null,"C","Possible/Unknown Injury (C)",{"$f":"_xlfn.IFNA(INDEX(a_CrashSvtyDist, MATCH($B57,a_CrashSvtyDist_abbrv,0), MATCH(calc_LocationChrct, a_CrashSvtyDist_colHeader,0)),0)","v":0},null,{"$f":"param_AllCrashes_ExpEC*lookup_CrashDist","v":0},null,{"$f":"param_AllCrashes_ExpAlt*lookup_CrashDist","v":0},null,{"$f":"a_expectedCrashes_ex-a_expectedCrashes_alt","v":0},null,{"$f":"J57/param_ServiceLife","v":0}]
[null,"O","Property Damage Only (O)",{"$f":"_xlfn.IFNA(INDEX(a_CrashSvtyDist, MATCH($B58,a_CrashSvtyDist_abbrv,0), MATCH(calc_LocationChrct, a_CrashSvtyDist_colHeader,0)),0)","v":0},null,{"$f":"param_AllCrashes_ExpEC*lookup_CrashDist","v":0},null,{"$f":"param_AllCrashes_ExpAlt*lookup_CrashDist","v":0},null,{"$f":"a_expectedCrashes_ex-a_expectedCrashes_alt","v":0},null,{"$f":"J58/param_ServiceLife","v":0}]
[]
[null,null,"Crash Severity","Rounded Societal Costs",null,"Annual Societal Benefit",null,null,null,null,null,null,null,null,null,null,null,null,"Annuity"]
[null,"K","Fatal (K)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B61,t_EscalatedCrashCosts_severity, 0))","v":3300000},null,{"$f":"D61*L54","v":0},null,null,null,null,null,null,null,null,null,null,null,"Benefit, first year",{"$f":"SUM(a_yrScBenefit_Pred)","v":0}]
[null,"A","Serious Injury (A)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B62,t_EscalatedCrashCosts_severity, 0))","v":3300000},null,{"$f":"D62*L55","v":0},null,null,null,null,null,null,null,null,null,null,null,"Present Value of Annual Benefit",{"$f":"calc_yrScBenefit_Pred*((1-(1+param_DiscountRate)^(-param_ServiceLife))/param_DiscountRate)","v":0}]
[null,"B","Minor Injury (B)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B63,t_EscalatedCrashCosts_severity, 0))","v":220000},null,0]
[null,"C","Possible/Unknown Injury (C)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B64,t_EscalatedCrashCosts_severity, 0))","v":105000},null,0]
[null,"O","Property Damage Only (O)",{"$f":"INDEX(t_EscalatedCrashCosts, MATCH(B65,t_EscalatedCrashCosts_severity, 0))","v":15000},null,0]
[]
["Benefit Cost",null,"Total Benefits and Costs"]
[null,null,null,null,null,"Implementation Cost",null,null,null,{"$f":"param_ImplementationCost","v":0}]
[null,null,null,null,null,"Present Value of Maint.",null,null,null,{"$f":"calc_PVannuity_maint","v":0}]
[null,null,"Present Value Benefits\n(Societal Benefits)",{"$f":"IF(param_Method=tag_CRF,\n             calc_PVannuity_CRF_growthFactor,\n            IF(param_Method =tag_Pred,\n                   calc_PVannuity_Pred,\n                  0\n             )\n    )","v":0},null,"Present Value Costs \n(Impl. and Maint. Costs)",null,null,null,{"$f":"param_ImplementationCost\n  + calc_AnnualizedImplCosts","v":0}]
[]
[null,null,"Present Value Net Return",{"$f":"calc_AnnualBenefit\n- calc_TotalAnnualCosts","v":0}]
[null,null,"Benefit Cost Ratio",{"$f":"IF(calc_TotalAnnualCosts=0,\n     \"\",\n     calc_AnnualBenefit/calc_TotalAnnualCosts)","v":""},null,null,null,"Form continues on next page"]
[]
[]
[]
[null,null,{"$f":"IF(bool_dfltUsed_all, desc_allDflt, desc_notDflt)","v":"No Input Necessary - No Custom Values In Use"}]
[]
[null,null,"General Customizations"]
[null,null,{"$f":"IF(param_inflationCrashCosts=dflt_inflationCrashCosts, desc_dflt_noJustify, desc_dflt_justify) & \"Crash Costs\"","v":"No change to Crash Costs"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Is using default value?"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Inflation on crash costs",{"$f":"param_inflationCrashCosts=dflt_inflationCrashCosts","v":true}]
[null,null,{"$f":"IF(param_DiscountRate=dflt_DiscountRate, desc_dflt_noJustify, desc_dflt_justify) &\"Discount Rate\"","v":"No change to Discount Rate"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Discount Rate",{"$f":"param_DiscountRate=dflt_DiscountRate","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Maintenance",{"$f":"param_AnnualMaintPercentOfImplmtn=dflt_MaintCostPerc","v":true}]
[null,null,{"$f":"IF(param_AnnualMaintPercentOfImplmtn=dflt_MaintCostPerc, desc_dflt_noJustify, desc_dflt_justify) & \"Maintenance Costs\"","v":"No change to Maintenance Costs"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Traffic Growth Factor",{"$f":"param_TrafficGrowthFactor=dflt_TrafficGrowthRate","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Years of Crash History",{"$f":"param_CrashHistoryYears=dflt_CrashHistory","v":true}]
[null,null,"Method-Specific Customizations"]
[null,null,{"$f":"IF(param_Method<>tag_CRF, desc_dflt_notAppl &tag_CRF,\n            IF(bool_dfltUsed_GrowthFactor, desc_dflt_noJustify,desc_dflt_justify) & \"Traffic Growth Rate\"\n)","v":"Only applicable for CRF Method"}]
[]
[null,null,{"$f":"IF(param_Method<>tag_CRF,desc_dflt_notAppl&tag_CRF,\n        IF(param_CrashHistoryYears=dflt_CrashHistory,desc_dflt_noJustify,desc_dflt_justify) & \"Crash History\"\n)","v":"Only applicable for CRF Method"}]
[]
[null,null,{"$f":"IF(param_Method=tag_Pred,desc_dflt_PredMethod,IF(param_Method=tag_CRF,\"\",desc_dflt_SelectMethod))","v":"Please select a method above to see applicable method-specific customizations."}]

{"sheet":"Reference Lists","index":2,"n_rows":174,"n_cols":22,"rows_kept":174,"cut":false,"hidden":true,"merged":[[97,11,98,18],[2,11,3,18],[35,11,36,18],[4,11,5,18],[29,11,34,18],[95,11,96,18]]}
["Statistical Data",null,null,"These values may be updated annually."]
[null,"Year of Crash Cost Data",2018,"← Update year when crash cost data is updated",null,null,null,null,null,null,null,"Style Key"]
[null,null,null,null,null,null,null,null,null,null,null,"\"Message\": this text is a message or menu option which would be shown to users"]
[]
[null,null,null,"↓ Update this column of crash costs",null,null,null,null,null,null,null,"\"Label\": this text is not shown to users of the BCR worksheet, but is intended to help the designer understand the gist of the associated Message, when making revisions."]
[null,"Crash Type","Abbreviation","Crash Costs"]
[null,"Fatal (K)","K",3253349]
[null,"Serious Injury (A)","A",3253349]
[null,"Minor Injury (B)","B",217607]
[null,"Possible/Unknown Injury (C)","C",102564]
[null,"Property Damage Only (O)","O",12656]
[]
[null,"Dates for Crash Severity Distribution"]
[null,"First Year",2017,"← Update date when crash severity distribution is updated"]
[null,"Last Year",2021,"← Update date when crash severity distribution is updated"]
[null,null,null,null,null,null,"Dynamic phrase for Worksheet:",{"$f":"\"Iowa Crash Distribution \" & C14 & \"-\"&C15","v":"Iowa Crash Distribution 2017-2021"}]
[]
[null,null,null,"↓ Update all of these crash distribution columns ↓",null,null,null,null,"↓ Update all of these crash distribution columns ↓"]
[null,"Crash Type","Abbreviation","Urban - Intersections Only","Urban - Segments Only","Urban - Intersections and Segments","Rural - Intersections Only","Rural - Segments Only","Rural - Intersections and Segments"]
[null,"Fatal (K)","K",2.628049689104431E-3,2.8372251742797248E-3,2.7496825108853412E-3,1.4527673446247608E-2,1.0330204066838232E-2,1.094159664732963E-2]
[null,"Serious Injury (A)","A",1.4210433628198703E-2,1.3874128601374738E-2,1.4014876632801161E-2,5.1803557508326839E-2,3.1135597520751023E-2,3.414603934845889E-2]
[null,"Minor Injury (B)","B",9.5016188244218966E-2,7.2324867157412376E-2,8.1821480406386066E-2,0.15597760612288286,9.7478463638889901E-2,0.10599929808625282]
[null,"Possible/Unknown Injury (C)","C",0.21242498543735353,0.16485155754887146,0.18476165638606676,0.19119835589256609,0.10581511955247865,0.11825182187906438]
[null,"Property Damage Only (O)","O",0.67572034300112438,0.74611222151806167,0.71665230406386071,0.58649280702997664,0.75524061522104224,0.73066124403889432]
[]
[]
[]
["Menu Options and Default Values",null,null,"The default values are used in VBA to reset the worksheet, unless otherwise noted."]
[null,"Default Value - Crash Cost Inflation Rate",null,null,null,null,null,null,null,null,null,"Style Key, cont."]
[null,0.04,"*Any change would need a matching update in Instructions page text",null,null,null,null,null,null,null,null,"\"Dynamic Default Value\": this is the source for the default value that is used when the worksheet is reset. Furthermore, as the source for the default value, any changes here will take effect in VBA, and are referenced by name in Conditional Formatting. \nHowever, Input Message text referencing default values will NOT automatically update."]
[]
[]
[]
[null,"Default Value - Maint Costs"]
[null,0.02,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"This Input Message referencing a specific value will not automatically update to match the default values here. If updates are widely needed, this workbook contained VBA code for settinng/resetting the Data Validation which triggers that message."]
[null,null,null,null,null,null,null,null,null,null,null,"\"Record of default value\": this was the default value at the time the cell was populated, but changing this cell does NOT update the default value."]
[]
[]
[null,"Default Value - Discount Rate"]
[null,0.04,"*Any change would need a matching update in Instructions page text",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"The conditional formatting which highlights a cell using custom values is based on a named range, so it will update as the Source changes here."]
[]
[]
[]
[null,"Default Value - Service Life"]
[null,20]
[]
[]
[]
[null,"Methods",{"$f":" \"Default Value - \" & B49","v":"Default Value - Methods"}]
[null,"Please Select",{"$f":"B50","v":"Please Select"}]
[null,"CRF Method"]
[null,"Predictive Method"]
[]
[]
[]
[null,"Default Value - Traffic Growth Rate"]
[null,0.01,"*Any change would need a matching update in Instructions page text"]
[]
[]
[]
[null,"Crash History",{"$f":" \"Default Value - \" & B61","v":"Default Value - Crash History"}]
[null,3,5]
[null,4]
[null,5]
[null,6]
[null,7]
[null,8]
[null,9]
[null,10]
[]
[]
[]
[null,"Crash Locations",{"$f":" \"Default Value - \" & B73","v":"Default Value - Crash Locations"}]
[null,"Please Select",{"$f":"B74","v":"Please Select"}]
[null,"Rural"]
[null,"Urban"]
[]
[]
[]
[null,"Road Features",{"$f":" \"Default Value - \" & B80","v":"Default Value - Road Features"}]
[null,"Please Select",{"$f":"B81","v":"Please Select"}]
[null,"Intersections and Segments"]
[null,"Intersections Only"]
[null,"Segments Only"]
[]
[]
[]
[null,"Number of CRFs","Default Value - Number of CRFs (default as of 3/14/22; implementation in Conditional Formatting as well as VBA)"]
[null,1,"[default is blank; conditional formatting displays 1 CRF]"]
[null,2]
[null,3]
[]
[]
[]
["Message Text",null,null,"These messages are referenced in formulas, not in VBA.",null,null,null,null,null,null,null,"Style Key"]
[null,"Default Explanation - Method Explanations",null,null,null,null,null,null,null,null,null,{"$f":"L3","v":"\"Message\": this text is a message or menu option which would be shown to users"}]
[null,"This area will describe the chosen method. Please use the dropdown to pick a method."]
[null,"Method Explanations",null,null,null,null,null,null,null,null,null,{"$f":"L5","v":"\"Label\": this text is not shown to users of the BCR worksheet, but is intended to help the designer understand the gist of the associated Message, when making revisions."}]
[null,"The Crash Reduction Factor (CRF) Method applies one to three CRFs to historical crashes to estimate reductions in future crashes. Crash Modification Factors (CMFs) can be converted to CRFs. For additional information, click the \"Learn More\" button."]
[null,"The Predictive Method uses the outputs from the Highway Safety Manual's Predicted or Expected crash tool. That tool accounts for changing risk due to increased traffic volumes, so a growth factor is not used in these calculations. For additional information, click the \"Learn More\" button."]
[]
[]
[]
[null,"Description - Dominant Common Residuals"]
[null,"When Dominant Common Residuals:","Iowa DOT's preferred methodology for combining CRFs is Dominant Common Residuals, since it is more conservative than the other options."]
[null,"When not Dominant Common Residuals:","*Dominant Common Residuals method is not valid when any CRF reflects an increase in crashes. Multiplicative Method is used instead."]
[]
[]
[]
[null,"Description - Overall Use of Custom Values"]
[null,"No custom values in use:","No Input Necessary - No Custom Values In Use"]
[null,"Some custom values in use:","User Input Required - Custom Values Used"]
[]
[]
[null,"Description - Custom Values for Specific Method"]
[null,{"$f":"dflt_methods","v":"Please Select"},"Please select a method above to see applicable method-specific customizations."]
[null,{"$f":"tag_Pred","v":"Predictive Method"},"Predictive Method in this tool does not use or customize Crash History or Traffic Growth Factor."]
[null,{"$f":"tag_CRF","v":"CRF Method"},"CRF method will display the rows for justifying custom values, and does not need a message"]
[]
[]
[]
[null,"Description - Justification for Use of Custom Values"]
[null,"Custom Value used:","Justification for deviation from default ",null,"[parameter name]"]
[null,"Default Value used:","No change to ",null,"[parameter name]"]
[null,"Not applicable:","Only applicable for ",null,"[method name]"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Workbook Design"]

{"sheet":"CRF Sources","index":3,"n_rows":25,"n_cols":2,"rows_kept":25,"cut":false,"hidden":false,"merged":[[15,1,15,14],[1,1,1,14],[7,1,7,14]]}
["Sources for CRFs"]
[null,"The Iowa Planning-Level CRF List should be the first source reviewed when selecting CRFs or CMFs for use in safety analyses. It is anticipated that the CRF list will be updated periodically to include the latest research as it relates to Iowa. The Iowa Planning-Level CRF List is available on the Iowa DOT website."]
[null,"IOWA PLANNING-LEVEL CRF LIST:"]
[null,"https://iowadot.gov/traffic/pdfs/CRFListVersion.pdf"]
[]
[]
[]
[null,"If a desired countermeasure is not located within the Iowa Planning-Level CRF List, then the practitioner should follow the guidance identified in the Iowa DOT Safety Analysis Guide for using the CMF Clearinghouse to select a CMF."]
[null,"IOWA DOT SAFETY ANALYSIS GUIDE:"]
[null,"https://iowadot.gov/traffic/documents/2021-12-20-Draft-SAG-V5.pdf "]
[null,"CMF CLEARINGHOUSE:"]
[null,"http://www.cmfclearinghouse.org "]
[]
[]
["Crash Modification Factors and Crash Reduction Factors "]
[null,"CMFs and CRFs are values used to compute the anticipated number of crashes after implementing a countermeasure or safety treatment at a specific site. A CMF is a multiplicative factor that can be multiplied by the number of crashes at a specific site to compute the number of anticipated crashes after a countermeasure is implemented. A CRF is similar to a CMF but is stated as the percent reduction in crashes after a countermeasure is implemented at a specific site. CMF and CRF calculations are presented in the following figures."]
[]
[null,"Crash Modification Factor Calculation "]
[]
[]
[]
[]
[]
[]
[null,"Crash Reduction Factor Calculation "]

{"sheet":"Predictive Method","index":4,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[[1,1,1,14]]}
["Predictive Method"]
[null,"Additional information on the Predictive Method is available in the Iowa DOT Safety Analysis Guide or the Highway Safety Manual."]
[]
[null,"HIGHWAY SAFETY MANUAL:","http://www.highwaysafetymanual.org/Pages/default.aspx "]
[null,"IOWA DOT SAFETY ANALYSIS GUIDE:","https://iowadot.gov/traffic/documents/2021-12-20-Draft-SAG-V5.pdf "]

{"sheet":"Service Life","index":5,"n_rows":39,"n_cols":8,"rows_kept":39,"cut":false,"hidden":false,"merged":[[33,1,34,1],[2,0,2,1],[2,6,2,7],[14,6,14,7],[21,1,22,1],[25,7,26,7],[31,7,32,7]]}
["Approximate Improvement Service Life"]
[]
["Spot Location Improvements",null,null,null,null,null,"Spot Location Improvements"]
["Improvement","Service Life (years)","---","---","---",null,"Improvement","Service Life (years)"]
["INTERSECTIONS",null,"---","---","---",null,"RAILROAD CROSSINGS"]
["Roundabout",20,26,22,41,null,"Add Signals",10]
["Add Signals",15,38,25,"---",null,"Upgrade Warning Devices",10]
["Upgrade Signals",15,33,41,66,null,"Illuminate",15]
["Channelize / Add Turning Lanes",15,83,82,87,null,"Replace with Grade Separation",50]
["Improve Sight Distance",15,23,11,"---",null,"Eliminate",50]
["Upgrade Signs / Markings","6 / 2","---","---"]
["New Signs / Markings (None previously)","6 / 2","---","---","---"]
["Illuminate",15,"---","---","---"]
["Add Accel / Decel Lane",20,"---","---","---"]
["Rumble Strips (Applies only to","A.C. - 5",null,null,null,null,"Road Segment Improvements"]
["           accidents involving stop)","P.C. - 10","---","---","---",null,null,"Service Life"]
["Reconstruct Approach Angle",20,"---","---","---",null,"Improvement","(years)"]
["Add Beacons",10,"---","---","---",null,"Add Lanes",20]
["CURVES",null,"---","---","---",null,"Widen Pavement",20]
["Vertical Realignment",20,null,null,null,null,"Widen Shoulder",20]
["Horizontal Realignment",20,47,49,"---",null,"Widen Pavement / Shoulder",20]
["Horizontal / Vertical Realignment / ",20,"---","---","---",null,"Flatten Foreslopes",20]
["         Correct Superelevation",null,"---","---","---",null,"Widen Shoulder / Flatten Foreslope",20]
["Pavement Markings / Delineate","2 / 6","---","---","---",null,"FRICTION IMPROVEMENT"]
["BRIDGES",null,"---","---","---",null,"Overlay",10]
["Widen",20,null,null,null,null,"P.C. Grooving, Diamond ",10]
["Guardrail",15,"---","---","---",null,"    Profiling, Texturing"]
["Impact Attenuator",10,"---","---","---",null,"Signing",6]
["Replace",50,"---","---","---",null,"Edgeline Markings",2]
["Eliminate",50,null,null,null,null,"Horizontal Realignment",20]
["CULVERTS",null,38,75,"---",null,"Vertical Realignment",20]
["Lengthen",20,31,"---","---",null,"Horizontal / Vertical Realignment /",20]
["Guardrail or Grate",15,66,"---","---",null,"      Correct Superelevation"]
["Remove Headwall & Delineate",20,37,41,"---",null,"Roadway Lighting",15]
["   (outside shoulder line)",null,"---","---","---",null,"Relocate Driveways",20]
["HIGH FILLS",null,null,null,null,null,"Flatten Enterance Slopes",20]
["Guardrail",10,18,12,50,null,"Right of Way",100]
["Delineate",6,"---","---","---"]
["Flatten Foreslopes",20,null,null,null,null,null,"Iowa DOT Office of Traffic & Safety, July 2009"]