Accepted for publication,J. of Water Resources Planning & Management,ASCE


WATER RESOURCES ANALYSIS
(fi USING ELECTRONIC SPREADSHEETS

By

Michael C. Hancock 1 and James P. Heaney 2, M. ASCE


INTRODUCTION

Comprehensive mathematical models have become very popular

Stools for solving water resources problems. In a recently

completed study, two popular hydrologic models were used: the

Hydrologic Simulation Program-Fortran (HSPF) for simulating sur-

face hydrology, and the USGS model for simulating groundwater.

The HSPF model required 150 parameter estimates and the USGS

model required 4000 parameter estimates. Initially, the

parameter estimation was done manually. Then, it was decided to

use an electronic spreadsheet available on microcomputers in

order to expedite the parameter estimation process and to provide

better quality control on how these values were selected.

As experience was gained with using spreadsheets, it became

apparent that they are very powerful computational tools. Thus,

a variety of analyses were done that traditionally required

mainframe computers. Some of these applications are described in

this paper.



-Graduate Assistant, Department of Environmental Engineering
Sciences, University of Florida, Gainesville, FL. 32611.
professor of Environmental Engineering Sciences, and Director,
L'lorida Water Resources Center, Univ. of Florida, Gainesville,
F'L. 32611.



3"










HYDROLOGIC ANALYSIS AND THE ELECTRONIC SPREADSHEET

Lotus 1-2-3, the most popular of the electronic spread-

sheets, is so named because three programs are combined into one:

the electronic spreadsheet, business graphics, and data manage-

ment programs. Version 2 of this spreadsheet is a matrix of 254

rows and 8192 columns, and can be considered to be a very large

sheet of electronic paper (Lotus Development Corporation, 1985).

Over 12000 page equivalents can be placed onto one spreadsheet.

The user is able to add or erase calculations, text, or data in

any block, or cell, at any time, and can import or export any

part of the work and data to and from other software. The data

handling capabilities allow the user such an easy method of

manipulation that many types of problems can be solved with

little effort. It literally replaces the paper and pencil.

Besides the comprehensive manual that accompanies this soft-

ware, several books have been written on the uses of Lotus 1-2-3

(e.g., LeBlond and Cobb, 1983, Anderson and Cobb, 1984, Ridington

and Williams, 1985), and a monthly publication, LOTUS, is

dedicated to advancing the use of the program. Although these

many guides exist, Lotus 1-2-3 is almost self explanatory once a

few basic concepts are understood.

The electronic spreadsheet has many uses in engineering

because of its versatility in data handling and analysis. In

water resources, this analytical tool can be very useful in many

areas, including data preparation and analysis, parameter estima-
tion, and simple simulations. Also, it can be used as a pre- and

2

q03











post-processor for larger models. The next section introduces

spreadsheets and data handling, with emphasis on the knowledge

base capabilities of the spreadsheet. An example is used to

illustrate how this simple, yet effective, method can be used in

hydrologic problem solving. Then, these ideas are expanded to

include data and parameter analysis for use in comprehensive

hydrologic models. Also, methods of performing basic hydrologic

analyses on the spreadsheet are presented that may preclude the

need for more comprehensive models. Examples are taken from a

recently completed water resources study of the impact of well-

field development and surface drainage on the hydrology of the

Cypress Creek Basin north of Tampa (Heaney et al., 1986). A more

complete explanation of the use of spreadsheets in hydrological

analysis can be found in Hancock (1986). Johnson (1986) des-

cribes the use of spreadsheets as part of a decision support

system for water resources. Olsthoorn (1985) shows how to use

spreadsheets for groundwater modeling.

SPREADSHEETS AND DATA ANALYSIS

Mapping

Main-frame and microcomputer based geographical information

systems are available, but they require specialized expertise and

facilities to use. Fortunately, a simple mapping system using

the spreadsheet can be devised.

A spreadsheet-based map of the Cypress Creek study area

showing the location of each raingage is presented in Figure 1.

f Through the use of a 1-2-3 XY plot and data labeling commands,