Page 2 of 47
Attachment 13
City of Bartlesville, OK ERP Business Case
2016
Prepared By The City of Bartlesville, OK and GFOA
Bartlesville, Oklahoma Business Case Analysis by Functional Area
Table of Contents
ACCOUNTING 3
Chart of Accounts 4
Financial Reporting 5
Grants 6
Project Accounting 7
Accounting - Future 8
ACCOUNTS PAYABLE 10
Accounts Payable 10
Bank Reconciliation 11
Purchasing Cards 12
Accounts Payable - Future 14
ACCOUNTS RECEIVABLE 16
Receivables – Set Up 16
Cash Intake 18
Accounts Receivable - Future 19
ASSET MANAGEMENT 20
Asset Acquisition /Asset Tracking 20
CIP Projects 21
Asset Year-End Reporting 22
Asset Transfers/ Disposal 23
Work Orders 24
Asset Management - Future 26
BUDGETING 27
Revenue Projections 27
Budgeting Adjustment / Amendments 28
Capital Budget Preparation 28
Operating Budget Preparation 29
Budget Preparation - Future 31
Human Resources 33
Benefit Enrollment 33
Performance Evaluations/Employee Relations 34
Recruitment 35
Salary Change 36
Separation 37
Human Resources - Future 37
PURCHASING 39
Purchase Requisitions 39
Contracts / Change Orders 40
Inventory 41
Receiving 41
Purchasing - Future 42
TIME ENTRY /PAYROLL 44
Time Entry 44
Payroll Processing/ Reporting 45
COMMUNITY DEVELOPMENT 46
Community Development Maintenance 46
ACCOUNTING
The County’s primary accounting system is the Select ERP solution; a product of Harris Computer Systems. There are four other primary systems that feed into the Select solution, which together, cover a majority of the business processes within the City. Northstar Utility Solutions (also a Harris product) is used for the water and sewer billing. Firehouse (Xerox product) is used for fire personnel scheduling. SleuthSystems is used by Bartlesville Municipal Court for case management and all accounting related to individual court cases. Hansen (an Infor product) is being used for work orders related to wastewater treatment.
The City operates throughout the year on modified accruals; however, the enterprise funds report on a full accrual basis throughout the year. The operating funds and the proprietary funds follow the same business processes; meaning, they follow the same process rules for approving purchases, time approvals, and other typical operational processes. The diagram below depicts the current relationship between the primary accounting systems and the current reporting requirements.
Diagram 1
/Chart of Accounts
The City’s primary chart of accounts resides in the Select ERP solution. All applications that integrate to Select must map to Select’s chart of accounts. The City of Bartlesville’s fiscal year ends June30.
Current State of City’s Chart of Accounts:
· The current chart of accounts uses four segments:
o Fund
o Department
o Account
o Project
· It was reported that all financial reporting comes from a single ledger; meaning, there are no separate reporting ledgers
o The City does not need to abide by a State Chart of Accounts for reporting purposes
o The City does need to report financials related to waste water and water to a Water Resource Board
o The City would like to have year-round GAAP statements
· The City has spent the past several years re-designing the chart of accounts to facilitate managerial reporting and end-of year financial reporting.
o GFOA did not find any major issues with the current design of the core chart of accounts. (The City did report that they would like to remove inactive accounts, which can be removed during implementation of a future system.)
o The project accounts will need to be assessed to fit modern systems. (See Project Accounting)
· Based on interviews with City staff, GFOA understands that any interface between an external system and the general ledger requires a significant amount of manual work. For example, FTP data exported from NorthStar will be in summary journal entry form. The detailed data remains in North Star.
· Inter fund transfers are (49 and 59) (49 if from the general fund) (59 is to the general fund)
Process Analysis Recommendations:
o Accounts that are candidates for removal will need to be identified as part of implementation.
o Funds that are no longer needed will be identified as part of implementation.
Policies and Procedures Document Reference:
· Procedure File Number AC-7
Process Maps:
· No process map was created for the Chart of Accounts section
/Financial Reporting
Although most of the administrative systems provide reporting functions, staff feels they are difficult to use, particularly when staff would like to modify an existing report or create new reports. As a work-around, staff uses Microsoft Office products to report information from the various systems
Reported Gaps/Requirements:
The City reports on a modified accrual basis throughout the year and a full-accrual basis for end of year reporting. The City would like to have capabilities to report on both views throughout the year.
The City uses fifteen accounting periods. The first 12 are operating periods based modified accrual accounting. The thirteenth period is used for reconciling between modified and full accrual (i.e., GAAP adjustments). The fourteenth period is used for audit adjustments. The fifteenth period is the closing period.
The following external stakeholders must be accommodated in any future solution:
· Water Resource Board – Financial reports from Wastewater and Water enterprise funds
· Local Taxing Bodies (e.g. County, School District) – Tax Increment Financing (TIF) activity (asset sales)
· State of Oklahoma State Auditor Report (SAI)
· Note: none of the above require the City to report activity based upon a separate chart of accounts
· The City would like functionality that will allow them to create distribution reports (940 reports) and taxable wage reports in a future solution. The City is currently entering this information manually.
· The City would like functionality in a new solution that would create the State Auditor Inspector (SAI) reports for them within the system if possible. (Desired but not required.)
· Systems that interface to the primary general ledger will serve as sub-ledgers. Summary level data will be sent to the general ledger. Detailed transactions will remain within these specialized applications. (See individual functions)
· City would like to have two-way interfaces with external systems (i.e., outside of the ERP solution)
Policies and Procedures Document Reference:
· Procedure File Number UB-58, Procedure File Number PA-10, Procedure File Number PA-4, Procedure File Number PA-7
Process Maps:
· Fixed Assets, Year-End Map
/Grants
The City does not have an automated grants accounting system. The City’s Grants Administrator tracks all grant activity through the financial system since grants are tracked as projects or funds within the system.
Current Process for Grant Management:
1. Departments are supposed to notify the Finance Office when they are applying for a grant. In most cases, the Finance Department finds out about a grant application after the application has been submitted.
2. All grants are tracked by a Grants Administrator. The Grants Administrator is also responsible for working with the department to set up the budget for grant.
3. The finance office runs quarterly reports
4. The City Manager has the authority to pursue any grant without preauthorization. This privilege extended to department directors. They do not have to go to the City Council for authorization to pursue a grant.
5. Many of the grants are multi-year grants
Reported Gaps/Requirements:
· The City wants easier ability to track vendor activity by grant
· The City will need to track by fiscal year and sponsor year
· The City would like a system that is user friendly
· It is difficult to extract or export information from the current systems
· System errors will occur without being invoked by a transaction or configuration change. Since the error messages are not intuitive, and root cause is not known, the City has to call contact the current ERP software company to fix the issues
· It’s difficult to conduct analysis, particularly ad-hoc analysis within the current system
· The City would like ad-hoc query functions for grants analysis (and for the entire system)
· The current system crashes on a regular basis
· The City’s grant policy will result in the finance department receiving funds throughout the year that were not budgeted for.
· The City faces challenges with grant reporting when they take or win grants, and fail to inform the cities designated grant manager. The grant manager needs to know the matching, revenue source, and transaction activity.
Policies and Procedures Document Reference:
· Grants are reference in the record retention policy section, number 28
Process Maps:
· No process map was created for the Grants section
/Project Accounting
Current Project Accounting Design:
Select’s chart of accounts contains a field for project accounts. Project accounts that begin with “99” (e.g., 99007) are projects that will remain in perpetuity (never be closed). All other project numbers use the first two starting digits to indicate the year that they were created followed by the sequential number assigned to project for that year (e.g., 04016)
Reported Gaps/Requirements:
· Reporting fiscal year activity within “99” projects can be difficult, since there is no way of identifying year to year activity easily.
· Projects need to show income statements
· There is no way to restrict transactions for projects (and grants) that have restricted uses.
· GFOA observed that some of the projects being tracked are not true projects. In most cases, these types of projects were assets. The City tracks assets as projects so that expenditures related to the assets can be tracked easily. Similarly, programs were also tracked as assets; again, they were tracked as projects for easier reporting.
Future Process:
· Assets will be accounted for through asset management functionality in a modern system.
· Funded programs and related activities will be tracked in a budget solution or ledger capable of tracking program related activities.
· Life-to-Date reporting functions for projects and programs will be accommodated through robust reporting functions capable of crossing fiscal years.
Policies and Procedures Document Reference:
· Procedure File Number AC-7, Procedure File Number A-13
Process Maps:
· No process map was created for the Project Accounting section
/Accounting - Future
GFOA reviewed possible scope options for the future general ledger solution. Although modern ERP applications offer most of the functions that are being provided in the City’s five enterprise applications, GFOA is recommending that the City limit the scope to the following:
The future general ledger should be limited to a primary ledger receiving financial data from other sub-ledgers.
The Firehouse software is a solution highly unique to the Fire Administration. It is unlikely that an ERP application will have this functionality. Therefore, Firehouse should interface to the future solution.
The NorthStar application contains detailed information about the utility accounts. A utilities solution implementation can be burdensome. GFOA is currently recommending that the City hold off on implementing a utilities solution and interface the current application to the future ERP solution instead. Once the future ERP system is stabilized, the City can explore the possibility of replacing the utilities application.
The Sleuth application is also highly unique. Current ERP solutions are limited in case management functions. Implementation requirements related to CJIS compliance requirements may also be burdensome to the City, if the City decides to replace the court management system. GFOA is currently recommending that the Sleuth interface to the future ERP solution and only interface summary financial data to the future financial application.
The Hansen permitting solution should be replaced by a future solution. Permitting functions are not highly unique and can be accommodated by modern ERP applications. It is recommended that the solution be incorporated into the future solution.
The following diagram depicts the desired state for the future solution.
Diagram 2
ACCOUNTS PAYABLE
The accounts payable process at the city is a highly central one. All payments are issued from the Select accounts payable module and managed by the Department of Finance/Treasury. The only exception is that the City’s Municipal Court has a manual checkbook used for payments related to the court docket. Municipal Court payments are later reconciled (in summary form) into Select.
/Accounts Payable
The City of Bartlesville has roughly 18,000 vendors in its current system. (A majority of the vendors represent utility billing refunds.) Payment runs occur every two weeks.
Current Process for Accounts Payable Processing:
1. Payments are authorized based upon the matching of the purchase order to the receiving document (invoice, packing slip with invoice, etc.)
a. Departments receive the item and scan the invoice or packing slip into Fortis, which is the City’s document management system.
b. After the receiving and scanning is complete, the Fortis system notifies the Department of Finance (via AP inbox) that the item is ready for payment.
The Department of Finance is responsible for completing the three-way match in Select (Purchase Order, Receiving Document, Invoice) and notifies the Department that the item is ready for payment. The Department approves the payment.
2. After Department approval, Finance creates a claim entry in Select.
3. City Clerk/City Manager approves the final payment
4. This list is compared against the City’s available cash balance
5. At the beginning of the check run process Finance runs a trial batch of check payments to ensure that batch claim entries are balanced.
6. If everything balances, they will run the final payment run, which generates a report of all checks to be issued.
7. Finance runs the checks
8. A final review of all printed checks is completed by Finance
9. The checks are scanned into Fortis and referenced to the PO in the document management system.
10. Once approved, Finance stuffs each check into envelopes and mails or distributes.
Future Process:
· Future system will accommodate “one-time” vendor records that will be used to issue utility payment refunds. “One time” vendor records do not require extensive data and can be marked for purging easily.
· The City’s practice of imaging the supporting documentation is a good one and will be used to support the future solution.
· Departments will be responsible for completing the three-way match. The system will not approve a payment without the matching transaction.