Propri
etary & Confidential
FINAL REPORT
City of Stockton
ENTERPRISE RESOURCE PLANNING (ERP) INTERNAL CONTROLS
REVIEW
December 6, 2022
Moss Adams LLP
999 Third Avenue, Suite 2800
Seattle, WA 98104
(206) 30
2-6500
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
Table of Contents
Executive Summary 1
A. Scope and Methodology 1
B. Summary of Findings and Recommendations 2
Backgroun d, Scope, and Methodology 3
A. Background 3
B. Scope and Methodology 3
C. Commendations 5
Findings and Recommendations 6
Process Improvement Opportunities 8
Appendix A – Policies and procedure recommendations 10
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 1
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
EXECUTIVE SUMMARY
Moss Adams LLP (Moss Adams) was contracted by the City of Stockton (the City) to perform a high- level evaluation of the internal controls related to the City’s new Enterprise Resource Planning (ERP) system, Munis by Tyler Technologies (Munis). At the time of the review, the City had completed two of the three phases of the full implementation of the ERP system; therefore, this internal controls review was limited to phases one and two. The review took place between June and November 2022.
We reviewed the City’s ERP internal controls for design but did not test those controls for operating effectiveness, which is assumed to be a forthcoming activity. Specific areas within the ERP system where controls were reviewed included:
Accounting
Budgeting
Procurement
Contract management
Vendo
r self-services
Acco
unts payable
General billing
Bu
siness licensing
Grant ma
nagement
Project management
We performed a variety of procedures to conduct this review, consisting of identifying control objectives and key controls that would satisfy those objectives, assessing key internal controls for proper design and effectiveness, evaluating how these functions interface with separate outside systems, assessing processes that require manual inputs for adequate internal controls, and comparing select processes and controls for compliance with policies and procedures and best practices.
The review of the ERP internal controls was completed under the consultancy standards of the American Institute of Certified Public Accountants (AICPA). Accordingly, we provide no opinion, attestation, or other form of assurance with respect to our work or the information upon which our work is based. This engagement was also performed consistent with the guidance issued by the Institute of Internal Auditor’s (IIA’s) International Professional Practices Framework (IPPF). This report was developed based on information gained from our interviews and analysis of sample documentation. The procedures we performed do not constitute an examination in accordance with generally accepted auditing standards or attestation standards.
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 2
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
FINDINGS AND RECOMMENDATIONS
1. Finding
Significant gaps in policies and procedures for key functional areas, including Accounting and Financial Reporting, Accounts Payable (including Vendor Self- Services), and the General Billing (including Business Licensing), were identified. Existing policies and procedures do not reflect updates and consideration for the new ERP system.
Recommendation
Update/expand policies and procedures at the City-wide level that address the minimum control standards, system requirements, and roles and responsibilities for key functional areas.
2. Finding
Workflow documents, which define transaction flows and identify responsibilities and approval requirements, were not available to support two key functions within Munis.
Recommendation Develop workflow documents to support the current Business Licensing and Vendor Self-Service functions within Munis.
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 3
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
BACKGROUND, SCOPE, AND METHODOLOGY
The City transitioned to a new ERP system, Munis, in January 2021 from their old ERP system, HTE.
The system transition was designed to be conducted in three phases. The City rolled out phase one in January 2021 which included many financial modules such as: accounting, procurement, contract management, vendor self-service and accounts payable. In July 2021, the City transitioned to phase two which included modules for general billing, business licenses, and citizen self-service. The last phase, which the City had not begun as of the time this review started, includes the City’s core Human Resource (HR) functions, such as implementing payroll, time keeping, employee expense reimbursement, and position control and salary functions/modules. Moss Adams was contracted by the City to perform an internal controls review related to the Munis, specifically around phase one and two, and to identify opportunities for improvement.
The scope of our review included a high-level evaluation of key internal controls related to the implementation of phases one and two of the new Munis. The evaluation was used to determine the general adequacy of internal controls and identify areas warranting more in-depth review in the future.
The scope of the internal controls review included the following aspects:
Identifying control objectives within Munis and key controls that would satisfy those objectives Reviewing policies and procedures
Examining relevant documentation in support of select key controls within Munis Assessing key internal controls for proper design and effectiveness Evaluating how functions within Munis interface with separate outside systems Assessing processes that require manual inputs for adequate internal controls Providing recommendations on key controls that need to be implemented or changed The work performed included interviews with personnel involved in managing the following key functions:
Accounting
Budgeting
Procurement
Contract management
Vendor self-services
Accounts Payable
General billing
Business licensing
Grant management
Project Management
The procedures performed included:
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 4
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
Document Review: We reviewed the existing City policies and procedures and other resources related to functional areas, including User Access Reports, Delegation of Signature Authority Forms, Process Workflows, and MUNIS Procedural Documentation. Policy and Procedure Analysis: To evaluate the City’s processes and controls for alignment with policies, procedures, and best practices, we performed the following:
− We met with City process owners to develop an understanding of the current processes in place for each function.
− We requested the policies and procedures, and any draft updates in progress, for each function/module under review and assessed the policies and procedures for alignment with current processes.
− If policies and procedures were not in line with processes, we determined if draft updates were in progress.
− We evaluated the policies and procedures and draft updates (if any) for comprehensiveness and alignment with best practices and City practices. We documented any policy and procedure gaps and proposed potential improvements and recommendations to address those gaps.
− We evaluated select key internal controls functions within the Munis system, such as employee system access rights, and assessed if the controls were supported by User Access Rights by System Role Reports and Delegation of Signature Authority Forms. − We evaluated each functions use of standard and accessible workflow documentation, such as flowcharts for significant processes which describe roles, assignments, individuals with access to specific tasks, and approval amounts and approval levels.
Where functions were not supported by the standard documentation, we assessed if workflows were documented elsewhere such as within the MUNIS Procedural Templates.
Utilizing the initial procedures performed, we identified areas with potential internal control gaps and increased risks of error due to the reliance of manual system inputs. For those areas identified, we performed additional procedures as described below.
Budgeting: We evaluated key internal controls within the budget function by performing the following:
− Requested a listing of all active employees as of October 2022 and selected a sample of five employees to perform the following tests:
Assessed the employee’s Delegation of Signature Authority form for alignment with system access levels and employee’s positional responsibilities. Assessed for segregation of duties between those who have the ability to create budgets and those with the ability to approve budgets.
− We requested the Project Master Files Report dated January 1, 2022, which provided us a listing of project budgets that were imported into Munis from the HTE system. Using this listing, we selected a sample of two budget imports and reconciled any budget amendments to evaluate whether project budgets were accurate within the Munis system and the original budgets had not been overridden by amendments. Business Licensing: We evaluated internal controls within the business licensing function within Munis by performing the following:
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 5
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
− Requested a listing of business license transactions from January 1, 2022, through September 30, 2022, and selected a sample of five applications to perform the following tests:
Reviewed applications for adequate approval documentation (outside of Munis) Reperformed manual inputs and fee calculations to determine if correct data was input, and accurate fees were collected.
Grant Management: We evaluated internal controls within the grant management function by performing the following:
− Requested a listing of grants transferred from HTE to Munis system and selected a sample of two grants and evaluated the corresponding grant information, which requires manual input, was correctly recorded and reconciled to the original grant data within HTE. − Requested a listing of grant projects from January 1, 2022, through September 30, 2022, and selected a sample of two grants to evaluate project expenditure reports that are tracked outside of Munis and compared those to the grant systems reporting for completeness and accuracy.
Vendor Self-Services: We evaluated internal controls within the vendor self-services function by performing the following:
○ Requested an audit history report of vendor changes from January 1, 2022, through September 30, 2022, and selected a sample of five vendor changes and evaluated supporting documentation to determine if vendor changes appeared accurate and reasonable.
○ Reviewed the vendor self-service workflow process provided by Munis and assessed the risk of paying for goods and services that have not been received. We assessed the results for any potential improvements to reduce the any identified risk.
Although the focus of this assessment was to identify opportunities for improvement, it is important to note the areas of commendable operations. The City should be commended for the following accomplishments:
Policies and Procedures: While policies and procedures need to be expanded, functions have Munis Procedural Templates which are in line with the City’s processes and provide a good baseline for implementing updated policies and procedures. Readiness for Change: During interviews, feedback from City employees indicated an eagerness to implement improvements to their responsible functions. Collaboration Across the City: The ERP implementation required collaboration across several departments and functions within the City. The assigned Munis Project Leads appeared knowledgeable of their assigned areas, and it was clear that a significant amount of work and cross-collaboration had occurred to ensure phases one and two were a success. Grant Management: For the sample of grants evaluated, the corresponding grant information appeared to be properly recorded in Munis and reconciled to the original grant data within HTE.
We would like to thank City staff and management for their willingness to assist with this project.
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 6
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
FINDINGS AND RECOMMENDATIONS
1. Finding Significant gaps in policies and procedures for key functional areas, including Accounting and Financial Reporting, Accounts Payable (including Vendor Self-Services), and the General Billing (including Business Licensing), were identified. Existing policies and procedures do not reflect updates and consideration for the new ERP system.
Recommendation Update/expand policies and procedures at the City-wide level that address the minimum control standards, system requirements, and roles and responsibilities for key functional areas.
The GFOA recommends that every government should document financial and accounting policies and procedures specific to their government. Policies and procedures should be in a searchable and electronic format and stored on a portal or intranet site. The policies and procedures should be readily available to all employees who need it and contain information on the key control activities between authority and responsibility of all employees, a description by which employees (by title) are assigned to perform which procedures, a practical, and step-by-step explanations of how procedures should be performed rather than descriptions of controls that are vague or stated in an idealized form or highly detailed step-by-step instructions.
Similar to other Cities, there is an opportunity to strengthen policies, procedures, and training opportunities for City employees. Gaps of varying degrees were discovered in many of the functions reviewed, especially in the case of the Accounting and Financial Reporting, Accounts Payable (including Vendor Self-Services), and the General Billing (including Business Licensing) functions.
Significant gaps existed in the documentation of the minimum internal controls that must be established and enforced for these functions.
The procedural document for these functions is primarily the Munis Procedural Templates, which cover highly detailed step-by-step instructions on performing tasks within the system. The MUNIS Procedural Templates were “how to” documents created by Tyler Technology that provide step by step instructions on how to use certain functionalities of the system. The documents are solely focused on the functionalities of the system, similar to a user manual for a product. City policies and procedures in the functional areas reviewed had not been updated to reflect all of the changes and considerations of the new ERP. As a result, the functional areas of the new ERP are not supported by documentation of the key control activities and the related assigned City authority and responsibility.
Existing P&Ps for each of these functional areas should be updated and/or expanded to incorporate key considerations and impacts of the new ERP. Specifically, they will need to address the key internal controls within the system, and those that are outside the system, and the assigned authority and responsibility for those controls and the detailed procedures to promote compliance. In appendix A, we have provided details on the key areas that should be included in updating/expanding resources into comprehensive policies and procedure documents for each functional area.
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 7
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
2. Finding Workflow documents, which define transaction flows and identify responsibilities and approval requirements, were not available to support two key functions within Munis.
Recommendation Develop workflow documents to support the current Business Licensing and Vendor Self-Service functions within Munis.
Documentation of significant business cycle workflows is essential to define how transactions should flow within the system, identify responsibilities, define approval requirements and authority levels, and define employee access rights. Documented workflows may also demonstrate the presence of segregation of duties and compensating controls that are required when duties cannot be adequately segregated. The GFOA recommends the following in relation to workflow documentation for governments to ensure staff accountability:
Develop flowcharts for each significant process
Maintain electronic copies of process flowcharts to facilitate updating Identify responsibilities for workflow approvals in key systems Ensure systems incorporate compensating controls, where appropriate In addition, systems access levels to key system functions should be properly documented, ensuring alignment with current job responsibilities, and should be approved.
For many functional areas reviewed, the City had current documented workflows that described applicable roles and assignments, including approval authority levels and thresholds, and indicated which employees have access to specific tasks within Munis. In many cases, these workflows were in line with best practices. The City utilizes Delegation of Authority Forms to indicate what levels of systems access rights and authority levels an employee should have within a specific system functionality based on positional relevance. These forms are supposed to be reviewed and approved by department heads and submitted to the relevant functional owner for review before the Information Technology (IT) Department applies a change to role or access.
During our review of workflow documentation for the ten functions evaluated as part of this review, we identified instances where workflow documents were not available to support key functions in the system. Specifically, we identified:
Two instances where functions did not have any supporting workflow documents or other similar procedural templates (Business Licensing and Vendor Self-Services).
Ensuring that workflow documentation is in place for all key functions within Munis is important to help demonstrate accountability of employees, allow for the evaluation of segregation of duties, and to ensure that access rights properly reflect the intended flow of transactions within the system. The City should develop workflow documentation for the Business Licensing and Vendor Self-Service functions that reflect current responsibilities and authority levels. These workflow documents should be aligned with the corresponding City policies and procedures to help build consistency throughout processes. Workflow documents should be evaluated on a regular basis to ensure that duties are appropriately segregated and that current processes are accurately reflected.
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 8
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
PROCESS IMPROVEMENT OPPORTUNITIES
Moss Adams identified an opportunity for process improvement because of this internal controls review. The following tables summarize the recommendation.
CATEGORY PROCESS IMPROVEMENT RECOMMENDATION
POLICY AREA RECOMMENDATIONS
1 System Receipts for
Business Licensing
The Business Licensing function requires the completion of a Business License Tax Renewal form by clients.
Clients manually complete the form noting total gross receipts and a manual calculation of the total fees and any applicable fees or penalties assessed. Clients submit the completed form to the City at the time of payment, and the City inputs the information into Munis to process each payment.
While Business Licensing transactions are being recorded in Munis, the system has not been setup to accurately reflect the various components of each transaction (i.e., total fees, penalties, adjustments, total payment, etc.) and errors appear to occur when certain penalties are assessed or waived. During our review of the supporting documentation for a sample of five business license applications/renewals, we found two instances where an adjustment was listed in the Payment Summary Receipt, which is a system generated record of each transaction. After evaluating the transactions further, we found that the line items presented as “adjustments” on the Payment Summary Receipt were the total payment rendered. In these instances, the total “payment tendered” was also incorrectly presented as zero and the base fee was blank, signifying that the line items on the Payment Summary Receipt were not accurately capturing the transactions being processed.
This error appears to have occurred in situations where a penalty was being waived for a client account. In 2022, the City decided, due to the Munis implementation, to waive penalties for business license renewals given the significant backlog in providing notices to business owners that their business licenses were going to expire. The errors identified above were related to instances where a penalty was calculated by the client and paid; however, the City planned to refund the assessed penalty fee. In addition, it was reported that a physical receipt (printed from Munis) is given to clients to reflect payments made; however, copies of the receipts are not retained by the City and personnel were not able to pull the receipts up in the system.
The City should work with Munis to update the system documentation/records of Business Licensing transactions to ensure that the fields reported in the Payment Summary Receipt correspond to the actual transactions and activity. When true “adjustments” are required, they should be accurately reflected in the system as such to allow for regular independent reviews to assess for supporting documentation and reasonableness. This will help ensure that Business Licensing activity recorded is accurate and allows for adequate monitoring to prevent inappropriate adjustments from being processed without being identified. In addition, copies of receipts that are provided to clients to support payments made should be retained by the Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 9
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
POLICY AREA RECOMMENDATIONS
City. Maintaining receipts allows for the City to pull the records in the event of a payment or balance dispute and to reconcile payment activity on a regular basis.
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 10
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
APPENDIX A – POLICIES AND PROCEDURE RECOMMENDATIONS
The following table details the functions lacking policies and procedures around the processes within Munis and provides the key areas that should be included in a comprehensive set of policies and procedures as described in Observation No. 1.
POLICY AREA RECOMMENDATIONS
1 Accounting and
Financial Reporting
The City does not have comprehensive Accounting and Financial Reporting P&Ps outside of the Munis Procedural Templates to document the process within the Munis system. A comprehensive set of Accounting and Financial Reporting P&Ps should be developed and, at minimum, cover the following key areas:
Chart of accounts maintenance and account setup and structure including which employees should have responsibility for, and access to, process account additions, changes, and deletions. Journal entry preparation, approval and posting including what supporting documentation is maintained within Munis and outside of the system.
Month-end and year-end close procedures including the timing, responsibilities for performing each procedure and review/approval, and monitoring.
Financial reporting specifically addressing the reports that will be prepared, on what basis (monthly, quarterly, etc.) and who is responsible for reviewing and approving each report. Significant accounting policies and procedures to ensure compliance Account reconciliations including which balance sheet accounts are reviewed monthly, responsibility for preparation, review and approval, and procedures on how the reconciliations are to be performed and how variances must be addressed.
2 Accounts Payable and
Vendor Self-Services
The City does not have comprehensive Accounts Payable P&Ps outside of the Munis Procedural Templates to document the process within the Munis system. A comprehensive set of accounts payable P&Ps should be developed and, at minimum, cover the following key areas:
New vendor setup and vendor changes, including required supporting documentation, approvals, and segregation of duties, vendor master list controls and annual reviews, dormant vendors, etc. Specifically address how the vendor master list within Munis will be restricted and monitored. Receiving purchases physically and storing supporting documents within the Munis (goods and services, as applicable)
Invoice receipt and approval
Attachment A
Enterprise Resource Planning (ERP) Internal Controls Review Report | 11
FOR INTERNAL USE OF CITY OF STOCKTON ONLY
POLICY AREA RECOMMENDATIONS
Reconciliation between invoice and purchase requisition (PR)/purchase order (PO), receiving documentation, etc. Invoice coding and system entry.
A/P processing including required reviews/approvals, invoice tie out, and pre and post check registers. Monitoring controls over the disbursement function such as vendor file changes/addition reviews, trend analysis (i.e., total payments by vendor), etc. The specific Munis reports utilized should be defined. Other key controls and internal procedures over the disbursement function
3 Revenues and
Accounts Receivable
(Including General
Billing and Business
Licensing)
The City does not have comprehensive Revenue or accounts receivables policy and procedure over general billing. Their current policy only contains information on adjustments. A comprehensive set of revenue and accounts receivable P&Ps at a city-wide level should be developed and expanded to include policies and procedures of other key areas in addition to adjustments. At minimum, a comprehensive P&P should cover the following key areas:
Defining the specific types of revenue throughout the City and referencing individual policies where appropriate Defining the City's requirements around billing and collection activities including defining roles, responsibilities, documentation, frequency, approvals, etc.
Revenue recognition and accounting for revenue
Systems access related to A/R management systems to ensure proper segregation of duties Customer account setup and requirements
Distinguishment of which City functions/departments process account activity within Munis and the related system controls applied, and which process account activity outside of Munis. In cases where outside systems are used, requirements and procedures for reconciling activity to Munis should be defined. Handling refunds and account credits including required documentation, approvals, etc. Overall monitoring of revenue activity and A/R balances
Attachment A
Attachment A