Tale of Two Cities (and two hurricanes): New Orleans
This is an activity that uses the spreadsheet program Excel to explore the origins of subsidence in New Orleans. There are two versions. The first is a traditional Spreadsheets Across the Curriculum (SSAC) module that couples a PowerPoint presentation with an embedded Excel spreadsheet where students construct a spreadsheet, and then submit the Excel file for grading. The second is a macro-enabled Excel spreadsheet that provides automatic feedback to answers and calculates the score. Upon completion the students are given a code that encrypts their spreadsheet score and then take a follow-up quiz that probes their understanding.
I use this in GLY2030, an introductory course in geologic hazards. Most of the students are first- or second-year students. The course is delivered entirely online.
Skills and concepts that students must have mastered
Students have completed at least two prior spreadsheet activities that emphasize basic Excel skills: differences between relative and absolute cell addresses, formulas, and some graphing.
How the activity is situated in the course
This is a stand-alone activity that students can elect to complete. There are five required spreadsheet activities, two of which are required and the other three which can come from a menu of 8 additional activities. This activity is part of the menu of 8.
Content/concepts goals for this activity
Content goal: to understand the origins of subsidence, and to consider how it has been effected by development.
Higher order thinking skills goals for this activity
To understand how difficult it is to assign 'blame' to any one cause when there are large uncertainties on the rates which govern the solution.
Other skills goals for this activity
To reinforce basic Excel skills, especially writing and copying equations, using relative and absolute cell addresses, and 'what-if' analysis.
Description and Teaching Materials
The basic core of the activity consists of an Excel spreadsheet file with a companion PowerPoint presentation The presentation explains the tasks that are required to be completed in the spreadsheet.
This activity is offered in two versions. The first is a typical SSAC module, which is contained in a single PowerPoint file that contains the Excel spreadsheet as an embedded file:
- A PowerPoint presentation with an embedded Excel spreadsheet; PPT (PowerPoint 2007 (.pptx) 7.1MB Apr27 12)
The second is a version that provides automatic feedback and grading, which requires three files:
- A Powerpoint presentation that is designed to be viewed alongside the Excel spreadsheet; PPTX (PowerPoint 2007 (.pptx) 7.8MB Apr27 12)
- A macro-enabled Excel spreadsheet file that performs the calculations; XLSM ( 1.3MB Apr27 12)
- A follow-up quiz based on the spreadsheet; PDF (Acrobat (PDF) 180kB Mar15 12)
Teaching Notes and Tips
The traditional version of the activity can be run on any computer, including Macs. The macro-enabled spreadsheets can only be run on PCs running Excel 2007/2010; I've been told they may work on Macs running the newest version of Office (Excel 2012), but I'm not sure. Upon completion the auto-graded spreadsheet a code is generated that that encrypts the student's score and name. The instructor then decrypts the code to get the score and ensure that the student hasn't cheated by copying someone else's code (it won't work).
The vast majority of students choose the auto-graded version of the activities and typically score better than 95%. The modal score is 100%.
Assessment depends on which version is used. For the traditional version, the instructor grades the spreadsheet file which contains both the calculations and answers to essay-type questions.
The auto-graded versions are different, in that spreadsheet activities are set up as a series of tasks that students MUST complete perfectly before they can move on. Most students get 100% on the spreadsheet portion. Students then take a follow-up quiz which asks the students to reflect on the calculations, and make some additional calculations that involve changing a number or two. These are graded.
References and Resources
Juster, T., Spreadsheet activities with conditional progression and automatically generated feedback and grades, submitted. reference
Burkett, V.R., Zilkoski, D.B., and Hart, D.A., Sea-level rise and subsidence: Implications for flooding in New Orleans, Louisiana, available at: .
Dixon, T.H., Amelung, F., Ferretti, A., Novali, F., Rocca, F., Dokka. R., Sella, G., Kim, S-W., Widowinski, S., and Whitman, D., 2006, Subsidence and flooding in New Orleans. Nature 44, 587-588.
Dokka, R.K., 2006, Modern-day tectonic subsidence in coastal Louisiana, Geology 34 (4) 281-284.
Dokka, R.K., Sella, G.F., and Dixon, T.H., 2006, Tectonic control of subsidence and southward displacement of southeast Louisiana with respect to stable North America, Geophysical Research Letters 33, L23308.
Dunbar, J.B. and Britsch III, L.D., 2008, Geology of the New Orleans area and the Canal Levee failures, Journal of Geotechnical and Geoenvironmental Engineering 134 (5), 566-582.
Grossi, P. and Muir-Wood, R., 2006, Flood risk in New Orleans, Risk Management Solutions Inc., available at: http://www.rms.com/Publications/NO_FloodRisk.pdf
Hart, D., 2006, Exploration of subsidence and elevation in Orleans Parrish.
Meckel, T.A., ten Brink, U.S., and Williams, S. J., 2006, Current subsidence rates due to compaction of Holocene sediments in southern Louisiana, Geophysical Research Letters 33, L11403.
Rogers, J.D., 2008, Development of the New Orleans flood protection system prior to Hurricane Katrina, Journal of Geotechnical and Geoenvironmental Engineering 134 (5) 602-617.
Snowden, J.O., Drainage-induced land subsidence in metropolitan New Orleans, Louisiana, USA. Available at: http://iahs.info/redbooks/a151/iahs_151_0507.pdf.
Tornqvist, T., Wallace, D.J., Storms, J.E.A., Wallinga, J., Van Dam, R.L., Blaauw, M., Dersken, M.S., Klerks, C.J.W., Meijneken, C., and Snijders, E.M.A., 2008, Mississippi delta subsidence primarily caused by compatction of Holocene strata, Nature Geoscience 11, 173-176.
US Army Corps of Engineers, 2009, Performance Evaluation of the New Orleans and Southeast Lousiana Hurricane Protection System, Volume 1: Executive Summary and Overview. Available at: https://biotech.law.lsu.edu/katrina/ipet/Volume%20I%20FINAL%2023Jun09%20mh.pdf.
Waltham, T., 2005, The flooding of New Orleans, Geology Today 21 (6): 225-231.
Yuill, B., Lavoie, D., and Reed, D.J., 2009, Understanding subsidence processes in coastal Louisiana. Journal of Coastal Research 54, 23-36.