The URL can be used to link to this page

Home My WebLink About2023-02-28 Policy & Services Committee Agenda PacketPOLICY AND SERVICES COMMITTEE Special Meeting Tuesday, February 28, 2023 Community Meeting Room & Hybrid 7:00 PM Pursuant to AB 361 Palo Alto City Council meetings will be held as “hybrid” meetings with the option to attend by teleconference/video conference or in person. To maximize public safety while still maintaining transparency and public access, members of the public can choose to participate from home or attend in person. Information on how the public may observe and participate in the meeting is located at the end of the agenda. Masks are strongly encouraged if attending in person. The meeting will be broadcast on Cable TV Channel 26, live on YouTube https://www.youtube.com/c/cityofpaloalto, and streamed to Midpen Media Center https://midpenmedia.org. VIRTUAL PARTICIPATION CLICK HERE TO JOIN (https://cityofpaloalto.zoom.us/j/94618744621) Meeting ID: 946 1874 4621 Phone: 1(669)900‐6833 PUBLIC COMMENTS Public comments will be accepted both in person and via Zoom for up to three minutes or an amount of time determined by the Chair. All requests to speak will be taken until 5 minutes after the staff’s presentation. Written public comments can be submitted in advance to city.council@CityofPaloAlto.org and will be provided to the Council and available for inspection on the City’s website. Please clearly indicate which agenda item you are referencing in your subject line. PowerPoints, videos, or other media to be presented during public comment are accepted only by email to city.clerk@CityofPaloAlto.org at least 24 hours prior to the meeting. Once received, the Clerk will have them shared at public comment for the specified item. To uphold strong cybersecurity management practices, USB’s or other physical electronic storage devices are not accepted. CALL TO ORDER PUBLIC COMMENT Members of the public may speak to any item NOT on the agenda. ACTION ITEMS 1.Office of the City Auditor Presentation of the Utility Work Order Process and Control Review Report Presentation 2.Approval of Office of City Auditor FY2023 Task Orders Presentation FUTURE MEETINGS AND AGENDAS Members of the public may not speak to the item(s) ADJOURNMENT PUBLIC COMMENT INSTRUCTIONS Members of the Public may provide public comments to teleconference meetings via email, teleconference, or by phone. 1. Written public comments may be submitted by email to city.council@cityofpaloalto.org. 2. For in person public comments please complete a speaker request card located on the table at the entrance to the Council Chambers and deliver it to the Clerk prior to discussion of the item. 3. Spoken public comments using a computer or smart phone will be accepted through the teleconference meeting. To address the Council, click on the link below to access a Zoom‐based meeting. Please read the following instructions carefully. You may download the Zoom client or connect to the meeting in‐ browser. If using your browser, make sure you are using a current, up‐to‐date browser: Chrome 30 , Firefox 27 , Microsoft Edge 12 , Safari 7 . Certain functionality may be disabled in older browsers including Internet Explorer. Or download the Zoom application onto your smart phone from the Apple App Store or Google Play Store and enter in the Meeting ID below. You may be asked to enter an email address and name. We request that you identify yourself by name as this will be visible online and will be used to notify you that it is your turn to speak. When you wish to speak on an Agenda Item, click on “raise hand.” The Clerk will activate and unmute speakers in turn. Speakers will be notified shortly before they are called to speak. When called, please limit your remarks to the time limit allotted. A timer will be shown on the computer to help keep track of your comments. 4. Spoken public comments using a phone use the telephone number listed below. When you wish to speak on an agenda item hit *9 on your phone so we know that you wish to speak. You will be asked to provide your first and last name before addressing the Council. You will be advised how long you have to speak. When called please limit your remarks to the agenda item and time limit allotted. CLICK HERE TO JOIN Meeting ID: 946‐1874‐4621 Phone: 1‐669‐900‐6833 Americans with Disability Act (ADA) It is the policy of the City of Palo Alto to offer its public programs, services and meetings in a manner that is readily accessible to all. Persons with disabilities who require materials in an appropriate alternative format or who require auxiliary aids to access City meetings, programs, or services may contact the City’s ADA Coordinator at (650) 329‐2550 (voice) or by emailing ada@cityofpaloalto.org. Requests for assistance or accommodations must be submitted at least 24 hours in advance of the meeting, program, or service. 1 Regular Meeting February 28, 2023 Materials related to an item on this agenda submitted to the Board after distribution of the agenda packet are POLICY AND SERVICES COMMITTEESpecial MeetingTuesday, February 28, 2023Community Meeting Room & Hybrid7:00 PMPursuant to AB 361 Palo Alto City Council meetings will be held as “hybrid” meetings with theoption to attend by teleconference/video conference or in person. To maximize public safetywhile still maintaining transparency and public access, members of the public can choose toparticipate from home or attend in person. Information on how the public may observe andparticipate in the meeting is located at the end of the agenda. Masks are strongly encouraged ifattending in person. The meeting will be broadcast on Cable TV Channel 26, live onYouTube https://www.youtube.com/c/cityofpaloalto, and streamed to Midpen MediaCenter https://midpenmedia.org.VIRTUAL PARTICIPATION CLICK HERE TO JOIN (https://cityofpaloalto.zoom.us/j/94618744621)Meeting ID: 946 1874 4621 Phone: 1(669)900‐6833PUBLIC COMMENTSPublic comments will be accepted both in person and via Zoom for up to three minutes or anamount of time determined by the Chair. All requests to speak will be taken until 5 minutesafter the staff’s presentation. Written public comments can be submitted in advance tocity.council@CityofPaloAlto.org and will be provided to the Council and available for inspectionon the City’s website. Please clearly indicate which agenda item you are referencing in yoursubject line. PowerPoints, videos, or other media to be presented during public comment are accepted only by email to city.clerk@CityofPaloAlto.org at least 24 hours prior to the meeting. Once received, the Clerk will have them shared at public comment for the specified item. To uphold strong cybersecurity management practices, USB’s or other physical electronic storage devices are not accepted. CALL TO ORDER PUBLIC COMMENT Members of the public may speak to any item NOT on the agenda. ACTION ITEMS 1.Office of the City Auditor Presentation of the Utility Work Order Process and Control Review Report Presentation 2.Approval of Office of City Auditor FY2023 Task Orders Presentation FUTURE MEETINGS AND AGENDAS Members of the public may not speak to the item(s) ADJOURNMENT PUBLIC COMMENT INSTRUCTIONS Members of the Public may provide public comments to teleconference meetings via email, teleconference, or by phone. 1. Written public comments may be submitted by email to city.council@cityofpaloalto.org. 2. For in person public comments please complete a speaker request card located on the table at the entrance to the Council Chambers and deliver it to the Clerk prior to discussion of the item. 3. Spoken public comments using a computer or smart phone will be accepted through the teleconference meeting. To address the Council, click on the link below to access a Zoom‐based meeting. Please read the following instructions carefully. You may download the Zoom client or connect to the meeting in‐ browser. If using your browser, make sure you are using a current, up‐to‐date browser: Chrome 30 , Firefox 27 , Microsoft Edge 12 , Safari 7 . Certain functionality may be disabled in older browsers including Internet Explorer. Or download the Zoom application onto your smart phone from the Apple App Store or Google Play Store and enter in the Meeting ID below. You may be asked to enter an email address and name. We request that you identify yourself by name as this will be visible online and will be used to notify you that it is your turn to speak. When you wish to speak on an Agenda Item, click on “raise hand.” The Clerk will activate and unmute speakers in turn. Speakers will be notified shortly before they are called to speak. When called, please limit your remarks to the time limit allotted. A timer will be shown on the computer to help keep track of your comments. 4. Spoken public comments using a phone use the telephone number listed below. When you wish to speak on an agenda item hit *9 on your phone so we know that you wish to speak. You will be asked to provide your first and last name before addressing the Council. You will be advised how long you have to speak. When called please limit your remarks to the agenda item and time limit allotted. CLICK HERE TO JOIN Meeting ID: 946‐1874‐4621 Phone: 1‐669‐900‐6833 Americans with Disability Act (ADA) It is the policy of the City of Palo Alto to offer its public programs, services and meetings in a manner that is readily accessible to all. Persons with disabilities who require materials in an appropriate alternative format or who require auxiliary aids to access City meetings, programs, or services may contact the City’s ADA Coordinator at (650) 329‐2550 (voice) or by emailing ada@cityofpaloalto.org. Requests for assistance or accommodations must be submitted at least 24 hours in advance of the meeting, program, or service. 2 Regular Meeting February 28, 2023 Materials related to an item on this agenda submitted to the Board after distribution of the agenda packet are POLICY AND SERVICES COMMITTEESpecial MeetingTuesday, February 28, 2023Community Meeting Room & Hybrid7:00 PMPursuant to AB 361 Palo Alto City Council meetings will be held as “hybrid” meetings with theoption to attend by teleconference/video conference or in person. To maximize public safetywhile still maintaining transparency and public access, members of the public can choose toparticipate from home or attend in person. Information on how the public may observe andparticipate in the meeting is located at the end of the agenda. Masks are strongly encouraged ifattending in person. The meeting will be broadcast on Cable TV Channel 26, live onYouTube https://www.youtube.com/c/cityofpaloalto, and streamed to Midpen MediaCenter https://midpenmedia.org.VIRTUAL PARTICIPATION CLICK HERE TO JOIN (https://cityofpaloalto.zoom.us/j/94618744621)Meeting ID: 946 1874 4621 Phone: 1(669)900‐6833PUBLIC COMMENTSPublic comments will be accepted both in person and via Zoom for up to three minutes or anamount of time determined by the Chair. All requests to speak will be taken until 5 minutesafter the staff’s presentation. Written public comments can be submitted in advance tocity.council@CityofPaloAlto.org and will be provided to the Council and available for inspectionon the City’s website. Please clearly indicate which agenda item you are referencing in yoursubject line.PowerPoints, videos, or other media to be presented during public comment are accepted onlyby email to city.clerk@CityofPaloAlto.org at least 24 hours prior to the meeting. Once received,the Clerk will have them shared at public comment for the specified item. To uphold strongcybersecurity management practices, USB’s or other physical electronic storage devices are notaccepted.CALL TO ORDERPUBLIC COMMENT Members of the public may speak to any item NOT on the agenda. ACTION ITEMS1.Office of the City Auditor Presentation of the Utility Work Order Process and ControlReview Report Presentation2.Approval of Office of City Auditor FY2023 Task Orders PresentationFUTURE MEETINGS AND AGENDASMembers of the public may not speak to the item(s) ADJOURNMENT PUBLIC COMMENT INSTRUCTIONS Members of the Public may provide public comments to teleconference meetings via email, teleconference, or by phone. 1. Written public comments may be submitted by email to city.council@cityofpaloalto.org. 2. For in person public comments please complete a speaker request card located on the table at the entrance to the Council Chambers and deliver it to the Clerk prior to discussion of the item. 3. Spoken public comments using a computer or smart phone will be accepted through the teleconference meeting. To address the Council, click on the link below to access a Zoom‐based meeting. Please read the following instructions carefully. You may download the Zoom client or connect to the meeting in‐ browser. If using your browser, make sure you are using a current, up‐to‐date browser: Chrome 30 , Firefox 27 , Microsoft Edge 12 , Safari 7 . Certain functionality may be disabled in older browsers including Internet Explorer. Or download the Zoom application onto your smart phone from the Apple App Store or Google Play Store and enter in the Meeting ID below. You may be asked to enter an email address and name. We request that you identify yourself by name as this will be visible online and will be used to notify you that it is your turn to speak. When you wish to speak on an Agenda Item, click on “raise hand.” The Clerk will activate and unmute speakers in turn. Speakers will be notified shortly before they are called to speak. When called, please limit your remarks to the time limit allotted. A timer will be shown on the computer to help keep track of your comments. 4. Spoken public comments using a phone use the telephone number listed below. When you wish to speak on an agenda item hit *9 on your phone so we know that you wish to speak. You will be asked to provide your first and last name before addressing the Council. You will be advised how long you have to speak. When called please limit your remarks to the agenda item and time limit allotted. CLICK HERE TO JOIN Meeting ID: 946‐1874‐4621 Phone: 1‐669‐900‐6833 Americans with Disability Act (ADA) It is the policy of the City of Palo Alto to offer its public programs, services and meetings in a manner that is readily accessible to all. Persons with disabilities who require materials in an appropriate alternative format or who require auxiliary aids to access City meetings, programs, or services may contact the City’s ADA Coordinator at (650) 329‐2550 (voice) or by emailing ada@cityofpaloalto.org. Requests for assistance or accommodations must be submitted at least 24 hours in advance of the meeting, program, or service. 3 Regular Meeting February 28, 2023 Materials related to an item on this agenda submitted to the Board after distribution of the agenda packet are 1 5 8 3 Policy & Services Committee Staff Report From: Chantal Gaines, Deputy City Manager Lead Department: City Auditor Meeting Date: February 28, 2023 Report #: 2301-0828 TITLE Office of the City Auditor Presentation of the Utility Work Order Process and Control Review Report BACKGROUND Baker Tilly, in its capacity serving as the Office of the City Auditor (OCA), performed a citywide risk assessment that assessed a wide range of risk areas, including strategic, financial, operational, compliance, technological, and reputation risks. The purpose of the assessment was to identify and prioritize risks to develop the annual audit plan. During the FY2021 risk assessment, the OCA identified a utility risk related to work order processes. With customers’ utility needs, it is vital that work order processes operate optimally. DISCUSSION The City of Palo Alto Utilities (CPAU) offers its residents and businesses a suite of utility services, including electricity, natural gas, water, and sanitary sewer (wastewater). All four utilities have a similar organizational structure around the work order process which includes designers, engineers, project utility coordinators, operations crew, and accountants who all play a vital role in ensuring that utility services are provided in a manner consistent with CPAU’s mission. Utility work orders include capital and customer work orders as well as maintenance jobs. The electric utility follows a slightly different process and uses different software systems than the water, gas, and wastewater utilities. The objectives of the Utility Work Order Process Review were to: 1) Determine whether adequate controls around the work order process are in place and working effectively 2) Assess the work order process against best practices 1 Packet Pg. 4 1 5 8 3 To evaluate work order controls, audit testing was conducted on a sample of electric, water, gas and wastewater work orders. The attached report summarizes the analysis, audit findings, and recommendations. FISCAL/RESOURCE IMPACT The Office of the City Auditor worked primarily with Utilities Department and engaged with additional stakeholders, including the City Manager’s Office and the City Attorney’s Office, as necessary. The timeline for implementation of corrective action plans is identified within the attached report. ATTACHMENTS Attachment A: 1 Packet Pg. 5 1 City of Palo Alto City Auditor's Office Utility Work Order Process & Control Review August 9, 2022 1 Packet Pg. 6 2 Executive Summary Purpose of the Audit The purpose of the audit was to gain an understating of the City's utility work order processes, evaluate the effectiveness of relevant internal controls and evaluate the City’s utility work order processes against best practices. Report Highlights Baker Tilly performed an analysis of the utilities work order processes and controls through sample-based testing, review of policy and procedure documentation and walkthroughs with key process owners. The “Audit Results” section of this report shows the detailed observations and recommendations based on our review. The following table highlights the four main recommendations (major themes) of these observations and recommendations. Note: In practice, the City generally refers to capital and customer work orders as “service orders” and maintenance jobs as “work orders”. Throughout this report we will refer to both as “work orders” and will identify if we are only referring to one or the other. Category/Theme Page Recommendation Internal Approvals Pg. 7, 8 We recommend that all work orders (including O&M) be approved by someone that has budget responsibility or the ability to approve unbudgeted maintenance work. Any design changes made after the initial approval should be approved by an engineer or supervisor as changes in the field are a safety issue for service and could impact other areas that the field crew may not be aware of. Once the work order is complete, the supervisor should review all labor, materials, and 3rd party invoices for accuracy and ensure any as-built documentation is correct. All of the above reviews and approvals should be evidenced in writing. System Utilization Pg. 8 We recommend the electric utility consider evaluating whether SAP has the capability to effectively track work orders to avoid using side systems. The electric utility should also evaluate whether implementing SoGen, the system used by the water, gas, and wastewater utilities to track work orders, would allow for easier, more accurate work order tracking. A system with the ability to schedule work orders based on priority will also ensure there is not unnecessary downtime or overburdening of worker time. Palo Alto utilities should also consider developing an interface between the handheld devices used by the crew in the field and SoGen to eliminate duplicate processes and allow stakeholders access to the most up-to-date information as changes are being made in real time. Recording & Reconciling Assets Pg. 7, 8 We recommend work orders be closed and capitalized monthly to prevent a backlog and to ensure depreciation starts immediately when the asset is placed in service. The utilities should perform a full system reconciliation of assets in AME and SoGen to ensure assets are accurately recorded. Assets in AME and SoGen should continue to be reconciled on an annual basis (or cycle counts can be performed monthly where a certain type of asset is counted each month). In addition, all reconciliations of the assets under construction account and asset accounts should be performed on a monthly basis to ensure monthly financials are accurate. Key Performance Indicators Pg. 8 We recommend that the electric utility develop and track maintenance and construction KPIs similar to those tracked by the water, wastewater, and gas utilities. In addition, all utilities should consider developing additional KPIs as identified in Appendix D. 1 Packet Pg. 7 3 Table of Contents Executive Summary ........................................................................................................................................................... 2 Purpose of the Audit ..................................................................................................................................................... 2 Report Highlights ........................................................................................................................................................... 2 Objective........................................................................................................................................................................... 4 Background ..................................................................................................................................................................... 4 Scope................................................................................................................................................................................. 4 Compliance Statement.................................................................................................................................................. 4 Detailed Testing & Analysis ........................................................................................................................................ 5 Methodology & Approach ............................................................................................................................................ 5 Audit Testing ................................................................................................................................................................... 6 Audit Results ....................................................................................................................................................................... 7 Appendices .......................................................................................................................................................................... 9 Appendix A: Process Flowcharts .............................................................................................................................. 9 Appendix B: Sampled Work Orders ........................................................................................................................ 15 Appendix C: Current KPI’s for Water, Wastewater, and Gas – Selected Examples ................................... 16 Appendix D: Potential KPIs for Consideration ..................................................................................................... 19 Appendix E: Management Response ...................................................................................................................... 20 1 Packet Pg. 8 4 Introduction 1 Government auditing standards require an external peer review at least once every three (3) years. The last peer review of the Palo Alto Office of the City Auditor was conducted in 2017. The Palo Alto City Council approved a contract from October 2020 through June 2022 with Baker Tilly US, LLP (Baker Tilly) and appointed Kyle O’Rourke, Senior Consulting Manager in Baker Tilly's Public Sector practice, as City Auditor. Given the transition in the City Audit office, a peer review was not conducted in 2020 and will be conducted after the third year of Baker Tilly’s contract. 0BObjective The purpose of the audit was to gain an understating of the City's utility work order processes, evaluate the effectiveness of relevant internal controls and evaluate the City’s utility work order processes against best practices. 1BBackground The City of Palo Alto Utilities (CPAU) offers its residents and businesses a suite of utility services, including electricity, natural gas, water, and sanitary sewer. The CPAU's mission statement is to "provide safe, reliable, environmentally sustainable and cost-effective services." Disruption in services can result in the CPAU's inability to meet its customer's needs and impact the reliability of its services. All four utilities have a similar organizational structure around the work order process which includes designers, engineers, project utility coordinators, operations crew, and accountants who all play a vital role in ensuring that utility services are provided in a manner consistent with CPAU’s mission. The electric utility follows a slightly different process and uses different software systems than the water, gas, and wastewater utilities. The detailed processes around work orders for all utilities can be found in Appendix A of this report. To ensure CPAU continues to meet its mission, and mitigate risks, the City engaged Baker Tilly to conduct an internal audit that would focus on current utility work order processes. This decision was in conjunction with a broader, Citywide audit plan detailing the potential risks facing each department. Baker Tilly performed a citywide risk assessment that assessed a wide range of risk areas, including strategic, financial, operational, compliance, technological, and reputation risks. The purpose of the assessment was to identify and prioritize risks to develop the annual audit plan. During the FY2021 risk assessment, Baker Tilly identified a utility risk related to work order processes. With customers’ utility needs, it is vital that work order processes operate optimally. In order to properly assess the CPAU's work order processes, Baker Tilly reviewed a sample of electric, gas, water and wastewater work orders for compliance with internal processes and controls. We also evaluated the City’s utility work order processes against best practices. For additional details, please review the Detailed Testing & Analysis section. 2BScope The scope of this engagement includes work orders for the Electric and Wastewater, Gas and Water (WGW) departments opened between 1/1/21 and 2/21/22. 3BCompliance Statement This audit activity was conducted from January 2022 to May 2022 in accordance with generally accepted government auditing standards, except for the requirement of an external peer review0F 1. Those standards require that we plan and perform the audit to obtain sufficient, appropriate evidence to provide a reasonable basis for our findings and conclusions based on our audit objectives. We believe that the evidence obtained provides a reasonable basis for our findings and conclusions based on our audit objectives. The team at CPAU was very proactive and involved in the engagement that allowed for clear communication and support for Baker Tilly’s team. CPAU was always available to provide additional support and hands on explanations on the work order processes. The Office of the City Auditor greatly appreciates the support of the CPAU in conducting this audit activity. Thank you! 1 Packet Pg. 9 5 Detailed Testing & Analysis Methodology & Approach The objectives of the Utility Work Order Process Review are to: (1) Determine whether adequate controls around the work order process are in place and working effectively (2) Assess the work order process against best practices Our review scope includes the following: • Interview the appropriate individuals to understand the process, the information system used, and internal controls related to the work order process • Review policies and procedures as well as the regulations and standards to identify the criteria to be used for evaluation of control design and effectiveness • Perform a test of key internal controls on a sample basis • Compare the process and controls against best practices To evaluate work order controls, audit testing was conducted on a sample of electric, water, gas and wastewater work orders. In addition, common work order best practices were identified and compared to the City’s utility work order processes identified in this audit. Additional information regarding the testing approach and methodology can be found in the Audit Testing section. In order to properly evaluate the work order process and internal controls, the specific approach included the steps noted below. Audit Planning • Conduct research and gather information to understand the current environment • Assess audit risk • Develop an audit planning memo and program • Conduct kick-off meeting with key stakeholders Control Review and Testing • Gather information to understand the environment under review • Conduct interviews with key process owners and management • Assess risks and identify controls in place • Perform testing of key controls around work orders • Identify work order best practices and compare with current utility work order processes Reporting • Develop findings and recommendations based on supporting evidence • Validate documented findings • Develop and validate a draft audit report • Finalize report with management responses • Review and finalize report with the City Council and/or appropriate Council Committee 1 Packet Pg. 10 6 Audit Testing Introduction In order to achieve the objectives of the engagement, Baker Tilly developed an audit testing approach and methodology that would test the design and operational effectiveness of controls and identify control gaps and unmitigated risks around utility work order processes. Approach & Methodology In order to evaluate CPAU's control environment, Baker Tilly developed process flowcharts based on process documentation and interviews with key process owners. The process flowcharts were developed to document known risk, and mitigation practices based on CPAU's information and identify opportunities and improvement areas. In addition, the process flowcharts were used to facilitate the risk assessment and note the risks for the current work order processes, identify control gaps, and determine key risk and control areas for audit testing. The detailed process flowcharts can be found in Appendix A. Sampling Methodology CPAU provided a list of all work orders opened from 1/1/2021 through 2/12/2022. Using the AICPA Statistical Sample Sizes for Test of Controls grid with the following parameters (90% confidence level, 9% tolerable rate of error, with 0% expected deviation) a sample size of 25 was established. Samples were selected using auditor judgment with a focus on ensuring a variety of work order types (differing utilities, capital vs. O&M). Because the work order process for the electric utility differs from the other utilities, we selected 12 electric work orders and 13 WGW work orders for our control testing. The work orders selected for detailed control testing can be found in Appendix B. Additional Review In addition to testing sample work orders for the key controls currently in place, we also identified process and control improvement recommendations based on best practices. As part of the best practices review, we identified common work order best practices and compared them to the utilities’ current work order processes and controls. Best practices also include the use of Key Performance Indicators (KPIs). We identified common work order related KPIs and determined whether such KPIs were in use. 1 Packet Pg. 11 7 Audit Results During our process walkthroughs, we identified six key controls that Palo Alto currently has in place around the work order process. Some controls only apply to a certain utility or only apply to capital work orders. As such, not all controls are applicable for each of our sampled work orders. The controls tested, results of each test, and our recommendations for improvement are shown in the table below. Control # Control Description # of sampled work orders where control is applicable # of deficiencies Result Recommendation 1 All capital work orders require supervisor approval before work can be performed. 21 3 Maintenance work for water, gas, and wastewater do not require approval. Of the 21 sampled work orders that currently require approval, 3 did not have appropriate signature approval on the work order. All 3 were capital work orders. We recommend that all work orders (including O&M) be approved by someone that has budget responsibility or the ability to approve unbudgeted maintenance work. Approval should be evidenced in writing. 2 For development services projects, a permit must be reviewed and approved before work can begin. 10 0 10 of our sampled work orders were for development services projects and all but one had an approved permit prior to work being performed. None 3 For customer work orders, customer payment must be received before work can begin. 9 0 9 of our sampled work orders were for customer services and all had customer payment received before work began. None 4 The Supervisor reviews the completed work order and as-built forms for accuracy. 25 5 2 of the 25 sampled work orders did not have a signature showing review of the completed work order. We also noted 3 work orders that had a signature showing a review was performed, however, there were costs which were incorrectly recorded to the wrong work order indicating the review may not be performed at a detailed enough level and could be improved. The review performed by the Supervisor should include review of all labor and materials including 3rd party invoices to ensure all costs are recorded to the correct work order. 5 For electric work orders, the Utility Coordinator completes a checklist to ensure all documents are complete and included in the job packet (control implemented in August 2021). 5 0 Because this control was not implemented until August 2021 and only pertains to electric work orders, this control only applied to 5 of the sampled work orders. All 5 work orders had a completed checklist. We recommend the Utility Coordinator complete a checklist to show review of verifying completeness of work orders for all utilities. 6 For closed capital work orders, the assets were recorded to an appropriate plant account. 4 0 Costs are capitalized to the asset account on a quarterly basis. As such, only 4 of our sampled capital work orders were closed and recorded to the asset account at the time of our testing. All 4 work orders appeared to be recorded to an appropriate asset account (although not all costs were included as noted in control number 4 above). Per Palo Alto, the additional costs will be recorded in Q3 FY22. We recommend work orders be closed and capitalized monthly to prevent a backlog and to ensure depreciation starts immediately when the asset is placed in service. 1 Packet Pg. 12 8 In addition to the sample work order testing performed above, we have developed additional observations and recommendations based on our process walkthroughs with key process owners and compared to best practices. These are shown in the table below. # Category Observation Recommendation 1 System Utilization Although the water, wastewater, and gas utilities utilize SoGen to track work order progress, the electric utility utilizes SharePoint to share files and track work order status. Scheduling work is often done by the supervisor on a white board. In addition, the operations team has handheld tablets in the field, however, these do not interface with SoGen and they have found that it is easier to handwrite all workorder information manually on paper forms. These paper forms are then given to the utility coordinator to be entered into SoGen, duplicating the data entry function. We recommend the electric utility consider evaluating whether SAP has the capability to effectively track work orders to avoid using side systems. The electric utility should also evaluate whether implementing SoGen, the system used by the water, gas, and wastewater utilities to track work orders, would allow for easier, more accurate work order tracking. A system with the ability to schedule work orders based on priority will also ensure there is not unnecessary downtime or overburdening of worker time. Palo Alto utilities should also consider developing an interface between the handheld devices and SoGen to eliminate duplicate processes and allow stakeholders access to the most up-to-date information as changes are being made in real time. 2 Design Changes Currently, only major field changes require approval from the engineering supervisor and that approval is oftentimes provided verbally. Any design changes should be approved by an engineer or supervisor. Changes in the field are a safety issue for service and could impact other areas that the field crew may not be aware of. All approvals should be evidenced in writing. 3 Recording Asset Additions & Retirements For water, gas, and wastewater, the asset additions and retirements that the Business Analyst provides to the Accountant for recording is a report from the SoGen system. These costs are settled to accounts in SAP. The Business Analyst indicated that assets may not have been recorded accurately in the past and that AME should be the system of record when recording asset additions and retirements. If assets are not recorded appropriately, financial, and other reporting becomes less reliable. We recommend that the utilities perform a full system reconciliation of assets in AME and SAP to ensure assets are accurately recorded. Assets in AME and SAP should continue to be reconciled on an annual basis (or cycle counts can be performed monthly where a certain type of asset is counted each month). This reconciliation should be documented and signed-off on. 4 Asset Reconciliations Asset reconciliations are performed on a quarterly basis. No review of the reconciliation is performed by a separate individual. In addition, the reconciliation process is very manual with the accountant manually entering in numbers instead of using formulas to add the next periods numbers. All reconciliations should be performed on a monthly basis to ensure monthly financials are accurate. All reconciliations should be reviewed for accuracy by another individual. This review should be evidenced in writing. In addition, Palo Alto should consider using more formulas in the reconciliation to reduce the risk of errors that can be caused by manually entering in numbers. 5 Key Performance Indicators The water, wastewater, and gas utilities currently utilize key performance indicators (KPIs) to assist with monitoring their performance around project management and operations. An example of some of the KPIs currently being used are shown in Appendix C. It is our understanding that these same KPIs are currently being developed for the electric utility. We agree that the electric utility would benefit from developing similar KPIs that the water, wastewater, and gas utilities currently use. In addition, the utilities may want to consider adding additional KPIs related to work orders and project management. Example KPIs that the City may want to begin tracking are shown in Appendix D. 1 Packet Pg. 13 9 Appendices Appendix A: Process Flowcharts Below are the process flowcharts developed to identify risks and key controls. Palo Alto Eng i n e e r / Est i m a t o r De s i g n e r Uti l i t y Coo r d i n a t o r Eng i n e e r Ma n a g e m e n t Op e r a t i o n s Cus t o m e r Ser v i c e Electric Service Order Process Process Note ControlRisk Start Creates Service Order SAP Designs Service Order and uploads to SAP Approves service order with design and estimate Assigns Work center 1 2 Requests service order Email AUD Service Order approved? Makes required changes No Customer Service Order? Sends invoice to customer Releases Service Order SAP Yes SAP No Reviews Service Order packet for completeness 3 SAP Works with assigned team to schedule & manage jobs in queue 6 4 AUD Crew/ group goes to field Obtains approval and updated drawing from Engineer Work different from Design? Completes work Yes No 5 78 A 1 1 3 3 Share Point Share Point S.O. Pkg Once, payment received, Releases Service Order Share Point Yes SAP Invoice 2 2 1 Packet Pg. 14 10 Palo Alto Ma p p i n g G r o u p Uti l i t y C o o r d i n a t o r Op e r a t i o n s S u p e r v i s o r Sen i o r A c c o u n t a n t Electric Service Order Process A Process Note ControlRisk Reviews As- Builts and asset forms Reviews Package & uploads to SharePoint Reviews Service Order for accuracy Updates GIS for As-Builts 9 GIS Settles all Service Order costs to g/l accounts SAP SAP 11 Capital Project? Quarterly, runs report of all closed work orders Yes Unitizes all costs to asset accounts End 12 Closes Service Order No SAP SAPSAPSAP 4 4 5 5 6 6 7 7 8 8 IW39 Puts forms in Package & forwards to Coordinator Package S.O. Pkg. Closes Work Order (TECO) SAP Closes Service Order SAP 10 1 Packet Pg. 15 11 1. When creating the service order, the Engineer identifies it as either capital or maintenance and includes the general ledger accounts the costs should be applied to. The Engineer includes the cost estimate when creating the service order. 2. The Designer includes a Material Release Date in SAP (either June or December) to ensure that the warehouse doesn’t pull materials prematurely. 3. The Service Order Package is signed using DocuSign. The Senior Engineer can approve projects up to $50,000 as long as there are funds in the budget. Projects over this amount need to follow the authority levels shown below: Up to $50,000 – Senior Engineer Up to $150,000 – Manager $150,000 and over – Engineering Manager 4. The service order is released in SAP and SharePoint. 5. The Utility Coordinator verifies that all required information is included and checked off in SAP and SharePoint including the service order type, materials, design/drawings, and the customer payment (if applicable). 6. The Utility Coordinator updates the work center in SAP which designates the group/crew the service order is assigned to. They also change the order status to “open” in SharePoint and indicate the date the service order is assigned. The Utility Coordinator updates the materials date in SAP if not already shown. The group/crew will update the material date when ready so the warehouse knows the materials are needed. 7. The group/crew examine the field conditions before work can be scheduled. For customer service orders, the operations team works directly with the customer to schedule the work. If it is a customer service job with underground work, it goes to the underground Inspection group. There would be an update in SAP showing the inspection is complete. 8. If the change is small, the crew can just make the change. If it is a major change, they call the Engineer for approval and an updated drawing/map. 9. The Operations Supervisor reviews and approves all crew time and ensures they charged the correct service order. They also review all materials and third party invoices for accuracy and that all associated costs are being charged against order (e.g. Crane Rental or PCard/credit card purchases). 10. The Utility Coordinator uses a checklist to ensure all documentation is included in the service order package including map changes. 11. Labor, materials, and overheads are posted to either an O&M account or to Construction in Progress. Service orders are held open for 6 weeks to ensure all costs have been applied. 12. AUD automatically creates an Excel report that shows all additions and retirements when added by the mapping group . These are stored on the network so the Senior Accountant can retrieve the report and unitize the assets. Process Notes Risks Controls 1. Work is performed that is not approved. 2. Work may be scheduled before customer payment is received. 3. The crew may change the design without approval which could cause unsafe materials or designs. 4. Labor and materials may be charged to the incorrect service order. 5. The As-Builts may be incorrect if design changes were made in the field. 6. The service order may be closed without proper documentation of completeness. 7. The GIS mapping system may not display the correct assets in the electric system. 8. Asset additions and retirements may not be recorded or may be recorded to the wrong account. 1. All service orders are reviewed and approved by the Senior Engineer prior to work being performed. 2. Customer Service will not release a service order until the customer payment has been received. 3. For major changes, the crew receives approval from the Lead Engineer and receives updated job drawing(s) to reflect changes. 4. The Operations Supervisor reviews and approves all employee timecards and ensures the correct service order was charged. They also review material charges to the service order for accuracy. 5. The Operations Supervisor ensures that the As-Builts match the work that was performed. 6. The Utility Coordinator uses a checklist to ensure all documents are included in the service order package including any map changes. 7. All asset additions and retirements are updated in EEM & GIS to accurately track all electric assets. 8. The Senior Accountant is provided a listing from Engineering showing all asset additions and retirements to ensure proper unitization. 1 Packet Pg. 16 12 Palo Alto Eng i n e e r / Est i m a t o r De s i g n e r Uti l i t y Coo r d i n a t o r Eng i n e e r / Op e r a t i o n s Ma n a g e m e n t Op e r a t i o n s De v e l o p m e n t Ser v i c e s Water/Gas/Wastewater Service Order Process Process Note ControlRisk Start Creates Service Order Designs Service Order and uploads to SAP Approves service order as designed 4 Requests service order Email AME Service Order approved? Makes required changes No Releases Service Order SAP Prints and reviews Service Order package for completeness 5 Works with assigned team to schedule & manage jobs in queue 6 AME Crew/ group goes to field Obtains approval and updated drawing from Engineer Work different from Design? Completes work 7 9 A 3 3 4 4 S.O. Pkg Once, payment received, Releases Service Order 2 2 Development Service order? Yes Reviews and approves permit application Creates and sends invoice to customer SAP SOGen No Yes SOGen SOGen SAP SAP SOGen SAP 1 1 3 Creates Service Order O&M service order? Yes Yes 8 SOGen SAP No 2 1 Customer payment required? Yes No No 1 Packet Pg. 17 13 Palo Alto Ma p p i n g G r o u p Uti l i t y C o o r d i n a t o r Op e r a t i o n s Sup e r v i s o r Sen i o r A c c o u n t a n t Water/Gas/Wastewater Service Order Process A Process Note ControlRisk Reviews Package & uploads to SOGen Reviews Service Order for accuracy Updates GIS for As-Builts 10 AME Settles all Service Order costs to g/l accounts 12 Capital Project? Quarterly, runs report of all closed work orders Yes Unitizes all costs to asset accounts End 13 Closes Service Order No SAP SAPSAPSAP 5 5 6 6 7 7 8 8 IW39 S.O. Pkg S.O. Pkg. Closes Work Order (TECO) Closes Service Order SAP 11 Sogen 1 Packet Pg. 18 14 1. Anyone in operations can request a service order for maintenance work. 2. The Development Services Engineer reviews the utilities application, request for service including number of meters, construction plans indicating location of services and meters. The Engineer signs off on his review in ACCELA, the permit tracking system. 3. Development Services receives an automated report from SAP daily that shows all payments received. 4. When creating the service order, the Engineer identifies it as either capital or maintenance and includes the general ledger accounts the costs should be applied to. The Engineer includes the cost estimate when creating the service order. 5. The Service Order Package is signed using DocuSign. The Senior Engineer can approve projects up to $50,000 as long as there are funds in the budget. Projects over this amount need to follow the authority levels shown below: Up to $50,000 – Senior Engineer $50,000 and above – Manager 6. The service order is released in SAP and SOGen. 7. The Utility Coordinator verifies that all required information is included and checked off in SAP and SOGen including the service order type and the design/drawings. They also change the order status to “Ops Inspection” in SOGen and indicate the date the service order is assigned. 8. The group/crew examine the field conditions before work can be scheduled. For customer service orders, the operations team works directly with the customer to schedule the work. If it is a service order for new construction, it goes to the Inspector. 9. If the change is small, the crew can just make the change. If it is a major change, they call the Engineer for approval and an updated drawing/map. It is very rare to have a redesign. 10. The Operations Supervisor reviews the service order to ensure that labor and materials are properly recorded and that the As-Builts match the work that was completed. 11. The Utility Coordinator reviews the service order package to ensure all forms are filled out and that maps, photos, and tags are all included. 12. All service orders are closed quarterly in SAP. 13. The Utility Coordinator runs a report of all additions and retirements out of Sogen monthly and emails it to the Accountant to record additions and retirements. For capital improvement projects (CIP), the Engineer provides the Accountant with an Excel spreadsheet that lists the asset additions and retirements to record. Process Notes Risks Controls 1. A service order may be created without an approved permit in place. 2. Work may be scheduled before customer payment is received. 3. Work is performed that is not approved. 4. The crew may change the design without approval which could cause use of incorrect materials or designs. 5. A service order may be closed without the appropriate labor, materials, and assets being recorded. 6. The service order may be closed without proper documentation of completeness. 7. The GIS mapping system may not display the correct assets in the w/g/w system. 8. Asset additions and retirements may not be recorded or may be recorded to the wrong account. 1. All permits are reviewed and approved before a service order is created. 2. Development Services will not release a service order until the customer payment has been received. 3. All service orders for new construction are reviewed and approved by the Senior Engineer prior to work being performed. 4. For major changes, the crew receives approval from the Lead Engineer and receives updated job drawing(s) to reflect changes. 5. The Operations Supervisor reviews the service order to ensure all labor and materials have been recorded. They also ensure that the As-Builts on the service order match the work that was performed. 6. The Utility Coordinator ensures all documents are included in the service order package including any map changes. They also ensure all required checkboxes and forms are complete. 7. All asset additions and retirements are updated in AME to accurately track all assets. 8. The Senior Accountant is provided a report from Sogen by the Utility Coordinator showing all asset additions and retirements to ensure proper unitization. The Engineer provides a schedule showing the asset additions and retirements for CIP project. 1 Packet Pg. 19 15 Appendix B: Sampled Work Orders Sample #Order Type Order Type Description Order Total Actual Costs WBS Element Project Description 1 UERE Electric Reconstruction 40026477 33,909.46$ EL-89044 Substation Facility 2 UGRE Gas Reconstruction 40026573 33,819.98$ GS-11002 Gas System Improveme 3 UECS Electric Customer Service 40026526 30,181.68$ EL-89028 Electric Customer Co 4 UWRE Water Reconstruction 40026411 24,021.92$ WS-80014 Water Hydrant and Va 5 USRE Wastewater Reconstruction 40026713 12,408.03$ WC-80020 Sewer System, Custom 6 UCM Utilities Corrective Maintenance Order 50037259 12,029.75$ 7 UECS Electric Customer Service 40026572 10,215.60$ EL-89028 Electric Customer Co 8 UCM Utilities Corrective Maintenance Order 50037518 10,094.45$ 9 UWCS Water Customer Service Order 40026530 8,935.45$ WS-80013 Water System, Custom 10 USRE Wastewater Reconstruction 40026744 7,614.81$ WC-80020 Sewer System, Custom 11 UWCS Water Customer Service Order 40026680 7,573.37$ WS-80013 Water System, Custom 12 UGCS Gas Customer Service Order 40026407 7,328.00$ GS-80017 Gas System, Customer 13 UECS Electric Customer Service 40026776 7,015.39$ EL-89028 Electric Customer Co 14 UERE Electric Reconstruction 40026628 6,900.10$ EL-19004 Wood Pole Replacemen 15 UCM Utilities Corrective Maintenance Order 50037558 6,747.60$ 16 UCM Utilities Corrective Maintenance Order 50036962 5,711.71$ 17 UERE Electric Reconstruction 40026983 5,333.24$ EL-19004 Wood Pole Replacemen 18 UECS Electric Customer Service 40026585 4,748.07$ EL-89028 Electric Customer Co 19 UGCS Gas Customer Service Order 40026403 2,958.42$ GS-80017 Gas System, Customer 20 USCS Wastewater Customer Service Order 40026836 2,169.98$ WC-80020 Sewer System, Custom 21 UERE Electric Reconstruction 40026798 2,076.36$ EL-98003 Electric System Imp 22 USCS Wastewater Customer Service Order 40026535 1,932.45$ WC-80020 Sewer System, Custom 23 UERE Electric Reconstruction 40026835 12,439.56$ EL-19004 Wood Pole Replacemen 24 UERE Electric Reconstruction 40026868 10,781.31$ EL-19004 Wood Pole Replacemen 25 UGRE Gas Reconstruction 40024105 113,840.40$ 1 Packet Pg. 20 16 Appendix C: Current KPI’s for Water, Wastewater, and Gas – Selected Examples 1 Packet Pg. 21 17 1 Packet Pg. 22 18 1 Packet Pg. 23 19 Appendix D: Potential KPIs for Consideration KPI Description Calculation Emergency Work This is a measure of how much unplanned emergency work your maintenance department does. Emergency Hours Worked / Total Hours Planned vs Unplanned This measure is a good indicator of how reactive a work team is, and how much unscheduled work is breaking into the work schedule. Sum of Scheduled Work Completed (Hours) / Sum of All Work Completed (Hours) x 100 Schedule Compliance Measure of planning and scheduling quality, whether the work plan is realistic and achievable. It is a good indicator of maintenance effectiveness, whether they are able to complete the work allocated to them. Sum of Scheduled Work Completed (Hours) / Sum of Scheduled Hours x 100 Maintenance Overtime Measure of the health of your maintenance organization. Maintenance Overtime / Total Maintenance Hours Paid Work order Cycle Time The objective is to understand how long it takes to complete work, from creation to completion. Work Order Completion Date – Work Order Creation Date (in days) Customer Satisfaction The objective is to score and track how happy your customers are with their service, product, and/or experience. Example: “How would you rate your recent experience with our operations team?” The customer is given a 5-point scale from very unsatisfied to very satisfied. The total sum of customers who answered with “satisfied” (4) and “very satisfied” (5). 1 Packet Pg. 24 20 Appendix E: Management Response Recommendation Responsible Department(s) Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action Plan We recommend that all work orders (including O&M) be approved by someone that has budget responsibility or the ability to approve unbudgeted maintenance work. Approval should be evidenced in writing. Utilities Concurrence: Agree Target Date: August 2022 Action Plan: Capital and customer service work order packets are prepared by an Estimator/Engineer and approved by Senior Engineer/Supervisor. In Operations all work orders are prepared at the direction of Supervisor, by a Project Coordinator, the work order packet is given to the Supervisor for distribution to the crews. At present, a signature is not required before handing the job packet to the crews. Utilities will add the step requiring the Supervisors to initial the work orders at the time the paperwork is handed off to the crews. The review performed by the Supervisor should include review of all labor and materials including 3rd party invoices to ensure all costs are recorded to the correct work order. Utilities Concurrence: Agree Target Date: August 2022 Action Plan: WGW Operations has a checklist when closing work orders and Electric Operations recently implemented one in August of 2021. In the current process, the Supervisor/Leads forwards the invoices to administrative staff to process the payment. To ensure all items are posted against the order, the Supervisor/Lead will include all invoices for services, labor (charge time), materials in the project closing packet for the Project Coordinator’s review. The Project Coordinator then becomes the initiator, rather than the Supervisor/Leads, for processing invoice payments. We recommend the Utility Coordinator complete a checklist to show review of verifying completeness of work orders for all utilities. Utilities Concurrence: Agree Target Date: August 2022 Action Plan: WGW Operations has a checklist when closing work orders and Electric Operations recently implemented one in August of 2021. The Project Coordinators will use the checklist to verify labor, materials, and 3rd party invoices have been charged to the order before closing the order. We recommend work orders be closed and capitalized monthly to prevent a backlog and to ensure depreciation starts immediately when the asset is placed in service. Utilities & ASD Concurrence: Partially Agree Target Date: December 2022 Action Plan: Settlement of costs, closing of work orders and reconciliation of Construction in Progress are done on a monthly basis. The reconciliation reports are saved on the network at quarter ending 1 Packet Pg. 25 21 Recommendation Responsible Department(s) Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action Plan since capitalization are done on the same period. Working with limited staff in both Utilities Operations and Accounting, monthly capitalization is time consuming and not practical. Although it ensures depreciation will start immediately when asset is placed in service, depreciation is still considered a low risk assessment since it is a non-cash item and the depreciation amount is relatively immaterial compared to the total asset base. However, we recognize monthly capitalization is the best practice and will evaluate the feasibility of implementing the process. We recommend the electric utility consider evaluating whether SAP has the capability to effectively track work orders to avoid using side systems. The electric utility should also evaluate whether implementing SoGen, the system used by the water, gas, and wastewater utilities to track work orders, would allow for easier, more accurate work order tracking. A system with the ability to schedule work orders based on priority will also ensure there is not unnecessary downtime or overburdening of worker time. Palo Alto utilities should also consider developing an interface between the handheld devices and SoGen to eliminate duplicate processes and allow stakeholders access to the most up-to-date information as changes are being made in real time. Utilities Concurrence: Partially Agree Target Date: Ongoing Action Plan: Utilities staff plans to evaluate a mobile work management application that will address the needs of all its utilities and utilize one utility operations application to compliment SAP. Background: Utilities explored using the scheduling feature in SAP and found that the task was too cumbersome and customizing SAP to accommodate the various utilities was cost prohibitive for an organization of our size and with different needs for each of the five utilities. The scheduling function in SAP has not changed since we first implemented in 2004 and with the different types of assets and maintenance and construction requirements for all utilities, the scheduling module is not feasible. At present, SoGen is not the ideal application to be used in the field nor for Electric Operations to adopt. It is a stand-alone application that serves as an intermediary to capture work details such as location, activity, labor, and materials. In staff’s experience, best of breed utility applications are more operationally efficient and cost effective than customizing SAP. The department is continually keeping an eye out for mobile work management applications. Some recent examples are: • In February 2019 through September 2019, Utilities attempted to bring SoGen mobile. The mobile application lacked a good drawing tool to allow staff to update maps while in the field and encountered critical data discrepancies. • In 2019, staff piloted a mobile workforce management system (SMW) through Smart Energy Systems for both WGW and 1 Packet Pg. 26 22 Recommendation Responsible Department(s) Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action Plan Electric Operations in an effort to provide a mobile platform for some of the work groups. • In late 2020, the Wastewater maintenance group implemented a new maintenance application through SEDARU which provides a mobile platform. • Utilities kicked off the Advanced Metering Infrastructure project in FY 2022, which is a multi-year project ending in 2025. Utilities will evaluate purchasing a 3rd-party mobile application used by the vendor installing the meters and integrating with SAP. Staff is currently working with the vendor to explore and design the product for use post-implementation. • The Utilities is currently reviewing and implementing mobile applications through ArcGIS Enterprise with Esri. Any design changes should be approved by an engineer or supervisor. Changes in the field are a safety issue for service and could impact other areas that the field crew may not be aware of. All approvals should be evidenced in writing. Utilities Concurrence: Partially Agree Target Date: Complete (June 2022) Action Plan: As the auditor noted, it is the City’s current practice to send back major design changes to Engineering for approval. The Leads on- site are trained and empowered to use independent judgment in the field for minor field changes that did not require engineering such as, moving the service alignment a couple of feet to accommodate field conditions when necessary. These changes are documented on the redlined as- builts drawings or work orders, signed by the Leads or Supervisors, then sent to mapping to capture the accurate alignment of the new services. These minor changes to slightly move the alignment of new services do not create any safety concerns to the utility infrastructure, because all installation work must comply with Building Code and Utility Standards. We recommend that the utilities perform a full system reconciliation of assets in AME and SAP to ensure assets are accurately recorded. Assets in AME and SAP should continue to be reconciled on an annual basis (or cycle counts can be performed monthly where a certain type of asset is counted each month). This reconciliation should be documented and signed-off on. Utilities & ASD Concurrence: Agree Target Date: June 2023 (estimated) Action Plan: Utilities will prepare a report by fund and asset for Accounting to perform a full system reconciliation between AME and SAP to ensure assets are accurately recorded. Since there are over 16,000 records and 7,000,000 assets, the system reconciliation effort will require at least one staff member dedicated to this project for a year. The tasks may include troubleshooting discrepancies, documenting business processes, and facilitating workshops between ASD and Utilities. Due to 1 Packet Pg. 27 23 Recommendation Responsible Department(s) Agree, Partially Agree, or Do Not Agree and Target Date and Corrective Action Plan staffing shortages in Accounting and various financial deadlines and projects, early estimate for completing the reconciliation is one year. To determine the amount of time and effort necessary to completely reconcile all records and assets, Accounting will reconcile one fund first and provide a better target completion date at the next update. All reconciliations should be performed on a monthly basis to ensure monthly financials are accurate. All reconciliations should be reviewed for accuracy by another individual. This review should be evidenced in writing. In addition, Palo Alto should consider using more formulas in the reconciliation to reduce the risk of errors that can be caused by manually entering in numbers. ASD Concurrence: Agree Target Date: Complete (June 2022) Action Plan: Settlement of costs, closing of work orders and reconciliation of Construction in Progress are done on a monthly basis. The reconciliation reports are saved on the network at quarter ending since capitalization are done on the same period. The Senior Accountant will review reconciliation performed. Accounting has created a template to incorporate use of formulas to conduct reconciliation. In certain cells where a copy and paste from SAP is possible, no formula will be utilized. We agree that the electric utility would benefit from developing similar KPIs that the water, wastewater, and gas utilities currently use. In addition, the utilities may want to consider adding additional KPIs related to work orders and project management. Example KPIs that the City may want to begin tracking are shown in Appendix D. Utilities Concurrence: Agree Target Date: 12/31/2022 Action Plan: Electric Operations has a few KPI’s mapped out and are actively working on developing the dashboards. Staff anticipates completing them by the end of the year. Electric Operations will incorporate the following KPIs by the end of the year: Maintenance Work New Construction/CIP Work Customer Service Work Inspections Emergency Work/3rd Party Damages 1 Packet Pg. 28 City of Palo Alto Office of the City Auditor Policy & Services Committee Meeting February 28, 2023 The information provided here is of a general nature and is not intended to address the specific circumstances of any individual or entity. In specific circumstances, the services of a professional should be sought. Tax information, if any, contained in this communication was not intended or written to be used by any person for the purpose of avoiding penalties, nor should such information be construed as an opinion upon which any person may rely. The intended recipients of this communication and any attachments are not subject to any limitation on the disclosure of the tax treatment or tax structure of any transaction or matter that is the subject of this communication and any attachments. Baker Tilly Virchow Krause, LLP trading as Baker Tilly is a member of the global network of Baker Tilly International Ltd., the members of which are separate and independent legal entities. © 2018 Baker Tilly Virchow Krause, LLP 1 Packet Pg. 29 1. Present the Utility Work Order Process Review Report •Project Background •Key Observations/Recommendations •Questions & Discussion The CAO thanks the Palo Alto Utilities Department for their work on this audit activity –THANK YOU! 2. Task order review/approval Agenda 1 Packet Pg. 30 Objectives for the audit activity include: •Determine whether adequate controls are in place and working effectively around the work order process •Assess the work order process against best practices •Provide recommendations for improvement related to increased controls or process efficiencies Project Background Audit Planning The OCA performed tasks to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, review process, and timing between stakeholders and auditors. Process Analysis and Testing The OCA tested 6 controls on a sample of work orders to determine if controls were operating effectively. In addition, the OCA reviewed current processes in place and systems used to identify areas of improved controls and efficiencies. Recommendations The OCA identified areas for improvement and drafted a report. 1 Packet Pg. 31 Project Background: Sample Work Order Testing Key controls tested 1. All capital work orders require supervisor approval before work can be performed 2. For development services projects, a permit must be reviewed and approved before work can begin 3. For customer work orders, customer payment must be received before work can begin 4. The Supervisor reviews the completed work order and as-built forms for accuracy 5. For electric work orders, the Utility Coordinator completes a checklist to ensure all documents are complete and included in the job packet (control implemented in August 2021) 6. For closed capital work orders, the assets were recorded to an appropriate plant account 1 Packet Pg. 32 Key Observations & Recommendations - Sample Work order Testing # Observation Recommendation 1 Of the 21 sampled work orders that currently require approval, 3 did not have appropriate signature approval on the work order. All 3 were capital work orders. We recommend that all work orders (including O&M) be approved by someone that has budget responsibility or the ability to approve unbudgeted maintenance work. Approval should be evidenced in writing. 2 Of the 25 sampled work orders, 2 did not have a signature showing review of the completed work order. We also noted 3 work orders that had a signature showing a review was performed, however, there were costs which were incorrectly recorded to the wrong work order indicating the review may not be performed at a detailed enough level and could be improved. The review performed by the Supervisor should include review of all labor and materials including 3rd party invoices to ensure all costs are recorded to the correct work order. 1 Packet Pg. 33 Key Observations & Recommendations - Sample Work order Testing # Observation Recommendation 3 Because this control was not implemented until August 2021 and only pertains to electric work orders, this control only applied to 5 of the sampled work orders. All 5 work orders had a completed checklist. We recommend the Utility Coordinator complete a checklist to show review of verifying completeness of work orders for all utilities. 4 Costs are capitalized to the asset account on a quarterly basis. As such, only 4 of our sampled capital work orders were closed and recorded to the asset account at the time of our testing. All 4 work orders appeared to be recorded to an appropriate asset account (although not all costs were included as noted in control number 4 above). Per Palo Alto, the additional costs will be recorded in Q3 FY22. We recommend work orders be closed and capitalized monthly to prevent a backlog and to ensure depreciation starts immediately when the asset is placed in service 1 Packet Pg. 34 Key Observations & Recommendations - System Utilization # Observation Recommendation 5 Although the water, wastewater, and gas utilities utilize SoGen to track work order progress, the electric utility utilizes SharePoint to share files and track work order status. Scheduling work is often done by the supervisor on a white board. In addition, the operations team has handheld tablets in the field, however, these do not interface with SoGen and they have found that it is easier to handwrite all workorder information manually on paper forms. These paper forms are then given to the utility coordinator to be entered into SoGen, duplicating the data entry function. We recommend the electric utility consider evaluating whether SAP has the capability to effectively track work orders to avoid using side systems. The electric utility should also evaluate whether implementing SoGen, the system used by the water, gas, and wastewater utilities to track work orders, would allow for easier, more accurate work order tracking. A system with the ability to schedule work orders based on priority will also ensure there is not unnecessary downtime or overburdening of worker time. Palo Alto utilities should also consider developing an interface between the handheld devices and SoGen to eliminate duplicate processes and allow stakeholders access to the most up-to-date information as changes are being made in real time. 1 Packet Pg. 35 Key Observations & Recommendations - Design changes and Recording Assets # Observation Recommendation 6 Currently, only major field changes require approval from the engineering supervisor and that approval is oftentimes provided verbally. Any design changes should be approved by an engineer or supervisor. Changes in the field are a safety issue for service and could impact other areas that the field crew may not be aware of. All approvals should be evidenced in writing. 7 For water, gas, and wastewater, the asset additions and retirements that the Business Analyst provides to the Accountant for recording is a report from the SoGen system. These costs are settled to accounts in SAP. The Business Analyst indicated that assets may not have been recorded accurately in the past and that AME should be the system of record when recording asset additions and retirements. If assets are not recorded appropriately, financial, and other reporting becomes less reliable. We recommend that the utilities perform a full system reconciliation of assets in AME and SAP to ensure assets are accurately recorded. Assets in AME and SAP should continue to be reconciled on an annual basis (or cycle counts can be performed monthly where a certain type of asset is counted each month). This reconciliation should be documented and signed-off on. 1 Packet Pg. 36 Key Observations & Recommendations - Asset Reconciliation and KPI’s # Observation Recommendation 8 Asset reconciliations are performed on a quarterly basis. No review of the reconciliation is performed by a separate individual. In addition, the reconciliation process is very manual with the accountant manually entering in numbers instead of using formulas to add the next periods numbers. All reconciliations should be performed on a monthly basis to ensure monthly financials are accurate. All reconciliations should be reviewed for accuracy by another individual. This review should be evidenced in writing. In addition, Palo Alto should consider using more formulas in the reconciliation to reduce the risk of errors that can be caused by manually entering in numbers. 9 The water, wastewater, and gas utilities currently utilize key performance indicators (KPIs) to assist with monitoring their performance around project management and operations. An example of some of the KPIs currently being used are shown in Appendix C. It is our understanding that these same KPIs are currently being developed for the electric utility. We agree that the electric utility would benefit from developing similar KPIs that the water, wastewater, and gas utilities currently use. In addition, the utilities may want to consider adding additional KPIs related to work orders and project management. 1 Packet Pg. 37 Policy & Services Committee action The City Auditor recommends that the Policy & Services Committee take the following action: •Review the Utility Work Order Process Review report and corresponding recommendations for improvement and recommend the City Council accept the report. 1 Packet Pg. 38 The City Auditor recommends that the Policy & Services Committee take the following actions and forward the corresponding report to City Council for consent: Approve the following Task Orders: •FY23-Task 01 – Citywide Risk Assessment •FY23-Task 02 – Annual Audit Plan •Task 04.12 – Wire Payment Process and Controls Review (Extension) •Task 04.13 – Remote and Flexible Work Study (Extension) •Task 04.14 – Cybersecurity Assessment (Extension) •Task 04.15 – Wastewater Treatment Facility Agreement (Extension) •Task 04.19 – Disaster Recovery Preparedness •Task 04.20 – Procurement Process Review Policy & Services Committee action 11 1 Packet Pg. 39 Questions and answers 3 1 Packet Pg. 40 Thank you, it was a pleasure working with you! Amanda Lasinski (920) 210 7796 Amanda.lasinski@bakertilly.com Adriane McCoy (312) 240 2440 Adriane.mccoy@bakertilly.com 1 Packet Pg. 41 1 5 8 2 Policy & Services Committee Staff Report From: Chantal Gaines, Deputy City Manager Lead Department: City Auditor Meeting Date: February 28, 2023 Report #: 2301-0827 TITLE Approval of Office of City Auditor FY2023 Task Orders RECOMMENDATION The City Auditor recommends that the Policy & Services Committee recommend City Council approval for the following Task Orders, identified in the Audit Plan Report: 1) FY23-Task 01 – Citywide Risk Assessment 2) FY23-Task 02 – Annual Audit Plan 3) Task 04.12 – Wire Payment Process and Controls Review (Extension) 4) Task 04.13 – Remote and Flexible Work Study (Extension) 5) Task 04.14 – Cybersecurity Assessment (Extension) 6) Task 04.15 – Wastewater Treatment Facility Agreement (Extension) 7) Task 04.19 – Disaster Recovery Preparedness 8) Task 04.20 – Procurement Process Review DISCUSSION In accordance with our agreement with the City, Baker Tilly is required to conduct recurring activities each year. Those recurring activities including the following tasks outlined in our agreement: •Task 1: Citywide Risk Assessment •Task 2: Preparation of Annual Audit Plan •Task 4: Execute Council approved Annual Audit Plan (Attachment B) The Office of the City Auditor (OCA) is seeking approval from the Policy & Services Committee of the Tasks Orders that correspond to the Tasks outlined above and recommendation to forward these task orders to the City Council for approval. The Task Orders provide the contractual authority to begin this work in the new Fiscal Year 2023. An excerpt from the contract outlining these tasks is below for ease of reference. 2 Packet Pg. 42 1 5 8 2 Task 1. Beginning with year 1 and continuing at a minimum every other year thereafter, prepare a citywide risk assessment following the same review and approval requirements described in Task 2. The risk assessment process will be the primary determinant of subsequent audit activity. Task 2. Prepare an annual audit plan for review by the City Manager and appropriate City Council committee(s), and approval by the City Council, that identifies preliminary objectives of each audit to be performed, the schedule for each audit, and the estimated not to exceed resources and costs for each audit. The City Auditor shall consult with the City Attorney as necessary when developing audit plans. The annual audit plan will be largely based on the risk assessment required in Task 1. Task 4. Execute Annual Audit Plan: Conduct a minimum number of internal audits in accordance with each approved annual audit plan based on the risk assessments. Each internal audit will commence only upon the City’s approval of a Task Order (which may be at the task or sub-task level) as required by this Agreement. Each internal audit requires the preparation of a written report for review by the City Manager, City Attorney and appropriate Council committee, and review/approval by the City Council as required. Task 4 Details. The details of the four task orders (4 extensions and 2 new) are as follows: 04.12 Wire Payment Process and Controls Review (Extension) This task order with the period of performance from January 10, 2022, to June 30, 2022, was signed at the end of February 2022, and the review was commenced in March 2022. Although the fieldwork was completed in May 2022, the report process took longer than expected, and then there was a transition period in early FY23 until the interim City Auditor was appointed. OCA requests the period of performance to be extended to March 31, 2023. The total not-to-exceed budget remains the same although the costs incurred after June 30, 2022, will be charged against the FY2023 budget (instead of the FY2022 budget). 04.13 Remote and Flexible Work Study (Extension) This task order with the period of performance from March 1, 2022, to December 31, 2022, was signed in mid-April 2022, and the review was commenced in late April 2022. Although the fieldwork was completed in September 2022, the management response process is taking longer than expected. OCA requests the period of performance to be extended to March 30, 2023. The total not-to-exceed budget remains the same. 04.14 Cybersecurity Assessment (Extension) This task order with the period of performance from March 1, 2022, to December 31, 2022, was signed in mid-April 2022, and the review was commenced in April 2 Packet Pg. 43 1 5 8 2 2022. Although the fieldwork was completed in November 2022, the management response process is taking longer than expected. OCA requests the period of performance to be extended to April 30, 2023. The total not-to-exceed budget remains the same. 04.15 Wastewater Treatment Facility Agreement (Extension) This task order with the period of performance from March 1, 2022, to December 31, 2022, was signed in mid-April 2022, and the review was commenced in June 2022. Although the fieldwork was completed in October 2022, the report process is taking longer than expected. OCA requests the period of performance to be extended to May 31, 2023. The total not-to-exceed budget remains the same. 04.19 Disaster Recover Preparedness The preliminary audit objectives include assessing the documentation of current disaster recovery plan for high priority application and supporting infrastructure to identify the adequacy of the documentation and identify additional documentation requirements. 04.20 Procurement Process Review The preliminary audit objectives include: •Determine whether adequate controls are in place and working effectively to ensure that the appropriate vendors are selected properly to achieve desired objectives. •Identify the opportunities to improve the efficiency and effectiveness of the procurement process. If these task orders are approved unanimously by the Policy & Services Committee, this recommendation will be forwarded to the full City Council approval on an upcoming consent calendar. FISCAL/RESOURCE IMPACT Work recommended in these tasks is within both the approved scope and compensation of the contract with Baker Tilly and funding levels in the FY 2023 Operating Budget for the Office of the City Auditor. ATTACHMENTS None. 2 Packet Pg. 44 PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY23-01 Citywide Risk Assessment Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): C21179340 1B. TASK O RDER NO.: FY23-01 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2023 COMPLETION: June 30, 2023 4 TOTAL TASK ORDER PRICE: $55,000 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT: TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 45 o Strategic plan(s) o Financial reports, including the most recent City Budget and Comprehensive Annual Financial Report (CAFR) o Operational policies and procedures o Municipal code o Consulting reports o Other relevant information and reports • Conduct interviews with City Council and management o Risk assessment interviews, aimed at understanding City functions and identifying risks, will be conducted with City Council members as well as department and division • Conduct a risk assessment survey, if necessary • Conduct research into key risks in order to identify relevant information to assess risks Overall, the project team will consider the following risk types: • Strategic • Financial • Operational • Technology • Compliance • Reputational • Political Step 3 – Risk Analysis In Step 3, the project team will develop a risk matrix consisting of auditable areas (also referred to as an audit or risk universe). The risk matrix will include the following risk categories: • Environment, Strategy, and Governance – risks that have an organization wide impact and are not subject to a specific department or function (e.g., ethics) • Significant Projects and Initiatives – risks associated with large projects (e.g., capital projects, technology implementation) or City initiatives (e.g., employee engagement initiative). • Function Specific Risks – risks associated with a specific department or function (e.g., procurement policy compliance) After assembling a risk matrix, the project team will assess the likelihood and impact of potential adverse events in order to quantitatively score each auditable area for purposes of prioritizing audit activities. Step 4 – Reporting In Step 4, the project team will finalize the draft Risk Matrix and prepare a draft Risk Assessment Report. The project team will ask for input (general completeness, risk scoring) on the Risk Matrix from key project stakeholders. Upon finalization of the Risk Matrix, the project team will finalize the Risk Assessment Report. 2 Packet Pg. 46 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting the Citywide Risk Assessment involves four (4) primary steps: • Step 1: Project Planning & Management • Step 2: Information Gathering • Step 3: Analysis • Step 4: Reporting Step 1 – Project Planning & Management This step includes those tasks necessary to solidify mutual understanding of the risk assessment scope, objectives, deliverables, and timing as well as ensuring that appropriate client and consultant resources are available and well-coordinated. Tasks include: • Finalize project design – The first project activities will be to: o Identify communication channels and reporting relationships and responsibilities of project staff o Review and confirm project timelines o Review and confirm deliverables • Arrange logistics/administrative support – Matters to be addressed include schedules for interviews and data collection, contact persons in the departments, any other logistical matters, etc. • Conduct kick-off meeting with key project stakeholders Step 2 – Information Gathering This step involves gathering information, through various means, that will enable the project team to understand the various risks facing the City. Tasks include: • Request and review background information – the project team will develop an information request(s) in order to obtain various background information from the City. The request will include, but not be limited to: 2 Packet Pg. 47 Deliverables: The following deliverables will be prepared as part of this engagement: • Risk Matrix • Risk Assessment Report • Presentation of Results to City Council (note that this may be combined with presentation of the Task 2 Annual Audit Plan) Schedule of Performance Anticipated Start Date: March 1, 2023 Anticipated End Date: June 30, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $55,000. The not-to-exceed budget is based on an estimate of 250 total project hours, of which 40 are estimated to be completed by the City Auditor. Reimbursable Expenses We plan to complete all work remote including all interviews and documentation review. If at any point the City and Baker Tilly mutually determine it will be beneficial to perform a portion of the work on-site, we will submit an estimate of our reimbursable expenses for the City’s approval prior to traveling to Palo Alto. 2 Packet Pg. 48 PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY23-02 Annual Audit Plan Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): C21179340 1B. TASK O RDER NO.: FY23-01 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2023 COMPLETION: June 30, 2023 4 TOTAL TASK ORDER PRICE: $10,500 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT: TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 49 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to preparing the Annual Audit Plan involves two (2) primary steps: • Step 1: Consultation with City Council and Management • Step 2: Reporting Step 1 – Consultation with City Council and Management The Risk Matrix and Risk Assessment Report will serve as the primary drivers of the Annual Audit Plan. The project team will initiate discussions over Risk Assessment results, potential audit activities, and audit coverage with City Council and Management. The purpose of those conversations will be to understand the priorities of City Council, and to develop a Draft Annual Audit Plan: The Draft Annual Audit Plan will identify the following components for each audit activity: • Audit activity type – audit or consulting activity • Audit objectives and scope • Anticipated budget – both in terms of hours and budget • Anticipated timeline Step 2 – Reporting The project team will present the Draft Annual Audit Plan to the City Council in order to obtain input on each potential audit activity. Upon refining the plan, the project team will finalize the Annual Audit Plan for presentation to City Council. Deliverables The following deliverable will be prepared as part of this engagement: • Annual Audit Plan 2 Packet Pg. 50 Schedule of Performance Anticipated Start Date: March 1, 2023 Anticipated End Date: June 30, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $10,500. The not-to-exceed budget is based on an estimate of 50 total project hours, of which 10 are estimated to be completed by the City Auditor. Reimbursable Expenses We plan to complete all work remote including all interviews and documentation review. If at any point the City and Baker Tilly mutually determine it will be beneficial to perform a portion of the work on-site, we will submit an estimate of our reimbursable expenses for the City’s approval prior to traveling to Palo Alto. 2 Packet Pg. 51 Step 2 – Process and Control Review This step involves executing the procedures in the audit program to gather information, interview individuals, and analyze the data and information to obtain sufficient evidence to address the audit objectives. The preliminary audit objective is to: (1) Determine whether adequate controls are in place and working effectively to ensure that all disbursements are valid and properly processed in compliance with City’s policies and procedures; (2) Determine whether end user security awareness training is sufficient to prevent erroneous payments caused by phishing. Procedures include: • Interview the appropriate individuals to understand the identified instance of wire fraud • Interview the appropriate individuals to understand the process, the information system used, and manual and automated controls related to the disbursement process including vendor record creation and modification • Interview the appropriate individuals to understand the end user awareness training • Review policies and procedures as well as the regulations and standards to identify the criteria to be used for evaluation of control design and effectiveness • Test disbursement transactions and new and modified vendor records as well as related key internal controls on a sample basis • Compare the process and controls against the best practices Step 3 – Reporting In Step 3, the project team will perform tasks necessary to finalize audit working papers, prepare and review a draft report with the stakeholders, and submit a final audit report. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals and discuss the root cause of the identified findings • Complete supervisory review of working papers and a draft audit report • Distribute a draft audit report and conduct a closing meeting with key stakeholders o Discuss the audit results, finings, conclusions, and recommendations o Discuss management responses • Obtain written management responses and finalize a report • Review report with members of City Council and/or the appropriate Council Committee • Present the final report to the City Council and/or appropriate Council Committee Deliverables: The following deliverables will be prepared as part of this engagement: • Audit Report 2 Packet Pg. 52 PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-4.12 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): C21179340 1B. TASK O RDER NO.: FY22-004.12 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: January 10, 2022 COMPLETION: June 30, 2022 March 31, 2023 4 TOTAL TASK ORDER PRICE: $54,550 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT: Remaining in Task 4 FY22: 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Lydia Kou Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 53 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting the Work Order Process Review involves three (3) primary steps: • Step 1: Audit Planning • Step 2: Process and Control Review • Step 3: Reporting Step 1 – Audit Planning This step consists of the tasks performed to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, audit process, and timing between stakeholders and auditors. Tasks include: • Gather information to understand the environment under review o Understand the organizational structure and objectives o Review the City code, regulations, and other standards and expectations o Review prior audit results, as applicable o Review additional documentation and conduct interviews as necessary • Assess the audit risk • Write an audit planning memo and audit program o Refine audit objectives and scope o Identify the audit procedures to be performed and the evidence to be obtained and examined • Announce the initiation of the audit and conduct kick-off meeting with key stakeholders o Discuss audit objectives, scope, audit process, timing, resources, and expectations o Discuss documentation and interview requests for the audit 2 Packet Pg. 54 Schedule of Performance Anticipated Start Date: January 10, 2022 Anticipated End Date: June 30, 2022 March 31, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $54,550. The not-to-exceed budget is based on an estimate of 270 total project hours. Reimbursable Expenses We plan to complete all work remote including all interviews and documentation review. If at any point the City and Baker Tilly mutually determine it will be beneficial to perform a portion of the work on-site, we will submit an estimate of our reimbursable expenses for the City’s approval prior to traveling to Palo Alto. 2 Packet Pg. 55 Audit Activity 4.13 – Remote and Flexible Work Study PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-004.13 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY22-004.13 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2022 COMPLETION: December 31, 2022 March 31, 2023 4 TOTAL TASK ORDER PRICE: $60,000 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greer Stone Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 56 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting the Construction Controls Assessment involves four (3) primary steps: • Step 1: Audit Planning • Step 2: Control review and analysis • Step 3: Reporting Step 1 – Audit Planning This step consists of the tasks performed to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, audit process, and timing between stakeholders and auditors. Tasks include: • Gather information to understand the environment under review o Understand the organization structure and objectives o Review the codes, regulations, policies, and other standards and expectations o Review the prior audit results, if any o Review previously conducted employee engagement and satisfaction surveys o Issue an employee survey centered on remote work capabilities o Issue a management survey centered on remote work capabilities o Review additional documentation and conduct interviews as necessary • Assess the audit risk • Write an audit plan and audit program o Define audit objectives and scope o Identify the audit procedures to be performed and the evidence to be obtained • Announce the initiation of the audit and conduct a kick-off meeting with key stakeholders o Discuss audit objectives, scope, audit process, timing, resources, and expectations o Discuss documentation and interview requests for the audit 2 Packet Pg. 57 Step 2 – Control Review and Testing This step involves executing the procedures in the audit program to gather information, interview individuals, and analyze the data and information to obtain sufficient evidence to address the audit objectives. The preliminary audit objective is to: (1) Assess employee and management perspectives for long-term remote and flexible work viability and associated challenges; (2) Evaluate positive outcomes and challenges for managing a mixed location workforce; (3) Identify policies, processes, management practices and work culture improvements that may improve the City’s ability to manage a remote workforce. Tasks include but are not limited to: • Analyze employee and management surveys to identify management and policy change opportunities and barriers for managing a mixed location workforce • Interview (focus group and/or individual) the Human Resources, employee representatives and management representatives to understand the current state, benefits and barriers to • Review relevant policies and procedures as well as the position eligibility standards for remote work to identify the criteria to be used for evaluation of control design and effectiveness • Research best practices and practices of surrounding communities • Analyze available data to assess current practices impact on recruitment and retention • Validate analysis with Human Resources Step 3 – Reporting In Step 3, the project team will perform tasks necessary to finalize audit working papers and submit a final audit report. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Complete the supervisory review of working papers and a draft audit report • Distribute a draft audit report and conduct a closing meeting with key stakeholders o Discuss the audit results, finings, conclusions, and recommendations o Discuss management responses • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • Audit Report with remote and flexible work data analysis and best practice recommendation Schedule of Performance Anticipated Start Date: March 1, 2022 2 Packet Pg. 58 Anticipated End Date: December 31, 2022 March 31, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $60,000. The not-to-exceed budget is based on an estimate of 285 total project hours, of which 16 are estimated to be completed by the City Auditor. Reimbursable Expenses If circumstances allow, Baker Tilly anticipates planning one on-site fieldwork. Given this possibility, Baker Tilly could incur reimbursable expenses for this Task. The not-to-exceed maximum for reimbursable expenses for this Task is $5,000. The following summarizes anticipated reimbursable expenses: • Round-trip Airfare – $1,200 • Rental Car - $600 • Hotel accommodation - $2,500 (8 nights) • Food and incidentals – $700 Note that, if current restrictions associated with COVID-19 continue, an on-site visit may not be possible. The project team will work with the City to consider circumstances at the time. 2 Packet Pg. 59 Audit Activity 4.14 – Cybersecurity Assessment PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-004.14 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY22-004.14 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2022 COMPLETION: December 31, 2022 April 30, 2023 4 TOTAL TASK ORDER PRICE: $110,000 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greer Stone Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 60 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Cybersecurity Maturity Assessment Baker Tilly’s approach to conducting a cybersecurity assessment and developing a cybersecurity program strategy involves four (4) primary steps: • Step 1: Assessment Planning and Kick-off • Step 2: Information Gathering • Step 3: Cybersecurity Capability Analysis and Recommendations • Step 4: Reporting Step 1 – Assessment Planning and Kick-off This step consists of the tasks performed to adequately plan the work necessary to address the overall assessment objective and to solidify mutual understanding of the assessment scope, objectives, assessment process, and timing between stakeholders and assessors. Tasks include: • Baker Tilly will work with the City to finalize the assessment scope and project timeline. Baker Tilly will also provide the City with an initial interview and documentation request list. • Finally, Baker Tilly will perform a project kick-off discussion with the City to ensure alignment with the project timeline, interview schedule, and deliverables. Step 2 – Information Gathering This step involves conducting interviews with identified IT security personnel and key stakeholders to identify security capabilities, processes, and currently implemented technologies. Baker Tilly will also review current IT security policy and procedure documentation, as well as network and infrastructure architecture documents. 2 Packet Pg. 61 Step 3 – Cybersecurity Capability Analysis and Recommendations This step involves mapping current state security capabilities to the NIST Cybersecurity Framework and evaluate the maturity of current security processes. Baker Tilly will also identify current risks related to weaknesses in the City’s cybersecurity program. Baker Tilly will then review current state capabilities and risks with the City to ensure alignment on Baker Tilly’s initial analysis and identify target state objectives utilizing the Capability Maturity Model (CMMI) Finally, Baker Tilly will take the identified improvement areas and target state maturity objectives to develop our recommendations for the City’s cybersecurity program to meet its target state objectives. Step 4 – Reporting The project team will perform tasks necessary to finalize the initial draft cybersecurity assessment report and review a draft report with the stakeholders. Additionally, the team will submit a final assessment report to the City. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Distribute a draft assessment report and conduct a closing meeting with key stakeholders o Discuss the assessment results, findings, conclusions, and recommendations • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • Cybersecurity Assessment Report and Program Strategy External Penetration Testing Baker Tilly will perform external penetration testing on behalf of the City. Baker Tilly’s approach to conducting these security testing activities involves four (4) primary steps: • Step 1: Assessment Planning and Kick-off • Step 2: Open-Source Information Gathering and Reconnaissance • Step 3: External Penetration Testing • Step 4: Reporting Step 1 – Assessment Planning and Kick-off This step consists of the tasks performed to adequately plan the work necessary to address the overall testing objective and to solidify mutual understanding of the testing scope, objectives, testing process, and timing between stakeholders and assessors. Tasks include: 2 Packet Pg. 62 • Baker Tilly will work with the City to finalize the testing scope and project timeline. • Baker Tilly will perform a project kick-off discussion with the City to ensure alignment with the project timeline, testing approach, and deliverables. • Baker Tilly will provide the City with an ISP authorization form and Rules of Engagement documents for signature to confirm testing scope and activities. Step 2 – Open-Source Information Gathering and Reconnaissance This step involves conducting interviews with identified IT security personnel and key stakeholders to identify security capabilities, processes, and currently implemented technologies. Baker Tilly will also review current IT security policy and procedure documentation, as well as network and infrastructure architecture documents. Step 3 – External Penetration Testing Baker Tilly will conduct external penetration testing on up to 300 active and 208 dormant external IP addresses provided by the City. External penetration testing services include: • Confirmation of active versus dormant IP addresses • Identification of services and service versions running on each active system; • Automated vulnerability discovery scanning for each active system; • Penetration attempts on systems identified that have known exploitable vulnerabilities; and • Deep dive exploitation of any identified exploitable vulnerabilities to gain unauthorized access to internal systems and/or data. Step 4 – Reporting The project team will perform tasks necessary to finalize our security testing report and review a draft report with City stakeholders. Additionally, the team will submit a final testing report to the City. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Distribute a draft testing report and conduct a closing meeting with key stakeholders o Discuss the testing results, findings, conclusions, and recommendations • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • External Penetration Testing Report 2 Packet Pg. 63 Schedule of Performance Anticipated Start Date: March 1, 2022 Anticipated End Date: December 31, 2022 April 30, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $110,000. The not-to-exceed budget is based on an estimate of 525 total project hours, of which 30 are estimated to be completed by the City Auditor. Reimbursable Expenses We plan to complete the audit work remotely, including all interviews and documentation review. However, if the City requests the assessment team to travel on-site for meetings, interviews, or assessment report readouts, these travel related expenses will be billed in addition to the fees above. 2 Packet Pg. 64 Audit Activity 4.15 – Wastewater Treatment Plant Agreement PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-004.15 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY22-004.15 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2022 COMPLETION: December 31, 2022 May 31, 2023 4 TOTAL TASK ORDER PRICE: $82,500 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greer Stone Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 65 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting a Wasterwater Treatment Plant Agreement Review involves three (3) primary steps: • Step 1: Audit Planning • Step 2: Process and Control Review • Step 3: Reporting Step 1 – Audit Planning This step consists of the tasks performed to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, audit process, and timing between stakeholders and auditors. Tasks include: • Gather information to understand the environment under review o Understand the organizational structure and objectives o Review the City code, regulations, and other standards and expectations o Review prior audit results, as applicable o Review additional documentation and conduct interviews as necessary • Assess the audit risk • Write an audit planning memo and audit program o Refine audit objectives and scope o Identify the audit procedures to be performed and the evidence to be obtained and examined • Announce the initiation of the audit and conduct kick-off meeting with key stakeholders o Discuss audit objectives, scope, audit process, timing, resources, and expectations o Discuss documentation and interview requests for the audit 2 Packet Pg. 66 Step 2 – Process and Control Review This step involves executing the procedures in the audit program to gather information, interview individuals, and analyze the data and information to obtain sufficient evidence to address the audit objectives. The preliminary audit objective is to: (1) Determine whether adequate controls are in place and working effectively to ensure that costs for treatment plan operations are properly accounted for and allocated; (2) Assess the compliance with contracts and regulations. Procedures include: • Interview the appropriate individuals to understand the process, the information system used, and internal controls related to accounting and allocation of costs for treatment plan operations. • Review the contracts, policies and procedures as well as the regulations and standards to identify the criteria to be used for evaluation of compliance and control design and effectiveness • Review the documents (such as contracts and supporting documents for allocation) for the selected allocation transactions • Compare the cost accounting and allocation methodology against the requirements Step 3 – Reporting In Step 3, the project team will perform tasks necessary to finalize audit working papers, prepare and review a draft report with the stakeholders, and submit a final audit report. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals and discuss the root cause of the identified findings • Complete supervisory review of working papers and a draft audit report • Distribute a draft audit report and conduct a closing meeting with key stakeholders o Discuss the audit results, finings, conclusions, and recommendations o Discuss management responses • Obtain written management responses and finalize a report • Review report with members of City Council and/or the appropriate Council Committee • Present the final report to the City Council and/or appropriate Council Committee Deliverables: The following deliverable will be prepared as part of this engagement: • Audit Report Schedule of Performance 2 Packet Pg. 67 Anticipated Start Date: March 1, 2022 Anticipated End Date: December 31, 2022 May 31, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $82,500. The not-to-exceed budget is based on an estimate of 400 total project hours, of which 20 are estimated to be completed by the City Auditor. Reimbursable Expenses If circumstances allow, Baker Tilly anticipates planning one on-site fieldwork week. Given this possibility, Baker Tilly could incur reimbursable expenses for this Task. The not-to-exceed maximum for reimbursable expenses for this Task is $4,750. The following summarizes anticipated reimbursable expenses (for three team members): • Round-trip Airfare – $1500 • Rental Car - $400 • Hotel accommodation - $2500 (4 nights) • Food and incidentals – $750 Note that, if current restrictions associated with COVID-19 continue, an on-site visit may not be possible. The project team will work with the City to consider circumstances at the time. 2 Packet Pg. 68 PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY23-4.19 Disaster Recover Preparedness Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY23-4.19 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2023 COMPLETION: June 30, 2023 4 TOTAL TASK ORDER PRICE: $87,500 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 69 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Disaster Recovery Assessment Baker Tilly’s approach to conducting a disaster recovery assessment involves four (4) primary steps: • Step 1: Assessment Planning and Kick-off • Step 2: Information Gathering • Step 3: Disaster Recovery Analysis and Recommendations • Step 4: Reporting Step 1 – Assessment Planning and Kick-off This step consists of the tasks performed to adequately plan the work necessary to address the overall assessment objective and to solidify mutual understanding of the assessment scope, objectives, assessment process, and timing between stakeholders and assessors. Tasks include: • Baker Tilly will work with the City to finalize the assessment scope and project timeline. Baker Tilly will also provide the City with an initial interview and documentation request list. • Finally, Baker Tilly will perform a project kick-off discussion with the City to ensure alignment with the project timeline, interview schedule, and deliverables. Step 2 – Information Gathering This step involves conducting interviews with identified IT security personnel and key stakeholders to gain an understanding of the operating environment and understand the desired outcome of the disaster recovery plan. Baker Tilly will also review current IT disaster recovery policy and procedure documentation, as well as review current infrastructure in place. 2 Packet Pg. 70 Step 3 – Disaster Recovery Analysis and Recommendations This step involves assessing the documentation of current disaster recovery plan for high priority application and supporting infrastructure to identify the adequacy of the documentation and identify additional documentation requirements. Baker Tilly will perform a gap assessment between the current disaster recovery capabilities, desired disaster recovery strategy, and industry best practices. Baker Tilly develop recommendation to remediate the identified documentation and capability gaps. Baker Tilly will provide recommendations to update the disaster recovery documentation to address the gaps identified. Step 4 – Reporting The project team will perform tasks necessary to finalize the initial draft disaster recovery assessment report and review a draft report with the stakeholders. Additionally, the team will submit a final assessment report to the City. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Distribute a draft assessment report and conduct a closing meeting with key stakeholders o Discuss the assessment results, findings, conclusions, and recommendations • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • Disaster Recovery Assessment Report Schedule of Performance Anticipated Start Date: March 1, 2023 Anticipated End Date: June 30, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $87,500. The not-to-exceed budget is based on an estimate of 400 total project hours, of which 20 are estimated to be completed by the City Auditor. 2 Packet Pg. 71 Reimbursable Expenses If circumstances allow, Baker Tilly anticipates planning one on-site fieldwork. The maximum compensation amount reflected above will be inclusive of any travel related expenses. Note that, if current restrictions associated with COVID-19 continue, an on-site visit may not be possible. The project team will work with the City to consider circumstances at the time. 2 Packet Pg. 72 PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY23-4.20 Procurement Process Review Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): C21179340 1B. TASK O RDER NO.: FY23-4.20 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2023 COMPLETION: September 30, 2023 4 TOTAL TASK ORDER PRICE: $61,550 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greg Tanaka, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE:  SERVICES AND DELIVERABLES TO BE PROVIDED  SCHEDULE OF PERFORMANCE  MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable)  REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 73 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting an internal audit of Procurement Process involves three (3) primary steps: • Step 1: Audit Planning • Step 2: Control Review and Testing • Step 3: Reporting Step 1 – Audit Planning This step consists of the tasks performed to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, audit process, and timing between stakeholders and auditors. Tasks include: • Gather information to understand the environment under review o Understand the organizational structure and objectives o Review the City code, regulations, and other standards and expectations o Review prior audit results, as applicable o Review additional documentation and conduct interviews as necessary • Assess the audit risk • Write an audit planning memo and audit program o Refine audit objectives and scope o Identify the audit procedures to be performed and the evidence to be obtained and examined • Announce the initiation of the audit and conduct kick-off meeting with key stakeholders o Discuss audit objectives, scope, audit process, timing, resources, and expectations o Discuss documentation and interview requests for the audit 2 Packet Pg. 74 Step 2 – Control Review and Testing This step involves executing the procedures in the audit program to gather information, interview individuals, and analyze the data and information to obtain sufficient evidence to address the audit objectives. The preliminary audit objective is to: (1) Determine whether adequate controls are in place and working effectively to ensure that the appropriate vendors are selected properly to achieve desired objectives. (2) Identify the opportunities to improve the efficiency and effectiveness of the procurement process. Procedures include, but not limited to: • Interview the appropriate individuals to gain an understanding of the organizational structure, processes, and controls related to procurement processes from the need assessment and market analysis to contract awarding and administration. • Review policies and procedures as well as the legislative and regulatory requirements to identify the criteria to be used for evaluation of control design and effectiveness. • Review the documents (such as contracts and related procurement files and performance reviews) for the selected contracts. • Analyze the data and information related to procurement, as appropriate. • Compare the process and controls against the best practices. Step 3 – Reporting In Step 3, the project team will perform tasks necessary to finalize audit working papers, prepare and review a draft report with the stakeholders, and submit a final audit report. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals and discuss the root cause of the identified findings • Complete supervisory review of working papers and a draft audit report • Distribute a draft audit report and conduct a closing meeting with key stakeholders o Discuss the audit results, finings, conclusions, and recommendations o Discuss management responses • Obtain written management responses and finalize a report • Review report with members of City Council and/or the appropriate Council Committee Deliverables: The following deliverable will be prepared as part of this engagement: • Audit Report 2 Packet Pg. 75 Schedule of Performance Anticipated Start Date: March 1, 2023 Anticipated End Date: September 30, 2023 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $61,550. The not-to-exceed budget is based on an estimate of 350 total project hours, of which 20 are estimated to be completed by the City Auditor. Reimbursable Expenses We plan to complete all work remote including all interviews and documentation review. If at any point the City and Baker Tilly mutually determine it will be beneficial to perform a portion of the work on-site, we will submit an estimate of our reimbursable expenses for the City’s approval prior to traveling to Palo Alto. 2 Packet Pg. 76 City of Palo Alto Office of the City Auditor FY22/FY23 Annual Audit Plan February 15, 2022 2 Packet Pg. 77 FY2022/2023 Audit Plan 2 Overview Introduction The purpose of the audit activites performed by the Office of the City Auditor (OCA) for the City of Palo Alto (the City) is “to ensure that city management is using its financial, physical, and informational resources effectively, efficiently, economically, ethically, and equitably, and in compliance with laws, regulations, contract and grant requirements, and city policies and procedures”, according to the Palo Alto Municipal Code (Section 2.08.130). It requires the City Auditor prepare an annual audit plan for the City Council’s approval at the beginning of each fiscal year. In accordance with Task #2 of the Baker Tilly agreement (City of Palo Alto Contract No, C21179340), Baker Tilly US, LLP (Baker Tilly) performed the initial risk assessment after having started to serve as OCA in October 2020 and submitted in early 2021 the FY21-FY22 annual audit plan identifying audit activities across an 18-months horizon (through FY22). The OCA updated the initial risk assessment in January 2022, one year after our initial risk assessment. This audit plan for the remaining FY22 and FY23 was prepared based on the results of the updated risk assessment. The OCA will seek approval of contract task orders iteratively during that timeframe in order to remain agile and accommodate changes to the plan as time passes. Other activities are addressed in separate task orders corresponding to the tasks in the Baker Tilly agreement. For example, the City Auditor performs follow up on audit findings and recommendations, as outlined in Task #5. Conformance with Local Ordinances and Standards Section 2.08.130 of the Palo Alto Municipal Code defines that the mission of OCA is to promote honest, efficient, effective, economical, and fully accountable and transparent city government. Audits are to be conducted and nonaudit services provided in accordance with Government Auditing Standards, as established by the Comptroller General of the United States, Governmental Accountability Office. The following duties of the City Auditor exist regarding the plan and scope of internal audits. Palo Alto City Charter Article IV Sec. 12 requires the City Auditor to perform the following: – Conduct audits in accordance with a schedule approved by the City Council and may conduct unscheduled audits from time to time. – Conducts internal audits of all the fiscal transactions of the City. Title 2 Administrative Code Section 2.08.130 requires the City Auditor to perform the following: – Prepare an annual audit plan for city council approval. – Identify the preliminary objectives of each audit to be performed, reflecting the purpose of the engagement and a preliminary description of the areas that may be addressed. – Conduct performance audits and perform nonaudit services of any city department, program, service, or activity as approved by the city council. California Government Code Section 1236 requires all cities that conduct audit activities to conduct their work under the general and specified standards prescribed by the Institute of Internal Auditors (IIA) or the Government Auditing Standards (GAO) issued by the Comptroller General of the United States, as appropriate. 2 Packet Pg. 78 FY2022/2023 Audit Plan 3 Audit Activity Types OCA will conduct performance audits and perform financial/operational analyses of any City department, program, service, or activity as approved by the City Council in accordance with the Baker Tilly agreement. Performance Audits According to the Government Auditing Standards (GAO-18-568G, Section 1.21 and 1.22, page 10-12), performance audits provide objective analysis, findings, and conclusions to assist management and those charged with governance and oversight with, among other things, improving program performance and operations, reducing costs, facilitating decision making by parties responsible for overseeing or initiating corrective action, and contributing to public accountability. Performance audits may include the following four (4) audit objectives: – Program effectiveness and results – Internal control design and effectiveness – Compliance with laws, regulations, and policies – Prospective analysis Audit Planning Considerations While maintaining its independence and objectivity in accordance with standards, the City Auditor considers a variety of matters when developing the Annual Audit Plan, including but not limited to: – Risk assessment – OCA performed a risk assessment and summarized the results in a separate report (Task #2). Generally speaking, audit activities target high(er) risk areas. The results are shown the following page. – Ability to add value – audit seeks to add value through independent and objective analysis. – City Council – the City Auditor reports to the City Council and seeks input on audit priorities. – Coverage and Prior Audits – the City Auditor considers prior audits conducted by OCA, the financial audit, and other audit and consulting reports recently issued. – “Ripeness” and On-Going Initiatives – certain risk areas may be addressed through operational activities, which could mean they are not be ripe for audit to add value. – Scheduling – the City Auditor takes into consideration the timing of an audit and other on-going initiatives that directly relate. Putting an undue burden on City staff may exacerbate the risk at hand or other interrelated risks. 2 Packet Pg. 79 FY2022/2023 Audit Plan 4 Risk Assessment Results The OCA performed a citywide risk assessment to plan for FY22 and FY23 audit activities and documented the methodology and the detailed results in a separate Risk Assessment Report. In summary, we identified the following areas rated as High or High-Moderate risks. In determining the audit activities to be performed in FY22 and in FY23, we further reviewed these risks and functional areas and considered the matters listed in the previous page. Functional Area Title Likelihood (1-5) Impact (1-5)Score City Wide COVID-19 Response 5 5 50 Org Wide Employee Retention & Succession Planning 5 4 46 Planning and Development Services Long Rnage Planning 5 4 46 Information Technology Disaster Recovery Preparedness and Testing 3 5 44 Information Technology Host Intrusion and Malware Defense 3 5 44 Information Technology Problem Management and Incident Response 3 5 44 Transportation Contract Management 3 5 44 Org Wide Workforce 4 4 42 Org Wide Citywide Risk Management 4 4 42 Administrative Services Procurement 4 4 42 Fire Emergency Medical Service 4 4 42 Human Resources High Cost Claims 4 4 42 Human Resources Workload 4 4 42 Information Technology Mobile Device Management 5 3 40 Information Technology Strategy and Governance 5 3 40 Public Works Secondary Treatment Upgrades 2 5 38 Public Works ADA Compliance Upgrade 2 5 38 Administrative Services Investments, Debt, and Cash Management 2 5 38 Information Technology Information Security 2 5 38 Information Technology Operations and Monitoring 2 5 38 Information Technology Physical and Environmental Controls 2 5 38 Information Technology Ransomware 2 5 38 Police Use of Force and Officer Conduct 2 5 38 Org Wide Governance 3 4 36 Org Wide Organizational Culture 3 4 36 Administrative Services ERP System Upgrade 3 4 36 City Wide Sustainability and Climate Action Plan 3 4 36 Administrative Services Accounts Receivable 3 4 36 Fire Fire Suppression 3 4 36 Fire Fire Prevention - Palo Alto Foothills & Wildlad Fire Risk 3 4 36 Public Works Public Services - Fleet 3 4 36 Public Works Wastewater Treatment Plant Operations 3 4 36 Public Works Public Services -Facilities 3 4 36 Utilities AMI (Advanced Metering Infrastructure) Project 3 4 36 Utilities Rates and Rate Adjustments 3 4 36 2 Packet Pg. 80 FY2022/2023 Audit Plan 5 Proposed Audit Activities for FY2022-2023 Included in the tables below are the proposed audit activities for the remainder of FY2022 and FY2023. Each audit activity corresponds to a risk rated as High or Moderate in the Risk Assessment Report and selected based on other factors outlined on page 3. The preliminary audit objectives are described for each audit listed. These objectives and scope of each audit activity will be further defined based on the result of a project planning risk assessment processes performed at the beginning of each activity. Audits are planned in three overall phases – note that the timing may differ slightly for each audit activity: – Phase I – Activities projected to start before March 2022 and end by June 2022 – Phase II – Activities projected to start in March 2022 and end by December 2022 – Phase III – Activities projected to start in June 2022 or January 2023 and end by June 2023 Amendments to the proposed audit plan will be proposed either as needed or after conducting an annual risk assessment and update the audit plan, as needed, during FY23. Amendments may be proposed in response to changes in the City’s environment such as organizational structure, operations, risks, systems, and controls. Please note that the City Auditor will actively manage projects and overall budgets and workload in its execution of the workplan. For each audit activity, a task order is submitted to the City Council for approval before the work is commenced. We have prepared and attached to this report multiple task orders that correspond to audit activities we have prioritized (e.g., those in Phase I). Those audit activities for are marked with an “X” in the ‘Seeking Approval’ column of the table below, and the Task Orders are included in the Appendix. 2 Packet Pg. 81 FY2022/2023 Audit Plan 6 Phase I Activities Seeking Approval Function Project Title Audit Objectives Timeline Estimated Hours FY22 Cost FY23 Cost (*) Total Cost FY21+22+23 _ Administrative Services Economic Recovery Advisory (Task Order 4.7) ● Review the City’s long-term financial planning model and offer recommendations for improvement. ● Identify and evaluate key revenue sources categories that present long term risk to the City's financial sustainability. ● Perform scenario analysis and advise in the development of long term financial projections. March - December 2021 400 $64,663 $64,663 Public Works Public Safety Building - Construction Audit (Task Order 4.8) ● Monthly invoice review ● Change order testing ● Contingency and allowance testing ● Lien waiver control ● Compliance with insurance requirements ● Closeout testing ● Verify the City’s implementation and adherence to documented project controls March 2021 - June 2023 420 $26,633 $26,633 $51,266 Planning and Development Services Building Permit & Inspection Process Review (Task Order 4.9) ● Identify highest impact area to focus the assessment (e.g., specific permit type(s), specific sub-processes, etc.). ● Document corresponding process(es) and evaluate for efficiency and effectiveness. ● Benchmark operational performance against industry practices and established standards. April – September 2021 360 $48,300 $48,300 Citywide Nonprofit Agreements Risk Management Review (Task Order 4.10) ● Evaluate controls in place to ensure that nonprofit organizations are properly vetted prior to selection and monitored through the life of an agreement. ● Assess the performance monitoring process against the best practice. ● Follow up on relevant audit findings from past audit work. May – September 2021 400 $55,246 $55,246 Utilities Utility Work Order & Process Review (Task Order 4.11) ● Determine whether adequate controls are in place and working effectively around the work order process ● Assess the work order process against best practices January - December 2022 400 $81,400 $81,400 Administrative Services / Information Technology Wire Payment Process and Controls (Task Order 4.12) ● Determine whether adequate controls are in place and working effectively to ensure that all disbursements are valid and properly processed in compliance with City’s policies and procedures ● Determine whether end user security awareness training is sufficient to prevent erroneous payments caused by phishing February - June 2022 270 $54,550 $54,550 Phase I Sub Total 2,250 $329,792 $26,633 $355,425 * For the purpose of audit plan preparation, OCA used the FY22 budget amount for FY23 2 Packet Pg. 82 FY2022/2023 Audit Plan 7 Phase II Activities Seeking Approval Function Project Title Audit Objectives (preliminary objectives for audits not currently subject to approval) Timeline Estimated Hours FY22 Cost FY23 Cost (*) Total Cost X Human Resources Remote and Flexible Work Study ● Assess employee and management perspectives for long-term remote and flexible work viability and associated challenges ● Evaluate positive outcomes and challenges for managing a mixed location workforce ● Identify policies, processes, management practices and work culture improvements that may improve the City’s ability to manage a remote workforce March - December 2022 285 $50,000 $10,000 $60,000 X Information Technology Cybersecurity Assessment ● Map current state security capabilities to the NIST Cybersecurity Framework and evaluate the maturity of current security processes ● Identify current risks related to weaknesses in the City’s cybersecurity program ● Identify target state objectives utilizing the Capability Maturity Model (CMMI) and develop recommendation to meet the objectives March - December 2022 525 $90,000 $20,000 $110,000 X Public Works Wastewater Treatment Plant Agreement Audit ● Evaluate whether direct and indirect costs incurred by the City are properly allocated to the operation of the Wastewater Treatment Plant. ● Review whether costs are properly allocated to the various parties to the Wastewater Treatment Plant Agreement. March 2022 - December 2022 400 $60,000 $2,250 $62,250 Phase II Sub Total 1,210 $194,000 $38,250 $232,250 * For the purpose of audit plan preparation, OCA used the FY22 budget amount for FY23 2 Packet Pg. 83 FY2022/2023 Audit Plan 8 Phase III Activities Seeking Approval Function Project Title Preliminary Audit Objectives Timeline Estimated Hours FY22 Cost FY23 Cost (*) Total Cost Transportation Contract Management - ALPR Technology ● Determine whether policies and procedures are implemented effectively to protect the privacy of personal information gathered using ALPR technology for the City's parking management. ● Determine whether the City monitors the vendor's performance to ensure the compliance with contract terms and applicable laws and regulations related to data privacy. June 2022 - January 2023 400 $82,500 $82,500 Administrative Services Investment Management ● Determine whether adequate controls are in place and operating effectively to ensure that investments are managed in accordance with the investment management and other relevant policies. ● Assess the organizational structure and operations of the investment portfolio management function against best practice. June 2022 - January 2023 350 $61,550 $61,550 Information Technology Disaster Recovery Preparedness ● Determine whether a formal disaster recovery plan exists and aligns with the City's needs for business continuity ● Determine whether a disaster recovery plan is periodically tested and updated to ensure a successful recovery January - June 2023 400 $87,500 $87,500 Administrative Services Procurement Process ● Determine whether adequate controls are in place and working effectively to ensure that the appropriate vendors are selected properly to achieve desired objectives ● Identify the opportunities to improve the efficiency and effectiveness of the procurement process January - June 2023 350 $61,550 $61,550 Planning and Development Services Long Range Planning ● Review progress against intended goals and identify any gaps ● Determine whether an effective control environment exists for the Long Range Planning group to maintain City's Comprehensive Plan ● Determine whether adequate controls are in place and working effectively for data analyses January - June 2023 400 $82,500 $82,500 Public Works ADA Compliance ● Determine whether improvements have been made to make facilities, programs, and services accessible in accordance with the Transition Plan and Self-Evaluation Final Study to ensure compliance with the Americans with Disabilities Act (ADA) OF 1990 January - June 2023 350 $61,550 $61,550 TBD TBD / Ad Hoc Requests TBD TBD TBD Phase III Sub Total 2,300 $0 $458,100 $458,100 Phase I + II + III TOTAL 5,760 $523,792 $521,983 $1,045,775 FY22 - FY23 Budget $600,000 $560,000 $1,160,000 FY23 Ad Hoc / Contingency $76,208 $38,017 $114,225 * For the purpose of audit plan preparation, OCA used the FY22 budget amount for FY23 2 Packet Pg. 84 FY2021/2022 Audit Plan 9 Appendix: Task Orders 2 Packet Pg. 85 10 Audit Activity 4.13 – Remote and Flexible Work Study PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-004.13 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY22-004.13 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2022 COMPLETION: December 31, 2022 4 TOTAL TASK ORDER PRICE: $60,000 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greer Stone, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE: ▪ SERVICES AND DELIVERABLES TO BE PROVIDED ▪ SCHEDULE OF PERFORMANCE ▪ MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable) ▪ REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 86 11 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting the Construction Controls Assessment involves four (3) primary steps: • Step 1: Audit Planning • Step 2: Control review and analysis • Step 3: Reporting Step 1 – Audit Planning This step consists of the tasks performed to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, audit process, and timing between stakeholders and auditors. Tasks include: • Gather information to understand the environment under review o Understand the organization structure and objectives o Review the codes, regulations, policies, and other standards and expectations o Review the prior audit results, if any o Review previously conducted employee engagement and satisfaction surveys o Issue an employee survey centered on remote work capabilities o Issue a management survey centered on remote work capabilities o Review additional documentation and conduct interviews as necessary • Assess the audit risk • Write an audit plan and audit program o Define audit objectives and scope o Identify the audit procedures to be performed and the evidence to be obtained • Announce the initiation of the audit and conduct a kick-off meeting with key stakeholders o Discuss audit objectives, scope, audit process, timing, resources, and expectations o Discuss documentation and interview requests for the audit 2 Packet Pg. 87 12 Step 2 – Control Review and Testing This step involves executing the procedures in the audit program to gather information, interview individuals, and analyze the data and information to obtain sufficient evidence to address the audit objectives. The preliminary audit objective is to: (1) Assess employee and management perspectives for long-term remote and flexible work viability and associated challenges; (2) Evaluate positive outcomes and challenges for managing a mixed location workforce; (3) Identify policies, processes, management practices and work culture improvements that may improve the City’s ability to manage a remote workforce. Tasks include but are not limited to: • Analyze employee and management surveys to identify management and policy change opportunities and barriers for managing a mixed location workforce • Interview (focus group and/or individual) the Human Resources, employee representatives and management representatives to understand the current state, benefits and barriers to • Review relevant policies and procedures as well as the position eligibility standards for remote work to identify the criteria to be used for evaluation of control design and effectiveness • Research best practices and practices of surrounding communities • Analyze available data to assess current practices impact on recruitment and retention • Validate analysis with Human Resources Step 3 – Reporting In Step 3, the project team will perform tasks necessary to finalize audit working papers and submit a final audit report. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Complete the supervisory review of working papers and a draft audit report • Distribute a draft audit report and conduct a closing meeting with key stakeholders o Discuss the audit results, finings, conclusions, and recommendations o Discuss management responses • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • Audit Report with remote and flexible work data analysis and best practice recommendation Schedule of Performance Anticipated Start Date: March 1, 2022 2 Packet Pg. 88 13 Anticipated End Date: December 31, 2022 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $60,000. The not-to-exceed budget is based on an estimate of 285 total project hours, of which 16 are estimated to be completed by the City Auditor. Reimbursable Expenses If circumstances allow, Baker Tilly anticipates planning one on-site fieldwork. Given this possibility, Baker Tilly could incur reimbursable expenses for this Task. The not-to-exceed maximum for reimbursable expenses for this Task is $5,000. The following summarizes anticipated reimbursable expenses: • Round-trip Airfare – $1,200 • Rental Car - $600 • Hotel accommodation - $2,500 (8 nights) • Food and incidentals – $700 Note that, if current restrictions associated with COVID-19 continue, an on-site visit may not be possible. The project team will work with the City to consider circumstances at the time. 2 Packet Pg. 89 14 Audit Activity 4.14 – Cybersecurity Assessment PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-004.14 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY22-004.14 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2022 COMPLETION: December 31, 2022 4 TOTAL TASK ORDER PRICE: $110,000 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greer Stone, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE: ▪ SERVICES AND DELIVERABLES TO BE PROVIDED ▪ SCHEDULE OF PERFORMANCE ▪ MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable) ▪ REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 90 15 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Cybersecurity Maturity Assessment Baker Tilly’s approach to conducting a cybersecurity assessment and developing a cybersecurity program strategy involves four (4) primary steps: • Step 1: Assessment Planning and Kick-off • Step 2: Information Gathering • Step 3: Cybersecurity Capability Analysis and Recommendations • Step 4: Reporting Step 1 – Assessment Planning and Kick-off This step consists of the tasks performed to adequately plan the work necessary to address the overall assessment objective and to solidify mutual understanding of the assessment scope, objectives, assessment process, and timing between stakeholders and assessors. Tasks include: • Baker Tilly will work with the City to finalize the assessment scope and project timeline. Baker Tilly will also provide the City with an initial interview and documentation request list. • Finally, Baker Tilly will perform a project kick-off discussion with the City to ensure alignment with the project timeline, interview schedule, and deliverables. Step 2 – Information Gathering This step involves conducting interviews with identified IT security personnel and key stakeholders to identify security capabilities, processes, and currently implemented technologies. Baker Tilly will also review current IT security policy and procedure documentation, as well as network and infrastructure architecture documents. 2 Packet Pg. 91 16 Step 3 – Cybersecurity Capability Analysis and Recommendations This step involves mapping current state security capabilities to the NIST Cybersecurity Framework and evaluate the maturity of current security processes. Baker Tilly will also identify current risks related to weaknesses in the City’s cybersecurity program. Baker Tilly will then review current state capabilities and risks with the City to ensure alignment on Baker Tilly’s initial analysis and identify target state objectives utilizing the Capability Maturity Model (CMMI) Finally, Baker Tilly will take the identified improvement areas and target state maturity objectives to develop our recommendations for the City’s cybersecurity program to meet its target state objectives. Step 4 – Reporting The project team will perform tasks necessary to finalize the initial draft cybersecurity assessment report and review a draft report with the stakeholders. Additionally, the team will submit a final assessment report to the City. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Distribute a draft assessment report and conduct a closing meeting with key stakeholders o Discuss the assessment results, findings, conclusions, and recommendations • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • Cybersecurity Assessment Report and Program Strategy External Penetration Testing Baker Tilly will perform external penetration testing on behalf of the City. Baker Tilly’s approach to conducting these security testing activities involves four (4) primary steps: • Step 1: Assessment Planning and Kick-off • Step 2: Open-Source Information Gathering and Reconnaissance • Step 3: External Penetration Testing • Step 4: Reporting Step 1 – Assessment Planning and Kick-off This step consists of the tasks performed to adequately plan the work necessary to address the overall testing objective and to solidify mutual understanding of the testing scope, objectives, testing process, and timing between stakeholders and assessors. Tasks include: 2 Packet Pg. 92 17 • Baker Tilly will work with the City to finalize the testing scope and project timeline. • Baker Tilly will perform a project kick-off discussion with the City to ensure alignment with the project timeline, testing approach, and deliverables. • Baker Tilly will provide the City with an ISP authorization form and Rules of Engagement documents for signature to confirm testing scope and activities. Step 2 – Open-Source Information Gathering and Reconnaissance This step involves conducting interviews with identified IT security personnel and key stakeholders to identify security capabilities, processes, and currently implemented technologies. Baker Tilly will also review current IT security policy and procedure documentation, as well as network and infrastructure architecture documents. Step 3 – External Penetration Testing Baker Tilly will conduct external penetration testing on up to 300 active and 208 dormant external IP addresses provided by the City. External penetration testing services include: • Confirmation of active versus dormant IP addresses • Identification of services and service versions running on each active system; • Automated vulnerability discovery scanning for each active system; • Penetration attempts on systems identified that have known exploitable vulnerabilities; and • Deep dive exploitation of any identified exploitable vulnerabilities to gain unauthorized access to internal systems and/or data. Step 4 – Reporting The project team will perform tasks necessary to finalize our security testing report and review a draft report with City stakeholders. Additionally, the team will submit a final testing report to the City. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals • Distribute a draft testing report and conduct a closing meeting with key stakeholders o Discuss the testing results, findings, conclusions, and recommendations • Obtain written management responses and finalize a report Deliverables: The following deliverable will be prepared as part of this engagement: • External Penetration Testing Report 2 Packet Pg. 93 18 Schedule of Performance Anticipated Start Date: March 1, 2022 Anticipated End Date: December 31, 2022 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $110,000. The not-to-exceed budget is based on an estimate of 525 total project hours, of which 30 are estimated to be completed by the City Auditor. Reimbursable Expenses We plan to complete the audit work remotely, including all interviews and documentation review. However, if the City requests the assessment team to travel on-site for meetings, interviews, or assessment report readouts, these travel related expenses will be billed in addition to the fees above. 2 Packet Pg. 94 19 Audit Activity 4.15 – Wastewater Treatment Plant Agreement PROFESSIONAL SERVICES TASK ORDER TASK ORDER FY22-004.15 Consultant shall perform the Services detailed below in accordance with all the terms and conditions of the Agreement referenced in Item 1A below. All exhibits referenced in Item 8 below are incorporated into this Task Order by this reference. The Consultant shall furnish the necessary facilities, professional, technical and supporting personnel required by this Task Order as described below. CONTRACT NO. C21179340 OR PURCHASE ORDER REQUISITION NO. (AS APPLICABLE) 1A. MASTER AGREEMENT NO. (MAY BE SAME AS CONTRACT / P.O. NO. ABOVE): 1B. TASK O RDER NO.: FY22-004.14 2. CONSULTANT NAME: Baker Tilly US, LLP 3. PERIOD OF PERFORMANCE: START: March 1, 2022 COMPLETION: December 31, 2022 4 TOTAL TASK ORDER PRICE: $110,000 BALANCE REMAINING IN MASTER AGREEMENT/CONTRACT $TBD 5. BUDGET CODE_______________ COST CENTER________________ COST ELEMENT______________ WBS/CIP__________ PHASE__________ 6. CITY PROJECT MANAGER’S NAME & DEPARTMENT: Greer Stone, Chair of the City Council’s Policy and Services Committee 7. DESCRIPTION OF SCOPE OF SERVICES (Attachment A) MUST INCLUDE: ▪ SERVICES AND DELIVERABLES TO BE PROVIDED ▪ SCHEDULE OF PERFORMANCE ▪ MAXIMUM COMPENSATION AMOUNT AND RATE SCHEDULE (as applicable) ▪ REIMBURSABLE EXPENSES, if any (with “not to exceed” amount) 8. ATTACHMENTS: A: Task Order Scope of Services B (if any): N/A I hereby authorize the performance of the work described in this Task Order. APPROVED: CITY OF PALO ALTO BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ I hereby acknowledge receipt and acceptance of this Task Order and warrant that I have authority to sign on behalf of Consultant. APPROVED: COMPANY NAME: ______________________ BY:____________________________________ Name __________________________________ Title___________________________________ Date ___________________________________ 2 Packet Pg. 95 20 Attachment A DESCRIPTION OF SCOPE OF SERVICES Introduction Attachment A, the Description of Scope of Services, contains the following four (4) elements: • Services and Deliverables To Be Provided • Schedule of Performance • Maximum Compensation Amount and Rate Schedule (As Applicable) • Reimbursable Expenses, if any (With “Not To Exceed” Amount) Services & Deliverables Baker Tilly’s approach to conducting a Wasterwater Treatment Plant Agreement Review involves three (3) primary steps: • Step 1: Audit Planning • Step 2: Process and Control Review • Step 3: Reporting Step 1 – Audit Planning This step consists of the tasks performed to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, audit process, and timing between stakeholders and auditors. Tasks include: • Gather information to understand the environment under review o Understand the organizational structure and objectives o Review the City code, regulations, and other standards and expectations o Review prior audit results, as applicable o Review additional documentation and conduct interviews as necessary • Assess the audit risk • Write an audit planning memo and audit program o Refine audit objectives and scope o Identify the audit procedures to be performed and the evidence to be obtained and examined • Announce the initiation of the audit and conduct kick-off meeting with key stakeholders o Discuss audit objectives, scope, audit process, timing, resources, and expectations o Discuss documentation and interview requests for the audit 2 Packet Pg. 96 21 Step 2 – Process and Control Review This step involves executing the procedures in the audit program to gather information, interview individuals, and analyze the data and information to obtain sufficient evidence to address the audit objectives. The preliminary audit objective is to: (1) Determine whether adequate controls are in place and working effectively to ensure that costs for treatment plan operations are properly accounted for and allocated; (2) Assess the compliance with contracts and regulations. Procedures include: • Interview the appropriate individuals to understand the process, the information system used, and internal controls related to accounting and allocation of costs for treatment plan operations. • Review the contracts, policies and procedures as well as the regulations and standards to identify the criteria to be used for evaluation of compliance and control design and effectiveness • Review the documents (such as contracts and supporting documents for allocation) for the selected allocation transactions • Compare the cost accounting and allocation methodology against the requirements Step 3 – Reporting In Step 3, the project team will perform tasks necessary to finalize audit working papers, prepare and review a draft report with the stakeholders, and submit a final audit report. Tasks include: • Develop findings, conclusions, and recommendations based on the supporting evidence gathered • Validate findings with the appropriate individuals and discuss the root cause of the identified findings • Complete supervisory review of working papers and a draft audit report • Distribute a draft audit report and conduct a closing meeting with key stakeholders o Discuss the audit results, finings, conclusions, and recommendations o Discuss management responses • Obtain written management responses and finalize a report • Review report with members of City Council and/or the appropriate Council Committee • Present the final report to the City Council and/or appropriate Council Committee Deliverables: The following deliverable will be prepared as part of this engagement: • Audit Report Schedule of Performance 2 Packet Pg. 97 22 Anticipated Start Date: March 1, 2022 Anticipated End Date: December 31, 2022 Maximum Compensation Amount and Rate Schedule The not-to-exceed maximum, inclusive of reimbursable expenses (as summarized below) for this Task is $82,500. The not-to-exceed budget is based on an estimate of 400 total project hours, of which 20 are estimated to be completed by the City Auditor. Reimbursable Expenses If circumstances allow, Baker Tilly anticipates planning one on-site fieldwork week. Given this possibility, Baker Tilly could incur reimbursable expenses for this Task. The not-to-exceed maximum for reimbursable expenses for this Task is $4,750. The following summarizes anticipated reimbursable expenses (for three team members): • Round-trip Airfare – $1500 • Rental Car - $400 • Hotel accommodation - $2500 (4 nights) • Food and incidentals – $750 Note that, if current restrictions associated with COVID-19 continue, an on-site visit may not be possible. The project team will work with the City to consider circumstances at the time. 2 Packet Pg. 98 City of Palo Alto Office of the City Auditor Policy & Services Committee Meeting February 28, 2023 The information provided here is of a general nature and is not intended to address the specific circumstances of any individual or entity. In specific circumstances, the services of a professional should be sought. Tax information, if any, contained in this communication was not intended or written to be used by any person for the purpose of avoiding penalties, nor should such information be construed as an opinion upon which any person may rely. The intended recipients of this communication and any attachments are not subject to any limitation on the disclosure of the tax treatment or tax structure of any transaction or matter that is the subject of this communication and any attachments. Baker Tilly Virchow Krause, LLP trading as Baker Tilly is a member of the global network of Baker Tilly International Ltd., the members of which are separate and independent legal entities. © 2018 Baker Tilly Virchow Krause, LLP 2 Packet Pg. 99 1. Present the Utility Work Order Process Review Report •Project Background •Key Observations/Recommendations •Questions & Discussion The CAO thanks the Palo Alto Utilities Department for their work on this audit activity –THANK YOU! 2. Task order review/approval Agenda 2 Packet Pg. 100 Objectives for the audit activity include: •Determine whether adequate controls are in place and working effectively around the work order process •Assess the work order process against best practices •Provide recommendations for improvement related to increased controls or process efficiencies Project Background Audit Planning The OCA performed tasks to adequately plan the work necessary to address the overall audit objective and to solidify mutual understanding of the audit scope, objectives, review process, and timing between stakeholders and auditors. Process Analysis and Testing The OCA tested 6 controls on a sample of work orders to determine if controls were operating effectively. In addition, the OCA reviewed current processes in place and systems used to identify areas of improved controls and efficiencies. Recommendations The OCA identified areas for improvement and drafted a report. 2 Packet Pg. 101 Project Background: Sample Work Order Testing Key controls tested 1. All capital work orders require supervisor approval before work can be performed 2. For development services projects, a permit must be reviewed and approved before work can begin 3. For customer work orders, customer payment must be received before work can begin 4. The Supervisor reviews the completed work order and as-built forms for accuracy 5. For electric work orders, the Utility Coordinator completes a checklist to ensure all documents are complete and included in the job packet (control implemented in August 2021) 6. For closed capital work orders, the assets were recorded to an appropriate plant account 2 Packet Pg. 102 Key Observations & Recommendations - Sample Work order Testing # Observation Recommendation 1 Of the 21 sampled work orders that currently require approval, 3 did not have appropriate signature approval on the work order. All 3 were capital work orders. We recommend that all work orders (including O&M) be approved by someone that has budget responsibility or the ability to approve unbudgeted maintenance work. Approval should be evidenced in writing. 2 Of the 25 sampled work orders, 2 did not have a signature showing review of the completed work order. We also noted 3 work orders that had a signature showing a review was performed, however, there were costs which were incorrectly recorded to the wrong work order indicating the review may not be performed at a detailed enough level and could be improved. The review performed by the Supervisor should include review of all labor and materials including 3rd party invoices to ensure all costs are recorded to the correct work order. 2 Packet Pg. 103 Key Observations & Recommendations - Sample Work order Testing # Observation Recommendation 3 Because this control was not implemented until August 2021 and only pertains to electric work orders, this control only applied to 5 of the sampled work orders. All 5 work orders had a completed checklist. We recommend the Utility Coordinator complete a checklist to show review of verifying completeness of work orders for all utilities. 4 Costs are capitalized to the asset account on a quarterly basis. As such, only 4 of our sampled capital work orders were closed and recorded to the asset account at the time of our testing. All 4 work orders appeared to be recorded to an appropriate asset account (although not all costs were included as noted in control number 4 above). Per Palo Alto, the additional costs will be recorded in Q3 FY22. We recommend work orders be closed and capitalized monthly to prevent a backlog and to ensure depreciation starts immediately when the asset is placed in service 2 Packet Pg. 104 Key Observations & Recommendations - System Utilization # Observation Recommendation 5 Although the water, wastewater, and gas utilities utilize SoGen to track work order progress, the electric utility utilizes SharePoint to share files and track work order status. Scheduling work is often done by the supervisor on a white board. In addition, the operations team has handheld tablets in the field, however, these do not interface with SoGen and they have found that it is easier to handwrite all workorder information manually on paper forms. These paper forms are then given to the utility coordinator to be entered into SoGen, duplicating the data entry function. We recommend the electric utility consider evaluating whether SAP has the capability to effectively track work orders to avoid using side systems. The electric utility should also evaluate whether implementing SoGen, the system used by the water, gas, and wastewater utilities to track work orders, would allow for easier, more accurate work order tracking. A system with the ability to schedule work orders based on priority will also ensure there is not unnecessary downtime or overburdening of worker time. Palo Alto utilities should also consider developing an interface between the handheld devices and SoGen to eliminate duplicate processes and allow stakeholders access to the most up-to-date information as changes are being made in real time. 2 Packet Pg. 105 Key Observations & Recommendations - Design changes and Recording Assets # Observation Recommendation 6 Currently, only major field changes require approval from the engineering supervisor and that approval is oftentimes provided verbally. Any design changes should be approved by an engineer or supervisor. Changes in the field are a safety issue for service and could impact other areas that the field crew may not be aware of. All approvals should be evidenced in writing. 7 For water, gas, and wastewater, the asset additions and retirements that the Business Analyst provides to the Accountant for recording is a report from the SoGen system. These costs are settled to accounts in SAP. The Business Analyst indicated that assets may not have been recorded accurately in the past and that AME should be the system of record when recording asset additions and retirements. If assets are not recorded appropriately, financial, and other reporting becomes less reliable. We recommend that the utilities perform a full system reconciliation of assets in AME and SAP to ensure assets are accurately recorded. Assets in AME and SAP should continue to be reconciled on an annual basis (or cycle counts can be performed monthly where a certain type of asset is counted each month). This reconciliation should be documented and signed-off on. 2 Packet Pg. 106 Key Observations & Recommendations - Asset Reconciliation and KPI’s # Observation Recommendation 8 Asset reconciliations are performed on a quarterly basis. No review of the reconciliation is performed by a separate individual. In addition, the reconciliation process is very manual with the accountant manually entering in numbers instead of using formulas to add the next periods numbers. All reconciliations should be performed on a monthly basis to ensure monthly financials are accurate. All reconciliations should be reviewed for accuracy by another individual. This review should be evidenced in writing. In addition, Palo Alto should consider using more formulas in the reconciliation to reduce the risk of errors that can be caused by manually entering in numbers. 9 The water, wastewater, and gas utilities currently utilize key performance indicators (KPIs) to assist with monitoring their performance around project management and operations. An example of some of the KPIs currently being used are shown in Appendix C. It is our understanding that these same KPIs are currently being developed for the electric utility. We agree that the electric utility would benefit from developing similar KPIs that the water, wastewater, and gas utilities currently use. In addition, the utilities may want to consider adding additional KPIs related to work orders and project management. 2 Packet Pg. 107 Policy & Services Committee action The City Auditor recommends that the Policy & Services Committee take the following action: •Review the Utility Work Order Process Review report and corresponding recommendations for improvement and recommend the City Council accept the report. 2 Packet Pg. 108 The City Auditor recommends that the Policy & Services Committee take the following actions and forward the corresponding report to City Council for consent: Approve the following Task Orders: •FY23-Task 01 – Citywide Risk Assessment •FY23-Task 02 – Annual Audit Plan •Task 04.12 – Wire Payment Process and Controls Review (Extension) •Task 04.13 – Remote and Flexible Work Study (Extension) •Task 04.14 – Cybersecurity Assessment (Extension) •Task 04.15 – Wastewater Treatment Facility Agreement (Extension) •Task 04.19 – Disaster Recovery Preparedness •Task 04.20 – Procurement Process Review Policy & Services Committee action 11 2 Packet Pg. 109 Questions and answers 3 2 Packet Pg. 110 Thank you, it was a pleasure working with you! Amanda Lasinski (920) 210 7796 Amanda.lasinski@bakertilly.com Adriane McCoy (312) 240 2440 Adriane.mccoy@bakertilly.com 2 Packet Pg. 111