Assignment #4- Hydrology by Spreadsheet

In this laboratory you will use an excel spreadsheet to solve a hydrology problem.

1. Hydrology Concepts

 (a) There are dome fundamental relations:                    

S flux in =

 S flux out                                         (1)

                Precipitation =

 Evapotranspiration + Infiltration + Runoff

                 S flux in - S flux out     =

  ± D storage                                     (2)
Residence Time in a reservoir =   Mass of reservoir / flux (in or out)

Equation (1) assumes a steady state, no water is gained or lost. The same is true for the balance shown for precipitation - it all goes somewhere.

Equation (2) accounts for differences between input and output due to storage.

(b) There are four large connected reservoirs in the Earth System:      

Region

 Size (x 1015 kg )

Oceans

 1,400,000

Land

 59,000

Marine Atmosphere

 11

Land Atmosphere

 4.5

(c) There are two basic principles:  

Flux is conserved between reservoirs

Residence Time = Size / Flux In (or Out)

Read the handout carefully. It was provided by Dr. Seth Rose, Department of Geology, Georgia State.

2. Hydrology Exercise - Scenario 1

Complete the following steps:

(a) Start a new Excel Spreadsheet called assn4.xls. Enter the headings and columns given in Sheet 1.

[Hint: it is not necessary to leave so much space between lines, you can make the layout much tighter without sacrificing clarity. ]

(b) Make one cell for the precipitation rate for the basin. Under the column percent flux, enter the percentages given in the first figure. These are the only hard numbers, the rest of the cells are formulae.

(c) To calculate total ET, STREAM and Groundwater storage, add up the appropriate cells and enter the formulae.

(d) Convert percentages to inches per year for the next column. Repeat for cubic meters/day, gallons per day and cubic feet/sec for the subsequent columns.

(d) Answer the following questions:

  1. What is the total ET in cubic ft / sec?

  2. What is the total runoff in cubic ft / sec?

  3. Does the system balance? Explain.

  4. Assuming a person uses 200 gal/day, how many people can the basin sustain?

  5. Calculate the volume of the groundwater reservoir using area, depth and porosity (porosity is the fraction of pore space).

  6. Calculate the residence time of the groundwater.

 3. Hydrology Exercise - Scenario 2

This scenario begins with the assumption that global warming occurs, so we make some modest changes in some of the parameters. Change the groundwater discharge, unsaturated soil moisture and saturated groundwater percentages as indicated.

(a) Calculate the changes to ET, runoff and the percent change in streamflow.

(b) How many people can the basin sustain under the new conditions?

 

Print out and hand in the two spreadsheets, together with your answers to the questions.

----------------------- end of Assignment #4 ----------------------