Title: Water Resources Analysis Using Electronic Spreadsheets, Table 5: Volume Stage Relationship for the Hypothetical Subdivision
Full Citation
Permanent Link: http://ufdc.ufl.edu/WL00000698/00001
 Material Information
Title: Water Resources Analysis Using Electronic Spreadsheets, Table 5: Volume Stage Relationship for the Hypothetical Subdivision
Physical Description: Photograph
Language: English
Spatial Coverage: North America -- United States of America -- Florida
Abstract: Water Resources Analysis Using Electronic Spreadsheets, Table 5: Volume Stage Relationship for the Hypothetical Subdivision
General Note: Box 7, Folder 1 ( Vail Conference 1987 - 1987 ), Item 91
Funding: Digitized by the Legal Technology Institute in the Levin College of Law at the University of Florida.
 Record Information
Bibliographic ID: WL00000698
Volume ID: VID00001
Source Institution: Levin College of Law, University of Florida
Holding Location: Levin College of Law, University of Florida
Rights Management: All rights reserved by the source institution and holding location.

Full Text

Table 5. Volume-Stage Relationship for the
Hypothetical Subdivision.


346 Sum
347 Storage Stage
348 acre-ft feet
349 0 11
350 100 12.8
351 200 14.2
352 300 15.2
353 400 15.6
354 500 16
355 600 16.2
356 700 16.5
357 800 16.7
358 900 17
359 1000 17

following equation was used in Column H:


-@VLOOKUP(SV,range,l))*(SV/100-@INT((SV/100))) ...(10)

where SV = stored volume, ac-ft. The @INT command produces the

integer of the term within the parentheses. Therefore, this

equation looks up the two neighboring values, does a linear

interpolation, and produces the answer. Alternatively, a

function could be fit to the data. Since the model is displayed

completely before the user and operates so quickly, many runs may

be performed within minutes, and parameters may be changed with

instant response.

These models can be contained within one spreadsheet, and

may be interconnected to produce more complex models. Although

1-2-3 allows existing models to be constructed on the spread-

sheet, such as the two discussed earlier, it also provides an

excellent means to develop new models. The following is an

example of a model that was developed and used in the Cypress

Creek study.

Spreadsheet Modeling Case Study

The HSPF model has been used in the Cypress Creek study to

analyze the surface hydrology and to perform a simplified

analysis of the groundwater system (Hicks, 1985). However, HSPF

cannot analyze the behavior of the shallow aquifer when the water

table falls below the streambed. Since streamflow is primarily a

function of the height of the water table in many parts of

Florida, this becomes a serious problem. HSPF also does not



incorporate the impact of pumpage.

Available groundwater models do not run on a continuous

basis. Thus, they are unable to output the necessary time series

information on well stages over several months or years.

Development of a comprehensive surface-groundwater model which

can run continuously would be a major effort and was well beyond

the available resources for this study. This model would be very

large because of its need to anticipate all possible configura-

tions of surface and groundwater systems as well as types of

problems ranging from flood to drought analysis.

Fortunately, for a specific study area and problem, the

required complexity of the model can be reduced significantly by

examining the local data. Also, the particular problem can be

diagnosed by formulating and testing specific hypotheses

regarding the anticipated behavior of the system. The knowledge

base construction and data analysis techniques presented in the

previous sections provided excellent insight as to the more

important aspects of the problem. Also, the results of the HSPF

and groundwater simulations gave a good indication of which

components of the hydrologic cycle are more important for the

study area and the specific problem.

A Continuous Surface-Groundwater Model

Using Lotus 1-2-3, a daily surface-groundwater model for

upper Cypress Creek was developed for the year 1979. Depth-

dependent relationships were included for both evapotranspiration

and stream flow. The model keeps track of the water table and can



M handle the case where it falls below the streambed. It is

calibrated against both streamflow and water table levels.

The daily water budget equation for the area is

dS/dt = (P-ET-Q-L)/(12*K) .............. ... ...... (11)

where dS/dt = daily change in the shallow water table, feet of

soil, P = rainfall, inches of water, ET = evapo-transpiration,

inches of water, Q = streamflow, inches of water, L = leakance to

lower aquifer, inches of water, and K = inches of water per inch

of soil. The water table elevation at the end of each time step

is estimated as a function of each of the above sources and

sinks. Thus, all inputs and outputs of water in inches are

converted to feet of change of the groundwater in the soil.

The table is formatted so that the calculations can be

I easily followed by the user. All of the assumptions and

parameters used in the model are input below the table as are a

set of summary statistics for the simulation, a continuity check,

and a presentation of the contributions of each water budget


The results of the daily Lotus simulation for Cypress Creek

above the San Antonio stream gage are summarized in Table 6. The

rain is estimated as a weighted average of the St. Leo and the

Cypress Creek gages, i.e.,

P = a*P(1)+(l-a)*P(2) ........................ .. (12)

This weighting factor, a, is used as a calibration parameter. By

comparison, the HSPF simulations were done using a single gage.

S The evaporation is the daily evaporation from the Lisbon station,



University of Florida Home Page
© 2004 - 2010 University of Florida George A. Smathers Libraries.
All rights reserved.

Acceptable Use, Copyright, and Disclaimer Statement
Last updated October 10, 2010 - - mvs