Table 5. VolumeStage Relationship for the
Hypothetical Subdivision.
R S T U V W
346 Sum
347 Storage Stage
348 acreft 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
360
361
362
following equation was used in Column H:
@VLOOKOP(SV,range,l)+(@VLOOKUP(SV+100,range,l)
@VLOOKUP(SV,range,l))*(SV/100@INT((SV/100))) ...(10)
where SV = stored volume, acft. 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
123 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
22
^23
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 surfacegroundwater 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 SurfaceGroundwater Model
Using Lotus 123, a daily surfacegroundwater 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
23
^;.n
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 = (PETQL)/(12*K) .............. ... ...... (11)
where dS/dt = daily change in the shallow water table, feet of
soil, P = rainfall, inches of water, ET = evapotranspiration,
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
component.
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)+(la)*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,
24
y.3^2
