HomeMy WebLinkAboutFCS-13-126 - Customer Information System (CIS) Replacement Initiative Update
REPORT TO:
Finance and Corporate Services Committee
DATE OF MEETING:
September 9, 2013
SUBMITTED BY:
Dan Chapman, Deputy CAO and City Treasurer
PREPARED BY: Alex Ahkoon, Manager, ERP Business Transformation
WARD(S) INVOLVED:
All
DATE OF REPORT:
September 4, 2013
REPORT NO.: FCS-13-126
SUBJECT: Customer Information System (CIS) Replacement Initiative
Update
RECOMMENDATION:
For information
EXECUTIVE SUMMARY:
In November 2012, Council passed a resolution to the effect that a final decision to proceed with
the acquisition and implementation of an SAP Customer Relationship & Billing (CR&B) solution
to replace the Customer Information System (CIS) be referred to the 2014 budget process.
Council also directed staff to undertake additional due diligence work, and report back prior to
Council’s consideration of the 2014 budget, on the following: (1) investigate the feasibility of
outsourcing the City’s utility billing functions, (2) further evaluate the option to re-architect CIS,
(3) monitor the City of Toronto’s initiative to acquire and implement SAP CR&B, and (4) develop
funding options for the implementation of SAP CR&B at the City.
EPCOR’s estimate for providing outsourced utility billing services to the City included a one-time
systems setup cost of $2.7M and a monthly operating cost of $1.5M. The monthly amount
would exceed the one-time cost of implementing SAP CR&B at the City in 14 months.
The revised estimates for re-architecting CIS call for a 4-year project as a cost of $12.4M.
Although this cost is approximately 38% lower than the cost of implementing SAP CR&B, a
project to re-architect CIS carries significant risks that can quickly lead to project delays and
cost overruns. A 4-year implementation project for a single application is very long by current IT
standards and the likelihood of rework due to changing business requirements is particularly
high. The long duration also increases the probability of failure, either in terms of cost, schedule
or functionality.
The re-architected application will retain many weaknesses associated with custom-built
applications such as slow adoption of new technologies, slow delivery of new functionality, lack
of broad user and vendor community support, and low probability of adopting best practices,
and will likely need to be replaced itself in 10 years.
7 - 1
The City of Kitchener’s Corporate Business Technology Strategy and industry trends strongly
support buying technology solutions and not building internal systems, especially in the case of
enterprise applications such as CIS.
The City of Toronto’s latest internal estimate for implementing SAP CR&B is in the $20M to
$30M range. It is now considering its approach for a Request for Proposal (RFP) to engage a
systems integrator to assist in establishing a more accurate Total Cost of Ownership (TCO)
figure through a proper scoping and fit-gap analysis exercise, similar to the City of Kitchener’s
SAP CR&B Blueprint engagement.
Staff is proposing to fund the SAP CR&B implementation project through existing CIS balances
and projected capital Reserve Fund balances from the utility Enterprises while adhering to the
minimum balance requirements identified in the Reserve Fund Policy previously approved by
Council. The proposed funding model ensures no impact to the existing tax base.
Based on the available funds and a revised SAP CR&B implementation cost of $19.9M, the
current shortfall in project funding is approximately $4.0M (20%) and is primarily split between
the Sanitary Sewer and Storm Water Utilities.
Staff are considering a combination of alternatives to address the project shortfall and will report
back on the outcome of this further review as part of the 2014 budget process.
Based on the findings of the recent due diligence work, an SAP CR&B implementation remains
the preferred option as a replacement for CIS.
BACKGROUND:
The Customer Information System (CIS) is a critical application that supports the City of
Kitchener’s property tax and utilities revenue management and billing processes.
The amount billed through CIS annually is by far the largest intake of revenue for the City. The
2011 and 2012 figures, following the introduction of the stormwater utility, are shown below.
Tax / Utility / Fees Billed 2012 Revenue ($ Million) 2011 Revenue ($ Million)
Property Tax 350.6342.0
Gas 75.4 94.9
Water 33.8 32.3
Sewer 36.8 35.4
Stormwater 12.3 12.5
Rental Water Heater 8.37.8
Administration Fees 0.30.4
Total 517.5 525.3
Over 910,000 utility and 138,000 property tax bills were produced and in excess of 1.28 million
payments were processed by the application last year.
7 - 2
CIS Replacement Initiative History
The initiative to pursue a replacement for CIS was launched in 2010 following an assessment of
the application by Prior & Prior Associates Ltd. (Prior & Prior) in 2009-2010.
Subsequent to assessing the application’s strengths and weaknesses, Prior & Prior identified
and evaluated three go-forward alternatives for CIS: (1) continue with the current system; (2)
buy an off-the-shelf solution; and (3) re-architect CIS to a more sustainable architecture and
technology platform. The recommendation was for the City to pursue the acquisition of SAP tax
and utility solutions to replace CIS, which had been in production for approximately 10 years.
Further to Council’s approval of the recommendation, staff conducted additional due diligence
research into the SAP Customer Relationship & Billing (CR&B) solution in 2011 and 2012 to
confirm its ability to meet the City’s requirements and to validate it as the preferred go-forward
option for the City.
The findings of the additional research and the resulting recommendations were presented to
Council in Staff Report FCS-12-040 in March 2012 and Staff Report FCS-12-176 in November
2012, and are summarized below.
Staff Report FCS-12-040 – Customer Information System (CIS) Replacement
Between June 2011 and March 2012, staff conducted additional research into SAP CR&B,
focussing on two key areas: (1) the ability of SAP CR&B to meet the City’s Property Tax and
Utilities Revenue Management & Billing requirements, and (2) the estimated effort and costs to
implement the solution.
The research validated the SAP CR&B solution’s capabilities:
Successfully implemented at Enbridge Gas (1.9M customers)
Selected for flexibility and powerful billing engine
Met all 2,800 documented requirements including Gas Distribution Access Rules (GDAR)
Right decision to select SAP CR&B
Successfully implemented at London Hydro (150,000 customers)
Selected for strength and flexibility
SAP CR&B right solution but choose systems integrator carefully
Successfully implemented in two municipalities for property tax billing
Cape Breton Regional Municipality, Nova Scotia (106,000 residents)
Billing for about 65,000 property tax accounts and 30,000 water utility accounts
No complex customizations needed to support property tax functionality
City of Cape Town, South Africa
Additionally, it showed that SAP CR&B was the only commercial software already in production
supporting both GDAR and property tax billing, two key differentiators for the City.
In December 2011 the City retained the services of SJH Consulting (SJH) for a 3 person-day
engagement to conduct a high-level scoping of an SAP CR&B solution that would meet the
City’s requirements and to produce a high-level implementation cost estimate. SJH’s estimate
was $16.4M.
7 - 3
Implementation costs mentioned by utility companies that staff contacted varied widely, ranging
from $10M to $180M. However, expressed as a percentage of revenue, the costs were all
within a small 3-4% range as shown in the following table.
Impl. Costs Annual Rev.% of
($ Million) ($ Million) Revenue
City of Kitchener (high-level estimate)16.4 525.0 3.12%
Enbridge 120.0 2,900.04.14%
London Hydro 10.0337.0 2.97%
EnWin (blueprint estimate) 16.0400.0 4.00%
Hydro One (in progress) 180.0 5,124.0 3.51%
Staff also documented specific examples of issues and inefficiencies that had occurred in CIS in
recent years which further reinforced Prior & Prior’s assessment that CIS does not provide a
sustainable long-term solution for the City. These are included in Appendix A for reference.
Based on the findings of the research undertaken, staff concluded that an SAP CR&B solution
remained the preferred option for the City as it provides the following:
Robust system for the next 15-20 years
Flexible and configurable solution
Solid foundation for e-services, mobile computing and smart meters
Continued functional and technical enhancements through regular upgrades
Access to a large community of users and implementers
Alignment with the City’s corporate technology direction and industry trends to buy not build
enterprise applications
Ability to leverage industry best practices
Opportunity to capitalize on investment already made in SAP
The following recommendations were made to Council and approved:
That staff be directed to continue to pursue the acquisition of the SAP Customer
Relationship & Billing (CR&B) solution to replace the existing Customer Information System
(CIS); and further
That staff be directed to issue an Expression of Interest for a business requirements
discovery and solution scoping engagement to deliver a detailed business design document
and the technical specifications of an SAP CR&B solution that meets the City’s
requirements, and to establish a detailed budget for implementation.
Staff Report FCS-12-176: Customer Information System (CIS) Replacement Initiative Update
In May 2012, Sparta Consulting (Sparta) was awarded the contract to perform the business
requirements discovery and solution scoping work. The SAP CR&B Blueprint project was
launched a month later with several key City resources from the business areas and from IT
assigned to work with Sparta on the project.
7 - 4
The blueprinting workshops revealed the following:
Good degree of fit between the City’s requirements and SAP CR&B’s functionality with few
critical gaps identified
Confirmation that SAP CR&B’s utility-focused data model could accommodate the property
tax data structure requirements with no extensive work-arounds
Some areas require higher level of effort than normally encountered in an average SAP
CR&B implementation
Development work to support Property Tax requirements
Configuration effort to meet GDAR requirements for a gas distributor
Reports and customer letters development – over 350 currently available in CIS
Historical data conversion – substantially higher than average
Based on the assessed scope of work to be undertaken and the associated effort, Sparta
estimated the SAP CR&B implementation project would be 17 months in duration, including 2
months of post go-live support.
The total cost of the project was estimated at $22.8M, including systems integrator costs, staff
costs, software licenses, hardware, training, legal costs, various miscellaneous charges, and a
20% contingency. This implementation cost estimate amounted to 4.34% of the City’s 2012
property tax and utilities revenue of $525M, falling just outside the upper end of the 3-4% range
noted in previous research.
Given the sizeable increase over initial estimates, staff recommended that further due diligence
work be undertaken and that the final approval to proceed with an SAP CR&B implementation
be referred to the 2014 budget process.
Based on the report’s recommendations, Council passed the following resolution:
That final approval to proceed with the acquisition and implementation of an SAP Customer
Relationship & Billing (CR&B) solution to replace the Customer Information System (CIS) be
referred to the 2014 budget process; and further
That staff be directed to undertake the following additional due diligence prior to Council's
consideration of the 2014 budget:
a) Continue to monitor the City of Toronto’s initiative to acquire and implement SAP
CR&B to replace its property tax and utility billing systems;
b) Develop funding options for a future SAP CR&B implementation;
c) Evaluate in further detail the option to re-architect CIS as identified in the 2010 CIS
Assessment; and
d) Investigate the implications and feasibility of outsourcing the City’s utility billing
functions, and/or other means to separate the City’s utility billing function from the
property tax billing system.
7 - 5
The current report presents the findings of the further due diligence work undertaken by staff as
directed by Council in advance of the 2014 budget review.
REPORT:
Staff undertook the following work in 2013 as per Council’s direction:
Contacted EPCOR Utilities Inc. (EPCOR) and Enbridge Gas Distribution Inc. (Enbridge) to
investigate the feasibility of a potential utility billing outsourcing arrangement
Reviewed in further detail the option to re-architect CIS into a more sustainable system
Contacted the City of Toronto for an update on its initiative to replace its existing property
tax and utility billing systems with SAP CR&B
Developed a funding model for an SAP CR&B implementation
In addition, Sparta proposed to work with staff to review and revise the implementation cost
estimates from the 2012 SAP CR&B Blueprint engagement, at no cost to the City, taking into
consideration certain factors that had not been considered in the original estimate.
The findings of each piece of work are discussed below.
Implications and Feasibility of Outsourcing Utility Billing Functions
Staff contacted EPCOR and Enbridge in May 2013 to investigate the possibility of outsourcing
the City’s utility billing functions to either company.
Since gas utility billing and GDAR are two key requirements for the City, staff approached
Enbridge as it is likely the only Ontario gas distributor with the capability to enter in an
outsourcing arrangement with the City of Kitchener. It implemented SAP CR&B in 2009 to
replace its legacy billing system and already provides billing and payment processing services
rd
to 3 party companies selling energy-related products and services. Union Gas is currently
assessing its legacy billing system and the City of Kingston runs a simpler version of the CIS
application.
EPCOR was contacted at the suggestion of Councillor Berry Vrbanovic as it currently bills utility
customers on behalf of the City of Edmonton.
EPCOR
EPCOR is wholly owned by the City of Edmonton and bills electricity, water, wastewater,
drainage and waste management services for the city. It owns and operates electricity
transmission and distribution as well as water and wastewater facilities in various jurisdictions in
Alberta, British Columbia, Arizona and New Mexico. It also bills a small number of gas
customers for a gas retailer in Alberta.
EPCOR is open to an outsourcing arrangement with the City of Kitchener to provide the full
scope of customer care and billing services for the City’s utilities – gas, water, wastewater and
stormwater – as well as rental water heaters. However, the City would remain responsible for
all functions related to meter reading and maintenance, rental water heater maintenance and
administration, and stormwater category code administration and credit approval. EPCOR
would refer all customer enquiries in these areas back to the City.
7 - 6
Based on information supplied by staff on each City utility (summarized in the table below),
EPCOR submitted a preliminary estimate for its services.
UtilityRate Structure Daily Interfaces Required # of
Customers
Gas Consumption 67,000
GDAR compliant interfaces to gas
retailers (XML based)
Itron meter reading system
Work order system to manage
meter reading issues
Meter and serial numbers
Water Consumption
62,000
Itron meter reading system
Work order system to manage
meter reading issues
Meter and serial numbers
Wastewater Off of water 62,000
consumption
Stormwater Fixed charges based 70,000
GIS system – category code based
on category codes
on calculated impervious area
and number of
Need ability to manually override
dwelling units
Rental Water Fixed charges based 40,000
Inventory System (SAP) for serial
Heateron water heater type
numbers
EPCOR estimated a 12-month project with three full-time EPCOR resources and a one-time
total information systems setup cost of $2.7M. The earliest start date for the project would be
June 2014 as EPCOR resources are not available until then.
The estimated operating cost is quoted at $5.02 per service instance per month. Each utility
provided at a service location is defined as a service instance. Based on a total of 301,000
service instances, the estimated operating cost for the City amounts to $1.5M per month or
$18.1M annually.
Given the high operating cost, staff did not spend additional effort working with EPCOR to refine
these preliminary estimates. Should the City decide to pursue this option further, detailed
business and technical requirements would need to be documented, the division of
responsibilities between the City and EPCOR determined, and an organizational impact
assessment undertaken so that a proper cost-benefit analysis can be performed.
Staff believe EPCOR’s $2.7M systems setup cost estimate may be low given the City’s own
implementation experience with GDAR and the stormwater utility:
Implementation of GDAR Phase 1 in CIS was a 12 person-years effort and cost $1.5M
GDAR Phase 2 was estimated to be another 12 person-years effort at an additional cost of
$1.3M
Implementation of the stormwater utility has so far taken 6.6 person years and cost
$598,000.
7 - 7
High-level cost estimates for City staff amount to $1.7M based on 10-15 business and IT staff
assigned full-time to the project and another 10-15 business subject matter experts involved
part-time during the design and testing phases.
Enbridge
rd
Enbridge’s Open Bill Access service provides 3 party companies offering energy-related
products and services the opportunity to invoice their customers through Enbridge.
The included business services depend on whether the service agreement is for in-franchise
customers, i.e. customers within the Enbridge franchise area, or ex-franchise customers, i.e.
customers outside of Enbridge’s franchise area.
The following table lists the service included in each.
Business Services In-Franchise Agreement Ex-Franchise Agreement
Billing
Included Included
rd
Invoicing
Enbridge invoices 3 party branded invoices
Payment Processing
Included Included
Collections
Included Not available
Customer Service
General enquiries only on Not available
rd
3 party charges
Detailed product- or
service-specific enquiries
rd
referred back to 3 party
contact centre
Meter Reading
Not available Not available
The Open Bill Access fee structure for in-franchise agreements consists of the following:
94.1c per bill per month for bills that include Enbridge gas distribution charges
$2.157 per bill per month for bills that do not include Enbridge gas distribution charges
0.54% of monthly charges retained to cover bad debt cost – Enbridge guarantees 99.46% of
rd
monthly charges remitted to the 3 party biller 21 days after invoice date
Pricing for ex-franchise agreements are typically based on number of bills but Enbridge will
entertain various pricing models if necessary. Enbridge had only one ex-franchise agreement
rd
and charged the 3 party company $1.90 + postage per bill based on simple fixed monthly
billing charges and 30,000 bills per month. The cost per bill for the City of Kitchener will be
substantially higher given the higher processing complexity of multiple metered utilities billing
The Open Bill Access Settlement Agreement approved by the Ontario Energy Board (OEB)
rd
prevents Enbridge from billing commodities (water, gas, electricity) for 3 party companies in its
rd
franchise area. As such, Enbridge’s billing system only supports non-commodity 3 party billing
rd
and its system interface to 3 party applications is not designed to receive metered
consumption data. Significant changes will be required to support the City’s metered utilities.
Enbridge indicated that it is open to discussing an outsourcing arrangement with the City.
However, with a major hardware upgrade project currently underway and a software upgrade
7 - 8
planned for 2014, it advised that further discussions will not be possible until late 2014 at the
earliest.
Additional Outsourcing Considerations
Several additional factors need to be considered in an outsourcing model, some of which can
add significant costs:
Organizational impact on staff can be significant and affect morale and productivity
Loss of unionized positions can result in difficult Labour Agreements negotiations with the
bargaining units
Continued compliance to the Municipal Freedom of Information and Protection of Privacy
Act (MFIPPA) by the service provider must be ensured – EPCOR’s Privacy Policy for
Personal Information is based on Alberta’s Freedom of Information and Protection of Privacy
Act (FOIP) and Enbridge falls under the federal Personal Information Protection and
Electronic Documents Act (PIPEDA)
Continued compliance to the City of Kitchener Records Retention By-Law and Schedule by
the service provider must be ensured
Access to the City’s customer data in the service provider’s billing system database must be
available to staff for ad-hoc enquiries and reporting
Outsourcing the City’s utility billing functions is not a cost-effective option. EPCOR’s
estimated one-time systems setup cost of $2.7M and monthly operating cost of $1.5M would
exceed the implementation cost of SAP CR&B at the City within the first year.
Kitchener-Wilmot Hydro – Opportunities for Collaboration
Kitchener-Wilmot Hydro (KW Hydro) is currently in the early stages of an investigation to
replace its Customer Information System. Staff met with KW Hydro in June 2013 to share the
approach and the findings of the City’s CIS Replacement Initiative and review opportunities for
collaboration between the two organizations.
The participants from KW Hydro included Mr. Jerry Van Ooteghem, President and CEO, Ms.
Rhonda Yaraskavitch, Director of Customer Service and Conservation, and Mr. Andy Borthwick,
Manager of IT.
Mr. Van Ooteghem indicated that they plan to make a recommendation to the KW Hydro Board
in the Fall of 2014. KW Hydro has so far evaluated the North|Star CIS product from North|Star
Utilities Solutions. North|Star CIS is widely used by hydro companies in Ontario.
KW Hydro is also looking into joining a planned regional hydro cooperative. The Ontario Energy
Board (OEB) has been encouraging hydro companies towards joint initiatives to reduce costs
and the regional cooperative provides a means to do that. Waterloo-North Hydro, Guelph Hydro
and Halton Hills Hydro have already committed to the cooperative with up to seven more
expected to join over time.
7 - 9
Systems acquisition and implementation is an obvious candidate for a joint initiative. Waterloo
North Hydro is taking the lead on this and has released a Request for Proposal for a CIS and
other systems.
A second meeting with KW Hydro is planned for November 2013 to continue the information
sharing and to remain aware of any opportunities to collaborate.
Re-Architecting Current CIS Application
Re-architecting CIS was identified and evaluated as an option in the Prior & Prior 2010 CIS
Assessment Report to address the many deficiencies and weaknesses of the application.
The high-level cost estimate for re-architecting CIS was projected to be $3.84M over a 4-year
period, broken down as follows:
Project Team Year 1 Year 2 Year 3 Year 4 Total
Design Consultants (2) 600,000 600,000
Contract Developer (1) 300,000300,000300,000 900,000
Internal Developers
187,558567,260567,260567,260 1,889,338
(2) Year 1, (6) Years 2-4
Business - Utilities 270,000
Business - Property Tax 180,000
Total3,839,338
In July-August 2013 staff reviewed the technical sections of the CIS Assessment Report and
focussed in particular on the key root causes of the technical architecture weaknesses that
would need to be addressed:
CIS was developed using an early pre-2000 technical architecture of the PowerBuilder
development platform and original design did not take advantage of the strongest features of
the development platform with respect to object-oriented design and programming
Original design extensively used coding techniques which resulted in complex coding
structures and paths that have become very difficult to maintain
Billing modules are very large and changes to the code require extensive testing to ensure
other areas of the system have not been adversely impacted
System architecture was not designed to be configurable and does not easily cope with
business and regulatory changes or support ongoing enhancements and maintenance
CIS database design includes a very large number of tables, treating every object as unique
instead of taking advantages of the commonalities of many objects to simplify the database
table structure, creating unnecessary complexity
Staff also reviewed a number of key system enhancements identified in the Assessment Report
that would be in scope for any project to re-architect CIS, such as:
Automation of the property tax collection processes
Increased automation of the property tax adjustments functions and property tax billing runs
GDAR Phase 2 enhancements planned but deferred
7 - 10
Bill messaging flexibility
On-line enquiry capability for customers
Archive and purge functionality
Based on the above, staff concluded that a more extensive re-design of CIS than originally
envisioned by Prior & Prior is required to develop a more efficient, more sustainable and easier
to maintain system capable of supporting the constantly evolving requirements of the City over
the next 10 years.
Prior & Prior’s estimates were premised on the assumption that a good part of the current CIS
design and code could be kept, therefore reducing the scope of the re-architecting effort.
However, due to the tightly coupled nature of the application code and database design that is
in large part the cause of the system’s inflexibility, staff indicated that it would be difficult to re-
design and re-write only sections of the application without impacting the overall efficiency of the
system.
According to staff, the scope of the re-architecting effort to achieve the desired result of an
efficient and sustainable system for the long term will be closer to that of a complete re-write of
CIS from scratch. As such, the revised cost estimates to re-architect CIS is substantially higher.
The revised project team resource requirements to perform this more comprehensive work and
the associated resource costs (including fringe benefits for staff) estimates are shown below:
Project Team Year 1 Year 2 Year 3 Year 4 Total
Lead Architect - Utilities (Consultant) 240,000 240,000 480,000
Architect – Property Tax (Consultant) 220,000 220,000 440,000
Technical Lead 125,000 125,000 125,000 125,000 500,000
System Analyst - Utilities 110,000 110,000 110,000 110,000 440,000
System Analyst - Property Tax 110,000 110,000 110,000 110,000 440,000
DBA125,000 125,000 125,000 125,000 500,000
QA Lead - Utilities 110,000 110,000 110,000 110,000 440,000
QA Lead - Property Tax 110,000 110,000 110,000 110,000 440,000
SME - Utilities (2) 200,000 200,000 200,000 200,000 800,000
SME - Property Tax 100,000 100,000 100,000 100,000 400,000
Contract Project Manager 220,000 220,000 220,000 220,000 880,000
Contract Developers – Utilities (6) 450,000 900,000 450,000 1,800,000
Contract Developers - Property Tax
450,000 900,000 450,000 1,800,000
(6)
Contract QA Analysts (6) 360,000 720,000 720,000 1,800,000
UAT Business Resources (8) 800,000 800,000
Total 11,960,000
Hardware and software costs for the new development, testing and production environments
are estimated to be approximately $400,000 for a total cost estimate of $12,360,000.
A project to re-architect CIS also carries a number of significant challenges and risks that will
need to be mitigated:
7 - 11
Availability of a Lead Architect with industry knowledge and PowerBuilder experience will
likely be an issue
Limited in-house expertise and experience in object-oriented design concepts and coding
techniques may lengthen the design and development effort
Higher risk of project delays and cost overruns exists
Backfill resources will be needed for an extended period of time to support the existing CIS
application
High probability of re-work due to new or changes to business requirements and/or business
processes being introduced during the 4-year project
Significant level of system and user acceptance testing will be required to ensure the quality
of what will essentially be a new complex custom-developed application
Fatigue can set in within the project team and the organization given the extended 4-year
project timeline
In addition, many disadvantages associated with custom applications will remain, such as:
Lack of broad external user and vendor community support
Low probability of following best practices
On-going need for regression testing with each new version of the development, database,
or operating system software
Slow delivery of new or revised system functionality, inconsistent with industry norms
Slow adoption of new technologies
These all lead to a higher on-going cost of innovation and support and a shorter lifespan for in-
house solutions when compared to a Tier 1 commercial software solution. A re-architected CIS
will likely need to be replaced itself in about 10 years.
For these reasons, both the City’s own Corporate Business Technology Strategy and industry
trends make a strong case of buying technology solutions and not building internal systems.
The significant risks associated with the 4-year project and the inherent deficiencies and
shorter life expectancy of custom-built applications outweigh the lower estimated cost to re-
architect CIS compared to an SAP CR&B implementation.
City of Toronto SAP Customer Relationship & Billing Initiative Update
The City of Toronto (Toronto) started considering the replacement of its ageing custom-built
property tax billing system and utility billing system in 2009. Following responses received to
two Request for Information (RFI) documents issued in April 2009 and October 2010, as well as
further discussion with SAP, the City of Toronto began pursuing the SAP CR&B solution to
potentially replace its legacy billing applications.
Toronto is currently reviewing the next steps for its initiative.
The city had originally established a high-level budget estimate of $15M for a new integrated
property tax and utility billing application, either custom-built or a commercial software package.
7 - 12
However, the latest high-level estimates by its ERP Competency Centre have increased to the
$20-30M range and the city agrees that it needs to engage a systems integrator to assist in
establishing a more accurate total cost of ownership (TCO) figure through a proper scoping and
fit-gap analysis exercise, similar to the City of Kitchener’s SAP CR&B Blueprint Project.
Toronto is now considering its approach for securing this work – either through a Request for
Proposal (RFP) for an SAP CR&B-specific TCO engagement or through an open RFP which
may possibly uncover an alternative which was not available when the original RFI’s were
issued.
A similar open RFP has not been considered by City of Kitchener staff since a key differentiator
for the City is the commercial software’s proven ability to support Ontario’s GDAR requirements.
In addition, as noted in the CIS Assessment Report and confirmed by staff through further
research, utility billing software vendors have shown little interest in adapting their software for
the limited Ontario market which consists of only four gas distributors: Enbridge, Union Gas, the
City of Kitchener and the City of Kingston.
The City of Toronto expects to make a final decision on their RFP approach by year end.
SAP CR&B Implementation Project Funding
Prior to developing a funding model for an SAP CR&B implementation for consideration in the
2014 budget as directed by Council, staff agreed to a proposal from Sparta to further consider
opportunities to reduce the implementation cost estimate from the 2012 SAP CR&B Blueprint
engagement.
The revised implementation cost estimate and the proposed funding model are documented
below.
Revised Implementation Cost Estimates
At the time of the SAP CR&B Blueprint engagement in 2012, staff instructed Sparta not to
consider the use of any Sparta accelerators (tools and templates that can reduce time and effort
on a project) in estimating the implementation costs of an SAP CR&B solution at the City. This
direction was provided as the eventual selection of a systems integrator for the implementation
phase through a subsequent Request for Proposal (RFP) process could result in a different
vendor be awarded the work.
In April 2013, Sparta proposed to work with staff, at no cost to the City, to conduct a review of
the implementation effort and cost estimates based on the following criteria:
Use of Sparta-specific accelerators
Sparta indicated that most, if not all, major SAP CR&B systems integrators should have their
own set of accelerators that should be able to offer comparable reduction in overall
implementation effort.
Use of an SAP Business Intelligence (BI) platform to support reporting requirements
7 - 13
Implementing an SAP BI solution allows for a reduction in report development effort through
the availability of standard industry-specific and function-specific reports and a decreased
reliance on IT for one-off customized reports. A BI platform additionally presents the
advantage of providing a user-friendly ad-hoc reporting and enquiry toolset as well as
delivering a foundation for future business analytics capabilities and management and
operational dashboards.
Full-time availability of City staff designated as core project team members
Core project team members who are fully dedicated to the project on a continuous basis,
with no interruptions from their regular functions, enable a more productive environment and
allow for faster decisions and faster completion of project tasks.
Leveraging the above factors, Sparta revised its previous effort estimates as follows:
An overall decrease of 5,870 hours in technical development work
A 3-month reduction in project duration from 17 months to 14 months
The reduced effort and shortened timelines resulted in a total implementation cost estimate of
$19.9M including contingency and HST, a 12.7% decrease from the $22.8M estimate from the
Blueprint engagement.
Using the implementation cost as a percentage of revenue metric, the revised estimate works
out to 3.82% falling within the 3-4% range previously mentioned.
Impl. Costs Annual Rev. % of
($ Million) ($ Million) Revenue
London Hydro 10.0337.0 2.97%
Hydro One (in progress) 180.0 5,124.0 3.51%
City of Kitchener (blueprint estimate) 19.9 521.0* 3.82%
EnWin (blueprint estimate) 16.0400.0 4.00%
Enbridge 120.0 2,900.04.14%
*Average of 2011 and 2012 revenue figures
Project Funding
Staff propose that the SAP CR&B implementation project be funded by the utility enterprises
and the tax base using the same funding split currently used for CIS, which considers a couple
of factors: the percentage split between property tax and utilities is based on the number of bills
produced annually for each, and the percentage split within the utilities is based on each
enterprise’s annual revenue.
The funding split and the corresponding portion of the implementation costs allocated to each
funding component are shown below.
Funding Component Funding Split Allocated Amount ($)
Gas 53% 10,553,711
Water 11% 2,190,393
7 - 14
Funding Component Funding Split Allocated Amount ($)
Wastewater 11% 2,190,393
Stormwater 11% 2,190,393
Property Tax 14%2,787,773
Total 100% 19,912,663
As of the date of this report, potential funding sources have been identified for approximately
80% of the revised project cost with the balance to be identified through the 2014 budget
process. It is proposed that the project be funded through existing CIS balances and projected
capital Reserve Fund balances from the utility Enterprises. Consideration will be given to the
following key principles:
Contributions from each enterprise will be limited to the funding split allocation identified.
For example; the Water Enterprise will contribute only their share of the funding split which
is 11%
Contributions from the Reserve funds will adhere to the minimum balance requirements
identified in the Reserve Fund Policy previously approved by Council
The Property Tax share of the funding split of 14% will be funded through existing capital
balances resulting in no impact to the existing tax base as it relates to this project
Based on the proposed funding split, the 2015 funding projections, and the minimum balance
requirement of each Reserve fund, staff developed a viable funding option for the SAP CR&B
implementation project. The funding model, as shown in the table below, ensures that there is
no impact on property tax by transferring funds from the Gas Utility Investment and Gas
Transportation Stabilization reserves to the tax-funded portion of the implementation costs.
The Gas Utility Investment reserve consists of funding available for tax based capital projects.
The funding available in the Gas Transportation Stabilization reserve represents the last portion
of the “transportation benefit” available for transfer to tax base projects prior to the transition to
the non-profit made-in-Kitchener transportation rate approved in mid-2013. A transfer of funds
from both the Gas Utility Transportation and the Gas Utility Investment reserves to the SAP
CR&B implementation project to fund the tax base share of the project costs is consistent with
the purpose of each of these reserves.
(A) (B) (C)* (D) (E)** (F) (G)***
2015MinimumNet from From CIS FundingCostCategory
Funding Category ProjectedTargets ReservesCapitalAvailable Allocation Shortfall
Total Gas less $750K Gas Transp.
16,119,0574,922,54311,196,5141,060,00012,256,514 10,553,711 -
funding allocated to Tax Base
Total Sanitary Sewer Reserve 1,977,123 4,212,631- 220,000220,000 2,190,393 (1,970,393)
- 1,331,700- 220,000220,000 2,190,393 (1,970,393)
Total Storm Water Reserve
5,671,5253,800,9961,870,529220,0002,090,529 2,190,393 (99,864)
Total Water Utility Reserve
Tax Base (Gas Utility Investment +
5,008,000 2,500,0002,508,000280,0002,788,000 2,787,773-
$750K in Gas Transp. funding)
Total 28,775,70516,767,87015,575,0432,000,00017,575,043 19,912,663 (4,040,650)
*(C) = (A) – (B), **(E) = (C) + (D), ***(G) = (E) – (F)
7 - 15
Based on the available funds, the current shortfall in project funding is approximately $4.0
million (20%) and is primarily split between the Sanitary Sewer and Storm Water Utilities. Staff
are considering a combination of the following alternatives as part of the 2014 budget process to
address the project shortfall:
Review of existing capital balance closeouts through the 2014 Budget process
Further review of Reserve funds to consider funding the projects in years subsequent to
2015 where funding may be available
Review of other project priorities to identify potential reductions or deferrals to create budget
capacity
Investigation of adjustments in utility rates
Staff will report back on the outcome of this further review as part of the 2014 budget process.
It should be noted that this information is all based on the approved 2013 budget and 10-year
capital forecast, adjusted for 2013 year-to-date actuals. It will be subject to change as revisions
are made to the 2014 operating and capital budgets for the related utilities.
The financial impact of this proposed funding model is discussed in the Financial Implications
section of this staff report.
7 - 16
ALIGNMENT WITH CITY OF KITCHENER STRATEGIC PLAN:
Strategic Directions Alignment
Financial Management
Invest and manage assets strategically
Provide a robust system for the next 15 to 20
years that will enable the City to reliably bill and
manage over $525 million in annual revenue
Align with the City’s corporate technology
direction to buy technology solutions rather than
build internal systems
Capitalize on the investment the City has
already made in SAP
Maximize value through cost effective
Implement cost effective optimized business
service delivery
processes by automating inefficient manual
processes
Information Technology
Utilize leading edge technology to
Provide the foundation to deliver e-services to
provide reliable and convenient access
our customers and implement mobile computing
to city services, information and
functionality for field staff
business processes through a full range
of access points
Maximize technology to support timely,
Shorten the time to delivery of new and
relevant and accurate information
enhanced system functionality
Implement ad-hoc user query and report
generation capability
Optimize technology solutions that will
Implement flexible message printing capability
enhance the city’s ability to interact and
on property tax and utility bills to provide timely
communicate more effectively with our
and relevant information to our customers
internal and external clients
Develop an enabled and
Increase understanding of current and future
knowledgeable workforce
system capabilities and industry trends by
becoming part of a large forum of users and
implementers that share their knowledge of the
solution and the industry
Customer Service
Ensure service is delivered in an
Implement optimized business processes by
effective and cost efficient manner
automating inefficient manual processes and
leveraging best practices inherent in a
purchased solution
7 - 17
FINANCIAL IMPLICATIONS:
Staff propose that the SAP CR&B implementation project be funded from the existing CIS
capital balances and the projected balances in the appropriate reserve funds identified in this
report. It is important to note that any funding allocations recommended from the reserve funds
will be done in adherence to the Reserve Fund policy previously approved by Council, including
the preservation of required minimum balances.
Currently, the project has a funding shortfall of approximately $4.0M (20%) due to insufficient
funds projected to be available in utility reserve funds by 2015 to cover each respective
enterprise’s share of the project funding. It is being proposed that this shortfall be addressed
through a combination of a detailed review of existing capital balances, adjustments to other
capital projects, potential rate adjustments and budgeting for the shortfall in the reserve funds in
future years when funding may be available.
During the 2014 budget process, staff will be bringing forward the project budget and detailed
projections of the reserve funds for Council approval.
COMMUNITY ENGAGEMENT:
Staff met with Mr. Doug Cooper, Executive in Residence for Communitech Venture Services in
December 2012. Mr. Cooper provides Business Strategy and Venture Funding advice to early
stage technology companies in the Kitchener-Waterloo Region.
The purpose of the meeting was to seek out Mr. Cooper’s input and advice, based on his
knowledge and experience in the technology field, particularly new technologies, on possible
alternative solutions to replace CIS.
Mr. Cooper was not aware of any such alternatives and agreed with the City’s direction to
pursue a Tier 1 commercial software solution like SAP CR&B.
CONCLUSION:
Outsourcing
the City’s utility billing functions is not a cost-effective option. EPCOR’s estimated
one-time setup cost of $2.7M and estimated annual fees of $18.1M would exceed the estimated
SAP CR&B implementation cost of $19.9M within a year.
An outsourcing arrangement will also very likely have a major organizational impact both
operationally and on staff’s morale. Significant costs could potentially be incurred to implement
a new organizational structure as well as new or revised business processes.
Re-architecting CIS
, while potentially costing approximately 40% less than an SAP CR&B
implementation, carries substantial risks (availability of experienced resources, limited in-house
expertise, high probability of rework during the long 4-year project due to new/changed
requirements, staff fatigue, etc.) that can result in significant project delays and cost overruns.
It also retains many of the disadvantages associated with custom-developed applications such
as lack of broad external user and vendor community support, low probability of following
industry best practices, slow delivery of enhanced functionality, and slow adoption of new
technologies.
7 - 18
A re-architected CIS application will likely need to be replaced itself in about 10 years.
Re-architecting CIS goes against both the City’s own Corporate Business Technology Strategy
and industry trends to buy technology solutions and not build them internally, especially ERP
systems (Enterprise Resource Planning).
An SAP CR&B solution
remains the better long-term option for the City, with a demonstrated
ability to support two critical requirements of the City – Property Tax and GDAR.
It provides a robust platform for the next 15 to 20 years that will enable the City to accurately
and reliably bill and manage over $520 million in annual revenue, and offers a solid foundation
for key enhancements such as on-line customer access, mobile computing, and smart meters.
Although the estimated costs to implement SAP CR&B at the City are higher than initially
expected, the cost as a percentage of revenue metric of 3.82% falls within the expected 3-4%
range.
Options for addressing the projected shortfall in funding for the SAP CR&B implementation
project will be reviewed during the 2014 budget process.
ACKNOWLEDGED BY:
Dan Chapman, Deputy CAO and City Treasurer
Finance and Corporate Services Department
7 - 19
Appendix A
Examples of CIS issues and inefficiencies as reported in Staff Report FCS-12-040:
Deposit interest was raised as an issue by the City’s auditors in 2005. It took six years
before changes were implemented in CIS in December 2011 to address the issue. The
project was stopped and re-started several times due to new higher-priority change
requests. More than $308,000 in interest accrued up to December 2010 was credited to
4,826 customer accounts in November 2011. 44 customer accounts received credits of over
$1,000, including five that were over $4,000. (This functionality is delivered out of the box in
SAP CR&B)
The functionality to suspend billing for one service was implemented in February 2012, six
years after the change was requested. Prior to that, the entire customer account had to be
suspended and no bills produced if there was an issue with one service, such as a stopped
water meter. Once the issue was resolved the customer would receive a utility bill for all the
services for the number of months that the account was suspended. The billed amounts
could reach up to $800 for residential properties and up to $5,000 for commercial properties.
Approximately 300 customer accounts were temporarily suspended in 2011 due to stopped
meters or in/out differences.(This functionality is delivered out of the box in SAP CR&B)
In July 2011, the monthly property tax pre-authorized payment transactions for 177 property
tax rolls were not generated by CIS and included in the bank transfer file. Staff only realized
a problem had occurred because a manual payment adjustment had to be made to one of
the missing rolls for that month. Had the adjustment not been required, the problem would
have gone unnoticed until a customer enquired why there had been no withdrawals for July
or at year end when all outstanding balances are billed. The total value of the missed
payment transactions amounted to $578,583.
In May 2011, the CIS daily transactions for the May 18 accounting day were posted twice to
the SAP G/L. These included billing transactions amounting to approximately $798,000 and
payment transactions amounting to approximately $391,000. This duplicate posting was
uncovered two days later only because a staff member was verifying that a manual $19.77
payment transaction she had entered in CIS to adjust a previous entry had properly been
posted to the G/L.
In January 2012, three property tax refunds totalling $1,000 were posted in CIS on two
separate days but not uploaded into SAP Accounts Payable for processing. This was due to
one of a 3-step manual interface process being missed in SAP. Staff only became aware of
the problem after a customer called to say she had not received her cheque. A request has
been made to automate the interface process. There is currently no way to determine
whether additional refunds may have been missed in previous months.
Other inefficiencies and areas of concern in CIS include:
The functionality does not exist for the business to enter new rates for rental water heaters
and there is no automated process to apply the new rates to customer accounts on the date
they take effect. Every water heater rental rate change requires resources from Utilities,
Revenue and IT over a period of 4-6 weeks to implement.
7 - 20
The property tax billing process requires manual intervention by IT and due to low
confidence in the system, comprehensive testing by Revenue staff for every billing run.
Balancing reports are not available through the application and must be run in the backend
by IT. Each billing run requires 2-3 days of effort from Revenue and IT resources over a 1-
week period. Requests for automation of the processes date back to 2005 but never
implemented due to higher-priority changes.
The collections process is not automated for property tax. No alerts are generated when tax
arrears thresholds (value or days) are reached. Staff need to manually monitor property tax
accounts that are in arrears and take appropriate action as each threshold is reached.
7,246 accounts were in arrears as of December 31, 2011 for a total amount of $26.4M.
The year-end numbers for 2011, 2010 and 2009 and the year-over-year change are shown
in the following table:
Table 8 – Property Tax Arrears
2011 2010 2009
Arrears
Change Total $ Change Total $ Change Total $
Current Year -0.85% 10,508,952 2.58%10,599,317 7.96% 10,333,227
Prior Year 17.48% 4,329,252 13.06%3,685,023 10.02% 3,259,254
2 Years Prior 16.37% 1,569,601 15.71%1,348,853 8.85% 1,165,711
3+ Years Prior 15.13% 3,961,812 7.29%3,441,204 5.22% 3,207,447
Penalties & Interest 13.47% 6,029,923 15.37%5,313,922 12.82% 4,606,082
Total 8.25% 26,399,540 8.05%24,388,319 8.85% 22,571,722
An “archive & purge” process does not exist. Data has never been purged from CIS since it
went live in 1998. System performance issues have so far been addressed through
hardware upgrades but this is not a viable long-term solution.
Extensive testing is required when a version upgrade is applied to one of the various
software components supporting CIS to ensure that the upgraded software does not have
compatibility issues with the other software components. Some of these key components
include the PowerBuilder development software, the Crystal Reports reporting tool and the
Windows operating system. For example, comprehensive testing is currently underway to
ensure that the City’s move to Windows 7 from Windows XP does not cause problems in
CIS.
7 - 21