Loading...
HomeMy WebLinkAbout09.b. Receive reporting impacts from implementation of new ERP and Chart of Accounts Page 1 of 31 Item 9.b. CENTRALSAN SANITARYJdf A- hom CENTRAL CONTRA COSTA , . September 3, 2020 TO: HONORABLE BOARD OF DIRECTORS FROM: KEVIN MIZUNO, FINANCE MANAGER REVIEWED BY: PHILIP LEIBER, DIRECTOR OF FINANCE AND ADMINISTRATION ROGER S. BAILEY GENERAL MANAGER SUBJECT: RECEIVE UPDATE ON REPORTING IMPACTS FROM IMPLEMENTATION OF NEW ENTERPRISE RESOURCE PLANNING (ERP) SYSTEMAND NEW CHART OF ACCOUNTS Background At the May 18, 2020 Finance Committee meeting, staff was asked to provide: (1) a reconciliation of the old chart of accounts to the new one and (2) an overview of the impacts of the change in the chart of accounts on financial reporting. n accordance with this request, on J my 21, 2020, staff presented to the Finance Committee the contents of this Board Memo as well as detailed indexes translating the old chart of accounts to the new one. While the Finance Committee found the old-to-new translation indexes to be informative, it was suggested that staff eliminate some of the attachments when presenting this topic to the full Board, as the level of detail is more appropriate for internal use by staff. Accordingly, this Board Memo focuses on providing an overview of the impacts of the change in the chart of accounts to financial reporting as well as an overview of the types of changes made to the chart of accounts and why they were made. The following attachments are included to help understand the new chart of accounts: Attachment 1 - New object class (expense type) segment chart of accounts Attachment 2 - New org unit segment chart of accounts Design and Implementation of New Chart of Accounts At the onset of the ERP implementation project over a year ago, Central San staff acknowledged the importance of reviewing the existing chart of accounts for suitability in a new state-of-the-art ERP. Prior to the selection of the ERP system (Oracle Cloud Fusion) and implementation consultant (Emtec), Central San contracted with the Government Finance Officer's Association (GFOA) and Rahban CPA& Consulting (Rahban)to assist in developing ERP system requirements as well as provide feedback on the existing chart of accounts used for many years in Central San's legacy ERP system. The existing chart of accounts in Central San's legacy HTE system has been in place since the early 1990s. September 3, 2020 Regular Board Meeting Agenda Packet- Page 126 of 172 Page 2 of 31 As described in a Best Practices article published by the GFOA in February 2019 (Attachment 3): "A chart of accounts, a listing of the accounts an organization uses to define each class of items for which money is spent or received, provides a uniform format that local governments can use for reporting and accounting. The chart also organizes finances and segregates expenditures, revenue, assets, and liabilities to provide a better understanding of the organization's financial picture. It is a resource that governments can use for budgeting, accounting, and financial reporting." Since there is no one right or statutorily required way to design a chart of accounts for a municipal utility such as Central San, creating an optimal chart of accounts is a challenging task requiring management judgment taking into consideration our industry (i.e. utilities, sanitation, governmental accounting, etc.), organizational structure (i.e. special district, size, departments, etc.), agency specific reporting needs, and accounting system specific factors. Further complicating this task is the fact that once a chart of accounts is in place as the backbone of an ERP system, significant changes to it are not viable. As outlined in the Attachment 3, the GFOA issued the following guidelines to assist governments in developing a new chart of accounts as part of an ERP system implementation: 1. Define Each Segment • Don't use one segment for multiple purposes • Each segment should have a strict definition 2. Start Over • Don't worry about mapping back to the existing chart • Reflect actual organizational structure • Avoid replicating a problematic structure 3. Start Simple and Build-Out Detail o Identify major categories within each segment first 4. Don't Store Unnecessary Data • Use sub-modules of system (i.e.AP,AR, Projects, etc.)to further define detail • Keep relatively simple if possible 5. Don't Repeat Ineffective Numbering or Accounts • Don't hold on to old taxonomy or vocabulary • End users will eventually learn the new chart of accounts despite an initial learning curve With these principles in mind, along with more specific recommendations and guidance from the GFOA and Rahban, Central San staff performed a detailed review of its existing chart of accounts and identified several deficiencies that could be addressed with modifications to the chart of accounts. Although the initial thinking was to implement critical changes while generally keeping the existing structure in place, ultimately, more significant changes were adopted. Minor changes included eliminating redundant accounts and creating some more granular accounts. More significant changes included restructuring the parent-child roll-up categories for the organization unit and objective class segments: • The old cost center and organization unit (dept-division) segments were consolidated into a single four-digit segment. Certain organization units were separated. The organization unit segment was re-engineered to reflect the current organizational chart structure to improve its usefulness for Managers responsible for overseeing divisional budgets. • The expense object classes (expense types such as travel, consulting, utilities)were entirely re- structured based on an illustrative example in the GFOA's Governmental Accounting,Auditing, and Financial Reporting book, which is referred to as the "blue book" by governmental accountants and is considered the most recognized and trusted non-authoritative literature in the governmental accounting field. Staff is confident that all significant changes made to the existing chart of accounts are supported by September 3, 2020 Regular Board Meeting Agenda Packet- Page 127 of 172 Page 3 of 31 GFOA best practices as well as recommendations from Rahban and will improve accounting, financial reporting, and budgeting capabilities at Central San. Reporting Impacts In regards to impacts on Central San's budget, as disclosed in the FY 2020-21 budget adopted on June 4, 2020, the budget book was prepared to reflect the updated and revamped chart of accounts designed by Central San as part of the Oracle Cloud Fusion ERP implementation project, scheduled to be in place during FY 2020-21. Accordingly, there are some differences in expenditure line items and organization unit groupings in this book as compared to previous budget books. As a result of these changes, to improve comparability, certain prior year expense categories have been restated in accordance with the new categories and groupings. The total budget figures for the given years remain the same, but expenditures by category may differ. I n addition to changes already visible in the FY 2020-21 adopted budget book, immediate impacts will be visible on the monthly financial reports prepared by the Finance Division and presented to the Finance Committee and Board, with the first impacted monthly report being for July 2020. The attached presentation (Attachment 4) provides an illustration of what the changes will be to the Operations and Maintenance (O&M) Fund using the financial results of a historical month (April 2020) comparing the old reporting method and the new reporting method. The changes to the O&M monthly financial reports are reflective of the new more logically structured parent-child roll-up categories for object classes (expense types) and organization units. As the new ERP's Projects Module was still undergoing final testing and configuration at the time this memo was posted with the agenda for public review, a sample of the Sewer Construction Fund's monthly financial report to be submitted to the Finance Committee and Board is not yet available. However, it is expected the new monthly projects financial report will remain largely the same as previous reports, as changes to the chart of accounts structure has minimal impacts on how capital project spending is tracked. Unlike the O&M fund where changes to object classes and organization units significantly impacts accounting and reporting, Sewer Construction Fund reports rely heavily on unique project identifications, project phases, and task codes which remain largely unchanged (apart from moving from four digit project codes to five digit codes to allow more projects to be maintained in the system without reusing codes). Strategic Plan Tie-In GOAL THREE:Fiscal Responsibility Strategy 2—Ensure integrity and transparency in financial management ATTACHMENTS: 1. New Chart of Accounts Object Class Codes (as of 6-30-20) 2. New Chart of Accounts Organization Unit Codes (as of 6-30-20) 3. GFOA Best Practice Guidelines 4. Presentation September 3, 2020 Regular Board Meeting Agenda Packet- Page 128 of 172 Page 4 of 31 Attachment 1 Central Contra Costa Sanitary District Master Chart of Accounts(as of June 30,2020) Object Class Codes(Old Element-Objects) Note:While financial reporting can be reported at the Grandparent or Parent levels,all transactions(including purchase orders and encumbrances)must be booked at the child account level. Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 10000 Cash And Cash Equivalents 10100 Cash And Cash Equivalents 10110 Cash Held With County Treasury 10111 Pooled Cash-(Wells Fargo) 10120 Petty Cash 10130 Payroll Clearing 10140 Cash on Hand 10200 Restricted Cash and Investments 10210 Restricted Cash and Investments 10220 Restricted Cash-Bond Proceeds 10230 Restricted Cash-Other 11000 Investments 11100 Unrestricted Investments,Current 11110 Short-term Investments 11200 Restricted Investments,Current 11210 Investments Held in Pension Trust 11220 Investments Held in OPEB Trust 12000 Receivables 12100 Accounts Receivable 12110 Accounts Receivable 12120 Overtime Inspection Receivable 12130 Accounts Receivable-Cobra 12140 Sewer Service Charge Direct Bill Receivable 12150 Loan Receivable-Computer Purchase Program 12151 Accrued Employee Training/Travel Advances 12160 Sewer Treatment Cost Sharing Receivable 12170 Household Hazardous Waste(HHW)Partner Receivable 12180 Lease Receivable-Current 12190 Deposit to Bank Escrow 12200 Interest Receivable 12210 Accrued Interest A/R 12300 Due from other funds 12310 Due from Other Funds 13000 Prepaid Assets 13100 Prepaid Assets 13110 Prepaid Insurance 13120 Prepaid Expenses 13130 Prepaid Retirement 13140 Prepaid Cafeteria Plan 13150 Prepaid Commuter Plan 14000 Supplies&Inventory 14100 Supplies&Inventory 14110 Supplies&Material Inventory Page 1 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 129 of 172 Page 5 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 15000 Non-Current Assets 15100 Cash and Investments 15110 Investments with Pension Trust 15120 Investments with OPEB Trust 15130 Cash with Fiscal Agent 15200 Long-term Receivables 15210 Alhambra Valley Assessment District(AVAD)Receivable 15220 Contractual Assessment District(CAD)Receivable 15230 Lease Receivable-Noncurrent 15300 Interfund Receivables 15310 Due From Other Funds-Noncurrent 15500 Capital Assets 15510 Land 15520 Permanent Easements 15531 Collection System 15532 Contributed Sewer Lines 15533 Pumping Stations 15540 Treatment Plant 15545 Outfall Sewers 15550 Recycled Water Infrastructure 15560 Buildings 15571 Vehicles 15572 Furniture&Equipment 15580 Enterprise Software 15590 Construction in Progress 15599 Construction in Progress 15600 Accumulated Depreciation 15660 Acc Depr-Buildings 15631 Acc Depr-Collection System 15632 Acc Depr-Contributed Sewer Lines 15633 Acc Depr-Pumping Stations 15640 Acc Depr-Treatment Plant 15645 Acc Depr-Outfall Sewers 15650 Acc Depr-Recycled Water 15671 Acc Depr-Vehicles 15672 Acc Depr-Furniture&Equipment 15680 Acc Depr-Enterprise Software 15700 Leased Assets 15750 Lease Asset-Facilities 15754 Lease Asset-Office Equipment 15800 Lease Amortization 15850 Acc Amortization-Lease Facilities 15854 Acc Amortization-Office Equipment 16000 Deferred Outflows of Resources 16100 Deferred Outflows Related to OPEB 16110 Deferred Outflows of Resources-OPEB 16200 Deferred Outflows Related to OPEB 16210 Deferred Outflows of Resources-Pension 16300 1 Deferred Outflows Related to Bonds 16310 1 Deferred Outflows of Resources-Bonds Page 2 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 130 of 172 Page 6 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 21000 Accounts Payable 21100 Accounts Payable 21110 Accounts Payable-Trade 21111 Accrued Liabilities-Services Received 21112 Accrued Liabilities-Inventory Received 21113 Accrued Liabilities-Procurement Cards 21114 Accrued Liabilities-Employee Reimbursements 21120 Sales Tax Payable 21130 Retention Payable 21140 Refundable Construction Bonds 21150 Accrued Liabilities-Year-End Close 21300 Due to Other Funds 21310 Due to Other Funds 22000 Salaries&Benefits Payable 22100 Wages Payable 22110 Salaries&Wages Payable 22200 Taxes Payable 22210 Federal Income Tax Withholding 22211 Medicare Withholding 22220 California Income Tax Withholding 22221 State Disability Withholding 22230 Other Payroll Withholding 22300 Benefits Payable 22310 Health Benefits Payable 22311 Dental Benefits Payable 22312 Vision Benefits Payable 22313 Disability Insurance Payable 22314 Life Insurance Payable 22315 Cafeteria Plan Payable 22316 Commuter Plan Payable 22317 Workers'Comp Insurance Payable 22320 Retirement Contribution Withholding 22321 ICMA Withholding 457/401A 22322 ICMA Withholding Tier 3 HRA 22330 Union Dues Withheld 22340 Wellness Payments 22350 Occupational Health Payable 23000 Other Current Liabilities 23100 Interest Payable 23110 Accrued Interest Payable 23120 Unearned Interest 23200 Provision for Uninsured Claims 23210 GASB 10 Actuarial Reserve 23300 Deposits Payable 23310 Refundable Deposits 23311 Misc. Deposits/Liabilities 23320 Reimbursement Fees Payable 23330 Permit Clearing 24000 Current Portion of Non Current Liabilities 24100 Compensated Absences Payable,Current 24110 Accrued Compensated Absences,Current 24200 Bonds Payable,Current 24210 Bonds Payable-Current Page 3 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 131 of 172 Page 7 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 25000 Noncurrent Liabilities 25100 Noncurrent Liabilities 25110 Net Pension Liability 25120 Net OPEB Liability 25130 Bonds Payable-Noncurrent 25131 Bond Discount 25132 Bond Premium 25140 Accrued Compensated Absences-Noncurrent 25300 Due to Other Funds-Noncurrent 25310 Due to Other Funds-Noncurrent 26000 Deferred Inflows of Resources 26100 Deferred Inflows 26110 Pension Related Deferred Inflows 26120 OPEB Related Deferred Inflows 26130 Bond Related Deferred Inflows 26140 Lease Related Deferred Inflows 30000 Equity 31000 Net Investment in Capital Assets 31110 Net Investment in Capital Assets-General 31120 Net Investment in Capital Assets-Contributed Capital 32000 Restricted Net Position 32110 Restricted for Debt Service 32120 Restricted for Capital Projects 32130 Restricted for Pension Obligations 33000 Unrestricted Net Position 33110 Undesignated Net Position 33120 Unrestricted Net Position-Current 33130 Unrestricted Net Position-Noncurrent 33140 Unrestricted Net Position Designated for Self Insurance 33150 Unrestricted Net Position Designated for Encumbrances 40000 Operating Revenues 40100 Sewer Service Charge Revenue 40110 Sewer Service Charge Counter 40120 Sewer Service Charge County 40130 Sewer Service Charge Direct 40140 Sewer Service Charge Prior Year 40200 Sewage Treatment Cost Sharing Revenue 40210 Sewage Treatment Cost Sharing Revenue(Concord/Clayton) 40300 Miscellaneous Service Charge Revenues 40310 Septic Tank Dumping 40320 Sales 40330 Other Service Charges 40340 Recycled Water 40350 Satellite Water Recycling Facility Reimbursement 40360 1 Stormwater Program 40370 1 Household Hazardous Waste Partner Cost Sharing Revenue Page 4 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 132 of 172 Page 8 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 41000 Non-Operating Revenues 41100 Grant Revenue 41110 State Grants 41120 Federal Grants 41200 Capacity Fees 41210 Facilities Capacity Fees 41220 Pump Zone Fees 41230 Capacity Use Charge 41300 Tax Revenue 41310 Secured Property Tax 41320 Unsecured Property Tax 41330 Homeowners Property Tax Relief 41340 Ad Valorem Taxes 41400 Permit and Inspection Fees 41410 Permit/Application Fees 41420 Main Lines Inspection Fees 41430 Plan Review 41440 Overtime Inspection 41450 TV Reinspection 41460 Side Sewer Inspection 41470 Industrial Permit Fees 41480 New Industry Permit Fees 41490 Annexation Charges 41500 Other Non Operating Income 41501 Pollution Prevention 41502 MVSD P2 Program 41503 Pretreatment Program Fine 41504 Rebates to District 41505 Other Agency Reimbursement-Misc. 41506 Other Government Revenue-Cal Trans 41507 Gain on Sale of Asset 41508 Lease Rental Income 41509 Miscellaneous Income 41510 Subrogation Recoveries 41511 Contribution From 0&M 41512 Insurance Recovery-HHW 41513 Right of Way Fees 41514 Partnering 41515 Reimbursements 41560 Contractual Assessment District(CAD)Reimbursements 41600 Interest Income 41610 Interest Income-General Investments 41620 Interest Income-Restricted Investments 41630 Pension Trust Interest Income 41640 Interest Rate Subsidy 41650 Change in Fair Value of Investments 41660 Change in Fair Value of Investments-Restricted 41670 Retention Account Interest Income 41700 Internal Service Charges 41710 Vehicle Replacement Charge Revenue 41720 Electricity Usage Internal Charge 41730 Recycled Water Internal Charge 41740 IT Replacement Charge Revenue 41750 Facility Replacement Charge 41760 1 Equipment Replacement Charge 41770 1 Worker's Comp Charges Page 5 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 133 of 172 Page 9 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 42000 Capital Contribution 42100 Capital Contribution 42110 Other Government Revenue-Concord 42120 State Revolving Fund Loan Proceeds 42130 Bond Proceeds 42140 Non-Exchange Capital Contribution/Donation 42150 Contribution in Aid of Construction 42160 Capital Recuperation-Connection Fees-Capital 42170 Capital Recuperation-Concord 42180 Capital Recuperation-Cal Trans 47000 Interfund Transfers In 47001 Transfers In 70000 Salaries&Wages 70100 FTE Regular Pay 70101 Management 70102 Non-Management 70103 Jury Duty Pay 70105 Capitalized Salaries 70200 Temporary Pay 70201 Salaries-District Temp 70202 Salaries-PT Regular 70203 Salaries-PT Seasonal/Hourly 70204 Interns Hourly 70400 Premium Pay 70401 Overtime 70402 Standby 70403 Accrued Compensated Absences Expense 70404 Compensated Absences Payouts 70405 Capitalized Overtime 70500 Board Fees 70501 Directors Fees&Expenses 70600 Other 70601 Salary Vacancy 70602 Recycled Water Transfer 70603 Severance Pay 70604 Special Duty Salaries Page 6 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 134 of 172 Page 10 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 71000 Employee Benefits 71100 Leave Accruals 71200 Insurance Premiums 71201 Medical&Health Insurance 71202 Dental Insurance 71203 Long-Term Disability Insurance 71204 Life Insurance 71205 Workers'Compensation Insurance 71206 Board Health Benefits 71207 Vision Insurance 71300 Employer Taxes 71301 State Unemployment Insurance 71302 Federal Medicare Tax 71400 Employer Retirement Contributions 71401 Pension Normal Cost Contribution 71402 Deferred Comp.Contrib 71500 OPEB Contributions 71501 OPEB Normal Cost-Health(ADC) 71502 OPEB Normal Cost-Dental(ADC) 71503 OPEB Normal Cost-Life(ADC) 71504 OPEB Normal Cost-Vision(ADC) 71600 Other Benefits 71601 Car Allowance 71602 Uniform Allowance 71603 Classification-Comp Study 71700 Benefit Adjustments 71701 Benefit Vacancy 71702 Capitalized Benefits 71703 Capitalized Admin.Overhead 71704 Recycled Water Benefit Transfer 72000 Retiree Costs 72100 Pension 72101 Additional UAAL Trust Contribution 72102 Pension Expense(GASB 68) 72103 Pension UAAL Contribution 72200 OPEB 72201 Additional UAAL Trust Contribution 72202 OPEB Expense(GASB 75) 72203 OPEB UAAL-Health(ADC) 72204 OPEB UAAL-Dental(ADC) 72205 OPEB UAAL-Life(ADC) 72206 OPEB UAAL-Vision(ADC) 72300 UAAL Vacancy 72301 Pension UAAL Vacancy 72302 OPEB UAAL Vacancy Page 7 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 135 of 172 Page 11 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 73000 Purchased Property Services 73100 Repairs&Maintenance 73110 Auto Damage Repair(Collision) 73111 Automotive Maintenance&Repairs 73112 Equipment Repairs&Maintenance 73120 Pipeline Maintenance 73121 Pump Repairs&Maintenance 73125 Patch Paving Repairs 73130 Building Repairs&Maintenance 73131 Painting Services 73132 Plumbing Maintenance 73133 HVAC Repairs&Maintenance 73134 Furnace Repairs&Maintenance 73135 Fire Alarm&Sprinkler Repairs&Maintenance 73136 Elevator Repairs&Maintenance 73137 Boiler Repairs&Maintenance 73138 Cogen Repairs&Maintenance 73140 Cloud Software as a Service(SaaS) 73141 Computer Repairs&Maintenance 73150 Landscaping Services 73151 Tree Trimming Services 73196 General Repairs 73197 Real Property Repairs/Landscaping 73198 Gen Equip And Mach Parts 73199 Maint&Repair-Non-Auto 73200 Hauling&Disposal 73201 Ash Removal 73202 Sludge Removal 73203 Grit Removal 73204 Spoils Removal 73205 Hazardous Waste Disposal 73206 Mobile Collection-HHW 73300 Security 73301 Security Guard Services 73302 Property Management Services 73303 Alarm Services 73304 Security Monitoring Service 73400 Rentals 73401 Equipment Rentals 73402 Rents&Leases 73500 Cleaning 73501 Janitorial Services 73502 Floor Cleaning 73503 Pest Control Services 73504 Uniform Service 73505 Garbage&Recycling Service Page 8 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 136 of 172 Page 12 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 73600 Construction 73601 General Construction 73602 Collection System Construction 73603 Treatment Plant Construction 73604 Recycled Water Plant Construction 73621 Grouting Services 73622 Metal Fabrication Services 73623 Patch Paving 73630 Right-Of-Way/Temp Easement 73640 Survey Monument Installation 74000 Purchased Professional,Technical&Other Services 74100 Professional Services 74110 Accounting/Audit Services 74120 Legal Services-Board Advice 74121 Legal Services-Staff Advice 74122 Legal Services-Board Litigation 74123 Legal Services-Staff Litigation 74130 Actuarial Services 74131 Appraisal Services 74140 Engineering Services-General 74141 Traffic Engineering Services 74142 Resident Engineering Services 74143 Value Engineering Services 74144 Structural Engineering Services 74145 Mechanical Engineering Services 74146 Construction Management Services 74198 Professional Services-General 74199 Consulting Services Page 9 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 137 of 172 Page 13 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 74200 Technical Services 74201 Reprographic Services 74202 Data Processing Services 74203 SCADA System Management Services 74204 Software Development Services 74205 Electrician Services 74206 Environmental Consulting Services 74207 Geophysical Data Acquisition Services 74208 Pipeline Consulting Services 74209 Air/Odor Consulting Services 74210 Geotechnical Services 74211 Land Surveying Services 74212 Traffic Control Services 74213 Potholing/Utility Locating Services 74214 Preconstruction Damage Analysis Services 74215 Biological Consulting Services 74216 CCTV Inspection Services 74217 Aerial Consulting Services 74218 Arborist Services 74219 Testing Services 74220 Contaminated Material Analysis Services 74221 Drafting Services 74222 Pavement Testing Survey Services 74223 Archeologist Services 74224 Vibration Analysis Services 74225 Landscape Architect Services 74226 Specialty Inspection Services 74227 Corrosion Consulting Services 74228 Inspection Services 74229 Noise Consultant Services 74230 Compaction Testing Services 74231 Construction Inspection Services 74232 Video Inspection Services 74233 CEQA Consulting 74234 Underground Service Alert Services 74235 Energy Consulting Services 74297 Structural 74298 Mechanical 74299 Other Technical Services Page 10 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 138 of 172 Page 14 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 74300 Other Purchased Services 74301 Public Agency Services 74302 Recruitment Services 74303 Temp Agency Staffing 74304 Advertising Services 74305 Advertising-Legal Services 74306 Marketing And Promotion Services 74307 Messenger Services/Express DE 74308 Interpretation Services 74309 Dispatch Services 74310 Real Estate Consulting Services 74311 Printing/Binding/Graphics Services 74312 Public Agency Fees 74313 Public Information Services 74314 Election Services 74315 Permits 74316 Public Notices 74317 Benefit Administration Services 74318 Classification-Comp Study 74319 Employment Screening Services 74320 Employee Wellness Services 74321 Labor Negotiation Services 74322 District Training Services 74323 Legislative&Advocacy Consulting 74324 Emergency Management Consulting 74399 Miscellaneous Other Purchased Services 75000 Supplies&Materials 75100 Utilities&Fuel 75110 Electrical 75120 Natural Gas 75130 Landfill Gas 75140 Water 75150 Telecomm-Landline 75151 Telecomm-Wireless 75160 Internet/Other Communications 75170 Fuel(Gasoline/Diesel) 75200 Chemicals 75201 Lime 75202 Polymer 75203 Boiler Chemicals 75204 Water Treatment Supplies 75205 Hypochlorite 75206 Sodium Hydroxide 75210 Other Chemicals Page 11 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 139 of 172 Page 15 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 75300 General Supplies 75301 Office Equipment/Supplies 75302 Furniture 75303 Postage&Parcel Delivery 75304 Coffee Supplies 75305 Other Operating Supplies 75306 Computer Equipment&Supplies 75307 Computer Software License 75308 Telecommunication Equipment 75309 Janitorial Supplies 75310 Laboratory Supplies 75311 Safety Supplies 75312 Inventory Adjustment-Other 75313 Inventory Transfer 75314 Mechanical Drafting Supplies 75315 Small Equip/Tools/Hardware 75316 Signage 75317 Plumbing/HVAC Supplies 75318 Plumbing/HVAC Equip 75319 Vehicle Repairs&Maintenance Supplies 75320 Vehicle Repairs&Maintenance Small Tools&Equipment 75330 Early Detection Overflow Meter Parts&Supplies 75340 Landscaping Supplies 75350 Construction Materials&Supplies 75399 Miscellaneous Materials&Supplies 76000 Other Expenses 76100 Insurance&Risk Management 76110 Self Insurance Losses 76111 Liability Insurance 76112 Property Insurance 76113 Insurance Premiums-Other 76120 Restoration Services 76121 Insurance Consulting Svcs 76122 Claims-Other 76123 Claims Adjustment 76124 Bad Debt Expenses 76150 Self Insurance Expense(Transfer) 76200 Memberships 76210 BACWA Expenses 76211 Outside Organization Fees 76220 Employee Memberships 76221 Certification&Licenses 76222 Subscriptions&Publications 76300 Training&Meetings 76301 Travel/Training/Conference(In-State) 76302 Travel/Training/Conference(Out-of-State) 76332 Mileage Reimbursement 76333 Travel-District Representation 76340 Tuition Reimbursement 76350 Professional Expense Reimbursement 76351 Food/Refreshments 76399 General Travel/Training/Conference Page 12 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 140 of 172 Page 16 of 31 Attachment 5 Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Object Class Description 76400 Miscellaneous Other 76401 Inventory Over/Short Adjustment 76402 Miscellaneous Expense 76403 Credit Card Fees 76404 Interest Disbursement-Deposits 76405 Disbursement Of Developer Refunds 76406 Investment Costs 76499 Purchase Requisition Clearing Account 77000 Interfund Transfers Out 77001 Transfers Out 78000 Debt Service 78100 Principal 78101 Principal Payment-Bonds 78102 Principal Payment-State Revolving Loan 78103 Principal Payment-Bank Loan 78200 Interest Expense 78201 Interest Expense Bonds 78202 Interest Expense State Revolving Loan 78203 Interest Expense Bank Loan 78205 Interest Expense-Lease Obligation Interest Expense-Deferred Amount On Refunding 78206 Amortization 78300 Other Debt Issuance Costs 78301 Financing/Debit Issue Costs 78302 Bond Redemption Transfer To Bond Payable 78900 Contra-Expense Principal Outlays 78999 Principal Expense Adjustment 79000 Capital Outlays 79100 Asset Purchases 79110 Equipment 79120 Vehicles 79130 Improvements-Non-Buildings 79140 Buildings Acquisition 79150 Buildings Improvements 79160 Land Acquisitions 79170 Permanent Easements 79190 Capital Outlay Transfer To Completed Fixed Assets 79200 Gain(Loss)On Sale 79201 Loss On Sale/Disposal Of Capital Asset 79300 Depreciation Expense 79301 Depreciation Expense 79302 Amortization Expense 79900 Contra-Expense Capital Outlays 79999 Capitalized Expense Adjustment Page 13 of 13 September 3, 2020 Regular Board Meeting Agenda Packet- Page 141 of 172 Page 17 of 31 Attachment 2 Central Contra Costa Sanitary District Master Chart of Accounts(as of June 30,2020) Organization Unit Codes(Old Dept-Division+Cost Center) Note:While financial reporting can be reported at the Great-grandparent,Grandparent,or Parent levels,all transactions(including purchase orders and encumbrances)must be booked at the child account level. Great-grandparent Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Roll-up Code Org Unit Description 1000 Administrative Services 1100 Executive Management 1110 1111 Office of the General Manager 1200 Office of Secretary of the District 1210 1211 Office of Secretary of the District 1300 Board of Directors 1310 1311 Board of Directors 1320 1321 Board Activities 1330 1331 1 Public Hearing program 2000 Administration Department 2100 Office of the Director of Finance and Administration 2110 2111 Office of the Director of Finance and Administration 2200 Finance Division 2210 2211 Finance Division General 2220 2221 Central Contra Costa Sanitary District Facilities Financing Authority(Authority) 2300 Human Resources Division 2310 2311 Human Resources General 2380 2381 Safety General 2400 Purchasing Division 2410 2411 Purchasing Division General 2500 Risk Management 2510 2511 Risk Management 2600 Communications&Intergovernmental Relations Division 2610 2611 Communications&Intergovernmental Relations General 2610 2612 Education&Outreach 2700 Information Technology Division 2710 2711 Information Technology Division General 3000 Engineering and Technical Services Department 3100 Office of the Director of Engineering&Technical Services 3110 3111 Office of the Director of Engineering&Technical Services 3120 3121 Resource Recovery/Program Management 3200 Planning&Developmental Services Division 3210 3211 Planning&Developmental Services General 3220 3221 Plannin &Applied Research 3220 3222 Collection System Planning 3220 3223 Treatment Plant Planning 3220 3224 Recycled Water Planning 3230 3231 Special Projects&Asset Management 3240 3241 Financial Planning 3250 Development Services Subdivision 3250 3251 Development Services Supervisor 3250 3252 Right of Way 3250 3253 Development Inspection 3250 3254 Permit Counter 3250 3255 Mainline Review 3260 3261 Real Property and Property Management 3300 Capital Projects Division 3310 3311 Capital Projects General Page 1 of 2 September 3, 2020 Regular Board Meeting Agenda Packet- Page 142 of 172 Page 18 of 31 Attachment 6 Great-grandparent Grandparent Parent Child Roll-up Code Roll-up Code Roll-up Code Roll-up Code Org Unit Description 3400 Environmental&Regulatory Compliance Division 3410 3411 Environmental&Regulatory Compliance General 3420 3421 Laboratory 3430 3431 Regulatory Compliance 3440 3441 Household Hazardous Waste 3450 3451 Environmental Compliance 3450 3452 Pollution Prevention 4000 Operations Department 4100 Office of the Director of Operations(Deputy General Manager) 4110 4111 Office of the Director of Operations(Deputy General Manager) 4200 Collection System Operations Division 4210 Collection System Operations Management General 4211 Collection System Operations Management General 4230 4231 Fleet Services 4240 4241 Tech Services CMMS 4250 4251 Site Planning Field Operations Subdivision 4220 4221 Field Operations 4220 4222 Construction 4220 4223 Rodding 4220 4224 CCTV/Locating 4220 4225 On-Call 4220 4226 1 Hydro 4300 Plant Operations Division 4310 4311 Plant Operations Management General 4320 4321 Process Control 4330 4331 Operations 4400 Plant Maintenance Division 4410 4411 Plant Maintenance Management General Maintenance Shop Subdivision 4420 4421 Pump Stations 4430 4431 Reliability Engineering 4440 4441 Maintenance Shop Superintendent 4440 1 4442 Mechanical Shop 4440 4443 Machine Shop 4440 4444 Electrical Shop 4440 4445 Instrument Shop 4440 4446 Buildings&Grounds 5000 Recycled Water 5100 Recycled Water 5110 5111 ReW Recycled Water General 5120 5121 ReW Water Exchanges 5200 Recycled Water Treatment Plant 5210 5211 ReW Treatment Plant Division General 5220 5221 ReW Increasing Recycled Water Supply 5300 Recycled Water Distribution System Division 5310 5311 ReW Distribution System General 5320 5321 ReW Expanding Recycled Water Availability 5400 Recycled Water Residential Fill Station Division 5410 5411 ReW Residential Fill Station Division General 5500 Recycled Water Satellite Water Rec .Facility Division 5510 1 5511 1 ReW Satellite Recycled Water Facility Division General Page 2 of 2 September 3, 2020 Regular Board Meeting Agenda Packet- Page 143 of 172 Page 19 of 31 Attachment 3 }Ft BEST PRACTICES Implementing a New ERP System? Take the Opportunity to Develop a New Chart of Accounts by Michael J. Mucha chart of accounts — a listing (ERP) implementation. For all projects, of the accounts an organiza- unless there are strict requirements that An up-to-date chart Ation uses to define each class a specific chart of account structure of items for which money is spent or be used, take the opportunity afforded of accounts helps the received — provides a uniform format by the ERP system implementation to that local governments can use for develop a new chart of accounts. organization view reporting and accounting. The chart 1. Define Each Segment. Each seg- also organizes finances and segregates ment of the chart of accounts should itself and it resources expenditures,revenue,assets,and liabili- ties to provide a better understanding have a strict definition that can be accurately, making service of the organization's financial picture.It communicated and enforced.This will is a resource that governments can use Prevent internal inconsistencies within and resource allocation for budgeting,accounting,and financial the chart of accounts. For example, reporting. the government should universally decisions easier. agree on what a "fund" is,what a Government agencies are not "department" is, and what a "program" required to have logical or even defined is, and not allow any exceptions. charts of accounts or accounting report- ing. Many states have suggested charts 2. Start Over.When developing the of accounts or even require that certain chart of accounts,start fresh and don't account ranges be used to report cer- initially worry about mapping back to tain funding. GFOA, the Association of the existing chart of accounts. Review Government Accountants, and other the actual organizational structure and professional associations strongly urge operations, and build the chart from governments to pay close attention to there to avoid replicating a problem- the structures used for financial report- atic chart of account structure. ing. The responsibility for identifying 3. Start Simple and Build-Out and managing this important tool lies Detail. Identify major categories with the organization's management, within each segment and then work just as the final responsibility for the to build out detail—again, this will financial audit's content also remains help the government take a fresh with management regardless of rules, perspective and prevent any unneces- regulations,guidelines, or suggestions. sary replication of the old chart. For example,when developing the object GUIDELINES code listing,start by identifying major GFOA has developed the following object code categories and then work guidelines to assist governments in to define detail to the extent neces- developing a new chart of accounts as sary.This will also help ensure that the part of an enterprise resource planning overall chart is organized. 28 Governmeg6baf6MudrYe�R�*egular Board Meeting Agenda Packet- Page 144 of 172 Page 20 of 31 Attachment 3 4. Don't Store Unnecessary Data. your government's chart of account If the chart of accounts is well devel- Most governments should segments using what is necessary oped, the government won't need to define a chart of accounts — remember, this structure should create new accounts very often. Most remain simple. organizations attempt to keep the with five major components: chart of accounts relatively simple fund, organizational unit, CONSIDERATIONS and at a high level, and then use other The common chart of account struc- components of the system (e.g., the program/activity, object/ ture defined above illustrates potential project ledger, accounts receivable account, and project. considerations and decision points that charge codes, salary pay codes,work will be needed within each segment. order detail) to further define the necessary for its tracking, managerial, After determining how many segments detail. This structure will lessen the amount maintenance required on and reporting needs. Exhibit 1 shows are most appropriate, the government potential options for each. will need to decide how many values the chart of accounts. The `Possible Segments" section of to include and how each should be 5. Don't Repeat Ineffective organized/numbered. Numbering or Accounts. Converting Exhibit 1 simply provides examples existing chart of account data or to choose from; most governments Funds. Governments often create reusing existing number schemes is won't need to use all of them. Define new funds in their charts of accounts not always beneficial. Many policies, processes, and systems will be chang- ing as part of training and change Exhibit I: Identifying and Defining Potential Segments management activities for the ERP Component Description Possible Segments implementation. Don't hold on to an Fund A fund is the self-balancing accounting • Fund old taxonomy or vocabulary with the unit required for govemmental accounting. chart of accounts. End users will even- Organizational Unit An organizational unit represents the • Department tually learn the new chart, and dealing organizational hierarchy reflected by an • Division with the initial learning curve is better organizational chart, listing of business units, • Business Unit or locations for which the govemment • Location than dealing with inefficiencies in the wants to track data. new chart of accounts for the length Program/Activity Programs (also commonly called activities) • Function of time the new ERP system is in use. are the services performed by organizational • Program units. Each program should have a service • Activity COMMON STRUCTURE outcome—the result produced.Typically • Sub-Activity programs are ongoing and not limited to Most governments should define a a specific organizational unit. chart of accounts with five major com- Object/Account The object or account is the classification • Object/Account ponents: fund, organizational unit, pro- of the balance sheet item, revenue, or gram/activity,object/account,and proj- expense. For expense and revenue,this ect. Each one will track a slightly differ- defines what was spent or eamed ent type of information, as described (e.g., supplies). Project Projects are often used to track programs • Project Roll Up by the blocks below. Doing so will with defined start and end dates or other • Additional also enable the government to track all events that would require additional detail segments defined required information for accounting, beyond the chart of accounts. Project as part of project/ budgeting, and financial reporting. costs would be summarized in this segment grant accounting but broken out in more detail in the project A government can have one or more ledger.This allows for detailed tracking segments within each major compo- by department. nent that defines the level of detail September 3, 2020 Regular Board Meeting Agenda Packet- Page 145 of 172 February 20191 Government Finance Review 29 Page 21 of 31 Attachment 3 as a way of tracking information in a be required. In Exhibit 3, all programs/ legacy financial system that lacks the Governments often create activities would be created under the flexibility to add segments and track new funds as a way of tracking example roll-up number by major func- projects,programs,or grants separately. tion. While this may be similar to orga- As a result, governments have long information in a legacy financial nizational unit or department, the gov- lists of "funds" that should not actu- system that lacks the flexibility ernment should avoid establishing a ally be tracked separately.GFOA's Fund to add segments and track 1:1 relationship between organizational Accounting Applications best practice projects, programs, or grants unit and program(e.g.,as not all police (available at gfoa.org) advises govern- programs are focused on public safety, ments to establish clear criteria for separately. and presumably the police is not the determining whether a given "fund" in only department that contributes to its accounting system should be treated organizations, one segment is probably public safety). as a fund for purposes of external finan- sufficient. Object. As stated previously, most cial reporting. The application of these Program/Activity. The previous governments should develop a new criteria to individual "funds" should be example wouldn't apply to programs object code listing and take this oppor- documented and periodically reviewed or activities. These programs wouldn't tunity to clear up and organize the to take changes in circumstances into usually be limited to one department; set of object codes used throughout account (e.g., a significant decrease in a different numbering method would the organization. Much like funds, a revenue source reported as a sepa- rate special revenue fund). Specifically consider whether the goals of general purpose external financial reporting Exhibit 2: Numbering Hierarchical Relationships with Prefix could better be achieved by combin- Numbers that Identify the Grouping ing similar "funds" in the accounting Department Department Division Division system into a single fund for financial Number Number reporting purposes. 10 Finance 1010 Accounting 1020 Purchasing Organizational Units. As stated 1030 Revenue above, many organizations create one 1040 Budget or more segments to refer to the orga- 20 Police 2010 Office of Chief nizational unit. These segments can 2020 Patrol include department, division (as a sub- 2030 Investigation2040 Jail section of a department), or any other vocabulary used to create the orga- nizational structure (typically super- visory and personnel reporting struc- Exhibit 3: Programs Numbered by Major Function tures). When developing the chart of accounts and defining segments, keep Example Number Description all information within a segment con- 10000 General Government sistent. Also, because each segment 20000 Public Safety code can only be used once, consider 30000 Transportation numbering hierarchical relationships 40000 Health and Welfare 50000 Recreation with prefix numbers that identify the 60000 Conservation and Planning grouping. For example, in Exhibit 2, 70000 Facilities all divisions belonging to the finance 80000 Business Enterprises department begin with 10. For smaller 30 Governmeg6baf6MudrYe�R�*egular Board Meeting Agenda Packet- Page 146 of 172 Page 22 of 31 Attachment 3 object codes tend to be created to that problem should be cleaned up. Additional detail can be defined within track projects, grants, and programs Exhibit 4 provides a structure for object each major category. that should be tracked in other seg- codes as an example. (This structure Project. The project segment often ments of the chart of accounts. Now is provided as an example only— the acts as an "optional' segment that that the government has the capa- government should develop its own allows the organization to track addi- bility to redo its chart of accounts, structure to meet its specific needs.) tional detail (in addition to the seg- Exhibit 4: An Example of a Structure for Object Codes Example Type Description Example Type Description Number Number 10000 Asset Assets 41900 Revenue Intergovernmental Revenue—State 10100 Asset Cash 42100 Revenue Intergovernmental Revenue— 10200 Asset Petty Cash County 10300 Asset Restricted Cash with Fiscal Agent 42200 Revenue Intergovernmental Revenue— 1 1500 Asset Investments Local Agency 12000 Asset Receivables 42300 Revenue Fines& Forfeiture 12200 Asset Short-Term Receivables 42400 Revenue License 12300 Asset Interest Receivables 42500 Revenue Permits 12400 Asset Due from other Governments 43000 Revenue Fees and Charges for Services 14000 Asset Due from other Funds 43400 Revenue Other Fees 15000 Asset Non-Current Asset 43500 Revenue Utility Fees 15100 Asset Long-Term Receivable 44000 Revenue Investment Income 15200 Asset Other Assets 45000 Revenue Internal Service Charges 15400 Asset Property Held for Resale 46000 Revenue Other Revenue 15500 Asset Capital Assets 49000 Revenue Other Resources—Operating 20000 Liability Liability Transfers In 21000 Liability Current Liability 50000 Expense Employee Services 21 100 Liability Accounts Payable 51000 Expense Salaries and Wages 21200 Liability Accrued Liability 51 100 Expense Salaries and Wages 21300 Liability Payroll Liability 51200 Expense Paid and Unpaid Leave 21400 Liability Other Current Liability 51300 Expense Premium Pay 22000 Liability Due to other Funds 55000 Expense Employee Benefits 25000 Liability Non-Current Liability 55100 Expense Employee Benefits 25100 Liability Non-Current Accrued Liability 55300 Expense Workers Compensation Premium 25200 Liability Other Non-Current Liability 60000 Expense Operating Expenditures 25300 Liability Deferred/Unearned Revenue 61000 Expense Maintenance and Utilities 25400 Liability Long-Term Debts 61 100 Expense Maintenance 25500 Liability Asset Forfeiture 61500 Expense Utilities 30000 Fund Balance Fund Balance 62000 Expense Supplies and Services 40000 Revenue Revenue 62100 Expense Supplies 41100 Revenue Property Taxes 62200 Expense Services 41200 Revenue Special Assessment Revenue 62300 Expense Travel and Education Expenditures 41300 Revenue Sales Taxes 62500 Expense Internal Service Charges 41400 Revenue Other Taxes 63000 Expense Debt Service 41600 Revenue Franchise Fees 70000 Expense Capital Expenditures 41800 Revenue Intergovemmental 90000 Other Other Resource Use Revenue—Federal September 3, 2020 Regular Board Meeting Agenda Packet- Page 147 of 172 February 20191 Government Finance Review 31 Page 23 of 31 Attachment 3 Exhibit 5: Additional Segments for More Detailed Tracking under Project Ledger ® ®® •. Project ments described above). It also allows accounts and being consistent with ■Unique department-by-department the government to use the "project led- the plan is key. tracking needs (project segment can ger," opening up additional segments typically be set differently for each The following common features may for more detailed tracking of and flex- project). be a good fit for the project segment ibility with expenses and revenues (see CONCLUSIONS the dark red segments in Exhibit 5). (and project ledger): Organizations that do not take advan- Use of the project segment does not ■Capital projects. tage of an ERP project as an opportu- need to be limited to capital projects, ■Operating projects with defined start nity to revise their chart of accounts although this will likely be one of the and end dates (e.g., grants). often limit the ERP system significantly, more common uses. The project led- m Events (e,g., disaster clean-up). and in the process make it much more ger (also called "job ledger" or "sub difficult to use features and functions in ledger") can define unique attributes ■Expenses that relate to numerous g ) q the new software. by project. As with all other the other revenue sources. chart of account segments, having a ■Activities or events that require high MICHAEL J. MUCHA is director of GFOA's clear plan for how to use the chart of levels of detail. Research and Consulting Center. P, kk 32 Governme6ibafWvre� ?*egular Board Meeting Agenda Packet- Page 148 of 172 Page 24 of 31 REPORTING IMPACTS FROM l� IMPLEMENTATION OF NEW ERP AND CHART OF ACCOUNTS y KEVIN MIZUNO, FINANCE MANAGER ,_ -- SEPTEMBER 3,2020 4 1 BACKGROUND • Existing chart of accounts in legacy system revamped as part of Central San's implementation of a new ERP system • Central San's FY 2020-21 budget was adopted on June 4, 2020 • Several presentation changes were incorporated into the budget related to the newly improved chart of accounts designed for the new ERP system. Changes largely impacted the following: • Financial Summary included new section on changes • O&M org unit structures • O&M expense line item titles & roll-ups • At the May 18, 2020 Finance Committee, staff was asked to provide a reconciliation of the old chart of accounts to the new one and an assessment of its impacts on financial reporting z 1 September 3, 2020 Regular Board Meeting Agenda Packet- Page 149 of 172 Page 25 of 31 CHART OF ACCOUNTS- KEEP AS IS OR CHANGE? • In an ERP project a foundational element is the Chart of Accounts. Should it be carried forward as is, or changed? Keep As Is Change It + Familiarity + Best practice is to redo(per GFOA) +"It generally seems to work" + Existing COA had problems that needed to be +Less work&complexity to addressed keep something as is than • Vendors embedded (a 25-year-old workaround) completely redesign • Distinctions without a difference(similar + Reporting consistency accounts; no one knows what distinguishes them) • Did not reflect current organizational structure • Running out of numbers in 4-digit capital projects +Changes necessary to take advantage of Oracle functionality in reporting We started with the premise of"keep things generally the same",but as we progressed,ended up adopting more significant changes. 3 3 CHART OF ACCOUNTS SEGMENTS (OLD FORMAT Account Structure Cost Fund- Dept/Div- Center. Element-Object Represents:Running Expense Fund,Admin Dept,Central Services 001- 0100- 400. 11-01 Division,Materials& Supplies,Office Supplies 4 4 2 September 3, 2020 Regular Board Meeting Agenda Packet- Page 150 of 172 Page 26 of 31 CHART OF ACCOUNTS SEGMENTS (NEW FORMAT) Org Units updated;will now have 3 primary departments reflecting adopted budget Account Structure Represents:Running Fund- OrgUnit ObiectClass Interfund Future1 Future2 Expense Fund,Purchasing, Overtime,then three segments that will see 10 1501 70105 00 0000 0000 infrequent or onlfuture use Reserving right to Reserving right to Fund numbers Renumbered with new scheme. implement programs implement treatment Five digits starting with: in the future phase costing,or other updated;some Assets 1 item in the future added for Trusts/ Liabilities 2 other needs Equities 3 Revenues 4 Expenditures/Expenses 7 Expenditure Accruals 8 Y 'A Statistical Accounts 9 _ 5 NEW ORGANIZATION UNIT ROLL-UPS • New roll-ups significantly improve roll-up capabilities,alignment with HR system,and nexus to budget book • Director cost center eliminates ® ® ® reporting Director&admin costs within one of own divisions • Operating Department divisions structured similarly as before with new numbering for roll-up purposes • Pump station cost child included within Plant Maintenance "parent"as a responsibility of Plant Maintenance Division Manager • Recycled Water within 5000- Series not depicted here 6 6 3 September 3, 2020 Regular Board Meeting Agenda Packet- Page 151 of 172 Page 27 of 31 NEW GRANDPARENT EXPENSE ACCOUNT ROLL-UPS ® At highest level within expenses(7-series),there are seven basic"grandparent"expense categories in new chart of account. ® Plan is for line items within budget book document will be presented at this level. Structure reflects template chart of accounts ® published by Improved parent-child roll-ups will allow for reporting at the highest to lowest levels at the"click of a button". Expenses organized into three levels:Grandparent, Parent and Child. • Lowest child accounts now a lot more granular to improve reporting,however plan to budget at the parentlevel. • Important:Per best practice guidance,natural accounts should describe the article purchased or service obtained,not the purpose(Le,org unit, ® program). 7 1 F1 OBJECT CLASS CHANGES APRIL 2020 REPORTS (OLD FORMAT) YTD Fav/(Unfav) Total FY Budget YTD Budget YTD Actual Variance Total Revenues $ 89,560,370 $ 71,179,950 $ 71,841,498 $ 661,548 O&M Expenses: Total Labor Charges $ 52,789,084 $ 43,426,710 $ 40,787,313 $ 2,639,397 Unfunded Liabilities 12,436,841 10,294,370 10,321,170 )26,800) Chemicals 1,620,000 1,349,970 1,191,646 158,324 Utilities 4,209,350 3,507,730 3,465,522 42,208 Repairs&Maintenance 5,242,867 4,368,830 3,426,769 942,061 Hauling&Disposal 1,186,175 988,420 772,074 216,346 Professional&Legal Services 874,300 728,500 295,463 433,037 Outside Services 3,639,547 3,094,762 1,908,546 1,186,216 Materials&Supplies 2,152,127 1,793,165 1,689,955 103,210 Other Expenses 2,464,834 2,141,198 1,668,147 473,051 All Other 969,650 945,530 951,263 (5,733) Total Expenses $ 87,584,775 $ 72,639,185 $ 66,477,868 $ 6,161,317 8 ' 8 4 September 3, 2020 Regular Board Meeting Agenda Packet- Page 152 of 172 Page 28 of 31 OBJECT CLASS CHANGES APRIL 2020 REPORTS (NEW FORMAT YTD Fav/(Unfav) Total FY Budget YTD Budget YTD Actual Variance Total Revenues $ 89,560,370 $ 71,179,950 $ 71,841,498 $ 661,548 O&M Exoenses: Salaries&Wages $ 38,545,093 $ 31,074,840 $ 29,831,525 $ 1,243,315 Employee Benefits 11,982,565 12,397,230 11,043,303 1,353,927 Unfunded Liabilities 14,782,917 10,336,210 10,340,304 (4,094) Purchased Property Services 4,483,744 5,584,590 4,418,674 1,165,916 Purchased Professional, Technical&Other Services 6,418,232 4,718,850 2,918,952 1,799,898 Supplies&Materials 9,322,677 6,650,865 6,316,113 334,752 Other Expenses 2,029,547 1,876,600 1,608,997 267,603 Total Expenses $ 87,564,775 $ 72,639,185 $ 66,477,868 $ 6,161,317 3 9 OBJECT CLASS CHANGES APRIL 2020 REPORTS (COMPARISON) Old Format New Format VTD Fav/(Unfav) KD Fav/(Unhv) otel FY ButlgM KD Butl¢et 1T0 Ad- Variance -tal lYButlge[ YTU ButlgetYTD ARual Variance Total Revenues $ 70 $ ]1,1]9,950 5 )1.841.498 $ 661.s48 11al Revenues $ 89,560,3]0 $ )1,1]9,950 $ 71541,98 $ 661548 —E.--M Emen— o&M E.A.— T-1 Labor Charges 3--5 43,42, 5a1ades&Wages $ 38,545,093 $ 31,0]4,840 $ 29,831,525 $ 1,243,315 Unfunded Liabilities "Y'6.g41--10594-3]0 10,311]0 as^Oq Employee Benefits 11,982,565 12,397,230 11,043,303 1,353,927 Chemicals 1,620,000 1,349,9]0 1,191,646 ,32 Unfunded Liablllties 14,]82,91] 10,336,210 10,340,304 (4,094) Ubliliez 350 3,50],]30 3,65,522 42,20 --e Purthased Pmpem Services 4,483,744 5,584,590 4,418,674 1,165,916 5,242,867 ,830 769 942,061 Ot' 5ervi 6,418,232 4,718,850 2,918,952 1,799,898 au ing 1,186,1]5 988,4 2,074 6,346 _ Supplies&Materials 9,322,677 6,650,865 6,316,113 334,752 Professional&Legal 8,500 433 Other Expenses 2,029,547 1,876,600 1,608,99] 26],603 Outside Services 3,639,547 6,216 Total Expenses $ 87,564,775 $ 72,639,185 $ 66,77,868 $ 6,161,31] Materials&Supplies E 2,15 ,793,165 ,689,955 103,210 OMer Expenses 2,469,8 2,141,198 1,668,14] 473,051 All Ocher 969,650 945,530 951,263 (5,733) Total p ses $ x,584,7)5 $ 72,639,185 $ 66,477,868 $ 6.161,317 10 ' 10 5 September 3, 2020 Regular Board Meeting Agenda Packet- Page 153 of 172 Page 29 of 31 ORGANIZATION UNIT CHANGES APRIL 2020 REPORTS (OLD FORMAT) Period Period Budget Period Actual Variance YTD Budget YTD Actual YTD Variance Administration $ 1,838,383 $ 1,477,472 $ 360,911 $19,230,847 $16,330,366 $ 2,900,481 Engineering 1,354,963 1,163,526 191,437 14,064,572 12,999,283 1,065,289 Collection System Operations 1,222,796 1,131,782 91,014 12,258,010 11,420,460 837,550 Plant Operations 2,295,519 2,525,353 (229,834) 23,329,906 22,406,206 923,700 Pump Stations 241,210 208,473 32,737 2,412,100 2,372,686 39,414 Recycled Water 132,215 70,695 61,520 1,343,750 948,868 394,882 Totals $ 7,085,086 $ 6,577,300 $ 507,786 $72,639,185 $66,477,868 $ 6,161,317 • Quarterly Financials provided to Finance Committee and Board previously showed these six (6) major organization units • Collection System, Plant Operations and Pump Stations all part of Operations Department • Plant Maintenance was consolidated with Plant Operations 11 IA ORGANIZATION UNIT CHANGES APRIL 2020 REPORTS (NEW FORMAT Period Period Budget Period Actual Variance YTD Budget YTD Actual YTD Variance Executive Management $ 222,605 $ 247,223 $ (24,618) $ 2,301,050 $ 2,477,414 $ (176,364) Administration 1,681,913 1,288,556 393,358 17,591,147 14,371,338 3,219,809 Engineering& Technical Services 1,354,963 1,163,526 191,437 14,064,572 12,999,283 1,065,289 Operations 3,693,390 3,807,301 (113,911) 37,338,666 35,680,965 1,657,701 Recycled Water 132,215 70,695 61,520 1,343,750 948,868 394,882 $ 7,085,086 $ 6,577,300 $ 507,786 $72,639,185 $66,477,868 $ 6,161,317 • Previously Board, GM and SOD were in Admin. Separated as they oversee other Departments as well. • Collection System, Plant Operations and Pump Stations consolidated into Operations Department as this report is intended to show Departments and not divisions • Org Unit report also generated at divisional level (one level lower than dept.)and tracked by management internally 12 � 12 6 September 3, 2020 Regular Board Meeting Agenda Packet- Page 154 of 172 Page 30 of 31 HELPFUL DOCUMENTS PROVIDED Accompanying Memo to Finance Committee on July 21 , 2020 includes the following helpful attachments provided to Divisional Management Attachment 2 - Translation index of old to new expense accounts Attachment 3 - Translation index of old to new org units Attachment 4 - Translation index of old to new full account strings Attachment 5 - Master chart of Accounts Document shows full new chart of accounts and the various parent-child rollup relationships CONCLUSION New chart of accounts format improves logic of reporting capabilities with numerical rollup structure (needed for Oracle) Finance anticipates many questions and feedback on new chart of accounts and some refinements over the next 1-2 years Providing referenced documents to Division management and administrative support team members will help smooth the transition to the new ERP over the next two months _P 14 ' 14 7 September 3, 2020 Regular Board Meeting Agenda Packet- Page 155 of 172 Page 31 of 31 QUESTIONS & FEEDBACK g September 3, 2020 Regular Board Meeting Agenda Packet- Page 156 of 172