HomeMy WebLinkAboutStaff Report 2303-10828.Approval of Office of the City Auditor's Utility Work Order Process and Control Review
Report
1
8
2
2
City Council
Staff Report
From: City Manager
Report Type: CONSENT CALENDAR
Lead Department: City Auditor
Meeting Date: March 20, 2023
Report #:2303-1082
TITLE
Approval of Office of the City Auditor's Utility Work Order Process and Control Review Report
RECOMMENDATION
The Policy and Services Committee, City Auditor, and Staff recommend that the City Council
approve the Utility Work Order Process and Control Review report.
BACKGROUND
Baker Tilly, in its capacity serving as the Office of the City Auditor (OCA), performed a citywide
risk assessment that assessed a wide range of risk areas, including strategic, financial,
operational, compliance, technological, and reputation risks. The purpose of the assessment was
to identify and prioritize risks to develop the annual audit plan.
During the FY2021 risk assessment1, the OCA identified a utility risk related to work order
processes. With customers’ utility needs, it is vital that work order processes operate optimally.
DISCUSSION
The City of Palo Alto Utilities (CPAU) offers its residents and businesses a suite of utility services,
including electricity, natural gas, water, and sanitary sewer (wastewater). All four utilities have a
similar organizational structure around the work order process which includes designers,
engineers, project utility coordinators, operations crew, and accountants who all play a vital role
in ensuring that utility services are provided in a manner consistent with CPAU’s mission. Utility
work orders include capital and customer work orders as well as maintenance jobs. The electric
utility follows a slightly different process and uses different software systems than the water,
gas, and wastewater utilities.
1 FY2021 risk assessment https://www.cityofpaloalto.org/files/assets/public/agendas-minutes-
reports/reports/city-manager-reports-cmrs/year-archive/2021/id-11952.pdf
1
8
2
2
The objectives of the Utility Work Order Process Review were to:
1) Determine whether adequate controls around the work order process are in place and
working effectively
2) Assess the work order process against best practices
To evaluate work order controls, audit testing was conducted on a sample of electric, water,
gas and wastewater work orders.
The attached report summarizes the analysis, audit findings, and recommendations.
FISCAL/RESOURCE IMPACT
The corrective action plans and the timeline for implementation are identified within the
attached report.
STAKEHOLDER ENGAGEMENT
The Office of the City Auditor worked primarily with Utilities Department and engaged with
additional stakeholders, including the City Manager’s Office and the City Attorney’s Office, as
necessary.
ATTACHMENTS
Attachment A: OCA - Utility Work Order Process and Control Review Report
1
City of Palo Alto
City Auditor's Office
Utility Work Order Process &
Control Review
August 9, 2022
2
Executive Summary
Purpose of the Audit
The purpose of the audit was to gain an understating of the City's utility work order processes, evaluate the effectiveness
of relevant internal controls and evaluate the City’s utility work order processes against best practices.
Report Highlights
Baker Tilly performed an analysis of the utilities work order processes and controls through sample-based testing, review
of policy and procedure documentation and walkthroughs with key process owners. The “Audit Results” section of this
report shows the detailed observations and recommendations based on our review. The following table highlights the four
main recommendations (major themes) of these observations and recommendations.
Note: In practice, the City generally refers to capital and customer work orders as “service orders” and maintenance jobs
as “work orders”. Throughout this report we will refer to both as “work orders” and will identify if we are only referring to
one or the other.
Category/Theme Page Recommendation
Internal Approvals Pg. 7, 8 We recommend that all work orders (including O&M) be approved by someone
that has budget responsibility or the ability to approve unbudgeted
maintenance work. Any design changes made after the initial approval should
be approved by an engineer or supervisor as changes in the field are a safety
issue for service and could impact other areas that the field crew may not be
aware of. Once the work order is complete, the supervisor should review all
labor, materials, and 3rd party invoices for accuracy and ensure any as-built
documentation is correct. All of the above reviews and approvals should be
evidenced in writing.
System Utilization Pg. 8 We recommend the electric utility consider evaluating whether SAP has the
capability to effectively track work orders to avoid using side systems. The
electric utility should also evaluate whether implementing SoGen, the system
used by the water, gas, and wastewater utilities to track work orders, would
allow for easier, more accurate work order tracking. A system with the ability to
schedule work orders based on priority will also ensure there is not
unnecessary downtime or overburdening of worker time.
Palo Alto utilities should also consider developing an interface between the
handheld devices used by the crew in the field and SoGen to eliminate
duplicate processes and allow stakeholders access to the most up-to-date
information as changes are being made in real time.
Recording &
Reconciling Assets
Pg. 7, 8 We recommend work orders be closed and capitalized monthly to prevent a
backlog and to ensure depreciation starts immediately when the asset is placed
in service. The utilities should perform a full system reconciliation of assets in
AME and SoGen to ensure assets are accurately recorded. Assets in AME and
SoGen should continue to be reconciled on an annual basis (or cycle counts can
be performed monthly where a certain type of asset is counted each month). In
addition, all reconciliations of the assets under construction account and asset
accounts should be performed on a monthly basis to ensure monthly financials
are accurate.
Key Performance
Indicators
Pg. 8 We recommend that the electric utility develop and track maintenance and
construction KPIs similar to those tracked by the water, wastewater, and gas
utilities. In addition, all utilities should consider developing additional KPIs as
identified in Appendix D.
3
Table of Contents
Executive Summary ........................................................................................................................................................... 2
Purpose of the Audit ..................................................................................................................................................... 2
Report Highlights ........................................................................................................................................................... 2
Objective........................................................................................................................................................................... 4
Background ..................................................................................................................................................................... 4
Scope................................................................................................................................................................................. 4
Compliance Statement.................................................................................................................................................. 4
Detailed Testing & Analysis ........................................................................................................................................ 5
Methodology & Approach ............................................................................................................................................ 5
Audit Testing ................................................................................................................................................................... 6
Audit Results ....................................................................................................................................................................... 7
Appendices .......................................................................................................................................................................... 9
Appendix A: Process Flowcharts .............................................................................................................................. 9
Appendix B: Sampled Work Orders ........................................................................................................................ 15
Appendix C: Current KPI’s for Water, Wastewater, and Gas – Selected Examples ................................... 16
Appendix D: Potential KPIs for Consideration ..................................................................................................... 19
Appendix E: Management Response ...................................................................................................................... 20
4
Introduction
1 Government auditing standards require an external peer review at least once every three (3) years. The last peer review of the
Palo Alto Office of the City Auditor was conducted in 2017. The Palo Alto City Council approved a contract from October 2020
through June 2022 with Baker Tilly US, LLP (Baker Tilly) and appointed Kyle O’Rourke, Senior Consulting Manager in Baker Tilly's
Public Sector practice, as City Auditor. Given the transition in the City Audit office, a peer review was not conducted in 2020 and will
be conducted after the third year of Baker Tilly’s contract.
0BObjective The purpose of the audit was to gain an understating of the City's utility work order
processes, evaluate the effectiveness of relevant internal controls and evaluate the City’s
utility work order processes against best practices.
1BBackground The City of Palo Alto Utilities (CPAU) offers its residents and businesses a suite of utility
services, including electricity, natural gas, water, and sanitary sewer. The CPAU's mission
statement is to "provide safe, reliable, environmentally sustainable and cost-effective
services." Disruption in services can result in the CPAU's inability to meet its customer's
needs and impact the reliability of its services.
All four utilities have a similar organizational structure around the work order process which
includes designers, engineers, project utility coordinators, operations crew, and accountants
who all play a vital role in ensuring that utility services are provided in a manner consistent
with CPAU’s mission. The electric utility follows a slightly different process and uses different
software systems than the water, gas, and wastewater utilities. The detailed processes
around work orders for all utilities can be found in Appendix A of this report.
To ensure CPAU continues to meet its mission, and mitigate risks, the City engaged Baker
Tilly to conduct an internal audit that would focus on current utility work order processes.
This decision was in conjunction with a broader, Citywide audit plan detailing the potential
risks facing each department. Baker Tilly performed a citywide risk assessment that
assessed a wide range of risk areas, including strategic, financial, operational, compliance,
technological, and reputation risks. The purpose of the assessment was to identify and
prioritize risks to develop the annual audit plan.
During the FY2021 risk assessment, Baker Tilly identified a utility risk related to work order
processes. With customers’ utility needs, it is vital that work order processes operate
optimally.
In order to properly assess the CPAU's work order processes, Baker Tilly reviewed a sample
of electric, gas, water and wastewater work orders for compliance with internal processes
and controls. We also evaluated the City’s utility work order processes against best
practices. For additional details, please review the Detailed Testing & Analysis section.
2BScope The scope of this engagement includes work orders for the Electric and Wastewater, Gas
and Water (WGW) departments opened between 1/1/21 and 2/21/22.
3BCompliance
Statement
This audit activity was conducted from January 2022 to May 2022 in accordance with
generally accepted government auditing standards, except for the requirement of an
external peer review0F
1. Those standards require that we plan and perform the audit to obtain
sufficient, appropriate evidence to provide a reasonable basis for our findings and
conclusions based on our audit objectives. We believe that the evidence obtained provides a
reasonable basis for our findings and conclusions based on our audit objectives.
The team at CPAU was very proactive and involved in the engagement that allowed for clear communication and
support for Baker Tilly’s team. CPAU was always available to provide additional support and hands on explanations on
the work order processes.
The Office of the City Auditor greatly appreciates the support of the CPAU in conducting this audit activity.
Thank you!
5
Detailed Testing & Analysis
Methodology &
Approach
The objectives of the Utility Work Order Process Review are to:
(1) Determine whether adequate controls around the work order process are in
place and working effectively
(2) Assess the work order process against best practices
Our review scope includes the following:
• Interview the appropriate individuals to understand the process, the information
system used, and internal controls related to the work order process
• Review policies and procedures as well as the regulations and standards to
identify the criteria to be used for evaluation of control design and effectiveness
• Perform a test of key internal controls on a sample basis
• Compare the process and controls against best practices
To evaluate work order controls, audit testing was conducted on a sample of electric, water,
gas and wastewater work orders. In addition, common work order best practices were
identified and compared to the City’s utility work order processes identified in this audit.
Additional information regarding the testing approach and methodology can be found in the
Audit Testing section.
In order to properly evaluate the work order process and internal controls, the specific
approach included the steps noted below.
Audit Planning
• Conduct research and gather information to understand the current environment
• Assess audit risk
• Develop an audit planning memo and program
• Conduct kick-off meeting with key stakeholders
Control Review and Testing
• Gather information to understand the environment under review
• Conduct interviews with key process owners and management
• Assess risks and identify controls in place
• Perform testing of key controls around work orders
• Identify work order best practices and compare with current utility work order
processes
Reporting
• Develop findings and recommendations based on supporting evidence
• Validate documented findings
• Develop and validate a draft audit report
• Finalize report with management responses
• Review and finalize report with the City Council and/or appropriate Council
Committee
6
Audit Testing
Introduction In order to achieve the objectives of the engagement, Baker Tilly developed an audit testing
approach and methodology that would test the design and operational effectiveness of
controls and identify control gaps and unmitigated risks around utility work order processes.
Approach &
Methodology
In order to evaluate CPAU's control environment, Baker Tilly developed process flowcharts
based on process documentation and interviews with key process owners. The process
flowcharts were developed to document known risk, and mitigation practices based on
CPAU's information and identify opportunities and improvement areas. In addition, the
process flowcharts were used to facilitate the risk assessment and note the risks for the
current work order processes, identify control gaps, and determine key risk and control areas
for audit testing. The detailed process flowcharts can be found in Appendix A.
Sampling
Methodology
CPAU provided a list of all work orders opened from 1/1/2021 through 2/12/2022. Using the
AICPA Statistical Sample Sizes for Test of Controls grid with the following parameters (90%
confidence level, 9% tolerable rate of error, with 0% expected deviation) a sample size
of 25 was established. Samples were selected using auditor judgment with a focus on
ensuring a variety of work order types (differing utilities, capital vs. O&M). Because the work
order process for the electric utility differs from the other utilities, we selected 12 electric work
orders and 13 WGW work orders for our control testing. The work orders selected for
detailed control testing can be found in Appendix B.
Additional
Review
In addition to testing sample work orders for the key controls currently in place, we also
identified process and control improvement recommendations based on best practices. As
part of the best practices review, we identified common work order best practices and
compared them to the utilities’ current work order processes and controls. Best practices also
include the use of Key Performance Indicators (KPIs). We identified common work order
related KPIs and determined whether such KPIs were in use.
7
Audit Results
During our process walkthroughs, we identified six key controls that Palo Alto currently has in place around the work order process. Some controls only apply to a
certain utility or only apply to capital work orders. As such, not all controls are applicable for each of our sampled work orders. The controls tested, results of each
test, and our recommendations for improvement are shown in the table below.
Control
# Control Description
# of sampled
work orders
where control
is applicable
# of
deficiencies Result Recommendation
1
All capital work orders
require supervisor approval
before work can be
performed.
21 3
Maintenance work for water, gas, and wastewater do not
require approval. Of the 21 sampled work orders that
currently require approval, 3 did not have appropriate
signature approval on the work order. All 3 were capital
work orders.
We recommend that all work orders (including
O&M) be approved by someone that has
budget responsibility or the ability to approve
unbudgeted maintenance work. Approval
should be evidenced in writing.
2
For development services
projects, a permit must be
reviewed and approved
before work can begin.
10 0
10 of our sampled work orders were for development
services projects and all but one had an approved permit
prior to work being performed.
None
3
For customer work orders,
customer payment must be
received before work can
begin.
9 0
9 of our sampled work orders were for customer services
and all had customer payment received before work
began.
None
4
The Supervisor reviews the
completed work order and
as-built forms for accuracy.
25 5
2 of the 25 sampled work orders did not have a signature
showing review of the completed work order. We also
noted 3 work orders that had a signature showing a
review was performed, however, there were costs which
were incorrectly recorded to the wrong work order
indicating the review may not be performed at a detailed
enough level and could be improved.
The review performed by the Supervisor
should include review of all labor and
materials including 3rd party invoices to
ensure all costs are recorded to the correct
work order.
5
For electric work orders, the
Utility Coordinator
completes a checklist to
ensure all documents are
complete and included in
the job packet (control
implemented in August
2021).
5 0
Because this control was not implemented until August
2021 and only pertains to electric work orders, this
control only applied to 5 of the sampled work orders. All
5 work orders had a completed checklist.
We recommend the Utility Coordinator
complete a checklist to show review of
verifying completeness of work orders for all
utilities.
6
For closed capital work
orders, the assets were
recorded to an appropriate
plant account.
4 0
Costs are capitalized to the asset account on a quarterly
basis. As such, only 4 of our sampled capital work orders
were closed and recorded to the asset account at the
time of our testing. All 4 work orders appeared to be
recorded to an appropriate asset account (although not
all costs were included as noted in control number 4
above). Per Palo Alto, the additional costs will be
recorded in Q3 FY22.
We recommend work orders be closed and
capitalized monthly to prevent a backlog and
to ensure depreciation starts immediately
when the asset is placed in service.
8
In addition to the sample work order testing performed above, we have developed additional observations and recommendations based on our process
walkthroughs with key process owners and compared to best practices. These are shown in the table below.
# Category Observation Recommendation
1 System
Utilization
Although the water, wastewater, and gas utilities utilize SoGen to
track work order progress, the electric utility utilizes SharePoint to
share files and track work order status. Scheduling work is often
done by the supervisor on a white board.
In addition, the operations team has handheld tablets in the field,
however, these do not interface with SoGen and they have found
that it is easier to handwrite all workorder information manually on
paper forms. These paper forms are then given to the utility
coordinator to be entered into SoGen, duplicating the data entry
function.
We recommend the electric utility consider evaluating whether SAP has the
capability to effectively track work orders to avoid using side systems. The
electric utility should also evaluate whether implementing SoGen, the
system used by the water, gas, and wastewater utilities to track work
orders, would allow for easier, more accurate work order tracking. A system
with the ability to schedule work orders based on priority will also ensure
there is not unnecessary downtime or overburdening of worker time.
Palo Alto utilities should also consider developing an interface between the
handheld devices and SoGen to eliminate duplicate processes and allow
stakeholders access to the most up-to-date information as changes are
being made in real time.
2 Design Changes
Currently, only major field changes require approval from the
engineering supervisor and that approval is oftentimes provided
verbally.
Any design changes should be approved by an engineer or supervisor.
Changes in the field are a safety issue for service and could impact other
areas that the field crew may not be aware of. All approvals should be
evidenced in writing.
3
Recording Asset
Additions &
Retirements
For water, gas, and wastewater, the asset additions and
retirements that the Business Analyst provides to the Accountant
for recording is a report from the SoGen system. These costs are
settled to accounts in SAP. The Business Analyst indicated that
assets may not have been recorded accurately in the past and
that AME should be the system of record when recording asset
additions and retirements. If assets are not recorded appropriately,
financial, and other reporting becomes less reliable.
We recommend that the utilities perform a full system reconciliation of
assets in AME and SAP to ensure assets are accurately recorded. Assets
in AME and SAP should continue to be reconciled on an annual basis (or
cycle counts can be performed monthly where a certain type of asset is
counted each month). This reconciliation should be documented and
signed-off on.
4 Asset
Reconciliations
Asset reconciliations are performed on a quarterly basis. No
review of the reconciliation is performed by a separate individual.
In addition, the reconciliation process is very manual with the
accountant manually entering in numbers instead of using
formulas to add the next periods numbers.
All reconciliations should be performed on a monthly basis to ensure
monthly financials are accurate. All reconciliations should be reviewed for
accuracy by another individual. This review should be evidenced in writing.
In addition, Palo Alto should consider using more formulas in the
reconciliation to reduce the risk of errors that can be caused by manually
entering in numbers.
5 Key Performance
Indicators
The water, wastewater, and gas utilities currently utilize key
performance indicators (KPIs) to assist with monitoring their
performance around project management and operations. An
example of some of the KPIs currently being used are shown in
Appendix C. It is our understanding that these same KPIs are
currently being developed for the electric utility.
We agree that the electric utility would benefit from developing similar KPIs
that the water, wastewater, and gas utilities currently use. In addition, the
utilities may want to consider adding additional KPIs related to work orders
and project management. Example KPIs that the City may want to begin
tracking are shown in Appendix D.
9
Appendices
Appendix A: Process Flowcharts
Below are the process flowcharts developed to identify risks and key controls.
Palo Alto
Eng
i
n
e
e
r
/
Est
i
m
a
t
o
r
De
s
i
g
n
e
r
Uti
l
i
t
y
Coo
r
d
i
n
a
t
o
r
Eng
i
n
e
e
r
Ma
n
a
g
e
m
e
n
t
Op
e
r
a
t
i
o
n
s
Cus
t
o
m
e
r
Ser
v
i
c
e
Electric Service Order Process
Process Note ControlRisk
Start
Creates Service
Order
SAP
Designs Service
Order and
uploads to SAP
Approves
service order
with design
and estimate
Assigns Work
center
1
2
Requests
service order
Email
AUD
Service Order
approved?
Makes
required
changes
No
Customer
Service Order?
Sends invoice
to customer
Releases
Service Order
SAP
Yes
SAP
No
Reviews
Service Order
packet for
completeness
3
SAP
Works with
assigned team to
schedule &
manage jobs in
queue
6
4
AUD
Crew/ group
goes to field
Obtains approval
and updated
drawing from
Engineer
Work different
from Design?
Completes
work
Yes
No
5
78
A
1 1
3 3
Share
Point Share
Point
S.O. Pkg
Once, payment
received,
Releases
Service Order
Share
Point
Yes
SAP Invoice
2 2
10
Palo Alto
Ma
p
p
i
n
g
G
r
o
u
p
Uti
l
i
t
y
C
o
o
r
d
i
n
a
t
o
r
Op
e
r
a
t
i
o
n
s
S
u
p
e
r
v
i
s
o
r
Sen
i
o
r
A
c
c
o
u
n
t
a
n
t
Electric Service Order Process
A
Process Note ControlRisk
Reviews As-
Builts and asset
forms
Reviews
Package &
uploads to
SharePoint
Reviews
Service Order
for accuracy
Updates GIS for
As-Builts
9
GIS
Settles all
Service Order
costs to g/l
accounts
SAP SAP
11
Capital
Project?
Quarterly, runs
report of all
closed work
orders
Yes
Unitizes all
costs to asset
accounts
End
12
Closes Service
Order
No
SAP
SAPSAPSAP
4 4 5 5
6 6
7 7
8 8
IW39
Puts forms in
Package &
forwards to
Coordinator
Package
S.O. Pkg.
Closes Work
Order (TECO)
SAP
Closes Service
Order
SAP
10
11
1. When creating the service order, the Engineer identifies it as either capital or maintenance and includes the general ledger accounts the costs should be applied to. The Engineer
includes the cost estimate when creating the service order.
2. The Designer includes a Material Release Date in SAP (either June or December) to ensure that the warehouse doesn’t pull materials prematurely.
3. The Service Order Package is signed using DocuSign. The Senior Engineer can approve projects up to $50,000 as long as there are funds in the budget. Projects over this amount need
to follow the authority levels shown below:
Up to $50,000 – Senior Engineer
Up to $150,000 – Manager
$150,000 and over – Engineering Manager
4. The service order is released in SAP and SharePoint.
5. The Utility Coordinator verifies that all required information is included and checked off in SAP and SharePoint including the service order type, materials, design/drawings, and the
customer payment (if applicable).
6. The Utility Coordinator updates the work center in SAP which designates the group/crew the service order is assigned to. They also change the order status to “open” in SharePoint
and indicate the date the service order is assigned. The Utility Coordinator updates the materials date in SAP if not already shown. The group/crew will update the material date when
ready so the warehouse knows the materials are needed.
7. The group/crew examine the field conditions before work can be scheduled. For customer service orders, the operations team works directly with the customer to schedule the work.
If it is a customer service job with underground work, it goes to the underground Inspection group. There would be an update in SAP showing the inspection is complete.
8. If the change is small, the crew can just make the change. If it is a major change, they call the Engineer for approval and an updated drawing/map.
9. The Operations Supervisor reviews and approves all crew time and ensures they charged the correct service order. They also review all materials and third party invoices for accuracy
and that all associated costs are being charged against order (e.g. Crane Rental or PCard/credit card purchases).
10. The Utility Coordinator uses a checklist to ensure all documentation is included in the service order package including map changes.
11. Labor, materials, and overheads are posted to either an O&M account or to Construction in Progress. Service orders are held open for 6 weeks to ensure all costs have been applied.
12. AUD automatically creates an Excel report that shows all additions and retirements when added by the mapping group . These are stored on the network so the Senior Accountant
can retrieve the report and unitize the assets.
Process Notes
Risks
Controls
1. Work is performed that is not approved.
2. Work may be scheduled before customer payment is received.
3. The crew may change the design without approval which could cause unsafe materials or designs.
4. Labor and materials may be charged to the incorrect service order.
5. The As-Builts may be incorrect if design changes were made in the field.
6. The service order may be closed without proper documentation of completeness.
7. The GIS mapping system may not display the correct assets in the electric system.
8. Asset additions and retirements may not be recorded or may be recorded to the wrong account.
1. All service orders are reviewed and approved by the Senior Engineer prior to work being performed.
2. Customer Service will not release a service order until the customer payment has been received.
3. For major changes, the crew receives approval from the Lead Engineer and receives updated job drawing(s) to reflect changes.
4. The Operations Supervisor reviews and approves all employee timecards and ensures the correct service order was charged. They also review material charges to the service order
for accuracy.
5. The Operations Supervisor ensures that the As-Builts match the work that was performed.
6. The Utility Coordinator uses a checklist to ensure all documents are included in the service order package including any map changes.
7. All asset additions and retirements are updated in EEM & GIS to accurately track all electric assets.
8. The Senior Accountant is provided a listing from Engineering showing all asset additions and retirements to ensure proper unitization.
12
Palo Alto
Eng
i
n
e
e
r
/
Est
i
m
a
t
o
r
De
s
i
g
n
e
r
Uti
l
i
t
y
Coo
r
d
i
n
a
t
o
r
Eng
i
n
e
e
r
/
Op
e
r
a
t
i
o
n
s
Ma
n
a
g
e
m
e
n
t
Op
e
r
a
t
i
o
n
s
De
v
e
l
o
p
m
e
n
t
Ser
v
i
c
e
s
Water/Gas/Wastewater Service Order Process
Process Note ControlRisk
Start
Creates Service
Order
Designs Service
Order and
uploads to SAP
Approves
service order
as designed
4
Requests
service order
Email
AME
Service Order
approved?
Makes
required
changes
No
Releases
Service Order
SAP
Prints and reviews
Service Order
package for
completeness
5
Works with
assigned team to
schedule &
manage jobs in
queue
6
AME
Crew/ group
goes to field
Obtains approval
and updated
drawing from
Engineer
Work different
from Design?
Completes
work
7
9
A
3 3
4 4
S.O. Pkg
Once, payment
received,
Releases
Service Order
2 2
Development
Service order?
Yes
Reviews and
approves
permit
application
Creates and
sends invoice
to customer
SAP
SOGen
No
Yes
SOGen
SOGen
SAP
SAP
SOGen SAP
1 1 3
Creates Service
Order
O&M service
order?
Yes
Yes
8
SOGen
SAP
No
2
1
Customer
payment
required?
Yes
No
No
13
Palo Alto
Ma
p
p
i
n
g
G
r
o
u
p
Uti
l
i
t
y
C
o
o
r
d
i
n
a
t
o
r
Op
e
r
a
t
i
o
n
s
Sup
e
r
v
i
s
o
r
Sen
i
o
r
A
c
c
o
u
n
t
a
n
t
Water/Gas/Wastewater Service Order Process
A
Process Note ControlRisk
Reviews
Package &
uploads to
SOGen
Reviews
Service Order
for accuracy
Updates GIS for
As-Builts
10
AME
Settles all
Service Order
costs to g/l
accounts
12
Capital
Project?
Quarterly, runs
report of all
closed work
orders
Yes
Unitizes all
costs to asset
accounts
End
13
Closes Service
Order
No
SAP
SAPSAPSAP
5 5
6 6
7 7
8 8
IW39
S.O. Pkg
S.O. Pkg.
Closes Work
Order (TECO)
Closes Service
Order
SAP
11
Sogen
14
1. Anyone in operations can request a service order for maintenance work.
2. The Development Services Engineer reviews the utilities application, request for service including number of meters, construction plans indicating location of services and meters. The
Engineer signs off on his review in ACCELA, the permit tracking system.
3. Development Services receives an automated report from SAP daily that shows all payments received.
4. When creating the service order, the Engineer identifies it as either capital or maintenance and includes the general ledger accounts the costs should be applied to. The Engineer
includes the cost estimate when creating the service order.
5. The Service Order Package is signed using DocuSign. The Senior Engineer can approve projects up to $50,000 as long as there are funds in the budget. Projects over this amount need
to follow the authority levels shown below:
Up to $50,000 – Senior Engineer
$50,000 and above – Manager
6. The service order is released in SAP and SOGen.
7. The Utility Coordinator verifies that all required information is included and checked off in SAP and SOGen including the service order type and the design/drawings. They also change
the order status to “Ops Inspection” in SOGen and indicate the date the service order is assigned.
8. The group/crew examine the field conditions before work can be scheduled. For customer service orders, the operations team works directly with the customer to schedule the work.
If it is a service order for new construction, it goes to the Inspector.
9. If the change is small, the crew can just make the change. If it is a major change, they call the Engineer for approval and an updated drawing/map. It is very rare to have a redesign.
10. The Operations Supervisor reviews the service order to ensure that labor and materials are properly recorded and that the As-Builts match the work that was completed.
11. The Utility Coordinator reviews the service order package to ensure all forms are filled out and that maps, photos, and tags are all included.
12. All service orders are closed quarterly in SAP.
13. The Utility Coordinator runs a report of all additions and retirements out of Sogen monthly and emails it to the Accountant to record additions and retirements. For capital
improvement projects (CIP), the Engineer provides the Accountant with an Excel spreadsheet that lists the asset additions and retirements to record.
Process Notes
Risks
Controls
1. A service order may be created without an approved permit in place.
2. Work may be scheduled before customer payment is received.
3. Work is performed that is not approved.
4. The crew may change the design without approval which could cause use of incorrect materials or designs.
5. A service order may be closed without the appropriate labor, materials, and assets being recorded.
6. The service order may be closed without proper documentation of completeness.
7. The GIS mapping system may not display the correct assets in the w/g/w system.
8. Asset additions and retirements may not be recorded or may be recorded to the wrong account.
1. All permits are reviewed and approved before a service order is created.
2. Development Services will not release a service order until the customer payment has been received.
3. All service orders for new construction are reviewed and approved by the Senior Engineer prior to work being performed.
4. For major changes, the crew receives approval from the Lead Engineer and receives updated job drawing(s) to reflect changes.
5. The Operations Supervisor reviews the service order to ensure all labor and materials have been recorded. They also ensure that the As-Builts on the service order match the work
that was performed.
6. The Utility Coordinator ensures all documents are included in the service order package including any map changes. They also ensure all required checkboxes and forms are
complete.
7. All asset additions and retirements are updated in AME to accurately track all assets.
8. The Senior Accountant is provided a report from Sogen by the Utility Coordinator showing all asset additions and retirements to ensure proper unitization. The Engineer provides a
schedule showing the asset additions and retirements for CIP project.
15
Appendix B: Sampled Work Orders
Sample #Order Type Order Type Description Order Total Actual
Costs WBS Element Project Description
1 UERE Electric Reconstruction 40026477 33,909.46$ EL-89044 Substation Facility
2 UGRE Gas Reconstruction 40026573 33,819.98$ GS-11002 Gas System Improveme
3 UECS Electric Customer Service 40026526 30,181.68$ EL-89028 Electric Customer Co
4 UWRE Water Reconstruction 40026411 24,021.92$ WS-80014 Water Hydrant and Va
5 USRE Wastewater Reconstruction 40026713 12,408.03$ WC-80020 Sewer System, Custom
6 UCM Utilities Corrective Maintenance Order 50037259 12,029.75$
7 UECS Electric Customer Service 40026572 10,215.60$ EL-89028 Electric Customer Co
8 UCM Utilities Corrective Maintenance Order 50037518 10,094.45$
9 UWCS Water Customer Service Order 40026530 8,935.45$ WS-80013 Water System, Custom
10 USRE Wastewater Reconstruction 40026744 7,614.81$ WC-80020 Sewer System, Custom
11 UWCS Water Customer Service Order 40026680 7,573.37$ WS-80013 Water System, Custom
12 UGCS Gas Customer Service Order 40026407 7,328.00$ GS-80017 Gas System, Customer
13 UECS Electric Customer Service 40026776 7,015.39$ EL-89028 Electric Customer Co
14 UERE Electric Reconstruction 40026628 6,900.10$ EL-19004 Wood Pole Replacemen
15 UCM Utilities Corrective Maintenance Order 50037558 6,747.60$
16 UCM Utilities Corrective Maintenance Order 50036962 5,711.71$
17 UERE Electric Reconstruction 40026983 5,333.24$ EL-19004 Wood Pole Replacemen
18 UECS Electric Customer Service 40026585 4,748.07$ EL-89028 Electric Customer Co
19 UGCS Gas Customer Service Order 40026403 2,958.42$ GS-80017 Gas System, Customer
20 USCS Wastewater Customer Service Order 40026836 2,169.98$ WC-80020 Sewer System, Custom
21 UERE Electric Reconstruction 40026798 2,076.36$ EL-98003 Electric System Imp
22 USCS Wastewater Customer Service Order 40026535 1,932.45$ WC-80020 Sewer System, Custom
23 UERE Electric Reconstruction 40026835 12,439.56$ EL-19004 Wood Pole Replacemen
24 UERE Electric Reconstruction 40026868 10,781.31$ EL-19004 Wood Pole Replacemen
25 UGRE Gas Reconstruction 40024105 113,840.40$
16
Appendix C: Current KPI’s for Water, Wastewater, and Gas – Selected Examples
17
18
19
Appendix D: Potential KPIs for Consideration
KPI Description Calculation
Emergency Work
This is a measure of how much unplanned
emergency work your maintenance
department does.
Emergency Hours Worked / Total Hours
Planned vs Unplanned
This measure is a good indicator of how
reactive a work team is, and how much
unscheduled work is breaking into the work
schedule.
Sum of Scheduled Work Completed
(Hours) / Sum of All Work Completed
(Hours) x 100
Schedule Compliance
Measure of planning and scheduling quality,
whether the work plan is realistic and
achievable. It is a good indicator of
maintenance effectiveness, whether they are
able to complete the work allocated to them.
Sum of Scheduled Work Completed
(Hours) / Sum of Scheduled Hours x 100
Maintenance Overtime Measure of the health of your maintenance
organization.
Maintenance Overtime / Total
Maintenance Hours Paid
Work order Cycle Time
The objective is to understand how long it
takes to complete work, from creation to
completion.
Work Order Completion Date – Work
Order Creation Date (in days)
Customer Satisfaction
The objective is to score and track how
happy your customers are with their service,
product, and/or experience. Example: “How
would you rate your recent experience with
our operations team?” The customer is given
a 5-point scale from very unsatisfied to very
satisfied.
The total sum of customers who
answered with “satisfied” (4) and “very
satisfied” (5).
20
Appendix E: Management Response
Recommendation Responsible
Department(s)
Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action
Plan
We recommend that all work orders (including O&M) be approved by
someone that has budget responsibility or the ability to approve
unbudgeted maintenance work. Approval should be evidenced in
writing.
Utilities Concurrence: Agree
Target Date: August 2022
Action Plan: Capital and customer service work order packets are
prepared by an Estimator/Engineer and approved by Senior
Engineer/Supervisor. In Operations all work orders are prepared at the
direction of Supervisor, by a Project Coordinator, the work order packet
is given to the Supervisor for distribution to the crews. At present, a
signature is not required before handing the job packet to the crews.
Utilities will add the step requiring the Supervisors to initial the work
orders at the time the paperwork is handed off to the crews.
The review performed by the Supervisor should include review of all
labor and materials including 3rd party invoices to ensure all costs are
recorded to the correct work order.
Utilities Concurrence: Agree
Target Date: August 2022
Action Plan: WGW Operations has a checklist when closing work orders
and Electric Operations recently implemented one in August of 2021. In
the current process, the Supervisor/Leads forwards the invoices to
administrative staff to process the payment. To ensure all items are
posted against the order, the Supervisor/Lead will include all invoices for
services, labor (charge time), materials in the project closing packet for
the Project Coordinator’s review. The Project Coordinator then becomes
the initiator, rather than the Supervisor/Leads, for processing invoice
payments.
We recommend the Utility Coordinator complete a checklist to show
review of verifying completeness of work orders for all utilities.
Utilities Concurrence: Agree
Target Date: August 2022
Action Plan: WGW Operations has a checklist when closing work orders
and Electric Operations recently implemented one in August of 2021.
The Project Coordinators will use the checklist to verify labor, materials,
and 3rd party invoices have been charged to the order before closing the
order.
We recommend work orders be closed and capitalized monthly to
prevent a backlog and to ensure depreciation starts immediately when
the asset is placed in service.
Utilities & ASD Concurrence: Partially Agree
Target Date: December 2022
Action Plan: Settlement of costs, closing of work orders and
reconciliation of Construction in Progress are done on a monthly basis.
The reconciliation reports are saved on the network at quarter ending
21
Recommendation Responsible
Department(s)
Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action
Plan
since capitalization are done on the same period. Working with limited
staff in both Utilities Operations and Accounting, monthly capitalization is
time consuming and not practical. Although it ensures depreciation will
start immediately when asset is placed in service, depreciation is still
considered a low risk assessment since it is a non-cash item and the
depreciation amount is relatively immaterial compared to the total asset
base. However, we recognize monthly capitalization is the best practice
and will evaluate the feasibility of implementing the process.
We recommend the electric utility consider evaluating whether SAP has
the capability to effectively track work orders to avoid using side
systems. The electric utility should also evaluate whether implementing
SoGen, the system used by the water, gas, and wastewater utilities to
track work orders, would allow for easier, more accurate work order
tracking. A system with the ability to schedule work orders based on
priority will also ensure there is not unnecessary downtime or
overburdening of worker time.
Palo Alto utilities should also consider developing an interface between
the handheld devices and SoGen to eliminate duplicate processes and
allow stakeholders access to the most up-to-date information as
changes are being made in real time.
Utilities Concurrence: Partially Agree
Target Date: Ongoing
Action Plan: Utilities staff plans to evaluate a mobile work management
application that will address the needs of all its utilities and utilize one
utility operations application to compliment SAP.
Background:
Utilities explored using the scheduling feature in SAP and found that the
task was too cumbersome and customizing SAP to accommodate the
various utilities was cost prohibitive for an organization of our size and
with different needs for each of the five utilities. The scheduling function
in SAP has not changed since we first implemented in 2004 and with the
different types of assets and maintenance and construction requirements
for all utilities, the scheduling module is not feasible. At present, SoGen
is not the ideal application to be used in the field nor for Electric
Operations to adopt. It is a stand-alone application that serves as an
intermediary to capture work details such as location, activity, labor, and
materials.
In staff’s experience, best of breed utility applications are more
operationally efficient and cost effective than customizing SAP.
The department is continually keeping an eye out for mobile work
management applications. Some recent examples are:
• In February 2019 through September 2019, Utilities attempted to
bring SoGen mobile. The mobile application lacked a good
drawing tool to allow staff to update maps while in the field and
encountered critical data discrepancies.
• In 2019, staff piloted a mobile workforce management system
(SMW) through Smart Energy Systems for both WGW and
22
Recommendation Responsible
Department(s)
Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action
Plan
Electric Operations in an effort to provide a mobile platform for
some of the work groups.
• In late 2020, the Wastewater maintenance group implemented a
new maintenance application through SEDARU which provides
a mobile platform.
• Utilities kicked off the Advanced Metering Infrastructure project
in FY 2022, which is a multi-year project ending in 2025. Utilities
will evaluate purchasing a 3rd-party mobile application used by
the vendor installing the meters and integrating with SAP. Staff
is currently working with the vendor to explore and design the
product for use post-implementation.
• The Utilities is currently reviewing and implementing mobile
applications through ArcGIS Enterprise with Esri.
Any design changes should be approved by an engineer or supervisor.
Changes in the field are a safety issue for service and could impact
other areas that the field crew may not be aware of. All approvals
should be evidenced in writing.
Utilities Concurrence: Partially Agree
Target Date: Complete (June 2022)
Action Plan: As the auditor noted, it is the City’s current practice to send
back major design changes to Engineering for approval. The Leads on-
site are trained and empowered to use independent judgment in the field
for minor field changes that did not require engineering such as, moving
the service alignment a couple of feet to accommodate field conditions
when necessary. These changes are documented on the redlined as-
builts drawings or work orders, signed by the Leads or Supervisors, then
sent to mapping to capture the accurate alignment of the new services.
These minor changes to slightly move the alignment of new services do
not create any safety concerns to the utility infrastructure, because all
installation work must comply with Building Code and Utility Standards.
We recommend that the utilities perform a full system reconciliation of
assets in AME and SAP to ensure assets are accurately recorded.
Assets in AME and SAP should continue to be reconciled on an annual
basis (or cycle counts can be performed monthly where a certain type
of asset is counted each month). This reconciliation should be
documented and signed-off on.
Utilities & ASD Concurrence: Agree
Target Date: June 2023 (estimated)
Action Plan: Utilities will prepare a report by fund and asset for
Accounting to perform a full system reconciliation between AME and
SAP to ensure assets are accurately recorded. Since there are over
16,000 records and 7,000,000 assets, the system reconciliation effort will
require at least one staff member dedicated to this project for a year. The
tasks may include troubleshooting discrepancies, documenting business
processes, and facilitating workshops between ASD and Utilities. Due to
23
Recommendation Responsible
Department(s)
Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action
Plan
staffing shortages in Accounting and various financial deadlines and
projects, early estimate for completing the reconciliation is one year.
To determine the amount of time and effort necessary to completely
reconcile all records and assets, Accounting will reconcile one fund first
and provide a better target completion date at the next update.
All reconciliations should be performed on a monthly basis to ensure
monthly financials are accurate. All reconciliations should be reviewed
for accuracy by another individual. This review should be evidenced in
writing.
In addition, Palo Alto should consider using more formulas in the
reconciliation to reduce the risk of errors that can be caused by
manually entering in numbers.
ASD Concurrence: Agree
Target Date: Complete (June 2022)
Action Plan: Settlement of costs, closing of work orders and
reconciliation of Construction in Progress are done on a monthly basis.
The reconciliation reports are saved on the network at quarter ending
since capitalization are done on the same period.
The Senior Accountant will review reconciliation performed. Accounting
has created a template to incorporate use of formulas to conduct
reconciliation. In certain cells where a copy and paste from SAP is
possible, no formula will be utilized.
We agree that the electric utility would benefit from developing similar
KPIs that the water, wastewater, and gas utilities currently use. In
addition, the utilities may want to consider adding additional KPIs
related to work orders and project management. Example KPIs that the
City may want to begin tracking are shown in Appendix D.
Utilities
Concurrence: Agree
Target Date: 12/31/2022
Action Plan: Electric Operations has a few KPI’s mapped out and are
actively working on developing the dashboards. Staff anticipates
completing them by the end of the year.
Electric Operations will incorporate the following KPIs by the end of the
year:
Maintenance Work
New Construction/CIP Work
Customer Service Work
Inspections
Emergency Work/3rd Party Damages