Investigating a Flood with EXCEL

1. Investigating a Flood with EXCEL Feb14, 2014 APEC Lesson Study Conference Hee-chan Lew & Chan-heeAn Korea National University of Education

2. Introduction • Teaching material for 6 hours for 10th graders and for advanced secondary students for investigating a flood with EXCEL based on the following principles: • Investigating a flood with Excel, students must use mathematics concepts or skills they have learned in schools. • Students need to use computer technology (Excel) in order to use complex data as realistic as possible. • Students must appreciate that mathematics is really valuable in handling natural disasters like issuing a flood forecast. • Students must understand that real situation is much complicated to consider more variables than in our materials. • Students must handle Excel by themselves byputting formulas on proper cells after understanding some mathematical concepts. • This material are supposed to be used in the textbooks based on the next national curriculum to be revised in 2016.

3. Basic information (I hour): Characteristics of a Flood in Korea • During Summer time of July to September, Korea has 2-4 typhoons carrying a heavy rain: Current average rainfall in summer time is 723.2mm and it is almost 55 % of the annual rainfall. • Two thirds of Korean Peninsular is a mountain area, consisting of granite and gneiss mainly to make soils difficult to keep a water.

4. Current Damage by Big Typhoons.

5. Process of a flood forecast • Korea Water Resources Corporation(KERC) • Functions of KERC: • It gets rainfall, an inflow and an outflow of main dams and rivers; measures water levels of main rivers; controls a storage of main dams or reservoirs in advance; issues aflood forecast • Here, students understand that some data for some key concepts are important to control a flood.

6. flood investigation • Three kinds of flood investigation • reservoir : for draining waters from reservoirs/dams for a flood controlin advance (2 hour material) • channel or river: for issuing a flood forecast in the river area(I hour material) • watershed : for forecasting a maximum outflow of the watershed(2 hour material) • Storage Equation to represent I, O, S

7. Reservoir Investigation • Inflow (I): Amount of water flowing in per second (㎥/sec) • Outflow (O): Amount of water flowing out per second (㎥/sec) • Storage (S): Amount of water kept in the reservoir (㎥)

8. Relation among I, O, S • Students can discuss with the following concepts: • If I-O > 0, the ratio of change for S is + • If I-O < 0, the ratio of change for S is -

9. Finding Storage using Excel • 𝐼−𝑂=∆𝑆/∆𝑡 is the Storage Equation I to represent the ratio of change for Storage • Problem 1: When I and O at given times are provided, find Storage for the time interval and accumulated Storage using EXCEL. • ..\..\..\Problem 1.xlsx

10. Storage Equation II • From Problem 1, students understand that in the discrete situation, we need to use the Storage Equation II rather than the storage Equation I: ∆𝑡−∆𝑡=𝑆2−𝑆1

11. S of a Dam according to Water levels • Average section method • If we know areas of sections at the two given water levels whose difference is small, we can find Storage between the two levels by multiplying the average section of the two sections and the level difference.

12. Example of finding Storage of a dam according to Water levels • Problem 2: Find Storage of the truncated conic shaped dam of which the bottom area at 30 m high is 2000m2 and the gradient of the side is 1:6. by increasing water levels by 0.2m each.

13. O of a dam according to Water levels • There are formulas for the outflow according to the water levels • water-gate: • no water-gate: Here, is the water amount coefficient, is the width of the waterway (m), is the distance from the water level to the structures (m), is an acceleration of gravity(m/sec2)

14. Example: Finding Outflow of a dam according to Water levels • Problem 3: If the dam has no watergate, the height of the dam is 31m and the width of the water gate is 10m, find the overflow according to water levels. Assume that the coefficient C of water amount is 1.6. • ..\..\..\Problem 2&3.xlsx

15. Outflow according to the time • Storage Equation III • If we use Storage Equation III, when the amount of Inflow of each time, we can find the amount of Outflow according to the time.

16. Example of Finding Outflow according to the time • Problem 4. There is adam to keep water during flooding period. The bottom area of the dam is 4000㎡ and the dam has a vertical wall. When we know the data of I according to the given times, find O according to time. (We assume that the initial dam water amount is 0) • ..\..\..\Problem 4.xlsx

17. Channel Investigation • Muskingum Method : This method was developed to forecast a flood on Muskingum area, Ohio, USA. • Storage Equation IV This method uses the storage equation IV formed by the special relation with the weighting value 𝑝 between Inflow and Outflow and the flowing index 𝑘 of the river :

18. Equation of Outflow • By using the equation IV and storage equation II, we can deduce the equation of outflow of the river: • ∆𝑡−∆𝑡=𝑆2−𝑆1

19. Example of Channel Investigation • Problem 4. If we know the data of inflow according to the given time, make a channel investigation by Muskingum method. We assume that 𝑝=0.25and 𝑘=1.5. • ..\..\..\Problem 5.xlsx

20. Watershed investigation • Watershed investigation is to forecast the final outflow of the watershed and its time after raining during some time interval. • How to calculate the outflow of the area?

21. Linear n dams • We have to assume that all amount of rainfall on the watershed flows through n hypothetical (non-exited) dams with linear arrangement in order to calculate outflow of the watershed for rainfall during some time interval.

22. Raining at an Instantaneous time • We have to suppose that the watershed has the rainfall of 1 cm at a instantaneous time to find the rainfall during some time interval. Why? • For example, if this area has 1 cm rainfall for 2 hours, we will interpret this rainfall as the average of two times 1 cm rainfall rained instantaneously at the beginning and the final of the 2 hour time interval.

23. Filling n dams • We also assume that the instantaneous rainfall fills n dams. It is possible because the dams are hypotheticaland we have to decide the number of n depending of rainfall. • The rainfall rained at the instantaneous time flows through n dams. The O of the (k-1)th dam is the I of the (k)thdam.

24. Relation between O and S • Usually, O and S are in the linear relation like the following equation: • Here, is the storage coefficient and empirically known that=(delay time)/n. • The delay time is the time difference between the time rain begins and the time of maximum outflow.

25. Equation for • implies that • By storage equation I, • By the instant rainfall, because the first dam was full, there is no inflow into the first dam, 1=0. So, • Because the outflow of the first dam is the inflowof the second dam

26. Equation for • Problem 7. Solve the above differential equation to find • Problem 8. Substituting the above equation for , find the equation for and repeat this process to find .

27. Example of finding maximum O • Problem9. Let’s the area of watershed is 325 km2and delaying time is 15 hours . Supposing that n=5 and n=6 respectively, compare their maximum outflows (㎥/sec). • ..\..\..\Problem 9.xlsx

28. maximum outflow and its time Problem10. Using the Excel data gotten in the case of n=5 in the problem 9, find the maximum outflow (㎥/sec) of the watershed and its time under the following condition. ..\..\..\Problem10.xlsx

29. Epilog • To use EXCEL spontaneously, students must know enough mathematical concepts and skills to represent a flood situation in mathematical expression. • Excel is a very useful tool for modeling situations with mathematical concepts and skills. • This semester, I will use these materials for high school students and revise it. Then I will report the result at my society or at the next APEC meetings.

30. Thankyoufor your attention