HomeMy WebLinkAboutCAO-2024-166 - 2nd Quarter Audit Status ReportREPORT TO: Audit Committee
DATE OF MEETING: June 24, 2024
vww kitcheneroce
SUBMITTED BY:
Corina Tasker, Internal Auditor, 519-741-2200 ext. 7361
PREPARED BY:
Corina Tasker, Internal Auditor, 519-741-2200 ext. 7361
WARD(S) INVOLVED:
ALL
DATE OF REPORT:
June 13, 2024
REPORT NO.:
CAO -2024-166
SUBJECT: 2nd Quarter Audit Status Report
RECOMMENDATION:
For information.
REPORT HIGHLIGHTS:
• The purpose of this report is to provide information regarding recent audits.
• There are four audits included in this report: 1) External Billable Work Orders; 2) Parking
and Mileage Expenses status report; 3) Underground Locates status report; and 4)
Technology Field Inventory status report.
• Results of the audits were positive, with no fraud detected.
• There are no financial implications.
• Community engagement included this report posted to the city's website with the
agenda in advance of the council / committee meeting.
• This report supports the delivery of core services.
EXECUTIVE SUMMARY:
The following report provides a summary of the Internal Audit assurance and consulting
services completed or underway during the period of January to June 2024.
Work in -progress:
Assurance work is in progress on the following topics:
• Hiring Transparency and Nepotism - compliance
Consulting work is in progress on the following reviews:
• Contractor Management — gap analysis review
• Bylaw — reorganization assistance
• Security — capacity and service level review
Work completed:
The table below shows the audits contained in this report.
Page 3 of 254
The Lean process review of external billable work orders found several areas for
improvement. Actions taken or in progress will help to ensure accurate, timely billing to
customers and less rework and frustration for staff.
The status update of employee parking and mileage expenses found that all
recommendations have been implemented or are no longer required. It will remain on the
rotating list of compliance audits.
The status update of the underground locates review found that the City has not incurred
any fines yet, since the Province postponed activation of the penalties until May 2024. The
Gas and Water and Sewer locates are 100% in compliance with legislated time frames as
of mid-June. It is anticipated that all locates will remain in compliance moving forward.
The status update of the technology field inventory found that work is underway to
implement a new asset management module which will ensure more accurate tracking of
assets in future.
BACKGROUND:
The overarching goal of internal audit is to protect the City's assets and interests. This
includes, but is not limited to, protecting the long-term health of the organization, its
financial and physical assets, its reputation, its ability to perform critical services and the
safety and well-being of employees and citizens.
Internal Audit provides assurance and consulting services in accordance with the
International Standards for the Professional Practice of Internal Auditing (Standards), IIA
2012. These services include independent, objective activities/reviews designed to add
value and improve an organization's operations. It helps an organization accomplish its
objectives by bringing a systematic, disciplined approach to evaluate and improve the
effectiveness of risk management, control, and governance processes.
Assurance services provide an objective assessment of evidence to provide an
independent opinion or conclusions regarding an entity, operation, function, process,
system, or other subject matter.
Consulting services are advisory in nature and are generally performed at the
specific request of an engagement client. When performing consulting services, the
internal auditor should maintain objectivity and not assume management
responsibility.
Audit topics are selected independently by the Internal Auditor and approved by Audit
Committee on an annual basis. Audit results and updates are brought back to Audit
Committee in reports such as this on a quarterly basis as completed.
Page 4 of 254
REPORT:
1. External Billable Work Orders — Lean Process Review
Completed: April 2, 2024
Background:
This consulting engagement was requested by Support Services to address issues in the
external billable workorder intake process which prevents Accounts Receivable (Revenue
division) from billing external customers for work performed by City staff. Previous work
including process mapping, problem identification, and recommendations had already
been completed by some of the stakeholders. One of the recommendations was to
complete a more fulsome review of the intake process. This review fulfilled that
recommendation.
Review Objective:
The overall goal of this review was to examine the external billable work order intake
process to find the most efficient, effective, and accurate intake method, in order to
facilitate billing in latter phases of the overall process.
Scope:
The following areas were within scope for this review:
• Lean process review of external billable work orders intake process, from service
request creation to work order completion
• Define divisional accountabilities
The following areas were out of scope for this audit:
• System integration / matching process
• Organizational structure review of Support Services
• Internal work orders
Methodology:
The following activities were completed as part of this review:
• Internal stakeholder interviews
• Document review (report on previous work, examples, process maps)
• Data collection
• Current state process mapping and problem identification
• Root cause analysis
• Solution generation and future state process mapping
• Implementation planning and Kaizen event
• Project close-out and lessons learned
Page 5 of 254
Macro -level Process:
When City crews perform any kind of work, from repairing roads to trimming grass, a work
order is created in the Cityworks system which allows the labour, equipment, and material
costs to be allocated to the task. Most of the work performed is considered internal
billable, meaning that it is funded either through the tax -base or through utility rates.
There are some types of work, however, for which we bill the end customer directly.
These include things like sewer blockages on private property, gas or watermain hits
caused by developers, or road closures required for private events or development
activities, etc. The charges for these external billables are sometimes a flat fee and other
times are based on labour and material costs. However, regardless of the method, the
fees are all laid out in the fees and charges schedule which is approved by Council each
year.
While each external billable service type has a slightly different process, the overall macro -
level process is the same for all types:
�a
mmm
mmmm
Intake -contact vuer Service (Request work order 777
atus SAIF to invoice
The process starts with the initial contact with the customer. At this initial stage
information about the work required is gathered and input into a service request in
Cityworks including what address the work is to occur at and what needs to be done.
A workorder is then created, also in Cityworks, and linked to the service request which
details the work actually performed including labour and equipment hours, and any
materials used.
Once the work is complete and all costs finalized, the work order status can be changed to
"Complete" which then triggers a transfer over to SAP so that invoicing can be completed.
For this to happen, the service request and work order must have an external billable flag
marked in three different places, a correct and complete owner name, a correct and
complete billing address (which may be different from the service address), a customer
number that matches a customer number in SAP and the amount billable must be greater
than $0. If all these things happen, then the invoice will flow smoothly.
Problems and Solutions:
The following six problems were identified by the project team consisting of internal
stakeholders. Root cause analysis was performed to determine why the problems were
happening. The team then brainstormed and prioritized the solutions listed below to
address the root causes.
Page 6 of 254
Problem or Issue
Solution(s)
There was no trigger to indicate to Support
Business rules established about when
Services when a work order is ready to bill.
certain types of billables can be billed,
This resulted in time spent following up
including some that can be billed
with business units multiple times until all
immediately and do not need to wait since
work was complete and the work order
they are a flat fee and not based on labour.
could be closed.
Programming changes to Cityworks to de-
couple labour charges from billing, allowing
interim and flat fee billings to occur.
There is often missing or incorrect
Roles and responsibilities established to
information on the service request and
identify who is responsible for gathering
work order, causing rework for Support
each piece of information.
Services when they attempt to bill.
Programming changes to Cityworks to
create one single screen with billing
information and mandatory fields.
Training on the new screen and how to
efficiently search for the correct customer
number.
Each service address may have multiple
Training on how to more efficiently search
owners, tenants, or businesses so it is
for the correct customer number.
unclear which customer number to choose
when creating the billing. This causes
delay and rework.
Some work orders are not reviewed in a
Certain types of billables which typically
timely manner and not initially set to
are billable will be set to "billable by
billable. This causes them to automatically
default", meaning the user does not need
close without billing. Which means rework
to manually check off the billable flag in
to re -open the work order so that it can be
Cityworks, which prevents them from
billed.
automatically closing.
Gas service requests that come through
A control report will show any mismatch
an online portal do not always
between records leaving the portal and
automatically transfer to Cityworks. This
entering Cityworks so that it is easy to tell if
currently requires a manual process to
any records are missing.
verify each transaction.
If there are billing issues or questions such
Roles and responsibilities cheat sheet
as incorrect addresses or names, Revenue
shared with Revenue staff for general
staff are uncertain who to contact.
positions / areas responsible for each type
of billable.
New contact field added to the new billing
screen in Cityworks which will contain the
specific name of the key contact for each
work order.
Page 7 of 254
A Kaizen (i.e., improvement) event was held with the stakeholders to provide input on a
mock-up of the new Cityworks billing screen. Work is underway to make the programming
changes to Cityworks. Training and process documentation will follow.
Future State Goals:
• More timely billing of customers and collection of revenue (i.e., ensure customers
do not receive an invoice several months or years after the work is complete).
• Ensure all customers are billed, eliminating the potential for lost revenue for the
City.
• Less rework for Support Services and Revenue staff.
• More accurate billing.
Accountability:
A project close-out meeting was held with stakeholders and the remaining action items
were assigned to the relevant parties. Status updates will be provided to the Internal
Auditor at the 1, 3, 6, and 12 month marks to ensure the project stays on track. A formal
status update will be provided to Audit Committee at the 12 -month mark.
2. Parking and Mileage Expenses — Status Update
Completed: March 28, 2024
Overview:
Prior to January 1, 2024, the City reimbursed staff for mileage and parking expenses
incurred for business purposes, subject to several rules and conditions. A compliance
audit of parking and mileage claims was part of the rotating schedule of compliance audits.
The goal of the original audit was to test compliance with the parking and mileage
reimbursement policy. The results of this audit showed a few minor errors in mileage and
parking claims that can be rectified with additional education of staff and management.
However, they do not show pervasive issues that are cause for concern.
Audit Objective:
All services which have undergone a service review in the past are subject to a status
update, not sooner than one year following the original audit. The purpose of status
updates is to hold staff accountable for addressing the audit findings and to identify any
areas that have not seen significant progress. In addition, if applicable, further testing or
review may also be done to test whether improvements have had the intended effect.
Status Definitions:
• Complete = the recommendation has been fully implemented.
• In progress = implementation has begun.
• Not started = no work has begun yet but will in the future.
• Not required = the recommendation either does not require any action, or it is no
longer relevant, and no work will be done to implement it.
Page 8 of 254
Number of original recommendations:
3
Number of recommendations complete:
1
Number of recommendations in progress:
0
Number of recommendations not started:
0
Number of recommendations not required:
2
As of January 1, 2024, the City has implemented paid parking for all staff. Two of the
recommendations related to parking reimbursement are no longer required. The third
recommendation was related to periodically reminding staff and approvers of the mileage
reimbursement rules. This has been accomplished through email reminders and is being
incorporated into the Expense 101 training.
Conclusion / Next Steps:
Mileage claims will remain on the list of rotating assurance audits.
3. Underground Locates — Status Update
Completed: March 15, 2024
Overview:
In 2022, the Province of Ontario introduced new legislation, Bill 93, Getting Ontario
Connected Act, 2022. This new legislation increases the City's risk exposure as it relates
to accuracy and timeliness of providing utility locates. Utility owners such as the City are
now liable for both Administrative Penalties (administered by Ontario One Call) and
financial claims made by excavators because of late or incorrect locates.
The goal of the original review was to examine locator services provided by Engineering
Construction and Gas & Water divisions to find the most efficient and effective method of
providing these services while being in compliance with Bill 93 and mitigating the risk of
significant financial penalties or third -party excavator claims.
Audit Objective:
All services which have undergone a service review in the past are subject to a status
update, not sooner than one year following the original audit. The purpose of status
updates is to hold staff accountable for addressing the audit findings and to identify any
areas that have not seen significant progress. In addition, if applicable, further testing or
review may also be done to test whether improvements have had the intended effect.
Status Definitions:
• Complete = the recommendation has been fully implemented.
• In progress = implementation has begun.
• Not started = no work has begun yet but will in the future.
• Not required = the recommendation either does not require any action, or it is no
longer relevant, and no work will be done to implement it.
Page 9 of 254
Number of original recommendations:
24
Number of recommendations complete:
10 (+4 complete for Gas & Water only)
Number of recommendations in progress:
5 (+4 in progress for Sewers only)
Number of recommendations not started:
4
Number of recommendations not required:
1
The outcomes of the review saw the approval of seven new FTE's, one seasonal staff
member, and additional funding for third party locate services to support peak periods.
Five of those positions have been filled but it is proving difficult to find qualified candidates
to fill the remaining Gas & Water positions. The seasonal position was not filled, as
capacity was identified in an existing role.
The City has not incurred any fines yet, since the Province postponed activation of
administrative penalties until May 2024. In addition, the added capacity has resulted in
99% and100% compliance in May and June, respectively.
Process improvements including utilizing locate software to manage tickets more
effectively and training provided to staff will be implemented for this construction season.
It is anticipated that all locates will remain in compliance in 2024 and beyond.
Conclusion / Next Steps:
Management will be monitoring compliance rates closely throughout the 2024 construction
season and will act to shift resources and employ the third party locate service provider
where necessary to ensure compliance. Internal Audit will review compliance rates at the
end of 2024 to determine if further action is required.
4. Technology Field Inventory — Status Update
Completed: May 2, 2024
Overview:
In February 2023 a full physical inventory of technology assets deployed in the field was
done to verify the existence of assets for budgeting purposes and to assess whether field
assets are at risk of theft or loss. The results of this audit showed many discrepancies in
the asset database that have accumulated over many years. There was noway to tell if
missing assets were stolen or simply returned for replacement and not updated in the
database. Therefore, it was impossible to comment on the security of TIS
assets. However, no divisions seemed worried that staff had taken assets.
A renewed focus by both division management and TIS on ensuring the database is up to
date, and rotating cycle counts should ensure a more accurate picture of the assets on
hand. Subsequent audits will provide a better picture of the security of the assets as there
should be significantly fewer missing assets.
Audit Objective:
All services which have undergone a service review in the past are subject to a status
update, not sooner than one year following the original audit. The purpose of status
updates is to hold staff accountable for addressing the audit findings and to identify any
Page 10 of 254
areas that have not seen significant progress. In addition, if applicable, further testing or
review may also be done to test whether improvements have had the intended effect.
Status Definitions:
• Complete = the recommendation has been fully implemented.
• In progress = implementation has begun.
• Not started = no work has begun yet but will in the future.
• Not required = the recommendation either does not require any action, or it is no
longer relevant, and no work will be done to implement it.
Number of original recommendations:
5
Number of recommendations complete-
1
Number of recommendations in progress:
2
Number of recommendations not started:
2
Number of recommendations not required:
0
Staff are in the process of implementing an asset management module and process to
keep records up to date. They will also be using the updated list of assets to inform the
2025 budget process to appropriately charge divisions for the assets that they have. They
have not shared the updated asset lists with divisions or implemented rotating cycle
counts yet as they are waiting for the asset management module implementation to be
complete.
They hope to be able to increase the contributions to the reserve fund for 2025 by
including technology assets that have not been updated by financial planning in recent
years.
Secondly, they will be able to plan their technology refresh cycles for laptops, desktops
and monitors with more accurate asset information and improve customer service to their
clients by ensuring their assets are up to date and reliable.
Conclusion / Next Steps:
A full physical inventory of field assets will be done in 2028 as part of the ongoing rotating
list of assurance audits performed by Internal Audit.
STRATEGIC PLAN ALIGNMENT:
This report supports the delivery of core services.
FINANCIAL IMPLICATIONS:
Capital Budget — The recommendation has no impact on the Capital Budget.
Operating Budget — The recommendation has no impact on the Operating Budget.
COMMUNITY ENGAGEMENT:
INFORM — This report has been posted to the City's website with the agenda in advance of
the council / committee meeting.
Page 11 of 254
PREVIOUS REPORTS/AUTHORITIES:
• CAO -2023-527 2024 Internal Audit Work Plan
APPROVED BY: Dan Chapman, CAO
ATTACHMENTS: None
Page 12 of 254
0
a
cc
W
H
Q
�J
O
Z
N
LO
N
O
M
N
0)
(6
n
LO
N
O
N
0)
(6
W
�
O
W
L
4J
O
i
I
N
L
W
—
ca
• —
�
—
c3
>%
Co
—
0
_O
C,
BLUM
X
W
CL
LO
N
O
N
0)
(6
LO
N
O
(� LO
N
(6
W
0
cc
0
�ld
cc
0
3:
J
Q
Z
W
H
X
W
c
cz
O
4--)
cz
cn
4-j
cz
j
^L
W
0 4-J
L �
O a -j
U
O >.
E
ca
— O
m �
C6 �
� O
X I
w
0
E
ca
O4
cn
-j
U
Q
M
X
E
O
a�
ca
cn
C!7
oc
X
I
I
I
0
LO
N
O
ti
N
0)
(6
►�
E
V
O
0
0 0 0
4A
.Q
Q
cc
V
O
CL
'i'
0
E
O
0
0
O
ca
E
E
•
0
LO
N
O
00
N
0)
(6
A
F
4-J
O
E
m
O
lj)-
n
LO
N
O
r
N
0)
(6
n
L
U (U
f6
L
L`
O
0
a..i
J
0
of
L�
M
a--+
i =3
U
a -J
Q
0
3
U
Q.
°
Q.
4-J
O
E
m
O
lj)-
n
LO
N
O
r
N
0)
(6
n
LO
N
O
O
N
N
0)
(6
n
N
4-J
O
CL
N
N
D
m
T!,
m
0 0 0
V
N
C�0
•
N
-
0
LO
N
O
N
N
0)
(6
LO
N
O
N
N
N
0)
(6
n
LO
N
O
M
N
N
0)
(6
N
i
4-J
W
W
E
O
O
N
U
C
=3
m
Q
N
kJo
O
�
m
i
�
�
o
.O
U
c
O
Q
a -J
cry
LL
0
0
0
LO
N
O
M
N
N
0)
(6
W
N
Z
W
a
X
W
W
Q
W
J
G
D
LO
N
O
N
N
0)
(6
n
�.J
L
0
C�
a --j
0
V
V
m
4-j
0
4�
L
0
L
CL
XT&UN
EIJI'
(L)
V
C�
E
0
V
nV //
W
V
0
Q
LO
N
O
LO
N
N
0)
(6
•
,i
4-j
0
4�
L
0
L
CL
XT&UN
EIJI'
(L)
V
C�
E
0
V
nV //
W
V
0
Q
LO
N
O
LO
N
N
0)
(6
M
O
O
N
N
O
N
r -I
ca
N
a--+
cn
a�
E
O
Q
•—
�
.E
_N
E
E
-0
O
0
4—JL
L2
cz
4—J
CL
E4—j
0
+-j
0
u
z
z
.
LO
N
O
CO
N
N
0)
(6
n
W
FE!Q
U
O
J
LO
N
O
ti
N
N
0)
(6
n
RIC
4-j
O
WIT%/1%
EIJI'
m
4-
0
LO
N
O
00
N
N
0)
(6
,i
4-j
O
WIT%/1%
EIJI'
m
4-
0
LO
N
O
00
N
N
0)
(6
LO
N
O
N
N
0)
(6
n
C:
O
N
C6
qzt
Or
N
-4
Ln
N
O
N
i
4O
aA
ca
O
`n
Ca
ca
_0O
N
O
0
O
E4
-J
O
._
U
cC:
O
>
},
+-+
O
O
O
O
�
u
LO
N
O
N
N
0)
(6
n
W
N
O
J
W
LCL
C7
O
J
Q
Z
U
W
H
LO
N
O
O
M
N
0)
(6
n
�.J
.J
0
V
V
m
4-j
0
•�
ii////�IIIIIIII
nV //
W
0
L
CL
4-j
V
•
CL
,i
0
4-j
0
•�
ii////�IIIIIIII
nV //
W
0
L
CL
4-j
V
0
EIJI'
4-j
0
V
-j
W
0
4-j
Lid
N
O
M
N
0)
(6
CL
0
0
EIJI'
4-j
0
V
-j
W
0
4-j
Lid
N
O
M
N
0)
(6
L )
O
a--)
ca
0
E
E
O
V
i
ca
a -j
CL
E
O
U
N
C
N
w
L
ca
0
O
z
C��
LO
N
O
N
M
N
0)
(6
n