-- We Will Do This In Class --

Each of you will do your own work, your own location, with the support of a lab partner.

Each of you submits your own two files: one CBA model (excel spreadsheet) and one document with the responses to the questions.

Part 1: Calculating Damages from Flooding and Sea Level Rise

For this lab activity, you will calculate damages from flooding and sea level rise for a coastal community of your choosing.

Step 1. Explore the Surging Seas Risk Finder data for your chosen city of interest

Navigate to the Surging Seas Risk Finder (you were introduced to this tool in the Pre-lab activity).

Search for the city you are interested in investigating. Take a few minutes to explore the website.

As an example, the figure here shows the population at risk in Newport, RI at a water level of 5 ft. (918 people)

Surging Seas Risk Finder example output for Newport, RI.

To help guide your exploration of some of the tools and settings, find the answers to these questions for your city of interest:

How many people in the population of your city are at risk given a water level of 6 ft?
How many homes are at risk given 8 ft of flooding? (Hint: click on the “Buildings” tab under “What Is at Risk?”)

Now find some data that you will need to save for our calculations:

Write down how many homes are at risk given 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, & 10 ft of flooding. You will use these values later!

Step 2. Estimate home values in flood prone areas.

Navigate to this GIS map of median home values and income for 2018.

Search for your location in the upper right search bar. Use the + button in the upper left to zoom in.

Click on a block to see the median home price in that block for 2018.

Here is my Newport, RI example:

ArcGIS map of median home values in the Newport, RI area.

Look at the "What is at Risk?" map in the Surging Seas Risk Finder tool (make sure buildings --> homes is selected). I had to play with some of the settings to find the best map to show my region. Display by zip code seemed to work best for me, but there are other ways to organize the maps that might be better for your region. (See how to explore those options in the tutorial videos in the Pre-lab).

Here is my Newport, RI example:

Map of total homes below 5ft in the Newport, RI area.

Compare the two maps (the GIS median home value map & the Surging Seas flood risk map) for your city of interest.

You might have to click on several median home price blocks to get an idea for the range of values across the entire flood risk area of your city map. (Here again we deal with real data - mapped on different spatial scales. Try to estimate averages and make the best use of the data we have.)

After considering the two maps, decide on a representative median home price to use in the analysis for estimating flood damages.

What is your estimate for the median home value in the flood prone areas of your city of interest? Save this value - you will use it later!

Step 3. Calculate the marginal damage cost of flooding

First, download this template excel spreadsheet.

Click on the "Part 1. MD Tables" tab of the spreadsheet.

To determine how much an urban center should spend on flood protection, planners need to consider the marginal damages done by increasingly higher flood levels. The marginal damage of flooding is the loss in property value that is associated with an additional foot of flooding, or

Marginal Damage = (Change in Total Property Damage )/(Change in Feet of flooding)

In your Excel spreadsheet, you can calculate the marginal damage from the first foot of flooding by subtracting the change in total housing damages from 1 to 0 feet of flooding (numerator) over the change in feet of flooding (denominator). Note that in this case, the changes in flood levels are in 1 foot increments, so the denominator will be “1” for all calculations.

To set up this calculation in the Excel sheet you downloaded, first do the following:

Enter the median home value you estimated into cell D3.
Enter the number of homes at risk for each foot of flooding into the cells B9-B19.

Next, calculate the marginal damage by subtracting each total property damage from the next step up, to determine the change in damage.

Here is an example of how to do that (note that your values for number of homes - entered in B9-B19 will be unique to your chosen city so your numbers will not be the exact same as the ones here):

Excel sheet example calculations.

Step 4: Examine probabilities for flooding under different model scenarios

Next you need to fill in the missing probabilities for flooding at each water level for each sea level rise scenario in Tables 2 and 3. The probability data comes from the Risk Finder website. Go to the bar graph under the “When Are the Risks?” section and select the “Slow rise” scenario. Note: Make sure the graph is set to Multi-year risk of flooding (which is the default option).

If you hover over the bar for the year 2050, the percentage likelihood of flooding pops up. Use these percentages to fill in the “Probability” column in Table 2 in the Excel spreadsheet for the “Slow” scenario (cells C25-C34) for each water level by clicking on the different water levels on the gauge on the left side of the webpage. (Note: be sure to enter the percentage as a probability, i.e. type in 0.45 instead of 45%, 1 instead of 100%.)

Repeat this process by filling in the probabilities for the Medium, Fast, and Extreme sea level rise scenarios in Table 2 (cells E25-E34, G25-G34, I25-I34).

Repeat the process again for Table 3 (cells C40-C49, E40-E49, G40-G49, I40-I49) except now you will hover over the bar for the year 2100. This part goes quickly since a lot of the probabilities are 1.0.

Part 2: Graph marginal damage curves

The marginal damage tables are a helpful way to organize the data and do some basic calculations, but when presenting the data, graphs are a better way to convey a lot of information in one figure. In this section you will make graphs of the expected marginal damages you just calculated in Excel.

Click on the “Part 2: MD graphs” tab at the bottom of the Excel spreadsheet. Tables 4 and 5 show the expected marginal damages from flooding for each SLR scenario for the year 2050 (Table 4) and 2100 (Table 5). Note: The tables have automatically populated with the expected MD values you calculated on the previous spreadsheet.

Excel tip showing how to click on a cell to see what data it is referencing

Now you will create a graph with the four SLR scenarios to show the marginal damages expected at each foot of flooding. A graph of Table 4 for the year 2050 has already been made for you, next you will learn how to create the same graph for Table 5 for the year 2100.

Make a scatter plot (with smooth lines and markers) for the data in Table 5. There are a couple different correct ways to do this. If you are new to working with Excel, follow the steps shown in the slides below, or if you understand how to edit source data you can copy and paste a copy of Figure 1, edit the source data and modify the chart title.

Visual instructions for making the graph

Visual instructions on how to modify chart titles and other options in Excel

Make the title of your graph be: "Figure 2. Expected MD from Flooding by SLR Scenario for 2100". (Note: MD is marginal damage and SLR stands for sea level rise)

Reflection Questions Part 1 & Part 2

What assumptions did we make when calculating marginal damages? (e.g. could you have calculated housing costs a different way?, what else could be damaged besides homes?, if a house floods, does it always loose its entire value?)
How might changes to our assumptions change our calculations? (What would make the calculated damages increase? Decrease?)
What do you think are some of the underlying factors driving the shape of the marginal damage curves in the graphs? Hint: Think about the assumptions you have made when calculating the marginal damages.
1. Why are the expected marginal damages low for small floods?
2. Why are the expected marginal damages low for large floods?

OPTIONAL: Part 3: Extension: Create Your Own Sea Wall

If you were able to work quickly through the steps above and you are interested in learning more... there are a few more steps (incorporating the "Part 3" tab) to get to the point of being able to do a full cost-benefit analysis that a city planner might take to weigh the costs of building a seawall (or other flood mitigation approach) against the marginal costs of flooding. Here is a link to the full set of instructions for the activity which includes the remaining 3rd part (also includes a few more equations for our first two parts, if you're into math, these are nice to supplement your understanding).

Part 4: Cost-Benefit Analysis Sea Wall along the Miami Coast

A sea wall analysis has already been completed for you for the case of Miami Florida.
The results follow.

Here is an example of the final analysis (Part 3 tab) for Miami, Florida.

Excel data for Miami, Florida cost-benefit analysis

Part 3 adds in an analysis (Table 6) of the likelihoods of different rates of sea level rise based on different emissions scenarios modeled after the Intergovernmental Panel on Climate Change (IPCC) report on Representative Concentration Pathways (RCPs).

Then weights the marginal damages (Table 5; like we found above) by the probabilities (Table 6) to find the marginal damage values shown in Table 7.

The marginal cost (far right column in Table 7 & solid yellow line on the graph) is the cost for a 12.3 mile long sea wall the height of each flooding scenario (assuming a taller wall doesn't cost more, which might not be a great assumption - since the taller the wall, the wider the footing would need to be... but... just to get an idea...).

Here is how far 12.3 miles of sea wall would go (blue stick with red/green circles for start-end), near Miami for reference.

Map of Miami with 12.3 mile length shown for reference Map of Miami zoomed in

Property at risk with water level +3 feet

Reflection Questions Part 4 (Required)

For the case of Miami, Florida, is building a sea wall cost effective, that is, would the benefit of building the seawall (measured in avoided housing damages) be greater than the cost?
If it is in the best interest of the region to build a seawall, how tall should it be?
Do you think this result is similar for all cities? It what ways is Miami different than the city you investigated?

Once you have completed the steps and questions in these instructions, save a copy of your edited excel file (export to an xlsx file if you are working in google sheets) & save a file with your answers to the Reflection Questions (Part 1 & Part 2). You will upload those files next to get points for this lab activity.