Loading...
HomeMy WebLinkAboutStaff Report 10166 City of Palo Alto (ID # 10166) Policy and Services Committee Staff Report Report Type: Action Items Meeting Date: 6/11/2019 City of Palo Alto Page 1 Summary Title: Status Updates of Audit Recommendations for Cable, Cash Handling and Continuous Monitoring Audits Title: Staff Recommends the Policy and Services Committee Recommend the City Council Accept the Status Updates of the Audits of; 1) Citywide Cash Handling and Travel Expense; 2) Cable Franchise and PEG Fees; 3) Continuous Monitoring: Overtime; 4) Continuous Monitoring: Payments; and 4) 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; Continuous Monitoring: Overtime; 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; Continuous Monitoring: Overtime; and Inventory Management. Staff has provided status updates on the audits in Attachments A-E. Of the remaining recommendations six are complete and ten are in progress or not started. The open recommendations include four that are linked to the future upgrade of the SAP enterprise resource planning (ERP) system. In the Cable PEG fees audit, two recommendations are linked to the upcoming agreements between the City and the Media Center to use PEG fees to purchase the Media Center building. Details of all recommendations can be found in the attachments. Attachments: • Attachment A: Overtime Audit • Attachment B: Cable Audit • Attachment C: Cash Handling Audit City of Palo Alto Page 2 • Attachment D: Inventory Audit • Attachment E: Payments Audit ATTACHMENT A STATUS OF AUDIT RECOMMENDATIONS CONTINUOUS MONITORING: OVERTIME – ISSUED 9/6/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: Implementing a continuous monitoring process for overtime in the new Enterprise Resource Planning (ERP) environment can help the City improve its resource allocation and utilization. 1. Explore the potential of developing a continuous monitoring process to provide more detailed information on overtime usage so that management can better manage and control overtime costs. A continuous monitoring system could include data analytics to extract data on service demands, absences and vacancies, and elements of city policies and contractual requirement that could be useful in identifying opportunities to reduce overtime costs. ASD Concurrence: Agree Target Date: 4th Quarter 2018 Action Plan: ASD will work with departments to explore the potential of developing a continuous monitoring process for overtime. In Progress April 2019 Management Update: Staff is working with departments to explore the potential of developing a continuous monitoring process for overtime. Expected Completion Date: 4th Quarter 2019 2. Form a work group consisting of main end users and stakeholders to design shared system capabilities and standardized overtime management processes in the new ERP environment by: • Identifying useful overtime data including their source, and user interface (e.g., dashboard with drilldown and reporting capabilities) that allow users to analyze pertinent overtime factors shown in Appendix 1 in a ASD Concurrence: Agree Target Date: 4th Quarter 2018 Action Plan: ASD will work with departments to determine shared needs for overtime in the new ERP system. Staff is currently evaluating ERP solutions for payroll and core financials and staff has communicated the important of overtime reporting to the vendors. Once an ERP solution is selected staff will finalize overtime reporting requirements and build those requirements into the new ERP system with the implementation consultants. Not Started April 2019 Management Update: The City will be upgrading the current SAP environment per CMR 9826 rather than implement a new ERP. As part of that project ASD will work with departments to determine shared needs for overtime in the upgrade, as new system capabilities allow. Expected Completion Date: TBD ATTACHMENT A STATUS OF AUDIT RECOMMENDATIONS CONTINUOUS MONITORING: OVERTIME – ISSUED 9/6/17 Recommendation Responsible Department(s) Original Target Date and Response Current Status Status comprehensive manner. • Identifying manual data collection and auxiliary processes (e.g., billing, reimbursement) that can be automated. • Reviewing applicable ERP system requirements to ensure needed capabilities are included in the City’s ERP contract. 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 April 2019 Management Update: On 1/28/2019 Council directed staff to negotiate agreements to purchase the Media Center building with PEG fee revenue (CMR 9804). The City is working through development of the transaction terms with the Media Center and the Cable Joint Powers partners, and will return to Council for approvals. Expected Completion Date: 4QTR 2019 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. 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. 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 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 April 2019 Management Update: On 1/28/2019 Council directed staff to negotiate agreements to purchase the Media Center building with PEG fee revenue (CMR 9804). The City is working through development of the transaction terms with the Media Center and the Cable Joint Powers partners, and will return to Council for approvals. 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: 4QTR 2019 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. 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 City Manager, ASD, IT, City Attorney’s Concurrence: Agree Target Date: 4Q 2016 Action Plan: Complete April 2019 Management Update: On 11/13/18 staff reached a global settlement with Comcast in the amount of $25,000. 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 shown in Exhibit 4, plus interest calculated in accordance with DIVCA requirements. Office 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. 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 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 Complete April 2019 Management Update: Comcast and AT&T have adopted standard company-wide reporting formats, providing a breakdown of franchise and PEG fee calculations. Staff will monitor and follow-up on any material variances identified in these reports. Neither company is able, or willing, to prepare a special set of reports for the Cable Joint Powers. 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 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 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. 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 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 City Manager’s Office, City Clerk Concurrence: Agree Target Date: 4Q 2016 Action Plan: Staff will determine where to assign 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. In Progress April 2019 Management Update: The Administrative Services Department assumed responsibility for the City’s cable communications program on an interim basis. A permanent placement will be identified after the audit findings are fully implemented. Expected Completion Date: TBD 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 completes the implementation of the audit Attachment B STATUS OF AUDIT RECOMMENDATIONS CABLE FRANCHISE AND PEG FEE AUDIT – ISSUED 6/14/16 PAGE 6 Recommendation Responsible Department(s) Original Target Date and Response Current Status Implementation Update and Expected Completion Date 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. 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 April 2019 Management Update: Staff plans to propose revisions to the Municipal Code after it determines where final responsibility for the City’s cable communications program will reside. Expected Completion Date: TBD 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 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 April 2019 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: 4QTR 2019 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 Attachment C 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 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 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 D 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. Completed June 2019 Management Update: Utilities’ staff has taken corrective measures and are adhering to ASD’s inventory management policies and procedures. Since the audit, Utilities and ASD staff has worked together to significantly reduced inventory (valued over $100) held in the storage sheds by returning materials to the warehouse. Utilities has established a Meter and Specification Approval procedure for Engineering, Operations, and ASD-Purchasing regarding additions and deletions of meter master records and specifications. Staff members are currently practicing procedures to properly ensure meter specification changes are communicated among staff. In addition, Utilities plans to deploy a mobile application to track emergency and spare parts (greater than $100 per unit) that are stored in storage sheds and substations. 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- Attachment D 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 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 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 Attachment D 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 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 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.” 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 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. Complete April 2019 Management Update: RWQCP has implemented the City’s stores/inventory policy and procedures to the extent feasible. Segregation of duties at the RWQCP has been achieved through various restrictions of access to the warehouse. Requests from the warehouse are logged in a computer using personal logon access at the front counter and orders are filled and placed in a designated location. RWQCP warehouse cycle counts are generated randomly and reviewed by the Warehouse Supervisor. 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. Attachment D 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 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 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 Attachment D 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 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 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 Attachment D 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 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. Completed Utilities’ staff has taken corrective measures and are adhering to ASD’s inventory management policies and procedures. Since the audit, Utilities and ASD staff has worked together to significantly reduced inventory (valued over $100) held in the storage sheds by returning materials to the warehouse. In addition, Utilities plans to deploy a mobile application to track emergency and spare parts (greater than $100 per unit) that are stored in storage sheds and substations. 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 Attachment D 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 information reporting, and compliance with laws and regulations. Utilities staff routinely monitors inventory to manage appropriate classification of maintenance, emergency and 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. 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 Complete April 2019 Management Update: Staff has completed the process of identifying, reviewing and updating these categories for all materials. September 2018 Management Update: Staff is in the process of identifying, reviewing and updating these categories for all materials. Attachment D STATUS OF AUDIT RECOMMENDATIONS INVENTORY MANAGEMENT – ISSUED 12/31/13 PAGE 8 Recommendation Responsible Department Original Target Date and Response Current Status Implementation Update and Expected Completion Date • Appropriately configures and updates SAP parameters affecting inventory levels. these reports will be included by Target Date. • Parameters affecting inventory levels will be explored and updated as needed. 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 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 Attachment D STATUS OF AUDIT RECOMMENDATIONS INVENTORY MANAGEMENT – ISSUED 12/31/13 PAGE 9 Recommendation Responsible Department Original Target Date and Response Current Status Implementation Update and Expected Completion Date 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 Attachment E 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 requirements we have seen an increase in Not Started April 2019 Management Update: The City will be upgrading the current SAP environment per CMR 9826 rather than implement a new ERP. Duplicate payment detection will be implemented in the upcoming upgrade to the current SAP environment. Target Completion Date: TBD (date of ERP upgrade) 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. Attachment E STATUS OF AUDIT RECOMMENDATIONS CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17 Recommendation Responsible Department(s) Original Target Date and Response Current Status Status 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. 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) 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. 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 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. 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. 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. Not Started April 2019 Management Update: The City will be upgrading the current SAP environment per CMR 9826 rather than implement a new ERP. Duplicate payment detection will be implemented in the upcoming upgrade to the current SAP environment. Target Completion Date: TBD (date of ERP upgrade) September 2018 Management Update: See below, no new update. Expected Completion Date: March 2018 Management Update: 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. Attachment E STATUS OF AUDIT RECOMMENDATIONS CONTINUOUS MONITORING: PAYMENTS – ISSUED 4/3/17 Recommendation Responsible Department(s) Original Target Date and Response Current Status Status Expected Completion Date: TBD (date of ERP implementation) 2.2. 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 April 2019 Management Update: The City will be upgrading the current SAP environment per CMR 9826 rather than implement a new ERP. The vendor master file will be cleaned as part of the upcoming upgrade to the current SAP environment. Target Completion Date: TBD (date of ERP upgrade) 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