An Experiment in Modeling Groundwater using Excel
http://www.aspire.cs.uah.edu/aspire/sc00/gw_module/gw_module.html
Module Description Requirements Objectives Introduction to Groundwater Featured Facts - Background Information Activities and Procedures References and Resources Teaching Strategies Thought Provoking Questions National Standards Addressed Cross Curricular Connections Assessment Techniques Glossary
Materials prepared by Edna Gentry, University of Alabama at Huntsville
Module Description
This sample module will introduce the idea of groundwater movement. It will also introduce some techniques of computational modeling using Excel. This model was first developed in 1999 by the Adventures in Supercomputing team. It has been modified to fit the parameters of the SC National Computational Science Leadership Program "Developing Educational Leadership in Computational Science". Modified by Edna Gentry, University of Alabama in Huntsville, October, 2000. Other sources were used for A Grave Mistake and the Incredible Journey. See References for a complete list of sources.
Requirements Classtime required for the completion of this model:
This model can be completed in class in approximately 2-3 class periods.
Materials required for the completion of this model:
Instructor’s station with projector; White board; Computer lab with Internet access; A Grave Mistake Scenario handout A Grave Mistake Instructions for model Dataset 2 and grave_mistake_map.gif Soil Absorption Rate Experiment Instructions Plume Experiment Incredible Journey Instructions
Objectives
Upon completion of this lesson the participants will be able to: Explain the locations of water and the Water Cycle Describe the way water moves from one location to another Develop a computational algorithm to model the sample groundwater model Develop a computational algorithm to model a real life situation in groundwater flow Revise assumptions, relationships, input and output variables and parameters as needed Develop a computational model for rainfall and floods Import the data into a visualization tool and graph it; Use reasoning abilities to form conclusions about the problem;
Introduction to Groundwater
Background information and the Basics of Groundwater can be found online at http://www.groundwater.org/GWBasics/gwbasics.htm Information found on the above website includes: What Is Groundwater? How Much Do We Depend On Groundwater? Groundwater Protection Hydrologic Cycle Contamination and Concerns Sources of Groundwater Contamination Wells and How They Work Ten ways you can help conserve and protect groundwater Groundwater Glossary The Basics of Groundwater page is developed and maintained by the Groundwater Foundation.
Activities and Procedures A Grave Mistake Model
Scenario Map 1 with Dataset 1 Excel chart for Dataset1 (must use Internet Explorer and must have Excel 2000 to view chart) Dataset 2 Excel chart for Dataset2 (must use Internet Explorer and must have Excel 2000 to view chart) Model Instructions for Excel Use Plume Experiment Instructions Soil Rate of Absorption Experiment Instructions
Classroom Procedures
Present background materials on groundwater; Play the Incredible Journey game to demonstrate groundwater flow, if desired; Present the A Grave Mistake Scenario; Distribute the initial dataset (Map1 with Dataset 1) and have participants enter the data into an Excel worksheet; Identify input variables (current well readings);
Discuss the mathematical techniques using LaPlace’s equation and diffusion, and the computational modeling techniques of Cellular Automata. Explain how this is accomplished in Excel through the use of iterations (see the list of instructions for a full explanation of how to enter the formulas; Create an image using the data obtained by running the Excel model, place the image on a separate worksheet within the workbook; Distribute dataset 2 and have participants enter the data into a different Excel worksheet within the same workbook; TIP: It is a good idea to have students highlight the data in the first worksheet, copy it, then paste it to a new worksheet in the same workbook. By using copy/paste they will not have to re-enter the data and formulas. Create an image using the data obtained by running the Excel model (to only calculate one worksheet at a time use Shift+F9 instead of F9) All of the documents and instructions for developing the model for the Grave Mistake example can be found in the Model Instructions for Excel Use from the list above; Analyze data and state assumptions about the model; Perform the Plume Experiment (see list above for instructions) and discuss how pollution spreads; Have students brainstorm a conclusion; Have students investigate embalming procedures of the nineteenth century; Transfer the knowledge gained through the development of the model to a relevant topic for your location; Discuss other water related topics, such as flooding What causes a hundred year flood?
Why do locations flood that have never flooded before? Can rainwater runoff be modeled? If so, what input variables should be used? (rate of rainfall, soil absorption rate, type of ground surface) Use the Soil Absorption Rate experiment to determine how fast rain might be absorbed into the ground in several different types of soil around your school; Perform some runoff experiments using the software on the Shodor Foundation website for runoff found at http://www.shodor.org/master/environmental/water/runoff/index.ht ml
A Grave Mistake Model Instructions
1. Distribute the Grave Mistake scenario and briefly discuss. 2. Distribute copies of the grid/map showing the concentration of arsenic in the specified locations 3. Set up the spreadsheet to be the size of the map, 10 columns and 14 rows The number of rows and columns may impact data positioning. Want to keep scale fine enough to have one data point per cell. 4. Use averages and iterations to determine values between the values given. Averages to create smoothing between the few data points. Iterations cause repeated calculations where there are multiple dependencies. 1. Go to Tools/Options/Calculations and a. Set calculation to Manual b. Notice the Calc Now button (F9). This means to manually calculate press F9 (to manually calculate only the current sheet press Shift+F9). c. Select Iteration d. Set Maximum Iterations to 100 (or whatever you choose) e. Set Maximum change to 0.001 (or whatever you choose) f. Remember that you must change Calculation back to Automatic before leaving this model 2. Start by creating row and column headers – to help see the alignment when you later create a chart. You might call columns (Map A, Map B, etc.) and Rows (Map 1, Map 2, etc.). You can copy and paste names to simplify the process. Click on a cell to highlight it, then move the cursor to the lower right corner of the cell to see “cross-hairs”, then drag the cell across other cells that should look similar. Do you see that numeric values are automatically incremented? 3. Enter data values collected from field samples in the cells that correspond to the map locations.
3. Each interior cell should have a formula that averages the 4 cells surrounding it, (above cell + right cell + below cell + left cell)/4 (for example, the formula in E12 would be =(E11+F12+E13+D12)/4, you are required to put an equal sign before the formula to indicate a calculation) Use “Insert” “Function” as an alternative (SUM or AVERAGE)
3. Edge cells will only use the active cells around them (for example the formula in A3 would be =(A2+B3+A4)/3)
4. It is possible to copy and paste the formula in the blank cells, but do not replace a value you already have in a cell with a formula 5. You will not get valid numbers in the cells until you manually calculate (remember you set the calculations to manual) 6. Manually calculate by pressing F9
5. Once the values are in place, create a graph of the data. 1. Highlight the cells containing the data including the row and column headers. The headers will appear on the chart. 2. Select the graph tool (in the toolbar it looks like a 3-D bargraph) 3. Step 1 of 4: Chart Type a. Select Surface under Chart Type b. Select Contour under Chart Sub-type 4. Step 2 of 4: Chart Source Data a. Verify Data Range b. Select Series in Rows 5. Step 3 of 4: Chart Options a. Give names to the Chart Title and the Axes names 6. Step 4 of 4: Chart Location a. Select As A New Sheet, this will place the chart on a different sheet from the data. 7. Double-click the Legend to modify
a. Select the Scale tab b. Change the Major Unit to 10 8. Do you notice a difference between the chart headers and the original community map? The map looks inverted. To flip the map vertically, hold the mouse over the labels on the vertical axis, until you see “Series Axis”, the double click on this box, and you will see a “Format Axis” Dialogue box. Be sure you are on the “Scale” menu selection. Click on “Series in reverse order” box, and click “OK”. The map now matches the grid you setup. Notice the area with the highest values (most arsenic content) – note that it appears to be around the factory. 6. Discuss plume effect of pollution 7. Discuss or do the plume experiment (see attachment for instructions) 8. Separate into teams to discuss the possible sources of the pollution and devise a plan 9. Possible solutions might be 1. Conduct research about arsenic poisoning and groundwater contamination. 2. Learn more about groundwater flow. 3. Investigate the factory, since the level or arsenic seems to be high near that site. Upon further investigation of the factory, it was found to follow the federal requirements concerning the use of all toxic substances. Furthermore, company records show that all arsenic has been properly used and disposed and there is nothing to cause suspicion that any arsenic has entered the groundwater from the factory. 4. Drill more wells and collect more data 10. Provide the new map with the data from the new wells drilled 11. Repeat steps 8-10 12. Discuss possible sources of the pollution now 13. Provide the information concerning the Civil War cemetery and the use of arsenic as an embalming material.
A Grave Mistake Scenario
You are a physician in a small community of about 1000 residents. Yours is an old family in this area; your great-great-grandfather settled here in the 1800s and was a famous officer in the Civil War. Several members of your family are buried in the local cemetery. Over the past couple of years a few members of the community have described to you their puzzling physical symptoms. Recently, a patient presented you with similar but more serious complaints: weakness, tingling, and numbness in his hands and feet. You listen carefully as your patient responds to questions related to his medical history. He works in the small local factory (Private well #6 on the Community Map) that produces wood preservatives. He has lived in the same area for about ten years. He and his wife of ten months have a private well at their home. His wife has not exhibited similar symptoms. He quit smoking three years ago and does not drink alcoholic beverages. He takes no medications, only vitamins. You meet with members of the town council and express your suspicions- that the symptoms you have documented over the last few years are related to chronic arsenic poisoning from contaminated drinking water. You advise them that the accepted level of arsenic in drinking water is 50 ppb( parts per billion. The town council votes to budget money for ground water testing that will initially be limited to well already in existence.
Data Set 1 Abandoned, Private and City Well Data
border border border border border border border border border border border border border border border border border 0 border border border 0 border 0 border border border border border
0
54 12 24 39 30 22.5 6 15 12 0 border border border border border border border border border border 3 35
9
0
Data Set 2 Expanded set of collection points
A Grave Mistake Dataset 2
B12 = 0 E12 = 0 G12 = 0 I12 = 0 B10 = 13 G10 = 20 I10 = 18 B8 = 18 E8 = 38 G8 = 42 I8 = 33 B6 = 15 E6 = 42 G6 = 61 I6 = 48 B4 = 6 E4 = 32 G4 = 65 I4 = 70 B2 = 0 E2 = 0 G2 = 0 I2 = 78
Dataset 2 – additional testing
Border Border Border Border Border Border Border Border Border Border Border Border Border Border Border Border Border 0 Border 0 4 15 12 18 35 13 6 0 0 0 Border Border Border Border Border Border Border Border Border Border 22.5 20 15 0 20 12 0 3 24 38 39 42 30 Border Border 0 Border 0 32 42 54 Border 0 Border 0 65 61 Border Border 78 70 48 35 33 22.5 18 0 Border
0
9
0
The Plume Experiment
This experiment will demonstrate the plume effect in the spread of pollution.
1. In a 9-14 dish place a package of grape fruit drink mix (Kool-Aid) in a pile (do not distribute it all over the bottom of the dish) 2. Put sand in the dish at least 1 inch deep. 3. Fill a spray bottle with water. 4. Elevate one end of the dish about an inch. 5. Spray the location of the drink mix with the water in the spray bottle. You will probably have to continue spraying for several minutes to simulate months or years of rainfall. 6. Hold the dish up to look at the bottom. 7. You will notice how the grape drink mix has spread in the shape of a plume. 8. This is known as the plume effect of pollution.
Soil Absorption Rate Experiment
This experiment will help you approximate the absorption rate of soil.
1. Cut the top and bottom from a can. 2. Hammer the can halfway into the ground you want to test. 3. Pour a specified amount of water into the can that is in the ground. The amount of water should not overflow the top of the can and you should be able to pour all of it into the can at one time. 4. With a stopwatch determine the number of seconds that elapse until the water is absorbed into the soil. 5. Divide the number of seconds by the number of ounces to get a rate in ounces per second.