HomeMy WebLinkAboutFCS-12-040 - Customer Information System (CIS) ReplacementREPORT TO:
Finance and Corporate Services Committee
DATE OF MEETING:
March 19, 2012
SUBMITTED BY:
Dan Chapman, Deputy CAO and City Treasurer
Finance and Corporate Services Department
519-741-2347
PREPARED BY:
Alex Ahkoon
Manager, ERP Business Transformation
Revenue Division
519-741-3400 ext. 3283
WARD(S) INVOLVED:
All
DATE OF REPORT:
March 13, 2012
REPORT NO.:
FCS-12-040
SUBJECT:
CUSTOMER INFORMATION SYSTEM (CIS)
REPLACEMENT
RECOMMENDATION:
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.
BACKGROUND:
The Customer Information System (CIS) is a critical application that supports the City’s property
tax and utilities revenue management and billing processes.
Over $525 million of revenue were billed through CIS in 2011, broken down as follows:
Table 1 – Revenue Breakdown
Tax / Utility / Fees Billed Revenue ($ Million)
Property Tax 342.0
Gas94.9
Water32.3
Sewer35.4
Stormwater12.5
Rental Water Heater 7.8
Administration Fees 0.35
î ó ï
Annually, CIS produces over 850,000 utility and 137,000 property tax bills, and processes over
830,000 utility and 440,000 tax payments for approximately 72,000 utility accounts and 68,000
property tax accounts.
In 2009, the City retained Prior & Prior Associates Ltd. (Prior & Prior) to undertake an
assessment of CIS. The assessment included the identification and evaluation of 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.
Based on its findings Prior & Prior recommended that the City pursue a Tier 1 off-the-shelf
solution based on the SAP suite of products.
In September 2010, staff report FIN-10-127 was presented to Council and, in accordance with
the findings of the CIS Assessment, recommended that staff be directed to continue with due
diligence to pursue the acquisition of the SAP tax and utility solutions to replace CIS, and to
report back to Council in 2011 with a detailed business case, including refined costing and a
more detailed project schedule. Council approved the recommendation.
The purpose of this report is to present the findings of additional research conducted by staff
into the SAP Customer Relationship & Billing (CR&B) solution and to provide a recommendation
on the next steps to be taken to continue pursuing the SAP CR&B solution.
REPORT:
Since June 2011, staff have conducted additional research into the SAP Customer Relationship
& Billing (CR&B) solution 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.
Ability of SAP CR&B to meet the City’s Business Requirements
Utilities Revenue Management & Billing
Gartner, an independent information technology research and advisory company, ranks SAP as
the leading Customer Information System provider with a 65% market share in the global
Utilities market. SAP CR&B is used by over 2,000 utilities worldwide producing over 3 billion
utility bills annually.
Several Canadian utilities have successfully implemented or are in the process of implementing
the SAP CR&B solution. The customer base of these companies ranges from small (15,000
water utility customers at the Capital Regional District in British Columbia) to very large (3.5
million hydro customers at Hydro Quebec).
Some of these utility companies are shown in the following table:
Table 2 – SAP CR&B in Canadian Utility Companies
Utility Company Customers Utility Go-Live
Hydro Quebec 3.5 Million Hydro2006
Enbridge Gas 1.9 Million GasSep 2009
BC Hydro 1.9 Million Hydro2004
î ó î
Utility Company Customers Utility Go-Live
Hydro One 1.3 Million HydroOct 2012
Terasen (FortisBC) 1 Million GasIn Progress
Hydro, Gas, Water,
EnMax Energy (Alberta) 697,0002008
Sewer
FortisAlberta 500,000Hydro2004
SaskPower400,000HydroJul2011
NB Power 375,000Hydro2000
Imperial Oil 250,000Home Heating Oil 2005
Gaz Metro 160,000Gas2007
Hydro, Water, Sewer,
London Hydro 150,000Jun 2009
Storm Drainage
EnWin (Windsor) 85,000HydroStart in 2012
Halifax Water 70,000Water2006
FortisOntario50,000Hydro2001
BlueWater Power (Sarnia) 45,000Hydro2004
Cape Breton Regional
30,000Water2005
Municipality
Capital Regional District (BC) 15,000Water2007
Staff met with Enbridge Gas Distribution (Enbridge) in August 2011 and London Hydro in
September 2011 to get feedback on the SAP CR&B software, which they have been running
since 2009, and to hear about their implementation experience. Both Enbridge and London
Hydro indicated that they are very satisfied with SAP CR&B and are confident that they selected
the right software.
Enbridge Gas Distribution
Enbridge is the largest gas distributor in Canada with $2.9B in annual revenue and serving 1.9
million customers in Ontario. It is also one of only four gas distributors operating in Ontario and
subject to the same provincial gas deregulation rules as the City of Kitchener.
Following a procurement process that shortlisted the SAP CR&B solution and the Oracle
Customer Care & Billing (CC&B) solution as a potential replacement for their existing Customer
Information system, Enbridge selected SAP CR&B for its flexibility and powerful billing engine.
The proposed cost estimates for SAP CR&B and Oracle CC&B were very similar.
The company went live with SAP CR&B in September 2009 following a 26-month
implementation project. Enbridge stated that the SAP solution is very flexible and met most of
their business requirements through configuration (defining the business rules and designing
the processes to implement) rather than customization (software code changes). The
implemented SAP CR&B solution was able to satisfy all of Enbridge’s 2,800 documented
requirements. 83 customizations were needed – the biggest to implement an Enbridge-specific
budget billing plan algorithm.
î ó í
Of more relevance to the City is the fact that all of Enbridge’s requirements related to the
Ontario Energy Board’s Gas Distribution Access Rule (GDAR) were successfully implemented
in SAP CR&B. The GDAR requirements are expected to be the most complex to implement
amongst the utilities provided by the City.
Enbridge also established an Open Bill Access (OBA) service that underlines SAP CR&B’s
flexibility. OBA allows other companies offering energy-related products and services to invoice
their customers on Enbridge’s bill, such as:
Sale, service or rental of natural gas appliances
Duct cleaning
Roof replacement
Attic insulation
Air conditioners
Water softeners
Energy efficiency or environmental initiatives
Household improvements that result in improved energy efficiency
Financing contracts for natural gas appliances, home improvements or energy efficiency
initiatives
The Enbridge SAP CR&B implementation project was a major undertaking and had a $120M
budget. Over 250 resources (about 40% internal) worked on the project at its peak.
Enbridge cited the following key success factors for a successful project:
Strong project governance, transparency and accountability
Executive-level involvement of SAP, Sapient (Systems Integrator) and Accenture (legacy
applications)
Support at senior levels to focus on quality – not just budget and schedule
High calibre of key project resources
Comprehensive testing program
Change management planned from the outset
London Hydro
London Hydro provides electricity to about 150,000 customers and generates approximately
$337M in annual revenue. It also bills water, sewer and storm drainage charges on behalf of
the City of London.
London Hydro received 12 vendor responses to a Request for Proposal (RFP) that it issued for
software and implementation services to replace its existing customer information system. Nine
of the responses proposed an SAP CR&B solution, two proposed an Oracle Customer Care &
Billing solution, and one proposed a Consumer Information System from Daffron & Associates.
London Hydro selected the SAP solution based on the strength and flexibility of the software as
well as its ability to support the company’s strategic goals. Oracle had recently acquired its
CC&B Solution from SPL Worldgroup and London Hydro was concerned about purchasing a
solution that might be undergoing significant changes in the near future to be able to provide
better integration with other Oracle products. The company did not further investigate Daffron &
î ó ì
Associates’ Consumer Information System which it considered a less capable solution from a
smaller vendor.
London Hydro went live with SAP CR&B in June 2009 following an 18-month implementation
project. The initial implementation did not go smoothly and the solution was not well received
by the user community. However, London Hydro acknowledges that this was the result of the
systems integrator’s lack of experience in the utility industry and lack of product knowledge
rather than the SAP software itself. Under the guidance of more experienced SAP consultants
from another consulting firm, the company spent about a year after go-live stabilizing the system
and backing out of unnecessary customizations recommended by the original systems
integrator.
The impact on customer service was significant initially as many business transactions that
could be completed using a single screen in the legacy system required navigating through
multiple screens in SAP CR&B due to improper design and configuration. The average
customer call duration increased from 4½ minutes to 7½ minutes. This situation was remedied
successfully with the help of the second consulting firm by re-configuring the SAP CR&B
screens.
All of the original business requirements have been met and London Hydro firmly believes it
made the right decision in selecting the SAP CR&B solution.
The implementation project had a $4M budget. The core project team consisted of 6 full-time
internal resources and between 18 and 24 consultants, half of which were based off-shore in
India. A number of internal subject matter experts were also heavily involved in the design
sessions, test cases development and user acceptance testing.
London Hydro’s advice was to make sure that the systems integrator’s functional leads on the
project are very experienced and have worked on multiple SAP CR&B implementations as their
expertise will be key to properly designing and configuring the system. Sufficient time and effort
should be allocated to the design phase as changes can become quite costly once the build
phase is well underway. The company also indicated that there were insufficient internal
resources dedicated to the project.
As of June 2011, London Hydro had spent a total of approximately $10M implementing,
stabilizing, and enhancing its SAP CR&B solution. Functionality added after the initial
implementation includes business analytics, interfaces to Ontario’s Meter Data Management
and Repository (MDM/R), and Time-of-Use billing.
Property Tax Revenue Management & Billing
SAP provides a Tax and Revenue Management (TRM) solution to support the property tax
revenue management and billing requirements of local governments. However, the SAP TRM
solution has not been implemented in any Canadian municipality and as delivered, does not
offer any integration capability with SAP CR&B.
According to SAP, integrating the two solutions would be a major undertaking and would be
very costly. SAP’s recommendation is to take advantage of SAP CR&B’s flexibility to design
and configure the property tax revenue management and billing functionality into the SAP CR&B
solution itself with some degree of customization.
î ó ë
Two municipalities have successfully configured / customized SAP CR&B to support their
property tax administration and billing processes: Cape Breton Regional Municipality (CBRM) in
Nova Scotia and the City of Cape Town in South Africa.
The Cape Breton Regional Municipality has been running SAP CR&B since 2005 for both
property tax and water utility billing. Approximately 65,000 tax rolls and 30,000 water utility
accounts are billed through the application.
In October 2011 staff attended a web conference between CBRM and the City of Toronto. The
goal of the session was to understand the differences and similarities in the property taxation
process of the two provinces, and to review the property tax functionality as implemented in
SAP CR&B by CBRM. The session included a live demonstration of some of the implemented
property tax processes.
Overall no major differences were found in the property taxation process. Business
requirements for annual and supplementary assessments, appeals, arbitrations, and rebates
are similar to Ontario’s and are fully supported by the implemented SAP CR&B solution at
CBRM. The municipality indicated that SAP CR&B did not require complex customizations to
support the property tax functionality.
Based on the information gathered, staff believe that SAP CR&B can be configured and
customized with no major issues to support the City of Kitchener’s property tax revenue
management and billing processes.
The City of Toronto is also pursuing an integrated property tax and utility revenue management
and billing solution based on SAP CR&B. SAP recently completed a fit-gap analysis which
reviewed SAP CR&B’s capability against the City of Toronto’s property tax requirements, as
documented in a Request for Information (RFI) published in April 2009. The analysis reported a
high degree of fit and identified only 6 gaps: 2 categorized as high level, 3 as medium, and 1 as
low. The City of Toronto is currently reviewing the report and providing more complex business
scenarios to SAP for further fit-gap assessment. SAP was the only vendor who responded to
the RFI in 2009 proposing the SAP TRM solution.
SAP CR&B Implementation Cost Estimates
Prior & Prior Cost Estimates
While the bulk of its effort was primarily focused on assessing CIS during the 2010 CIS
Assessment engagement, Prior & Prior also worked with SAP Canada to provide the City with a
cost estimate for an SAP CR&B / SAP TRM implementation.
To manage costs, the implementation of the SAP Customer Relationship Management (CRM)
module of SAP CR&B was not included in the scope of the project.
The total implementation costs were estimated to be $4.561 million over a 36-month project with
the following cost breakdown:
î ó ê
Table 3 – Prior & Prior Cost Estimates
Cost Component Estimated Costs
Software Licenses
Utilities (SAP CR&B) $250,000
Property Tax (SAP TRM) $125,000
Total Software Licenses $375,000
Implementation - Vendor Costs
Utilities$1,250,000
Property Tax $750,000
Additional e.g. GDAR configuration $611,000
Base System & Integration $300,000
Total Vendor Costs $2,911,000
Implementation - City Staff Costs
IT Project Manager - Utilities $300,000
Business Resources – Utilities $405,000
IT Project Manager – Tax $300,000
Business Resources - Property Tax $270,000
Total City Staff Costs $1,275,000
Total Costs $4,561,000
SJH Consulting Cost Estimates
SJH Consulting provides management consulting and project management services as well as
SAP functional and technical expertise to Energy & Utility clients in Ontario. In December 2011
the City retained the services of the company for a 3 person-days engagement to conduct a
high-level scoping of an SAP CR&B solution that would meet the City’s business requirements
and to produce a high-level cost estimate to implement the solution.
SJH Consulting met with key business and IT resources to understand the City’s property tax
and utilities business requirements and processes, the reports and customer letters currently
produced by CIS, and the interfaces to internal and external systems.
Based on the information gathered, SJH Consulting estimated the effort required for each phase
of a standard SAP implementation methodology. Using average hourly rates of Tier 1 systems
integrators and assuming a mix of on-shore and off-shore resources, SJH Consulting estimated
the implementation costs to be approximately $16.4M, including consulting costs of $11.2M,
over a 21-month project.
Primary Factors in Consulting Costs Estimates Variance
The primary factors that explain the variance between the consulting costs estimates provided
by Prior & Prior and SJH Consulting for an SAP CR&B implementation are as follows:
î ó é
Table 4 – Costs Estimates Variance Factors
Variance Factor Prior & Prior SJH Consulting
Systems Integrator Mid-tier SI focused on small to Tier 1 SI focused on medium to
(SI)mid-market organizations. large companies. Typically
Smaller implementation teams larger project teams.
and somewhat lower rates.
SAP Modules Implementation of SAP Customer SAP CRM included. Required
Relationship Management (CRM) for more user-friendly and
module not included. efficient screens for customer
service representatives (CSR’s).
Project Scope and Based on type of utility services Based on business processes
Effortprovided by City, high-level around each utility, and number
metrics, and number of systems and type of reports, interfaces,
interfaced to. conversion, enhancements,
forms and workflows required.
Actual and Estimated Costs of SAP CR&B Implementations at Other Utility Companies
Information gathered by staff in 2011 from utility companies that have implemented or are in the
process of implementing SAP CR&B show that implementation costs can vary considerably
based on a number of factors such as size of customer base, number and type of utility services
provided, number of users, and category of systems integrator.
Tier 1 systems integrators are the large consulting firms, such as Deloitte, Accenture, IBM and
Sapient, who target medium to large companies with annual revenues of $250M and above.
Tier 2 or mid-tier systems integrators focus on the small to mid-size market and include
consulting companies like Ray Tech, Sparta, IDS Sheer, Wipro and Sita Corp. Their rates are
usually lower.
The range of the implementation costs gathered by staff is shown below:
Table 5 – Implementation Costs Comparison
Systems SI Costs Total Budget
Utility Company Customers Utility
Integrator (SI) ($ Million) ($ Million)
Implemented
Enbridge1.9 Million GasSapient$48.0$120.0
London Hydro 150,000HydroWipro$3.0-$6.5$4.0-$10.0
Water
Sewer
In Progress
Hydro One 1.3 Million HydroHCL Axon $49.0$180.0
Reviewing Vendor Quotes
EnWin*85,000HydroDeloitte $12.0 $16.0
(after blueprint)
EnWin $8.0-$9.0
(RFI Responses)
î ó è
*EnWin acquired the licenses for a number of SAP modules in 2008 including Financials (G/L, A/R, A/P),
Purchasing, Inventory Management, Supply Chain Management, Project Systems, Plant Maintenance,
Asset Management, HR / Payroll, and CR&B. The company engaged Deloitte as systems integrator and
went live with all modules except CR&B in May 2010. Following the completion of the SAP CR&B
solution design in April 2011, which included new functionality to support the introduction of Time-of-Use
legislation and the Green Energy Act, Deloitte submitted a $12M quote for consulting services to
implement the solution. EnWin subsequently issued a Request for Information (RFI) in November 2011,
at the Board of Directors’ direction, to obtain comparison numbers from other systems integrators.
Implementation Costs as a Percentage of Annual Revenue
While the actual and estimated implementation costs vary considerably for each individual SAP
CR&B implementation, they fall within a smaller range when expressed as a percentage of
revenue (with the exception of the Prior & Prior estimate), as shown in the following table:
Table 6 – Implementation Costs as a Percentage of Revenue
Impl. Costs Annual Rev.% of
($ Million) ($ Million) Revenue
City of Kitchener (Prior & Prior) 4.5525.00.86%
City of Kitchener (SJH Consulting) 16.4525.03.12%
Enbridge120.02,900.04.14%
London Hydro 10.0337.02.97%
EnWin (after blueprint) 16.0400.04.00%
Hydro One 180.05,124.03.51%
Rationale for Not Continuing with CIS
Although recent data gathered by staff suggests that the cost of implementing SAP CR&B may
be higher than first estimated, it is still considered to be the preferred option as the risks
associated with CIS continue to increase as the application grows in complexity with each new
functionality added.
CIS has been in production at the City for 14 years and was never intended to be maintained as
an in-house developed system. It was licensed as a software package from Sierra Systems and
the expectation was that enhancements and upgrades would be provided regularly by the
vendor. However, Sierra Systems discontinued the product shortly after it was implemented at
the City, at which time the City decided to purchase and continue maintaining it internally.
It is generally agreed that the average life span of in-house developed systems is 10 to 15 years
after which its efficiency and effectiveness start to decline while the risk that the system
becomes unstable increases and maintenance costs become unsustainable.
The 2010 Prior & Prior CIS Assessment Report identified a few of those underlying issues and
concerns which have a direct impact on the City’s ability to effectively and efficiently maintain
the CIS application, including the following:
Developed using a pre-2000 technical architecture of the PowerBuilder development
platform, the original design did not use object-oriented design principles and contains
complex coding structures that are becoming increasingly difficult to maintain
î ó ç
The system architecture was not designed to be configurable and does not easily support
ongoing enhancements and maintenance. The enhancements and fixes create more
complexity which in turn makes the system more difficult to enhance and maintain, forming a
vicious circle
No documentation of the overall application architecture exists
No in-depth end-to-end understanding of the entire system from either a functional or
technical perspective exists
Due in large part to the above, time to delivery of system enhancements and fixes is
inconsistent with the industry norm. The time and effort required from both IT and business
resources to implement changes in CIS is directly impacting our ability to meet new business
requirements in a timely manner, particularly the high-priority deadline-driven requirements
resulting from new legislation and council resolutions.
In the meantime, an already extensive backlog of lower-priority but necessary enhancements to
increase the efficiency of our processes and improve customer service keeps growing. As of
February 2012, 175 requests for changes in CIS remained opened.
The escalating complexity of CIS is also causing a high number of defects as changes are
made to the system and is increasing the risks that changes made in one area of the application
unexpectedly impact other areas. The total number of defects logged in the past three years is
shown below.
Table 7 – Number of CIS Defects Logged
201120102009
Defect Category OpenedClosedOpenedClosedOpenedClosed
1 - Critical 181431213631
2 - Important 594580649379
3 - Medium 413034155632
4 - Minor 312046237027
5 - Usability Issue 991100
Total Defects 158118192124255169
The following recent examples illustrate the various challenges noted above:
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.
From a sustainability perspective, the implementation of new functionality in CIS to support
legislated requirements such as gas deregulation in 2007 and council resolutions such as
stormwater utility in 2011 provide an indication of the significant time, effort and costs involved
in continuing to enhance CIS:
The Gas Distribution Access Rule (GDAR) Phase 1 project to support the gas deregulation
legislation required a total effort of approximately 2,850 person-days, or 12 person-years,
between 2004 and 2007 and cost approximately $1.5 million.
The final standards published by the Ontario Energy Board in April 2006, less than nine
months before the January 1, 2007 implementation deadline, required significant changes to
the original solution design. Due to this short timeframe the decision was made to continue
with the original design and knowingly implement a sub-standard solution that would require
multiple manual workarounds and would result in significant ongoing maintenance issues.
The intent was to subsequently launch a GDAR Phase 2 project to address all the
shortcomings of the original design.
The estimates for the GDAR Phase 2 project were 2,800 person-days of effort at a cost of
approximately $1.3 million. The project was not started in accordance with the 2010 CIS
Assessment recommendation to cease further development of non-legislated changes in
CIS in anticipation of an SAP solution implementation.
The Stormwater project to support the introduction of the stormwater utility for the 2011
budget year required 708 person-days or 3 person-years of effort over 9 months at a cost of
approximately $260,000. To make the target implementation date the functionality for the
î ó ïï
business to change the stormwater rate and the automation of a nightly interface to update a
property’s stormwater classification code from the City’s Geographical Information System
(GIS) had to be deferred.
In March 2011 Council directed staff to implement a 10% decrease in stormwater rate to be
effective July 2011. This required a further 114 person-days or 5.5 person-months of effort
over a 4-month period and cost approximately $38,000 to implement in CIS.
A third stormwater project was recently launched to implement the stormwater credit policy
approved by Council in March 2012. The policy does not come into effect until October
2012 as it is estimated that it will take 505 person-days or 2 person-years of effort over
approximately 7 months for the stormwater credit functionality to be developed in CIS.
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.
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
201120102009
Arrears
Change Total $ Change Total $ Change Total $
Current Year -0.85%10,508,9522.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,8127.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
Total8.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.
A software package such as SAP CR&B would not require such extensive testing by City
resources as the vendor would test its product and certify that it works with the upgraded
software component.
Payment Obligations to the Region of Waterloo and to the School Boards
The City bills and collects property tax on behalf of the Region of Waterloo (the Region) and
four school boards and must remit payments to these entities according to a set schedule: nine
instalments on the first of every month from April to December for the Region, and four quarterly
payments for the school boards.
Payments must be made regardless of the City’s ability to bill and collect the taxes on time.
Penalties in the form of interest are incurred for late payments.
The Region’s 2011 payment schedule under the Region of Waterloo By-Law 11-019 and the
school boards’ 2011 payment schedule under the provincial Education Act were as follows:
Table 9 – Region of Waterloo Payment Schedule
Apr 1, 2011 May 1, 2011 Jun 1, 2011 Jul 1, 2011 Aug 1, 2011
$16,238,352 $16,824,047 $16,824,049$16,824,049$16,824,049
Sep 1, 2011 Oct 1, 2011 Nov 1, 2011 Dec 1, 2011 Total
$16,824,049 $16,824,049 $16,824,049$16,824,049$150,830,742
Table 10 – School Boards Payment Schedule
Mar 31, 2011 Jun 30, 2011 Sep 30, 2011 Dec 15, 2011 Total
$85,307,927
$21,149,451 $21,503,802 $21,327,337$21,327,337
The City issues interim, final, and supplementary property tax bills to property owners according
to the following schedule:
The interim bill is sent out by the beginning of February with payments due the first business
day in March and May.
The final bill for residential properties is sent out in June with payments due the first
business day in July and September.
The final bill for multi-residential, commercial and industrial properties is sent out in August
with payments due the first business day in September and October.
Three or four supplementary bills are sent out between August and December for changes
in assessment values from the Municipal Property Assessment Corporation (MPAC).
î ó ïí
The payment due dates for 2011 were as follows:
Table 11 – City of Kitchener Property Tax Due Dates
Property Tax Bill Mar 1, 2011 May 2, 2011 Jul 4, 2011 Sep 1, 2011 Oct 3, 2011
Interim$82,759,320 $82,759,320
Final - Residential $48,193,079$48,193,079
Final - Commercial /
Industrial / Multi-Res $34,267,995 $34,267,995
Supplementary 1 $928,144 $928,144
Supplementary 2 $673,423
$82,759,320 $82,759,320$48,193,079$83,389,218 $35,869,562
Property Tax Bill Nov 1, 2011 Nov 28, 2011 Dec 28, 2011 Dec 30, 2011 Total
Supplementary 2 $673,423
Supplementary 3 $1,853,535$1,853,535
Supplementary 4 $731,673
$673,423 $1,853,535$1,853,535$731,673 $338,082,665
A major delay in issuing the property tax bills or processing customer payments caused by a
serious issue in CIS would impact the City’s ability to meet its payment obligations to the Region
and the school boards.
As an example, if the City had not been able to bill the $82.76 million in interim property taxes
due March 1, 2011 on time, it might not have been able to pay the $21.15 million due March 31,
2011 to the school boards and the $16.24 million due April 1, 2011 to the Region. The City’s
$8.0 million overdraft protection agreement is inadequate for such large amounts.
CIS Controls Assessment by KPMG
In March 2012, KPMG conducted a high level controls assessment of CIS in connection with
their annual financial statement audit. The assessment provided the City with a high-level
independent perspective of the appropriateness and efficiency of application controls and
general IT controls in CIS versus an SAP CR&B solution and focused on the following risks:
Reporting risks: risks that the City relies on inaccurate data, systems or reports in making
decisions or providing information externally
Compliance risks: risks that the City is not complying with applicable laws and regulations
Operations risks: risks that the company uses its resources ineffectively and inefficiently
KPMG reported that CIS and its integration to the City’s SAP financial system require significant
manual detective controls which increase the risks of human error and irregularities.
In addition, the operating effectiveness of the current controls relies primarily on City resources’
familiarity with the current business processes and manual controls rather than on documented
controls objectives and activities.
î ó ïì
An SAP CR&B solution would allow the City to leverage more automation of preventive controls
through the SAP delivered and configurable controls. The delivered integration to the SAP
Financial system would enable the automation of the G/L reconciliation process.
Rationale for Continuing to Pursue an SAP CR&B Solution
SAP CR&B is still considered by staff as the preferred option, offering the City the following key
benefits:
Provides a robust system for the next 15 to 20 years that will enable the City to accurately
and reliably bill and manage over $525 million in annual revenue.
Provides a flexible and configurable solution that makes it easier to accommodate new
business requirements and allows new functionality to be implemented faster.
Provides a solid foundation to deliver e-services to our customers, implement mobile
computing functionality for field staff, and plan for the introduction of smart meters.
Provides continued functional and technical enhancements through regular system
upgrades and maintenance releases.
Provides the City the opportunity to become part of a large community of users and
implementers who are knowledgeable about the solution. The Americas SAP User Group
(ASUG) and several on-line forums dedicated to SAP solutions provide channels to share
ideas and ask questions. Customers have the ability to influence the direction of SAP
solutions through ASUG.
Provides alignment with both the City’s corporate technology direction and industry trends to
buy technology solutions rather than build internal systems.
Allows the City to leverage industry best practices inherent in a purchased solution and
optimize its business processes.
Capitalizes on the investment the City has already made in SAP Financials and provides the
opportunity to build a strong team of skilled SAP resources in IT.
In addition, SAP CR&B is the only package solution that has been implemented by a utility
company to support the deregulated Ontario gas market requirements and the only integrated
property tax and utilities package solution implemented by a municipality.
î ó ïë
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
î ó ïê
FINANCIAL IMPLICATIONS:
The costs of implementing SAP CR&B vary considerably based on such factors as size of
customer base, number and type of utility services provided, number of users, and category of
systems integrator.
Prior & Prior provided the City with an estimate of $4.56M while SJH Consulting’s estimate was
approximately three and a half times higher at $16.4M (including 15% contingency and
consulting expenses equal to 16% of consulting costs).
A business requirements discovery and solution scoping engagement needs to be undertaken
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.
The duration of this engagement is expected to be eight to twelve weeks and costs between
$171,000 and $257,000 inclusive of expenses and exclusive of HST.
This engagement will be funded through provisions for CIS replacement studies and non-
renewal of two contract resources in the current CIS capital budget.
COMMUNITY ENGAGEMENT:
Not Applicable
CONCLUSION:
CIS is a critical application that supports the City’s property tax and utilities revenue
management and billing processes. Over $525 million of revenue were billed through the
system in 2011.
An assessment conducted in 2010 by Prior & Prior identified a number of key underlying issues
and concerns with the 14-year old CIS application which makes it unsuitable as a long-term
solution. Prior & Prior recommended that the City pursue a Tier 1 off-the-shelf solution based
on the SAP suite of products.
Further research by staff in 2011 confirmed the ability of the SAP CR&B solution to meet the
business requirements of the City.
To obtain implementation cost estimates which can be used with a high degree of confidence to
establish a detailed budget for an SAP CR&B implementation, staff propose that a business
requirements discovery and solution scoping exercise be undertaken. This engagement is
expected to be eight to twelve weeks in duration.
ACKNOWLEDGED BY:
Dan Chapman, Deputy CAO and City Treasurer
Finance and Corporate Services Department
î ó ïé
Appendix A – Glossary
ASUGAmericas SAP User Group – largest independent SAP users’ group
representing approximately 3,200 companies across 17 industries
CBRMCape Breton Regional Municipality, Nova Scotia
CISCustomer Information System – the City of Kitchener’s current
Property Tax and Utilities Account Administration and Billing
application
GDARGas Distribution Access Rule – set of rules from the Ontario Energy
Board governing the conduct of gas distributors toward gas
vendors, including gas marketers
MPACMunicipal Property Assessment Corporation – independent body
established by the Ontario Government to administer a uniform,
province-wide property assessment system based on current value
assessment
OBAOpen Bill Access – service provided by Enbridge that allows
companies offering energy-related products and services to invoice
their customers on Enbridge’s gas bill
RFIRequest for Information – issued to receive feedback from vendors
on their goods or services
SAP CR&B SAP Customer Relationship & Billing software
SAP CRM SAP Customer Relationship Management software – included in
SAP CR&B
SAP TRM SAP Tax and Revenue Management software
SISystems Integrator – consulting company specializing in package
software implementation
î ó ïè
î ó ïç
î ó îð
î ó îï
î ó îî
î ó îí
î ó îì
î ó îë
î ó îê
î ó îé
î ó îè
î ó îç
î ó íð
î ó íï
î ó íî
î ó íí
î ó íì
î ó íë
î ó íê
î ó íé
î ó íè
î ó íç
î ó ìð
î ó ìï
î ó ìî
î ó ìí
î ó ìì
î ó ìë
î ó ìê
î ó ìé
î ó ìè
î ó ìç
î ó ëð
î ó ëï
î ó ëî
î ó ëí