Introduction to Hazard and Risk: Part 1

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

Summary

SSAChaz.TCJ.1(trad)
In this Spreadsheets Across the Curriculum activity, students are introduced to the spreadsheet program Excel while preparing a risk assessment for Benton County, Oregon, based on the threat of a catastrophic earthquake along the Cascade subduction zone. This is the first in a sequence of two modules on this topic, and emphasizes basic Excel skills that will be needed in all subsequent activities. In the module students first are introduced to the idea of Excel formulas by creating a summary table of structures in Benton County (which is needed to compute the monetary exposure to risk). They then learn about built-in functions such as SUM, COUNT, and MAX. The final task requires the students to reprise these skills on a table of buildings classified by construction.

Used this activity? Share your experiences and modifications

Learning Goals

Students will:

  • Examine a geologic map of Benton County and consider how the geology affects the distribution of population and exposure to risk.
  • Given a row of numbers representing the different classes of structures in Benton County, compute the total number of structures and the percentage for each class.
  • Using a table of hurricanes from the Atlantic and Caribbean from 2005-2010, compute the number of years, number of hurricanes, maximum number in any year, minimum number in any year, and average number of hurricanes per year.
  • Use these same tools to compute some statistics for the list of structure types in Benton County.
  • Given a table of structures in Benton County organized by construction, compute the percentage of the total for each.

In the process the students will:

  • Lean how to read a geologic map.
  • Learn how to enter formulas into a cell on an Excel spreadsheet.
  • Discover the difference between relative and absolute cell references.
  • Learn how cell formulas can be copied to other cells.
  • Learn how to find and use some built-in Excel functions, such as SUM, COUNT, and AVERAGE.
  • Practice these new skills through repetition on different subjects.

Context for Use

This module was designed for use in the Hazards of the Earth's Surface service course at USF. It makes no assumptions about prior experience or familiarity with Excel, and thus has potential for any class in which students need to develop Excel skills. 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 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 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]