Introduction to Hazard and Risk: Part 2

Tom Juster
University of South Florida, Tampa
Created: April 14, 2012

NOTE: certain slides were borrowed and modified from "Spreadsheet Warm Up for SSAC Geology of National Park Modules" by McGee, Lindsey, and Vacher

Author Profile
Initial Publication Date: May 16, 2012

Summary

intro_haz_risk_thumbnail
In this Spreadsheets Across the Curriculum activity, students continue to learn Excel while computing the risk posed to Benton County, Oregon, caused by a catastrophic earthquake along the Cascadia subduction zone. This is the second of two modules that compute the risk. The twin objectives are to teach students about the quantification of risk and to reinforce their newly learned Excel skills. Students are first introduced to the concept of the Risk Equation and its three components: probability of hazard, exposure, and vulnerability. They then calculate the monetary and mortality risk to Benton County posed by a large Cascadia earthquake. By deconstructing the risk equation into its components students see how each is computed and how each affects the calculation of risk. The end-of-module task requires the students to reprise the Excel skills they've learned to compute the probabilities of occurrence of a large earthquake along three fault segments.

Share your modifications and improvements to this activity through the Community Contribution Tool »

Learning Goals

Students will:

    • From a table of earthquake occurrences, calculate both the recurrence interval and probability of occurrence in any given year for a great Cascadia earthquake (this is the hazard term of the risk equation).
    • Compute the monetary exposure term of the risk equation by adjusting 1994 numbers to 2010 by accounting for inflation and growth.
    • Compute the mortality and monetary risk based on the risk equation and the results of the previous steps.
    • Use these same Excel tools to investigate the relationship between recurrence interval and probability for earthquakes along three other faults.

    In the process the students will:

    • Discover how recurrence interval and probability are related, and how probability of an earthquake can be computed from historical data.
    • Learn how to use Excel to multiply and add numbers in a table, including the use of relative and absolute cell references which allow formulas to be copied.
    • Better understand the source of the terms in the risk equation, and the effect each has on the computation of risk.
    • Work with actual data to compute a meaningful measure of risk, and compare this value to other sources of risk.
    • Master common Excel skills.

    Context for Use

    This module was designed for use in the Hazards of the Earth's Surface service course at USF. It is the second part of a two-module introduction to Excel united by the common theme of the risk equation. The topic of risk assessment is sufficiently general that this two-module sequence could be used in other classes as well if an introduction to Excel is desired.

    Description and Teaching Materials

    The module is a PowerPoint presentation with embedded spreadsheets. Click on the link below to download a copy of the module.

    Optimal results are achieved with Microsoft Office 2007 or later; the module will function in earlier versions with slight cosmetic compromises. If the embedded spreadsheets are not visible, save the PowerPoint file to disk and open it from there.

    The above PowerPoint presentation is the student version of the module. The embedded spreadsheet consists of a template for students on which students complete their work and answer the end-of-module questions, and then turn in for grading. Since this module is designed as a stand-alone resource, instructions for extracting and saving the embedded spreadsheet are included in the PowerPoint presentation.

    This module is offered in two versions: a traditional SSAC version and a new auto-feedback/graded (AFG) version. The AFG version: (a) provides automatic and immediate feedback to incorrect answers, including formulas; (b) requires students to complete tasks sequentially by not allowing them to advance until they've completed a task perfectly; and (c) automatically computes a grade and encrypts it into a code the students submit to verify successful completion. The files needed for this version can be accessed here.

    Teaching Notes and Tips

    This module is constructed to be a stand-alone resource. It can be used as a homework assignment, lab activity, or as the basis of an interactive classroom activity. The two-part sequence Risk Assessment for Benton County, Parts 1 and 2 has been used to introduce students to Excel in Hazards of the Earth's Surface, an online service course at USF designed for non-majors, for the last two years.

    Assessment

    There is a slide at the end of the presentation that contains end-of-module questions. The end-of-module questions can be used to examine student understanding and learning gains from the module. The answer key for the end-of-module questions is at the end of the instructor version of the module.

    References and Resources

    Juster, T., Hazards and Risks, Part 1 (podcast).

    Juster, T., Hazards and Risks, Part 2 (podcast)

    Wang, Z., Graham, G.B., and Madin, I.P., 2001, Earthquake hazard and risk assessment and water-induced landslide hazard in Benton County, Oregon. Oregon Dept. of Geology and Mineral Industries. [http://www.academia.edu/27303612/Earthquake_Hazard_and_Risk_Assessment_and_Water-Induced_Landslide_Hazard_in_Benton_County_Oregon_Final_Report]

    Nelson, A.R., Kelsey, H.M., and Witter, R.C., 2006, Great earthquakes of variable magnitude at the Cascadia subduction zone, Quaternary Research 65(3), 354-365.