United States
Environmental Protection
Agency
Municipal Environmental Research ^
Laboratory
Cincinnati OH 45268
Research and Development
EPA-600/S2-83-069 Sept. 1983
&EBA Project Summary
Development and Application of
a Water Supply Financial
Reporting System: Volumes I
and II
Victor C. Fender, James I. Gillean, and William L Britton, Jr.
This two-volume report describes the
development of a computerized finan-
cial reporting system (FRS) for the
Kenton County, Kentucky, Water Dis-
trict No. 1 (KCWD). The system was
designed and implemented after a
computer-based cost analysis system
(CAS) had proved to be advantageous
and had demonstrated the value of
automated data processing. Together
the two systems will help the utility
control costs and manage a rapidly
expanding system more effectively and
efficiently. The objectives of the FRS
were (1) to provide computer programs
that produce a general ledger accounting
system and financial reports, (2) to
function as a modular system that can
be used either independently or with the
CAS, and (3) to verify its satisfactory
operation through practical applications
at KCWD.
The computer programming was
accomplished by use of American
Standard COBOL operating in an IBM
370 environment. * This combination of
software and hardware was chosen
because of its widespread use.
The FRS was able to satisfy the
KCWD reporting requirements with
information furnished monthly. The
system has met the basic requirements
and is considered a proven system
because it has produced monthly and
annual reports parallel with the old
manual system, and it has provided
financial data for rate adjustments and a
bond issue.
•Mention of trade names or commercial products
does not constitute endorsement or recommenda-
tion for use.
This Project Summary was developed
by EPA's Municipal Environmental
Research Laboratory, Cincinnati, OH,
to announce key findings of the research
project that is fully documented in a
separate report of the same title (see
Project Report ordering information at
back).
Introduction
The Kenton County, Kentucky, Water
District No. 1 (KCWD) has developed a
computer-based financial reporting
system (FRS) that is compatible with their
automated cost analysis system (CAS).
Together the two systems will help the
utility to control costs and manage a
rapidly expanding system more effectively
and efficiently.
The CAS was developed first as a
management tool for cost control. The
system consists of a group of computer
programs that use financial and opera-
tional information to identify expenses to
cost centers. After the CAS proved
advantageous and demonstrated the
value of computerized data processing,
KCWD took steps to develop an auto-
mated FRS that would be compatible.
This two-volume report discusses the
background conditions and results of the
project in Volume I and provides complete
documentation (including diagrams and
program listings) in Volume II.
Background
The Safe Drinking Water Act of 1974
(PL 93-523) necessitated a fundamental
reexamination of the way in which
drinking water is supplied tothe American
-------
public. A primary feature of the Act is that
the economics and cost of water supply
and delivery must be considered before
regulations are promulgated. In 1974, a
cost study of selected water utilities was
undertaken to collect data from at least
one Class A water utility (revenues
greater than $500,000 per year) in each of
the 10 EPA Regions. The purpose of the
study was to collect data in such a
manner that the resulting information
could be easily compared among utilities
and provide an understanding of some of
the fundamental factors that affect the
cost of water supply and utility manage-
ment.
One conplusion resulting from the
investigation was that although each of
the selected utilities kept financial
records (in some cases extensive ones),
the data among utilities varied signifi-
cantly. Another conclusion was that
many of the records were kept primarily
for accounting purposes and had little
relation to the short- and long-term
operational needs of the utility manager.
After carefully analyzing the data flow
and the information transfers of a
number of large and small utilities, the
investigators developed a technique for
data collection and analysis that would
fulfill both managerial and accounting
needs. The technique was based on a
matrix concept and was tested by means
of a water supply simulation model
developed by the Drinking Water Research
Division of EPA in Cincinnati.
After the data collection and analysis
technique was proved both effective and
efficient in a hypothetical situation, it was
developed into the CAS and implemented
at the KCWD, where it was further
refined and modified based on operational
experience. Both the system and the
KCWD experience are described in a two-
volume report entitled Development and
Application of a Water Supply Cost
Analysis System (James I. Gillean,
William L Britton, Jr., John H. Brim, Rex
D. Osborn, and Robert M. Clark, July
1980, EPA-600/2-80-012a and EPA-
600/2-80-012b, U.S. Environmental
Protection Agency, Cincinnati, OH 45268).
The CAS implemented at the KCWD
was functional on a computer and
provided timely cost accounting data. At
the time of implementation, the KCWD
had a manual financial reporting system,
and their expanding service area was
creating a heavy burden of record main-
tenance. The CAS made the advantages
of automation obvious, and therefore
KCWD took steps to develop an FRS. The
FRS was to provide KCWD's routine
financial reports and was to be designed
to use the same source data as the CAS.
Figure 1 illustrates the combined CAS
and the manual financial reporting
system used by KCWD before develop-
ment of the automated FRS.
Objectives of the Financial
Reporting System
The following objectives were estab-
lished for the FRS:
1. Development of a standardized,
automated FRS that will provide a
general ledger accounting system
and financial accounting reports.
The FRS is to be compatible with
the existing CAS and to interface
with it. Together the FRS and the
CAS are to form a complete,
automated financial accounting
system with broad application to
the water supply industry.
2. Development of the CAS and the
FRS as modular systems so that
utilities can use either one inde-
pendently or together as a total
financial accounting system.
3. Verification of the satisfactory
operation of the FRS as an inde-
pendent subsystem through prac-
tical applications at KCWD.
Description of the Kent on
County Utility
The KCWD was the first water district
established in the State of Kentucky and
originally developed its water supply
system by purchasing the Dixie Highway
Water Company system in 1926. Before
that date, treated water was being pur-
chased from the City of Covington, Ken-
tucky, and delivered by the KCWD to its
customers.
In 1954, the KCWD built a new water
intake on the left bank of the Licking River
approximately 4 3/4 miles upstream
from the Ohio River. A treatment plant
was also built at Grant and Howard
Avenues in Taylor Mill, Kentucky. The
treatment plant presently contains eight
mixed-media filters and has a rated
capacity of 12.0 mgd. After being treated,
the raw water flows into a clearwell with
a capacity of approximately 1 mil gal. The
treated water is pumped into the distribu-
tion system by two primary pumping
stations—one at the treatment plant and
one at Dudley Pike. The distribution
system contains approximately 160 miles
of pipe and consists of three pressure
levels. The first pressure level (a 5.0 mgd
storage tank) is filled directly from the
treatment facility or by the pumping
capacity at the treatment plant. The
second pressure level (which provides
water to the largest service area) is sup-
plied by gravity from three elevated
storage tanks or by the capacity of the
main booster pump station at Dudley
Pike. The third pressure level is fed by
gravity from two elevated storage tanks in
the Northern Kentucky Industrial Foun-
dation Park and the City of Florence, or by
the two booster stations on Turkey Foot
Road at Lafayette Avenue.
In July 1977, the KCWD acquired
operating control, and about 1 1/2 years
later, it merged with the City of Covington,
Kentucky, Water Utility to form the KCWD
system. In addition to the Covington
utility service area, the KCWD provides
water to about 15,600 customers of the
northwestern portion of Kenton County,
Kentucky, in a 40-square-mile retail
service area (Figure 2). Treated water is
supplied to 12 townships, an industrial
park, and unincorporated areas of Kenton
County lying within the service area. In
addition, water is supplied to the City of
Florence and to the International Airport.
The map in Figure 2 shows the KCWD
service (excluding Covington) and the
locations and relative sizes of the three
original pressure zones.
As with most government accounting
systems, the KCWD system is operated on
a fund basis (defined as a group of
accounts, including assets, liabilities, and
balance accounts, that are isolated to
comply with a specific objective or to
specify a special purpose of the activities
associated with the accounts). The funds
established by the KCWD are categorized
as general, operating and maintenance,
bond, depreciation, and plant funds.
The general fund consists of the
accounts that are general in nature and
not applicable to the other specific funds.
At KCWD, revenues are initially recorded
within the general fund and then are
transferred to other individual funds as
needed to provide for the expenses or
appropriations required of the other
specific funds. The operating and main-
tenance fund consists of only those ac-
counts that reflect the operating and
maintenance activities of the utility. The
bond, depreciation, and plant funds, like
the operating and maintenance fund,
consist of groups of accounts that identify
activities associated with the funds.
Other funds may be established as the
need arises to isolate a certain account-
ing activity.
The KCWD chart of accounts, like those
of many other water supply utilities, was
originally developed by the National
-------
I Budget Data
I Depreciation Data
I Revenue Producing Water Data\
Total Costs Incurred for Period
*1
h-
Sources of
Cost Data
(Monthly)
Cost At
Syi
(Autt
»
'counting
'tern
imated)
Cost 1
Accounting\
Reports \
IjJ^SS!
Expenditures Incurred for
Period (i.e.. Purchases for
Inventory, Plant & Equipment
Prepaid Expenses)
Liability Transactions
(i.e.. Moneys Received from
and Repayment to Debt
Obligations)
Revenue Transactions
(i.e.. Billings to and
Collection from Water
Customers and Others)
Sources of Data
on Expenditures
Sources of Data
on Liabilities —
Payments and
Receipts
Sources of Data
on Revenue —
Billings and
Collections
1
^ Gener
Ace
al Ledger
ounting
1*. (Manual)
1
»
»
Payroll I
Records [
Inventory
Records
^ _^~~
General
Ledger
Sales and
Accounts
Receivable
Records
and Reports
L ^x — '
Trial
Balance
^jX**1^^^
Figure 1. Financial accounting system used by KCWD before development of the financial reporting system.
Association of Regulatory Utility Com-
missioners (NARUC). According to them,
the KCWD is a Class A water utility
(having annual operating revenues of
$500,000 or more) and therefore uses
the chart of accounts for Class A utilities.
For further description or information
concerning the NARUC/KCWD chart of
accounts, refer to the National Associa-
tion of Regulatory Utility Commissioners -
Uniform System of Accounts for Water
Utilities (National Association of Regula-
tory Utility Commissioners, Washington,
DC, 1977).
As transactions occur each month,
various source documents are routinely
prepared to record information concerning
the transaction. On these source docu-
ments are coded with the account
numbers, cost center numbers, amounts,
and any other descriptive data important
in identifying the transactions. Since the
source document is the authority from
which the accounting entry originates,
recording of complete accounting infor-
mation is imperative.
From the originating documents,
accounting entries are summed each
month by account number and then
recorded into the general and subsidiary
ledgers within each applicable fund. After
all entries have been posted to the
general ledger, a total is calculated by
account, and the accounts in each fund
are balanced. At this point, a preliminary
trial balance is prepared until the end of
the fiscal year. Thus each year, after year-
end adjustments and closing entries have
been recorded in the general ledger, the
accounting books are closed and a trial
balance, statement of revenue and
expenditures, and balance sheet are
manually prepared.
Accounting Considerations
The function of accounting is to provide
quantitative financial information useful
for economic decision making. The
products of an accounting operation are
financial statements or reports. The
financial reports may vary because of the
way in which financial accounting infor-
mation is listed, classified, recorded,
summarized, or reported to the person
who will make use of the information; but
they normally are produced in a standard,
generally accepted format. The financial
reports provide information on current
and past events and thus can help
management evaluate earlier decisions
and provide a starting point for producing
future conditions or changes. In addition
to being useful for assessment of current
and future financial needs, the information
may be important for policy setting. In
essence, financial reports are normally
designed to provide a manager with some
of the information required for many of his
decisions (operational as well as financial)
about the use and stewardship of
resources. Adequate financial reporting
is important because the utility has an
economic obligation and responsibility to
manage its resources efficiently and
provide the services required by its
customers.
All water utilities are concerned with
two basic types of financial reports:
external and internal. The external
financial reports are necessary to meet
the requirements of various interested
parties within the management or
regulatory area of the utility. Some users
of the external financial reports (such as
the State Public Service Commissions)
are interested because they are charged
with the responsibility of protecting
customers who have a direct interest. In
the case of the KCWD, the Kentucky
-------
• Booster Station
* Storage Facilities
Treatment Plant
River Intake
— Major Pipelines
Figure 2. Kenton County Water District No. 1 service area.
Public Service Commission uses the ex-
ternal financial reports of the utility to
determine the reasonableness of the rate
of return from their sale of water. An
example of an internal report is a trial
balance report; examples of external
reports are the balance sheet and the
statement of revenue and expenditures.
As the KCWD manual financial reporting
system was being adapted to the com-
puterized system, careful attention was
given to the accounting practices so that
they would conform to the guidelines set
forth by Governmental Accounting,
Auditing, and Financial Reporting (Na-
tional Committee on Government Ac-
counting, Municipal Finance Officers
Association of the United States and
Canada, Chicago, IL, 1978).
Internal Reports
To produce internal financial reports,
transactions are entered into the com-
puter system from various source docu-
ments, creating a general ledger data file.
Figure 3 is a simplified flow schematic of
the FRS. The same six types of source
documents on which financial data were
recorded for the KCWD manual accounting
system are also used in the FRS. These
source documents include the payroll
time card, miscellaneous transaction
form, cashier's daily report, service
contract form, requisition from stock
form, and journal authorization form.
From the source documents, data are
keyed into the computer.
To verify that source document data are
entered into the computer correctly, edit
reports for each type of source document
are produced. The payroll edit report lists
the date of the report, the employee social
security number, the date of the pay
period, the account number to which his
labor was charged, the cost center or
location in which his labor was charged,
the cost center or location in which the
work was performed, the total cost by
account, and balancing features of the
edit routine, including comments for
error conditions.
Edit reports are produced from the
source data by computer programs in a
similar process. By matching the edit
reports with the source documents, the
flow of data can be examined. Since the
computer program does as much as
possible, every transaction is not appa-
rent but follows a similar logic to the
payroll edit report. For details, refer to the
computer program listings in Volume II of
the full report. These describe the action
of each specific data element.
After all the source documents are
recorded and entered into the computer
system, edit listings and a trial balance
sheet are produced. The trial balance
report is printed in account number order
within each fund. The report provides the
following information: account descrip-
tion (along with the account number), the
beginning amount within each account
as of the beginning of the accounting
period, the current amount charged to the
account during the current period, and
the ending amount (which is a total of the
beginning balance and the current
amount). The beginning amount of the
trial balance is the ending amount of the
previous trial balance and is produced
from retained information. The current
amount is the summation of all expenses
from the source data (edit reports) for
each account number. Thus, the audit
trial between the trial balance and the
edit reports is the summation of individual
accounts. Assuming that the beginning
amount on the trial balance is accurate,
an out-of-the-balance condition on a new
trial balance would most likely be caused
by an error in the source data. Thus the
trial balance report and the edit reports
are used to detect errors before they are
processed by the accounting system. The
trial balance report may be run as many
times as necessary to produce valid data.
Once the data are determined to be valid
and each account is in balance by fund, a
-------
Journal
Authorization
Form
Requisition
from Stock
Form
Miscellaneous
Transaction
Form
Other Reports
(i.e.. Cost
Analysis Reports)
and program listings) are described in
Volume II of the full report.
Conclusions
The FRS was able to satisfy the KCWD
financial reporting requirements with
information furnished monthly. The
system has met the basic requirements
and is considered a proven system
because it has:
• Produced parallel reports with
KCWD's manual system,
• Produced more than 24 sets of
monthly reports,
• Produced reports for two annual
closings,
• Provided the financial data to the
Kentucky Utility Regulatory Com-
mission for rate adjustments,
• Been examined and accepted by
interveners at a rate adjustment,
and
• Provided the financial data for a
major bond issue.
The full report was submitted in
fulfillment of Cooperative Agreement No.
CR806448-01 by Kenton County, Ken-
tucky, Water District No. 1 under the
sponsorship of the U.S. Environmental
Protection Agency.
Figure 3. Simplified flow schematic of the FRS.
final trial balance for the current period is
produced.
External Reports
After the final trial balance is completed,
the combined fund balance sheet and the
statement of revenue and expenditure
reports are produced. These reports are
generated from information contained in
the trial balance, plus data retained from
the previous processing. As in the trial
balance, these reports are developed by
mbining data in a predetermined way
to produce the desired specialized
reports.
Computer Programs
All of the computer programs are
written in American Standard COBOL to
operate in the IBM 370 environment.
The computer language and hardware
were selected because of their widespread
use.
The computer operations and programs
(including operating instructions, data
format, file layout, program flowdiagrams.
-------
Victor C. Fender is with the Kenton County, Kentucky. Water District, Ft. Mitchell,
KY 41017; and James I. Gillean and William L Britton, Jr., are with ACT
Systems. Inc., Winter Park, FL 32789
Robert M. Clark is the EPA Project Officer (see below).
The complete report consists of two volumes entitled "Development and
Application of a Water Supply Financial Reporting System":
Volume I (Order No. PB 83-249 417; Cost: $ 10.00, subject to change)
Volume II (Order No. PB 83-249 425; Cost: $20.50, subject to change)
The above report will be available only from:
National Technical Information Service
5285 Port Royal Road
Springfield, VA 22161
Telephone: 703-487-4650
The EPA Project Officer can be contacted at:
Municipal Environmental Research Laboratory
U.S. Environmental Protection Agency
Cincinnati, OH 45268
United States
Environmental Protection
Agency
Center for Environmental Research
Information
Cincinnati OH 45268
Postage and
Fees Paid
Environmental
Protection
Agency
EPA 335
Official Business
Penalty for Private Use $300
RETURN POSTAGE GUARANTEED
Third-Class
Bulk Rate
------- |