HomeMy WebLinkAbout2018-10-23 Policy & Services Committee Agenda PacketPolicy and Services Committee
1
MATERIALS RELATED TO AN ITEM ON THIS AGENDA SUBMITTED TO THE CITY COUNCIL AFTER DISTRIBUTION OF THE AGENDA
PACKET ARE AVAILABLE FOR PUBLIC INSPECTION IN THE CITY CLERK’S OFFICE AT PALO ALTO CITY HALL, 250 HAMILTON AVE.
DURING NORMAL BUSINESS HOURS.
Tuesday, October 23, 2018
Special Meeting
Community Meeting Room
6:00 PM
Agenda posted according to PAMC Section 2.04.070. Supporting materials are available in
the Council Chambers on the Thursday 12 days preceding the meeting.
PUBLIC COMMENT
Members of the public may speak to agendized items. If you wish to address the Committee on any issue that is on this agenda, please complete a speaker request card located on the table at the entrance to the Council
Chambers/Community Meeting Room, and deliver it to the Clerk prior to discussion of the item. You are not required to give your name on the speaker card in order to speak to the Committee, but it is very helpful. Public comment may be addressed to the full Policy and Services Committee via email at City.Council@cityofpaloalto.org.
Call to Order
Oral Communications
Members of the public may speak to any item NOT on the agenda.
Action Items
1. Policy and Services Committee Recommends the City Council Accept
the Status Updates of the Audits of the Citywide Cash Handling and
Travel Expense; Cable Franchise and Public, Education and
Government (PEG) Fees; Continuous Monitoring: Payments Audit;
Utility Meters; and Inventory Management
2. Policy and Services Committee Recommends the City Council Accept
the Status Update of the 2016 Disability and Workers Compensation
Rates Audit
3. Policy and Services Committee Recommends the City Council Accept
the ERP Planning: Data Standardization Audit
4. Policy and Services Committee Recommends the City Council Accept
the ERP Planning: Separation of Duties Audit
5. Policy and Services Committee Recommends the City Council Accept
the Auditor's Office Quarterly Report as of September 30, 2018
2 October 23, 2018
MATERIALS RELATED TO AN ITEM ON THIS AGENDA SUBMITTED TO THE CITY COUNCIL AFTER DISTRIBUTION OF THE AGENDA
PACKET ARE AVAILABLE FOR PUBLIC INSPECTION IN THE CITY CLERK’S OFFICE AT PALO ALTO CITY HALL, 250 HAMILTON AVE.
DURING NORMAL BUSINESS HOURS.
Future Meetings and Agendas
Adjournment
AMERICANS WITH DISABILITY ACT (ADA) Persons with disabilities who require auxiliary aids or services in using City facilities, services or programs or who would like information on the City’s compliance with the Americans with Disabilities Act (ADA) of 1990, may
contact (650) 329-2550 (Voice) 24 hours in advance.
City of Palo Alto (ID # 9565)
Policy and Services Committee Staff Report
Report Type: Action Items Meeting Date: 10/23/2018
City of Palo Alto Page 1
Summary Title: Status Updates of Audit Recommendations for Cable, Cash
Handling and Continous Monitoring Audits
Title: Recommendation that Policy and Services Committee Recommends the
City Council Accept the Status Updates of the Audits of the Citywide Cash
Handling and Travel Expense; Cable Franchise and Public, Education and
Government (PEG) Fees; Continuous Monitoring: Payments Audit; Utility
Meters; and Inventory Management
From: City Manager
Lead Department: Administrative Services
Recommendation
Staff recommends that the Policy and Services Committee recommend that the City Council
accept the status updates of the audits of Citywide Cash Handling and Travel Expense; Cable
Franchise and Public, Education and Government (PEG) Fees; Continuous Monitoring: Payments
Audit; Utility Meters; and Inventory Management.
Background
The City Auditor’s Office previously issued audits of Citywide Cash Handling and Travel Expense;
Cable Franchise and Public, Education and Government (PEG) Fees; Continuous Monitoring:
Payments Audit; Utility Meters; and Inventory Management.
Staff has provided status updates on the audits as Attachments A through B. Of the remaining
recommendations three are complete and thirteen remain open. The open recommendations
include two that are linked to the future implementation of a new enterprise resource planning
(ERP) system. The implementation of a new inventory tracking system in late 2018 will address
three recommendations in the utility meters audit. In the Cable PEG fees audit, two
recommendations are linked to the ongoing discussion between the City and the Media Center
about the idea of using PEG fees to purchase the Media Center building. Details of all
recommendations can be found in the attachments.
Attachments:
• Attachment A: Cash Handling Audit
• Attachment B: Cable PEG Fees Audit
City of Palo Alto Page 2
• Attachment C: AP Audit
• Attachment D: Utility Meters Audit
• Attachment E: Inventory Management Audit
Attachment A
STATUS OF AUDIT RECOMMENDATIONS
CITYWIDE CASH HANDLING AND TRAVEL EXPENSE – ISSUED 9/15/10
PAGE 1
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report progress on
implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been implemented.
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
Finding 1: Stronger controls are needed for cash handling
Finding 2: Increased oversight and coordination can improve the employee travel expense process.
6. ASD should review the nighttime
meeting reimbursement policy. If the City
decides to maintain this practice, ASD
should report the amounts as income on
employee Form W-2s to conform to
Internal Revenue Service requirements.
In addition, ASD should review other
types of meal expense to ensure any
reportable amounts are included on
employee Form W-2s.
Auditor’s Note: The City Auditor and
Administrative Services Department staff
met to discuss using federal per diem
rates, which is a best practice, instead of
requiring employees to provide meal
receipts when traveling.
Administrative
Services
Department
Target Date Not Provided
ASD has determined that handling
such reimbursements through payroll
would involve significant staff time.
Staff is developing a process that will
comply with the IRS regulation in the
most economical and efficient
fashion. Any change in
reimbursements would be subject to
meet-and-confer depending on the
labor group.
In Process September 2018 Management Update:
Staff has developed a form and draft policy for
tracking meal expenses on employee W2s. Staff is
reviewing the form and policy with departments.
Expected Completion Date: 1QTR 2019
March 2018 Management Update:
Staff has developed a form and draft policy for
tracking meal expenses on employee W2s. Staff is
reviewing the form and policy with departments.
The new expected due date for capturing meals
expenses on W2s is 12/31/2018.
April 2017 Management Update:
Staff is planning to include taxable meals on
employee W2s by the end of 2017.
Expected Completion Date: 12/31/2017
October 2015 Management Update: ASD, working
with the City Auditor and the City Manager’s Office,
and has completed the first phase changes to the
meal reimbursement policy. Staff changed the
reimbursement for travel meals to the IRS per diem
limits, which do not require reporting on an
employee’s W2.
June 2014 Management Update: ASD staff is
Attachment A
STATUS OF AUDIT RECOMMENDATIONS
CITYWIDE CASH HANDLING AND TRAVEL EXPENSE – ISSUED 9/15/10
PAGE 2
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
reviewing process changes coupled with search and
reporting capabilities in the purchase card system
that could make it feasible for the Accounts Payable
and Payroll processes to sync up so that all taxable
meal reimbursements would be included on
employee paychecks to ensure proper handling of
taxable meal pay to employees. Expected
Completion Date: 4/1/15
Prior Years’ Management Updates (summarized):
ASD updated the travel policy, petty cash policy, and
reimbursement form to ensure proper coding of
meals. ASD also established a new general ledger
account to capture taxable meals for inclusion on
employee W-2 forms as compensation. It takes
considerable staff time to track and record these
taxable meals such as meals provided during one-
day training and meals provided to employees
during overtime. Given the small number of
incidents and the low dollar amounts, probably in
the few thousand dollars citywide in a given year,
staff is looking at phasing out these types of meals.
A further complication is that meals are sometimes
purchased with a P-card and may be for several
staff. There is currently no easy way to assign these
charges to the appropriate person receiving the
meal.
ATTACHMENT B
STATUS OF AUDIT RECOMMENDATIONS
CABLE FRANCHISE AND PEG FEE AUDIT – ISSUED 6/14/16
PAGE 1
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
Finding 1: The Media Center did not restrict its use of $340,000 of annual PEG fees to capital expenditures as required by the federal Cable Act.
We recommend that the City Manager’s Office:
1.1 Consult with ASD, IT, the City
Attorney’s Office, and Cable Joint
Powers members to assess the need
to continue collecting PEG fees and
adjust the fee based on a
demonstrated need for future capital
expenses related to PEG access
facilities or discontinue collecting the
fee.
a. If it is determined that the PEG fee
should be adjusted or
discontinued, submit a staff report
to the City Council with a
recommendation to amend the
Municipal Code to reflect the
revised fee or to eliminate the
requirement and recommend to
the other Cable Joint Powers
members that they do the same.
b. If it is determined that the PEG fee
should continue to be collected:
• Amend the agreement with
the Media Center to remove
the requirement for the City to
remit all PEG fees collected to
the Media Center.
City Manager’s
Office, ASD, IT,
City Attorney’s
Office
Concurrence: Agree
Target Date: 2017
Action Plan:
Staff agrees that it should confirm the
ongoing need for the PEG fee and
ensure it is set at a level that is
consistent with future capital needs.
Staff will work with the City Attorney’s
Office to develop a “capital cost”
definition that eliminates any cost
categories that could be construed as
operating costs and will restrict the use
of PEG fees to expenditures that meet
this definition. Staff will also develop
and adopt procedures that define the
PEG fee distribution and reporting
process.
Staff will propose the appropriate
revisions to the Municipal Code if it is
determined that the PEG fee should be
modified in any way.
In Progress September 2018 Management Update:
The Cable Joint Powers is evaluating the possible
purchase the Media Center building over time
using PEG fees, in order to dedicate the building to
future PEG use and to maximize the benefit of PEG
fee revenue for PEG capital costs. The City is
discussing the terms of the building purchase
agreement with the Media Center. In the
meantime, PEG fees are being placed in a
restricted account only to be used for capital
expenditures that meet Federal Cable Act
requirements. Staff expects to provide an update
on the status of the possible purchase of the
Media Center building to the City Council before
the end of 2018.
Expected Completion Date: 1QTR 2019
March 2018 Management Update:
Staff is working with the Cable Joint Powers and
the Media Center to confirm the ongoing need for
PEG fees. It is anticipated that PEG fee revenue
will continue to be needed for appropriate capital
equipment and building expenses. Effective 2016,
PEG fees have been placed in a restricted account
only to be used for capital expenditures that meet
Federal Cable Act requirements. Procedures that
ATTACHMENT B
STATUS OF AUDIT RECOMMENDATIONS
CABLE FRANCHISE AND PEG FEE AUDIT – ISSUED 6/14/16
PAGE 2
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
• Coordinate with ASD, the City
Attorney’s Office, and the
Cable Joint Powers to develop
and implement criteria for the
use of PEG fees to ensure
compliance with the federal
Cable Act, and that the fees
are set at a level appropriate
for anticipated and necessary
capital expenses.
• Place the PEG fees in a
restricted account and
distribute them based on City-
approved capital expenditures
that meet federal Cable Act
requirements.
• Require that semi-annual
documentation of
expenditures be provided and
adopt procedures to review
the documentation to ensure
that PEG fees are spent only as
allowed by the federal Cable
Act and take immediate
corrective action as necessary.
define capital assets, and the PEG fee distribution
and reporting process are under development.
Expected Completion Date: 3QTR 2018
1.2 Consult with ASD, IT, the City
Attorney’s Office, and the Cable Joint
Powers on whether to allocate a
portion of the unrestricted franchise
fees or other funds, instead of
restricted-use PEG fees, to subsidize
the Media Center’s operations or to
discontinue subsidizing the Media
City Manager’s
Office, ASD, IT,
City Attorney’s
Office
Concurrence: Agree
Target Date: 2017
Action Plan:
Staff will consult with the Cable Joint
Powers to determine if there is any
interest in subsidizing the Media
Center’s operations. Staff will propose
In Progress September 2018 Management Update:
The Cable Joint Powers prefers not to subsidize the
Media Center operations with franchise fees or
other funds. Instead, the City is pursuing the
option of using PEG fees to purchase the Media
Center building, thus providing the Media Center
with funds to cover its operating costs.
ATTACHMENT B
STATUS OF AUDIT RECOMMENDATIONS
CABLE FRANCHISE AND PEG FEE AUDIT – ISSUED 6/14/16
PAGE 3
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
Center’s operations. Based on the
resulting recommendation, the City
Manager’s Office should make
recommendations to the Council
regarding appropriate future funding,
if any, for the Media Center.
recommendations to the City Council if
needed.
Expected Completion Date: 1QTR 2019
March 2018 Management Update:
The City is exploring with the Media Center a
proposal for the use of PEG fees to purchase the
Media Center’s facility. Under this option, the JPA
would use PEG fees for capital, enabling the Media
Center to cover operational expenses. (Staff will
report back to Council on the merits of this option
in the spring/summer 2018 timeframe.) The Cable
Joint Powers favors this option over using
franchise fees or other funds to subsidize the
Media Center’s operations.
Expected Completion Date: 4QTR 2018
Finding 2: Comcast and AT&T did not remit the full amount of franchise and PEG fees due.
We recommend that the City Manager’s Office, in coordination with ASD, IT, and the City Attorney’s Office:
2.1 Send a letter to AT&T and Comcast
describing the results of the audit and
demanding payment of the
underpaid franchise and PEG fees
shown in Exhibit 4, plus interest
calculated in accordance with DIVCA
requirements.
City Manager,
ASD, IT, City
Attorney’s
Office
Concurrence: Agree
Target Date: 4Q 2016
Action Plan:
Staff will draft a letter to Comcast/AT&T
demanding payment of the underpaid
franchise and PEG fees, plus interest
(and audit costs in the case of AT&T).
Staff will work with Comcast/AT&T to
correct their address databases so that
future payments are properly remitted
and will develop criteria to assess the
accuracy of future payments. Staff will
work with San Mateo and Santa Clara
Counties to adjust their PEG fee rates as
needed.
In Progress September 2018 Management Update:
Staff has reached a tentative agreement with
Comcast on the terms of a global settlement. Staff
obtained Council approval for the financial portion
of the settlement. Staff is working with the City
Attorney’s Office to finalize the Comcast
settlement agreement.
Expected Completion Date: 4QTR 2018
March 2018 Management Update: In Progress
Staff issued letters to Comcast/AT&T demanding
payment of the underpaid franchise and PEG fees,
plus interest. Staff has reached a global settlement
with AT&T in the amount of $75,647. Staff
continues to negotiate the terms of a global
ATTACHMENT B
STATUS OF AUDIT RECOMMENDATIONS
CABLE FRANCHISE AND PEG FEE AUDIT – ISSUED 6/14/16
PAGE 4
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
settlement with Comcast.
Expected Completion Date: 2QTR 2018
2.4 Develop criteria for assessing the
accuracy of future Comcast and AT&T
franchise and PEG fee payments on an
ongoing basis and:
• Communicate the criteria to
Comcast and AT&T and that it will
be used to review the accuracy of
future payments.
• Require Comcast and AT&T to
report the breakdown of their
fees in more detail, including
identifying what is and is not
included in the gross revenues
used to calculate the fees and the
reason for any exclusions.
• Review the franchise and PEG fee
payments to ensure that they
were calculated on all revenues
that are subject to franchise and
PEG fees and promptly follow up
with Comcast and AT&T regarding
any discrepancies.
In Progress September 2018 Management Update:
Staff has attempted to get Comcast and AT&T to
provide more detailed reporting formats to
improve the monitoring of future franchise and
PEG fee payments. The cable companies use
standard report formats for all their customers and
are not willing to provide a unique set of reports
for the Cable Joint Powers. Staff is working to
develop other ways to assess the accuracy of
future franchise and PEG fee payments
Expected Completion Date: 4QTR 2018
March 2018 Management Update:
After staff reaches a settlement with Comcast,
(determining what is and what is not included in
gross revenues used to calculate franchise fees), it
will finalize criteria and establish a more detailed
reporting format to assess the accuracy of future
franchise and PEG fee payments.
Expected Completion Date: 3QTR 2018
Finding 3: Roles and responsibilities for managing the City’s cable communications program are not clearly defined or assigned.
We recommend that the City Clerk and City Manager’s Office:
3.1. Confer and develop a
recommendation for the City Council
to assign responsibility for the City’s
cable communications program and
City Manager’s
Office, City
Clerk
Concurrence: Agree
Target Date: 4Q 2016
Action Plan:
Staff will determine where to assign
In Progress September 2018 Management Update:
Responsibility for the City’s cable communications
program was transferred to the Administrative
Service Department on an interim basis. After staff
ATTACHMENT B
STATUS OF AUDIT RECOMMENDATIONS
CABLE FRANCHISE AND PEG FEE AUDIT – ISSUED 6/14/16
PAGE 5
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
require the assigned department to
provide appropriate program
oversight to ensure that:
a. The City’s cable communications
program objectives are aligned
with the City’s goals and
objectives.
b. The assigned department
develops performance measures
to demonstrate that the program
is effective and is meeting the
City’s goals and objectives.
c. There is effective oversight and
management of the cable
coordinator’s contract and
activities.
responsibility for the City’s cable
communications program/activities and
propose the appropriate revisions to the
Municipal Code. The responsible
department will establish performance
measures to ensure proper program
administration and oversight.
completes the implementation of the audit
findings, it will determine where to assign final
responsibility for the program.
Expected Completion Date: 1QTR 2019
March 2018 Management Update:
Staff is evaluating where to assign responsibility
for the City’s cable communications
program/activities and will propose the
appropriate revisions to the Municipal Code.
Expected Completion Date: 4QTR 2018
3.2. Submit a draft ordinance to the Palo
Alto City Council recommending
revisions to the Palo Alto Municipal
Code based on the revised
assignment of roles and
responsibilities.
Not Started September 2018 Management Update:
Staff will propose appropriate revisions to the
Municipal Code after it determines where final
responsibility for the City’s cable communications
program will reside.
Expected Completion Date: TBD
April 2017 Management Update:
Not started.
Expected Completion Date: 4QTR 2018
ATTACHMENT C
STATUS OF AUDIT RECOMMENDATIONS
CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report progress on
implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been implemented.
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status Status
Finding: 1. Implementing a continuous monitoring process can help the City identify duplicate invoice payments. The City recovered 17 (71 percent) of 24 confirmed
duplicate invoice payments.
1.1. Build a continuous monitoring
reporting process into the new ERP
system to identify potential
duplicate invoices based on
information such as vendor, date,
invoice number, and amount, and
run the report at least monthly. ASD
should review the results, seek
recovery of duplicate payments, and
identify and correct process
deficiencies that allowed the
duplicate payments to be processed.
ASD Concurrence: Agree
Target Date: TBD (date of ERP implementation)
Action Plan: ASD agrees that a continuous
monitoring reporting process should be part of
the accounts payable process. ASD and City staff
currently detects and recovers duplicate
payments through periodic account analysis,
contract monitoring and notifications from
vendors. Per the auditor’s recommendation,
ASD will develop and document an internal
control process to identify duplicates for the
new ERP system.
ASD is in the process of implementing a hard
stop in the City’s SAP system if the invoice date,
invoice number, and invoice amount are the
same. Previously, only a warning was issued and
it was possible to still enter a duplicate invoice.
This more restrictive configuration should
decrease the number of duplicate payments.
It is important to note that no system can
prevent 100% of duplicate payments. However
strong internal controls and entity -wide
coordination, can prevent most duplicates. With
technological advances and changing
Not Started
September 2018 Management Update:
See below, no new update.
Expected Completion Date:
March 2018 Management Update:
As of 12/28/17, SAP is configured to result
in a hard stop if a duplicate invoice is
entered. Previously only a warning was
given. The fields that are configured for a
hard stop are a combination of vendor
number, invoice date, reference text
(invoice number), amount and company
code.
In the upcoming demonstrations, ASD will
seek confirmation that the new ERPs can
provide similar duplicate invoice
detection.
Expected Completion Date: TBD (date of
ERP implementation)
ATTACHMENT C
STATUS OF AUDIT RECOMMENDATIONS
CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status Status
requirements we have seen an increase in
duplicate invoices arriving in Accounts Payable.
Invoices come in to Accounts Payable from
multiple sources, and while previously a best
practice, requiring original invoices is no longer
practical. Invoices are now emailed by the
vendor, sent via DocuSign, by internal
departments and sometimes also sent via U.S.
mail.
1.2. Update invoice processing policies
and procedures, and disseminate the
updated policies to appropriate City
staff, to require:
a. Unique invoice numbers on all
documents submitted for
payment.
b. Use of credit memorandums or
other accounting entries to
correct invoice errors such as
duplicate invoices.
c. Referencing of the erroneous or
duplicate invoice using a unique
identifier (e.g., invoice number)
in credit memorandum entries
in SAP.
ASD Concurrence: Partially Agree
Target Date: 12/31/17
Action Plan:
a. ASD will request invoice numbers from
vendors, however it may not be
practical to require all vendors to
provide for unique invoice numbers on
all documents submitted for payment.
Some vendors such as phone
companies do not provide invoice
numbers. To follow-up with all vendors
that do not provide an invoice number
would slow down payment and require
additional staff hours. However ASD
staff will be more proactive in working
with vendors that submit invoices
without invoice numbers. We have
created a “Master Invoice Key” to
improve consistency for non- invoice
payment requests such as employee
reimbursements, rebates and refunds,
dues, subscriptions and registration
Complete September 2018 Management Update:
Updated policy and procedures will sent
out in August 2018.
Expected Completion Date:
March 2018 Management Update:
a. ASD is being pro-active in contacting
departments to request that their vendors
use unique invoice numbers. ASD has
created a “Master Invoice Key” to improve
consistency for non-invoice payment
requests. We continually update this Key
as new patterns are detected.
b. ASD requests a credit memo from
vendors when possible. ASD has informed
departments that when items are
returned or an invoice correction is
needed, the preference is to receive a
credit memo rather than a check payment
from the vendor.
ATTACHMENT C
STATUS OF AUDIT RECOMMENDATIONS
CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status Status
fees. This should mitigate risk of
duplicate payments on these invoices.
b. ASD requests a credit memo from the
vendor, when possible. Not all vendors
are set up to issue credit memos and
sometimes a reimbursement check is
generated before we were aware of
the duplicate payment. Sometimes the
departments request that the vendor
apply the credit or duplicate payment
amount to future invoices without ASD
staff’s knowledge. ASD staff will
include in the disseminated policy and
procedures instructions to the
departments explaining the process
when/if they detect or are informed of
a duplicate payment.
c. Credit memorandums typically have
their own unique identifier. This
unique identifier often does not have
any relationship to the invoice number
on the invoice that that was paid more
than once. ASD will add instructions in
the Accounts Payable manual to
reference the duplicate payment in the
text field. However this field was not
used in the audit and therefore would
not have reduced the false positives.
c. ASD has written a procedure specifically
on how to process a credit memo. The
procedure instructs ASD and department
staff to reference the original invoice
associated with the credit memo.
Expected Completion Date: March 31,
2018
Finding: 2. Numerous unneeded vendor records increase the risk of inappropriate and erroneous payments and payment records, as well as incorrect tax reporting.
2.1. Update its policies and procedures
to provide clear guidance regarding:
ASD Concurrence: Agree
Target Date: 12/31/17
Complete September 2018 Management Update:
Procedures have been updated.
Expected Completion Date:
ATTACHMENT C
STATUS OF AUDIT RECOMMENDATIONS
CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status Status
• Information needed to create
complete and accurate vendor
master records.
• Not to create a new vendor
record when one already exists
for a vendor or its parent or
subsidiary companies unless, on
an exception basis, there is a
documented business need that
cannot be met (e.g., tracking
payments and creating payments
for a vendor with multiple
taxpayer identification
numbers).
• A coding standard for entering
vendor information that includes
guidance on punctuation,
capitalization, spacing,
abbreviation, special characters,
and other potential variables in
formatting identifying
information in order to prevent
duplicate records. This change
should be incorporated in the
new ERP system.
Action Plan: ASD will update policies and
procedures to provide information needed to
create complete and accurate vendor master
records. In some cases, for business needs,
duplicate vendor records are needed in the
current configuration of SAP to allow for
different payment addresses, for instance.
As part of the new ERP system City staff will
clean-up and establish new vendors for a fresh
start with the new ERP vendor database.
March 2018 Management Update:
New standards have been rolled out to
standardize vendor creation and to
prevent the creation of duplicate vendors.
Staff is in the process of finalizing these
standards and incorporating them into the
formal procedures.
2.2. Build a continuous monitoring
process into the new ERP system to:
• Review the vendor master file at
least annually to identify
duplicate, incomplete, or unused
vendor records (i.e., vendor
ASD Concurrence: Agree
Target Date: TBD (date of ERP implementation)
Action Plan: ASD agrees that a continuous
monitoring process should be built into the new
ERP system.
Not Started September 2018 Management Update:
See below, no new update.
Expected Completion Date:
March 2018 Management Update:
ATTACHMENT C
STATUS OF AUDIT RECOMMENDATIONS
CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17
Recommendation
Responsible
Department(s) Original Target Date and Response
Current
Status Status
records not used during a time
frame determined by ASD).
• Inactivate duplicate vendor
records, enter missing identifying
information based on reliable
source documents such as a
vendor-provided IRS Form W9,
and inactivate or archive unused
vendor records.
When the new ERP is implemented ASD will
prepare a plan to review the vendor master file
at least annually and inactivate unused,
incomplete or inactive vendors.
Part of the annual review of the master vendor
file will also entail identifying and deleting
duplicate vendors. In addition, staff will also
update the missing vendor record using
information from sources mentioned in the
recommendation. ASD staff will also work with
ERP Team to explore other options to
accommodate different “Remit To” addresses
without creating a new vendor number.
To be implemented with new ERP. Prior to
implementation, ASD will prepare a plan
to review the vendor master file at least
annually and inactivate unused,
incomplete or inactive vendors.
Expected Completion Date: TBD (date of
ERP implementation)
2.3. Clean the City’s vendor master file in
accordance with recommendations
2.1 and 2.2 before merging the data
into the City’s proposed new ERP
system.
ASD Concurrence: Agree
Target Date: TBD with adoption of new ERP
system
Action Plan: In order to provide consistency,
ASD intends to begin from scratch with the
Master Vendor File when the City adopts a new
ERP.
In Progress September 2018 Management Update:
See below, no new update.
Expected Completion Date:
March 2018 Management Update:
Prior to the conversion to a new ERP, ASD
will identify the vendors to input into the
new system. ASD will also create a coding
standard for data entry to be used by
Purchasing and Accounts Payable.
Expected Completion Date: Prior to ERP
implementation
ATTACHMENT D
STATUS OF AUDIT RECOMMENDATIONS
UTILITY METERS: PROCUREMENT, INVENTORY, AND RETIREMENT – ISSUED 3/10/15
PAGE 1
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department Original Target Date and Response Current Status
Implementation Update and
Expected Completion Date
Finding 2: The Utilities Department has incomplete, incorrect, and inconsistent meter records, which causes data reliability concerns and increases the risk for
incorrect customer billing.
2.1 The Purchasing Division should
correct the purchase order
documents to accurately reflect the
engineering specifications.
Remaining
Department
Responsible:
ASD
Concurrence: Agree
Target Date: Completed
Action Plan: The Purchasing Division has
updated the purchase order documents to
accurately reflect the current engineering
specifications.
Complete September 2018 Management Update:
Staff has updated all active meter records
with the current specification.
Expected Completion Date:
October 2017 Management Update:
Purchasing Division has updated most of the
material master records and purchasing
orders to reflect current engineering
specifications. Remaining updates will be
completed by November 2017.
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 1
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
Finding 1: ASD and City departments should implement inventory management policies and procedures citywide to achieve the City’s inventory goals and
objectives.
1. ASD and City departments
should implement the City’s
inventory management policies
and procedures citywide to
achieve inventory goals and
objectives.
Administrative
Services (lead)
Remaining
Department
Responsible:
Utilities
6/30/14
Citywide policies and procedures
will be reviewed, updated, and
shared with all departments cited in
this audit. Inventory goals and
objectives will be stressed and
implemented where it is cost-
effective to do so.
In Progress September 2018 Management Update:
Utilities awarded the inventory tracking system to Smart Energy
Water (SEW) in September 2017. SEW will deploy their Smart
Mobile Workforce application which includes inventory
management tracking and reporting, dynamic inspection forms
with backend integration, and mobile SAP work orders. SEW is
in the final configuration phase and the tentative go-live date is
Q4 2018. With Smart Mobile Workforce, Utilities will have real-
time inventory counts in various locations, real-time work
orders, and elimination of duplicate entry for inspection forms.
Expected Completion Date: November 2018
October 2017 Management Update:
ASD conducted the follow-up outlined in the October 7, 2014
Staff Report and the attached Policy, Process & Procedures
Document.
Recommendation completed for ASD, Public Works, and IT.
Utilities has requested that an exception be added in the policies
and procedures governing items valued at less than $100 stored
outside the warehouse (i.e. sheds, North Dock, substations).
These consumables are excluded from physical inventory counts
such as nuts, bolts, washers, pipe fittings, gloves, hard hats, and
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 2
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
small tools. Most of these items have high turnover and are
purchased in bulk rather than individual units. The time and cost
involved in recording these materials will greatly exceed the
value of the materials.
Utilities is working with Stores on the return process for unused
materials after completion of a project.
Utilities will be piloting an inventory tracking system with
barcode scanning capability to track and monitor materials
valued at greater than $100 stored outside the warehouse.
Expected Completion Date: June 2018
June 2014 Management Update: When the audit was presented
to the Finance Committee in February 2014, the Finance
Committee requested that staff return in 6 months (August
2014) to provide a status update. Due to various scheduling
conflicts, the meeting has been moved from August 19 to
October 7, at which time the Finance Committee will hear staff’s
responses to and the implementation plan for the Auditor’s
findings and recommendations. Since the Auditor’s
Recommendation Status update report ends June 30, 2014, staff
will provide status responses to the audit in the FY 2015
Recommendation Status update report. Expected Completion
Date: 10/31/14
Finding 2: ASD should improve controls to ensure the accuracy of recorded inventory.
3. ASD should update and
enforce inventory count policies
and procedures to help ensure
consistent and accurate
inventory records. The update
should at minimum require
blind inventory counts,
sufficient documentation of
Administrative
Services
Remaining
Department
Responsible:
Utilities
6/30/14
• ASD has implemented blind
inventory counts and appropriate
segregation of duties at the MSC
warehouse and will continue to
improve. Limited staffing at the
In Progress September 2018 Management Update:
Utilities awarded the inventory tracking system to Smart Energy
Water (SEW) in September 2017. SEW will deploy their Smart
Mobile Workforce application which includes inventory
management tracking and reporting, dynamic inspection forms
with backend integration, and mobile SAP work orders. SEW is
in the final configuration phase and the tentative go-live date is
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 3
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
counts and adjustments, and
appropriate segregation of
duties. ASD should consider
implementing controls included
in the GAO publication titled
“Executive Guide Best Practices
in Achieving Consistent,
Accurate Physical Counts of
Inventory and Related
Property.”
MSC does make segregation of
duties challenging.
• To achieve greater accuracy in
counts at the RWQCP warehouse
as well as segregation of duties,
additional staffing is necessary.
Staff from Public Works and ASD
propose to evaluate the costs and
benefits of the recommendation
and report back to Council.
• Staff will strive to implement
controls cited in GAO publication
and inform Council of its progress.
Q4 2018. With Smart Mobile Workforce, Utilities will have real-
time inventory counts in various locations, real-time work
orders, and elimination of duplicate entry for inspection forms
Expected Completion Date: November 2018
October 2017 Management Update:
ASD has implemented the GAO’s best practices in the
warehouse. ASD is conducting blind inventory counts at the
warehouse. ASD conducts a full inventory count at fiscal year-
end to ensure accuracy and accountability of inventory on hand.
Constant SAP auto-generated inventory cycle counts are
performed to maintain an accurate inventory.
Utilities is conducting semi-annual inventory counts of materials
valued over $100 and emergency parts stored in MSC sheds,
North Dock and substations.
Utilities will be piloting an inventory tracking system with
barcode scanning capability to track and monitor materials
outside the warehouse.
Expected Completion Date: June 2018
June 2014 Management Update: When the audit was presented
to the Finance Committee in February 2014, the Finance
Committee requested that staff return in 6 months (August
2014) to provide a status update. Due to various scheduling
conflicts, the meeting has been moved from August 19 to
October 7, at which time the Finance Committee will hear staff’s
responses to and the implementation plan for the Auditor’s
findings and recommendations. Since the Auditor’s
Recommendation Status update report ends June 30, 2014, staff
will provide status responses to the audit in the FY 2015
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 4
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
Recommendation Status update report. Expected Completion
Date: 10/31/14; All policies and procedures and responsibilities
for this recommendation are in place; will go live on 10/31/14
after Finance Committee review
Finding 4: The City’s warehouses have significant quantities of unused or infrequently used inventory.
6. ASD should identify,
formalize, and communicate
inventory management goals
and objectives to City
departments.
Administrative
Services
Remaining
Department
Responsible:
Utilities
6/30/14
Once current policies and
procedures are reviewed and
updated and inventory
management goals and objectives
are reaffirmed, they will be
discussed and left with
departments.
In Progress September 2018 Management Update:
Utilities awarded the inventory tracking system to Smart Energy
Water (SEW) in September 2017. SEW will deploy their Smart
Mobile Workforce application which includes inventory
management tracking and reporting, dynamic inspection forms
with backend integration, and mobile SAP work orders. SEW is
in the final configuration phase and the tentative go-live date is
Q4 2018. With Smart Mobile Workforce, Utilities will have real-
time inventory counts in various locations, real-time work
orders, and elimination of duplicate entry for inspection forms.
Expected Completion Date: November 2018
October 2017 Management Update:
Recommendation completed for ASD, Public Works, and IT.
Policies and procedures were developed with and approved by
departments. They include a definition of the goals and
objectives of those policies and procedures. The procedures
incorporate internal controls that ensure the responsible use of
public funds and efficiency of operations, including the
safeguarding of assets, the reliability and completeness of
information reporting, and compliance with laws and
regulations.
Utilities staff routinely monitors inventory to manage
appropriate classification of maintenance, emergency and
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 5
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
obsolete parts/materials. Inventory minimum and maximum
levels are reviewed at least semi annually.
Utilities will be piloting an inventory tracking system with
barcode scanning capability to track and monitor materials
outside the warehouse.
Expected Completion Date: June 2018
June 2014 Management Update: When the audit was presented
to the Finance Committee in February 2014, the Finance
Committee requested that staff return in 6 months (August
2014) to provide a status update. Due to various scheduling
conflicts, the meeting has been moved from August 19 to
October 7, at which time the Finance Committee will hear staff’s
responses to and the implementation plan for the Auditor’s
findings and recommendations. Since the Auditor’s
Recommendation Status update report ends June 30, 2014, staff
will provide status responses to the audit in the FY 2015
Recommendation Status update report. Expected Completion
Date: 10/31/14; All policies and procedures and responsibilities
for this recommendation are in place; will go live on 10/31/14
after Finance Committee review
8. ASD should consult with the
IT Department and other City
departments to ensure staff:
• Identifies and uses key SAP
inventory management
reports.
• Appropriately configures and
updates SAP parameters
affecting inventory levels.
Administrative
Services
3/31/14
• ASD has worked and will continue
to work with SAP staff to use and
develop SAP inventory
management reports. A list of
these reports will be included by
Target Date.
• Parameters affecting inventory
levels will be explored and
updated as needed.
In Progress September 2018 Management Update:
Staff is in the process of identifying, reviewing and
updating these categories for all materials.
Expected Completion Date: 4QTR 2018
October 2017 Management Update:
The warehouse provides a customized SAP “ZMMR05”
report semi-annually of slow-moving goods to the
respective departments. The report contains material lead
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 6
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
time, stock quantity, minimum and maximum stock
threshold, year to date usage and average monthly life
usage.
This report assists the Supervisors with determining
whether the minimum and maximum needs to be
adjusted, whether a part should be classified as
“emergency” because it is slow moving or whether a part
should be removed from stock entirely.
Materials should be identified as one of the following
categories: “Emergency”, “Maintenance”, “Delete” or
“Obsolete”. “Delete” is defined as a material that no
longer exists in the system, whereas “Obsolete” is a
material being phased out. Staff is in the process of
identifying, reviewing and updating these categories for all
materials.
Expected Completion Date: January 2018
June 2014 Management Update: When the audit was presented
to the Finance Committee in February 2014, the Finance
Committee requested that staff return in 6 months (August
2014) to provide a status update. Due to various scheduling
conflicts, the meeting has been moved from August 19 to
October 7, at which time the Finance Committee will hear staff’s
responses to and the implementation plan for the Auditor’s
findings and recommendations. Since the Auditor’s
Recommendation Status update report ends June 30, 2014, staff
will provide status responses to the audit in the FY 2015
Recommendation Status update report. Expected Completion
ATTACHMENT E
STATUS OF AUDIT RECOMMENDATIONS
INVENTORY MANAGEMENT – ISSUED 12/31/13
PAGE 7
Recommendation
Responsible
Department
Original Target Date and
Response
Current
Status
Implementation Update and
Expected Completion Date
Date: 10/31/14; All policies and procedures and responsibilities
for this recommendation are in place; will go live on 10/31/14
after Finance Committee review
City of Palo Alto (ID # 9669)
Policy and Services Committee Staff Report
Report Type: Action Items Meeting Date: 10/23/2018
City of Palo Alto Page 1
Summary Title: Status Update of Audit for Disability and Work Compensation
Rates
Title: Policy and Services Committee Recommends the City Council Accept
the Status Update of the 2016 Disability and Workers Compensation Rates
Audit
From: City Manager
Lead Department: Human Resources
Recommendation
Staff recommends that Policy and Services Committee recommend that the City Council accept
the attached Status Update of Audit Recommendations for the 2016 Disability Rates and
Workers’ Compensation Audit.
Background
The City Auditor’s Office issued an audit with an objective to assess the effectiveness of
activities to manage and minimize disability retirements and workers’ compensation claims.
This audit included the review of processes to ensure employee safety, tracking and reporting
activities, contract administration, and efficiency of claim processing.
As a result of the audit, procedures and processes to improve claim monitoring have been
implemented and there is new focus on timeliness of claim reporting and improved and
streamlined recordkeeping where possible. Training for supervisors on how to enter claims into
third-party administrator portal has been completed to provide streamlined claim filing. This is
a critical first step necessary to provide an injured employee with medical treatment as soon as
possible.
Staff has continued to work on addressing and completing audit recommendations. Staff last
reported on audit status to the Policy and Services Committee in February 2018. At that point,
seven recommendations identified in the audit had been completed.
Discussion
City of Palo Alto Page 2
The Human Resources (HR) Department has now completed 10 of the 15 recommendations.
The recommendations and actions taken for completion are listed in the attached document
(Attachment A).
The attached report also provides an update of the status of the recommendations. HR is in the
process of coordinating employee safety training to minimize strain injuries, implementing
supervisor training to improve investigations and injury prevention, as well as working with
third-party administrator (York Risk Services) to ensure correct data. In addition, HR team will
be working with the Administrative Services Department SAP Functional and Payroll teams to
test and implement the updated CalPERS report, which includes correcting disability coding.
Staff expects to have all recommendations completed by June 2019.
Attachments:
Attachment - Status of WC Audit Recommendation 2018_final P and S Oct 23
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 1
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
Finding 1: The City’s Injury and Illness Prevention Program (IIPP) is comprehensive but has lost its effectiveness due to the loss of its City Safety Officer.
1.2. HR update the safety manual,
including supplemental tools and
guidance posted on the intranet,
to ensure:
•The roles and responsibilities
over the IIPP are redefined.
•The City’s IIPP complies with all
Cal/OSHA standards and other
applicable safety laws.
Human
Resources
Concurrence: Agree
Target Date: March 2017
Action Plan: Once appropriate staff
resources are in place, the roles and
responsibilities can be reestablished to
ensure the safety manual (IIPP) is reviewed
and updated as necessary.
While a few safe work practices have been
updated, such as the Heat Stress Prevention
Guidelines in July 2014, a thorough review
will be conducted and any necessary
updates will be completed.
Completed September 2018 Management Update:
All safe work practices have been reviewed and
updated. Link to Heat Illness Prevention videos in
English and Spanish are now posted on the
intranet. Manuals and checklists on the intranet
are now in working order.
January 2018 Management Update:
Updates have been posted to HR Risk
Management and Safety website, specifically
targeting Ergonomic awareness. Review of all
other safe work practices will continue.
Expected Completion Date: June 2018
1.3. HR review departmental
procedures and safety
requirements to ensure they align
with the revised IIPP and City
policy and procedures.
Human
Resources
Concurrence: Agree
Target Date: December 15, 2016
Action Plan: HR is in process of securing a
safety specialist to conduct periodic
inspections of City facilities for hazard
assessment. This comprehensive evaluation
will include determining if any corrections
need to be completed, including updating or
implementing work procedures.
In Progress September 2018 Management Update:
Supervisor safety training presentation has been
identified to train supervisors on effective injury
and illness prevention strategies and conducting
accident investigations.
Expected Completion Date: January 31, 2019
January 2018 Management Update:
Currently, the following components of an
effective IIPP are ongoing:
Accident Investigation: supervisors investigate all
accidents, injuries and near-misses and make
appropriate changes to minimize recurrence.
Attachment A
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 2
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
Hazard Correction:
Supervisors correct conditions that are discovered
during their monthly inspections or after an injury
or accident to prevent reoccurrence.
Training:
Supervisors are responsible for ensuring their
employees are trained to perform work safely.
Documentation:
Safety training sign-in sheets and investigation
reports are maintained.
Communication:
Employees know how to inform management
about health and safety matters.
Expected Completion Date: June 2018
1.5. HR identify industry-specific
ergonomics and general wellness
training opportunities to minimize
common injuries, and coordinate
with departments to ensure
regular training is provided to
employees.
Human
Resources
Concurrence: Agree
Target Date: September 16, 2016
Action Plan: HR will coordinate industry-
specific ergonomic training, similar to
training provided to Parks maintenance
employees in 2010 and Library employees in
2014. We agree industry-specific ergonomic
training serves as a reminder for example,
that the best way to prevent back injuries is
to develop habits that reduce the strain
placed on the back.
In Progress September 2018 Management Update:
Due to training staffing changes, industry-specific
ergonomic training has not been coordinated and
will be scheduled in Spring 2019.
Expected Completion Date: May 2019
January 2018 Management Update:
Have identified trainer and in process of
establishing contract and training dates. Using
claim data for previous 5 years, have identified top
5 body parts injured with objective to train
employees with highest exposure and focus on
injury prevention.
Expected Completion Date: May 2018
Finding 2: Injured employees’ benefit eligibility is not accurately and completely tracked and monitored, resulting in both overpayments and underpayments of
workers’ compensation benefits.
2.1. HR continue working with ASD and
CalPERS to address the disability
Human
Resources
Concurrence: Agree In Progress September 2018 Management Update:
Implentation of new disability coding delayed due
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 3
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
leave benefits that were
incorrectly reported as
compensation to CalPERS.
Target Date: February 2017
Action Plan: HR worked with Payroll to
identify wage codes and required
corrections needed to fix CalPERS payroll
report.
to priority FLSA project and Union contract
implementation. Testing dates under review with
ASD.
Expected Completion Date: June 2019
January 2018 Management Update:
SAP Functional has configured new disability
coding to make correction to CalPERS report. Next
steps include testing by Payroll and HR to move
forward with correction which have been delayed
due to other priority projects.
Expected Completion Date: June 2018
2.2. HR review the 22 claims that
accounted for 87 percent of the
total additional city benefits
difference in Exhibit 11, and take
necessary action to address any
errors identified.
Human
Resources
Concurrence: Agree
Target Date: July 2016
Action Plan: HR working on timecard
amendments to address errors with 2 Public
Safety officers whose claims were initially
delayed when claims first submitted and
final determination resulted in denial and
non-acceptance.
Completed September 2018 Management Update:
HR reviewed 22 claims and completed action on
errors where possible.
January 2018 Management Update:
HR reviewed 22 claims and action on errors where
possible still under review.
Expected Completion Date: May 2018
2.3. HR review the existing disability
leave management process in
Exhibit 12 and determine the
optimal monitoring structure,
update the tools and procedures,
and allocate sufficient and skilled
resources to ensure:
a. Benefit eligibility and work
status of injured employees is
accurately, completely, and
timely tracked including:
• Start date of disability
Human
Resources
Concurrence: Agree
Target Date: December 16, 2016
Action Plan:
a. HR is exploring better methods to
track temporary disability and
return-to-work status accurately,
completely and timely.
b. HR will also improve process of
maintaining timely communication
with York.
c. HR staff will explore how to
improve management of disability
Completed September 2018 Management Update:
a. Completed- York has identified return-to-
work report which York claims staff has
now incorporated to track modified duty
and lost time status. HR staff is working
with York to ensure data reliability.
d. Completed- HR explored how to audit
benefit notices and vouchers issued by
York and determined that comparison is
not possible due to difference between
what the City pays highly compensated
injured employees and state
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 4
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
• Date released back to work
by the treating physician
• Date returned to modified
duty, including the
assignment and payroll
code used
• Date returned to full duty
b. Any changes in work status are
communicated to York
c. Disability leave time buckets
are updated as soon as HR is
notified of the status change.
d. Benefit notices and vouchers
issued by York are consistent
with the timecards and actual
benefits paid through payroll.
e. Employees comply with the
City policy requiring timely
submission of work status
notes.
leave buckets in SAP.
d. HR will explore how to audit
benefit notices and vouchers issued
by York to ensure they are
consistent with timecards. This
would be a manual process that
may not be able to be supported.
e. HR will work with supervisors to
ensure employees comply with City
policy requiring timely submission
of work status notes.
f. HR will explore options for
improved disability leave
management and tracking as part
of the SAP replacement RFP
process.
requirements.
HR posts benefit notice on HR site
notifying employees regarding benefit
notice TD and salary continuation on
paycheck. Handout explaining how to
interpret York notice provided to
impacted employees.
January 2018 Management Update:
80% complete
a. In progress- York is able to track return-
to-work in claim file in “notes,” which
does not provide ability to run report. HR
still exploring improved tracking
methodology.
b. Completed- The workers’ compensation
desktop procedures have been reviewed
and revised to include the most effective
monitoring process based on the tools,
systems and resources currently
available.
c. Completed- Central email box was
created to receive work status notes.
Monitoring now in place morning and
afternoon as well as establishing timely
responses. Work procedures have been
established to ensure the monitoring and
management of the box, including
process in place with support staff to
track and monitor start and end dates of
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 5
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
disability leave buckets.
d. In progress- HR explored how to audit
benefit notices and vouchers issued by
York and determined that comparison is
not possible due to difference in TD
payments. HR will review with York to
determine if they can track the salary
continuation TD code 7200 as well as the
difference between what the City pays
highly compensated injured employees
and state requirements.
e. Completed-
1. Procedure document was updated
and posted on City website in 2016.
Roles of WC staff members, third
party administrator, injured
employee’s and their supervisors has
been streamlined and documented in
detail.
2. Training was completed in June 2016;
74% of supervisors/managers
attended. Created training video
which is posted on the HR Workers’
Compensation intranet site and
PowerPoint slides for future
reference for supervisors.
Expected Completion Date: March 2018
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 6
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
2.4. HR work with ASD to ensure that
the data necessary for disability
leave management is captured
through time reporting in SAP to
support the process, including:
a. Revising the City’s payroll
procedures, Policies and
Procedures 2-06/ASD, to
provide clear instruction for
reporting disability leave on
SAP timecards.
b. Configuring the SAP system to:
• Track compensation
reportable to CalPERS
separately.
• Track medical
appointments that qualify
as disability leave by
creating a separate payroll
code.
• If feasible and cost-
effective, limit the number
of days each employee can
code disability leave or
modified duty on their
timecard based on their
position.
Human
Resources /
Administrative
Services
Concurrence: Agree
Target Date: February 2017
Action Plan:
a. HR following up with ASD Payroll on
feasibility to revise City payroll
procedures; otherwise, will include
steps in City’s Workers Compensation
policy.
b. ASD and HR have determined how to
code public safety temporary disability
as “PERSable” and non-public safety
temporary disability as “non-PERSable”
by using different SAP codes.
- A separate timecard code (7600) is
being developed to track medical
appointments.
- Limiting the number of hours each
employee can code disability leave
can be accomplished when disability
leave bucket is established; may not
be feasible for modified duty and
requires further review.
In Progress September 2018 Management Update:
50% complete
b.
1. In Progress- SAP Functional has
configured new disability coding to make
correction to CalPERS report.Next steps
include testing by Payroll and HR to move
forward with correction which has been
delayed due to other priority FLSA and
Union contract implementation project.
3. Not started- HR discussed new process
with SAP Functional to develop new SAP
coding system for workers compensation
tracking. At this time, determined not
feasible in SAP. Instead, HR will review
new HR ERP solution to look for improved
coding process.
Expected Completion Date: June 2019
January 2018 Management Update:
50% complete
a. Completed- HR has drafted Timecard
Disability and Modified Duty Coding
guideline which will be attached to City’s
Workers Compensation policy
b.
1. In Progress- SAP Functional has
configured new disability coding to make
correction to CalPERS report.Next steps
include testing by Payroll and HR to move
forward with correction which has been
STATUS OF AUDIT RECOMMENDATIONS
DISABILITY RATES AND WORKERS’ COMPENSATION – ISSUED 10/17/16
PAGE 7
Recommendation
Responsible
Department Original Target Date and Response
Current
Status
Implementation Update and
Expected Completion Date
delayed due to other priority projects.
2. Completed- New SAP payroll code has
been created by ASD and tested by HR to
track medical appointments separately
from disability leave. An email
communication was distributed to city
staff on use of new code.
3. Not started- HR discussed new process
with SAP Functional to develop new SAP
coding system for workers compensation
tracking. At this time, determined not
feasible in SAP. Instead, HR will review
new HR ERP solution to look for improved
coding process.
Expected Completion Date: June 2018
Finding 5: Workers’ compensation revenues, costs, and performance data are not clearly reported for informed decision making.
5.2. HR work with York to identify
useful performance measures and
establish procedures to ensure
reliable reporting of performance
data using a consistent
methodology.
Human
Resources
Concurrence: Partially Agree
Target Date: January 2017
Action Plan: HR will explore best practice
performance measures and establish
procedure to ensure reliable reporting.
Completed September 2018 Management Update:
A set of best practice measures have been
identified and HR has developed a consistent
methodology to ensure reliable reporting.
January 2018 Management Update:
HR has worked with York to identify useful
performance measures available on York
Scorecard. HR participating in webinar to explore
workers compensation best practice metrics.
Expected Completion Date: March 2018
CITY OF PALO ALTO OFFICE OF THE CITY AUDITOR
October 23, 2018
The Honorable City Council
Palo Alto, California
Policy and Services Committee Recommends the City Council Accept
the ERP Planning: Data Standardization Audit
In accordance with the Fiscal Year 2018 Annual Audit Work Plan, the Office of the City Auditor
has completed the ERP Planning: Data Standardization audit. The audit report presents one
finding and four recommendations. The Office of the City Auditor recommends that the Policy
and Services Committee review and recommend to the City Council acceptance of the ERP
Planning: Data Standardization audit.
Respectfully submitted,
Harriet Richardson
City Auditor
ATTACHMENTS:
• Attachment A: Data Standardization Audit (PDF)
Department Head: Harriet Richardson, City Auditor
Page 2
ERP Planning: Data Standardization
October 17, 2018
Office of the City Auditor
Harriet Richardson, City Auditor
Mimi Nguyen, Senior Performance Auditor
Jordan Christenson, Performance Auditor
Attachment A
Page intentionally left blank for double-sided printing
Attachment A
Office of the City Auditor ● 250 Hamilton Avenue, 7th Floor ● Palo Alto, CA 94301 ● 650.329.2667
Copies of the full report are available on the Office of the City Auditor website at:
http://www.cityofpaloalto.org/gov/depts/aud/reports/performance/default.asp
OFFICE OF THE CITY AUDITOR
EXECUTIVE SUMMARY
ERP Planning: Data Standardization
October 17, 2018
PURPOSE OF THE AUDIT
The purpose of this audit was to determine if the City currently has data standardization in place in
ERP master data and to give examples for types of standardization recommended prior to transferring
data to the new ERP system.
REPORT HIGHLIGHTS
Finding:
Implementing data
standardization will
increase data accuracy
and uniformity in the
future ERP system.
The City can benefit from formalizing data standardization. Within the City’s
current ERP system, SAP, we identified examples for improving data quality,
compatibility, and consistency. Implementing data standardization will offer
a structure for facilitating the input of data in a more accurate and uniform
manner. It will also improve data output and data analytics.
The five main benefits of data standardization:
• Improved data quality
• Increased data compatibility
• Improved consistency and efficiency of data collection
• Reduced data redundancy
• Improved data access.
Key Recommendations:
The City should review city data and implement data cleansing through
manual or automated methods for data standardization prior to transfer to
the new ERP system. The Information Technology Department should:
• Provide governance over data standardization, such as who is
responsible for data standardization, what data is subject to
standardization, what is the data standardization, when does
standardization change, etc.
• Work with Departments to review the data within SAP and determine
what will benefit most by standardizing data.
• Review other systems and implement data standardization, where
feasible and beneficial; especially in circumstances when the data feeds
into SAP.
• Require Departments to implement data standardization requirements
during data cleansing in the ERP transition.
Attachment A
Page intentionally left blank for double-sided printing
Attachment A
TABLE OF CONTENTS
Objective ................................................................................................................................................. 1
Background ............................................................................................................................................. 1
Scope ...................................................................................................................................................... 2
Methodology .......................................................................................................................................... 2
Finding:
Implementing data standardization will ensure increased data accuracy and uniformity in the
future ERP system .............................................................................................................................. 4
Recommendations ........................................................................................................................... 11
Appendix 1: City Manager’s Response ................................................................................................ 12
ABBREVIATIONS
DAMA Data Management Association
DMBOK Data Management Body of Knowledge
EPA Environmental Protection Agency
Attachment A
Page intentionally left blank for double-sided printing
Attachment A
ERP Planning: Data Standardization 1
INTRODUCTION
Objective The purpose of this audit was to determine if the city has data
procedures that govern standardization of master data in SAP and
to give examples for types of standardization that would be
beneficial when implementing the new ERP system.
Background In the ERP Planning: Data and System Security Governance audit,
we identified areas of concern for the lack of data governance in
the City. The audit states that successful business processes
require accurate, consistent, and complete master data.
Master data Master data is the source for the most accurate data on business
practices within the City and is used to inform transactional data. It
pertains to business entity information regarding employees,
customers, vendors, and the organization. Management provides
control over how master data is entered in a system to enable
consistent, shared, and contextual use across systems.
Data Standardization Data standardization is the model, examples, or rules to increase
reliability and effectiveness of common data elements. The two
main components to data standardization are implementing
policies and automated processes to reduce errors as data is being
entered in the database and making corrections to data in the
database.
Policies and procedures should define an organization’s data
standards, including controlling input structures within the
database, such as format, naming conventions, standard
abbreviations, and allowable ranges of master data values;
organizational policies, such as data cleansing standards and
procedures; or greater data governance structures. Once data is
entered into the database, automatic or manual tools can be used
to standardize data.
Data quality rules for master data should include elements of both
strategies. It is considered a best practice to control data entry on
the front end through policies and procedures rather than relying
primarily on downstream data cleansing tools.
City’s Commitment to Accurate
Data
The City instituted an open data web portal in 2012 to offer
residents and interested parties access to city data and proclaimed
in 2014 that it would institute “open data by default” to make city
Attachment A
2 ERP Planning: Data Standardization
functions more “transparent, participatory, and accountable.”
Adopting a new ERP system is a moment of opportunity for the
City to ensure the reliability and transparency of city data and to
allow for greater investment in data in the future.
Past Audits The City Auditor’s Office has encountered data standardization
and data integrity issues in several past audits, including:
• 2017 Continuous Monitoring Audit: Payments
• 2017 Accuracy of Water Meter Billing Audit
• 2015 Utility Meter Audit: Procurement, Inventory, and
Retirement
• 2013 Employee Health Benefits Administration Audit
Scope This audit gives guidance on data standardization policies and
consistency issues that should be addressed through data
cleansing prior to transferring data to the new ERP system. It
provides general examples of non-standardized data as currently
entered in SAP to provide context for the type of data
standardization concerns. Companion audits will explore the
accuracy and completeness of specific city data sets and give
recommendations to specific departments for improving the
reliability and integrity of those data sets.
It was beyond the scope of this audit to look at standardization of
data from sources other than SAP that may filter into the Open
Data platform. However, instituting organization-wide data
standardization and governance policies will allow for more
accurate outfacing data to the public through the Open Data
platform, which will increase the reliability and transparency of
data for city residents.
Methodology To accomplish our objective, we:
• Identified standardization issues in city master data sets in SAP,
which is the City’s current enterprise resource planning system.
• Identified any existing data policies in place (data dictionaries,
data input rules in SAP).
• Analyzed a sample of data sets for inconsistency and data
standardization issues.
• Displayed these examples based on the potential benefits that
Attachment A
ERP Planning: Data Standardization 3
would come from standardizing the data.
• Provided examples on how to improve data input processes and
data cleansing for more standardized data.
• Used the Environmental Protection Agency’s (EPA) data
standards, developed in partnership with the Environmental
Data Standards Council for information collection and exchange,
to identify potential benefits for data standardization.1
• Referenced and used as guidance the Data Management Body
of Knowledge (DMBOK), published in 2010 by the Data
Management Association (DAMA). We used this guidance to
define data standardization and to outline practices for
evaluating the extent to which the City’s data is standardized.
We selected data that was entered into SAP from January 1, 2016
through April 13, 2018. We chose this date range to concentrate
examples on recent rather than historical data while still having a
large enough range to demonstrate patterns in the data. The
examples we used represent typical issues in SAP that could
benefit from standardization, rather than specific high-risk areas.
These types of issues occur throughout SAP, as evidenced by the
number of past audits that have identified data standardization
and integrity issues. As such, no specific department or area is
intended to be singled out. Rather, the audit discusses the greater
issue with data standardization across City master data.
Compliance with government
auditing standards
We conducted this audit of ERP Planning: Data Standardization in
accordance with our FY 2018 Annual Audit Work Plan and
generally accepted government auditing standards. 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.
We would like to thank management and staff in the Administrative Services Department
and the Information Technology Department for their time, cooperation, and assistance during
the audit process.
1 “Data Standards Briefing Paper,” Environmental Protection Agency, available at
https://www3.epa.gov/ttnchie1/conference/ei12/panel/kohn.pdf
Attachment A
4 ERP Planning: Data Standardization
Finding Implementing data standardization will ensure increased data
accuracy and uniformity in the future ERP system.
Summary The City can benefit from formalizing data standardization. Within
the City’s current ERP system, SAP, we identified examples for
improving data quality, compatibility, and consistency based on
our assessment of data in different working areas. Implementing
data standardization will offer a structure for facilitating the input
of data in a more accurate and uniform manner. It will also
improve data output and data analytics.
Benefits of Data Standardization The EPA Data Standards Briefing Paper explains five main benefits
of data standardization:
• Improved data quality
• Increased data compatibility
• Improved consistency and efficiency of data collection
• Reduced data redundancy
• Improved data access.
The sections below give examples of the City’s SAP master data
that is not standardized and explain benefits that would potentially
be gained by standardizing data.
Improved Data Quality Improving data quality would ensure that a common language is
established for data entered in SAP, which can facilitate an easier
and more accurate exchange of information. Exhibit 1 shows
examples of data quality issues in one type of SAP master data.
Exhibit 1: Inventory Master – Inconsistencies in Data Table
Attachment A
ERP Planning: Data Standardization 5
Exhibit 1 shows that the material description does not have a
consistent structure. Although all of the items include similar
information for lamps, there are discrepancies in the item details,
which could cause ordering errors if the description does not
include all needed information or potentially result in mistakes
when ordering parts and/or reordering items. For example, there
are:
• inconsistencies on what is included as lamp specifications
(watts, voltage, or both)
• inconsistencies in the technical description (e.g.,
inclusion/exclusion of the base size)
• inconsistencies in the use of “W,” ”Watts,” or neither to denote
wattage
• inconsistencies in the use of commas and/or periods to
separate the item type from its description
• spelling errors
In addition, some columns are not consistently filled out, such as
the manufacturer part number that is often left blank. Omitting
key information in the master data could cause ordering issues for
items that require, for example, a specific manufacturer or energy
rating to comply with city policy.
Although the description field has some structure to the input,
creating standardization rules would be more useful both for those
familiar and unfamiliar with any specific item:
• Having a consistent format would makes it easier to find a
specific item and help ensure that the correct item is
purchased.
• Including all key information in appropriate field would allow
searches on other details, such as the manufacturer part
number, rather than relying only on information entered into
the material description field.
• Exhibit 2 shows how stricter standardization rules would assist
in the consistency of the light bulb data for the Material
Description field.
Attachment A
6 ERP Planning: Data Standardization
Exhibit 2: Inventory Master – Current vs. Improved Description Example
The current data entry rule:
ITEM and TYPE and other information
An example standardization rule of:
ITEM, TYPE, WATTAGE AND SHAPE, BASE SIZE
These two changes, standardizing in data the material description
column and including manufacturer part numbers in the
manufacturer part number column, are examples of how to
increase the overall quality of the dataset. Information in these
columns could better direct inventory and purchasing staff
regarding what to buy.
Increased data compatibility Data standardization can improve the compatibility of data across
modules in SAP. This can be accomplished by establishing data
dictionaries or building policies in the system to require that all
data in having the same field names contain identical data
throughout the system. This can be accomplished by requiring a
single point of entry for any particular data field. Data that is
inconsistently entered across the system can create errors that
could lead to financial and business losses, as well as an inability to
provide accurate reporting for decision-making purposes. It can
also create a need for additional data cleansing if data used as the
primary field for migration into the new ERP system is selected
from an incorrect data source. It may not be possible to combine
data from nonmatching fields, but if done, can lead to general
incompatibility, redundancy, or other errors.
Attachment A
ERP Planning: Data Standardization 7
Two examples of data incompatibility are shown in Exhibit 3. First,
there are two fields in SAP that identify a Division, which specifies
a type of service provided (i.e., electric, gas, water, etc.). One field
is Division and the other is Division Category, both representing
the service type; however, they are inconsistent with each other.
Second, the two fields Account Determination (AD) and Type of
Premise specify the type of property serviced (i.e., residential,
commercial, industrial, etc.). Although both fields may be used
slightly differently, the numbering, sequencing, naming, and
detailed breakdown would likely benefit from standardization.
These two examples show nonmatching and incompatible data
that should be reviewed for structural inconsistency in the new
ERP system.
Exhibit 3: Utility Service and Property Type Inconsistency and Incompatibility Examples
• The division type (under Dv and
Division category) and description
(under Name and Short Description)
do not contain the same dropdown
list/choices.
• The naming, Electric and Electricity,
are inconsistent.
• The naming and use of Division
number 06 are different from each
other.
• There are gaps in the numbering
within each.
• The property type categories, Account
determination and Type of premise, do
not contain the same dropdown
choices.
• The naming descriptions and detail of
description are inconsistent.
• The numbering and sequencing are
incompatible.
Attachment A
8 ERP Planning: Data Standardization
Improved consistency and
efficiency of data collection
As stated in the data governance audit, the City does not currently
have data standardization policies. These policies can improve the
consistency of data by outlining the information that should be
included within a field and requiring a consistent format. Some
policies can be built into an ERP system through drop-down menus
that limit input to standardized choices and requiring that open-
text inputs be in a certain format. Doing this provides the
additional benefit of increasing efficiency by reducing the amount
of typing needed for data collection.
Examples of inconsistency issues that can be controlled by data
standardization policies include:
• Formatting (0.11 vs. 0.1, (XXX) XXX-XXXX vs. XXXXXXXXXX vs.
XXX.XXX.XXXX)
• Inputs (Street vs. St. vs. st)
• Type (12 vs. twelve)
In addition to formatting consistency, standardization policies can
require input of fields that are deemed important to be included in
the database, thereby reducing the number of blank or missing
fields. This type of policy can also be built into an ERP system by
not allowing the person entering the data to move on to the next
screen or action unless certain data has been entered.
City data currently has both omissions and inconsistent formatting,
which demonstrate the effect of not having strong data
standardization policies. Exhibit 4 shows an example of City vendor
data that has inconsistent formatting of the vendor name, city
name, postal code, and address. Additionally, although there is a
field for a PO Box, it was not used in in the time period reviewed in
favor of being included in the street field. This could possibly cause
issues with mailings or contacting the vendor.
Attachment A
ERP Planning: Data Standardization 9
Exhibit 4: Vendor Master - Formatting Inconsistencies
Implementing a data standardization policy either within the ERP
system or through a data governance policy requires input of fields
that are deemed important for consistency.
Reduced data redundancy Data redundancy can be reduced through data standardization.
Reducing data redundancy makes it easier to locate existing
records rather than creating new records for the same data.
We observed redundancy of data in master vendor files. For
example, Exhibit 5 shows that the same vendor name was
associated with several vendor numbers rather than only one
vendor number being associated with each unique vendor. The
only difference between the vendor files is the formatting of the
street address. Redundancy in this case could cause issues with the
history of purchases from the vendor and cause other data issues.
Exhibit 5: Vendor Master - Name and Search Term
Exhibit 6 shows that there are two records for what are likely
identical items. They have different material numbers due to
inconsistency in inputting information. Adding to the issue, the
material part number was not entered, which could make it
difficult to find the right item when ordering (see above discussion
regarding improved data quality). Not having consistent rules for
entering data can lead to multiple records for the same items.
Attachment A
10 ERP Planning: Data Standardization
Redundancy in this example could lead to purchasing and purchase
history errors.
Exhibit 6: Inventory Master – Material Description
These two examples demonstrate how redundant data may be a
result of formatting inconsistency and result in search errors.
Standardizing the structure of data inputted into the ERP system
would help minimize redundancy.
Improved data access Standardized data can improve data access by making it easier to
retrieve information. For example, it can be difficult to retrieve
complete and accurate information if search terms are not
standardized. Exhibit 7 shows an example shows that the search
terms have several standardization issues:
• Search term does not match name of vendor
• One search term for multiple vendors
• First and/or last name used for search term inconsistently
These inconsistencies could lead to issues of data access.
Exhibit 7: Vendor Master Search Terms
Attachment A
ERP Planning: Data Standardization 11
Other data feeding into the ERP
system
The standardization examples we identified are applicable to data
in other systems outside of SAP. It is important to also consider
and apply standardizations to these data sets because the data
may feed into the ERP system. Exhibit 8 shows an example of some
of the same inconsistencies and redundancies in other systems.
Palo Alto 311 and Accela are an external facing, multi-platform
solution for providing Palo Alto residents, businesses, and visitors
access to a set of local government-provided services and online
requests. The Issue Types and the Case Types contain similarities
but lists different descriptions. Standardization can assist with data
quality, consistency, compatibility, redundancy, and access.
Exhibit 7: Other systems with data inconsistencies
PaloAlto311 Code Enforcement Issue Types: Accela/Building Eye Case Types:
• Building Construction (Unpermitted)
• Property Maintenance (Weeds, Vegetation,
Encroachment, Junk/Debris in Public View, etc.)
• Fences
• Zoning Compliance (AirBnB, Vacation Rentals, Non-
Conforming Use, etc.)
• Signs (Non Permitted, Signs in Public Property, etc.)
• Leaf Blower (Gas Powered)
• Other
• Building
• Encroachment
• Fences
• Home Occupation
• Long Term Condition Monitoring
• Property Maintenance
• Short Term Rental
• Signs (ARB)
• Signs (Public Property)
• Vehicles
• Zoning
• Leaf Blower
Recommendations To help ensure that the City adopts best practices for data
standardization when transitioning to the City’s new ERP system,
we recommend that the Information Technology Department
adopt practices for standardizing data, specifically including:
1. Provide governance over data standardization, such as who is
responsible for data standardization, what data is subject to
standardization, what is the data standardization, when does
standardization change, etc.
2. Work with Departments to review the data within SAP and
determine what will benefit most by standardizing data.
3. Review other systems and implement data standardization,
where feasible and beneficial; especially in circumstances
when the data feeds into SAP.
4. Require Departments to implement data standardization
requirements during data cleansing in the ERP transition.
Attachment A
12 ERP Planning: Data Standardization
APPENDIX 1 – City Manager’s Response
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department(s)
Agree, Partially Agree, or Do
Not Agree and Target Date and
Corrective Action Plan
To be completed 6 months after Council acceptance and every 6 months
thereafter until all recommendations are implemented
Current Status
Implementation Update and
Expected Completion Date
Finding 1: Implementing data standardization will ensure increased data accuracy and uniformity in the future ERP system.
To help ensure that the City adopts best practices for data standardization when transitioning to the City’s new ERP system, we recommend that the
Information Technology Department adopt practices for standardizing data, including:
1. Provide governance over data
standardization, such as who is
responsible for data
standardization, what data is
subject to standardization, what
is the data standardization, when
does standardization change, etc.
Information
Technology
Agree.
Target Date: Dec 31, 2019
Corrective Action Plan:
Data standardization and
governance are both already
priorities of the draft data
strategy plan document that is
being developed.
2. Review other systems and
implement data standardization,
where feasible and beneficial;
especially in circumstances when
the data feeds into SAP.
Information
Technology
Agree.
Target Date: Dec 31, 2019
(For standardization guidance
only. Remediation may take
significantly longer and will be
established once an assessment
is made).
Attachment A
ERP Planning: Data Standardization 13
Recommendation
Responsible
Department(s)
Agree, Partially Agree, or Do
Not Agree and Target Date and
Corrective Action Plan
To be completed 6 months after Council acceptance and every 6 months
thereafter until all recommendations are implemented
Current Status
Implementation Update and
Expected Completion Date
Corrective Action Plan:
The plan to implement data
standardization across systems
beyond SAP will be covered in
the City’s upcoming data
strategy plan.
3. Work with Departments to review
the data within SAP and
determine what will benefit most
by standardizing data.
Information
Technology
Agree.
Target Date: Dec 31, 2019.
Corrective Action Plan:
The plan to identify data and
data stewards for SAP to
determine standardization
benefits will be covered in the
City’s upcoming data strategy
plan.
4. Require Departments to
implement data standardization
requirements during data
cleansing in the ERP transition.
Information
Technology
Agree.
Target Date: Dec 31, 2019.
Corrective Action Plan:
This is already a mandatory
component of the design phase
of implementing the new ERP
system.
Attachment A
CITY OF PALO ALTO OFFICE OF THE CITY AUDITOR
October 23, 2018
The Honorable City Council
Palo Alto, California
Policy and Services Committee Recommends the City Council Accept
the ERP Planning: Separation of Duties Audit
In accordance with the Fiscal Year 2018 Annual Audit Work Plan, the Office of the City Auditor
has completed the ERP Planning: Separation of Duties audit. The audit report presents one
finding and two recommendations. The Office of the City Auditor recommends that the Policy
and Services Committee review and recommend to the City Council acceptance of the ERP
Planning: Separation of Duties audit.
Respectfully submitted,
Harriet Richardson
City Auditor
ATTACHMENTS:
• Attachment A: Separation of Duties Audit (PDF)
Department Head: Harriet Richardson, City Auditor
Page 2
ERP Planning: Separation of Duties
October 17, 2018
Office of the City Auditor
Harriet Richardson, City Auditor
Mimi Nguyen, Senior Performance Auditor
Lisa Wehara, Performance Auditor II
Jordan Christenson, Performance Auditor
Attachment A
Page intentionally left blank for double‐sided printing
Attachment A
Office of the City Auditor ● 250 Hamilton Avenue, 7th Floor ● Palo Alto, CA 94301 ● 650.329.2667
Copies of the full report are available on the Office of the City Auditor website at:
http://www.cityofpaloalto.org/gov/depts/aud/reports/performance/default.asp
OFFICE OF THE CITY AUDITOR
EXECUTIVE SUMMARY
ERP Planning: Separation of Duties
October 17, 2018
PURPOSE OF THE AUDIT
The purpose of this audit was to evaluate the adequacy of separation of duties for various activities
in the current SAP system and make recommendations to ensure that any identified deficiencies
are corrected for the new ERP system.
REPORT HIGHLIGHTS
Finding:
Implementing
effective separation
of duties and ensuring
well‐restricted user
access controls for the
new ERP system will
decrease
vulnerabilities and
risks
The City uses varying automated and manual processes for separating key
business activities and duties among staff for the high‐risk activities we
reviewed, such as payroll processing, purchase orders and check
processing, revenue collections, and asset management transactions.
Although we did not find any major concerns, we identified opportunities
for improvement. We assessed an employee’s ability to access and
perform transactions within high‐risk areas. We also offered an
understanding of where the high‐risk areas are within various workflows.
Key Recommendation:
When implementing the new ERP system, the Administrative Services,
Information Technology, and Utilities Departments should separate duties
for high‐risk conflicting tasks by restricting transaction codes or
developing mitigating controls where conflicts cannot be avoided.
Attachment A
Page intentionally left blank for double‐sided printing
Attachment A
TABLE OF CONTENTS
Objective ................................................................................................................................................. 1
Background ............................................................................................................................................. 1
Scope ...................................................................................................................................................... 3
Methodology .......................................................................................................................................... 3
Finding:
Implementing effective separation of duties and ensuring well‐restricted user access controls for
the new ERP system will decrease vulnerabilities and risks. ............................................................ 5
Recommendations ........................................................................................................................... 14
Appendix 1: City Manager’s Response ................................................................................................ 15
ABBREVIATIONS
ACFE Association of Certified Fraud Examiners
AP Accounts Payable
ASD Administrative Services Department
ERP Enterprise Resource Planning
FISCAM Federal Information System Controls Audit Manual
IT Information Technology
RC Revenue Collections
RFP Request for Proposal
SoD Separation of Duties
Attachment A
Page intentionally left blank for double‐sided printing
Attachment A
ERP Planning: Separation of Duties 1
INTRODUCTION
Objective The purpose of this audit was to evaluate the adequacy of separation of
duties for various activities in the current SAP system and make
recommendations to ensure that any identified deficiencies are corrected
for the new Enterprise Resource Planning (ERP) system.
Background An ERP system is a type of business management software that integrates
key business activities of the City, such as purchasing, inventory, utilities,
accounting, payroll, and information technology. SAP is the current ERP
system and has been in place since 2003. The city issued a Request for
Proposal (RFP) and plans to complete migrating the City’s business data
and processes into a new ERP system by June 2022.
Separation of Duties
(SoD)
Separation of duties (SoD), also known as segregation of duties, is an
internal control mechanism to reduce the risk of erroneous or fraudulent
transactions, improper program changes, and the damage or destruction
of computer resources. This is accomplished by separating parts of a
process or activity across a department or organization. To reduce the risk
of unauthorized transactions (intentional or unintentional), work
responsibilities and the corresponding computer access should be
segregated so that one individual does not control multiple critical stages
of a process. For example, a person should not be allowed to enter an
invoice for payment, approve an invoice for payment, process the invoice
for payment, and disburse a check for payment. Doing so would result in
an opportunity for that individual to create and process an unauthorized
payment transaction.
Standards and
Guidance
We used the ISACA report, “Best Practices to resolve Segregation of
Duties conflicts in any ERP environment," to document high‐risk
conflicting tasks in ERP systems and how they can be mitigated with
automated separation of duties within the system, and developed criteria,
which is explained below in the methodology section.1
For general guidance on separation of duties, we referred to the
“Standards for Internal Control in the Federal Government,” sections
10.12 ‐ 10.14: Segregation of Duties, published in September 2014 by the
United States Government Accountability Office. These sections give
1 ISACA previously stood for Information Systems Audit and Control Association, but now goes by its acronym only. It is an
independent, nonprofit, global association that engages in the development, adoption, and use of globally accepted,
industry‐leading knowledge and practices for information systems.
Attachment A
2 ERP Planning: Separation of Duties
general guidance on the role of segregation of duties for internal control
and the option for alternative control activities if separation of duties is
not practical due to staffing limitations or other factors.
We referenced and used as guidance the “Federal Information System
Controls Audit Manual” (FISCAM), sections 3.2: Access Controls and 3.4:
Segregation of Duties, published in February 2009 by the United States
Government Accountability Office, to generally assess the City’s control
systems. It states that “effective segregation of duties starts with effective
entitywide policies and procedures that are implemented at the system
and application levels.”
Risk of not
Implementing SoD
According to a 2016 report on occupational fraud and abuse by the
Association of Certified Fraud Examiners (ACFE), asset misappropriation
was the most common form of occupational fraud.2 Among the various
forms of asset misappropriation, billing schemes and check tampering
schemes were reported as posing the greatest risk.
In an ERP system, risks and vulnerabilities may arise from the lack of
proper segregation of duties. Unintended risks often stem from granting
employees excessive system authorizations by providing access to
functions that are not within their official duties. Challenges can occur
with the lack of resources, both financial and staffing.
Therefore, planning for the division of responsibilities and reflecting it in
the access privileges granted through an automated process to users of
Information Technology (IT) systems, as well as implementing manual
processes to mitigate any residual risk, such as collusion, becomes
necessary for the proper, efficient, and secure execution of the business
processes.
SoD Responsibility The responsibility of SoD in the City resides within each business process
area and within the IT systems supporting their execution. An effective
SoD strategy requires that each business area, with a thorough
understanding of its business process and workflow, collaborate with IT to
gain an understanding of the system supporting SoD so the business area
can structure and help IT design ERP security around separation of duties
issues, particularly in the highest‐risk areas.
2 Under the Occupational Fraud and Abuse Classification System (Fraud Tree), asset misappropriation includes the theft of
cash receipts and fraudulent disbursements, such as billing schemes, expense reimbursement schemes, check tampering,
and register disbursements. Statistics included in the ACFE’s report are based only on the results of the single largest fraud
case that certified fraud examiners self‐reported in an online survey sponsored by the ACFE.
Attachment A
ERP Planning: Separation of Duties 3
Scope We reviewed best practices for separation of duties for ERP systems and
used criteria to assess the highest areas of risk to the City. Because this
audit is intended to provide high‐level guidance, we did not review and
assess SoD for all workflow processes. We only identified the highest‐risk
areas and made recommendations for use as the City implements the
future ERP system.
Methodology To accomplish our objective, we:
Researched and identified SoD best practices and guidance.
Created separation of duties criteria matrices from the list of high‐risk
conflicting tasks in ISACA’s document, “Best Practices to resolve
Segregation of Duties conflicts in any ERP environment," for six areas:
1. Accounts Payable
2. Payroll/Human Resource
3. Revenue Collections
4. Treasury
5. Utilities
6. Information Technology
Identified active employees, their user profiles, and their executable
transactions in SAP.
Reviewed and analyzed conflicting tasks within the high‐risk list.
Discussed with staff any mitigating processes that address active users
who have conflicting tasks.
Determined the effectiveness of the mitigating processes, both
automated and manual.
How to Use This Report Criteria matrices are presented for each business area we analyzed, which
we developed based on ISACA's “Best Practices to resolve Segregation of
Duties conflicts in any ERP environment." Each matrix displays the
employee roles and responsibilities, separated by key tasks within the
business area, and identifies the optimum separation of duties to mitigate
high‐risk conflicts.
The criteria matrices should be used as guidance to understand the
conflicting tasks within a business area and where automation would be
beneficial in the new ERP system to prevent employees from performing
high‐risk conflicting tasks. Exhibit 1 shows an example of a criteria matrix
and how to read it. The intent of this report is to identify areas of highest
risk, identify mitigating controls currently in place, and encourage the use
of system automation to mitigate such risks.
Attachment A
4 ERP Planning: Separation of Duties
EXHIBIT 1
Example Criteria Matrix with Auditor Explanation of How to Read
Although these practices are recommended, full implementation may not
be possible due to constraints such as ERP configuration, City budget,
staffing, or other resource factors, in which case, manual controls may
need to be substituted in lieu of automated processes.
Compliance with
government auditing
standards
We conducted this audit of ERP Separation of Duties in accordance with
our FY 2017 and FY 2018 Annual Audit Work Plan and generally accepted
government auditing standards. 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.
We would like to thank management and staff in the Information Technology, Administrative
Services, and Utilities Departments for their time, cooperation, and assistance during the
audit process.
Attachment A
ERP Planning: Separation of Duties 5
Finding Implementing effective separation of duties and ensuring well‐
restricted user access controls for the new ERP system will
decrease vulnerabilities and risks.
Summary The City uses varying automated and manual processes to
separate key business activities and duties among staff for the
high‐risk activities we reviewed, such as payroll processing,
purchase orders and check processing, revenue collections, and
asset management transactions. Although we did not find any
major concerns, we identified opportunities for improvement.
We assessed an employee’s ability to access and perform
transactions within a high‐risk area. We also offered an
understanding of where the high‐risk areas are within various
workflows.
ACCOUNTS PAYABLE Accounts Payable (AP) is a division of the Administrative Services
Department (ASD). Their goal is to process, record, and report
citywide financial transactions. AP primarily uses SAP to maintain
and process vendor invoices and payments. In FY 2017, the City
issued 10,301 checks, and purchased $122 million of goods and
services. AP has four employees: a Senior Accountant, a Lead
Account Specialist, and two Account Specialists.
Based on the matrix we developed in Exhibit 2, nine conflicting
tasks would need to be performed by at least nine different
employees for maximum separation of duties. Because this is not
feasible with the four employees currently in AP, manual controls
are needed to mitigate the high risks in this work area.
Attachment A
6 ERP Planning: Separation of Duties
EXHIBIT 2
Accounts Payable
SOURCE: Auditor’s analysis and summary of ISACA's “Best Practices to resolve Segregation of Duties conflicts in any ERP
environment.”
Accounts Payable employees can
enter an invoice and process
payment to that invoice, which
creates an unnecessary risk
While other departments enter invoices into SAP for AP staff to
process, creating a separation of duties, occasionally AP staff
processes their own invoices. All three Accounting Specialists in
AP can enter an invoice and process the payment for supervisory
approval. This creates a separation of duties conflict because
payment may be made on a fraudulently created and entered
invoice. User access allows invoices to be entered through three
types of SAP transactions, each differing based upon the type of
invoice entered for payment. Discontinuing AP’s access to these
SAP transactions and transferring this task to ASD Administration,
for example, would immediately mitigate this high risk.
We reviewed other high‐risk areas on the ISACA list for AP and
determined that they are well separated and well administered.
PAYROLL/HUMAN RESOURCES Payroll is a division of ASD that primarily processes payroll for
city employees through timesheet and check processing.
Paychecks are processed for about 1,200 employees, and total
$116 million dollars in authorized salary and benefits. Payroll has
five employees: a Senior Accountant, an Accountant, two Payroll
Analysts, and a Management Specialist.
Based on the matrix we developed in Exhibit 3, five conflicting
tasks would need to be performed by at least four different
Attachment A
ERP Planning: Separation of Duties 7
employees for maximum separation of duties. Staffing levels are
sufficient with five employees in Payroll for effective separation
of duties.
EXHIBIT 3
Payroll
SOURCE: Auditor’s analysis and summary of ISACA's “Best Practices to resolve Segregation of Duties conflicts in any ERP
environment.”
Payroll employees have access to
all payroll operations, which
creates risk
Each City employee enters time into a timesheet system. A
supervisor approves the time entered and Payroll approves and
processes the timesheets for payroll processing.
Four of the five payroll employees have access to all payroll
operations, for all employees and themselves. This is a high‐risk
access because it allows the ability to modify employee master
data or salary information and then process payroll fraudulently.
Some high‐risk tasks that should have restrictions, separation, or
effective manual processes instituted, currently do not. These
tasks allow for potential fraudulent activity, including the ability
for Payroll staff to:
Change their own and each other’s salary data, which
would allow the salary increase to go unnoticed.
Change employee time data by entering fraudulent time
to increase regular or overtime pay.
Enter false personnel data and time to process a
fraudulent payroll.
Attachment A
8 ERP Planning: Separation of Duties
Although Payroll has a manual process in place to manage the
risk of employees modifying master data and fraudulently
processing the change through payroll, the control can be more
effective. There may be opportunities within the new ERP system
to automate or separate duties between Human Resources and
Payroll to achieve a higher level of risk mitigation.
Human Resources had limited
high risk areas
The ISACA report listed only two high risk areas within Human
Resources: 1) change employee HR benefits then process payroll
without authorization, and 2) change master data and creating
the remittance to a third party vendor. The first was categorized
and reviewed as a Payroll item because the high risk is in the
fraudulent disbursement of a payroll check. The second was
determined as external because the risk was associated with a
third party vendor and check disbursement which was covered
under AP.
REVENUE COLLECTIONS Revenue Collections (RC) is a division in ASD and is responsible
for collecting City revenue generated from various city services.
RC collects over $97 million in revenue annually and is one of the
most public‐facing divisions of the City. RC has nine employees: a
Manager, two Lead Account Specialists, and six Account
Specialists.
Based on the matrix we developed in Exhibit 4, three conflicting
tasks would need to be performed by at least three different
employees for maximum separation of duties. Staffing levels are
sufficient with nine employees in RC for effective separation of
duties.
Attachment A
ERP Planning: Separation of Duties 9
EXHIBIT 4
Revenue Collections
SOURCE: Auditor’s analysis and summary of ISACA's “Best Practices to resolve Segregation of Duties conflicts in any ERP
environment.”
RC uses a revenue collection system external to SAP that is
integrated to upload transactions into SAP. Due to the customer
service and cash handling nature of RC, and the need for desk
rotation, multiple employees are needed to fill the same role.
Therefore, six of the nine employees in RC perform the same
tasks and many of the processes are manual and paper‐based.
Under this current process, RC has well separated, administered,
and mitigated the high‐risk tasks.
Although Revenue Collections has a manual process in place to
manage the risk of employees stealing cash, the control can be
more effective. The current control is paper‐based. It may be
more effective to move to an automated reconciliation and
reporting process.
TREASURY Treasury is a division of ASD and is responsible for managing and
investing the City’s funds and assets and facilitating debt
financing. Treasury manages $532 million of City cash and
investments and has two employees: a Manager and a Senior
Management Analyst.
Based on the matrix we developed in Exhibit 5, two conflicting
tasks need to be performed by at least two different employees
for adequate SoD in the high‐risk areas. Staffing levels are
sufficient with two employees in Treasury.
Attachment A
10 ERP Planning: Separation of Duties
EXHIBIT 5
Treasury
SOURCE: Auditor’s analysis and summary of ISACA's “Best Practices to resolve Segregation of Duties conflicts in any ERP
environment.”
The only high‐risk conflicting task we reviewed in Treasury was
the ability to create and confirm the processing of a stock trade.
The process for the tasks is completed manually and is separated
properly. However, automating some of these processes in the
new ERP system, if possible, would achieve some efficiencies.
UTILITIES The City’s Utility’s Department (Utilities) operates and provides
electric, gas, water, wastewater, and fiber optic services. Utilities
performs many of the same high‐risk duties as other divisions in
the Administrative Services Department; however, the
transactions are performed at a much less and more limited
capacity. These duties include maintaining utility customer data,
processing customer bills and payments, and collecting utility
revenue.
Due to the limited transactions, we did not determine this to be a
high‐risk area. However, Utilities should follow the same
separation of duties processes and practices established by the
Administrative Services Department when performing the high‐
risk tasks.
The matrix in Exhibit 6 identifies the high‐risk tasks performed by
Utilities and the separation of duties needed. We encourage
Utilities to continue implementing recommendations of previous
audits to strengthen their processes, which will also strengthen
the area of separation of duties.
Attachment A
ERP Planning: Separation of Duties 11
EXHIBIT 6
Utilities
SOURCE: Auditor’s analysis and summary of ISACA's “Best Practices to resolve Segregation of Duties conflicts in any ERP
environment.”
INFORMATION TECHNOLOGY The Information Technology (IT) Department is responsible for
the overall operational duties for the ERP system, including
development, maintenance, and administration. The matrix in
Exhibit 7 identifies the high‐risk tasks and the separation of
duties needed in these areas.
EXHIBIT 7
Information Technology
SOURCE: Auditor’s analysis and summary of ISACA's “Best Practices to resolve Segregation of Duties conflicts in any ERP
environment.”
Attachment A
12 ERP Planning: Separation of Duties
High‐Risk Areas The IT Department has two distinct roles in the area of
separation of duties: 1) within the IT Department as identified in
the matrix, and 2) as support for all the work areas throughout
the City. As with other work areas, separation of duties is
tempered by the size of the IT staff; however, where separation
of duties is not enforced, compensating controls are critical to
reduce the risk.
Within the IT department, generally, the following separation of
duties are key:
Computer operators should be prohibited from making
changes to programs and data.
System development staff should not have physical access to
computer rooms and not have update access to production
data.
Technical support staff should not have access to application
programs, production data, or physical access to the
computer room.
System access controls are an important part of IT’s role in
maintaining effective separation of duties. IT should be aware of
and responsive to all the key components of access control,
including authentication of who is given access, authorization
toward what they are given access to do, an audit trail to identify
what they have done, and administration to maintain privileges
and manage administrators.
The IT department, responding to a prior SAP Security audit and a
consultant’s review of the City’s separation of duties, has
implemented positive changes to their separation of duties
processes around access control. Their separation of duties policy
has been updated to provide clarity regarding roles and
responsibilities, for both IT staff and end users. A key, beneficial
change is that the IT Service Desk is now responsible for resetting
SAP passwords, which separated the SAP Basis Team’s ability to
have access to SAP user account creation and modification and
password reset.
One area that should be reviewed for improvement during the
ERP design and implementation period is the redefining of user
access profile and roles. Defining user access by profiles and roles
assignment is effective; however, how the profiles and roles are
Attachment A
ERP Planning: Separation of Duties 13
defined and using the concept of least privilege are important to
mitigating separation of duties. Least privilege is the concept and
practice of restricting access rights for users, accounts, and
computing processes to only those absolutely required to
perform routine, legitimate activities. Applied to people, least
privilege means enforcing the minimal level of user rights, or
lowest clearance level, that allows the user to perform his/her
role. In the previous separation of duties examples, we identified
areas where transactional access was given to users
unnecessarily.
IT provides support to the various work areas. Our general review
did not identify conflicts for concern; however, we would like to
reiterate that where separation of duties is not possible due to
limited staff, it is especially important for the end‐user
department to:
Authorize transactions.
Reconcile input/output and run‐to‐run cycles.
Control changes to master files.
Control resubmission of rejected transactions.
Restrict access to assets such as cash, blank checks,
negotiable documents and inventory.
Attachment A
14 ERP Planning: Separation of Duties
Recommendations To help ensure that the City adopts best practices for separation
of duties when transitioning to the City’s new ERP system, we
recommend that the City Manager direct all departments to
consult with the Information Technology Department to adopt
practices for ensuring separation of duties for high‐risk
conflicting tasks, based on the matrices in Finding 1, or develop
mitigating controls where conflicts cannot be avoided.
Specifically, we recommend that:
1. Administrative Services:
a) Transfer the task of entering Accounts Payable
invoices to ASD Administration and either discontinue
Account Payable’s SAP access for entering invoices or,
if not possible, create a procedure that can identify
if/when an Accounts Payable invoice is entered by an
Accounts Payable employee for supervisory review.
b) Have Payroll redesign the existing manual controls to
mitigate against the high‐risk areas of SoD conflict
identified.
c) Share with Utilities all relevant SoD practices adopted,
and Utilities practices should be consistent with that
of ASD.
2. Information Technology revisit the design and definition of
profiles and roles according to the concept of least privilege,
where possible.
Attachment A
ERP Planning: Separation of Duties 15
APPENDIX 1 – City Manager’s Response
The City Manager has agreed to take the following actions in response to the audit recommendations in this report. The City Manager will report
progress on implementation six months after the Council accepts the audit report, and every six months thereafter until all recommendations have been
implemented.
Recommendation
Responsible
Department(s)
Agree, Partially Agree, or Do
Not Agree and Target Date and
Corrective Action Plan
To be completed 6 months after Council acceptance and every 6 months
thereafter until all recommendations are implemented
Current Status
Implementation Update and
Expected Completion Date
Finding 1: Implementing effective separation of duties and ensuring well‐restricted user access controls for the new ERP system will decrease
vulnerabilities and risks.
To help ensure that the City adopts best practices for separation of duties when transitioning to the City’s new ERP system, we recommend that the City
Manager direct all departments to consult with the Information Technology Department to adopt practices for ensuring separation of duties for high‐risk
conflicting tasks, based on the matrices in Finding 1, or develop mitigating controls where conflicts cannot be avoided. Specifically, we recommend:
1.a. Transfer the task of entering
Accounts Payable invoices to ASD
Administration and either
discontinue Account Payable’s
SAP access for entering invoices
or, if not possible, create a
procedure that can identify
if/when an Accounts Payable
invoice is entered by an Accounts
Payable employee for supervisory
review.
1.b. Have Payroll redesign the existing
manual controls to mitigate
against the high‐risk areas of SoD
conflict identified.
Administrative
Services
Department
Agree.
Target Date: With new ERP.
Corrective Action Plan:
1a. Explore the possibility of
transferring the task of entering
Accounts Payable invoices to
ASD Administration.
1b. Explore having Payroll
redesign the existing manual
controls to mitigate against the
high‐risk areas of SoD conflict
identified in the new ERP.
Attachment A
16 ERP Planning: Separation of Duties
Recommendation
Responsible
Department(s)
Agree, Partially Agree, or Do
Not Agree and Target Date and
Corrective Action Plan
To be completed 6 months after Council acceptance and every 6 months
thereafter until all recommendations are implemented
Current Status
Implementation Update and
Expected Completion Date
1.c. Share with Utilities all relevant
SoD practices adopted, and
Utilities practices should be
consistent with that of ASD.
1c. Share with Utilities all
relevant SoD practices adopted,
and Utilities practices should be
consistent with that of ASD.
2. Information Technology revisit
the design and definition of
profiles and roles according to the
concept of least privilege, where
possible.
Information
Technology
Agree.
Target Date: June 30, 2020
Corrective Action Plan:
The plan is to review and
modify as appropriate the
approach to profiles and roles
during the design and
implementation phases of the
new ERP system. If it makes
sense timing wise, the new
design will be incorporated
back into the legacy system
during the project.
Determination of value and
cost in retrofitting to the legacy
system will be made during
design.
Attachment A
CITY OF PALO ALTO OFFICE OF THE CITY AUDITOR
October 23, 2018
The Honorable City Council
Palo Alto, California
Auditor's Office Quarterly Report as of September 30, 2018
RECOMMENDATION
The City Auditor’s Office recommends the Policy and Services Committee review and
recommend to the City Council acceptance of the Auditor’s Office Quarterly Report as of
September 30, 2018.
SUMMARY OF RESULTS
In accordance with the Municipal Code, the City Auditor prepares an annual work plan and
issues quarterly reports to the City Council describing the status and progress towards
completion of the work plan. This report provides the City Council with an update on the first
quarter for FY 2019.
Respectfully submitted,
Harriet Richardson
City Auditor
ATTACHMENTS:
• Attachment A: Auditor's Office Quarterly Report as of September 30, 2018 (PDF)
Department Head: Harriet Richardson, City Auditor
Page 2
Quarterly Report as of September 30, 2018
Office of the City Auditor
“Promoting honest, efficient, effective, economical, and fully
accountable and transparent city government.”
Attachment A
PAGE 2
Fiscal Year (FY) 2019 First Quarter Update (July – September 2018)
Overview
The audit function is essential to the City of Palo Alto’s public accountability. The mission of the Office of the City Auditor,
as mandated by the City Charter and Municipal Code, is to promote honest, efficient, effective, economical, and fully
accountable and transparent city government. We conduct performance audits and reviews to provide the City Council
and City management with information and evaluations regarding how effectively and efficiently resources are used; the
adequacy of internal control systems; and compliance with policies, procedures, and regulatory requirements. Taking
appropriate action on our audit recommendations helps the City reduce risks and protect its good reputation.
Activity Highlights
•Completed and submitted to the Policy & Services Committee the FY 2019 Annual Audit Work Plan.
•City Auditor Harriet Richardson provided internal control training to 12 City staff who manage federally funded
grants.
Audit and Project Work
Below is a summary of our audit and project work for the first quarter of FY 2019:
Title Objective(s) Start
Date
End
Date
Status Results/Comments
Business Registry Evaluate the rules and processes used
to establish the business registry and
make recommendations to help clean
up the data and ensure accuracy in the
future.
02/18 11/18 In Progress This audit was presented to
the Policy & Services
Committee in September
2018. The Committee
requested additional analysis,
which we will bring back to
the Committee in November
2018.
ERP Planning
Audit: Data
Reliability and
Integrity – Data
Standardization
This is the first in a series of audits that
focus on evaluating the integrity and
reliability of data in SAP and making
recommendations to ensure that
identified deficiencies are corrected
prior to transferring data to the new
ERP system. This audit focuses on
identifying where standardizing data
would be beneficial when
implementing the new ERP system
05/17 10/18 In Progress This audit is in the technical
review phase and will be
presented to the Policy &
Services Committee in
October 2018.
ERP Planning
Audit: Separation
of Duties
Evaluate the adequacy of separation of
duties for various activities in the
current SAP system and make
recommendations to ensure that
identified deficiencies are corrected for
the new ERP system.
05/17 10/18 In Progress This audit is in the technical
review phase and will be
presented to the Policy &
Services Committee in
October 2018.
Attachment A
PAGE 3
Title Objective(s) Start
Date
End
Date
Status Results/Comments
Code
Enforcement
Audit
Evaluate the timeliness and
effectiveness of code enforcement
actions, the effectiveness of
communication with the public, and the
accuracy and completeness of code
enforcement case tracking for decision
making purposes. We conducted a
resident survey to help inform our audit
recommendations, as described below.
05/17 11/18 In Progress The audit is in the technical
phase and will be presented to
the Policy & Services
Committee in November
2018.
ERP Planning
Audit: Data
Reliability and
Integrity –
Personnel Data
This is the second in a series of audits
that focus on evaluating the integrity
and reliability of data in SAP and
making recommendations to ensure
that identified deficiencies are
corrected prior to transferring data to
the new ERP system. This audit focuses
on assessing the accuracy of employee
master data, such as name, address,
birthdate, and social security number.
09/17 12/18 In Progress This audit is in the report
writing phase, and we expect
to present it to the Policy &
Services Committee in
December 2018.
NOTE: We previously referred to
this audit as “Human
Resources/Payroll Data” but
changed it to “Personnel Data”
for clarification.
ERP Planning
Audit: Data
Reliability and
Integrity –
Utilities
Customer Data
This is the third in a series of audits that
focus on evaluating the integrity and
reliability of data in SAP and making
recommendations to ensure that
identified deficiencies are corrected
prior to transferring data to the new
ERP system. This audit focuses on
assessing the accuracy of Utilities’
customer master data that is used for
billing purposes, such as customer
name, service and billing addresses,
and move-in and move-out dates.
06/18 2/19 In Progress This audit is in the field work
phase. We expect to present
the audit to the Policy &
Services Committee in
February 2019.
Mobile Device
Inventory and
Security
Determine if the City accurately
inventories and securely manages city-
owned mobile devices, including
laptops, tablets, cell/smart phones, and
radios.
03/18 2/19 In Progress This audit is in the field work
phase. We expect to present
the audit to the Policy &
Services Committee in
February 2019.
Nonprofit
Organizations
Audit
Evaluate whether nonprofit
organizations that receive City funding
are achieving the outcomes we expect
from the funding we provide. The audit
focuses primarily on nonprofit
organizations that provide senior
services.
06/18 02/19 In Progress This audit is in the field work
phase. We expect to complete
the audit in early 2019.
Attachment A
PAGE 4
Title Objective(s) Start
Date
End
Date
Status Results/Comments
Contract
Oversight
Select a sample of contracts to evaluate
the contract oversight process by
determining if the City has adequate
processes to ensure that the City
receives the goods and services it paid
for, that contracts did not result in
unnecessary overlaps in services, and
that contract extensions and change
orders were appropriate.
06/18 03/19 In Progress This audit is in the field work
phase. We expect to present
the audit to the Policy &
Services Committee in March
2019.
Transferable
Development
Rights
Determine if the City maintains an
accurate and complete record of the
transferable development rights (both
City-owned and non-City-owned) that
have been certified, transferred, and
used to date.
06/18 06/19 In Progress This audit is in the planning
phase. We have delayed
moving forward with this
audit until late 2018, after we
have completed other in-
progress audits. We expect to
complete the audit in mid-
2019.
ERP Nonaudit
Service
Provide advisory services to the
Department of Information Technology
regarding its planning of a new ERP
system.
09/16 N/A Ongoing We did not provide this
service during the first quarter
of FY 2019 and will resume
our service after the City
decides how to move forward
with a new ERP system. We
will then focus on assisting the
City with addressing the issues
we identified during the ERP
planning phase and prior
audits as it designs and
implements the new system.
National Citizen
Survey™
Obtain resident opinions about the
community and services provided by
the City of Palo Alto and benchmark
our results against other jurisdictions.
06/18 01/19 In Progress Survey was distributed to
4,500 residents in August
2018, with the final postmark
due date of September 21.
The National Research Center
is currently compiling the
results of the survey but has
provided a final count of 893
returned surveys, which is a
21 percent response rate after
subtracting the undeliverable
surveys.
Attachment A
PAGE 5
Other Monitoring and Administrative Assignments
Below is a summary of other assignments as of September 30, 2018:
Title Objective(s) Status Results/Comments
City Auditor
Advisory Roles
Provide guidance and advice to
key governance committees
within the City.
Ongoing The City Auditor serves as an advisor to the Utilities Risk
Oversight Committee and Information Security Steering
Committee. We are also serving as an advisor for the
strategic and technical planning groups for planning the
new ERP system (see comment in the Audit and Project
Work section above).
Sales and Use
Tax Allocation
Reviews
1)Identify businesses that do
business in Palo Alto that may
have underreported or misallo-
cated their sales and use tax and
submit inquiries to the state for
review and tax reallocation.
2)Monitor sales taxes received
from the Stanford University
Medical Center Project and
notify Stanford of any differences
between their reported taxes
and state sales tax information,
in accordance with the
development agreement.
3)Provide Quarterly Status
Updates and Sales Tax Digest
Summaries for Council review.
Ongoing 1)There were no sales or use tax recoveries for the first
quarter of FY 2019 from either our inquiries or the
vendor’s inquiries. However, due to processing delays at
the State Board of Equalization, 51 potential
misallocations are waiting to be researched and
processed: 24 from our office and 27 from the vendor.
2)We receive prior calendar-year sales tax information for
the Stanford Medicine development project several
months after the end of the calendar year and report
these on our June quarterly report. The City has received
$3,681,743 for calendar years 2011 through 2017 as a
result of the development agreement for this project.
3)Quarterly sales tax reports are published on the Office
of the City Auditor website at
www.cityofpaloalto.org/gov/depts/aud/reports/default.asp.
Status of Audit Recommendations
Fifty-one recommendations were open at the beginning of the first quarter of FY 2019, and seven were closed. We
added three recommendations during the quarter, which resulted in 47 recommendations open at the end of the
quarter. No status reports were presented during the quarter, and five are scheduled to be presented in October. Below
is a summary of the open audit recommendations as of September 30, 2018:
Audit Title and
Report Date
Due Date and
Prior Status
Report Dates
Total
Recommendations/
Number Open Summary of Open Recommendations
Citywide Cash
Handling and Travel
Expense
Issued 09/15/10
Scheduled for
10/18
03/21/18
08/22/17
11/10/15
09/23/14
09/10/13
10/22/12
04/19/11
Recommendations: 11
Open: 1
Implemented during
quarter: 0
•Review practice of reimbursing employee meals
when not in a travel status and report the amounts as
income to employees to conform to Internal Revenue
Service requirements (ASD)
Attachment A
PAGE 6
Audit Title and
Report Date
Due Date and
Prior Status
Report Dates
Total
Recommendations/
Number Open Summary of Open Recommendations
Inventory
Management
Issued 02/18/14
Scheduled for
10/18
11/02/17
09/23/14
Recommendations: 14
Open: 4
Implemented during
quarter: 0
• Implement City’s inventory management policies and
procedures (ASD/UTL/PWD/IT)
• Update and enforce inventory count policies and
procedures to ensure consistent and accurate
inventory records (ASD)
• Identify, formalize, and communicate inventory
management goals and objectives to City
departments (ASD)
• Ensure staff identify and use key SAP inventory
management reports and appropriately configure and
update SAP parameters that affect inventory levels
(ASD/IT)
Utility Meters:
Procurement,
Inventory, and
Retirement
Issued 03/10/15
Scheduled for
10/18
11/02/17
Recommendations: 15
Open: 0
Implemented during
quarter: 1
All recommendations are now closed.
Parking Funds
Issued 12/15/15
Due – 12/18
06/21/18
11/14/17
Recommendations: 8
Open: 1
Implemented during
quarter: 0
• Develop policies and procedures to clarify roles and
responsibilities and ensure accurate calculation and
reporting of parking-in-lieu fees (PCE, ASD, PWD, CLK)
Disability Rates and
Workers’
Compensation
Issued 05/10/16
Scheduled for
10/18
02/13/18
Recommendations: 15
Open: 4
Implemented during
quarter: 4
• Review departmental procedures and safety
requirements to ensure they align with citywide
policies and procedures (HR)
• Identify and provide industry-specific ergonomics and
general wellness training opportunities (HR)
• Address the disability leave benefits incorrectly
reported as compensation to CalPERS (HR)
• Ensure that data for managing disability leave is
accurately captured through SAP time reporting (HR)
Attachment A
PAGE 7
Audit Title and
Report Date
Due Date and
Prior Status
Report Dates
Total
Recommendations/
Number Open Summary of Open Recommendations
Cable Franchise and
Public, Education,
and Government
(PEG) Fees
Issued 06/14/16
Scheduled for
10/18
03/21/18
08/22/17
Recommendations: 9
Open: 6
Implemented during
quarter: 0
•Assess ongoing need for PEG fees; place fees in
restricted account until decisions are made about use
of fees (CMO/ATTY/ASD/IT)
•Determine whether to allocate unrestricted funds,
instead of PEG fees, to subsidize the Media Center’s
operations. (CMO/ATTY/ASD/IT)
•Send letters to cable companies to demand payment
of underpaid franchise and PEG fees
(CMO/ATTY/ASD/IT)
•Develop criteria for assessing the accuracy of future
cable franchise and PEG fee payments and require
more detail with payment remittances (ASD)
•Assign responsibility for the cable communications
program and provide effective oversight of the
program (CMO/CLK)
•Draft an ordinance to update the Palo Alto Municipal
Code based on clarified assignment of responsibility
(CMO/ASD/ATTY/CLK)
Community Services
Department (CSD):
Fee Schedule Audit
Issued 02/14/17
Due – 12/18
06/21/18
11/14/17
Recommendations: 3
Open: 2
Implemented during
quarter: 0
•Revise City’s cost recovery policy to align with
relevant laws and reconfigure the Questica budget
system to support fees that recover more than
100 percent of costs (ASD)
•Configure SAP or the new ERP system to align cost
centers with CSD programs (CSD)
Continuous
Monitoring:
Payments
Issued 04/13/17
Scheduled for
10/18
03/21/18
Recommendations: 7
Open: 3
Implemented during
quarter: 2
•Build a continuous monitoring process into the new
ERP system to identify potential duplicate invoices
and seek recovery of duplicate payments (ASD)
•Build a continuous monitoring process into the new
ERP system to identify duplicate, incomplete, or
unused vendor records (ASD)
•Clean vendor master file before merging data into
new ERP system (ASD)
Attachment A
PAGE 8
Audit Title and
Report Date
Due Date and
Prior Status
Report Dates
Total
Recommendations/
Number Open Summary of Open Recommendations
Green Purchasing
Practices
Issued 04/13/17
Due – 12/18
06/21/18
Recommendations: 8
Open: 6
Implemented during
quarter: 0
•Clearly define department(s) responsible for
implementing green purchasing policies and
determine if additional staffing and funding are
needed to implement the policies (ASD/CMO)
•Develop consolidated procedures to implement green
purchasing policies (CMO/ASD/PWD)
•Educate City staff on green purchasing policies (ASD)
•Evaluate potential for use of 40 percent post-
consumer fiber paper towels/other green janitorial
products and monitor janitorial contractor’s
compliance with green purchasing requirements
(PWD)
•Evaluate if new e-procurement system or other
technology solution can help with tracking and
reporting green purchases and establish appropriate
green purchasing performance measures (ASD/PWD)
•Require vendors to provide data on amounts of green
products and services that City purchases from them
(ASD/PWD)
Utilities
Department: Cross
Bore Inspection
Contract
Issued 06/01/17
Due – 12/18
06/21/18
Recommendations: 4
Open: 4
Implemented during
quarter: 0
•Prioritize uninspected sewer pipelines for inspection
and disclose potential inspection challenges in future
contract solicitations (UTL)
•Identify and update missing data in laterals database
(UTL)
•Incorporate relevant provisions from National
Association of Sewer Service Companies’ contract
template in future sewer inspection contracts (UTL)
•Identify gaps in staff expertise and develop a training
and certification plan for field staff who will monitor
field inspections (UTL)
Accuracy of Water
Meter Billing
Issued 08/16/17
Due – 12/18
06/21/18
Recommendations: 11
Open: 7
Implemented during
quarter: 0
•Review and correct meter records for meters larger
than 2 inches (UTL)
•Explore options for addressing equity in meter size
rates (UTL)
•Develop a policy and procedures to report significant,
systemic infrastructure changes to Council and
update City of Palo Alto Utilities’ (CPAU) Rules and
Regulations as needed (UTL)
•Seek direction from Council before proceeding with
installing additional electronic meters (UTL)
•Determine if installed eMeters should be replaced
and if billing adjustments are required (UTL)
•Clarify purchasing policy and procedures for product
standardization and sole source (ASD)
•Retrain staff on purchasing policies and procedures
and completion of required forms (ASD)
Attachment A
PAGE 9
Audit Title and
Report Date
Due Date and
Prior Status
Report Dates
Total
Recommendations/
Number Open Summary of Open Recommendations
Continuous
Monitoring:
Overtime
Issued 09/06/17
Due – 12/18
None
Recommendations: 2
Open: 2
Implemented during
quarter: 0
• Explore potential of developing a continuous
monitoring process for overtime (ASD)
• Form a work group to design standardized overtime
management processes in the new ERP environment
(ASD)
Information
Technology and
Data Governance
Issued on 06/13/18
Due – 2/19
None
Recommendations: 4
Open: 4
Implemented during
quarter: 0
• Assign roles and responsibilities for IT governance to
ensure that governance coveralls all key aspects of
the City’s information systems (IT)
• Adopt an industry standard IT governance framework
and create a plan to achieve a process capability
model of “established” or higher (IT)
• Assign roles and responsibilities for data governance
to ensure that governance coveralls all key aspects of
the City’s information systems (IT)
• Adopt an industry standard data governance
framework and create a plan to achieve a process
capability model of “established” or higher (IT)
Business Registry
Issued on 08/28/18
Will be
determined
after Council
acceptance
Recommendations: 3
Open: 3
Implemented during
quarter: 0
• Clarify existing and potential uses and priorities for
business registry data and update questions in
business registry survey as appropriate (CMO, DSD,
PCE, Trans)
• Identify external data that can improve accuracy of
data collected and provide the data to the business
registry consultant (DSD)
• Update business registry administrative manual to
reflect current process, including roles and
responsibilities (DSD and Attorney)
7
0 0 60
1
2
3
4
5
6
7
8
Q1 Q2 Q3 Q4
Number of Implemented
Recommendations by Quarter
16
15
13
3
0
20
40
60 Number of Open Recommendations
FY 19
FY 18
FY 17
Prior Fiscal Years
Attachment A
PAGE 10
Open Recommendations by Audit Issuance Date
Fiscal
Year
Audit Title Number of Open
Recommendations
2011 Citywide Cash Handling and Travel Expense 1 of 11
2014 Inventory Management 4 of 14
2016 Parking Funds 1 of 8
Cable Franchise and Public, Education, and Government (PEG) Fees 6 of 9
Disability Rates and Workers’ Compensation 4 of 15
2017 Community Services Department: Fee Schedule 2 of 3
Continuous Monitoring: Payments 3 of 7
Green Purchasing Practices 6 of 8
Utilities Department: Cross Bore Inspection Contract 4 of 4
2018 Accuracy of Water Meter Billing 7 of 11
Continuous Monitoring: Overtime 2 of 2
Information Technology and Data Governance 4 of 4
2019 Business Registry 3 of 3
Fraud, Waste, and Abuse Hotline Administration
The hotline review committee, composed of the City Auditor, the City Attorney, and the City Manager, or their
designees, meets as needed to review hotline-related activities. One complaint was received during the first quarter of
FY 2019, which is still open pending receipt of additional information from complainant. All prior-year complaints have
been closed. The chart below summarizes the status of complaints received in each fiscal year since the hotline was
implemented.
1
7
3 2
13
9
00
2
4
6
8
10
12
14
FY 2013 FY 2014 FY 2015 FY 2016 FY 2017 FY 2018 FY 2019
Status of Complaints Received by Fiscal Year
Closed Complaints
Open Complaints
Attachment A