United States Environmental Protection Agency Municipal Environmental Research O Laboratory <*. Cincinnati OH 45268 Research and Development EPA-600/S2-84-158 Nov. 1984 &ER& Project Summary City of Tampa: Management Analysis and Report System (MARS) David L. Tippin, Elton Smith, James I. Gillean, and Robert M. Clark This three-volume report describes the development and implementation of a management analysis and report system (MARS) in the Tampa, Florida, Water and Sanitary Sewer Depart- ments. Original system development was based on research conducted in a smaller water utility in Kenton County, Kentucky. MARS will help both the Water and Sanitary Sewer Departments control costs and manage expanding service requirements more effectively and efficiently. The objectives of MARS were (1) to provide computer programs that pro- duce a MARS (compatible with the original research) for Tampa's Water and Sanitary Sewer Department; (2) to investigate the problems of scaling up from a small to a large system, including the problems of interfacing utility opera- tions with a city financial reporting system; (3) to provide the mechanisms for establishing a self-sustained finan- cial base for a wastewater utility (the water department of Tampa is already an enterprise system); (4) to convert the originally developed programs from an IBM 370* to a small stand-alone com- puter used only by the Water and Sewer Departments; and (5) to verify the system's satisfactory operation through practical application in Tampa. The computer programming was ac- complished with American Standard COBOL operating on a Prime Computer. This combination was chosen because of its widespread use and stand-alone capability. •Mention of trade names or commercial products does not constitute endorsement or recommenda- tion for use. MARS was able to meet project objectives and satisfy the reporting requirements of the Tampa Water and Sanitary Sewer Departments with in- formation furnished monthly. The sys- tem is considered proven because it has produced reports as required in a stand- alone environment. However, substan- tial difficulties were encountered in interfacing MARS with the city's finan- cial reporting system (FIN). This Project Summary was developed by EPA's Municipal Environmental Re- search Laboratory, Cincinnati. OH, to announce key findings of the research project that is fully documented in < separata report of the same title (see Project Report ordering information at back). Introduction The Tampa Water and Sanitary Sewer Departments have developed and imple- mented a management analysis and report system (MARS) designed to help control costs and manage expanding service requirements. MARS is based on research conducted at the Kenton County (Kentucky) Water District No. 1 (KCWD)in which a cost analysis system (CAS) and a financial report system (FRS) were suc- cessfully developed and implemented. This report discusses xhe 2-year research effort—scaling up the cost and financial reporting systems to the Tampa Water Department and extending the concept to the Tampa Sewer Department. A key feature of this research was modification of the programs, originally developed to run on a large IBM mainframe computer in a time-sharing environment, to fit on a stand-alone computer (Prime). Volume I ------- is a general description of the project and provides background conditions and re- sults. Volume II is an operations manual, and Volume III is a programming manual. A prime feature of the Safe Drinking Water Act of 1974(PL-93-523) is that the economics and cost of water supply and delivery must be considered before regu- lations are promulgated. In a 1974 study of water utilities, data were collected so that fundamental factors that affect the costs of water supply and utility man- agement could be compared. After ana- lyzing the data flow and information transfers from a number of large and small utilities, a technique, based on a matrix concept, was developed and tested by means of a water supply simulation model developed by the EPA Drinking Water Research Division in Cincinnati. This data collection and analysis tech- nique was further refined at KCWD (Development and Application of a Water Supply Cost Analysis System, J. I. Gillean, W. L. Britton, Jr., J. H. Brim, and R. M. Clark, EPA-600/2-80-012a and 012b, U.S. Environmental Protection Agency, Cincinnati, Ohio 45268). Because of the success at the KCWD, EPA decided to verify the system's use- fulness at a larger water utility, and Tampa was selected as the site. The Tampa Water Department had partici- pated in EPA's 1974 cost studies, was aware of the successful application of the CAS at the KCWD, had recently expanded its treatment facilities, and was looking for a tool to assist in controlling costs. The Sanitary Sewer Department had also expanded, andtheiroperational costs had increased. Sanitary Sewer Department The Tampa Sanitary Sewer Depart- ment, with 300-plus full-time employees, provides wastewater collection and treat- ment to persons and industries in the city and in nearby unincorporated areas. A system includes 130 pump stations and ejector stations drawing sewage from the service area; more than 1400 miles of 12-to60-in.-diameter collection and trans- mission lines for the gravity system, a number of 4-in. force mains; metering at major interceptors or pumping stations and at the intake junction to the waste- water treatment facilities. Generally, Tampa wastewater is pro- cessed through the Hooker's Point treat- ment plant with some flow receiving various levels of treatment at intermedi- ate locations. Three Tampa users have pretreatment facilities: the Woodcrest, Juanita, and National Gypsum plants. Several residential developments have package plants serving a treatment func- tion. Water Department The self-supporting Tampa Water Department, with about 250 full-time employees, supplies more than 419,500 people through 100,000 metered .ac- counts (both residential and industrial) within an 84-square-mile service area. The Hillsborough River is the main raw water source (30 to 100 MGD). The main treatment facility, on the river, can treat a maximum of 100 MGD during periods of hard water/low coloration and a mini- mum of 75 MGD during periods of relatively soft water/high coloration. To serve a growing population, the city completed the Morris Bridge treatment plant and constructed 29 wells (500-ft- deep) each producing a maximum of 2 MGD. This facility, with a maximum treatment capacity of 40 MGD, provides water during high-demand periods. The Matrix Concept Regardless of physical structure, all water utility systems can be divided into the functions of acquisition, treatment, and delivery (Figure 1). For example, a well or a well field, rather than a reservoir, could be the source of raw water. Expan- sion of this concept allows costs to be identified both for specific functions (acquisition, treatment, and delivery) and for subf unctions. An acquisition subf unc- tion might include source of supply, reservoir, pumps, transmission lines; a delivery subfunction, pumps, storage tanks, transmission, distribution. By properly identifying these sublevels (including every part of the physical system with no overlapping areas) and by allocating costs to them, the complete physical utility becomes a group of cost centers. The cost center can be as small or as large as the utility wishes. As the water moves from its source to its desti- nation, costs can be allocated to the water. In Figure 1, for example, acquisi- tion consists of all costs for acquiring and moving raw water to the treatment plant. When the water is treated, it is assigned a treatment cost. The cost of treating the water can be added to the acquisition cost, which will be the cost for producing water at that point. Cost is also incurred by additional pumping within the treat- ment plant to move the water into the delivery area. Even though the pumping unit in the example is at the treatment plant, its purpose is to move water into the delivery area and is therefore con- sidered a subfunction of delivery. Trans- mission and distribution costs are accu- mulated in each zone. The transmission costs are those for moving water to zones 1 and 2. Distribution costs include those for the network of mains and laterals that deliver water to customers within zones. An analysis of-fhe system in Figure 1 reveals that part of the cost for pump No. 2 is allocated to move water through zone 1 to zone 2, and another portion of the cost is incurred in delivering water through the laterals to customers in zone 1. Similarly, the transmission mains include both a transmission cost element and a delivery cost element. Storage costs are assigned only to the zone in wh ich the storage facility is located because no element of this cost is applied to any other zone. Two types of costs were identified as the technique was refined. One type was associated directly with the operating functions of acquisition, treatment, and delivery, whereas the other type could not be identified with any specific operating function. The latter nonoperating costs (which include billing, accounting man- agement, etc.) were placed in a separate category called support services, as shown in Figure 1. Within this general concept, two basic types of information are required. If one thinks of a matrix of information, then on the horizontal axis are listed the specific functions of acquisition, treatment, de- livery, and support services. These func- tions can be subdivided into the smallest component desired (for example, an indi- vidual pump), and they are related directly to the physical delivery system depicted in Figure 1. On the vertical axis of the matrix are the financial data that identify the cost items for operating the'utility— labor, power, etc., generally recorded by a chart of accounts. Documentation of costs in this format makes it possible to retrieve and display the various levels or types of information desired. For example, the labor costs for treating water can be determined by examining the first cost category listed within the treatment function. Output Reports The first step in developing MARS was to identify the desired outputs. Four levels of cost reports were designed to provide information for specific levels of man- agement, ranging from more detailed (for line supervision) to summary reports (for top administrators). ------- r Water Mains & Materials Support Service Costs, Interest Costs, and Tax Costs Figure 1. Simplified schematic of a typical water utility. The Level IV report was developed to provide detailed cost information. The Level III report relates the detailed cost by the general, standardized categories of acquisition, delivery, etc. The Level II report compares actual expenditures with budgeted amounts and with expenditures for the previous year. The Level I report summarizes the cost data and identifies the cost per million gallons (S/MG) of revenue producing water (RPW) (RPW is the amount of water from which revenue was derived) for the major cost centers and the total cost of the system. Costs for the previous month, the previous year, and the budgeted amounts are also compared on the Level I report. Cost Coding System The cost coding system includes the chart of account numbers for the financial reporting system and the special code required for MARS. Three groups of numbers are required. The first group designates the general assignment to the National Association of Regulatory Utility Commissions(NARUC) chart of accounts. The second group identifies the cost centers that generate the expense. The first digit of the cost center number identifies the function; the second, the subfunction; and the last two, the unit. The subfunction (second digit) and identi- fication (third and fourth digits) appear only in the Level IV report. The third group of numbers is a two- digit series corresponding to the 12 standard cost categories used in the Level III summary reports. Cost Allocation MARS can fulfill its function only when all cost data are charged to the appropriate cost centers. This task is done by identify- ing the correct cost center when the data are entered on the source document— that is, payroll time card, requisition from stock form, etc. Exceptions to this pro- cedure occur when the cost center infor- mation is not obtained or when a general cost cannot be identified to a specific center. To deal with such exceptions, an allocation concept assigns-costs to ap- propriate cost centers. MARS Application to Wastewater Conceptually, the adaptation of MARS to a wastewater operation proved no more difficult than its application to a water utility except for the initial definition of terms. As a water supply tool, MARS was designed to accumulate detailed cost into the four major functions of acquisi- tion, treatment, delivery, and support services. This structure of four major cost categories was maintained throughout the entire study for the water supply application. For wastewater application, these terms are not appropriate. For example, the basis for productivity in the water supply application is revenue- producing water; in wastewater, there is no equivalent value. Therefore in devel- oping the terms for wastewater utility functions, the following two groups were selected: A. Collection - All costs associated with moving wastewater to the treatment facility. Treatment-All costs associated with removing the impurities ------- from wastewater and with preparing the impuri- ties for disposal. Disposal - All costs associated with disposing of the liquids and solids after treat- ment. Support - All costs associated with Services support activities related tothe utility that are nota part of the above three areas. B. Collection - All costs associated with moving wastewater to the treatment facility. Treatment-All costs associated with Liquid the liquid portion of the treatment process where impurities are being re- moved. Solid - All costs associated with the solids portion of the treatment process where the impurities are being conditioned. Support - All costs associated with Services support activities related to the utility that are not a part of the above two areas. The differences in the liquid and solid treatments (Approach B above) are illus- trated in Figure 2, which depicts the wastewater treatment process in the Tampa plant. The processes in the vertical stack of boxes on the left side of Figure 2 describe the liquids treatment since they are all related to handling liquids and removing their impurities. The horizontal lines identifying function, such as sludge thickening and drying beds, are associ- ated with solids that have been removed from the liquid wastewater. With this method, definitive costs can be obtained from the different treatment processes and can be identified on all levels of reports as associated with liquid and/or solids treatment. The problem with this approach is the requirement for recording detailed source data. Record keeping may become difficult because two processes sometimes occur at the same physical location. Approach A has the advantage of being able to identify the treatment process to any detail desired(including separation of the liquids and solids) in Level IV of the MARS report. The only disadvantage is that it does not identify the liquids and solids cost in the higher level reports but accumulates them into a total treatment cost. Tampa Sanitary Sewer Department personnel preferred Approach A because they believed it provided more flexibility in identifying the treatment cost based on the process configurations most mean- ingful to them. Treated wastewater was selected as the expression for productivity (unit value) to be used in MARS since it represents the actual flow through the treatment facility. All other problems associated with MARS application to wastewater are similar to those that may be expected in application of MARS to any utility. For example, some of the output reports from the sewer and water departments were structured differently. The reporting differences reflected individual management philosophies as flaw Sewage Influent much as differences between water and wastewater operations. Tampa's Financial Reporting System (FIN) FIN was developed to streamline input requirements by using on-line terminals that validate and edit input data as they enter into the data base files. The ob- jective of FIN is to provide the detailed information required for the highest levels of management and budgeting. FIN is based on Florida's standardized system of accounts and each coding element has its unique meaning and identification within the city's financial system. The data for FIN originate from many different sources, such as the Purchasing Department. Figure 3 shows the general data flow through FIN and some of its Screening & Grit Removal Primary Sedimentation Tanks - -*. Anaerobic -j. S'ud- °*~~ ZZ Carbonaceous Sedimentation Tanks Nitrification Sedimentation Tanks Denitrification Filters Post Aeration & Chlorination n j (HAS) .1 (WAS) 1 Thickeners — Aerobic — j 1 Digesters 'JRASJ \(WASJ -I J ge >• Sludge '^ Removal Aluminate 1. Sludge __^ e Ly'?9 Remova Beds Pnfymor Figure 2. Tampa Bay Schematic of Hooker's Point Wastewater Treatment Plant in Tampa, Florida. Ft AS refers to recycled activated sludge; WAS, to waste activated sludge. 4 ------- (Source Data) Payroll Time Cards Equipment Usage Cards Stock Issues Purchase Requisitions Authority to Pay Forms P.O. Corrections Journal Entries Misc. Forms, i.e.. Central Garage Billing, Depreciation Entries, etc. - — Interface Programs * FIN Accounting System Programs (Output Reports) FGL 040-General Ledger Daily Activity Report FGL 042-General Ledger Account Balance Report FGL 042A -General Ledger A ct ivit y/ Object Summary FGL MSB-Fund, Dept., Object Code Weekly Expenditure Summary FGL M90-Fund/ Object Weekly Expenditures Summary FGL 440-General Ledger Monthly A ctivity Report FPL 040-Financial Statement CIP 040-Job/ltem Detail Transactions CIP 045-WO/Capital Improvements Recapitulation Summary Figure 3. General flow diagram for Tampa's financial reporting system (FIN). output reports. FIN coding uses a pyramid structure in which a transaction enters at the lowest level and is automatically accumulated through higher levels of activities. The information varies with the type of transaction, but all of the latter normally share the common key fields that interpret and categorize the data. These fields create the account number and consist of the fund, department, etc. The system enables management to identify and accumulate data on like items from all budgeting elements for the fiscal cycle. Management in turn can solicit bids valid for specific periods, establish firm prices, and identify individual vendors for various purchases. The overall concept of FIN is to establish a detailed data base on a daily basis. thereby creating a voluminous and com- plex system. Within FIN, the budget is developed to the lowest level of a product code. For example, consider purchasing a 75W, inside frosted, 130V incandescent lamp. If this item is budgeted but the department decides later to purchase a clear lamp rather than a frosted one, a budget change is required. The system may thus require numerous budget changes each month. FIN was primarily developed from the standpoint of accounting and fiscal man- agement, not from that of an operational department. Such an overall approach may be the best for the city, but a system developed in this manner generally lacks the type of information useful to opera- tional management. For example, the major systems developed for FIN include revenue and cash management, expendi- ture management (accounts payable), purchasing, inventory management, and fixed assets. All of these systems are certainly important to a department, but they do not include cost-center informa- tion for managing departmental opera- tions. FIN's original development included coding flexibility at departmental levels using the job/item fields. However, to make proper use of this flexibility, the department needs an in-depth under- standing of FIN. Without major modifica- tions, it would be difficult to obtain from FIN much of the information needed for day-to-day management of a water and wastewater department. ------- Computer Selection A computer selection committee was appointed consisting of a consultant and a representative from the Sanitary Sewer Department, the Water Department, and the Management and Information System Department (city computer center). The objective of the committee was to identify for Tampa's Water and Sanitary Sewer Departments the best computer hardware and operational software to support both the MARS project and other applications within these departments. The Prime computer was selected based on its reliable and proven software—an especi- ally important factor in the development of new programs such as MARS. MARS Report The MARS reports are specifically designed for top utility management. They contain operating and maintenance cost data summarized into major opera- tional categories along with additional information useful in evaluating the operations of the entire utility. An example of a Level I report for the Water Department appears in the com- puter print-out shown in Figure 4. The first five rows contain cost data expressed on a productivity basis with dollars per million gallons of water ($/MG) for the: (1) current month, current year, (2) pre- vious month, current year, (3) current month, previous year, (4) year-to-date, current year, and (5) year-to-date, pre- vious year. These unit costs may be compared to facilitate a trend analysis. The next two rows contain the total costs for the current month and year-to- date, and the last two rows provide a percentage of total cost for each function for the current year-to-date and the previous year-to-date. This information provides an indication of shifts in cost and/or trends in areas where expenses are changing. Production unit cost information is pre- sented at the bottom of the page, along with percent of budget funds remaining. The production unit for the Water Depart- ment is classified as "revenue-producing MONTH ENDINO: JAN 31. 1982 SUMMARY OF SYSTEMS INFORMATION FOR TAMPA MATER DEPARTMENT (LEVEL. I) PAGE: CATEOORV ACQUISITION PURIFICATION DELIVERY SUPPORT •****•• DEBT EXPENSE DEPRECIATION TOTAL UNIT COST «/MO CURRENT MONTH • PREVIOUS MONTH CURRENT MONTH PREVIOUS YEAR CURRENT YEAR (VTD) PREVIOUS YEAR (VTD) TOTAL COST CURRENT MONTH « YEAR TO DATE PERCENTAGE OF TOTAL CURRENT YEAN (YTD) PREVIOUS YEAR (YTDI 9.21 » 7.28 B. 96 124 97 • 159. 23 143. 64 228. 16 • 173. 07 22O. 46 202.83 • 70. 62 96.69 138. 67 • 137. 93 164. O9 101. 74 • 96. O6 96.33 823. 60 666. 21 730. 37 13.312.49 • 207. BIB. 07 • 379.413.33 * 337.328.68 • 263. B6O. 13 • 169.176.94 « 1372.911.68 6O. 43O. 23 1. 23X 968. 306. 22 19. 67X I486. 111. 17 30. 1BX 631.783. 36 13. 24X 1106. 1O6. 74 22. 47X 630.723. 91 13. 22X 4923. 463. 83 10O. OOX TREATED UATER (MO) REVENUE PRODUCING WATER (MO) CURRENT MONTH 16B7. 431 YEAR TO DATE 734O. 196 PREVIOUS Y-T-D 7261, 631 »••****•*»*•»••**• CURRENT MONTH YEAR TO DATE PREVIOUS Y-T-D 1662. 910 474O. 982 6894. 831 UNACCOUNTED FOR UATER (MO) CURRENT MONTH 24. 321 YEAR TO DATE 799.214 BUDOET REMAINING (X) YEAR TO DATE 6*. 10 Figure 4. Level I status report for Tampa Water Department. 6 ------- water," (the Sanitary Sewer Department uses "Billable Q," or billed sewer line flow into the treatment facility). Conclusions The following conclusions are based on the MARS experience in Tampa. 1. MARS was less adaptable to the Tampa utilities than anticipated, for the following reasons: a. MARS was originally developed using the KCWD and other wate utilities as models, but FIN was so inflexible that the original MARS and some basic developmental concepts had to be modified to function with FIN. b. Because of the size of the Tampa utility's operation, significantly more data entered this system than in the smaller KCWD. Con- sequently, MARS required larger system files for data processing and storage, along with different data handling techniques. c. Modifying the original computer programs from a batch mode on the large main-frame computer to an automatic interactive mode on a small computer proved difficult. These modifications combined with conceptual program changes required careful planning and considerable additional program- ming effort. 2. The MARS concepts are transfer- able, as demonstrated by adapting the original four levels of the KCWD reports to both the Water and Sanitary Sewer Departments in the same general format. 3. Adapting MARS to the Tampa wastewater operation was no more difficult than applying it to the Tampa water utility. The Tampa application disclosed enough differ- ences between water and waste- water operations to merit water- unique and wastewater-unique programs in future applications. If the water and wastewater opera- tions can be integrated under a single fiscal manager, it may be possible to use one group of pro- grams. 4. The use of Prime 250 or equivalent computer hardware for the MARS application is evidenced by success- ful operation on the Prime com- puter. Since a financial reporting system already existed in Tampa, MARS consisted only of the cost analysis and management informa- tion module. The processing time on the computer will probably require approximately 1 or 2 days per month (if monthly reports are required), leaving the computer idle for the remainder of the month except as a means of data storage. However, both Tampa departments have numerous other computer programs that were intended to operate on the same computer system. Thus in a situation that requires only the cost analysis module of MARS, justifying the use of a small computer is questionable. A more reasonable option may be to operate this system on remote, time sharing hardware. 5. The ease or difficulty of adapting MARS to other water and waste- water utilities depends on site- specific conditions. Solving the MARS/FIN interface at Tampa con- sumed more time than was origin- ally anticipated. 6. MARS assisted in detecting data errors that existed in FIN. The MARS programs edit each FIN expense item to ensure that the code is correct with respect to the require- ments of MARS. If the code is erroneous, the item is listed for correction. To maintain a balance, data corrected in MARS must also be corrected in the original system (FIN) that feeds data into MARS. 7. The value of MARS for Tampa Water and Sanitary Sewer Departments has not yet been completely evalu- ated because the MARS reports have been available for only a short period. The full report was submitted in fulfill- ment of Cooperative Agreement CR- 807440 by the City of Tampa Water and Sanitary Sewer Departments under the sponsorship of the U.S. Environmental Protection Agency. •OSGPOs 1984-559-111-10730 ------- David L. Tippin is with City of Tampa Water Department. Tampa. FL 33602; Elton Smith is with City of Tampa Sanitary Sewer Department, Tampa, FL 33602; James I. Gillean is with ACT Systems, Inc., Winter Park, FL 32289; and Robert M. Clark (also the EPA Project Officer, see below) is with Municipal Environmental Research Laboratory, Cincinnati, OH 45268. The complete report consists of three volumes entitled "City of Tampa: Manage- ment Analysis and Report System (MARS): "Volume I (Case Study)," (Order No. PB85-106 177; Cost: $14.50, subject to change). "Volume II (Operations Manual)," (Order No. PB 85-106 185; Cost: $14.50, subject to change). "Volume III (Programming Manual)." (Order No. PB 85-112 241; Cost: $46.00, subject to change). The above reports 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 BULK RATE POSTAGE & FEES P, EPA PERMIT No. G-35 Official Business Penalty for Private Use $300 5S 8W«teVIB>1 "*"" y'KUiSW'W' ------- |