Work Session - Enterprise Resource Planning (ERP) Project Update Copyrighted
J uly 20, 2020
City of Dubuque Work Session - Top # 1.
City Council Meeting
ITEM TITLE: Work Session - Enterprise Resource Planning (ERP) Project Update
SUM MARY: Director of Finance and Budget Jennifer Larson along with Senior
Manager Katie Ludwig and Manager Mark Mack from Government
Finance Officers Association Research and Consulting will present on
the Enterprise Resource Planning (ERP) Project in the Finance
Department.
SUGGESTED Council Discussion Only. Public Input is not accepted during the work
DISPOSITION: session portion of the meeting.Suggested Disposition:
ATTACHMENTS:
Description Type
MVM Memo City Manager Memo
Staff Memo Staff Memo
Enterprise Resource Planning (ERP) Project Plan of Supporting Documentation
Action
Enterprise Resource Planning (ERP) Project Supporting Documentation
Presentation
Dubuque
THE CITY OF �
All•Aoerica Ciq
DuB E ;,��, � ,
II �
Maste iece on the Mississi i Z°°'"z°lz"z°13
rP pp zol�*�0�9
TO: The Honorable Mayor and City Council Members
FROM: Michael C. Van Milligen, City Manager
SUBJECT: Enterprise Resource Planning (ERP) Project Update Work Session
DATE: July 10, 2020
Director of Finance and Budget Jennifer Larson is transmitting material for the
Enterprise Resource Planning (ERP) Project Update Work Session. There will be a
presentation by Senior Manager Katie Ludwig and Manager Mark Mack from
Government Finance Officers Association Research and Consulting.
�;N"'r�"� �GN,t
Mich el C. Van Milligen
MCVM/jml
Attachment
cc: Crenna Brumwell, City Attorney
Teri Goodmann, Assistant City Manager
Cori Burbach, Assistant City Manager
Jennifer Larson, Director of Finance and Budget
Chris Kohlmann, Information Services Manager
Dubuque
THE CI'TY OF �
All•Anerics Ci�
I�UB E ,���, �� ,
Ilm
Maste iece on the Mississi i Z°°'"z°lz.Z°13
rP Pp �ol�«zoi9
TO: Michael C. Van Milligen, City Manager
FROM: Jennifer Larson, Director of Finance and Budget
SUBJECT: Enterprise Resource Planning (ERP) Project Update Work Session
DATE: July 10, 2020
INTRODUCTION
The purpose of this memorandum is to transmit materials for the July 20, 2020 City
Council work session on the Enterprise Resource Planning (ERP) Project.
BACKGROUND AND DISCUSSION
The Fiscal Year 2021 and 2022 planned capital budget includes $1,000,000 to
purchase and implement a new ERP financial system.
The planning process to begin selecting a new ERP financial system began on January
28, 2020. The selection and implementation of a new system takes approximately 3
years. The first six months entailed reviewing and revising business processes. The
next six months will be determining what is needed in the ERP and writing an RFP,
doing software demonstrations, and selecting a software vendor. The second year will
be implementing the main financial core of the system. The third year will be
implementing the human resources and other modules of the system.
Due to the highly technical and complicated components of reviewing business
processes, determining organization readiness for implementation, determining the
components of the ERP system, writing a RFP and selecting and implementing a
software, the City hired Government Finance Officers Association Research and
Consulting Center to provide ERP selection advisory services.
Approximately 20 years ago, GFOA began consulting for enterprise resource planning
(ERP) system assessments, procurement, contract negotiation, and implementation
advisory services. This practice now represents the majority of all GFOA engagements
and GFOA has built a reputation as the unparalleled leader in the field of providing
objective, independent advice for ERP procurement and implementation projects.
GFOA's approach to ERP projects focuses on business process improvement, effective
governance, and building organizational readiness throughout each stage of the
procurement process. GFOA's strategic focus includes:
1. GFOA will bring many resources to this project. Best practices, ever-
evolving templates, lessons learned, tools, and most importantly,
knowledgeable consultants.
2. GFOA prioritizes training throughout a consulting approach. Consultants
will provide training on the ERP market, process analysis, ERP proposal
evaluation, implementation, project management, and more to help
increase your chance of long- term success.
3. GFOA's strength is in their network of past clients and overall members.
Every government is different, and every project has a unique set of
circumstances. GFOA's consultants are the conduit to this network.
4. GFOA will champion best practices, work to identify and mitigate risk, and
work hard to ensure the best deal possible from the vendor selected that
allows this ERP project to be an investment that the City can benefit for
many years.
The goals of the project include:
1. Understand City of Dubuque needs and challenges and work to meet
project goals
2. Provide accurate, timely, and relevant information and gain trust as a key
memberof the Dubuque team.
3. Provide honest recommendations that are solely influenced by the best
interests of the City.
4. Serve as the City's advocate throughout the project. In both short and long-
term, GFOA will be a passionate champion for best practice and ensuring
the City gets the best possible project outcomes.
The project scope includes:
1. Project Planning
a. Governance Support. This would include identifying the model for a
steering committee, project team, and any business process improvement
functions. As part of the business process analysis sessions, it is expected
that many change issues will be discussed and GFOA consultants will
monitor the potential impacts and provide guidance and appropriate ways
to structure change management efforts. Making decisions often requires
an effective (and well understood) process.
b. Project Plan. GFOA will prepare a project plan in Microsoft Project. This
document identifies all the detailed tasks for the project, the person responsible
for executing those tasks, the estimated time required to complete them, and any
dependencies that a given task may have relative to other tasks.
2
c. Project Documentation. If the City uses a website or other collaboration tool for
project and document management, we will discuss early in the project how we
can use this for sharing documents and information across the larger project
team. If desired, GFOA can also host a website with collaboration tools
specifically for this project.
d. Project Management. GFOA will participate in regular project management
meetings and provide a regular (monthly) status report for the project. We expect
our project manager to serve as a coach, guide, and advisor throughout the
project. They will maintain regular communication to address issues, point out
risks, provide lessons learned, and ultimately work to help the project be a
success. Ongoing costs and effort for all project management activities are built
into GFOA's milestones and deliverables.
Senior Manager Katie Ludwig and Manager Mark Mack from Government Finance
Officers Association will provide an update for the Enterprise Resource Planning (ERP)
Project.
I have attached the PowerPoint that will be used for the presentation and the plan of
action report prepared by Government Finance Officers Association.
Thank you.
Attachments
cc: Crenna Brumwell, City Attorney
Teri Goodmann, Assistant City Manager
Cori Burbach, Assistant City Manager
Jennifer Larson, Director of Finance and Budget
Chris Kohlmann, Information Services Manager
3
1 1
�
� � � � � . � � .
PREPARED FOR THE CITY OF DUBUQUE, IOWA
Mark Mack & Katie Ludwig
GFOA � 203 North La Salle,Chicago IL,60601
Note:This is a privileged and confidential document between the client listed above and GFOA.
G l HECITY OF
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
Contents
INTRODUCTION.........................................................................................................................................2
OVERALL SYSTEMS ASSESSMENT..............................................................................................................3
BUSINESS PROCESS ASSESSMENT.............................................................................................................4
RECOMMENDATIONS ...............................................................................................................................9
PIT Crews: PIT Crews should continue to meet on a regular basis .............................9
Planning & Goals: Redesign and plan for a new Chart of Accounts...........................10
Governance & Training: Establish Communication Plan and Project Charter......11
Policies & Procedures, Planning: Change Management, Roles, Etc.........................12
SCOPE & SCHEDULE................................................................................................................................12
COST ESTIMATES.....................................................................................................................................15
1 � Page
GFOA Research& Consulting Center
G l HECITY OF
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
IN'1'RODUCTION
This Recommended Plan of Action Report has been developed by GFOA to provide the
City of Dubuque, IA (the City) our recommendations on ERP scope, process
improvements, ERP implementation schedule, and also ERP software and
implementation cost estimates. Our findings and recommendations are based on our
business process analysis meetings that were conducted with City staff, key
stakeholders, GFOA consultants, and our past experience in the industry.
Key Findings:
❖ The City has clearly defined its project scope and is using collective decision
making to determine the scope and direction of the project.
❖ Staff appear to be motivated and enthusiastic about the project and how it can
help improve overall productivity and simplify daily business activities.
❖ The City's current finance system, FinancePlus, is outdated and lacks
functionality found in a modern ERP system.
❖ The City faces a lack of connectivity between its Finance, HR, and Budget
functions. This requires staff to enter the same data into multiple systems
and/or extract data from multiple systems, re-aggregate data, run calculations
outside of the system and re-enter the data in order to complete transactions or
develop needed reports.
❖ The City faces challenges generating financial and other reports. Many of the
reports used by management require multiple systems and are often generated
outside of FinancePlus.
❖ Security access in the current systems is often all or nothing. This makes
reporting difficult and often requires granting employees access to unnecessary
information in order to access needed information.
2 � Page
GFOA Research& Consulting Center
G l HECITY OF
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
OVERALI, SYS"1'EMS ASSESSMEN'1'
The City has made the strategic decision to move away from its current ERP System,
FinancePlus, for core business functions. Other systems such as Workiva W-Desk and
Cognos IBM (Reporting), Accela (Permit Tracking, Billing), NeoGov (Recruiting),
Laserfiche (Document Management), CommunityPlus, Utility Billing Plus, and various
MS Access databases and spreadsheets are also under consideration for replacement.
The City has chosen to explore the entire ERP marketplace, including vendors that cater
specifically to local government and those that provide ERP systems to various
industries. The City has also chosen to explore both Cloud/SaaS based deployments
as well as traditional "On Premise" solutions. GFOA agrees with the City's decision to
let the competitive market determine who can best meet the organization's needs.
GFOA recommends the City continue to focus on planning activities related to the
selection and implementation of an Enterprise Resource Planning (ERP) system as well
as Utility Billing (UB) and Community Development (CD) systems.
REDUCED REDUNDANCY
✓ Nearly all departments reported keeping redundant systems that duplicate data
and require double entry by staff.
✓ The City should reduce and/or eliminate redundant systems wherever possible
throughout the City by moving to an ERP System for core Financial, HR/Payroll,
and Budget related functions with direct interfaces to critical third party systems.
Functions such as Utility Billing and Community Development should also be
interfaced to the ERP system.
✓ The City should identify a single source of truth for critical records such as
vendor, employee and/or customer files. All other systems that use this data
should interface to the primary system of record rather than storing duplicate
records.
IMPROVED INTEGRATION
✓ Many departments need to maintain and report on financial information, but the
City's lack of integration between detailed transactional systems and the use of
redundant systems has led to manual data entry. The lack of integration has
also limited the availability of business process workflows.
✓ By moving to a modern ERP system, the City can increase integration between
many of its administrative systems and develop business process workflows for
high volume activities.
FINANCIAL, HR, AND GENER.AL ADMINISTR.ATIVE REPORTING
✓ The current system lacks the ability to run queries and ad hoc reports on much
of the City's administrative data.
✓ User access to general ledger and financial reports creates issues where users do
not have access to real-time financial data.
✓ Reporting capabilities for simple queries, detailed complex reports, and
dashboards can be found in modern ERP Systems.
3 � Page
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
BUSINESS PROCESS ASSESSMENT
GFOA has identified numerous examples where the City could benefit from a process
improvement effort. The City's processes are very common for an organization utilizing
a legacy financial system. Due to some system limitations, the City has created
workarounds and manual processes to accomplish necessary tasks, such as financial
reporting and tracking projects and grants. These processes can be inefficient,
inconsistent, and require significantly more manual effort than other comparable
organizations employing modern software. In other areas, the City has already improved
business processes and systems (e.g. NeoGov. for recruiting) to meet the needs of end
users.
Functional gaps can be addressed by improving processes and by implementing a
modern ERP system. GFOA noted the following for areas of potential business
improvements:
ACCOUNTING/FINANCE
• The City's current chart of accounts is outdated. Segments within the chart are
used differently by staff based on their departmental needs. Although the
finance department staff have a clear understanding of how to use each
segment in the account string, the segment descriptions are dated and are not
widely known throughout the City. GFOA has seen this occur in other cities as
the reporting needs of departments change over time.
• Many of the challenges the City faces with its account and finance business
processes are related to a lack of functionality. Journal entries for Balance
Sheet activity use a different account string than the General Ledger in the
FinancePlus System. Staff reported difficulty with posting grant related Journal
Entries to the correct period and finding descriptions of journal entries after
they are posted. Staff reported a high volume of old, sometimes inactive
account numbers. The use of manually generated numbering was also
reported.
• The City would like to move to the uniform chart of accounts for the State of
Iowa as part of this project. This change will simplify state reporting for staff.
• Some challenges are related to the manner in which the FinancePlus system
was configured. The bank reconciliation process is challenging due in part to
the number of bank accounts held by the City and the lack of corresponding
accounts within FinancePlus.
• The City should formalize a policy to enforce chart of account maintenance and
transactions in a decentralized manner. This policy should be designed to
prevent inconsistent use of the chart of accounts in the future after the
reporting needs of the City's departments have been addressed.
4 � ,' age
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
PROJECTS AND GRANTS
• The lack of grant and project functionality within FinancePlus has made
tracking and managing projects and grants challenging. This lack of
functionality has led to a large amount of redundant data entry, shadow
systems, and manual reporting processes.
• The development of policies and procedures for new grants (e.g. application and
tracking) could be beneficial. Grant related reporting at the department-level by
department personnel is difficult. This has led to department-level staff relying
on the Finance Department for basic information about their grants.
ACCOUNTS RECEIVABLE/MISCELLANEOUS BILLING
• Most of the City's miscellaneous billing is done by Finance utilizing Community
Plus. In addition, a small number of departments do manual billing.
• The centralized method or system for tracking invoices and/or payments owed
to the City is not strong. This means staff don't always know when payments
are due. In many cases, manual reminders are used to track when payments
are expected.
TIME ENTRY/PAYROLL
• The City faces challenges with activity costing. Staff are sometimes pulled from
their normal work to do small projects for other departments. In these cases
departments are supposed to charge each other for any related staff time.
However, tracking the hours is difficult because it's completely manual. In some
cases, departments do not charge the time back to the other department or
project, due to the level of complexity involved.
• Tracking project codes is also challenging, which in turn impacts the City's ability
to charge an employee's time to a give project. These are common problems found
in legacy systems like FinancePlus, in GFOA's experience.
• Staff report challenges with employees using the correct pay codes. Due to
system limitations, the wrong pay-code is often used/selected.
• Time entry related data such as overtime, on-call time, and time spent on
special projects is tracked manually.
• Payroll staff reported using manual audits for each individual timesheet due to
the frequency of errors that occur from using a manual process. This is
common in GFOA's experience when using an older system.
• Staff reported challenges tracking and managing longevity pay due to a lack of
functionality within FinancePlus.
• The system does not allow users to differentiate between salaried employees
and hourly employees. This has led to employees doing so manually which
makes the payroll process more complex and time consuming than necessary.
• All of the challenges reported by the City are common among legacy systems
like FinancePlus.
S � Page
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
HUMAN RESOURCES
• The City is using manual/paper files to track employee information. There is no
central location for employee information. Some employees have two files with
HR (Personnel and Medical) as well as an additional (third) file maintained within
their department. All of these files are manual/paper files. The use of multiple
files means staff are required to look in several different places to find the
information they need for evaluations.
• The lack of a modern Human Capital Management (HCM) system has led to
tracking required training and certifications for employees manually as well.
• While the existing FinancePlus system does have a position control module, the
City is not able to utilize it due to the way the HR module was set up to
accommodate Police and Fire bonus pay. As a result, position control is
managed and tracked with spreadsheets. This has led to a lack of visibility and
budget control with regard to positions.
• The current Open Enrollment process is mostly manual. In cases where
systems are used, the systems are managed by other organizations, such as
benefit providers. This means staff often cannot change things after they have
entered/submitted.
• Some departments are required to evaluate staff annually while others are not.
This leads to confusion amongst staff. Here, GFOA recommends considering a
more uniform policy with regards to personnel evaluations. This is both a best
practice and will work well with a modern HCM system.
• Managing pay changes is challenging. Due to the manual nature of the process,
pay charges and/or step increases are missed in some cases. This leads to
confusion and back pay, which requires additional manual calculations. Things
like longevity increases are missed in some cases, due to the lack of system
functionality.
• The City manually tracks ACA data with Cognos IBM reports. Staff are supposed
to do this at the department-level by tracking the number of hours worked by
each employee to determine if they are ACA eligible as well as to ensure they are
within the guidelines for things such as rules for seasonal employees and IPERS.
However, the manual nature of the process presents the opportunity for human
error and for late or delayed reporting.
• The City's recruitment system, NeoGov, does not directly interface with
FinancePlus, (the system employees are paid from). This means an applicant's
information must be manually entered into FinancePlus and other systems after
an applicant is hired. Staff reported a general lack of visibility throughout the
recruitment and new hire process. This is common in GFOA's experience with
organizations, like the City, that are using older systems.
PROCURE TO PAY
6 � Page
GFOA Research& Consulting Center
G 1 HE CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
• FinancePlus does not have vendor self-service (VSS) functionality. Staff
reported issues tracking tax information for vendors. Checking vendors T�
ID's for debarment is also manual, and time consuming.
• FinancePlus does not use/track or report by commodity code (e.g. NIGP code),
so reporting on the types of goods and services is much more time consuming
than it would otherwise be in a similar organization. Vendor information such
as addresses can be incorrect or out dated in some cases. Staff reported
challenges with sending payment checks to the wrong location. This is not
uncommon for organizations that do not use Vendor Self-Service functionality.
• The City does not currently use purchase requisitions to pre-encumber funds
due to challenges experienced in the past. The City does utilize purchase
orders to encumber funds. GFOA recommends that the City implement a
requisition process to serve as the first step in any procurement to pre-
encumber funds and to ensure that departments are receiving approval for all
purchases before the purchase is made.
• The bid process is completely manual which adds time and complexity to the
process and requires duplicate data entry in various places.
• Staff reported challenges scanning receipts and other supporting documents
into the U.S. Bank p-Card system. It appears this may be both a functional
and employee training challenge. GFOA has observed this with other cities like
Dubuque and recommends the City use this project as an opportunity to
ensure employees are trained on all relevant systems as well as improve
technical functionality in the systems it uses.
• Reconciling Purchasing Card data with information in FinancePlus is
cumbersome. Staff reported a desire to shorten the time between the original
purchase, and when the reconciliation within the primary financial system
takes place for P-Cards. GFOA believes the addition of a modern ERP system
that can use more of the information provided by the bank will help with this
challenge.
• Staff reported challenges tracking critical dates related to contracts. Most dates
are being tracked manually at this time and emails are being sent to staff as
reminders when important contract dates approach. GFOA has seen this
approach with other cities. The adoption of a modern ERP system should vastly
improve the tracking and managing of dates and documents, and the
communication with department and vendors. The City also has various
revenue generating contacts, but manages these manually due to a lack of
functionality within FinancePlus.
• Functionality around purchase orders and change orders is limited.
• Matching (e.g. 2-way and 3-way match) does not occur in the system, and it
therefore not used by staff currently. The City could benefit from modern
procure-to-pay functionality within an ERP system. This would allow the City
to leverage things such as automatic budget checks, pre-encumbrances,
encumbrances, 3-way matching, strategic sourcing, vendor self-service, and
other industry best practices.
7 � Page
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
• The accounts payable function within FinancePlus is limited. This has led to
staff tracking accounts payable info in the vendor file.
BUDGET
• FinancePlus does not have budget management functionality capable of ineeting
all of the City's needs for forecasting, budget analysis and development, and
capital budget planning. To fill this gap in functionality, the City utilizes other
systems, including Workiva WDesk and Microsoft Excel. While there is some
interface between FinancePlus and these other tools, there is not a direct
connection. As such, the most comprehensive and most up-to-date data may not
be easily available for analyzing and developing the budget.
• The Budget module within FinancePlus does not have the same functionality as
Core Finance portion of FinancePlus. This means staff cannot upload all
personnel costs (benefits and salaries) for all departments into the system at the
same time. Data used to develop the budget must be completed in steps/phases.
This causes the process to be extremely time consuming.
• Development and management of the capital budget is an extremely manual
process. Staff are required to use various third party systems that do not
interface to each other to build and track the capital budget.
• The City should conduct all capital and operating budget development within the
ERP system in a future solution. This would include all related activities such
as budget request, scoring, forecasting, versioning, etc.
ASSET MANAGEMENT
• The tracking of capital assets is done differently in each department. This makes
the overall financial management and reporting of capital assets difficult.
Departments are using multiple systems to track and manage capital assets at
the departmental-level, which means staff must enter the same data manually
into lots of different systems, and spreadsheets as well as into FinancePlus and
perform reconciliations between systems.
• FinancePlus does not automatically flag items above the capitalization
threshold when they are entered (built or purchased).
• Tracking non-capital inventory (e.g. high-dollar items that are below the
capitalization threshold) is challenging or non-existing for most departments.
• The systems used for work order management (Web-QA and Cartegraph) are
not integrated with the ERP system, FinancePlus. This makes activity costing
much more difficult and limits the City's ability to charge employee time directly
to a specific work order.
• Inventory is tracked in several different systems. Staff reported a desire for
modern inventory management functionality as part of a future ERP system.
• The City could benefit from the development of a standard, detailed, capital asset
file that is housed within the ERP system.
8 � Page
GFOA Research& Consulting Center
G l HECITY OF
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
TREASURY
• Many of the systems used throughout the City (e.g. POS, TPS) are not integrated
to FinancePlus.
• Manual forms are used to identify, document and track revenue and receivables.
They are then manually added in to FinancePlus in some cases.
• The City's cash receipting process faces some challenges. Accounting for the
payments, depositing the payments in the bank, and updating the City's books
can vary in some cases. The lack of uniformity can cause time delays and require
more manual effort than would otherwise be needed in a modern system.
• The City could benefit from modern treasury functionality which can be found
within an ERP system. This would allow staff to improve accounts receivable
management, forecasting, cash flow management, cash handling, and other
treasury functions.
RECOMMENDATIONS
As the City prepares for the purchase and implementation of new ERP, UB, and CD
systems, GFOA recommends that the City focus on activities that will help prepare the
organization for an informed and methodical purchasing process and a successful
implementation. To this end, GFOA has developed a listing of recommendations based
on our business process analysis meetings, our past experience, and discussions with
key City stakeholders. These recommendations can be grouped into several categories
which are listed below. GFOA is aware that some of the activities have already begun
and strongly encourage the City to continue them throughout the life of the project.
• PIT Crews: PIT Crews should meet to discuss and address recommendations
that will impact their functional areas.
• Planning: These recommendations are related to overall planning activities.
• Governance: These recommendations are related to the City's project
governance structure.
• Policies & Procedures: These recommendations identify areas where the City
should focus on creating or updating rules, policies and procedures.
• Training: These recommendations will impact future ERP, UB, and CD
training activities.
• Goals: These recommendations will impact the City's project goals.
PIT Crews: Continue to Meet on a Regular Basis
By maintaining the PIT Crews and empowering them to work independently, the City is
fostering the development of a diverse group of the City's leaders and functional and
technical experts. In addition, developing change agents in all parts of the City will
facilitate organizational change and broader adoption of the new system. The teams will
9 � Page
GFOA Research& Consulting Center
G l HECITY OF
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
build momentum for achieving the organization's goal of maximizing the investment in
a new system and avoiding costly customizations, shadow systems, and inefficient
processes. PIT Crews will accomplish their goals by doing the following:
• Using GFOA recommendations as a starting point to create business process
recommendations for review by Steering Committee
• Review GFOA-recommended system requirements and help finalize any needed
documents for the software and implementation services RFP
• Develop business cases to help describe required software functionality to serve
as examples during software demonstrations, support RFP development, and
use during implementation, testing, and training
• Develop readiness strategies for positioning the City for implementation
• Develop business process documentation including:
o Listing of future workflow roles
o The legal, regulatory, policy, and contractual framework
o Trends, issues and pending decisions which could affect the City's ability
to adopt new business processes, or be relevant to software
implementation
o Collecting reference documents which will be needed during system
configuration
• Serve as change agents (e.g., bring key messages to buildings and departments,
etc.)
Planning & Goals: Redesign and Plan for a New Chart of Accounts
GFOA strongly recommends that City use the ERP implementation project to review and
revise the existing chart of accounts segments and values. GFOA understands that the
City is looking to move to the Iowa Uniformed Chart of Accounts for Cities and Counties.
However, GFOA still recommends that the City consider its own reporting needs as well
in the process. GFOA has developed the following guidelines to assist governments in
developing a new chart of accounts as part of an ERP implementation. GFOA
recommends that organizations take the opportunity of the ERP system implementation
to maximize their new chart of accounts.
Below are GFOA's chart of accounts recommendations
• Define Each Segment. Each segment of the chart of accounts should have a
strict definition that can be communicated and enforced. This will present
internal inconsistencies within the chart of accounts. For example, the City
should universally agree on the definition of a "fund," "department," and
"program," and not allow any exceptions.
• Start Over. When developing the chart of accounts, the City should start fresh
and not concern itself initially with mapping back to the existing chart of
accounts. The City should be reviewing its actual organizational structure and
operations, compare this will the Iowa Uniform chart of accounts and build the
10 � Page
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
chart off of that rather than its existing chart of accounts. This will prevent the
City from replicating a problematic chart of account structure.
• Start Simple and Build-Out Detail. The City should identify major categories
within each segment and then work to build out detail. Again, this will help the
City take a fresh perspective to the chart of accounts and prevent any
unnecessary replication of the old chart. For example, when developing the object
code listing, the City should first identify major object code categories and then
work to define detail to the extent necessary. This will also help ensure that the
overall chart is organized and should not contradict any of the guidance form the
Iowa Uniformed chart of accounts.
• Do Not Store Unneeessary Data. A well-developed chart of accounts will not
require new accounts to be created often. Most organizations attempt to keep
the chart of accounts relatively simple and high level, and then utilize other
components of the system (e.g., the project ledger, AR charge codes, salary pay
codes, work order detail, etc.) to further define the detail. This will reduce the
amount of maintenance required on the chart of accounts
• Do Not Repeat or Bring Forward Ineffective Numbering or Accounts. Simply
being able to convert chart of account data or having the ability to re-use existing
number schemes is not always beneficial. As part of training and change
management activities for the ERP implementation, many policies, processes,
and systems will be changing. The City's end-users will eventually learn the new
chart and dealing with the initial learning curve is better than dealing with
inefficiencies in the new chart of accounts for the length of time the new ERP
system is used.
Governance & Training: Establish Communication Plan and Project Charter
One important element to enterprise projects is to establish a communication plan and
make it a priority to ensure that the organization stays informed. As the project
progresses the City will want to perform some form of stakeholder analysis to determine
the level of engagement and frequency of communication needed for various groups of
stakeholders throughout the City. This is typically done with a Stakeholder Analysis
Matr�.
A complete and comprehensive Stakeholder Matr� will also help the City with training
on the new ERP, UB, and CD systems by identifying who needs to be trained on which
parts of the new system. It can also help with determining when and how they should
be trained.
The project charter is also a critical part of the governance framework as it will clearly
define the project goals and governance structure. A critical component of the charter
is that it should clearly define the roles of the parties included in the governance
structure. Having roles clearly defined will ensure that all parties understand their role
and expectations on the project. For example, the role and expectation of the Steering
Committee should be clearly defined so all parities understand their role during the
11 � age
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
actual implementation. GFOA strongly recommends the City finalize and sign the
charter and have all parties agree to their respective roles.
Policies & Procedures, Planning: Change Management, Roles, Etc.
As the City ramps up its efforts to prepare for this multi-year implementation project
there will be other considerations besides those highlighted above that the City will want
to address:
• Assign a Change Manager. All organizations experience change management
issues through the duration of an ERP system implementation project. GFOA
recommends proactively addressing those issues as they arise. While a potential
implementation partner may provide a change management resource, GFOA
clients have found success with identifying an internal change manager that has
an awareness of existing City policies and procedures, and who is also in a
positon to influence key decision-makers. This resource will work in coordination
with the PIT Crews to ensure that barriers to project success are removed in a
timely manner.
• Develop Policies and Procedures. GFOA provided the City with a number of
process and policy recommendations to consider in the `As-Is' Business Process
Maps and Narrative documents as well as the `To-Be' Business Process Maps.
GFOA also provided Policy Change Recommendation Tracking Forms, Workflow
Development Trackers and other tools to help facilitate the documentation of
policy change recommendations. GFOA encourages the City to continue to
document all process and policy changes and develop procedures to support
these modified or new processes.
• Identify System Roles. While this task cannot be completed until a system is
selected for implementation, City staff have expressed interest in cataloging
existing user roles to determine if any gaps exist within current business
processes and any potential future processes. This analysis will help the City
determine workflow approval paths as well.
SCOPE & SCHEDULE
SCOPE CONSIDER.ATIONS
GFOA recommends that the City's scope for its project include the functionality listed
in the table below. The scope of the project will deliver a "core" system that can provide
the capability to handle essential functions for finance, procurement, HR/payroll, and
other related areas. Utility Billing and Community Development are also
considered in scope by GFOA for this project at this time. GFOA recommends the
city use a competitive procurement process that will provide the City with the most
options.
12 � Page
GFOA Research& Consulting Center
G l HECITY OF
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
The "core" functions found within the ERP system are modules or functions so tightly
integrated that removal of the module would have adverse impacts on the other "core"
modules. For example, an employee's pay (HR/Payroll) and their paycheck (Finance) are
heavily dependent on each other make integration between the two extremely important.
Features not included in the core modules would then be treated as additional scope
and would be viewed as sub-projects. All planning and implemented would likely take
place after the initial sequence of phases are completed for the Core ERP system. For
example, while the City has a desire for Utility Billing (UB) and Community Development
(CD), GFOA recommends the City identity these as "Additional Scope" within the RFP.
Scope for ERP Implementation
Core ERP
Financials Human Resources
Accounting Personnel Actions
o General Ledger o Recruitment
o Project Accounting Benefit Administration
Budget Leave Management
Treasury o FMLA
Customer Billing Payroll
o Accounts Receivable Time Entry
o Sales Tax Collection o Scheduling
Procure to Pay
o Accounts Payable
o Contracts* (Bi-Directional)
Assets Management
o Work Orders
o Invento
Additional Scope Additional Scope
Utility Billing Community Development
o Permit Application
o Building Plan Reviews
o Licenses
o Inspections
o Code Enforcement
Note: GFOA identified over sixty (60J sub processes that would roll-up to one of
the major functional areas listed above. All sub processes are not listed in the
table above.
GFOA recommends that the scope of any RFP include all core functionality and that the
RFP require that vendors provide a complete set of functional responses. This will
facilitate a more competitive response to the RFP.
As part of any project, both the City and its chosen vendor will be collaborating on
providing services necessary to successfully implement the new systems. With every
13 � Page
GFOA Research& Consulting Center
G 1 HE CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
project, the exact split of work effort between vendor and the government organization
is different. In addition, GFOA has identified the scope of the vendor's implementation
services to be the largest differentiator among different vendors in the market, as well
as a key determinant in project success.
Some vendors approach the project as each party having shared responsibility and truly
working together at every step to complete tasks. Other vendors approach the project
as a "training" project where vendor staff only have a responsibility to train City staff on
features of the software, not for outcomes of the project. GFOA recommends that certain
aspects of the implementation be "required scope" for the project. Ultimately, while this
project involves software, it should not be treated as a software project. These
components help shift the focus away from strictly technology to include business
process, project management, and organizational transformation.
These essential project components for the City include:
• Projeet management. Active project management, management of a detailed
project plan, issue/risk tracking, deliverable acceptance, and regular status and
steering committee meetings.
• System design / business process documentation. Preparation of a complete
system design document that includes both system configuration documentation
and City process documentation.
• Technical implementation. Implementation team staff from the vendor that is
responsible for technical details related to implementation (not utilizing support
staff or remote department that handles all work).
• Functional configuration responsibility. Vendor staff should have primary
responsibility for configuration of the system and delivering a system that meets
the functional requirements. City staff can assist, but the vendor should be
responsible for the delivered system.
• Accountability for requirements. The vendor should be responsible for
tracking the completion of project requirements. This maintains an "outcome"
focus on the results of the project so it does not get lost in the software details.
• Interfaee development. Detailed requirements for a pre-determined list of
interfaces. The vendor should have responsibility for leading the interface effort.
• Data eonversion. The vendor should play an active role in data conversion.
GFOA would caution against "over-converting" data, but some data conversion is
critical.
• Reporting. Listing of required reporting from the system. This list should include
any financial reports.
• Training. Vendor led training sessions for City staff at the start of the project and
assistance in preparing end-user training materials based on the City's
configured system.
• Deliverables (work products). The project should define expectations for critical
deliverables including: project plan, system design document, testing scripts,
interface specifications, and training materials.
14 � .� age
GFOA Research& Consulting Center
G ,t�,,r���
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
SCHEDULE CONSIDER.ATIONS
To mitigate the risk of the large change that the City will face during the implementation,
and to reduce the impact of limited resources on the project, GFOA recommends
phasing the implementation project with two sequential phases (plus a defined
readiness phase). Phases would overlap slightly, but would be staggered to create three
go-live events. The table below represents the recommended ERP implementation
schedule for the City.
GFOA recommends the City implement the core ERP functions in two primary phases
over approximatelyl8 months, with Financials (phase 1) starting first and lasting about
nine months. Human Resources and Payroll (phase 2) would start sometime after and
last approximately nine months as well.
Finally, Utility Billing and Community Development would start after HR/Payroll is
nearing completion. GFOA recommends each major phase take between nine and twelve
months. The City will likely receive recommended implementation schedules from
vendors where the City can implement and go-live on all functions in a year. This "big-
bang" approach will allow the City to go-live early, but it will also require the necessary
City resources to be assigned and available on the project at the same time.
PHASE 2ozo zou zo2z
Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2
A Readiness/RFP
1 Financials
2 HP/Payroll
3 Utility Billing
4 Community
Development
Resource planning for ERP projects is a critical component to ensuring the City's goals
are met during the implementation and long into the future. As stated earlier in this
report, GFOA recommends the City plan and document the resource needs, including a
backfill plan for City resources that will be working on the project if needed. GFOA
recommends the City plan for the PIT Crews to actively participate in the
implementation, particularly during the design, prototyping, testing, and training
phases of the implementation.
COST ESTIMATES
Based on GFOA's understanding of the City's scope, goals, implementation preferences,
and current market conditions, GFOA has prepared the following cost estimates. Cost
ranges are fairly broad to accommodate the wide range of vendors that are likely to
15 � Page
GFOA Research& Consulting Center
G 1 Ht CITY Of
Dus E
Mnste�yiete on the MississiPP�
Plan of Action Report ERP, UBS, CD System Replacement Project
submit proposals. ERP cost and services have a direct relationship. Some
implementations will focus on "getting the system live," while others will spend time
with business process improvement, project management, quality assurance, and other
aspects of"complete" projects. In estimating costs for the City, GFOA was also required
to make assumptions about the complexity of implementation and about decisions that
the City would make during the procurement process and contract negotiations. GFOA
can also advise the City on areas that are likely to increase/decrease potential costs.
• Low Estimate vs High Estimate - It is important to realize that GFOA cost
estimates attempt to forecast costs across all viable products in the market.
Given that no two products are the same or implementation approaches are
identical, and because pricing differences can relate to value differences in the
products and services, the City should not view pricing as an estimated range on
a single product or vendor, but rather the spread of costs from proposals that will
likely be received. GFOA believes that products represented by both the low
estimate and high estimate could fully meet the City's current software needs
(assuming business process change). GFOA expects the City's costs to fall in the
middle of the low and high estimates.
GFOA's estimates are based on vendors proposing a Software as a Service (SaaS) model
where the City will "lease/subscribe" the software and make annual payments to the
software provider. Under this model, the City will not purchase the software license
outright. The SaaS model is now the preferred model for most, if not all, software
providers. In addition, the SaaS model is also preferred by governments as it does not
require a tremendous amount of IT support, as the software is hosted in the cloud and
maintained by the software providers.
Note: Cost estimates do not include internal staffing costs or hardware costs.
Software (SaaS)Annual $ 240,000 $ 400,000
Implementation Costs $ 750,000 $ 2,700,000
Prolect Cost(Year 15aaS& Implementation) $ 990,000 $ 3,100,000
Software (SaaS)4 Years $ 960,000 $ 1,600,000
5 Year Cost of Ownership $ 1,950,000 $ 4,700,000
Overall, GFOA strongly believes that long term costs will be much less with a successful
implementation and recommends that all governments (including the City) take into
account overall risk, process improvement, ability to achieve project goals, and long
term stability along with the initial project costs. Typically (but not always) with ERP
projects �ou qet what�you pa�for.
16 � Page
GFOA Research& Consulting Center
� • � � � • �
THE �TI'�C �F
� • � �
�� � �
�V1�����~��e�� �� ��e II�It��z�s���r� � • ' • • . �
Government Finance Officers Association
�
� �
• � I • � � � � � I
• GFOAwas established in 1906
• 20,000+ members
• Offices in Chicago and Washington , DC
• Product and services
• Consulting . . �
• Publications
• Training S P O N S O R S
• Award programs
• Best Practices �
�e���f�c��e
°�c��eve����
u�ry.:_. ._:�.,. . -��:�:. ._.- __ �. _. �
��������� ���������
• • • ' - • • • • •
• Started providin technolo procurement and
g gy
implementation advisory services in 1998
• GFOA has become a nationally recognized leader in the
E RP system market generati ng val ue for cl ients by:
• Reducing costs
• Reducing risk . ' •� • �
. .
�. •� • . . �.
• Providing best practice ' '����• • •� �' d
, _ .
:r� • . , . .�� . . v�.i�
�� • � •
•• �.� • �� ���
recommendations : . �, ' ' ,, : ' ' :�r.-.
.
' ,� •.�
�!�'�� •
`•ii�� • i •
�• • �i
�� � • S
•
• i�,.
• • ��c�
i
����
•
� �
I
Human �
Resources/
Budget
Payroll
Financials " Planning"
" Enterprise Resources"
� � • • �
Business Performance
Training/ Risk Applicant Employee Management
Learning Management Tracking Records Intelligence
Pension Time Entry Position payroll Forecasting
Admin Control
Budget
H R/
Performance Employee Payroll Budget Prep
Evals Benefits Relations
Finance
General
Customer Ledger
Business Relationship Work Fixed Assets
License Management Orders
Accounts Accounts
Utility Billing Payable Receivable Contracts
Permitting Purchasing
Grants
Property I Projects
Management
Special Cashiering
Assessments Property Tax P-Cards
Bids Self-Service
I � � I • � � ' � � �
I / � � / / I
�
� �
� � � •�
� � � i�
� � • �
� �
�
.I-..�,hw {i7Y{/dNdl "IM55'� # 5f p
� , ' f-� �
� .a xw-�.� � ♦ �
� �
- � ��� �
� � n..��o.. �.� ��w, c•.�} fA
� � ....«�,»�.». . . , ..,,,�
��«.�
�,� .,..dw..d ; r„b.,+�.,,
� .: �.�,..�,�,a.
tw.n.nsw. _ .,a_.�..W..
� tr.r.ie..r..�.oa, . ..w�w�.�.�..,�.�
— ti.�..r+,�.+,�.a..
� #INxYRTN�4 r+ri�'Ivv�cri
� a+rtr�v�
* . �
�
�
/ �
�� � ,
� •�
_ •� - •�
•� •�
� � •� �
� �
_ � � � � _ I � � � I I I �
o Out-dated chart of accounts
o Difficu It bud et reporti n
g g
o No centralized trackin for receivables
g
o Extensive use of paper
■ Employee files %
�
■ Time entry �.
� �
■ Payroll , � � - r �
��� �
• � �
■ � � �
Cash receipts -- � _
, -
�
f � �
� ,.- �
�-e�,
• • • • - � � -
o True enter rise-wide re ortin m� �
p p g �
� �
.
. .a._..
■ "One version of the truth" `� µ��s� - f2e� 19���
01
?],1% �39.
�=n.r maurc rmm�� �.., " ��nu 6.
O'�
' " o.
■ Sharin of information 3P� ���
_ � �.
g �
=_ o
■ Transparency � �
o Business process support for all departments ,���K��.
■ Wo rkf I ows ._e . 8 9 0 0 �
F..... .�a ....,. �.���.. .....,�...
■ I nternal controls o�F'�g"�8 �fe��N�
o��vo,�a
■ Budget monitoring °P��tl51�B8
o�o���,�o�.
�. ,�..a,a��_
o Modern technology
. �
e c u r�ty ..,��.��.�e..ee�.�
�wo,.b.a .. � �;�q ,, < y.�...s�oo�M. >
■ Mobile applications ��a� ,:� 15 �� �p� : ..
...�.�. # 9 n��15 -z PM��
■ ■ ■ ■ ■ .�r. � i_i.,.,,,., �� . .
�
D�saster recove and bus�ness cont�nuit .._.�. -. _
� y ...n � .. - :m._�.. �...m :
-waaxw.wm..�.�M. a �.�"
■ Use of "cloud" technologies o�o„�� ��.. �� ,. .
� • � � � ,
• - � �
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr
Task 0: Pro'ect Plannin
Task 1 : Process and S stem Anal sis
Task 2: Develop a Plan of Action to
Im lement S stems
Task 3: Develop Request for Proposal
RFP
Task 4: Vendor Selection
Task 5: Contract Ne otiations
Task 6: Im lementation Transition
■
ro ec co e
Community
Financials HR/Payroll Utilit Billin Develo ment
Y g p
Accounting Human Customer Permitting
Procurement Resources Accounts Plan Review
Payables Position Meter Reads Inspections
Receivables Control Billing Code
Capital Assets Benefits Payment Enforcement
Business Time Entry Process Customer
License Payroll Relationship
Budget Management
� � � • � • � . •
Project Cost Estimates: $990K to $3 . 1 M (software and
implementation costs ; does not include costs of internal staff
involved in implementation )
. •
. . - . . � . $240,o00 $400,o00
• - - . . . $�50,000 $Z,�oo,000
. � _ � _ •
. . . .
• - - . • 990 000 $3, 100,000
Project Implementation Timeline: 2 — 2 . 5 years
I I � � I
� � � � � ��
� :.
� � -- - VS. � ���
� �
P [1 T T F I� � P� � I� N
o Implementation approach drives cost.
■ Some firms will propose a more full-service, hands-on approach that will
require more consulting hours and will result in a higher cost proposal .
■ Other firms will propose that City staff play a more active role in the
implementation , meaning fewer consulting hours and more City staff
hours.
o There is also variation in software costs, but to a lesser extent.
� � � � � � � � � � �
o Vision and Focus on Business Process
■ Identify project goals
■ "Why are we doing this?"
■ Adopt process improvements and best practices
o Planning and Governance
■ Governance / project management
■ Staffing
o Documentation of Detailed Requirements
■ RFP
■ Requirements
■ Business Process
o Accountability
■ Requirements traceability
■ Testi n g
■ Quality assurance