HomeMy WebLinkAbout03/28/2013 - Workshop Agenda Packet - City CouncilTable of Contents
Agenda 2
Workshop No. 5 - Enterprise Resource Planning (ERP)
Upgrade/Replacement Consultant Report
Coversheet_Revised 5
Remediation and Action Plan 6
Workshop No. 6 - Wellborn Area Plan
Coversheet Revised 63
1
March 28, 2013
Workshop Agenda Item No. 5
Enterprise Resource Planning (ERP) Upgrade/Replacement Consultant Report
To: Frank Simpson, Interim City Manager
From: Ben Roper, IT Director
Agenda Caption: Presentation, possible action, and discussion regarding the report and
recommendations from BerryDunn Consultants regarding analysis of the current city ERP
software and recommend alternatives.
Relationship to Strategic Goals:
1. Financially Sustainable City
2. Core Services and Infrastructure
Recommendation(s): Staff recommends acceptance of the Report, and requests Council
direction to proceed as recommended or alternate direction.
Summary: At the December 13th meeting, Council was briefed on plans to assess and
evaluate the city's Enterprise Resource Planning (ERP) software applications. Since that
briefing, the BerryDunn Consultant Team met with city staff during the week of Jan 7-11.
The draft Needs Assessment and Gap Analysis Report was delivered to the city on Feb 18th
and reviewed by staff. Clarifications and changes were discussed with the City and
BerryDunn project teams. The final Needs Assessment and Gap Analysis Report was
delivered to the City in mid-March. Based on the Needs Assessment and Gap Analysis, the
BerryDunn team developed a recommended Remediation and Action Plan.
Budget & Financial Summary: Delivery and briefing of the Remediation Action Plan
completes the contract with BerryDunn in the amount of $103,550.
Reviewed and Approved by Legal: Yes
Attachments:
Remediation and Action Plan
5
Consultation on ERP Assessment and Evaluation
Remediation andAction Plan
March 19, 2013
Prepared for:
City of College Station
1101 Texas Ave
College Station, TX 77842
6
Remediation and Action Plan Report Page 2 March 19, 2013
City of College Station
Remediation andAction Plan
Table of Contents
Section Page
Executive Summary .............................................................................................................. 4
1.0 Introduction .............................................................................................................. 7
1.1 Project Background ............................................................................................................... 7
1.2 Report Format ....................................................................................................................... 8
1.3 Work Performed .................................................................................................................... 8
2.0 Needs Assessment Summary and Options ......................................................... 14
2.1 Identified Challenges ........................................................................................................... 14
2.2 ERP Improvement Options .................................................................................................. 17
2.3 Option 1: Implement the Newest Version of SunGard’s ERP Software (ONESolution) ........ 17
2.4 Option 2: Issue a Request for Proposal for a New ERP System .......................................... 19
3.0 Cost Analysis .......................................................................................................... 21
3.1 Request for Information Approach ....................................................................................... 21
3.2 Cost Findings ...................................................................................................................... 22
3.3 Additional Costs .................................................................................................................. 28
4.0 Implementation Timelines ..................................................................................... 31
4.1 Implementation Timelines .................................................................................................... 31
4.2 Implementation Considerations ........................................................................................... 31
4.3 Factors and Methods of Successful Implementation ............................................................ 32
4.4 Summary of Considerations ................................................................................................ 33
5.0 Consideration of Options ...................................................................................... 34
5.1 Sources of Modules in Each Replacement Option ............................................................... 34
5.2 Additional Considerations .................................................................................................... 36
6.0 Recommendations and Next Steps ....................................................................... 38
6.1 BerryDunn Recommendations ............................................................................................. 38
6.2 Collect Functional and Technical Requirements .................................................................. 39
6.3 Document Existing Business Processes .............................................................................. 39
6.4 Develop a Request for Proposal (RFP) ................................................................................ 39
6.5 Evaluate and Score RFP Responses ................................................................................... 39
6.6 Conduct Vendor Demonstrations ......................................................................................... 39
7
Remediation and Action Plan Report Page 3 March 19, 2013
6.7 Identify Preferred Vendor and Conduct Contract Negotiations ............................................. 40
6.8 Implementation Project Management planning .................................................................... 40
6.9 Project Timeline ................................................................................................................... 41
6.10 Recommendations Summary .............................................................................................. 41
Appendix A: Detailed Challenges and Needs ................................................................... 43
8
Remediation and Action Plan Report Page 4 March 19, 2013
Executive Summary
This section contains an executive summary of the Remediation and Action Plan.
The City of College Station has been using the SunGard Public Sector (formerly HTE) product (SPS)
for several years for its Finance, Planning and Development Services, Utility Customer Billing, and
Human Resources (HR) functions (including payroll). Due to the continued expansion of functionality
used by City staff and the continued growth within the City, College Station’s current SunGard Public
Sector installation is believed to be one of the larger population centers using the SPS product. Over
the past 20 years, City business needs have continued to evolve and as a result, the City has decided
to reassess its current systems ability to support the changing business needs. The City’s goal for this
project is to conduct an evaluation of the current SPS system to determine if the City should continue
using the current SPS product, upgrade to a newer SunGard product, or purchase a new Enterprise
Resource Planning (ERP) system.
In December 2012, the City of College Station retained Berry Dunn McNeil & Parker, (BerryDunn) to
provide enterprise resource planning (ERP) assessment and evaluation consulting services to assess
the ability of the City’s current system to meet its business needs. A Needs Assessment and Gap
Analysis report was developed that considered the following issues:
• The effectiveness of the current software in meeting business needs
• Existing department-specific data or data needs that may be incorporated into a citywide ERP
solution
• The ability of existing stand-alone systems to effectively integrate into an ERP system
• The effectiveness of existing hardware in supporting an ERP system
• The current staffing support for the ERP system and identify potential staffing efficiencies that
may be recognized with implementation of a new system
• Other relevant system or resource components/issues that may factor into the decision
process for adopting a new ERP system
BerryDunn facilitated fact-finding meetings with department users of current City systems, specifically
SPS. The purpose of these meetings was to follow up on information previously provided, document
high-level functional requirements necessary to meet the City’s needs, understand the current
business processes associated with the use of SPS, and identify challenges with the use of SPS.
During the process to identify and document challenges in the current environment, many recurring
themes related to the challenges in the current environment were identified. The list below describes
the high level themes identified. (Additional detail related to challenges and business needs is detailed
in Appendix A: Detailed Challenges and Needs).
• Many City departments are using manual processes to track information outside of SPS.
• Expenditures cannot be tracked in SPS with sufficient detail
9
Remediation and Action Plan Report Page 5 March 19, 2013
• Budget preparation typically involves the use of MS Excel spreadsheets
• Workflow functionality is not meeting all City needs
• The City does not have (or is not using) modules to support certain business functions
including Grant Management, Contract Management, and Project Accounting.
• Time entry for the payroll process is largely manual
• City IT staff spend time supporting business processes involving SPS.
• Disparate systems do not promote data sharing
• An overall lack of functionality in SPS has created the need for manual processes
• The inability to drill down to source information including supporting documentation creates
numerous challenges
The second phase of the ERP assessment and evaluation project was to develop a Remediation and
Action Plan report (this report) that is a comprehensive plan of action to implement solutions identified
to challenges and needs identified in the Needs Assessment and Gap Analysis report. The report
includes a prioritization of solution requirements, potential for phase in of solution implementation,
suggested time frames for implementing the project, and estimated costs for potential replacement
options, including equipment and training.
BerryDunn has identified two options the City could consider to improve the existing ERP
environment:
• Option 1: Review the newest version of SunGard’s ERP software (ONESolution).
• Option 2: Issue a Request for Proposal (RFP) for a new ERP system.
The City has the option to perform business process analysis and redesign with the current SPS
system, implement SunGard’s ONESoution or purchase a new ERP system. Based on the business
needs and the challenges documented by both BerryDunn and City staff, BerryDunn
recommends that the City proceed with Option 2.
The City should begin the process of identifying the staff that will be part of the procurement selection
team as well as the project management teams, subject matter experts, training and technical support
teams. It will also be important for the City to begin planning for business process changes as a result
of the new ERP system by beginning change management activities to increase the likelihood of
acceptance of business process changes throughout the organization.
It is important that the City establish the proper expectations within all levels of the organization prior
to initiating an ERP implementation. There are several reasons why large-scale system
implementations are not successful. Failure to set proper expectations and the lack of an adequate
staffing and resource planning can negatively impact the project from the beginning and create project
risks that adversely affect the project outcome.
10
Remediation and Action Plan Report Page 6 March 19, 2013
The City should be commended for undertaking this study and proactively assessing its existing
systems and business processes. In many instances City staff openly acknowledged business
processes that needed to be addressed due to their inherent inefficiencies. Many organizations do not
embark on the level of planning and analysis the City is undertaking and, as a result, end up with a
systems environment that does not support organizational business functions. The executive support
and staff involvement in this phase of the project can help build the foundation for future collaborative
decision making.
11
Remediation and Action Plan Report Page 7 March 19, 2013
1.0 Introduction
This section of the report describes the format of the report, background events leading up to the
project and the work performed as part of the project.
1.1 PROJECT BACKGROUND
TheCity of College Station has been using the SunGard Public Sector(SPS) (formerlyHTE) product for
several years for its Finance, Planning and Development Services, Utility Customer Billing, and
Human Resources (HR) functions (including payroll). Due to the continued expansion of functionality
used by City staff and the continued growth within the City, College Station’s current SunGard Public
Sector installation is believed to be one of the larger population centers using the SPS product. Over
the past 20 years, City business needs continued to evolve andas a result, the Citydecided to
reassess its current systems ability to supportthe changing business needs.The City’s goalfor this
project is to conduct an evaluation of the current SPS system to determine if the City should continue
using the current SPS product, upgrade to a newer SunGard product, or purchase a new Enterprise
Resource Planning (ERP) system.
In December 2012, the City of College Station retained BerryDunn, to provide enterprise resource
planning (ERP) assessment and evaluation consulting services to assess the ability of the City’s
current system to meet its business needs. A Needs Assessment and Gap Analysis report was
developed in February 2013 that analyzed the following issues:
• The effectiveness of the current software in meeting business needs
• Existing department-specific data or data needs that may be incorporated into a Citywide ERP
solution
• The ability of existing stand-alone systems to effectively integrate into an ERP system
• The effectiveness of existing hardware in supporting an ERP system
• Assess the current staffing support for the ERP system and identify potential staffing
efficiencies that may be recognized with implementation of a new system
• Determine other relevant system or resource components/issues that may factor into the
decision process for adopting a new ERP system
In addition to the focus areas listed above, the City has also identified a need for integration (or at a
minimum, interfacing) with other third-party applications in use, including the City’s document imaging
system, parks and recreation management system, public works and water services asset and work
management system, geographical information system (GIS), fuel management system, utility meter
reading system, municipal court software, and the Microsoft Office Suite.
The second phase of the ERP assessment and evaluation project is to develop a Remediation and
Action Plan report (this report) that is a comprehensive plan of action to implement solutions identified
to challenges and needs identified in the Needs Assessment and Gap Analysis report. The
12
Remediation and Action Plan Report Page 8 March 19, 2013
Remediation and Action Plan includes a prioritization of solution requirements, potential for phase in
of solution implementation, suggested time frames for implementing the project, and estimated costs
for potential replacement options, including equipment and training.
1.2 REPORT FORMAT
This report is comprised of six sections and an executive summary, as described below:
1. Introduction. This section describes the background of the project leading up to the report,
the format of the report and the work performed in the development of the report.
2. Needs Assessment Summary and Options.This section of the report summarizes the needs
and challenges identified in the Needs Assessment Report and summarizes the options that
the City has to address the challenges.
3. Cost Analysis.This section of the report contains the analysis of the estimated costs provided
by ERP Vendors as a result of the Request for Information process.
4. Implementation Timelines. This section of the report outlines the potential timelines the City
may follow for the implementation of each option.
5. Consideration of Options. This section of the report describes the pros and cons of each of
the replacement options identified in section 2.0 Needs Assessment Summary and Options.
6. Recommendations and Next Steps. This section of the report describes BerryDunn’s
recommendations for each of the options described in Section 2.0 and outlines the next steps
the City may take to implement solutions to issues identified in the Needs Assessment report.
1.3 WORK PERFORMED
In December 2012, BerryDunn facilitated a project planning meeting with the City project manager
and members of the City’s project team. During this meeting, BerryDunn provided the City with an
Information Request document that summarized requested background information to be reviewed by
the BerryDunn team. BerryDunnalso developed (and reviewed with the project team) a web-based
end user survey that was issued to City staff to collect the perceived strengths and weaknesses
related to SPS. The City’s project manager also provided extensive background documentation
related to the current SPS environment, including product modifications and business process
documentation.
Prior to conducting on-site fact-finding meetings, the BerryDunn project team reviewed the
documentation provided by the City. In addition, the results of the city staff survey responses were
reviewed and trends and themes related to strengths and challenges related to SPS were identified.
During the fact finding meetings with city staff, BerryDunn discussed the reported challenges and
business needs identified in the surveys.
13
Remediation and Action Plan Report Page 9 March 19, 2013
A project kickoff meeting was conducted with city staff and the BerryDunn project team the week of
January 7, 2013. During the meeting, project team members were introduced and the approach and
timeline for the project was reviewed. In addition, City staff had the opportunity to ask questions about
the project and the City’s goals and objectives.
Following the Project Kickoff Meeting, BerryDunn facilitated fact-finding meetings with department
users of current City systems, specifically SPS. The purpose of these meetings was to follow up on
information previously provided, document high-level functional requirements necessary to meet the
City’s needs, understand the current business processes associated with the use of SPS, and identify
challenges with the use of SPS. The scope of the functional areas reviewed is summarized in the
table below:
Table 1.1 Functional Areas
No. Functional Areas
1 General Ledger
2 Budget
3 Purchasing/Receiving
4 Information Technology (Infrastructure and IT Support for SPS)
5 Human Resources
6 Risk Management
7 Electric Utilities
8 Utility Customer Service
9 Accounts Payable
10 Bids
11 Inventory
12 Asset Management
13 Financial Reporting
14 Planning and Development Services
15 Payroll
16 Treasury
17 Municipal Court
18 Accounts Receivable
19 Cash Receipts
20 Utilities, Water, Wastewater
21 Fleet
22 Facilities
23 Interfaces and Integration
24 City Manager
25 City Secretary
14
Remediation and Action Plan Report Page 10 March 19, 2013
26 City Auditor
The meetings conducted during the fact finding process were organized by functional area. In some
instances, concurrent meetings were conducted. The meeting schedule is depicted in the exhibits
included on the following pages:
15
Remediation and Action Plan Report Page 11 March 11, 2013
Exhibit 1.1 Onsite Meeting Schedule
BerryDunn Team 1 BerryDunn Team 2 BerryDunn Team 3 BerryDunn Team 1 BerryDunn Team 2 BerryDunn Team 3
Follow up / Meeting Prep
Follow up/Meeting Prep
Follow up/Meeting Prep
Follow up/Meeting Prep
Wednesday
January 9, 2013
Information Technology
Part 2 - Network, IT staffing
and ERP Support
Discussion
1:00 - 2:30
Location: IT Training Room
Parks and Recreation
3:00 - 4:30
Location: Parks Conference
Room
Planning and Development
Services Continued
(Planning/Zoning, Building
Inspections,Licenses, Land
Management, Code
Enforcement)
1:00 - 4:30
Location: City Hall 2nd Floor
Conference Room #1
Tuesday
January 8, 2013
Finance - Accounts
Payable
8:30 - 10:00
Location: City Hall
Administrative Conference
Room #2
Finance - Asset
Management
2:00 - 3:00
Location: City Hall
Administrative Conf. Rm. #2
Finance - Financial
Reporting
3:30 - 4:30
Location: City Hall
Administrative Conf. Rm. #2
Human Resources Part 1
11:00 - 12:00
Location: City Hall
Administrative Conference
Room #2
Human Resources Part 2
(Applicant Tracking, Benefits,
Compensation,Employee
Records,Employee Training)
1:00 - 4:00
Location: City Hall
Administrative Conference
Room #2
Information Technology
Part 1 -Tour and Remote
Location Discussion
11:00- 12:00
Planning and Development
Services
(Planning/Zoning, Building
Inspections,Licenses, Land
Management, Code
Enforcement)
8:30 - 12:00
Location: City Hall 2nd Floor
Conference Room #1
Finance - Budget
1:00 - 2:00
Location: City Hall 2nd
Floor Conference Room #1
Finance -
Purchasing/Receiving
2:30 - 4:30
Location: Council
Chambers
Finance - Bids
10:30 - 12:00
Location: City Hall
Administrative Conference
Room #2
Project Team Meeting
8:30 - 9:00
Location: City Hall Administrative Conference Room #2
Kickoff Meeting Prep
9:00 - 9:30
Project Kickoff Presentation
9:30 - 10:30
Location: Council Chambers
Lunch
12:00 - 1:00
Lunch
12:00 - 1:00
Kickoff Presentation Wrap up and Transition to Meeting Locations
10:30 - 11:00
Finance - General Ledger
11:00 - 12:00
Location: City Hall 2nd
Floor Conference Room #1
Electric Utilities (Inventory,
Asset Management,Work
Orders,Blling, Collections,
FERC reporting)
8:30 - 12:00
Location: John Simek's
Office
Finance - Inventory
1:00 - 2:00
Location: City Hall
Administrative Conference
Room #2
Utility Customer Service
(Account
Maintenance,Billing,
Meters,Financial
Collections)
1:00 - 4:30
Location: IT Conference
Room
Utility Customer Service
(Account
Maintenance,Billing,
Meters,Financial
Collections)
1:00 - 4:30
Location: IT Conference
Room
16
Remediation and Action Plan Report Page 12 March 11, 2013
Exhibit 1.2 Onsite Meeting Schedule(Continued)
BerryDunn Team 1 BerryDunn Team 2 BerryDunn Team 3 BerryDunn Team 1 BerryDunn Team 2 BerryDunn Team 3
City Manager's Office
8:30 - 9:00
City Secretary's Office
8:30 - 9:00
Follow up / Meeting Prep
Follow up/Meeting Prep
Follow up/Meeting Prep
Follow up / Meeting Prep
Follow up/Meeting Prep
Thursday
January 10, 2013
Friday
January 11, 2013
Follow up and Additional Meeting Time
Finance - Court
10:00 - 12:00
Location: Municipal Court
Interfaces and Integration
Discussion with IT
9:00 - 11:00
Location: IT Training Room
Finance - Accounts
Receivable
1:00 - 2:00
Location: City Hall 2nd
Floor Conference Room #1
Finance - Treasury
10:30 - 12:00
Location: City Hall 2nd
Floor Conference Room #1
Project Team Closeout Meeting
11:00 - 12:00
Location: IT Training Room
Utilities, Water,
Wastewater
(Inventory, Work Orders)
8:30 - 10:30
Location: CSU MTF
Auditorium
Available Meeting and
Follow up Time
(walkthroughs, location
visits etc).Finance - Cash Receipts
2:30 - 4:00
Location: City Hall 2nd
Floor Conference Room #1
Lunch
12:00 - 1:00
Finance - Payroll
8:30 - 10:00
Location: City Hall 2nd
Floor Conference Room #1
Finance - Payroll
8:30 - 10:00
Location: City Hall 2nd
Floor Conference Room #1 Follow up/Meeting Prep
Utility Customer
Service(Part 2)
11:00 - 12:00
Location: IT Training Room
Fleet
1:00 - 2:30
Location: Feet Break Room
Facilities
3:00 - 4:30
Location: Facilities' offices
17
Remediation and Action Plan Report Page 13 March 15, 2013
As a result of the documentation provided by the City and information gathered during the fact-finding
meetings, BerryDunn developed a Needs Assessment and Gap Analysis Report. The report
described the current environment at the City related to business processes and systems. The report
also described the strengths and challenges of the current systems environment and identified the
business needs of the City related to the current (or potential future systems).
BerryDunn and the City project team met to review the Needs Assessment and Gap Analysis report
and confirm the findings, challenges and business needs identified in the report. The City project team
worked with other City staff to collect feedback on the report. Upon incorporating feedback from city
staff involved in the fact finding meetings and the project team, BerryDunn provided the city with a
final updated version of the report.
Based on the challenges and business needs documented in the Needs Assessment and Gap
Analysis report, BerryDunn developed this report which contains the options the City has to address
the issues with the current environment. As a step in developing this report, BerryDunn issued a
request for information (RFI) to ERP vendors requesting cost estimates including hardware, software
and implementation costs for a new ERP system. BerryDunn also requested cost estimates from
SunGard related to training and analysis costs for SPS as well as upgrade costs to SunGard’s
OneSolution ERP product. The results of the RFI process are summarized in section 3.0 Cost
Analysis.
BerryDunn will be meeting with the City project team to review the draft Remediation and Action Plan
report to discuss the options and recommendations in the report. Following the work session to review
the report, BerryDunn will be presenting the results of the report to the City’s Management Team.
18
Remediation and Action Plan Report Page 14 March 15, 2013
2.0 Needs Assessment Summary and Options
This section of the report summarizes the needs and challenges identified in the Needs Assessment
Report and summarizes the options that the City has to address the challenges.
2.1 IDENTIFIED CHALLENGES
There were many challenges related to the current ERP environment reported by City staff and
identified by BerryDunn as a result of the fact finding activities.The challenges documented are based
on the use of paper-based and manual processes, external databases (MS Excel and Access) to
track information, challenges with the setup, configuration, or knowledge of the SPS system, and a
reported lack of needed functionality in SPS.
During the process to identify and document challenges in the current environment, many recurring
themes related to the challenges were identified.The section below describes the high level themes
identified, followed by a detailed list of the challenges identified by City staff and BerryDunn. The
challenges are not listed in any priority order.
For purposes of this report, a challenge is a requirement for a change or improvement to existing
software functionality or technology that if addressed can either remove an impediment in the current
business process or assist in streamlining an existing process.
Summary Challenges:
2.1.1 City IT staff spend time supporting business processes involving SPS.
Support of SPS extends beyond true technical activities to supporting the actual regular business
processes of City staff using the system. The IT Department has designated resources that have
developed an understanding of the functions of various departments and will assist their respective
departments in using the system. Due to configuration, customization, and manual process in the
current financial management system environment; City staff often require the assistance of IT staff to
assist in performing reconciliation, reporting, and other tasks to complete City business processes.
2.1.2 Many City departments are using manual processes to track information outside of
SPS.
Many departments reported the use of MS Excel spreadsheets, paper files, and scanned images of
documents to track expenditures, financial transactions, work orders, cash receipts activities, and
other information.
19
Remediation and Action Plan Report Page 15 March 15, 2013
2.1.3 Expenditures cannot be tracked in SPS with sufficient detail
SPS does not record enough detail for departments to track their budgets to the necessary
expenditure level. Instead, departments are using various spreadsheets to track their detailed
expenditures. During budget preparation, this causes significant manual effort as forecasting and
reporting from various spreadsheets is cumbersome.
2.1.4 Budgets preparation typically involves the use of MS Excel spreadsheets
The Budget functionality within SPS does not allow for forecasting and analysis necessary in the
preparation of the City budget. Developing reports is difficult from SPS, and any manipulation for
“what-if” analysis must be conducted outside of SPS in MS Excel spreadsheets. This process takes a
large amount of time and does not contribute to accurate representation of budget amounts in an
efficient timeframe.
2.1.5 Workflow functionality is not meeting all City needs
Functionality existing in SPS for workflow is not meeting all City needs. City departments are
distributing documents for approval in paper copy and often this procedure creates large delays in
business processes. The invoices in the A/P process force users to constantly check a network folder
for items to approve as oppose to being proactively notified of pending activity.
2.1.6 The City does not have (or is not using due to lack of functionality) modules to support
certain business functions including Grant Management, Contract Management, and
Project Accounting.
Due to the lack of the grant management module, the City must use Excel spreadsheets and paper
copies of applications to track grant submissions, status, payments, and reimbursements. While many
grant applications can be submitted online through the granting agency’s website, the process of
tracking the overall status of the grant involves storing manual reports. In addition, reimbursement
payments are processed as part of the Accounts Receivable process in SPS; however, since a grant
management module is not in use, manual spreadsheets and reports must be maintained. The lack of
a grant management system also means that the City cannot leverage the integration available in that
allow grant information and tracking to carry into budget, payroll, work order, purchasing, and general
ledger applications.
Due to the limits of a project accounting system, the City must use MS Excel spreadsheets and paper
reports to track both capital and departmental project activity. The lack of field constraints in the
project accounting system also means that the City cannot leverage the integration available that
allow project information and tracking to carry into budget, payroll, fixed assets, grants, work order,
purchasing, and general ledger applications.
20
Remediation and Action Plan Report Page 16 March 15, 2013
Due to the lack of a contract management system, the City must use scanned images, Excel
spreadsheets, and paper copies of contracts to track contract status, milestones, renewal options,
expired insurance certificates, payment history and vendor performance. The lack of a contract
management system also means that the City cannot leverage the integration of contract information
and tracking to carry into budget, inventory, purchasing and general ledger applications.
2.1.7 Time entry for the payroll process is largely manual
All departments track time on paper time sheets. Some departments have their own scheduling
system (public safety) to record time, others use paper time sheets generated from SPS, and further
departments use Excel spreadsheets. All departments submit paper timesheets through their
department’s specific payroll clerk.
2.1.8 Disparate systems do not promote data sharing
The City’s current environment of COTS and custom developed systems as well as Access databases
does not facilitate sharing of common data across the City. The current environment limits the ability
of City staff to perform enterprise wide reporting, forecasting, and overall analysis. In addition, due to
the lack of integration of key City systems, staff are required to perform duplicate data entry into
multiple systems to track similar pieces of information. For example, work order information is keyed
into CityWorks and SPS in some instances.
2.1.9 An overall lack of functionality in SPS has created the need for manual processes
City staff reported numerous issues and challenges with an overall lack of functionality in some
modules in SPS. Budgeting was one area cited by staff as lacking the functionality needed to perform
forecasting and analysis necessary to create the annual budget. The reported lack of functionality in
critical areas has forced City staff to develop manual processes and maintain external spreadsheets
and reports to track City data for reporting purposes.
2.1.10 The inability to drill down to source information including supporting documentation
creates numerous challenges
City staff reported the inability to drill down to source information and across modules in SPS
impacted reporting and analysis. In addition, the inability to have document images automatically
attached to records in the system created time consuming and inefficient processes by requiring staff
to store electronic documents in network folders and in some cases paper-based folders.
21
Remediation and Action Plan Report Page 17 March 15, 2013
2.2 ERP IMPROVEMENTOPTIONS
As described in Section 2.1 Identified Challenges, City staff are in some instances relying on
inefficient processesto complete their day to day business functions. Staff spend time working with
external databases,spreadsheets and paper-based processes.Staff reported the need to combine
multiple SPS reports in MS Excel to create desired reports. Severalgaps exist between the
functionality provided bySPS and the functionality required by city staff.City staff and BerryDunn
documented over 100 unique challenges and business needs (refer to Appendix A) impacting city
staff’s ability to complete business processes in an efficient manner. The challenges and business
needs documented also have the potentialto limit the city’s ability to further automate existing
processes, perform advanced reporting (forecasting and financial analysis) and leverage newer
technology including expanding mobile computing opportunities.In addition, the current SPS system
does not provide some of the modules (contract and grant management) required by city staff.
Based on BerryDunn’s review of the City’s current ERP environment as it relates to financial, human
resources, payroll and revenue applications (land management, utility billing, permitting, etc.),
BerryDunn recommends that the City take steps to improve and enhance the current environment.
Other potential considerations could include the continued use of the current SPS system “as-is” or
the continued use of the current SPS system with additional training and analysis provided by
SunGard. Based on the likelihood that the continued use of SPS will not meet the current and future
needs of the City, the City should consider replacing SPS.
BerryDunn has identified two options the City could consider to improve the existing ERP
environment:
• Option 1: Review the newest version of SunGard’s ERP software (ONESolution).
• Option 2: Issue a Request for Proposal (RFP) for a new ERP system.
The following sections summarize each of the two options as well as the pros and cons of each
option.
2.3 OPTION 1: IMPLEMENT THE NEWEST VERSION OF SUNGARD’S
ERPSOFTWARE (ONESOLUTION)
In this option, the City would review and consider implementing SunGard’s new ERP product,
ONESolution. SunGard has indicated that the process to migrate from SPS to ONESolution is
considered essentially a new software implementation, however, under the City’s current software
maintenance agreement; licensing fees for the modules in SPS that are implemented in ONESolution
are waived. This approach leverages the knowledge that city technical staff have gained through use
of SunGard products and could potentially reduce the level of staff effort from a technical standpoint,
versus the level of effort required to implement an ERP system from a new vendor. Technical staffthat
22
Remediation and Action Plan Report Page 18 March 15, 2013
support the SPS system are in place and have relevant SunGard knowledge and experience in
supporting the existing system.
This approach could provide a somewhat streamlined procurement process (since the City could
consider the purchase of ONESolution as an upgrade to the SPS solution) and pay licensing fees for
new modules; however the City will need to expend funds for professional services (implementation
costs).
Option 1 creates the need to define functional and technical requirements and examine and modify
existing business practices and the policies that support those processes. While the existing
deficiencies can be the focus of the implementation of ONESolution, they are only part of the current
challenges faced by city staff. The existing business practices need to be reviewed and revised where
appropriate.
Table 2.1: Pros and Cons of Option 1
Option 1: Implement the newest version of SunGard’s ERP system, ONESolution
In this option, the City wouldimplement SunGard’s new ERP product, ONESolution. SunGard has
indicated that the process to migrate from SPS to ONESolution is considered a new software
implementation, however, under the City’s current software maintenance agreement; licensing fees f or
the modules in SPS that are implemented in ONESolution are waived.
Pros of Option 1
1. Implementing ONESolution may provide an updated systems environment in a shorter
implementation period than a full system replacement.
2. Option 1 does not require a competitive procurement process which will contribute to a shorter overall
implementation timeline.
3. Option 1 could have an overall lower estimated cost than a replacement system through a
competitive procurement process since initial licensing fees have been waived by SunGard.
4. Training and end-user adoption of ONESolution may be shorter when compared to a new system
since ONESolution provides a similar user interface to the version in use in the City today.
5. ONESolution uses newer technology (.NET) which may allow for easier integration with other
systems.
Cons of Option 1
1. ONESolution Work Management (including Work Orders, Fleet and Asset Management) are not in
general release and are still considered Beta products by SunGard.
2. ONESolution Utility Billing is still in development.
3. The City will need to procure additional modules (contract management, grant management etc.) to
meet its overall goals.
4. The City has limited leverage for negotiating costs by going through a singlesource procurement.
5. ONESolution does not have as great a footprint in the public sector market for organizations similar in
size to College Station as compared to other ERP systems the City could consider.
6. Many of the same challenges and business needs identified in the Needs Assessment report may still
23
Remediation and Action Plan Report Page 19 March 15, 2013
exist after implementing another SunGard product.
7. The challenges reported by city staff related to receiving SunGard customer support (lack of
knowledge, frequent turnover) may still be an issue with another SunGard product line.
2.4 OPTION 2: ISSUE A REQUEST FOR PROPOSAL FOR A NEW ERP SYSTEM
In this option the City would undertake a competitive procurement process that could result in the
implementation of a new ERP system. This approach would require a substantial commitment from
City staff to participate in implementation actives. Similar to Option 1, this approach requires the city
to review existing business processes and associated policies. In addition, functional and technical
requirements need to be documented for inclusion in a request for proposal and a competitive
procurement process performed.
The need to revise business processes and define functional and technical requirements would be
approximately the same level of effort as Option 1. However, implementing a new ERP system would
likely take longer than implementing ONESolution. In addition, the staff commitment required to
implement a new ERP system could be greater with this approach.
The selection and implementation of a new ERP system would also introduce the need for
significantly more training for both functional and technical staff. Through the development of detailed
functional and technical requirements, the city has a greater potential to select a system that meets
their business requirements. It is important to note that the City defines an ERP system as providing a
complete and fully integrated solution which could entail the use of specific 3rd
Table 2.2: Pros and Cons of Option 2
party/point solution
applications.
Option 2: Issue a request for proposal for a new ERP system
In this option the City would undertake a competitive procurement process that could result in the
implementation of a new ERP system. This approach would require a substantial commitment from City
staff to participate in implementation actives. Similar to Option 1, this approach requires the city to review
existing business processes and associated policies. In addition, functional and technical requirements
need to be documented for inclusion in a request for proposal and a competitive procurement process
performed.
Pros of Option 2
1. When compared with Option 1, the City will be able to select an ERP system that is widely used by
similar sized organizations.
2. City can select an ERP system where none of the modules are in beta or still being developed.
3. New technologies included in an ERP system will provide tools the City can use to significantly
improve its business processes.
4. Long-term benefits and potential savings are greatest with implementing a new system.
24
Remediation and Action Plan Report Page 20 March 15, 2013
Option 2: Issue a request for proposal for a new ERP system
In this option the City would undertake a competitive procurement process that could result in the
implementation of a new ERP system. This approach would require a substantial commitment from City
staff to participate in implementation actives. Similar to Option 1, this approach requires the city to review
existing business processes and associated policies. In addition, functional and technical requirements
need to be documented for inclusion in a request for proposal and a competitive procurement process
performed.
5. Competitive procurement process will likely lower costs of available system options.
6. Option 2 does not eliminate Option 1as a potential option. The City can develop functional and
technical requirements and issue a request for proposal and depending on the results of the RFP
responses, still decide to pursue Option 1.
Cons of Option 2
1. Initially implementing a full replacement system will be the most costly option for the City.
2. Project resource demands to implement a replacement system will be the greatest when compared to
Option 1.
3. The timeframe to implement Option 2 is the longest.
25
Remediation and Action Plan Report Page 21 March 15, 2013
3.0 Cost Analysis
This section of the report contains the analysis of the estimated costs provided by ERP Vendors as a
result of the Request for Information process.
3.1 R EQUEST FOR INFORMATION APPROACH
To develop potential costs for each of the three options described in section 2.0, BerryDunn
developed a Request for Information (RFI) worksheet and issued it to the ERP vendor community.
Vendors were asked to provide a total cost estimate (low and high estimates) comprised of software,
professional services and other necessary implementationservices based on their experience
implementing similar system(s) for similar sized organizations. The cost estimates submitted by
vendors were non-binding and it was explained to vendors that the numbers provided would be used
for planning purposes only.
The RFI was sent directly to several ERP vendors and vendors of specific point solutions (utility
billing, planning and development services etc.). The vendors that received the Request for
Information included, New World Systems, Lawson, Tyler Technologies, Oracle, CGI, SAP,
Springbrook, EnerGov, MSGovern, Harris, and Microsoft. In addition, SunGard was asked to provide
pricing for the implementation of ONESolution. Some vendors responded that they were not able to
respond to the RFI for a variety of reasons including availability of resources and company policy. The
following table contains the list of vendors that responded to the Request for Information. Some
vendors responded with specific point solutions and those are noted in the table.
Table 3.1: RFI Participating Vendors
RFI Participating Vendors
No. Vendor No. Vendor
1 Velocity (Lawson ERP Software) 5 SunGard (ONESolution ERP Software)
2 Tyler Technologies (MUNIS ERP Software) 6 Advanced Utilities (Utility Billing)
3 Microsoft (Dynamics AX 2012 ERP
Software) 7 EnerGov (Permitting and Land Management
Suite)
4 Oracle (JD Edwards Enterprise One ERP
Software)
26
Remediation and Action Plan Report Page 22 March 15, 2013
3.2 C OST F INDINGS
The vendors were also asked to provide a cost estimate for the new ERP system based on their
experience implementing systems for similar sized municipalities. Each vendor was asked to provide
a total cost estimate (low and high estimates) comprised of software, hardware and implementation
services relative to each of the modules. Vendors were also asked to provide hourly rates for
professional services and for custom programming.
The cost areas organized by the type of costs are listed in Table 3.2 and the list of system modules
included in the RFI is contained in Table 3.3.
Table 3.2: RFI Cost Areas
RFI Cost Type
One-Time Costs
Software License Costs
Data Conversion Costs
Professional Service Costs
Training Costs
Expenses (miscellaneous)
Recurring Costs
Annual Maintenance (Once Live)
Custom Modification Maintenance
Additional Maintenance Fees (Once Live, if Applicable)
Additional Costs
Hourly Rate for Professional Services
Hourly Rate for Custom Programming
Other Costs if Applicable
27
Remediation and Action Plan Report Page 23 March 15, 2013
Table 3.3: RFI Modules
No. Modules
1 General Ledger Accounting and Financial Reporting
2 Budget
3 Project Accounting
4 Grant Management
5 Purchasing and Receiving
6 Human Resources
7 Payroll
8 Electric Utilities
9 Utility Customer Service
10 Accounts Payable
11 Bid Management
12 Treasury Management
13 Inventory
14 Contract Management
15 Fixed Assets and Asset Management
16 Planning and Development Services (Land Management, Permitting, Inspections, Planning, Zoning, Code
Enforcement).
17 Accounts Receivable
18 Cash Receipts
19 Fleet Management
20 Facilities Management
21 Work Orders
28
Remediation and Action Plan Report Page 24 March 15, 2013
3.2.1 One Time Costs
The following table contains the high and low one-time cost estimates from vendors in dollars.
Table 3.4: High and Low One-Time Cost Estimates ($)
ERP High and Low One-Time Cost Estimates ($)
Vendor Low Estimate High Estimate
Velocity (Lawson ERP Software) $3,367,475 $4,071,223
Tyler Technologies (MUNIS ERP Software) Not Provided $1,859,600
Microsoft (Dynamics AX 2012 ERP Software) $1,505,200 $2,535,840
Oracle (JD Edwards Enterprise One ERP Software) $760,000 $1,130,000
SunGard (ONESolution ERP Software) $645,000 Not Provided
Point Solution High and Low One-Time Cost Estimates ($)
Vendor Low Estimate High Estimate
Advanced Utilities (Utility Billing) $1,101,000 $1,514,000
EnerGov (Permitting and Land Management Suite) $450,000 $645,000
29
Remediation and Action Plan Report Page 25 March 15, 2013
The following table contains the average one-time cost estimates by cost area from vendors in dollars.
Table 3.5: Average One-Time Cost Estimates by Cost Area ($)
Average One-Time ERP Cost Estimates by Cost Area ($)
Vendor Software License Costs Data Conversion Costs Professional Services Costs Training Costs Expenses (miscellaneous) Total Velocity (Lawson ERP
Software) $1,265,000 $49,728 $1,467,648 $77,700 - $2,860,076
Tyler Technologies (MUNIS
ERP Software) $1,228,500 $158,750 - $472,350 - $1,859,600
Microsoft (Dynamics AX 2012
ERP Software) $616,840 - $1,403,680 - - $2,020,520
Oracle (JD Edwards
Enterprise One ERP
Software)
- $175,000 $700,000 $70,000 - $945,000
SunGard (ONESolution ERP
Software) $95,000 - $550,000 - - $645,000
Average $1,666,040
Average One-Time Point SolutionCost Estimates by Cost Area ($)
Vendor Software License Costs Data Conversion Costs Professional Services Costs Training Costs Expenses (miscellaneous) Total Advanced Utilities (Utility
Billing) $187,500 $122,500 $822,500 $175,000 - $1,307,500
EnerGov (Permitting and
Land Management Suite) $262,500 $40,000 $212,500 $32,500 - $547,500
Average $927,500
Overall Average $2,593,540
30
Remediation and Action Plan Report Page 26 March 15, 2013
Vendor responses to each one-time cost for the modules are summarized below in Table 3.6. The
table contains the average of the responses for the low and high estimates. One-time costs are
defined as upfront costs that the City will only pay for once as part of the implementation. These costs
include but are not limited to, software, programming, hardware, equipment and interface
customizations. Recurring costs include software maintenance. In addition, some vendors bundled
modules with other modules (for example Accounts Receivable is included with the Cash Receipts
module by some vendors).
Table 3.6: Average One-Time Costs by Module
Average One-Time Costs by Module($000s)
Module Velocity (Lawson) Tyler Technologies (MUNIS) Microsoft (Dynamics AX) Oracle (JD Edwards) SunGard (ONESolution) Advanced Utilities EnerGov General Ledger
Accounting and
Financial Reporting
$264,542 $239,675 $1,505,520 INC INC * *
Budget $26,869 INC INC INC INC * *
Project Accounting $82,673 $52,425 INC INC INC * *
Grant Management $204,616 INC INC INC INC * *
Purchasing and
Receiving $197,240 $123,600 INC INC INC * *
Human Resources $329,652 $57,325 INC INC INC * *
Payroll $215,309 $62,275 INC INC INC * *
Electric Utilities $282,500 * 3rd * party * * *
Utility Customer
Service $334,475 $159,275 $300,000
3rd * party * $1,307,500 *
Accounts Payable $86,807 INC INC INC INC * *
Bid Management $159,938 $30,225 INC INC INC * *
Treasury
Management $94,869 $45,575 INC INC * * *
Inventory $41,337 $67,450 INC INC INC * *
Contract
Management $112,438 $30,225 INC INC INC * *
Fixed Assets and
Asset Management $45,470 $102,075 INC INC INC * *
Planning and $334,475 $161,125 CRM * INC * $547,500
31
Remediation and Action Plan Report Page 27 March 15, 2013
Average One-Time Costs by Module($000s)
Module Velocity (Lawson) Tyler Technologies (MUNIS) Microsoft (Dynamics AX) Oracle (JD Edwards) SunGard (ONESolution) Advanced Utilities EnerGov Development
Services (Permitting,
Inspections,
Planning, Zoning,
Code Enforcement).
Accounts Receivable $88,873 $114,950 INC INC INC * *
Cash Receipts $31,003 $70,575 INC INC INC INC *
Fleet Management * INC $225,000
3rd * party INC * *
Facility Management $393,094 INC 3rd * party INC * *
Work Orders $393,094 $126,700 3rd INC party INC * *
Other - $416,125 - - - - -
TOTAL $3,719,274 $1,859,600 $2,030,520 $945,000 $645,000 $1,307,500 $547,500
*Indicates that the module was not included.
3.2.2 Recurring Costs
Vendors were asked to provide estimated costs for recurring maintenance. The costs for modules are
presented in Table 3.7.
Table 3.7: High and Low Recurring Cost Estimates ($)
High and Low Recurring ERP Cost Estimates ($)
Vendor Low Estimate High Estimate
Velocity (Lawson ERP Software) $226,600 $330,000
Tyler Technologies (MUNIS ERP Software) Not Provided $286,073
Microsoft (Dynamics AX 2012 ERP Software) $74,944 $122,445
Oracle (JD Edwards Enterprise One ERP Software) $12/employee* $15/employee*
SunGard (ONESolution ERP Software) - -
High and Low Recurring Point Solution Cost Estimates ($)
32
Remediation and Action Plan Report Page 28 March 15, 2013
Vendor Low Estimate High Estimate
Advanced Utilities (Utility Billing) $46,875 $46,875
EnerGov (Permitting and Land Management Suite) $45,000 $60,000
*JD Edwards proposed Software as a Service (SAAS) model.
Table 3.8 contains the average recurring cost estimates for all modules that each vendor reported.
Table 3.8: Recurring Maintenance Cost Estimates for All Modules
Recurring Cost Estimates for allModules
Vendor Average Estimate
Velocity (Lawson ERP Software) $278,300
Tyler Technologies (MUNIS ERP Software) $286,073
Microsoft (Dynamics AX 2012 ERP Software) $98,695
Oracle (JD Edwards Enterprise One ERP Software) $13.5/employee*
SunGard (ONESolution ERP Software) -
Advanced Utilities (Utility Billing) $46,875
EnerGov (Permitting and Land Management Suite) $52,500
Average $152,489
*JD Edwards proposed Software as a Service (SAAS) model.
Recurring costs will be impacted by both the number and type of software modules the City eventually
selects. Consideration in comparing the recurring cost estimates must be made to the number of
modules the vendor can provide. A higher recurring software maintenance cost estimate should be
consistent with a higher level of fit for the City’s needs and requirements, however, such a correlation
does not necessarily occur with all vendors.
In addition, these estimates should be expanded to a five or ten year planning horizon. A small
variance in annual maintenance can quickly grow over a ten year period.
3.3 ADDITIONAL COSTS
One-time and recurring maintenance costs typically make up the majority of the investment needed
for a new ERP system. However, other additional costs such as server hardware and hourly rates for
training and custom programming need to be considered. If the City selections option 2 or 3, vendors
will be asked to provide a comprehensive pricing schedule so all of these costs are known and can be
analyzed. The RFI asked vendors to provide estimated hourly rates. The rates are summarized below.
33
Remediation and Action Plan Report Page 29 March 15, 2013
Vendor responses to each estimated additional cost area are presented in the following sections.
Table 3.9: Estimated Additional Costs
Estimated Additional Costs
Vendor Hourly Rate for Professional
Services
Hourly Rate for Custom
Programming
Velocity (Lawson ERP Software) $185 $185
Tyler Technologies (MUNIS ERP
Software) $147 $138
Microsoft (Dynamics AX 2012 ERP
Software) - -
Oracle (JD Edwards Enterprise One
ERP Software) $150 $80
SunGard (ONESolution ERP Software) - -
Advanced Utilities (Utility Billing) $175 $175
EnerGov (Permitting and Land
Management Suite) $170 $250
Average $165 $166
In addition to the hourly rates above, SunGard provided training and analysis rates. The rates are
summarized in the table below.
Table 3.10: Estimated Additional Costs
SunGard Training and Analysis Rates
Service Rate*
Onsite Training – 3 day minimum $1,280 per day
Business Process Reviews (BPR) $1,500 per day
*Rates do not include travel expenses
BerryDunn’s experience with similar projects that include a Request for Information process is that
costs in a response to a Request for Proposal (RFP) are typically significantly higher than in an RFI
response.This is largely due to additional detail that is contained in an RFP. With this additional detail,
vendors are able to submit costs with an understanding of specific functionality, necessary
integrations and customizations, and more accurate resource hour estimates related to professional
services. BerryDunn has found that average one-time cost estimates are typically 20-50% higher in a
proposal to an RFP over an RFI Response. The next sub-section contains projected budget estimates
based on these adjustments.
34
Remediation and Action Plan Report Page 30 March 15, 2013
The following table contains adjusted average, one-time budget estimates for each vendor based on
BerryDunn’s experience of higher costs in a response to an RFP. Four levels are provided
representing a 20%, 30%, 40%, and 50% increase over the estimate provided in the RFI response.
Table 3.11: Adjusted Average One-Time Cost Estimates ($)
Adjusted Average One-Time ERP Cost Estimates ($)
Vendor Average Est. 20% Increase 30% Increase 40% Increase 50% Increase
Velocity $2,860,076 $3,432,091 $3,718,099 $4,004,106 $4,290,114
Tyler $1,859,600 $2,231,520 $2,417,480 $2,603,440 $2,789,400
Microsoft $2,020,520 $2,424,624 $2,626,676 $2,828,728 $3,030,780
Oracle $945,000 $1,134,000 $1,228,500 $1,323,000 $1,417,500
SunGard $645,000 $774,000 $838,500 $903,000 $967,500
Adjusted Average One-Time Point Solution Cost Estimates ($)
Vendor Average Est. 20% Increase 30% Increase 40% Increase 50% Increase
Advanced
Utilities $1,307,500 $1,569,000 $1,669,750 $1,830,500 $1,961,250
EnerGov $547,500 $657,000 $711,750 $766,500 $821,250
35
Remediation and Action Plan Report Page 31 March 15, 2013
4.0 Implementation Timelines
This section of the report outlines the potential timelines the City may follow for the implementation of
each option.
4.1 IMPLEMENTATION TIMEL INES
4.1.1 Implementation Timeline for Option 1
Option 1 is for the City to review and consider implementing SunGard’s new ERP product,
ONESolution. SunGard reported that the implementation of ONESolution needs to be treated in a
similar fashion as the implementation of a new ERP system. Based on the number of SPS modules
currently being used at the City, SunGard reported that the implementation of ONESolution could take
between 15 and 21 months.
4.1.2 Implementation Timeline for Option 2
In this option the City would undertake a competitive procurement process that could result in the
implementation of a new ERP system. This approach would require a substantial commitment from
City staff to participate in implementation actives. Similar to Option 1, this approach requires the city
to review existing business processes and associated policies. In addition, functional and technical
requirements need to be documented for inclusion in a request for proposal and a competitive
procurement process performed. Estimated implementation timelines as reported by ERP vendors
can take between 12 and 36 months.
The actual implementation timeline will depend on several factors including staffing and other projects
in the City as well as the implementation approach selected by the City. These considerations are
discussed in the next Section.
4.2 IMPLEMENTATION CONSIDERATIONS
As the City considers options 1 and 2 as a potential solution to implementing a new ERP system, it
should also begin to consider the factors and approach for a future system implementation. Many
factors can begin to be addressed now so that they will be in place or mitigated in time for the
implementation process.
36
Remediation and Action Plan Report Page 32 March 15, 2013
4.3 FACTORS AND METHODS OF SUCCESSFUL IMPLEMENTATION
4.3.1 Factors for Approach
There are multiple factors that the City will need to consider in planning for the implementation of a
new ERP system. Many of these considerations can be determined as part of the project planning
phase. The primary consideration is the staffing levels that the City will commit to the implementation
of a new ERP system.
Other factors to be considered include the number of other city-wide projects underway, both
technical and non-technical; the number of third-party applications that will be used; the number of
interfaces that must be built; and the amount of data that will be converted to the new system. All of
these factors will contribute to the decision of which implementation approach will be used. Potential
implementation approaches the City could consider are described in the following sections.
4.3.2 “Big Bang” Approach
A “big bang” approach for a system implementation involves going live with all system modules and
functionality at the same time. This allows full integration of modules to be realized from the onset of
the go-live period. This approach can also assist in change management activities, since staff may
realize the benefit of an integrated system early in the implementation. Another advantage is that
training and business process redesign can focus on the functionality provided by the new system and
not focus on changing processes during the implementation of multiple phases of the system.
Many disadvantages and risks exist with this implementation approach. In order for it to be successful,
significant planning must be done prior to starting implementation. This planning effort can require
significant City resources and can be time-consuming. Once the project schedule and plan is
developed, it is very difficult to modify the approach due to the other contingencies in the plan.
Another disadvantage is that the configuration of the system is not able to progressively develop as it
is implemented.
If this method is to be chosen, it is crucial that a detailed contingency plan is developed and that
appropriate City resources are dedicated to the project to increase the likelihood of overall of project
success.
4.3.3 Phased Approach
A phased system approach involves groupings of modules or business processes being brought into
production on the new system while progressively going live with additional modules as the
implementation progresses. Typically, there is a core group of modules that must interact with each
other which will go live first. From there, many of the ancillary modules can go live once the
foundation has been established. The phased approach is the common approach for ERP upgrade
37
Remediation and Action Plan Report Page 33 March 15, 2013
and implementations used in the public sector today. This approach typically involves going “live” on
core financials (General Ledger, Budget, Procurement, Accounts Payable) first followed by Payroll,
Human Resources and other ancillary modules. Revenue applications can typically go-live at the
same time as Payroll and Human Resources modules, since the city resources in involved in the
implementation of the modules have limited overlap.
An advantage of the phased approach is that the progression of modules allows for adjustments and
configurations to be made throughout the implementation. Another advantage is that system users are
given a longer period of time to adapt and learn the new system functionality.
One of the disadvantages of this approach is that it will generally require two separate systems to run
in parallel for some time. This can quickly add complexity to the City infrastructure and place
additional strain on support resources. In addition, the overall timeline of a phased approach is longer
when compared to a “big bang” approach.
4.4 SUMMARY OF CONSIDERATIONS
In the event the City selects option 1 or 2, BerryDunn recommends that a phased approach be used
for any potential future system implementation. Due to the many risks involved with a big bang
approach, a phased approach has a higher likelihood of project success. A phased approach
minimizes impact on City staff and resources, allows for a longer implementation timeline to target go-
live dates with calendar and fiscal year starts and allows the City to pay for initial start-up and
maintenance costs over a longer period of time.
A successful phased implementation requires significant planning. One of the most important aspects
of a phased project plan is the criteria for exiting and entering each phase. Adhering to entrance and
exit criteria will ensure that each phase is fully complete before the next one begins and can assist in
mitigating project risks in future phases.
In BerryDunn’s experience in organizations similar in size to College Station, an estimated timeframe
to implement the modules being considered (and identified in the Needs Assessment) would be 10–
12 months for core financials (Phase 1), 8 -12 months for payroll and human resources (Phase 2) and
6-8 months for planning and development services as well as utility and revenue modules (Phase
3).The phases can overlap depending on City staffing, however a range of 10 to 20 months for
implementation of all three phases, depending on the amount of overlap, is appropriate given the
timelines provided by the ERP vendors, and BerryDunn’s experience with similar ERP
implementations.
38
Remediation and Action Plan Report Page 34 March 15, 2013
5.0 Consideration of Options
This section of the report summarizes the current and desired modules/systems used by the City and
the source of these modules depending on the option selected.
5.1 S OURCES OF MODULES IN E ACH R EPLACEMENT O PTION
The City has several third party systems in their current environment with varying levels of integration
with SPS. Depending on the option selected by the City the need for third party systems and
integration can change. The table below summarizes the current and desired modules/systems used
by the City and the source of these modules depending on the option selected.
Table 5.1: Source of Modules in Each Replacement Option
Source of Modules in Each Replacement Option
Module Option 1: Implement
ONESolution
Option 2: Issue RFP for new
ERP
General Ledger Accounting and Reporting ONESolution New ERP
Budget ONESolution New ERP
Project Accounting ONESolution New ERP
Grant Management ONESolution New ERP
Purchasing and Receiving ONESolution New ERP
Human Resources ONESolution New ERP
Payroll ONESolution NEW ERP or 3rd Party (City
initiative in progress)
Electric Utilities
TBD – Module Not Available.
May need to be 3rd New ERP party
product
Utility Customer Service
TBD – Module Not Available.
May need to be 3rd New ERP party
product
Accounts Payable ONESolution New ERP
Bid Management ONESolution New ERP
Treasury Management ONESolution New ERP
Inventory
TBD – Module Not Available.
May need to be 3rd New ERP party
product
39
Remediation and Action Plan Report Page 35 March 15, 2013
Source of Modules in Each Replacement Option
Module Option 1: Implement
ONESolution
Option 2: Issue RFP for new
ERP
Contract Management TBD New ERP
Fixed Assets (Asset Management)
TBD – Module Not Available.
May need to be 3rd New ERP party
product
Planning and Development Services (Land
Management, Permitting, Inspections,
Planning, Zoning etc.).
ONESolution New ERP or 3rd Party
Accounts Receivable ONESolution New ERP
Cash Receipts ONESolution New ERP
Fleet Management
TBD – Module Not Available.
May need to be 3rd New ERP party
product
Facilities Management
TBD – Module Not Available.
May need to be 3rd New ERP party
product
Work Orders
TBD – Module Not Available.
May need to be 3rd New ERP or CityWorks party
product
Other City Systems/Modules
CityWorks CityWorks New ERP or CityWorks
Timekeeping
ONESolution (or 3rd New ERP (or 3 party
depending on timing of current
city initiative)
rd party
depending on timing of current
city initiative)
Tracker (Investment) ONESolution New ERP
Wallace ONESolution New ERP
CAFR Unlimited CAFR Unlimited New ERP, 3rd party, or CAFR
Unlimited
NeoGov NeoGov New ERP, 3rd party, or
NeoGov
Potential System Interfaces
RecTrac RecTrac RecTrac
JEMS (Court Management) JEMS (Court Management) JEMS (Court Management)
40
Remediation and Action Plan Report Page 36 March 15, 2013
Source of Modules in Each Replacement Option
Module Option 1: Implement
ONESolution
Option 2: Issue RFP for new
ERP
PaymentNet PaymentNet PaymentNet
Laserfiche Laserfiche Laserfiche
GIS GIS GIS
Itron Itron Itron
Petro Petro Petro
Long Distance Phone Bill Allocation
Long Distance Phone Bill
Allocation
Long Distance Phone Bill
Allocation
Local Phone Bill Allocation Local Phone Bill Allocation Local Phone Bill Allocation
BCAD (Brazos County Assessment District) BCAD (Brazos County
Assessment District)
BCAD (Brazos County
Assessment District)
Depending on the option selected, the City could expect to retire multiple existing systems. The level
of integration with the remaining systems will vary depending on the option selected by the City. In the
event option 1 or 2 is selected, the level of integration will also depend on the technology available
from the new system.
5.2 ADDITIONAL C ONSIDERATIONS
Regardless of the option selection by the City, there are processes that BerryDunn recommends the
City perform as part of the next steps in the project. These processes can assist the City in mitigating
some of the potential risks involved with the implementation of a new system or a significant revision
to existing business processes. Many of these considerations are applicable to all three options;
however their significance and impact vary depending on the option selected by the City.
5.3.1 Dedicate Adequate Resources
Executive sponsorship, long-term budgeting and planning must be realized in order to ensure that the
project is properly funded and supported throughout the project lifecycle. Adequately staffing the
project will be important for future project success.
5.3.2 Plan Business Process Change
One common area where large scale enterprise implementation projects fail is with a lack of proper
planning and change management. Implementing a new system and continuing to use existing
business processes will not lead to the benefits the City desires out of a new system(s). Properly
41
Remediation and Action Plan Report Page 37 March 15, 2013
planning for implementing business process change is critical to the success of this project. City staff
must acknowledge and embrace the need to change their processes. This often requires executive
sponsorship, planning for change, and facilitating appropriate training and knowledge transfer
meetings.
5.3.3 Assign a City Project Manager
Enterprise system implementation projects or major business process revisionsrequire that a Project
Manager be assignedto the project. The Project Manager will need to be responsible for monitoring
the agreed-upon deliverables and work products provided by anyvendor and for coordinating City
resource involvement in the project.
5.3.4 Properly Plan for Integrating with Existing City Systems
The City must ensure that integration is being built into the system and tested so that City-wide
systems work in an integrated fashion. A common area of planning that is often underestimated, is the
“upfront” time required to determine the scope of applications that will be replaced by the new system,
and those systems that will need to be integrated with. With each system that will require a future
interface, the City and the vendor should agree upon the specifications of how the future interface will
work.
5.3.5 Ongoing Training and Support
It will be important that the City renew the focus on end user training and ensure that training is
conducted as part of each option, and ongoing training is conducted after business process changes,
new configuration or a new system is implemented. The ongoing training is important to maintain a
deep knowledge base among city staff. In addition, BerryDunn recommends that the City reinstate the
concept of the Application Administrators regardless of the option selected. The Administrators can
serve in an internal support role as well as leading the process of continually evaluating the use of
each module in relation to city business processes.
42
Remediation and Action Plan Report Page 38 March 15, 2013
6.0 Recommendations and Next Steps
This section of the report describes BerryDunn’s recommendation for the options described in Section
2.0 and outlines the next steps the City may take to implement solutions to issues identified in the
Needs Assessment report.
6.1 BERRYDUNN R ECOMMENDATIONS
As described in the previous sections the City has the option to perform business process analysis
and redesign with the current SPS system, implement SunGard’s ONESoution or purchase a new
ERP system. Based on the business needs and the challenges documented by both BerryDunn and
City staff, BerryDunn recommends that the City proceed with Option 2. This option requires the city to
develop functional and technical requirements for an ERP system, issue a request for proposal and
conduct vendor evaluations. This option provides the greatest flexibility and does not eliminate option
1 and after review of the proposals from the vendor community, the City can decide to purchase a
new ERP system (including ONESolution).
Regardless of the option selected by the City, there are several project activities that must be
completed. BerryDunn recommends that functional and technical requirements be developed and
current business processes be documented before an RFP is issued. The City has already taken
steps to document several business processes with additional documentation occurring as part of the
Needs Assessment Report.
Analyzing existing business processes and developing future processes will allow the City to
potentially overcome existing configuration limitations, and fully utilize the capabilities provided with
new software. The following table outlines the proposed next steps. Each of these recommended
steps are further defined in the following sections of this report.
Table 6.1: Recommended Next Steps
Steps Recommended Next Steps
Step 1 Collect Functional and Technical Requirements
Step 2 Document Existing Business Processes
Step 3 Develop a Request for Proposal (RFP)
Step 4 Evaluate RFP Responses
Step 5 Conduct Vendor Demonstrations
Decision Point
Step 6 Identify Preferred Vendor and Conduct Contract
43
Remediation and Action Plan Report Page 39 March 15, 2013
Steps Recommended Next Steps
Negotiations
Step 7 Implementation Project Management Planning
6.2 COLLECT FUNCTIONAL AND TECHNICAL REQUIRE MENTS
BerryDunn recommends that functional and technical requirements be gathered from City
departments and staff.BerryDunn has collected considerable information by working closely with City
staff during fact-finding sessions, in creating the RFI, and from participating user feedback. While this
information is substantial, additional information is needed to complete a set of functional and
technical requirements for the City. The documented challenges and business needs should form the
basis of the City’s requirements and be incorporated in the requirements document.
6.3 DOCUMENT EXISTING BUSINESS PROCESSES
Existing business processes will be an important element to consider as part of collecting business
and technical requirements for the City. City staff has identified several functional challenges that
currently exist. It is recommended that the list of processes be expanded, as appropriate, to include all
critical business processes to be documented. Understanding the existing processes and to-be
processes will assist the City in further developing functional and technical requirements and also
identifying potential policy changes or business process changes.
6.4 DEVELOP A REQUEST FOR PROPOSAL (RFP)
BerryDunn recommends that a Request for Proposal be developed that would define City
requirements and provide the option for vendors to respond by proposing a solution that satisfies the
documented requirements.This allows vendors to propose a new system, or any other alternative
approach that the vendor feels could best satisfy the documented requirements. This approach is
believed to provide a competitive process, which will provide the City with the maximum number of
alternatives and approaches. An RFP needs to comply with standard terms and conditions of the
organization, and should include a high-level description of the current environment, anticipated
project management deliverables and the functional and technical requirements.
6.5 EVALUATE AND SCORE RFP RESPONSES
BerryDunn is recommending that the Cityfollow a structured approach to evaluate and rank vendor
responses.BerryDunn also recommends that an evaluation team be identified that provides broad
representation across the organization. A structured evaluation approach that includes multiple
representatives from key stakeholder groups is an important step in the change management process
as well.
6.6 CONDUCT VENDOR DEMONSTRATIONS
44
Remediation and Action Plan Report Page 40 March 15, 2013
BerryDunn recommends that a structured approach be undertaken to identify the vendors that will
participate in product demonstrations. Once the top vendors have been identified based upon the
evaluation criteria, BerryDunn recommends that a structured and scripted demonstration format be
followed. This approach guides the vendors to present common functionality, allowing City staff to
experience a more consistent approach to the overall demonstration. Another approach that has
proven beneficial is having a second room where City staff can experiment with a functioning system
and gain a greater understanding of the capabilities of the proposed solution. This approach allows for
a more detailed level of discussion with the vendors.
Following the vendor demonstration process, the City can assess the options available in comparison
to the functionality demonstrated by the ERP vendors and make the final decision to either purchase a
new ERP system, ONESolution or reinvest in SPS. The functional and technical requirements
documented as part of the RFP process could be used as part of option 1 and assist in guiding the
BPR process and any configuration changes that need to be made to SPS.
6.7 IDENTIFY PREFERRED VENDOR AND CONDUCT CO NTRACT NEGOTIATIONS
To ensure that there continues to be a high degree of competition, it is recommended that vendors
are not eliminated from the process until contract negotiations conclude. To improve vendor
discussions during contract negotiations, it may be beneficial to identify more than one vendor to enter
in contract negotiations.
6.8 IMPLEMENTATION PROJECT MANAGEMENTPLANNING
As the implementation project begins, BerryDunn recommends that there be a full-time project
manager during the implementation. The objective is to minimize risks and increase the likelihood of a
successful implementation. An individual in this role will work in collaboration with City leadership to
monitor project activities, proactively identify issues and risks, develop strategies to mitigate risks, and
ensure that the vendor is meeting its contractual obligations. An individual in this role will oversee all
implementation activities.
It is recommended that Project Status Reports be prepared so that the progress of the project can be
closely monitored. Status Reports document the work completed during the previous reporting period,
identify and prioritize risks and issues, provide recommendations for mitigating risks and issues, and
outline the tasks planned for the upcoming reporting periods. During the implementation there will be
a need for a communications plan that outlines the frequency and types of communication efforts that
will be conducted throughout the course of the implementation. BerryDunn would also recommend
that regularly scheduled meetings (defined in the planning efforts) be conducted with appropriate City
stakeholders to update them on project activities.
Projects that do not have some form of dedicated project management resources can be subject to
greater risk. Another important consideration in this role is to ensure that the vendor and City project
teams properly address issues relating to business process change. An individual in this role needs to
45
Remediation and Action Plan Report Page 41 March 15, 2013
protect the long-term interests of the City and challenge approaches that diminish the capabilities of
the software to satisfy the majority of City requirements without special configuration or customization.
The implementation approach should challenge any configuration that limits the current or future
capabilities of the software to satisfy City requirements. This may mean that business processes need
to change, or it may mean that an additional step is required to address a specific issue, but the
overall system setup needs to be configured to serve the bulk of the requirements without requiring
customization or configurations that functional staff cannot perform.
6.9 PROJECT TIMELINE
The overall timeline is an important consideration, and something that needs to be anticipated as the
organization plans and prepares for this project. There are many factors that could impact and
potentially change the overall timeline. In the table below we have included a recommended timeline
to illustrate the major initiatives that need to be completed. The decision point to move forward with a
new ERP system is included as part of the Product Demonstration task.
The major elements and the overall timeline for the project activities are outlined in Exhibit 6.1 below.
Exhibit 6.1: Project Timeline
6.10 RECOMMENDATIONS SUMMARY
The City should begin the process of identifying the staff that will be part of the procurement selection
team as well as the project management teams, subject matter experts, training and technical support
46
Remediation and Action Plan Report Page 42 March 15, 2013
teams. It will also be important for the City to begin planning for business process changes as a result
of the new ERP system by beginning change management activities to increase the likelihood of
acceptance of business process changes throughout the organization.
It is important that the City establish the proper expectations within all levels of the organization prior
to initiating an ERP implementation. There are several reasons why large-scale system
implementations are not successful. Failure to set proper expectations and the lack of an adequate
staffing and resource planning can negatively impact the project from the beginning and create project
risks that adversely affect the project outcome.
47
Remediation and Action Plan Report Page 43 March 15, 2013
Appendix A: Detailed Challenges and Needs
The following table provides details on the challenges identified by City staff and BerryDunn. The
challenges are grouped by functional business area.
Detailed Challenges
General Ledger
• It was reported that the system does not report against an inactive account for a
prior year even if it has a balance.
• It was reported that the system provides the ability to post to the incorrect period.
• It was reported that the system provides the ability to post to control accounts.
• It was reported that the system provides the ability to edit sub-system batches
prior to posting to GL.
• It was reported that the City has to perform balance sheet reconciliation each
month for all accounts and this is how the City finds out that control accounts are
posted against, and keeps the outside modules in sync with the general ledger.
• It was reported that the number of funds is limited to 1000, limiting the number of
funds that can be kept as records.
• It was reported that SPS reports are used very infrequently and most reporting is
done using Cognos and MS Excel.
• It was reported that SPS reports are capable of reporting over custom time
periods, but doing so very substantially slows down the system.
• It was reported that journal entries cannot be uploaded to SPS and must be
entered manually.
• It was reported that files are not able to be attached to financial data and are
stored in Laserfiche without a linkage to financial information.
• It was reported that the description for each account is limited; data that is
allowable does not always appear on printed reports.
• It was reported that using canned reports to show a period of time is difficult; IT is
currently working on trying to improve this process.
• It was reported that all documents are housed in Laserfiche and not electronically
linked.
• It was reported that there is a system limitation on account security levels.
Budget
• It was reported that budget adjustments are a manual process that requires a
budget transfer form to be created and routed for approval external of the
system.
• It was reported that the system has the ability to create monthly budgets,
however other than the budget divided by 12 this has to be manually entered into
the system and is not an automated process.
• It was reported that budget detail is not transferred to the General Ledger.
• It was reported that a detailed budget is tracked through MS Excel.
48
Remediation and Action Plan Report Page 44 March 15, 2013
• It was reported that the revenue budget is created via MS Excel.
• It was reported that SLA (Service Level Agreement) budgets are created via MS
Excel.
• It was reported that end users found it easier to create budgets via MS Excel
therefore the City transitioned away from creating the budget directly within the
system.
• It was reported that the process of determining budget transactions with the
associated detail can be cumbersome.
• It was reported that users have to manually search in the system by purchase
order number, contract number, or bid number to determine detailed
transactional history.
• It was reported that users are not able to format canned reports.
Purchasing and Receiving
• It was reported that all PR’s require a commodity code.
• It was reported that the commodity code on the PR and the receiving file do not
always match.
• It was reported that approvals are based on General Ledger account and not the
actual item.
• It was reported that the purchasing system has a $10 million dollar limit.
o This causes multiple purchase orders to be created and the detailed
transactions are tracked in MS Excel.
• It was reported that if the Purchase Order and Invoice are an exact match that
the Purchase Order is automatically closed.
o This can create purchase orders with $.01 remaining and a change order
is needed to completely close out the purchase order.
• It was reported that change orders are a forms based process.
• It was reported that duplicate vendors exist.
• It was reported that most users prefer “green-screen” (as opposed to Naviline)
due to ease of use and functionality.
• It was reported that end user security is maintained in both versions of SPS
(green screen and Naviline).
• It was reported that PO/FPO numbers have to be reset manually every year for
the new fiscal year and that this is a time consuming process.
• It was reported that minor changes such as correcting a grammatical error or
account number are not possible without completing a change order.
Accounts Payable
• .
• It was reported that some staff have the login credentials for their supervisor and
carryout approval responsibilities assigned to their supervisor. (P-Card)
• It was reported that invoices are not currently able to be linked to payments.
• It was reported that SPS is able to report based on approved purchase orders
that need invoices, but not all purchase orders.
• It was reported that invoices and other documentation cannot be attached to
49
Remediation and Action Plan Report Page 45 March 15, 2013
records in PaymentNet.
• It was reported that SPS system does not support the City’s compliance with the
Prompt Payment Act.
• It was reported that a workflow process for the approval of Invoices does not
exist.
Bids
• It was reported that an electronic proposal may be submitted after the deadline
as long as the electronic submission was initiated prior to the deadline.
• It was reported that hard copy bids received must be manually entered into
Brazos Bid.
Inventory
• It was reported that ‘off-inventory’ items exist and are tracked mentally by
employees.
• It was reported that it is not possible to remove inventory items that are no longer
used.
Asset Management
• It was reported that there is no system being used to track capitalized assets
purchased through debt financing – currently tracking with Microsoft Excel.
• It was reported that current asset management system functionality has
limitations including data field sizes, search criteria, and depreciation codes.
• It was reported that it is not possible to electronically attach documentation to an
asset record.
• It was reported that the process of tracking land acquired from a developer or
from an easement is carried out manually.
Financial Reporting
• It was reported that there was an inability to generate an actual versus budgeted
report based on department – due to account rollup restrictions (i.e. when
department budgets cross funds).
• It was reported that control accounts can have journal entries made against
them.
• It was reported that batches, even payroll batches, are able to be edited with no
audit trail.
• It was reported that projection reports are produced manually.
Payroll
• It was reported that the demographic file gets locked periodically.
• It was reported that the intranet site shows incorrect accrual dates since pending
data is only displayed.
• It was reported that users are not able to enter benefits when payroll is being
processed. Benefit entry or updates cannot proceed until the payroll process is
closed.
• It was reported that the “pending” status contributes to a several system
challenges.
50
Remediation and Action Plan Report Page 46 March 15, 2013
• It was reported that the year-end process is manual and data entry intensive.
• It was reported that there are challenges with work orders because they are not
validated and updated until data has posted to the General Ledger.
• It was reported that users have the ability to update the payroll batch prior to
posting to the General Ledger introducing the potential for errors.
• It was reported that the ability to report from the system is challenging, and
multiple reports need to be run to get standard information.
• It was reported that once the TMRS (Texas Municipal Retirement System) file is
uploaded, the file itself is no longer available.
• It was reported that the system does not track employee re-hire status creating
challenges in use of the system.
• It was reported that some departments utilize data from Crystal Reports as well
as MS Excel in order to enter time into SPS.
• It was reported that there are challenges relating to the identification of
supervisors and an ability to understand reporting structures since there is no
organizational chart for reference in the system.
Treasury
• It was reported that there is duplicate data entry and reconciliation as information
is tracked in multiple sources.
• It was reported that there are limited staff at the City that understands how the
City’s debt is tracked in MS Excel.
• It was reported that the system currently only tracks the expenses associated
with a project and not the revenues or funding source.
• It was reported that MS Excel is used to maintain the debt schedules.
o This ultimately affects the utility rates charged to customers.
• It was reported that the system does not provide the ability to apply overhead to
projects that can be funded by debt.
• It was reported that the City did use Sympro in the past for tracking investments
but upgrades were not maintained and the City has since replaced the system
with Tracker.
• It was reported that an interface is available between Tracker and the
SPSsystem but due to the cost, the City has decided not to use this interface.
Accounts Receivable
• It was reported that an image of an invoice is not generated at the time of billing
creating the need to scan and store a PDF image of invoices.
Cash Receipts
• It was reported that a master MS Excel file is maintained to monitor deposits and
bank account details.
• It was reported that fees cannot be assigned a debit or credit code in JEMS.
Court
• It was reported that JEMS is not interfaced with SPS due to past challenges with
reconciliation.
51
Remediation and Action Plan Report Page 47 March 15, 2013
• It was reported that a monthly report is sent to Accounting to be entered
manually. A daily transactions report is also sent to Accounting.
• It was reported that parking citations are received electronically with data files,
but not an image of the citation.
• It was reported that a delinquency report is run daily, but there is no report or
notification of a new delinquent.
• It was reported that there are limitations on the availability of electronic storage
and that an additional server may be purchased in the future.
Human Resources
• It was reported that the current vendor does not have adequate training to
support the HR system.
• It was reported that getting information out of the SPS system is difficult. And
data integrity from the system is a concern.
• It was reported that with the SPS system there is a need to manage data in many
different places, and typically includes the need for a separate database or
spreadsheet to meet department needs.
• It was reported that the QISS claims management software does not integrate
with the SPS system making it difficult to report from disparate systems.
• It was reported that actuarial reports are challenging to support related to
gathering data from multiple sources, including from the system and manual
spreadsheets.
• It was reported that extracting employee information from the system regarding
Leave administration is a significantly manual process and the system does not
meet the reporting needs to provide departments information regarding their
employees.
• It was reported that the HR department utilizes many different systems and
modules which include third-party administrators and contracted services, each
of which have their own data structures that can’t be changed. This makes it
challenging to report information that must be assembled using several different
systems.
• It was reported that although the SPS system does have an employee grievance
module, but the information in the system is incomplete and thus not used.
• It was reported that equipment numbers within the SPS asset management
module must be manually identified when processing claims because the Fleet
system and the asset management system are not aligned.
• It was reported that there are many processes that require approvals, and
currently many of them require sending paper around the City to get the
necessary approvals.
Utilities, Water, Wastewater
• It was reported that multiple records may exist due to name spelling iterations.
This creates difficulty when sorting data to create reports.
• It was reported that sorting data in the system is challenging.
• It was reported that there is no map based work order system.
52
Remediation and Action Plan Report Page 48 March 15, 2013
• It was reported that the SPS system is difficult to push ESRI data into.
• It was reported that the system is not user-friendly, specifically that there were
reports of challenges with the user-interface.
• It was reported that the search function in the system is slow to create reports.
Multiple data fields need to be pulled in order to create the correct reports. This
leads to poor customer service.
• It was reported that it is difficult to pull out types of usage from multi-family
properties.
• It was reported that categorizing is difficult in the system due to multiple rate use.
It is difficult to create reports based on these rates and customer usage.
• It was reported that work orders are set-up as services requests initially, which
leads to missing details necessary for accounting.
• It was reported that street name retrieval is not a phonetic search, which creates
challenges for the department.
Utility Customer Service
• It was reported that there is limited work order automation.
• It was reported that name identification is the only way to find an account on the
customer master file screen, which leads to occasionally creating a new account
each time a customer requests service, even if they are a repeat customer.
• It was reported that challenges exist in looking up certificates of occupancy due
to a lack of workflow with the building permits module making it difficult for staff to
determine if electric service has been activated.
• It was reported that compactor data is collected and manually downloaded and
entered into the system.
• It was reported that a pending file exists in auto pay, but the system does not
recognize if that payment is not necessary. For instance, if a customer pays a
month ahead the system will still indicate that an amount is due.
• It was reported that autopay functionality exists but it is prone to errors and
requires manual reconciliation.
• It was reported that the Online Portal is not user-friendly. The bill displayed on
the portal is difficult for customers to understand.
• It was reported that there are two work order systems in use, which leads to
duplicate entries. Electric and wastewater each use their own (CityWorks) work
order systems and they are not integrated.
• It was reported that employees must manually look for work orders when they
are printed in advance during peak times. There is no automatic indication that
there is an order that needs to be fulfilled.
• It was reported that the report functionality is not user-friendly. It is difficult to pull
ad-hoc reports and create a report in a usable format.
• It was reported that there is no way to tell the difference between a no read and a
no usage meter read.
• It was reported that revenue reports do not meet departmental needs.
• It was reported that rate changes require changing all rates in the rate table.
53
Remediation and Action Plan Report Page 49 March 15, 2013
• It was reported that changes in credit cards create challenges with autopay.
• When reinstating an Auto Leave On or Residential Clean Up customer the last
read for that customer ID is in the hand-held device causing a failed reading
instead of giving the last read for the location.
Electric Utilities
• Departments outside Electric are using cost accounting incorrectly in the system.
It is being done with sub-ledgers (projects) instead of work orders.
• It was reported that budgeting cannot be done by work order.
• It was reported that budgeting is performed through use of spreadsheets to track
budgets and projection.
• It was reported that there are no forecasting tools in the system, so manual
calculations are made using spreadsheets.
• It was reported that during the implementation there were situations where
service orders were setup and configured for use, when it would have been more
appropriate to use work orders.
• It was reported that mapping capabilities do not meet departmental needs.
Fleet
• It was reported that some staff do not feel that they know the SPS system well
and could benefit from additional training.
• It was reported that there was no ability to archive inventory data.
• It was reported that incorrect mileage may be entered, leading to inaccurate
maintenance scheduling.
• It was reported that reporting is difficult for employees to sort data in a useful
way. For instance, there are no sub-categories.
• It was reported that the inventory module was reported as difficult for users. In
particular, partial look-ups and locating parts is done through scrolling down the
list of item numbers in order to issue parts to a work order. This was reported to
be inefficient and time consuming.
• It was reported that a cost is not associated with a part in the inventory module.
• It was reported that the re-ordering system was not used.
• It was reported that reference numbers in the fleet module do not match asset
management numbers.
• It was reported that the manual was not comprehensive, and staff have had to
essentially write their own process documentation.
• It was reported that management level data extracts are difficult, and staff cannot
create high level reports.
• It was reported that department codes vary from account codes, so when a
vehicle changes departments the vehicle history is lost.
• It was reported that the department uses two types of fuel cards, one with 10
digits and one with 16. The 16 digit codes do not fit in the SPS system.
• It was reported that fuel cards cannot be used to validate the amount of fuel used
at a site, leading to potential abuse of the fuel system.
Facilities
54
Remediation and Action Plan Report Page 50 March 15, 2013
• It was reported that duplicate entry is performed by entering the same data into
multiple fields.
• It was reported that SPS standard reports do not meet department needs.
• It was reported that management level reporting is not available.
• It was reported that receipts are used to track cost and noted on the paper work
order form.
• It was reported that adding a new building to the SPS system was difficult and
required navigating through many screens.
• It was reported that there is no automated inventory. Inventory re-ordering is
performed by their supply vendor.
• It was reported that a manual filing system is used to organize documents.
• It was reported that a calendar book is used to record when a work order was
performed.
Parks and Recreation
• It was reported that inventory is tracked manually.
• It was reported that multiple funds within the department caused challenges
interfacing with SPS.
• It was reported that RecTrac is currently used to carry out only the processes
that the previous system handled.
Planning and Development
• It was reported that irrigation inspections are not maintained in the system.
• It was reported that yearly renewal notifications can be challenging.
• It was reported that inspection scheduling is a manual process.
• It was reported that the inspection approvals are a manual process.
• It was reported that GIS integration is not available.
• It was reported that grease trap inspections are maintained through MS Excel.
• It was reported that customer service inspections are paper based in order to
follow to the state TCQ form.
• It was reported that the ability to search for properties in the system is difficult.
o Users have to enter the exact address.
• It was reported that there are duplicate addresses in the system.
• It was reported that attaching pictures is time consuming.
• It was reported that users find it difficult to access data in the system.
o Most users have to request data through Cognos.
• It was reported that users will export data to MS Excel for reporting purposes.
o However data that is exported to MS Excel requires additional formatting.
• It was reported that users are not able to see enforcement history in the field with
the Toughbook’s due to system performance and lack of functionality.
• It was reported that the import file from BCAD overwrites address data in the
system.
• It was reported that steps in the inspection process can be skipped in order to
keep the process moving forward.
• It was reported that users have to view two screens for permit number and
55
Remediation and Action Plan Report Page 51 March 15, 2013
address data.
• It was reported that the system does not provide a trigger or notification when a
temporary CO is about to expire.
• It was reported that users are not able to see an image of the CO in the system.
• It was reported that the City paid for an interface to Laserfiche but no one is
using the interface.
• It was reported that users would like the ability to attached documents that have
already been scanned in Laserfiche to the system.
• It was reported MS Excel is used for engineering inspections tracking.
• It was reported that the rental registration reconciliation process is manual and
time consuming.
• It was reported that the City can issue permits if a contractor has an expired
license.
56
Remediation and Action Plan Report Page 52 March 15, 2013
Identified Business Needs
This section of the report details the business needs identified by functional area/department at the
City. In some instances, similar needs were identified by several departments. These needs only
appear once in the table below and are not repeated in each functional area. The needs are
organized by functional area in the table below and are not in a priority order.
General Ledger
• The ability to expand the number of characters in each segment.
• The ability to expand segment descriptions.
• The ability to create reports without extensive IT involvement.
• The ability to have a potential segment to assign a reporting indicator.
• The ability to restrict GL posting by account number.
• The ability to have the system only display accounts that staff have access to
when entering a Journal Entry.
• The ability to edit data that is imported to the General Ledger prior to posting
(with audit trail).
• The ability to view a General Ledger audit trail report.
• The ability to import Journal Entries from MS Excel.
• The ability to attach documentation to accounts.
• The ability to drill into account level detail attachments.
• The ability to post statistical or non-financial data.
Budget
• The ability to import budget level entries via MS Excel spreadsheets.
• The ability to have system-wide comment/note data entry.
• The ability to have expanded transaction descriptions.
• The ability to budget by month without manual intervention.
• The ability to create budget projections based on current actual – with or without
multipliers or seasonality.
• The ability to drill down to salary and benefit details.
• The ability to conduct Year-To-Date Budget reporting.
• The ability to view budget data in a dashboard format.
• The ability to enter statistical data for a specific General Ledger Account (i.e.,
Performance Measures/Metrics).
Purchasing and Receiving
• The ability to generate a list of BPO’s available to departments that would allow
the user to click on a Vendor or Commodity to see the associated contract and
pricing.
• The ability to use an online catalog, which would allow the user to place a
purchase against the BPO within the same application.
• The ability to see all documentation associated with a transaction – PO, invoices,
checks, bids, etc.
• The ability to use contract management functionality within a system.
57
Remediation and Action Plan Report Page 53 March 15, 2013
• The ability to email notification for FPOs pending approval.
Accounts Payable
• The ability to use a workflow for invoice approval, payment, and creation of
voucher packets.
• The ability to attach digital copies of receipts and other supporting documentation
to records in SPS.
• The ability to automatically (proactively) notify end users of pending approval
actions.
• The ability to import the description and detail information stored in PaymentNet
to SPS.
• The ability to close out a PO if it is known that it is the final payment being made
against a PO.
• The ability to import detail transactional data for purchasing cards and attach
receipt images and other documentation.
Bids
• The ability to automate contract management.
• The ability to monitor vendor performance.
Inventory
• The ability to receive an automatic notification at reorder points.
• The ability to track department specific inventory.
Asset Management
• The ability to attach documents to asset records.
• The ability for the fleet and asset modules to be integrated with data sharing
capabilities.
• The ability to identify a portion of an asset that needs to be disposed of.
• The ability to track assets purchased with debt.
Financial Reporting
• The ability to have a robust report writer that produces presentation style reports,
and does not require substantial IT involvement to use.
• The ability to attach supporting documentation to journal entries.
• The ability to ensure that interfaces between modules stay in sync.
Payroll
• The ability to enter benefits prior to the end of the payroll period.
• The ability to track and report on employees on “light duty.”
• The ability to route supporting documentation with EAFs using workflow
processing for employee changes.
• The ability to perform historical reporting.
• The ability to allow an employee to view their check stub and make other
changes through a secure internet connection.
• The ability to perform W-4 “what-if” calculations.
• The ability to execute reports that combine performance evaluations with
58
Remediation and Action Plan Report Page 54 March 15, 2013
employee eligible for pay increases.
• The ability to calculate FLSA. This currently requires the use of Cognos reports.
• The ability to maintain W -2 totals. Currently maintained in MS Excel for end of
year reconciliation.
• The ability to access W -2s online. Currently employee W -2 forms must be
manually printed and mailed to employees.
• The ability to use an automated time-keeping system.
• The ability to use automated workflow process for EAF processing and
approvals.
• The ability to estimate and forecast staffing and benefit costs.
• The ability to report and monitor part-time employees to maintain compliance
with new regulations.
Treasury
• The ability to monitor debt tracking in the SPS system.
• The ability to track funding sources with projects in system and by bond issue.
• The ability to track multiple funding sources when bonds are issued in multiple
years for a project.
• The ability to transfer funding sources from one project to another project if the
project is completed under budget.
Accounts Receivable
• The ability to produce PDF images of invoices automatically when printing as
opposed to scanning the printed version of the invoice image.
• The ability to email invoices to a customer through the SPS system.
• The ability to issue and track permits for liquor and sales (door to door).
59
Remediation and Action Plan Report Page 55 March 15, 2013
Cash Receipts
• The ability to enter payment information into one central cashiering system or
ability to have stand-alone systems interface with SPS.
Court
• The ability to program an offset account (financial account) in JEMS is needed.
This would assist with automating an interface between JEMS and SPS again.
Human Resources
• The ability to view dashboard displays for certain data to report such things as
number of accidents, employees on leave, or other information that user
departments may want to regularly view.
• The ability to track contracts and in particular insurance certificates for
contractors doing business with the City.
• The ability to provide notification of certificates that need renewal or will soon
expire.
• The ability to show trends and provide departments the ability to understand
historical information was reported as one of the most significant needs.
• The ability to utilize incident reporting capabilities.
• The ability to create an organizational chart. Currently all departments do their
own which feeds into incident management challenges.
• The ability to automate the claims process and include the ability to route claims
to the appropriate staff for approvals.
• The ability to track all training provided to employees allowing supervisors access
to this information.
• The ability to electronically create position postings and route them for approval
electronically was a reported need
• The ability to provide an employee and manager self-service portal.
• The ability to identify supervisors and managers by creating and maintaining an
organizational chart.
Utilities, Water, Wastewater
• The ability to integrate with the mapping system.
• The ability to visually map an issue or service request.
• The ability to geo-reference records.
• The ability to report at the management level.
• The ability to tie end user reporting to the SQL database.
• The ability to link assets to a location.
• The ability to identify people related to a water program.
Utility Customer Service
• The ability to create user-defined sorting and printing, for instance, sort by routes
in addition to cycles or reverse a route.
• The ability to receive notification of electrical service completion for new
construction.
• The ability to have workflow automation to determine rates and changes for
60
Remediation and Action Plan Report Page 56 March 15, 2013
commercial accounts to reduce the manual effort and diminish the opportunity of
missing or inaccurate rates for service.
• The ability to have the due dates for senior customers handled by the system,
both for payments and meter readings.
• The ability to automatically send drafts to the bank.
• The ability to upload compactor account information into the system to reduce
manual entry of this account information.
Electric Utilities
• The ability to view quantity in addition to units.
• The ability to budget and forecast in the SPS system.
Fleet
• The ability to electronically notify departments when a pick-up is ready.
• The ability to associate cost with each part in the inventory module.
• The ability to track warranty information.
• The ability to attach PDF’s to work orders.
• The ability to track vehicle history across asset management.
• The ability to link codes for a new item to the same, existing item.
• The ability to generate a report that showed the cost of all vehicles, created by
vehicle type.
• The ability to have vehicles on a citywide numbering system.
Facilities
• The ability to use electronic work orders.
• The ability to use a workflow for work orders.
• The ability to generate a cost report.
• The ability to enter equipment on the building information screen.
• The ability to track equipment.
• The ability to track preventative maintenance.
• The ability to track materials and report on challenges with equipment types
(brands) so that faulty equipment will no longer be purchased.
• The ability to geo-locate for major facility systems.
• The ability to link inventory to identify commonly used equipment.
Planning and Development
• The ability to distribute inspections geographically.
• The ability to view inspection notes for all inspection types.
• The ability to integrate and utilize GIS.
• The ability to have one screen to view all inspection and violation data.
• The ability to flag a contractor in the system with an outstanding balance.
• The ability to email/notify approvers by permit type.
• The ability to print the CO and permit that is posted onsite.
• The ability to attach pictures of inspections and violations in order to determine if
issue has been fixed in a follow up inspection.
• The ability to automate the walk-through permit process.
61
Remediation and Action Plan Report Page 57 March 15, 2013
• The ability to provide notification when impact fees and park land dedication fees
are required.
• The ability to verify registered state licenses and insurers for contractors.
• The ability to calculate the square footage value calculation.
• The ability to calculate the engineering fee.
o 1% of total project.
• The ability to take bond payments in order to release partial payments.
• The ability to indicate the type of permit needed based upon the plan submitted.
• The ability to provide a notification when a contractor’s license is about to expire.
62
March 28, 2013
Workshop Agenda Item No. 6
Wellborn Area Plan
To: Frank Simpson, Interim City Manager
From: Bob Cowell, AICP, CNU-A, Executive Director of Planning & Development Services
Agenda Caption: Presentation and discussion regarding the Wellborn Area Plan.
Relationship to Strategic Goals: Financially Sustainable City, Core Services and
Infrastructure, Neighborhood Integrity, Improving Mobility, and Sustainable City.
Recommendation(s): This item is an update for informational purposes only. No action is
requested at this time.
Summary: The planning process for the Wellborn Area Plan, a component of the College
Station Comprehensive Plan, began in late 2011 and is currently being finalized for
adoption. This is the fourth neighborhood, district, corridor planning effort since the
adoption of the Comprehensive Plan in 2009. The Wellborn Area Plan engagement process
began with an area-wide kick-off meeting in February of 2012, and was followed by several
other area-wide meetings throughout 2012 and 2013.
A Wellborn Resource Team, composed of area residents, land owners and business owners,
was also formed and met numerous times throughout the process to provide additional
guidance to the formation of the plan’s goals, strategies, and actions. An Open House was
held at the beginning of March to obtain feedback from area residents on the draft plan.
Through these efforts, Staff has worked with the Wellborn Community to develop a plan to
address community character, land use, and mobility.
Staff will provide a draft copy of the Wellborn Area Plan as well as an overview of the
document at the March 28th Workshop. This information is being delivered to the Council in
advance of a public hearing, presentation, possible action, and discussion regarding
adoption of the plan scheduled for the April 25th Council meeting. The Bicycle, Pedestrian,
and Greenways Advisory Board will consider the mobility components of the Plan and make
a recommendation at their April 1st meeting while the Planning and Zoning Commission will
hold a public hearing and make a recommendation regarding the plan at their April 4th
meeting.
Budget & Financial Summary: Details provided in the Plan.
Attachments:
1. Draft Wellborn Area Plan (provided at meeting)
63