A Time Saving Tool

Water system modeling can be enhanced by the use of spreadsheets

• By William A. Timmons
• Mar 01, 2005

Computer spreadsheet software provides a powerful means for the planning, preparation, calibration, and use of a water distribution, pumping, or storage system computer model. In addition, for small system main extensions, single pump stations, tank evaluations, or design, a spreadsheet model can greatly simplify the process and eliminate the effort required to setup a more complex system network model. Standard spreadsheets can be prepared to assist in setting up models of the pump or piping and conducting the analysis of interest quickly and easily.

A system model is a mathematical description of a given piping, pump, and tank system (physical model) prepared for the purpose of calculating pipe flow, system pressures, tank or wetwell volumes, etc. under existing or proposed real-world water-demand (flow-model) situations. These calculations are typically performed to evaluate existing problems, the effects of future system growth, or for design of new system infrastructure.

Various factors considered in preparation and use of a system model are discussed below and selected spreadsheet tools are demonstrated. These spreadsheets are not meant to replace the powerful system network modeling software available in today's complex modeling applications, but can assist in efficient use of such software and in efficient use of your time for less complex modeling problems.

Modeling Applications
Computer modeling of water supply, transmission, and distribution systems is commonplace in today's utility environment. There are many applications for an accurate model of a water utility's distribution system -- including planning, design, operations, and maintenance evaluations; value-engineering studies; and emergency planning. The burden rests with the modeler to prepare an accurate physical pipe and system flow model tailored for the purpose intended and to debug, calibrate, and document the model to assure its accuracy and give confidence in its use and results.

This article presents some of the modeling issues and problems experienced in the preparation, use, and results interpretation of water system models. Selected spreadsheet modeling tools are also presented.

Preparation of Physical System Model
Modeling a system can be reliable only if the physical data entered into the model is correct. That means first of all that the maps, plans, and information forming the basis for the model must be as accurate as possible. This goes back to the recordkeeping of the system being modeled. A primary goal of system engineers or utility managers should be to keep accurate, up-to-date records of their system. Not only to know what size pipe is in the ground and where it is located for operation and maintenance, but also to be able to accurately predict the capacities and characteristics of the system when modeling.

Preparation of Flow Model
The flow model must be a reasonable estimate of demand at determined locations in the system to obtain reliable results from the modeling calculations. It must include accurate base or current demands that can be generated from pumping records, billing records, meter readings, pump run-time measurements, or other means. The flow model also includes future demands, which can be generated from historical trends, population growth projections, zoning density estimates, developer input, or existing planning projections using base flow per capita demands or typical unit demands. Fire flows must similarly be determined and included for the proper evaluation of the system capabilities.

Where current usage data is not available, and for future demand projections, a standard demand calculation spreadsheet can quickly calculate standard design flows for use in the model. The same sheet can be used for a wide area and the demand distributed; or the sheet can be copied and used for each demand input as deemed appropriate by the person preparing the model.

Other considerations for the flow model include peaking factors and diurnal demand curves. Existing system records can usually assist in the generation of average flows, but daily and hourly peak values must rely on flowmeter records or field-testing. Spreadsheet calculations can be set up based on pump curve data and operation or pressure records of period flows to determine diurnal flows and resulting peaking factors.

Calibration of the Model
Calibration of all but the simplest models is recommended for the physical model and as a check of the base demands for the flow model. To use calibration as a check of your model preparation, it is also necessary that accurate data be gathered for calibration. If you don't know the system-wide situation when making measurements, if you don't plan for the proper times to measure flows and pressures, or if the system is not stressed adequately to generate significant headloss to allow accurate evaluation of friction factors, then your calibration data will not be as helpful in determining the accuracy of your model.

Model Complexity Considerations
Today's powerful desktop computer and modeling software gives us the opportunity to design and evaluate piping networks that were not conceived of a few years ago. We have the ability to determine the pressure and flows at points in a piping network in a few minutes that in times past, using manual calculations and methods like the Hardy-Cross analysis, would have taken an individual months or even years. The benefit of all this power is obvious, but the problem we have such power is significant. It is worthwhile to spend time considering how we might simplify the modeling process and determine what effort is required to achieve our planning, design, or system evaluation goal.

Interpreting the Modeling Results
With a large network model it is easy to get bogged down in interpreting the model output. If you have properly planned each run in your network evaluation, you will have in mind a modeling objective and related results you want to achieve. You will have set a flow condition or a pressure condition and then input demands, pumping situations, tank levels, valve positions, etc. that serve as model input and determine the resulting output of the model. To interpret this output, there are varying ways to look at the calculated results. These include calculated tank levels, system pressures, or individual main flow or velocity to get a look at a point in the system. You might also generate system pressure contours, hydraulic grade line, and pressures along particular pipe segments and so forth for an area-wide evaluation.

Maintenance and Documentation Issues
Proper documentation of the model and maintenance with continued use is necessary to protect against errors and loss of accuracy. With spreadsheet models, this documentation can be added as another tab in the spreadsheet, thus saving the documentation permanently in the spreadsheet file. Another advantage of spreadsheet models is that by copying the model you have setup for each condition within the same spreadsheet as a new tab, you can maintain all your runs in one file with comments on input and results. You can even setup summary sheets linked back to each model sheet to present the range of calculations.

In summary, the basic spreadsheet software provided in typical computer software office packages can be a powerful system-modeling tool for the system manager, engineer, and operator. It allows preparation of powerful calculation tools that can be tailored to the individual user's specific system modeling, calculation, or reporting needs; and once prepared, it can be quickly used again whenever needed.

This article originally appeared in the 03/01/2005 issue of Environmental Protection.