National Academies Press: OpenBook

Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports (2019)

Chapter: Appendix E - Microsoft Excel Templates

« Previous: Appendix D - Accessing Available Climate Projections
Page 89
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 89
Page 90
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 90
Page 91
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 91
Page 92
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 92
Page 93
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 93
Page 94
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 94
Page 95
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 95
Page 96
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 96
Page 97
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 97
Page 98
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 98
Page 99
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 99
Page 100
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 100
Page 101
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 101
Page 102
Suggested Citation:"Appendix E - Microsoft Excel Templates." National Academies of Sciences, Engineering, and Medicine. 2019. Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports. Washington, DC: The National Academies Press. doi: 10.17226/25497.
×
Page 102

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.

89 The project team constructed Microsoft Excel–based simulation templates that can be used by individual airport personnel to conduct their own Monte Carlo–based BCAs of climate change. The team developed two separate templates: one for sea level rise, and one for high temperatures. The scope and availability of localized data are different between the two, as are the implications of unknown future climate change; each is discussed in the following. The Excel templates may be found by searching for “ACRP Research Report 199” at www.TRB.org. Template for RSL Rise The Excel template for potential climate change events reflecting sea level rise includes local- ized historical estimates of the likelihood of EWL events plus projections of RSL for U.S. coastal areas that are near 153 different airports. Both the historical and projected estimates are based on published analyses from NOAA. The template file is composed of four separate Excel sheets: • Overview. This sheet provides a quick-start section plus a high-level overview of how the model embedded in the template works. • Data Tables. This sheet contains all of the base input data collected from NOAA, plus infor- mation about each of the 153 airports. • User Selections. This sheet is where the user can select the airport of interest and enter various assumptions regarding the global emissions scenario to be used, the costs that would be incurred for a specific mitigation investment, and the dollar damages that would be incurred both with and without the investment. • Results. This sheet presents the results of 5,000 Monte Carlo simulations based on the user selections. Data Tables At the outset, it is important to note that the user does not have to input any information in the Data Tables sheet and may skip directly to the User Selections sheet if desired. However, the Data Tables sheet does contain critical inputs that are needed for analysis. The first part of the Data Tables sheet contains information on the 153 airports. For each air- port, the nearest EWL and RSL stations are identified along with their distances from the airport. The relevant data for a partial sample of the airports are shown in Exhibit E-1. This is followed by a table showing relevant data for the EWL stations, including the esti- mated parameters that are used to produce historical exceedance probabilities of extreme A P P E N D I X E Microsoft Excel Templates

90 Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports water events at the indicated location. A sample is shown in Exhibit E-2. For those interested, details on exactly how the probabilities are calculated using these parameters are discussed in Appendix D. The next table lists the GMSL rise scenario probabilities estimated for three of the four latest emissions scenarios currently used by climate scientists. This table is reproduced as Exhibit E-3. As described earlier, after the user selects one of the emissions scenarios, the Excel model will generate random draws by interpolating between the GMSL rise scenarios. Combining random draws from the EWL probabilities with those from the GMSL rise scenarios results in probabi- listic localized projections of the height of future EWL events. REGION LOCID CITY STATE NLAT NLONG ELEVATION EWLID EWLDIST RSLID RSLDIST SO JKA GULF SHORES AL 30.28964 -87.67178 17.1 8735180 24.20 1005952725 17.77 WP NTD POINT MUGU CA 34.11927 -119.11958 13.0 9411270 24.30 1013 24.26 WP OAK OAKLAND CA 37.71869 -122.22166 9.4 9414750 5.56 437 5.55 WP PAO PALO ALTO CA 37.46111 -122.11506 6.8 9414750 23.68 1005252375 21.26 WP SAN SAN DIEGO CA 32.73356 -117.18967 16.8 9410170 1.72 158 1.99 WP SBA SANTA BARBARA CA 34.42619 -119.84149 13.4 9411270 23.34 2126 8.70 WP SFO SAN FRANCISCO INTERNATIONAL AIRPORT CA 37.61881 -122.37542 13.1 9414750 11.39 1005252375 10.67 NE BDR BRIDGEPORT CT 41.16347 -73.12617 8.5 8467150 2.98 1068 2.83 NE GON GROTON CT 41.33006 -72.04514 9.1 8461490 2.77 429 3.11 SO APF NAPLES FL 26.15244 -81.77564 8.2 8725110 2.49 1107 2.63 Exhibit E-1. SLR template airport data. Source: https://tidesandcurrents.noaa.gov/publications/NOAA_Technical_Report_NOS_COOPS_067a.pdf. STATION NAME STATE NLAT NLONG LOC SCALE SHAPE 8410140 EASTPORT MAINE 44.903 -66.985 1.044 0.094 0.000 8413320 BAR HARBOR MAINE 44.392 -68.205 0.754 0.092 -0.008 8418150 PORTLAND MAINE 43.657 -70.247 0.714 0.103 0.038 8419870 SEAVEY ISLAND MAINE 43.080 -70.742 0.637 0.120 -0.035 8443970 BOSTON MASSACHUSETTS 42.355 -71.052 0.764 0.133 0.019 8447930 WOODS HOLE MASSACHUSETTS 41.523 -70.672 0.565 0.161 0.240 8449130 NANTUCKET ISLAND MASSACHUSETTS 41.285 -70.097 0.504 0.125 0.052 8452660 NEWPORT RHODE ISLAND 41.505 -71.327 0.582 0.131 0.290 8454000 PROVIDENCE RHODE ISLAND 41.807 -71.402 0.720 0.190 0.323 8461490 NEW LONDON CONNECTICUT 41.355 -72.087 0.618 0.188 0.158 Exhibit E-2. Historical EWL parameters. GMSL Rise Scenario 2.6 4.5 8.5 0.3 Low 94.00% 98.00% 100.00% 0.5 Intermediate-Low 49.00% 73.00% 96.00% 1.0 Intermediate 2.00% 3.00% 17.00% 1.5 Intermediate-High 0.40% 0.50% 1.30% 2.0 High 0.10% 0.10% 0.30% 2.5 Extreme 0.05% 0.05% 0.10% Source: https://tidesandcurrents.noaa.gov/publications/techrpt83_ Global_and_Regional_SLR_Scenarios_for_the_US_final.pdf. Exhibit E-3. GMSL scenario probabilities.

Microsoft Excel Templates 91 SITE PSMSL_ID NLAT NLONG SCENARIO RSL2000 RSL2010 RSL2020 RSL2030 RSL2040 RSL2050 RSL2060 SAN FRANCISCO 10 37.81 -122.47 0.3 - MED 0 3 6 10 13 17 21 SAN FRANCISCO 10 37.81 -122.47 0.5 - MED 0 3 8 12 17 22 28 SAN FRANCISCO 10 37.81 -122.47 1.0 - MED 0 5 10 17 25 36 47 SAN FRANCISCO 10 37.81 -122.47 1.5 - MED 0 7 13 22 34 51 69 SAN FRANCISCO 10 37.81 -122.47 2.0 - MED 0 8 16 28 46 70 97 SAN FRANCISCO 10 37.81 -122.47 2.5 - MED 0 8 18 32 54 83 118 NEW YORK 12 40.70 -74.01 0.3 - MED 0 5 11 15 20 25 31 NEW YORK 12 40.70 -74.01 0.5 - MED 0 6 13 19 25 31 39 NEW YORK 12 40.70 -74.01 1.0 - MED 0 9 19 29 39 51 65 NEW YORK 12 40.70 -74.01 1.5 - MED 0 12 25 39 53 71 92 NEW YORK 12 40.70 -74.01 2.0 - MED 0 14 31 48 67 92 124 NEW YORK 12 40.70 -74.01 2.5 - MED 0 14 29 50 76 105 144 Exhibit E-4. Projected RSL data. Finally, there is a data table of the localized RSL rise projections. A sample is shown in Exhibit E-4; these projections are given in centimeters. The decadal projections go out to 2100 (and beyond) even though the exhibit only shows the results to 2060. Note that there are six differ- ent projections for each site, corresponding to the GMSL rise scenarios shown in Exhibit E-3. User Selections On the User Selections sheet, the user selects the airport of interest, an emissions scenario, the costs of a possible mitigation project, and the dollar damages incurred by different-sized flood- ing events with and without the project. An example of the top of the sheet is shown in Exhibit E-5. After selecting a specific airport in the indicated cell, the sheet will show the published elevation of the airport and its lowest runway relative to MSL and MHHW. It is important to emphasize that all of the results shown in the file are relative to MHHW (see the discussion in Appendix D for more information). Information about the closest EWL and RSL stations is presented, and the user can click to see a map of their locations relative to the airport. Implied water levels of 1-, 10-, 50-, and 100-year events based on historical data are also shown. It is important to note that the EWL and RSL stations used as data points do not necessarily coincide exactly with the airport locations. Thus, users should review the distances and inspect the available map to assess whether the data collected from the listed stations can reasonably be used to project extreme water events at the airport being analyzed. The user also selects the emissions scenario to be used. As seen in Exhibit E-5, explanatory text is provided to help the user understand the possible scenario selections. For presentation purposes here, New Orleans (MSY) airport is selected for analysis. The second part of the User Selections sheet, shown in Exhibit E-6, allows the user to enter assumptions about the mitigation project, costs, and potential damage impacts from flooding. The user can specify the time horizon for the analysis, discount rate, and construction, mainte- nance, and rehab costs for the mitigation project. Damage costs are also entered here; by design, these are specified generically as dollars per event for different-sized flooding events. Note that it is quite possible that the proposed project may offer only partial protection against flooding; this can be analyzed by entering non-zero damage amounts in the “With Project” column. Again, explanatory text is provided to help the user with the various options.

Exhibit E-5. RSL user selections 1. State_Locid LA_MSY Name LOUIS ARMSTRONG NEW ORLEANS INTERNATIONAL Relative to MSL Relative to MHHW Airport Elevation (ft) 3.7 3.2 Lowest Runway Elevation (ft) -2.4 -2.9 Historical extreme water levels (EWL) based on: EWL Curve Parameters Implied Water Levels above MHHW baseline based on historical data (ft) EWL_Station GRAND ISLE Location 0.433 100-yr event 6.33 EWL_Distance (miles) 53.58 Scale 0.168 50-yr event 5.16 Shape 0.261 10-yr event 3.11 1.01-yr event 0.73 Projected relative sea levels (RSL) based on: RSL_Station grid_29.5_269.5 RSL_Distance (miles) 37.00 RCP_Scenario 8.5 RCP stands for Representative Concentration Pathway: There are 4 different RCP scenarios, each with different assumptions about the future path of global emissions reductions. Sea level rise projections for 3 of the 4 scenarios are available for this ACRP project analysis of extreme water levels. RCP 2.6 represents a "low emissions" scenario where significant global efforts are made to curb emissions. RCP 8.5 represents a "high emissions" scenario and assumes little or no successful global efforts to mitigate greenhouse gases. RCP 4.5 represents an intermediate case. User should select or enter values in blue shaded boxes only Click here for map

Microsoft Excel Templates 93 Results Once all of the selections have been made, the user can navigate to the Results sheet and click the “Recalculate All Results” button to generate predictions from 5,000 Monte Carlo simulations.27 Or if interested just in the water event probabilities (exclusive of the NPV cal- culations from the mitigation project), one can click the button at the top of the sheet to recalculate just those results immediately below. Shown in Exhibit E-7, the results reflect the uncertainty inherent in the projections of future flooding events. The table at the top displays the simulated probability of EWL events of different heights; the first column of probabilities reflects historical data only, while the remaining columns show how those probabilities change over time due to sea level rise. Just below, the user may also enter a desired height level to assess the cumulative likelihood of an extreme water event at or above that height over the indicated years. This information may be particularly useful to airports when they assess the risk to specific pieces of infrastructure; this is discussed in more detail in the next section. The bottom part of the sheet shows the average of the NPV and benefit–cost ratio from the 5,000 Monte Carlo simulations followed by charts displaying the VaR results. The chart on Analysis_Start_Yr 2020 Analysis_End_Yr 2099 Discount_Rate 3.0% Mitigation_Project_Type Simplified Project_Start_Yr 2020 Mitigation_Start_Yr 2021 Simplified Mitigation Project Costs Construction_Cost $5,000,000 Annual_Maint_Cost $500,000 Rehab_Interval_Yrs 25 Rehab_Cost $2,000,000 Flooding Event Damage Costs EWL (ft) Without Project With Project 0-1 $0 $0 1-2 $0 $0 2-3 $100,000 $0 3-4 $500,000 $0 4-5 $1,000,000 $0 5-6 $1,000,000 $200,000 6-7 $5,000,000 $1,000,000 7-8 $10,000,000 $2,000,000 8-9 $10,000,000 $2,000,000 9+ $10,000,000 $2,000,000 Note: For any given EWL, costs should reflect impacts net of freeboard (if any). To enter user-specified costs year-by-year instead: Discounted benefit-cost calculations reflect time horizon between Analysis_Start_Yr and Analysis_End_Yr Maximum time horizon is 2020 - 2099. Traditional FAA guidance (based on Office of Management and Budget directives) suggests using a 7% real discount rate for standard investment projects. But see discussion in Handbook for possible use of a lower rate that may be appropriate for climate resilience projects with a long time horizon. Simplified Mitigation Project assumes upfront construction costs, then constant annual maintenance or rehab costs according to schedule below. Project_Start_Yr signifies first year of construction; this can be after Analysis_Start_Yr if considering a delayed project start. Mitigation_Start_Yr signifies first year of damage mitigation. If using Simplified Mitigation Project: -- Construction_Cost is spread evenly between Project_Start_Yr and Mitigation_Start_Yr. -- Final Rehab_Cost before end of analysis time horizon is prorated. Click Here Exhibit E-6. RSL user selections 2.

94 Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports the left shows the estimated net impacts with and without the mitigation project.28 In the example shown previously, one can see that without undertaking the proposed mitigation project, there is almost a 20% chance that the airport could be exposed to damages in excess of $40 million. With the project, that chance is reduced to under 1%. The chart on the right shows just the net difference in NPV between the scenario and baseline cases, in this example indicating a range of anywhere between about +$60 million to -$10 million for the net impacts of investing in the proposed mitigation project. Overall, the chances of the project paying off (i.e., where the net impacts of undertaking the project are greater than the net impacts of not doing so, or equivalently, where the benefit–cost ratio of the project is greater than 1) is close to 75%. (Although not shown in the exhibit, the template Water Level Rise above MHHW (ft) Historical 2025 2035 2045 2055 2065 2075 2085 2095 0-1 9.66% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 1-2 58.18% 30.80% 4.06% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 2-3 20.62% 46.74% 56.22% 29.88% 4.68% 0.22% 0.00% 0.00% 0.00% 3-4 6.78% 14.38% 26.12% 45.72% 50.08% 25.88% 6.82% 0.84% 0.04% 4-5 2.76% 4.36% 8.00% 16.20% 29.96% 44.86% 41.32% 22.38% 8.52% 5-6 0.80% 1.72% 3.04% 4.64% 9.54% 18.36% 31.78% 38.96% 31.08% 6-7 0.44% 1.00% 1.18% 1.66% 3.10% 6.62% 12.24% 21.64% 31.34% 7-8 0.24% 0.36% 0.70% 0.74% 1.42% 1.92% 4.22% 9.80% 16.00% 8-9 0.18% 0.22% 0.20% 0.32% 0.66% 0.90% 2.16% 3.40% 7.60% 9+ 0.34% 0.42% 0.48% 0.84% 0.56% 1.24% 1.46% 2.98% 5.42% TOTAL 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% Median (ft) 1.62 2.31 2.80 3.32 3.91 4.46 5.05 5.66 6.27 100-Yr Event (ft) 6.41 6.99 7.38 8.40 8.43 9.25 9.66 10.41 11.37 Height Above MHHW (ft) 2025 2035 2045 2055 2065 2075 2085 2095 8.0 3.35% 9.27% 17.00% 26.33% 37.58% 52.57% 71.02% 89.66% Mean Std Deviation Avg NPV of Project $5,600,518 $8,566,656 Avg B/C Ratio 1.27 0.42 MSY Extreme Water Level Event Probabilities from 5,000 Simulations (RCP 8.5) Cumulative Probability of Inundation above MHHW (from 2020) Recalculate All Results -$120 -$100 -$80 -$60 -$40 -$20 $0 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% N PV ($ m ill io ns ) Net Impacts: Baseline vs. Scenario Baseline (Without Project) Scenario (With Project) -$20 -$10 $0 $10 $20 $30 $40 $50 $60 $70 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Ch an ge in N PV ($ m ill io ns ) NPV Difference (Scenario - Baseline) Recalculate Water Event Probabilities Exhibit E-7. Sea level rise results.

Microsoft Excel Templates 95 also shows a table of results for the right-hand chart, indicating the actual NPV difference at 5% increments.) Overall, the template should be useful to users by allowing them to quickly assess different possible mitigation strategies to combat sea level rise. As one considers the future, the probability of higher extreme weather events increases, which exposes more and more of the airport’s infra- structure. The analyst may consider the cost to repair infrastructure that is inundated (including costs of services interrupted) versus the cost to mitigate. As higher extreme water events become more likely, certain potential mitigation projects may become economically attractive. Further Use of the Climate Projections to Assess Current Infrastructure Independent of the mitigation project VaR results, airports may be interested in assessing what the climate projections imply for their existing infrastructure that may have been designed to specific standards. For example, many airports utilize design standards based on adding 1 to 3 ft of freeboard to the 100-year (1%) storm projection. The last row in the top table of Exhibit E-7 shows how projected 100-year stormwater levels are expected to change over time (relative to MHHW). These figures could be compared to elevation data for the lowest critical level of each piece of infrastructure at the airport. Related to this, the section in the exhibit labelled “Cumulative Probability of Inundation” can be used to assess the likelihood that specific pieces of infrastructure would remain safe. For example, if a particular asset has been designed to withstand inundations up to, say, 8 ft above MHHW, then by entering that value into the shaded cell on the left labelled “Height Above MHHW,” the results will show the cumulative probability that an event at or above 8 ft would occur by the indicated years based on the 5,000 simulations. To take this a step further, an airport could develop a complete inventory of relevant assets and their corresponding critical elevation levels and then assess the likelihood of inundation by entering those levels into the shaded cell on the left of the sheet. The resulting table might look something like that shown in Exhibit E-8.29 This provides a useful summary of the increasing vulnerability of infrastructure to extreme water events over time if no intervening mitigations are undertaken. One could also assess the reduction in inundation probabilities of a single asset at different levels of additional eleva- tion. Again, this could be accomplished simply by entering the relevant elevation levels into the shaded cell in the template. For example, focusing on the TSA building, one could create a table like that shown in Exhibit E-9. Presenting the projections in this format could make it easier to discuss options with senior management. Cumulative Probability of Inundation (from 2020) Infrastructure Critical Elevation (Relative to MHHW) End of Useful Life 2025 2035 2045 2055 2065 2075 TSA building 8.0 2045 3.4% 9.3% 17.0% Fire station 10.0 2050 1.6% 3.8% 7.4% 11.3% Utility tunnel 12.0 2055 0.8% 1.9% 3.8% 5.8% Terminal 15.0 2070 0.4% 1.0% 1.8% 2.5% 3.4% 4.6% Exhibit E-8. Vulnerability of infrastructure over time.

96 Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports Template for Maximum Daily Temperatures The look and feel of the Excel template for potential climate change events reflecting high temperatures is similar to that for sea level rise. But unlike the latter, there is no centralized collection of high-temperature projections for multiple airports that can be gathered into a single file. Here it is up to the user to retrieve temperature projections for a specific emissions scenario and for the specific airport of interest. In addition, this template focuses specifically on aircraft weight restrictions that may be incurred due to high temperatures and how these may be mitigated with a specific runway extension project.30 This is very different from the SLR template where the mitigation project is generic and defined only as it relates to varying extreme water height projections. Also in contrast to the sea level rise projections that represent six specific GMSL scenarios at 10-year intervals, the data for high temperatures at any given location come in the form of daily projections for more than 80 years (out to 2099) across many different climate models. Adding to the data burden is the fact that best practices in the climate science field suggest that data be collected for at least four geographically adjacent grid points near the location of interest. Thus, the task of retrieving daily high-temperature data and summarizing them in a useful way for analysis is non-trivial and may well require the use of outside professional help.31 The process of determining the weight restriction that might occur for a specific flight depends on many variables, including aircraft type, takeoff weight, runway length, elevation, and temperature. Normally, one would have to do many manual calculations by reading off aircraft payload/range and takeoff weight/runway charts published by manufacturers in order to estimate the potential weight restriction for any particular flight. (The FAA has published an advisory circular outlining how to determine minimum required runway takeoff length.32) However, the template implements the results of a published analysis that allows for direct lookups of estimated weight restrictions that would apply for a number of popular aircraft types used for long-haul flying and for a given elevation, runway length, and temperature (Coffel et al. 2017).33 This allows the Excel template to be a useful tool where the incidence and impact of weight restrictions on specific flights can be assessed automatically once the user enters the high-temperature projections along with information on elevation and runway length. The template file is composed of five separate Excel sheets: • Overview: This sheet provides a Quick Start section plus a high-level overview of how the model embedded in the template works. • Weather Data: This sheet contains a summarized version of the high-temperature data that the user must retrieve, along with a list of weather models used and model weights. • Aircraft Data: This sheet contains the weight restriction lookup information mentioned previously for specific aircraft types. Cumulative Probability of Inundation Critical Elevation 2025 2035 2045 8.0 (Current) 3.4% 9.3% 17.0% 9.0 2.3% 6.1% 11.3% 10.0 1.6% 3.8% 7.4% 11.0 1.1% 2.7% 5.2% 12.0 0.8% 1.9% 3.8% Exhibit E-9. Effect of alternative mitigations on vulnerability of the TSA building.

Microsoft Excel Templates 97 • User Selections: This sheet is where the user inputs the airport elevation and runway length, details about which specific routes and aircraft types may be candidates for weight restrictions if temperatures get high enough, the costs that would be incurred for the proposed runway extension, and the delay costs that would be incurred for passengers that must be removed from a flight if it is weight restricted. • Results: This sheet presents the results of 5,000 Monte Carlo simulations based on the user selections. Weather Data The Excel template assumes that the relevant data for daily high temperatures at any given airport for a specific emissions scenario can be retrieved and summarized to look like that shown in Exhibit E-10. (Instructions are provided in the template file.) Again, it is up to the user to gather and paste in this data. The data shown here is for PHX airport. Each row represents a unique model/year/grid-point combination, and the “H” columns represent counts of annual days at or above the indicated Fahrenheit temperature but below the next column’s temperature. For example, the row for the model named ACCESS1-0/Year 2020/Grid_ID 4 projects that there will be 16 days with daily high temperatures between 100° and 102°; 14 between 102° and 104°, and so forth. Summariz- ing the data in these 2-degree increments between 100° and 128° drastically cuts down on the number of total data points but still retains a full range of high temperatures that should be relevant for purposes of estimating weight restrictions at airports across the United States. The Weather Data sheet also contains a list of the 32 climate models and their analysis weights.34 Higher weights are given to those models that have historically provided better predictions, and MODEL YEAR GRID_ID H100 H102 H104 H106 H108 H110 H112 H114 H116 H118 H120 H122 H124 H126 H128 ACCESS1-0 2020 1 15 16 13 22 29 15 18 1 0 0 0 0 0 0 0 ACCESS1-0 2020 2 18 13 13 23 27 22 13 4 0 0 0 0 0 0 0 ACCESS1-0 2020 3 16 16 12 22 25 19 19 1 0 0 0 0 0 0 0 ACCESS1-0 2020 4 16 14 16 21 28 18 15 2 0 0 0 0 0 0 0 ACCESS1-0 2021 1 24 21 15 21 19 11 7 3 2 0 0 0 0 0 0 ACCESS1-0 2021 2 30 18 18 18 20 12 9 2 2 0 0 0 0 0 0 ACCESS1-0 2021 3 25 16 15 22 21 11 8 3 2 0 0 0 0 0 0 ACCESS1-0 2021 4 32 20 16 20 18 11 8 1 2 0 0 0 0 0 0 … … … … … … … … … … … … … … … … … … ACCESS1-0 2090 1 7 13 13 7 18 25 28 31 15 5 1 1 1 0 0 ACCESS1-0 2090 2 8 9 14 11 14 24 32 32 13 6 2 2 0 0 0 ACCESS1-0 2090 3 8 11 10 11 17 22 30 32 15 6 1 1 1 0 0 ACCESS1-0 2090 4 11 8 14 8 20 26 32 32 8 6 1 2 0 0 0 ACCESS1-3 2020 1 19 33 32 18 7 10 6 0 0 0 0 0 0 0 0 ACCESS1-3 2020 2 22 32 39 13 10 9 6 1 0 0 0 0 0 0 0 ACCESS1-3 2020 3 20 34 34 15 10 11 5 0 0 0 0 0 0 0 0 ACCESS1-3 2020 4 21 37 37 12 9 11 3 0 0 0 0 0 0 0 0 ACCESS1-3 2021 1 9 22 27 26 19 14 5 2 0 0 0 0 0 0 0 ACCESS1-3 2021 2 14 16 33 22 19 18 5 2 0 0 0 0 0 0 0 ACCESS1-3 2021 3 9 17 29 27 18 14 8 2 0 0 0 0 0 0 0 ACCESS1-3 2021 4 15 20 28 25 18 17 3 2 0 0 0 0 0 0 0 … … … … … … … … … … … … … … … … … … ACCESS1-3 2090 1 12 15 20 20 37 33 19 15 5 3 1 0 0 0 0 ACCESS1-3 2090 2 12 14 16 23 35 32 30 13 5 1 2 0 0 0 0 ACCESS1-3 2090 3 11 15 18 24 34 35 22 12 6 3 1 0 0 0 0 ACCESS1-3 2090 4 13 16 16 25 34 40 18 12 5 1 2 0 0 0 0 … … … … … … … … … … … … … … … … … … Exhibit E-10. High-temperature data.

98 Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports the sampling method directly accounts for the weights by adjusting the probability of being sampled in each year of the Monte Carlo simulations.35 Aircraft Data As mentioned previously, the Aircraft Data sheet contains a lookup table where the weight restriction for a given aircraft type can be estimated given the airport elevation, runway length, and temperature. Basic assumptions for aircraft takeoff weights and fuel consumption are also contained in this sheet. An important practical inference from the weight restriction data table is that net improve- ment in allowed takeoff weight from a longer runway does not vary much based on tempera- ture. In other words, for a given aircraft type, if moving from a 7,000-ft runway to a 9,000-ft runway increases the allowed takeoff weight by, say, 15,000 lbs when the temperature is 100°F, then the improvement at 110°F will also be around 15,000 lbs (or until the structural maxi- mum takeoff weight of the aircraft is reached). For example, Exhibit E-11 shows the change in required weight restriction for the Boeing 737-800. This implies that the uncertainty of future temperature increases will not likely have large effects on the BCA of a proposed runway extension—the net impact of a 2,000-ft extension will be approximately the same regardless of the future path of high temperatures (assuming the restriction is binding to begin with). A related implication from the data is that the increase in allowed takeoff weight as a func- tion of temperature is very gradual. From Exhibit E-11, one can see that the weight restriction for the 7,000-ft runway increases gradually and steadily from 24,000 lbs at 100°F to 35,000 lbs at 116°F. This implies that, whatever the runway length is, the weight impacts (and corresponding costs due to delay for passengers who cannot be accommodated) will change only modestly, leading to relatively flat NPV curves that make up the VaR analysis. This will be the case under both the baseline and the scenario cases. User Selections On the User Selections sheet, the user first inputs the airport elevation, baseline runway length, and runway length after the extension project is completed. This is followed by a section where the user may enter specific routes and aircraft types in order to have the model estimate the implied weight restrictions at different temperatures. Exhibit E-12 shows the part of the User Selections sheet where this information is entered. Again, explanatory text is provided to help the user understand the possible selections. The esti- mated required weight restrictions at different temperature levels are shown to the right of where the specific flight information is entered.36 In this example, information on three new long-haul routes not currently served from PHX is considered. Temp (°C) 38 39 40 41 42 43 44 45 46 47 Runway Length Temp (°F) 100.4 102.2 104.0 105.8 107.6 109.4 111.2 113.0 114.8 116.6 7000 24 26 27 28 30 31 32 33 35 35 9000 11 12 14 15 17 18 19 21 22 23 Change 13 14 13 13 13 13 13 12 13 12 Weight Restriction (000 lb) from Maximum Takeoff Weight for 737-800 Aircraft at 2000-Ft Elevation Exhibit E-11. Impact of runway length on weight restrictions.

Exhibit E-12. High-temperature user selections 1. When finished making selections and/or changing values, press the Refresh Current Sheet button. Baseline represents current status; Scenario represents runway extension to mitigate weight restrictions. Elevation 1135 ft Valid elevation range is 0-4000 ft; latter is the maximum elevation for which model can automatically estimate weight restrictions. Rwylength_Baseline 11500 ft Valid runway length range is 4000-16000 ft. Rwylength_Scenario 14500 ft Enter routes and distances (measured in nautical miles) below to analyze weight restrictions. Select aircraft from drop-down list; for aircraft types not listed, go to next section, where you must enter weight restrictions manually. Weekly Departures should represent # of weekly flights scheduled during peak temperature times in Analysis Start Year. The estimates of required weight restrictions shown here are based on standard assumptions and averages that may not be appropriate for a specific route flown by a particular carrier with their own equipment specifications. The reported weight restrictions are derived by comparing estimated allowed takeoff weight (based on elevation, runway length and temperature) vs. required takeoff weight for the route and passenger counts entered. Inaccurate results may occur if you specify routes that are beyond the practical range of the aircraft selected, or if you enter passenger counts beyond the seatsize capacity. Guidelines are shown below: Max Range (nm) Typical U.S. Maximum Seatsize A320 3300 160 186 B737-800 3000 158 189 787-8 7000 235 300 777-300 7000 317 500 Route Distance (nm) Eqpt Passenger s per Weekly Departure 100-101 102-103 104-105 106-107 108-109 110-111 112-113 114-115 116-117 118-119 Baseline 0.0 0.0 0.0 0.0 0.0 0.0 0.4 1.9 3.4 4.9 Scenario 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Baseline 3.5 7.5 15.0 19.0 23.0 26.6 30.6 33.6 34.9 36.2 Scenario 0.0 0.0 0.0 0.0 2.6 7.2 11.2 14.2 18.2 21.7 Baseline 29.6 35.1 46.1 51.6 57.1 63.1 68.1 74.1 79.7 85.7 Scenario 0.0 5.1 15.7 21.7 27.7 32.7 38.7 44.7 50.1 56.1 4570 4999 130 195 260 7 7 7 Weight Restriction (000 lb) per Flight at Indicated Temperatures (°F) PHX-LHR User should select or enter values in blue shaded boxes only 787-8 27281 2 3 PHX-BOG 737-800 777-300PHX-NRT Refresh Current Sheet

100 Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports The second part of the User Selections sheet is shown in Exhibit E-13; it is similar to the corresponding section of the RSL template and allows the user to enter assumptions about the mitigation project, costs, and potential damage impacts from weight restrictions (measured as passenger delays). The user can specify the time horizon for the analysis, discount rate, and construction, maintenance, and rehab costs for the runway extension project. In addition, the user may select whether or not to use model weights when sampling, and whether to sample from the same model across all years of a given simulation or from a differ- ent model each year. (Remember that a set of predictions across all years of interest represents a single simulation, and there are 5,000 simulations.) Again, explanatory text is provided to help the user with the various options. It is important to note that the template considers only a narrow definition of negative impacts due to the weight restrictions—namely, delay costs to passengers; no net impacts on airlines or the airport itself are considered. But a broader analysis that accounts for impacts beyond simple passenger delay costs could be undertaken using the same overall format and approach shown here. Results Once all of the selections have been made, the user can navigate to the Results sheet and click the Recalculate button to generate predictions from 5,000 Monte Carlo simulations. Sample results are shown in Exhibit E-14. The charts at the top display the uncertainty in high temperatures across the available climate models. Not surprisingly, the uncertainty grows in later years. The middle portion of the sheet shows the average of the net NPV and benefit–cost ratios from the Monte Carlo simulations, while the charts below display the VaR results. The chart on the left shows the net impacts with and without the mitigation project, while the one on the right shows the difference in NPV between the scenario and baseline cases. The sample results shown here are consistent with the previous discussion. While high temperatures may certainly have a large impact on the passengers affected by weight restrictions (the net damages shown on the left chart, which reflect delay costs to passengers, reach as high as $60 million under the baseline case), the uncertainty of when those high temperatures will occur has only a modest effect on the net impact of a given runway extension. The chart shows that the runway extension never pays off under the current assumptions, and the net impacts do not vary much across the simulations. The chart on the right shows that NPV difference between the baseline and scenario cases ranges between about $15 million and $20 million (consistent with the very small standard deviation in the benefit–cost ratio). If this sort of result holds up in an actual airport analysis, it suggests that decision makers could focus more on whether weight restrictions are likely to be an issue in the first place and the appropriate size and cost of a runway extension, and less on how the BCA results may be affected by climate change uncertainty.

Exhibit E-13. High-temperature user selections 2. Analysis_Start_Yr 2020 Discounted benefit-cost calculations reflect time horizon between Analysis_Start_Yr and Analysis_End_Yr. Analysis_End_Yr 2060 Maximum time horizon is 2020 - 2099, but actual limits depend on the years covered by the Weather Data. User may wish to consider a time horizon matching the expected/remaining life of current or new assets. Discount_Rate 3.0% Traditional FAA guidance (based on Office of Management and Budget directives) suggests using a 7% real discount rate for standard investment projects. But see discussion in Handbook for possible use of a lower rate that may be appropriate for climate resilience projects with a long time horizon. Mitigation_Project_Type Simplified Simplified Mitigation Project assumes upfront construction costs, then constant annual maintenance or rehab costs according to schedule below. Project_Start_Yr 2020 Mitigation_Start_Yr 2021 Project_Start_Yr signifies first year of construction; this can be after Analysis_Start_Yr if considering a delayed project start. Mitigation_Start_Yr signifies first year of damage mitigation. Simplified Mitigation Project Costs Construction_Cost $40,000,000 If using Simplified Mitigation Project: Annual_Maint_Cost $1,000,000 -- Construction_Cost is spread evenly between Project_Start_Yr and Mitigation_Start_Yr. Rehab_Interval_Yrs 25 -- Final Rehab_Cost before end of analysis time horizon is prorated. Rehab_Cost $10,000,000 Damage Parameters Avg_PaxPayload 220 Avg payload per passenger in lbs Avg_PaxDelay 2.0 Avg hours of delay for passengers on impacted flights Hourly_DelayCost $44.30 Passenger delay cost per hr Sampling_Method One Model per Year One Model per Simulation -- same model is used for all years of a single simulation (rem 5,000 total simulations) One Model per Year -- different model is used for each year of a single simulation (reduces influence of outlier models) Use_Model_Weights Yes Select Yes to use model weights listed in Weather Data sheet; No to use equal weighting across whatever models are in Weather Data. To enter user-specified costs year-by-year instead: Click Here

102 Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports After making sure you have refreshed other sheets, click Recalculate button for results: Mean Std Deviation Avg NPV of Project -$18,458,050 $662,973 Avg B/C Ratio 0.64 0.01 Recalculate -$90 -$80 -$70 -$60 -$50 -$40 -$30 -$20 -$10 $0 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% N PV ($ m ill io ns ) Net Impacts: Baseline vs. Scenario Baseline (Without Project) Scenario (With Project) 0 20 40 60 80 100 120 140 160 180 200 2020s 2030s 2040s 2050s 2060s 2070s 2080s 2090s Av g An nu al D ay s ab ov e 11 0° F Range of Avg Annual Days above 110°F across Models by Decade Max Min Median 0 50 100 150 200 250 2020s 2030s 2040s 2050s 2060s 2070s 2080s 2090s Av g An nu al D ay s ab ov e 10 0° F Range of Avg Annual Days above 100°F across Models by Decade Max Min Median -$25 -$20 -$15 -$10 -$5 $0 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Ch an ge in N PV ($ m ill io ns ) NPV Difference (Scenario - Baseline) Exhibit E-14. High-temperature results.

Next: Appendix F - Climate Risk and Mitigation Numerical Examples »
Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports Get This Book
×
MyNAP members save 10% online.
Login or Register to save!
Download Free PDF

TRB’s Airport Cooperative Research Program (ACRP) Research Report 199: Climate Resilience and Benefit–Cost Analysis: A Handbook for Airports provides information on how to apply benefit–cost analysis tools and techniques to improve decision making affecting resilience of airport infrastructure projects in response to potential long-term impacts of climate change and extreme weather events.

The handbook is designed to improve the process by which infrastructure investment strategies are evaluated, with an emphasis on ensuring climate-related resiliency.

Procedures for presenting assumptions and results transparently and for implementing the process are also included so that industry users and decision makers can understand and communicate the outcome of the analytical process.

Based on data availability, the analytical methods included in the handbook focus on two specific areas of climate change likely to affect airports (although these methods can, in principle, be used more widely): (1) the potential for extreme flooding events resulting from storm surge and sea level rise near coastal airports, and (2) the potential for rising temperatures that require weight restrictions on aircraft takeoffs (or possibly full flight delays) at airports with shorter runways in warm climates or at high elevations.

The results available from application of the suggested methodologies do not necessarily make the decision of whether to invest in a mitigation project to combat climate change any easier but, rather, provide a full range of potential outcomes and possibilities for airport planners and managers to consider. Using this methodology, airport decision makers can then determine how much risk from uncertain climate change and extreme weather events they are willing or able to accommodate. Implementation of the methods presented in the handbook can be used to obtain essential quantifiable estimates of those risks, which is of particular value to airport financial professionals.

The handbook is accompanied by a set of Microsoft Excel models to support the decision-making process (one for extreme water rise causing potential flooding events, and the other for high temperatures that may affect weight restrictions on aircraft takeoffs), a video tutorial, a report summary document, and an executive briefing to help decision makers understand the process.

  1. ×

    Welcome to OpenBook!

    You're looking at OpenBook, NAP.edu's online reading room since 1999. Based on feedback from you, our users, we've made some improvements that make it easier than ever to read thousands of publications on our website.

    Do you want to take a quick tour of the OpenBook's features?

    No Thanks Take a Tour »
  2. ×

    Show this book's table of contents, where you can jump to any chapter by name.

    « Back Next »
  3. ×

    ...or use these buttons to go back to the previous chapter or skip to the next one.

    « Back Next »
  4. ×

    Jump up to the previous page or down to the next one. Also, you can type in a page number and press Enter to go directly to that page in the book.

    « Back Next »
  5. ×

    To search the entire text of this book, type in your search term here and press Enter.

    « Back Next »
  6. ×

    Share a link to this book page on your preferred social network or via email.

    « Back Next »
  7. ×

    View our suggested citation for this chapter.

    « Back Next »
  8. ×

    Ready to take your reading offline? Click here to buy this book in print or download it as a free PDF, if available.

    « Back Next »
Stay Connected!