Loading...
HomeMy WebLinkAboutStaff Report 2412-3953CITY OF PALO ALTO Policy & Services Committee Special Meeting Tuesday, February 11, 2025 7:00 PM     Agenda Item     2.Office of the City Auditor Presentation of the Utility Billing Audit Report And Recommendation to City Council for Approval Staff Presentation 6 2 0 8 Policy & Services Committee Staff Report From: City Manager Report Type: ACTION ITEMS Lead Department: City Auditor Meeting Date: February 11, 2025 Report #:2412-3953 TITLE Office of the City Auditor Presentation of the Utility Billing Audit Report And Recommendation to City Council for Approval RECOMMENDATION The Office of the City Auditor recommends the Policy and Services Committee recommend the City Council approve the results of the Utility Billing Audit 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 FY 2023 risk assessment, the OCA identified utility billing as a potential area of risk and included this audit on the FY 2024 Audit Plan. ANALYSIS The objectives of the Utility Billing Audit were to: 1. Determine whether the internal controls over the utility billing process are adequate and working effectively to ensure billing is accurate and in compliance with the City's policies and regulations. 2. Determine whether billing adjustments are properly supported and approved. The City of Palo Alto Utilities Department provides the City of Palo Alto with five municipal utility services: electricity, natural gas, water, wastewater, and fiber optics. The City’s Public Works Department also provides refuse collection and processing for recycling, compost and garbage, wastewater treatment, and stormwater management services. Palo Alto is the only 6 2 0 8 city in California that owns and operates a full suite of municipal utility services, including electric, fiber optics, natural gas, water and wastewater. The OCA assessed the City’s efforts to date on the utility billing process through interviewing key staff, analyzing policies and procedures, and selected a sample of Utility Billing Invoices, Utility Billing Adjustments, Billing and Usage Exceptions, and Write-Offs of Uncollectable Accounts for testing to compare the process and controls against City policy and regulations, as well as developed process maps to identify internal controls and control gaps for the meter reading and utility billing adjustment process. Audit findings and recommendations focus on two areas of improvement: •The OCA found utility bills and adjustments are accurately calculated in compliance with City policies and regulations, however, some billing processes were not properly documented or approved; and •The OCA identified some internal control gaps in the Utility Billing Process that can be addressed to improve the overall process. The attached report summarizes the analysis, audit findings, and recommendations. FISCAL/RESOURCE IMPACT The timeline and resource needs for implementation of management’s corrective action plans are identified by management within the attached report. Implementation of the recommendations regarding updates to policies and procedures will be completed within existing resources, however, should new systems or system reconfigurations be necessary, financial implications will be brought forward as necessary. STAKEHOLDER ENGAGEMENT The Office of the City Auditor worked primarily with and would like to thank the City of Palo Alto Utilities department and other departments providing support services to the Utility Billing department for their assistance in conducting this audit. ENVIRONMENTAL REVIEW Council action on this item is not a project as defined by CEQA because contracting for auditing services is an organizational or administrative activity that will not result in direct or indirect physical changes in the environment. CEQA Guidelines section 15378(b)(5). ATTACHMENTS Attachment A - Utility Billing Audit Report January 15, 2025 APPROVED BY: Kate Murdock, City Auditor 1 January 15, 2025 City of Palo Alto Office of the City Auditor Utility Billing Audit Contents Baker Tilly Advisory Group, LP and Baker Tilly US, LLP, trading as Baker Tilly, operate under an alternative practice structure and are members of the global network of Baker Tilly International Ltd., the members of which are separate and independent legal entities. Baker Tilly US, LLP is a licensed CPA firm that provides assurance services to its clients. Baker Tilly Advisory Group, LP and its subsidiary entities provide tax and consulting services to their clients and are not licensed CPA firms. EXECUTIVE SUMMARY...................................................................................................1 INTRODUCTION...............................................................................................................5 DETAILED ANALYSIS.....................................................................................................9 AUDIT RESULTS............................................................................................................12 APPENDICES.................................................................................................................20 1 Executive Summary Purpose of the Audit Baker Tilly Advisory Group, LP (Baker Tilly), in its capacity serving as the Office of the City Auditor (OCA) for the City of Palo Alto (the City), conducted a Utility Billing Audit based on approved Task Order 4.26 as part of the City’s FY24 Audit Plan. The objectives of this audit were to: 1. Determine whether the internal controls over the utility billing process are adequate and working effectively to ensure billing is accurate and in compliance with the City's policies and procedures. 2. Determine whether billing adjustments are properly supported and approved. Report Highlights Finding 1: (Page 12) The OCA found utility bills and adjustments are accurately calculated in compliance with City policies; however, certain billing adjustments and account write-offs were not properly documented or approved per City policy. Overall, the OCA concluded that there is a robust utility billing process in place and found that utility bills are accurate and in agreement with rates annually established by the City. The OCA noted the following positive observations: •For all utility billing invoices tested, the OCA concluded that billing was accurate and in compliance with City policies as we found no test exceptions. •Utility billing is appropriately reviewed for exceptions. •The implementation of AMI meters will provide an opportunity to reduce both the frequency and magnitude of adjustments as it will enable the City of Palo Alto Utilities (CPAU) Department to have more timely access to usage data. While billing adjustments are properly billed, the OCA identified some instances in which utility billing adjustments were not properly documented or approved during the period tested. The OCA selected a sample of adjustments from FY 2023 – FY 2024 for testing. The OCA tested all categories of adjustments, which include consumption, miscellaneous debit/credit, water leak, and GreenWaste adjustments. Out of 60 adjustments tested, there were 10 exceptions noted. These exceptions were primarily due to documentation and approval errors. The OCA observed the following: •For 6 adjustments tested, there was no IC WEB (Interaction Center Webclient) interaction record as the processor did not note the adjustment in SAP per the Processing Adjustments procedural document. 2 EXECUTIVE SUMMARY •For 1 miscellaneous adjustment tested, a Customer Service Specialist (CSS) approved transactions above their authority level. •For 1 miscellaneous adjustment tested, there was no Adjustment Form submitted to verify proper approval. •For 1 miscellaneous adjustment tested, the Invoice Cancellation Form was incorrectly filled out by the CSS to include the incorrect invoice document number. •For 1 consumption adjustment tested, an adjustment was incorrectly entered in the Adjustment Log due to a typographical error; however, the correct amount was entered into SAP. Therefore, this did not result in a billing error to the customer. When proper approvals for adjustments are not obtained and documented, there is potential for inappropriate adjustments, financial losses, or fraud. As approval is currently obtained via DocuSign or manually entered in the Adjustment Log, there is an increased possibility for improper approval due to human error. Additionally, the OCA identified some instances when account write-offs of stale-dated checks were not properly approved during the period tested. The OCA selected a sample of 25 account write-offs of uncollectable utility accounts during FY 2023 – FY 2024 for testing. During the period tested, collections were put on hold from March 4th, 2020 – October 2023 due to COVID-19. Therefore, the OCA did not test the collections process. Out of 25 account write-offs tested, there were 4 exceptions noted. The OCA observed that for all 4 stale-dated check write-offs sampled, the support was not attached to the Quarterly Utilities Invoice Cancellation Memorandum and Form (Quarterly Memo) for proper approval per the City Write-off Policy for Utilities Bad Debt. By omitting support for review and approval, this could lead to inaccurate financial reporting as not all write-offs are properly reviewed. Key Recommendations We recommend that the City review the adjustments process to determine if there are ways to automate approvals in the billing system rather than relying on manual processes which can increase the possibility for human error. Otherwise, the City should review the current Processing Adjustments procedural document to ensure it is comprehensive and includes all necessary steps for accurately recording and reviewing adjustments. The City should determine if an IC WEB interaction record should be submitted for all miscellaneous adjustments and ensure this process is implemented by the CPAU Department. Additionally, if automation is not possible, the City could consider enhancing the procedural document by incorporating a checklist for both the processor and the approver of the adjustment. The approver checklist should include steps to reconcile adjustments entered in SAP with supporting documentation. By defining these steps in a checklist, the reviewer is more likely to identify any data entry errors before approving an adjustment. The CPAU Department should provide regular training 3 EXECUTIVE SUMMARY sessions for staff on any updated procedures and the importance of accurate data entry and review. Lastly, we recommend that the City maintain the practice of incorporating stale-dated check support in the Quarterly Memo and integrate this procedure into training materials or official policies. This will help ensure employees consistently follow this important step and prevent any potential oversights. Finding 2: (Page 16) Two internal control gaps in the utility billing process should be addressed to prevent potential errors and inefficiencies. The OCA identified some internal control gaps in the utility billing process, specifically related to segregation of duties and timely resolution of billing adjustments. •First, the utility billing process does not currently instruct staff on the proper segregation of duties to ensure that the same person processing a consumption adjustment is not the same person approving the adjustment. We noted that for 3 consumption adjustments tested, the same individual processed and approved the adjustments on the Adjustment Log. Segregating duties helps an organization reduce the risk of errors and fraud by distributing responsibilities and accountability among multiple people and/or departments. •Second, outstanding investigations are not regularly reviewed to ensure prompt resolution of billing adjustments. Despite the CSS’s responsibility to follow up on outstanding Service Notifications (SNs), there is no regular, formal review process for these outstanding investigations. While such instances appear to be rare, we noted an example where this lack of a structured review led to a prolonged open investigation for more than 2 months (77 days) after the initial customer complaint and unresolved customer bill, causing delays in resolution. Delays in resolving billing issues can lead to inaccurate financial records and operational inefficiencies as well as customer frustration and dissatisfaction. Key Recommendations We recommend that the CPAU update the utility billing process to include a control to ensure proper segregation of duties between processing and approving adjustments. Without proper segregation of duties, there is a risk of potential error or fraud, as an individual can self-authorize an adjustment without adequate review. Additionally, we recommend that the City consider implementing a regular, periodic review process for outstanding billing adjustments to prevent prolonged unresolved billing issues. The City should establish specific timeframes for reviewing outstanding adjustments and maintain thorough documentation of these reviews. This approach will enable the CPAU Department to better monitor the timeliness and effectiveness of resolving investigations and billing adjustments. 4 EXECUTIVE SUMMARY The City should provide ongoing training and support for CSSs and Field Service Technicians to ensure they are equipped to handle billing adjustments efficiently and effectively. 5 Introduction 1 utilities-at-a-glance-fy-2023_final.pdf 2 https://www.cityofpaloalto.org/Departments/Utilities/Customer-Service/Utilities-at-a-Glance Objective The objectives of this audit were to: 1. Determine whether the internal controls over the utility billing process are adequate and working effectively to ensure billing is accurate and in compliance with the City's policies and procedures. 2. Determine whether billing adjustments are properly supported and approved. Background The CPAU Department provides the City with five municipal utility services: electricity, natural gas, water, wastewater, and fiber optics. The City’s Public Works Department also provides refuse collection and processing for recycling, compost and garbage, wastewater treatment, and stormwater management services. Palo Alto is the only city in California that owns and operates a full suite of municipal utility services, including electric, fiber optics, natural gas, water and wastewater. These services benefit thousands of customers, including residential, commercial, and City facilities. In FY 2023, there were 30,002 electric; 20,533 water; and 23,837 natural gas customer accounts, the majority of which were residential households1. See table below for breakout by customer type (residential, commercial, or City). See below FY 2023 financial highlights (in thousands) per utility2: Electric Water Natural Gas City 142 345 36 Commercial 3,640 3,572 2,202 Residential 26,220 16,616 21,599 26,220 16,616 21,599 3,640 3,572 2,202 142 345 36 0 5,000 10,000 15,000 20,000 25,000 30,000 35,000 Residential Commercial City Number of Customer of Accounts in FY 2023 By Utility & Customer Type 6 INTRODUCTION 3 https://www.cityofpaloalto.org/files/assets/public/v/1/administrative-services/financial-reporting/annual-comprehensive-financial-reports- acfr/current-2011-cafrs/city-of-palo-alto-acfr-single-audit-fy2024-final-unsercured.pdf Electricity led with the highest sales revenue of $171,605 (in thousands) in FY 2023. Per the FY 2024 Annual Comprehensive Financial Report, electric operating revenue totaled $131,273, $164,556, and 178,549 in FY 2022, 2023, and 2024 (amounts in thousands). The average monthly residential bill totaled $451.84 in FY 2023. See chart below for a breakout by utility: Per the FY 2024 Annual Comprehensive Financial Report3, due to the COVID-19 pandemic, the City Council aimed to provide economic relief for residential and commercial utility customers by directing staff to maintain lower rate increases over several years without compromising the safety and integrity of the utility systems. As the City and the economy continues to transition into a steadier state, rates were approved to increase in FY 2024 for all utility services except electric, which decreased, and refuse, which remained flat, resulting in a $11.11, or 3.01%, increase for the average residential bill. $171,605 $72,474 $41,802 $20,694 $2,629 $29,542 $0 $20,000 $40,000 $60,000 $80,000 $100,000 $120,000 $140,000 $160,000 $180,000 $200,000 Electric Natural Gas Water Wastewater Fiber Optics Refuse Sales Revenue Interest, Fees & Other Revenue Commodity Purchase Costs Operating Costs FY 2023 Financial Highlights (in thousands) $103.23 $125.15$107.99 $48.64 $50.07 $16.76 Electric Gas Water Sewer Refuse Stormwater FY 2023 Average Monthly Residential Bill by Utility 7 INTRODUCTION 4 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 with Baker Tilly U.S, LLP for internal audit services for October 2020 through June 2022 with an extension through June 2025. City Council appointed Kate Murdock, Audit Manager in Baker Tilly’s Risk Advisory practice, as City Auditor in May 2024. As a result of transitions in the Audit Office and peer review delays due to the COVID pandemic, an external peer review is targeted for 2025. It should be noted that Baker Tilly’s most recent firmwide peer review was completed in October 2021 with a rating of “Pass”. The scope of that peer review includes projects completed under government auditing standards. A report on the next firmwide peer review should be available in 2025. As of the writing of this report, CPAU management staff reported the City has installed approximately 80% of advanced or smart electric, gas, and water meters as part of the Advanced Metering Infrastructure (AMI) project. Staff reported that AMI implementation will likely be completed by Spring 2025.The AMI meters will empower customers to utilize energy and water more efficiently, better enable customer adoption of distributed energy resources (DER) such as solar photovoltaics, energy storage, and electric vehicles, and enable the timely detection of water leaks. AMI will also enable Utilities to optimize operations and improve reliability by reducing restoration time for outages. Scope The OCA obtained utility billing records from FY 2023 to FY 2024 and assessed whether internal controls over the utility billing process and billing adjustments are working effectively and in compliance with the City's policies. Methodology To achieve the audit objectives, the OCA performed the following procedures: •Interviewed the appropriate individuals to gain an understanding of the organizational structure, processes, and controls related to utility billing. •Analyzed City policies and procedures to identify the criteria to be used for evaluation of control design and effectiveness. •Selected a sample of utility billing invoices, utility billing adjustments, billing and usage exceptions, and write-offs of uncollectable accounts for testing to compare the process and controls against City policies. •Developed Meter Reading and Utility Billing Adjustments process maps, identifying internal controls and control gaps. •Completed audit report of findings, conclusions, and recommendations based on the supporting evidence gathered. Compliance Statement This audit activity was conducted from June 2024 to December 2024 in accordance with generally accepted government auditing standards, except for the requirement of an external peer review4. Those standards require that we plan and perform the audit to obtain sufficient, appropriate evidence to provide a reasonable basis for our findings, observations, and conclusions based on our audit objectives. We believe that the evidence obtained provides a reasonable basis for our findings, observations, and conclusions based on our audit objectives. Organizational Strengths During this audit activity, we noted the expertise and responsiveness demonstrated by the CPAU Department, notably the Director of Customer Service, Credit & Collections Manager, and the Customer Service Manager. We greatly appreciate staff participating in interviews and providing a substantial amount of documentation for testing. 8 INTRODUCTION The OCA greatly appreciates the support of the departments involved in conducting this audit activity. Thank you! 9 Detailed Analysis Utility Billing Process and Controls The utility billing function must accurately measure a customer’s usage and bill customers for their electricity, water, and gas usage based on current utility rates. The Utility Billing Memo provides a summary of the utility billing process in the City of Palo Alto. It outlines the procedures performed for meter reading, billing and invoicing, and processing adjustments. Meter Reading Meter readings for natural gas, electric, and water consumption are required within a 27 to 33-day period. The SAP CCS system, a customer information system, generates a daily meter read route file, which is given to 7 to 9 Meter-Readers. They visit customer addresses to record readings using handheld or drive-by devices. These readings are batch uploaded to the CCS system nightly. For residents with Advanced Metering Infrastructure (AMI) meters, data is transmitted via radio signals to pole-mounted antennas and substations, then stored in the Head End System (HES). The data is verified, stored in the Meter Data Management System (MDMS), and uploaded to the billing system for processing. If meter readings fall outside pre-programmed tolerance bands, the system generates an exception report. CSSs perform an “Implausibles” review of these exceptions using account history to determine if the readings are reasonable. If accuracy is in question, a “check-read” is requested. Once validated or corrected, the account proceeds to the next billing step. If exceptions remain, the account cannot proceed to invoicing. The City also accepts monthly self-readings from customers, which are subjected to the same tests as those by Meter Readers. Billing and Invoicing Invoices are pre-numbered and created in the CCS system, which Customer Service and Credit Collections use to track the outstanding balance owed by customers, usage statistics, past due amounts, etc. Billing charges that appear out-of-range must pass a “Billing Outsorts” review to ensure they are reasonable. If charges fail standard financial controls, an exception report is generated for further review by Customer Service staff. Accounts failing validation are held from invoice processing until manually released. Monthly customer invoices are created in the CCS system and update the Accounts Receivable (A/R) account balance in the SAP FI-CA system nightly. This system tracks customer payments, late fees, and account adjustments. Adjustments 10 DETAILED ANALYSIS Adjustments are initiated from various sources, including customer disputes or exceptions flagged during the “Implausibles” or “Billing Outsorts” reviews. Account adjustments and credits are prepared by Utilities Customer Service staff. Before processing, these are reviewed and approved by varying levels of Utilities management based on organizational level and financial limits. If the adjustments exceed internal signing authority limits, additional reviews and approvals from external departments are required. Financial Authorization Levels and Signing Authority: •Customer Service Specialist – Authorized to approve billing adjustments up to $500 per transaction per account. •Customer Service Specialist Lead – Authorized to approve billing adjustments of up to $2000 per transaction per account. •Manager, Utilities Credit and Collection - Authorized to approve billing and financial transactions up to $19,999 per transaction. •Manager, Utilities Customer Service - Authorized to approve billing and financial transactions up to $19,999 per transaction. •Assistant Director, Utilities Customer Support Services Division- Authorized to approve billing and financial transactions up to $65,000 per transaction. •General Manager, Utilities Department –Authorized to approve billing and financial transactions greater than $65,000. Twice per month the SAP ZCAR002 report of adjustments/credits is generated by the billing system. Utilities Credit and Collections staff review the report to make sure that the scheduled transactions and adjustments have been processed correctly in the billing system. See Appendices for flowcharts showing the abbreviated current processes and controls in place. Write-off Policy for Utilities Bad Debt A quarterly memorandum requesting approval for “Utility Invoice Cancellation” is submitted by the Utilities Credit Collection Specialist, reviewed by the Utilities Back Office, and approved by the Manager of Utilities Customer Service and the Utilities Assistant Director of Customer Support Service. Utilities Debt Collection Guidelines. Accounts are defined by their aging as follows: 11 DETAILED ANALYSIS •1 to 30 days Current – No action •31 to 90 days Past Due – Automated system letter generation •91 to 180 days Delinquent – Internal Collections while customer account is in an Active Status •181 days to 3 years Collection Agency - External Collections while customer account is in an Inactive Status and Accounting calculates Allowance for Doubtful Customers and Bad Debt •4+ years and beyond Write-off - Cancellation of Uncollectible Utilities Accounts Receivable and Write-off The exceptions below are all considered automatic cancellation of uncollectible utilities accounts receivable and write-off: •Amounts <$25 and 91+ days •Discharged/Uncollectible Bankruptcies and 1+ days •Verified deceased and 1+ days •Deemed as uncollectible by external collection agency The thresholds below require signatures of approval before write-off occurs: •$.01 or more Signed by Assistant Director of Customer Support Services •$10,000 or more Signed by Director of Utilities Department and Director of Administrative Services Department • $25,000 or more Signed by City Manager or Assistant City Manager 12 AUDIT RESULTS Audit Results Finding 1: The OCA found utility bills and adjustments are accurately calculated in compliance with City policies; however, certain billing adjustments and account write- offs were not properly documented or approved per City policy. Overall, the OCA concluded that there is a robust utility billing process in place and found that, based on our testing, utility bills are accurately calculated. During our assessment of the City’s policies and procedures as well as testing of internal controls over the utility billing process, the OCA concluded that the process is working as intended. Specifically, the OCA performed testing over utility billing invoices, utility billing adjustments, billing and usage exceptions, and write-offs of uncollectable accounts to compare the process and controls against City policies. For all utility billing invoices tested, the OCA concluded that billing was accurate and in compliance with City policies as we found no test exceptions. On a monthly basis, utility billing invoices are generated from SAP based on monthly usage and the City Council approved rates. These are then billed to customers. To test the completeness and accuracy of utility billing invoices, the OCA selected an invoice from each account class (Residential Single Family, Residential Multi Family, Commercial, Industrial, Public Facilities Non-City, City, and Residential Multi Family Master Meter) during FY 2023 – FY 2024. This resulted in a sample size of 7 invoices. In our testing of utility billing invoices, we found no exceptions. For all invoices tested, the utility rates billed on the customer invoice aligned with City Council approved rates and the invoice amount and usage was accurately reflected in the system-generated reports from SAP. Utility billing is appropriately reviewed for exceptions. Prior to customer billing of invoices, CPAU generates a daily exception report and reviews it for implausible and billing outsorts exceptions. The exception reports flag when meter readings or billing charges fall outside pre-programmed tolerance bands and need to be reviewed to ensure they are reasonable. If exceptions are deemed reasonable during a daily review, they are released for billing. However, if the accuracy of an exception is in question, it is investigated further and may result in an adjustment. The OCA tested a sample of implausibles and billing outsorts exceptions from randomly selected dates during FY 2023 – FY 2024. The OCA randomly selected 4 dates from each year that fell between FY 2023 - FY 2024. The OCA tested 1 implausible and 1 billing outsort for each date, excluding one date from testing that did not have Outsorts or Implausibles 13 AUDIT RESULTS 5 https://www.cityofpaloalto.org/files/assets/public/v/1/administrative-services/financial-reporting/annual-comprehensive-financial-reports- acfr/current-2011-cafrs/city-of-palo-alto-acfr-single-audit-fy2024-final-unsercured.pdf Exception Report. This resulted in testing 22 samples. In our testing of implausibles and billing outsorts exceptions, we found no exceptions. For all implausibles and billing outsorts exceptions tested, the exception reports included appropriate amounts based on established parameters and were properly reviewed by the CSS to evaluate whether the readings are determined to be reasonable before released for billing or resulting in an adjustment. With the implementation of AMI Meters, the City is confident that there will be a reduction in the number and magnitude of adjustments. With the implementation of Advanced Metering Infrastructure (AMI) Meters in FY 2024, the CPAU Department will be able to flag high usage of meters on a daily rather than monthly basis. This will enable more timely detection of possible water leaks or meter read errors. Additionally, customers will be able to track their own meter usage as the AMI system can share usage information with customers. According to the FY 2024 Annual Comprehensive Financial Report5, the City has installed approximately 38,000 or 40% electric, gas, and water AMI meters. As of the writing of this report, staff reported that CPAU has installed approximately 80% of water meters. Additionally, CPAU staff indicated that AMI implementation will likely be completed by Spring 2025. The OCA identified some instances in which utility billing adjustments were not properly documented, supported, or approved during the period tested. Utility billing adjustments are initiated from a few different sources: 1) Customer calls to question or dispute billings; 2) Incorrect read identified while processing “Implausible” exceptions; 3) Miscellaneous billing adjustments submitted to CSS for addition to the account, 4) Incorrect billing charges are identified while processing “Billing Outsorts” exceptions, and 5) annual reconciliation of the refuse service data. Any of the above actions may result in a consumption or miscellaneous adjustment. Consumption adjustments primarily result from incorrect reads identified while processing implausibles exceptions. Miscellaneous adjustments result from credit/debiting a customer account for billing discrepancies, a water leak credit, or from periodic emails received from GreenWaste, a waste collection service provider for the City of Palo Alto, to process refuse adjustments. See table below: Adjustment Type Table Adjustment Type Adjustment Cause Consumption Implausibles Exceptions Billing Discrepancies / Billing Outsorts ExceptionsMiscellaneous Water Leak Credit 14 AUDIT RESULTS GreenWaste Adjustment The City has a Processing Adjustments procedural document in place which describes the steps taken to process adjustments. See Appendix B: Utility Billing Adjustment Process Map for the abbreviated current processes and controls in place for processing adjustments. The OCA requested a population of adjustments from FY 2023 – FY 2024 to select a sample for testing. The OCA sampled all categories of adjustments and determined that the following sample sizes were appropriate using AICPA sample size guidance: 25 consumption adjustments and 35 miscellaneous adjustments (13 miscellaneous debit/credit adjustments, 12 water leak adjustments, and 10 GreenWaste adjustments). This totaled a sample size of 60 adjustments tested for FY 2023 – FY 2024. Out of 60 adjustments tested, there were 10 exceptions noted. These exceptions were primarily due to documentation and approval errors. The OCA observed the following: •For 6 adjustments tested, there was no IC WEB interaction record. The Processing Adjustments procedural document states that the CSS goes to IC WEB to note the account for Miscellaneous Adjustments. Failure to note the adjustment in IC WEB results in gaps in documentation used internally by the CPAU Department. •For 1 miscellaneous adjustment tested, a CSS approved transactions above their authority level. When the CSS entered signatures in DocuSign, they accidentally placed the signature and date block in the wrong place on the Miscellaneous Adjustment Form. The absence of proper approval from the appropriate authorization level and signing authority can result in inaccurate or unauthorized adjustments. •For 1 miscellaneous adjustment tested, there was no Miscellaneous Adjustment or Invoice Cancellation Form submitted. The Processing Adjustments procedural document states that the CSR (Utilities Customer Service Center Representative) will fill out an Invoice Cancellation Form if the entire invoice needs to be cancelled. Failing to submit the Invoice Cancellation Form results in gaps in documentation and does not allow verification of proper review and approval of the invoice cancellation. •For 1 miscellaneous adjustment tested, the Invoice Cancellation Form was incorrectly filled out by the CSS to include the incorrect Invoice Document Number. This resulted in a data entry error on the Invoice Cancellation Form. •For 1 consumption adjustment tested, an adjustment was incorrectly entered in the Adjustment Log, due to a typographical error, however, the correct amount was updated in SAP. Therefore, this did not result in a billing error to the customer. 15 AUDIT RESULTS When proper approvals for adjustments are not obtained and documented, there is potential for inappropriate adjustments, financial losses, or fraud. As approval is currently obtained via DocuSign or manually entered in the Adjustment Log, there is an increased possibility for improper approval due to human error. Some account write-offs of stale-dated checks were not properly approved during the period tested. The City collects customer payments and adjusts A/R for cash received. Accounts that are aged past 30 days are considered “past due” and may be escalated to internal collections, an external collections agency, and eventually written off, as demonstrated in the table below: Customer Account Aging Table Aging Period Categorization Action Taken 1 to 30 days Current No action 31 to 90 days Past Due Automated system letter generation 91 to 180 days Delinquent Internal Collections 181 days to 3 years Collection Agency External Collections 4+ years Write-off Cancellation of A/R and Write-off Cancellation of uncollectable utilities A/R and write-off occurs during the following exceptions: •Aged 4+ years •Amounts <$25 and 91+ days •Discharged/Uncollectible Bankruptcies and 1+ days •Verified deceased and 1+ days •Deemed as uncollectible by external collection agency For invoice cancellation and write-off of customer accounts, a Quarterly Utilities Invoice Cancellation Memorandum and Form (Quarterly Memo) is submitted for review and approval of cancellation of uncollectible utilities accounts receivable and write-off. The OCA selected a sample of 25 account write-offs of uncollectable utility accounts during FY 2023 – FY 2024 for testing. During the period tested, collections were put on hold on March 4th, 2020 – October 2023 due to COVID-19. Therefore, the OCA did not test the collections process. Out of 25 account write-offs tested, there were 4 exceptions noted. The OCA observed that for all 4 stale-dated check write-offs sampled, the support, showing proper approval for the write-off, was not attached to the Quarterly Memo per the City Write-off Policy for Utilities Bad Debt. By omitting support for review and approval, this could lead to inaccurate financial reporting if all write-offs are not properly reviewed. Per discussion with the CPAU Department, these were unintentionally omitted from the reports by an employee in training. The amounts of each stale-dated check did not exceed $25. Per review of the FY 24-25 1st 16 AUDIT RESULTS Quarter Memo, the OCA observed that stale-dated check write-offs were properly included in the attached support. Recommendation We recommend that the City review the adjustments process to determine if there are ways to automate approvals in SAP rather than relying on manual processes which can increase the possibility for human error. Otherwise, the City should review the current Processing Adjustments procedural document to ensure it is comprehensive and includes all necessary steps for accurately recording and reviewing adjustments. The City should determine if an IC WEB interaction record should be submitted for all miscellaneous adjustments and ensure this process is implemented by the CPAU Department. Additionally, if automation is not possible, the City could consider enhancing the procedural document by incorporating a checklist for both the processor and the approver of the adjustment. The approver checklist should include steps to reconcile adjustments entered in SAP with supporting documentation. By defining these steps in a checklist, the reviewer is more likely to identify any data entry errors before approving an adjustment. The CPAU Department should provide regular training sessions for staff on any updated procedures and the importance of accurate data entry and review. Lastly, we recommend that the City maintain the practice of incorporating stale-dated check support in the Quarterly Memo and integrate this procedure into training materials or official policies. This will help ensure employees consistently follow this important step and prevent any potential oversights. Management Response Responsible Department(s): CPAU Concurrence: Agree Target Date: Q4 CY 2025 Action Plan: Staff concur with the audit finding regarding the benefits of automation in mitigating human error. As the Office of the City Auditor (OCA) acknowledges, automation is not always feasible or readily attainable. Currently, staff adhere to the established standard operating procedures outlined in the Processing Adjustments procedural document to ensure accurate processing. To further address the finding, we will undertake the following actions: •Automation Assessment: Staff will assess the effort and costs associated with automating the adjustment process within the current SAP environment. These specifications will be considered during the development of the next system upgrade. •Procedural Enhancement: Staff will revise the Processing Adjustments procedural document to incorporate a reference to an adjustment checklist. This will enhance clarity and ensure all necessary steps are followed. 17 AUDIT RESULTS Furthermore, to ensure comprehensive clarity and consistency across all related processes, the following areas within the procedural document will be reviewed and updated as needed: inclusion of the ICWEB interaction record with each adjustment; clarification of training requirements and documentation expectations at each staff/approval level; all processes, including the quarterly write-off process, reflect appropriate backup and review procedure expectations in the checklist. Targeted changes include: •Adjustments (Misc. Adjustments, Invoice Cancellations, Green Waste Adjustments, Water Leak Credits) are to be submitted in DocuSign for approval process, according to the Authority Level. •Adding that an IC WEB interaction record is required for adjustments. (Misc. Adjustments, Invoice Cancellations, Green Waste Adjustments, Water Leak Credits) •Review and update the Miscellaneous Adjustment and Invoice Cancellation forms including process for checking data in the form prior to approving and processing. Process to include steps to verify information in SAP. •Review and update Adjustment Log. Include steps to verify information in SAP before providing approval. Clarifying approval process, to include screenshots of the steps. •The City’s Quarterly Write off training instructions will add the process to include the stale dated checks on the Quarterly Write-Off Memo Finding 2: Two internal control gaps in the utility billing process should be addressed to prevent potential errors and inefficiencies. Through our testing and analysis of the process map, the OCA identified some internal control gaps in the billing process, specifically related to segregation of duties and timely resolution of billing adjustments. The CPAU Department does not enforce proper segregation of duties between processing and approving consumption adjustments. The utility billing process does not currently instruct staff on the proper segregation of duties to ensure that the same person processing a consumption adjustment is not the same person approving the adjustment. In the current utility billing process, the same individual can both record and self- approve adjustments. For 3 consumption adjustments tested, the same individual processed and approved the adjustments on the Adjustment Log. The absence of segregation of duties between processing and approving consumption adjustments can result in inadequate review and increased risk for potential errors or fraud. The City can minimize these risks by utilizing more than one person to complete they types of tasks. Segregating duties helps an organization reduce the risk of errors and fraud by distributing responsibilities and accountability among multiple people and/or departments. Outstanding investigations are not regularly reviewed to ensure prompt resolution of billing adjustments. 18 AUDIT RESULTS Adjustments can originate from various sources, such as customer billing disputes or when atypical billing and/or consumption amounts are during review via exception reports. If the accuracy of a customer bill is in question, the CSS opens a Service Notification (SN) in SAP to investigate the potential issue. After the SN is created, a Field Service Technician conducts a further review of the meter. The CSS is responsible for monitoring the status of any outstanding SNs. Despite the CSS’s responsibility to follow up on outstanding SNs, there is no regular, formal review process for these outstanding investigations. This lack of a structured review can lead to prolonged open investigations and unresolved customer bills, causing delays in resolution. We noted an example where this lack of a structured review led to a prolonged open investigation for more than 2 months (77 days) after the initial customer complaint and unresolved customer bill, causing delays in resolution. Delays in resolving billing issues can lead to inaccurate financial records and operational inefficiencies as well as customer frustration and dissatisfaction. Recommendation We recommend that the City consider implementing a control to ensure proper segregation of duties between processing and approving adjustments. Without proper segregation of duties, there is a risk of potential error or fraud, as an individual can self-authorize an adjustment without adequate review. Additionally, we recommend that the City consider implementing a regular, periodic review process for outstanding billing adjustments to prevent prolonged unresolved billing issues. The City should establish specific timeframes for reviewing outstanding adjustments and maintain thorough documentation of these reviews. This approach will enable the CPAU Department to better monitor the timeliness and effectiveness of resolving investigations and billing adjustments. The City should provide ongoing training and support for CSSs and Field Service Technicians to ensure they are equipped to handle billing adjustments efficiently and effectively. Management Response Responsible Department(s): CPAU Concurrence: Agree Target Date: Q4 CY 2025 Action Plan: Staff concur that standardized processes and robust internal controls are essential for mitigating the risks of errors and fraud. As noted in the prior response, staff are revising the Processing Adjustments procedural document to include a reference to a comprehensive adjustment checklist, ensuring all required steps are clearly delineated and consistently followed. The Processing Adjustments procedural document and the new checklist will incorporate the following enhancements: •Clear Approval Authorities: A review and update of approval authorities will be conducted, specifying both dollar thresholds and appropriate signing authority. 19 AUDIT RESULTS •Implausibles Documentation: The Implausibles documentation will be revised to include a documented process for managing outstanding investigations. •Training Expectations: Consistent with the above, training expectations related to the revised procedures and controls will be clearly outlined within the documentation. To further strengthen oversight and segregation of duties, staff will facilitate coordination between the Utilities Department and the Administrative Services Department providing an independent review of these procedure revisions to ensure their effectiveness and alignment with the City’s internal control practices. Review and update Implausibles documentation to include a process for these outstanding investigations, which includes checkpoints for follow up. •Review DocuSign documentation to include expectations for review steps and timely approvals deadlines. •Coordinate training with CSR, CSS for follow up procedures for Service Notifications investigations. •Project coordinator currently working on checklist and updated policies and procedures for follow up on billing investigations. •Completion target is resolutions within two billing cycles. •Communicate with Meter Shops on timely resolution of field issues. Additional Observations: 1. Formalizing and more consistently documenting adjustments may improve efficiency The CSR/CSS manually inputs adjustments into the Adjustment Log, an excel sheet kept by the CPAU Department. When entering an adjustment in the log, the CSR/CSS notes the reasoning for the adjustment in the “Adjustment Reason” and/or “Notes” column of the log. Per the OCA’s observation of the adjustment documentation in the adjustment logs, we noted that there were blank rows, and a variety of adjustment reasons and notes entered in the Adjustment Log. The adjustment notations did not follow a consistent naming convention or provide a clear and detailed reasoning for the adjustment. The CPAU Department could enhance the adjustment documentation process by creating a bank of approved adjustment reasons (reason codes) and notes to ensure consistency throughout the process. Additionally, by formalizing notations of adjustments and the reasoning, the reviewer could better understand the adjustment justification and ensure that it was appropriate. 20 AUDIT RESULTS 6 7 KPIS and Metrics That Your Utility Needs to Know - Silver Blaze Solutions 7 7 KPIs to Streamline Utility Billing 2. Formally tracking key Utility Billing indicators could aid CPAU in monitoring performance over time CPAU management said they do not formally track utility billing Key Performance Indicators (KPIs) but do monitor some indicators periodically. There are several utility billing KPIs that could help the utility track its billing function performance over time and improve its effectiveness and efficiency. Based on our analysis of a few industry publications, use of such indicators can help management make more informed decisions, improve operations and increase customer satisfaction67. Some of these indicators include: •Billing Error Rate – percentage of total bills that have errors •Customer Satisfaction Score – a direct measure of satisfaction with services provided •Collection Rate – percentage of billed amount received within a specific time frame •Percentage of On-Time Payments – percentage of payments that were paid timely •First Contact Resolution Rate – percentage of customer inquiries or issues resolved during the first interaction Tracking KPIs over time can help an organization see trends, note when performance either improves or worsens, and determine what areas may need assessment. 21 Appendices 22 Appendix A: Meter Reading Process Map 23 Appendix B: Utility Billing Adjustment Process Map Baker Tilly Advisory Group, LP and Baker Tilly US, LLP, trading as Baker Tilly, are members of the global network of Baker Tilly International Ltd., the members of which are separate and independent legal entities. Baker Tilly US, LLP is a licensed CPA firm that provides assurance services to its clients. Baker Tilly Advisory Group, LP and its subsidiary entities provide tax and consulting services to their clients and are not licensed CPA firms. City of Palo AltoOffice of the City Auditor (OCA) Policy & Services Committee Meeting Utility Billing Audit, January 15, 2025 Presenter: Kate Murdock, City Auditor, Manager, Baker Tilly February 11, 2025 2 Audit Objectives •Determine whether utility billing internal controls are adequate and ensuring billing is accurate and in compliance with City policies and procedures •Determine whether billing adjustments are properly supported and approved 3 Background •City of Palo Alto Utilities (CPAU) provides 5 municipal utility services: electricity, natural gas, water, wastewater, and fiber optics. •Public Works provides recycling, compost, garbage, wastewater, and stormwater management services. •As of mid-January, approximately 80% of the City’s Advanced Metering Infrastructure (AMI) project has been completed. •Empower residents to use energy and water resources more efficiently •Enable customers to more easily adopt Distributed Energy Resources (DER) such as solar photovoltaics and energy storage 4 Finding 1: Billing Accuracy Finding Utility bills and adjustments are accurately calculated in compliance with City policies Some billing adjustments and account write-offs were not properly documented or approved Out of 60 adjustments tested, we noted 10 were not properly documented or approved Out of 25 write-offs tested, we noted 4 were not properly approved Implementation of AMI meters will likely reduce the number and magnitude of billing adjustments 5 Finding 1: Billing Accuracy Recommendation We recommend the City: •Review the adjustments process to determine if there are ways to automate approvals •Ensure the adjustments procedural document includes all necessary steps •Develop process checklists for the both the processor and reviewer •Provide regular training on procedures and any changes/updates •Maintain practice of incorporating stale –dated check support in the Quarterly Memo and ensure staff receive training on this process 6 Finding 2: Internal Control Gaps Finding Two internal control gaps in the utility billing process should be addressed to prevent potential errors and inefficiencies Department does not enforce proper segregation of duties for consumption adjustments Outstanding investigations are not regularly reviewed to ensure prompt resolution of billing statements 7 Finding 2: Internal Control Gaps Recommendation We recommend the City: •Consider implementing a control to ensure proper segregation of duties between processing and approving adjustments •Consider implementing a regular, periodic review process for outstanding billing adjustments 8 Additional Observations Finding 1.Formalizing and more consistently documenting adjustments may improve efficiency 2.Formally tracking key Utility Billing indicators could aid CPAU in monitoring performance over time a)Billing error rate b)Customer Satisfaction Score c)Collection Rate d)Percentage of On-Time Payments e)First Contact Resolution Rate Questions? Baker Tilly Advisory Group, LP and Baker Tilly US, LLP, trading as Baker Tilly, operate under an alternative practice structure and are members of the global network of Baker Tilly International Ltd., the members of which are separate and independent legal entities. Baker Tilly US, LLP is a licensed CPA firm that provides assurance services to its clients. Baker Tilly Advisory Group, LP and its subsidiary entities provide tax and consulting services to their clients and are not licensed CPA firms. The name Baker Tilly and its associated logo is used under license from Baker Tilly International limited. 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. © 2024 Baker Tilly Advisory Group, LP