Quarterly Audit and Performance Review Report — CleanPowerSF Sfpuc Commission, November 8, 2022

Quarterly Audit and Performance
Review Report
FY 2022-23, Q1
November 8, 2022
Irella Blackwood, Audit Bureau Director
1 FY 2022-23 Audit Plan Portfolio Status
28 Audits and Assessments to Date
Status Count
Completed 4
In Progress 15
Upcoming 9
Total 28
2 Upcoming
32% Completed
14%
In Progress
54%
Audits Completed and Reports Issued
Audit / Assessment Oversight
Body Report Date
CY 2021 Post Audit Controller 8/1/2022
FY 2021-22 Annual Physical
Inventory Count SFPUC, Finance 9/7/2022
3 Open CSA Audits and
Recommendations
4 # Audit Title
Report
Issued
Date
Open
Rec
Total
Rec
Months
Open
%
Complete
Target
Close
Date
External Affairs
1 Significant Changes Are Needed to the Design, Monitoring, and Control of the San Francisco Public Utilities Commission’s Social Impact Partnership Program
12/9/2021 4 7 11 43% 12/31/2022
Revenue Bond Oversight Committee
2 The San Francisco Public Utilities Commission Spent Revenue Bond Funds for Allowable Purposes but Should Improve Its Controls Over Reporting and Quality Assurance
2/9/2022 2 2 9 0% 12/31/2022
Total 6 9 33%
5 FY 2022-23, Q2 Outlook
Completing
• FY 2021-22 Audited Financial Statements
• Franchise Fee Audits: PG&E and Energy Center SF LLC
• FY 2020-21 Wholesale Revenue Requirement: Statement of Changes in Balancing Account
• FY 2021-22 Annual Comprehensive Financial Report
• FY 2021-22 Popular Annual Financial Report
• Public Integrity Assessment: SOLIS Procurement
• SSIP CS-165 Program Management Contract Audit
• IT / OT Network Audit
• 2020-22 CAISO Scheduling Coordinators Self-Audit
• Revenue Lease Audit: Crystal Springs Golf Partners LP • SFPUC Revenue Bond Programs Audit: Phase II
6 FY 2022-23, Q2 Outlook
Upcoming
• FY 2021-22 Wholesale Revenue Requirement: Statement of Changes in Balancing Account
• FY 2022-23 Interconnection Security Agreement
• Enterprise Risk Management: Cyber Risk Assessment

THANK YOU
Feedback & Questions?
7 OUR MISSION: To provide our customers with high-quality, efficient and reliable water, power and sewer services in a manner that values environmental and community interests and sustains the resources entrusted to our care.

Audit Bureau | Business Services
525 Golden Gate Avenue, 13th Floor
San Francisco, CA 94102
T 415.934.5707
F 415.554.1877

Date: November 8, 2022

To:

Commissioner Newsha Ajami, President
Commissioner Sophie Maxwell, Vice President
Commissioner Tim Paulson
Commissioner Tony Rivera
Commissioner Kate Stacy

Through: Dennis J. Herrera, General Manager

From: Irella Blackwood, Audit Bureau Director

Subject: FY 2022-23 Q1 Audit and Performance Review Report

This memorandum summarizes the San Francisco Public Utilities Commission (SFPUC) Quarterly Audit and Performance Review (QAPR) report, as of FY 2022-23 Q1.

I. Completed Audits

There were two completed audits since the last quarterly report:

1. Calendar Year 2021 Post Audit | Augus t 1, 2022
Report Link: https://tinyurl.com/55dtyrut

Report Summary: The Controller’s Office performed the post audit to examine the effectiveness of the design and implementation of each department’s accounting and internal control practices and compliance with City laws, regulations, and policies.

Audit Findings Summary: The post audit highlighted SFPUC’s strengths in compliance with the year-end close schedule, clearing receipts, and documentation related to budget entries, journal entries, and cash receipts. In response to the audit, SFPUC staff will continue to evaluate opportunities to promote and improve compliance with particular focus on observations noted in the areas for improvement, specifically the timely processing of invoices and accurate recording of grant terms in the financial system.

2. FY 2021-2
2 Warehouse Inventory Counts | September 7, 2022
Report Link: https://tinyurl.com/pxm65ztp

Report Summary: The SFPUC engaged third-party auditor Crowe, LLP (Crowe) to perform FY 2021-22 physical inventory counts at locations in Millbrae, Sunol Yard, Hetchy Power In-City, Moccasin, Wastewater Southeast, City Distribution Division (CDD), and Auxiliary Water Supply System (AWSS).

Audit Findings Summary: The audits found variances between the physical count and the amounts reported in the inventory system (Maximo). The four main findings were attributed primarily to:

1. Minor miscounts or errors in data entry

2. Inventory updates not posted to Maximo in a timely fashion

3. Items not labeled or organized in a manner conducive to inventory counting

2 4. Interface issues between automated fuel tracking system and Maximo

Management concurred with the recommendations and provided additional detail regarding mitigating actions.

II. Audit Recommendation Status

6 audit recommendations remain open for two audits: the SFPUC Revenue Bond Programs Audit: Phase I and the Social Impact Partnership (SIP) Program Audit. All recommendations are on target to be implemented by the end of the calendar year.

If you have questions, please contact me at [email protected].

Attachment: FY 2022-23 Audit Plan, By Status

CC: Ronald P. Flynn, Deputy General Manager
Nancy L. Hom, Chief Financial Officer & AGM Business Services

3 # Quarter
Status Audit Type Enterprise / Bureau Audit Name Oversight Body 1 [Updated]
Completed Financial Water
Annual
Physical Inventory Count, FY 2021-22 SFPUC, Finance 2 [Updated] Completed Financial Wastewater
3 [Updated]
Completed Financial Hetch Hetchy Water & Power
4 [Updated]
Completed Financial Business Services, Finance Pos t Audit, CY 2021 Controller 5 In Progress Financial Water
Audited Financial Statements, FY 2021-22 Controller 6 In Progress Financial Wastewater 7 In Progress Financial Hetch Hetchy Water & Power
& CleanPowerSF
8 In Progress Financial Power Franchise Fee Audit:

Pacific Gas & Electric Company Controller
9 In Progress Financial Power Franchise Fee Audit: Energy Center SF LLC Controller 10 In Progress Financial Business Services, Finance Whol esale Revenue Requirement: Statement of Changes in Balancing Account, FY 2020-21 BAWSCA
11 [Updated]
In Progress Financial Business Ser vices, Finance Annual Comprehensive Financial Report, FY 2021-22 SFPUC
12 [Updated]
In Progress Financial Business Services, Finance Popul ar Annual Financial Report, FY 2021-22 SFPUC 13 [Updated]
Upcoming Financial Power 2022 Gr een-e Verification Audit, SuperGreen Center for
Resource
Solutions
14 [Updated]
Upcoming Financial Power 2022 Gr een-e Verification Audit, SuperGreen Saver
Center for
Resource
Solutions
15 [Updated]
Upcoming Financial Business Services, Finance Pos t Audit, CY 2022 Controller 16 [Updated]
Upcoming Financial Business Services, Finance Whol esale Revenue Requirement: Statement of Changes in Balancing Account, FY 2021-22 BAWSCA
17 In Progress Performance Infrastructure Public Integrity Assessment:

SOLIS Procurement CSA, Controller
18 In Progress Performance Infrastructure SSIP CS-165 Program Management Contract Audit CSA, Controller
19 In Progress Performance Business Services, ITS IT / OT Network Audit CSA, Controller 20 [Updated]
In Progress Performance Hetch Hetchy Water & Power Rel iability Standards Compliance Audit WECC / NERC 21 [Updated]
In Progress Performance Power CA Independent System Operator (CAISO) Scheduling Coordinators Self-Audit, 2020-22 CAISO
22 Upcoming Performance Business Services, ITS Interconnection Security Agreement, FY 2022-23 CSA, Controller
23 [Updated]
Upcoming Performance Business Services, ITS Cyb ersecurity Maturity Assessment, FY 2022-23 CSA, Controller
Quarterly Audit & Performance Review Report
FY 2022-23 Audit Plan, By Status
As of September 30, 2022

Status

Completed: 4
In Progress: 15
Upcoming: 9
Total: 28

4 # Quarter
Status Audit Type Enterprise / Bureau Audit Name Oversight Body 24 Upcoming Performance Business Services, ITS Enterprise Risk Management:

Cyber Risk Assessment SFPUC
25 In Progress Concessions,
Lease Revenue Real Estate Services Rev enue Lease Audit:

Crystal Springs Golf Partners LP CSA, Controller
26 [Updated]
Upcoming
Concessions,
Lease Revenue Real Estate Services Rev enue Lease Audit: Mission Valley Rock CSA, Controller 27 In Progress
Revenue Bond
Oversight
Committee
RBOC SFPUC Revenue Bond Programs Audit:

Phase II RBOC
28 [Updated]
Upcoming
Revenue Bond
Oversight
Committee
RBOC SF PUC Revenue Bond Programs Audit:

Phase III RBOC

CITY AND COUNTY OF SAN FRANCISCO
OFFICE OF THE CONTROLLER Ben Rosenfield
Controller
Todd Rydstrom
Deputy Controller

415-554-7500 City Hall • 1 Dr. Carlton B. Goodlett Place •Room 316• San Francisco CA 94102-4694 FAX 415-554-7466

DATE: August 1, 2022

TO: Dennis Herrera, General Manager, Public Utilities Commission

FROM: Ben Rosenfield, Controller

SUBJECT: Results of CY2021 Post Audit & Continuous Monitoring Program

Thank you for your staff’s support of the Controller’s continuous monitoring and post audit program. This letter summarizes the audit work completed and explains observations that may have been found during that work. We recognize the extra work and resources required to collect the subject documentation, especially under these extraordinary circumstances, and we truly appreciate your department’s effort and assistance.

Department Financial Activity Highlights:

Program Overview
The continuous monitoring and post audit program are designed to help assess each department’s accounting and internal control practices and compliance with City laws, regulations, and policies. The monthly monitoring reports that we send you or your staff provide regular feedback about processing in your department. They can help identify areas that are working well and highlight those that may need immediate attention for change and improvement.

Post-Audit Approach
The post audit examines the effectiveness of the design and implementation of each department’s internal control. This year, a risk-based approach was used to determine the transaction cycles selected for testing. Each department was assigned a departmental risk level for each transaction cycle based on the materiality of the department’s operations to the City’s financial reporting objectives. This was done primarily on the magnitude, volume, and complexity of the department’s transactions, and adjusted for factors such as separate reporting funds and new systems, personnel, or management. Departments with more transactions, higher dollar amounts, more complex transactions, and separate financial statements were assigned a higher risk. The assessment of inherent risk is used to design audit procedures and is not a reflection on your department’s management or performance.

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 2 of 9

Financial transactions were selected for analysis and testing on a risk basis. Documentation provided by your department as well as preliminary inquiries of key personnel were used to evaluate the adequacy of the design of the controls on selected cycles. Subsequent testing was used to evaluate the adequacy of the implementation of the controls. In all cases, the existence of citywide controls and potential compensating controls are considered in the evaluation.

Procedures
Our audit team:
• Reviewed your response to our internal control questionnaire • Reviewed your continuous monitoring results
• Reviewed your prior year’s post audit results and your response • Examined your written policies and procedures pertaining to the audit areas • Interviewed key staff members to understand the design of controls

Strengths
The following areas are highlighted as your strengths. Your cooperation in timely preparation and organization of backup documentation contributed to an efficient post audit. The Controller’s Office thanks you for your prompt response to our inquiries throughout the duration of the post audit.

1. Compliance with Year-End Close Schedule. Your department’s fiscal staff is serious about meeting the year-end schedule and promptly responds to the Controller’s request for information.

2. Budget Entry Documents. Your department has maintained performance with no findings in this area.

3. Promptly Clearing Unidentified Receipts. Your department has maintained performance with no exception in this area.

4. Journal Entry Documents. Your department has improved performance with no findings in these areas for this post audit.

5. Cash Receipt Documents. Your department has improved performance with no findings in these areas for this post audit.

Areas for Improvement
Based on the test work performed, there were certain areas that were found to need improvement or requested documentation was not received. Specific areas include:

1. Purchasing & Payables Processing. Four invoices we reviewed were paid untimely.

Additionally, the department was not able to take advantage of discounts offered for prompt payment for three invoices. The department should ensure that all invoices are paid timely, within 30 days of the invoice receipt date, and within the discount term. One invoice was inappropriately paid as a Single Payment voucher. The department should update their procedures to ensure Single Payment vouchers are used appropriately. See Appendix A, Transaction Documentation for details.

2. Grants Processing. For one grant we reviewed, the grant award period in the financial system did not match the grant obligation letter. The department should periodically review and update Grant Award Profiles to ensure the proper grant period, as well as other grant terms, are accurately reflected in the financial system. See Appendix A, Transaction Documentation for details.

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 3 of 9

Summaries of all higher risk findings are presented in Appendix A: Fieldwork Test Results on page 4, Appendix B: Grant Administration on page 8, and Appendix C: P Card on page 9.

Year-End Close
An ongoing goal of the Controller’s office is to have a well-managed process for CCSF’s Year-End Close. To accomplish this goal, departments must meet the year-end schedule, respond promptly to Controller’s request for information and required actions. This ensures that the processes for the issuance of our Annual Comprehensive Financial Report (ACFR) provides financial data integrity, accuracy, and timeliness. Departments’ year-end statistics will be included in next year’s audit letter.

The statistics will include but not be limited to number of PO closed, incomplete vouchers deleted by Controller’s office, outstanding and incomplete expense reports and cash advance denied by Controller’s office. Year-end workshops will be scheduled for debrief for areas of improvement.

Follow-up
A detailed audit matrix that reflects test work for each area of the Post Audit was provided to the Department’s Controller and discussed at the exit conference. We have not removed findings which we believe are valid although your staff may disagree. In those cases, we have communicated our reasons for retaining the finding to your staff and included any comments in the appendices.

By September 30, 2022, please provide us with a response for the observations that were reported above. If you have any questions about the audit or this report, please call Jocelyn Quintos at 415-554-6609 or Lilly Ting at 415-554-7567.

Cc: Nancy Hom, Deputy Chief Financial Officer, Public Utilities Commission Charles Perl, Deputy Chief Financial Officer, Public Utilities Commission Vivian Chen, Accounting Services Director
Sailaja Kurella, Director, Office of Contract Administration Jocelyn Quintos , Director of Accounting Operations and Supplier, Controller’s Office

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 4 of 9

Appendix A: Fieldwork Test Results
Overview
Communication, documentation, and monitoring of compliance are critical elements of every sound financial system, and policies and procedures are an essential part of establishing internal controls.

For this year’s post audit, departments were asked to respond to an internal control questionnaire and submit copies of their policies and procedures for the basic accounting cycles, submit selected transaction documentation for review and to conduct walkthroughs on accounting cycle procedures.

Internal Control
While it was not within the scope of this audit to perform an extensive internal control review, the status of procedure documentation your department has submitted on requested cycles is below.

Cycle Status
Cash Handling Received
Revenue and
Receivables
Received
Purchasing & Payables Received
Payroll Received
Grants Not Received. Department follows Controller’s Policies.

Journal Entries &
Financial Closing
Received
Budget Changes Not Received. Department follows Controller’s Policies.

Debt Received
Fixed Assets Received
Inventory Received
Trustee Accounts Received
Claims N/A

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 5 of 9

Transaction Documentation
The following documents were selected for review, document numbers in bold indicate issues found. Please find our observations below.

Purchasing &
Payables
Findings and Recommendation
01536972
01606037
01652892
01662648
01703924
01705036
01723620
01753541
01791374
01629952
01654998
01745607
01917147
01826391
01606776
01533278
01739625
01746191
01815208
01591042
01626345
01658609
01662648
01687219
01772464
01794025
01823474
01831940
01914240
01634346
01546173
01144788
01649808
01844908
01786533
01586706
01652195
01867235
01627165
01750033

Our review found six higher risk items in this category.

Four vouchers we reviewed were paid 30 days after the invoices were received. As a result of late payment, the department was not able to take advantage of discounts offered for prompt payment for two invoices.

Another voucher, although it was paid within 30 days of invoice receipt, missed the discount offered because the voucher was paid outside of the discount term. We recommend that the department ensure that invoices are paid within the City’s prompt payment rule of 30 days. We also recommend that the department pay within the discount term from the invoice receipt date to capture the prompt payment discount.

One voucher we reviewed was inappropriately processed as a Single Payment voucher. Single Payment vouchers should only be used for non-recurring and non-1099 reportable payments to one-time suppliers not set up in PeopleSoft. We recommend that the department update their procedures to ensure Single Payment vouchers are used appropriately.

Other minor findings related to these transactions were discussed with your staff.

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 6 of 9

Cash Receipts Findings and Recommendation
157040
194303
204150
176450
195771
203550
160667
176910
199639
177279

Our review did not result in any findings for the documents selected.

Journal Entry -
Revenue &
Receivable
Findings and Recommendation
0000362242
0000349191
0000424409
0000355343
0000401674

Our review did not result in any findings for the documents selected.

Journal Entry -
Year end
Findings and Recommendation
0000389176
0000395105
0000394032
0000395735
0000395426
Our review did not result in any findings for the documents selected.

Other minor findings related to these transactions were discussed with your staff.

Budget – Non-
Year End
Findings and Recommendation
0000370967
0000342473
0000371956
0000361409
0000390715
Our review did not result in any findings for the documents selected.

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 7 of 9

Budget -
Financial Closing
Findings and Recommendation
0000390221
0000384834
0000394134
0000395509
0000390858
Our review did not result in any findings for the documents selected.

Expense Findings and Recommendation
0000073335
0000075444
0000066474
0000065430
0000070417
0000067998
0000065239
0000064730
0000074300
0000072789

Our review did not result in any findings for the documents selected.

Other minor findings related to these transactions were discussed with your staff.

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 8 of 9

Appendix B: Grant Administration
The following grants were selected for review, below are our observations.

Grant Code & Title Findings and Recommendation
CTR00002322 - SRF -
OSP Digester Gas
Utilization Upgrade -
10029737

Our review did not result in any findings for the documents selected.

CTR00002490 -
Disaster #4558DR
Culvert

Our review found one higher risk item in this category.

For one grant, the grant period in PeopleSoft Grant Award Profile does not match with the grant obligation letter. We recommend that the department periodically review all grants in the financial system and work with the Controller's Office to reflect the proper grant period in the financial system. This is to ensure prompt closure of the grant and to prevent unallowed costs beyond the grant period from being charged to the grant.

N:\ACCNTING\Post Audit 2022\Audit Letters\PUC - Post Audit Letter CY2021 Final.docx Page 9 of 9

Appendix C: Procurement Card (P-Card)
Overview
The City Procurement Card (P-Card) program enables designated City employees to make authorized purchases during declared emergencies and for certain employee reimbursement items. All transactions should comply with both the citywide P-Card policy and your pre- approved departmental policy. P-Card purchases are monitored monthly and quarterly using reports available from PeopleSoft and US Bank. Departments must respond to all inquiries from the P-Card team and/or fund accountant to ensure that transactions are compliant and that any potential violations are reviewed and granted an exception.

Your department did not have any P Card process during our post audit period.

OUR MISSION: To provide our customers with high-quality, efficient and reliable water, power and sewer services in a manner that values environmental and community interests and sustains the resources entrusted to our care.

Financial Services B
525 Golden Gate Avenue, 4
San Francisco, CA

Date: August 25, 2022
To: Ben Rosenfield, Controller

Through: Dennis Herrera, General Manager

From: Nancy L. Hom, Co-acting Chief Financial Officer, Deputy CFO

Subject: Management Response to CY 2021 Post Audit Findings

Thank you for your Post Audit Report, dated 8/01 /22, detailing results of the Calendar Year 2021 Post Audit & Continuous Monitoring Program. The Controller’s Office monitoring and review program is beneficial and supports SFPUC’s enforcement of adherence and compliance to citywide policies and procedures . We appreciate your highlight of our department’s strengths, in addition to the observations noted in the report.

SFPUC staff will continue to e valuate opportunities to promote and improve compliance in the monitored areas, with specific focus upon areas with noted findings from the recent review.

SFPUC’s detailed responses and work plans are as follows:

A. Purchasing & Payables

Findings Response/Action Plan
Four vouchers we reviewed were paid 30 days
after the invoices were received. As a result of
late payment, the department was not able to
take advantage of discounts offered for prompt
payment for two invoices.

Another voucher, although it was paid within
30 days of invoice receipt, missed the discount
offered because the voucher was paid outside
of the discount term. We recommend that the
department ensure that invoices are paid
within the City’s prompt payment rule of 30
days. We also recommend that the
department pay within the discount term from
the invoice receipt date to capture the prompt
payment discount.

One voucher we reviewed was inappropriately
processed as a Single Payment voucher.

Single Payment vouchers should only be used
for non-recurring and non-1099 reportable
payments to one-time suppliers not set up in
SFPUC continues to improve
business processes and
upgrading local systems to
ensure timely payment to
suppliers and discount is
taken.

Accounting Services will
continue to provide trainings to
divisional purchasing and
accounts payable staff of City
purchasing and payment
policies and procedures.

PeopleSoft. We recommend that the
department update their procedures to ensure
Single Payment vouchers are used
appropriately.

B. Grant Administration

Findings Response/Action Plan
For one grant, the grant period in PeopleSoft
Grant Award Profile does not match with the grant
obligation letter. We recommend that the
department periodically review all grants in the
financial system and work with the Controller's
Office to reflect the proper grant period in the
financial system. This is to ensure prompt closure
of the grant and to prevent unallowed costs
beyond the grant period from being charged to the
grant.

Accounting Services will
continue to review grants in
PeopleSoft at least quarterly
to ensure proper grant period
is set up correctly in
PeopleSoft.

SFPUC staff will c onsider other observations and recommendations for improvement, as discussed or reported by the Controller’s staff in relation to the current post-audit.

We appreciate your team’s commitment to excellence and thank you for the time the team spent on completing this important audit. If you have any questions, please don’t hesitate to contact me.

cc: Jocelyn Quintos, Director of Accounting Operations and Systems, Controller’s Office Sailaja Kurella, Acting Director, Office of Contract Administration

Ronald P. Flynn, Deputy General Manager
Irella C. Blackwood, Audit Director
Vivian Aiyi Chen, Accounting Services Director

Crowe LLP
Independent Member Crowe Global
575 Market Street, Suite 3300
San Francisco, California 94105-5829
Tel 415.576.1100
Fax 415.576.1110
www.crowe.com

San Francisco Public Utilities Commission
525 Golden Gate Avenue, 13th Floor
San Francisco, CA 94102

September 7, 2022

To: Steven Ritchie, Assistant General Manager, Water Enterprise Ellen Levin, Deputy Assistant General Manager, Water
Angela Cheung, Water Supply & Treatment Manager
Annette Devincenzi, Warehouse Manager

Cc: Ronald P. Flynn, Deputy General Manager
Nancy L. Hom, Assistant General Manager, Business Services & Chief Financial Officer Charles Perl, Deputy Chief Financial Officer
Jennifer Hopkins, Information Technology Services Director

Subject: Fiscal Year End 2021-2022 Inventory Count –
Water Enterprise, Millbrae Warehouse and Sunol Yard Fuel Station

Dear Mr. Ritchie, Ms. Levin, Ms. Cheung, and Ms. Devincenzi:

The San Francisco Public Utilities Commission (SFPUC) engaged Crowe LLP (Crowe) to perform physical inventory count services for three enterprise funds including the Wastewater Enterprise, Water Enterprise, and Hetch Hetchy Enterprise.
1 This report represents results of Crowe’s physical count of the inventory at the Water Enterprise’s Millbrae warehouse and fuel inventory at the Millbrae and Sunol Yards.

A. Scope of Inventory Work

The SFPUC requested Crowe to perform an annual physical inventory of these enterprise funds to test whether significant variances exist between the physical count and the records in the SFPUC’s Maximo Inventory Tracking System (Maximo), to determine the cause of variances, and to determine amounts that need to be adjusted in Maximo. The SFPUC requested reporting of the analysis, findings, and recommendations consistent with the format requested by SFPUC’s Financial Services Bureau.

B. Warehouse Locations

The locations for the Water Enterprise physical inventory were:

Millbrae Warehouse
1000 El Camino Real
Millbrae, CA 94030

Sunol Yard
505 Paloma Way
Sunol, CA 94586

1 Note that some of the enterprises have multiple warehouse locations.

Page 2 ends

On June 30, 2022, Crowe visited the above site and conducted the inventory observation that is the subject of this report. The last full inventory count was performed by Crowe for Post FY 2020-2021 on September 7, 2021, at the above identified warehouses.

The Millbrae Warehouse has controls in place for security, including a closed warehouse where only authorized personnel are allowed access. In Table 1 below, we provide key data 2 from Crowe’s inventory
count as well as the results from the Post FY2020-2021 physical inventory count conducted by Crowe.

Table 1
San Francisco Public Utilities Commission
Water – Millbrae Warehouse and Sunol Yard Inventory Count Comparison with Prior Physical Inventory Counts
(As of June 30, 2022)

Results

As indicated in Table 1, small discrepancies between Crowe’s results from the sample count and the Maximo system were identified. The observation that follows explains the discrepancies in counts from the Maximo system. Crowe’s recommendations are also included. Management responses were provided by the Millbrae Warehouse and Sunol Yard management team on August 22, 2022.

Observation #1 – Physical Inventory Counts Differed Slightly from Maximo Counts

Condition:

We identified a total of 13 items with a difference between the physical count and the count in Maximo. The total variance in the number of items counted was 1.2 percent (13 items divided by 1,117 items). Based on the physical count, and using the average cost in Maximo, we determined that the total value of the combined Millbrae Warehouse and Sunol Yard asset inventory was $441, or 0.08 percent, above the total asset value of $581,378 in Maximo.

We found 6 positive variances (where the physical count exceeded the Maximo inventory count) which totaled $948. We found 7 negative variances (where the physical count fell below the Maximo inventory count) which totaled ($507). In Table 2 below we provide a summary of variances which shows that most of the variance dollar amount is fuel-related.

2 Crowe did not perform any procedures related to the assigned value in the Maximo system.

It ems Value It ems Value
Count with no Discrepancies 1,104 581,819 $ 1,104$ 544,435 $ Count Discrepancies 13 (441) 17 460 Total Inventory Counted 1,117 581,378 $ 1,121 544,895 $ Percentage of Inventory Sampled 100% 100% 100% 100%
Percentage of Discrepancies 1.2% -0.08% 1.5% 0.1%
Total Inventory per Maximo 1,117 581,378 $ 1,121 544,895 $ Description
FY 2021-2022 FY 2020-2021

Page 3 ends

Table 2
San Francisco Public Utilities Commission
Water – Millbrae Warehouse and Sunol Yard Inventory Count Summary of Variances

Finally, as shown in Table 3 below, based on the fuel dip procedures Crowe performed for this physical count, we found the actual quantities of fuel contained in the four tanks varied from those in the Maximo system. We believe that the quantities we observed during our physical count using a manual dip process and a conversion formula were reasonably accurate as they closely matched quantities registered by an electronic measurement system contained in the fuel tanks that the Water Enterprise generated that day (referred to as the System Status Report).

Table 3
San Francisco Public Utilities Commission
Water – Millbrae Warehouse and Sunol Yard Fuel Inventory Summary of Fuel Gallon and Cost Variances
Criteria:

Chapter 6 of the City and County of San Francisco Office of the Controller’s Accounting Policies and Procedures identifies that Departments shall conduct a physical inventory at least annually and ensure that the inventory management system records accurately reflect inventory on hand. Additionally, Section 6.1 (Policy Guidelines) indicates that inventory system records must be promptly adjusted for any discrepancies identified in the physical counts, Departments must periodically review the inventory records for reasonableness and appropriateness of any changes to inventory unit cost, inventory valuation, and unit of measure, and Departments must periodically review the inventory for obsolescence and reasonableness of inventory classification. The Policy Guidelines also indicate that Departments should investigate significant discrepancies between the physical count, the inventory system records, and the accounting records; document the reasons for discrepancy and take corrective actions to reduce future discrepancies.

Cause:

Regarding the fuel variances observed, we determined that the Water Enterprise uses the automated fuel tracking information system, E.J. Ward, to capture fuel usage and the SFPUC Information Technology Services Bureau has developed an interface designed to periodically upload the E.J. Ward fuel usage data to the Maximo system. However, based on problems with the system interface between the E.J. Ward Description Item Count Amount
Positive 2 739$
Neg
ative 2 (129)$
Total Fuel Variances 4 610$
Positive 4 209$
Negative 5 (378)$
Total Other Variances 9 (169)$
Total Variances 13 441$
Fuel Variances
Other Variances
Item
Num be r Description Item Code Issue Unit
CURBAL

6.30.22 Crowe
Count

6.3 0.22 Variance
Average
Cost Variance
1 GASOLINE, TRUCK, DIESEL FUEL, MILLBRAE 091 -40-3146 GAL 2,531 2,518 -13 5.6871$ (76)$ 2 GASOLINE, DIESEL, SUNOL 091-40-3147 GAL 1,597 1,609 12 6.3570$ 79$ 3 GASOLINE, AUTO, UNLEADED, MILLBRAE 091-30-3255 GAL 2,782 2,897 115 5.7508 $ 660$ 4 GASOLINE, AUTO, UNLEADED, SUNOL 091-30-3260 GAL 846 837 -9 5.7538$ (53)$ Total 610$

Page 4 ends

system and the Maximo system, actual Water Enterprise fuel usage data was not consistently and accurately uploaded into Maximo causing the variances.

Effect:

The combined Millbrae and Sunol inventory value in the Maximo system is overstated by $2,393.

Regarding the fuel usage discrepancies, absent periodic monitoring of the fuel levels, the Millbrae Warehouse and Sunol Yard will have difficultly reconciling future inventories and identifying variances that are the result of missing fuel (e.g., theft), delivery errors, data entry errors, tank leaks, or shrinkage.

Recommendation:

The Millbrae Warehouse staff should update the asset inventory counts in Maximo after management approval. The SFPUC’s Financial Services Bureau should adjust the General Ledger to reflect the actual cost of inventory on hand.

Management Response:

The audit recommendation is already implemented at the SFPUC. Millbrae Warehouse’s inventory items are updated by staff on a regular basis. SFPUC’s Financial Services Bureau staff audits the inventory adjustments periodically. Any variances noted are responded in writing by warehouse staff.

Other Matters

Crowe did not perform any procedures to verify the Management Responses and Crowe does not provide any assurance regarding the accuracy or completeness of the responses. Any and all amounts referred to in this letter are preliminary and could change should additional procedures be performed. Additionally, issues that are not mentioned here could develop subsequent to the date of this letter that may have impacted Crowe’s analysis or that Crowe may cite should additional procedures be performed.

The inventory services did not contemplate obtaining the understanding of internal controls or assessing control risk, tests of accounting records and responses to inquiries by obtaining corroborating evidenti al matter, and certain other procedures ordinarily performed during an audit or examination. Thus, this engagement was not intended to provide assurance that we would become aware of significant matters that would be disclosed in an audit or examination.

As part of this inventory, the SFPUC agreed to be responsible to: make all management decisions and perform all management functions; designate an individual who possesses suitable skill, knowledge, and/or experience, preferably within senior management to oversee our services; evaluate the adequacy and results of the services performed; accept responsibility for the results of the services; and establish and maintain internal controls, including monitoring ongoing activities. The SFPUC has ultimate authority for making changes to inventory reported in its Maximo system and on its financial statements .

Crowe‘s fees are not dependent upon the outcome of this report.

We appreciate the contribution of SFPUC management and your input and direction on this project. We also thank Millbrae Warehouse and Sunol Yard management and staff for providing access to the inventory and for timely responses to our requests.

Crowe LLP

Crowe LLP
Independent Member Crowe Global
575 Market Street, Suite 3300
San Francisco, California 94105-5829
Tel 415.576.1100
Fax 415.576.1110
www.crowe.com

San Francisco Public Utilities Commission
525 Golden Gate Avenue, 13th Floor
San Francisco, CA 94102

September 7, 2022

To: Barbara Hale, Assistant General Manager, Power
Ramon Abueg, Deputy Manager, Power Operations
Richard Stephens, Utility Services Manager
David Carter, Materials Coordinator
Bart Murphy, Materials Coordinator

Subject: Fiscal Year End 2021-2022
Inventory Count – Hetch Hetchy Enterprise, Hetchy Power In-City Warehouse

Dear Ms. Hale, Mr. Abueg, Mr. Stephens, Mr. Carter, and Mr. Murphy:

The San Francisco Public Utilities Commission (SFPUC) engaged Crowe LLP (Crowe) to perform physical inventory count services for three enterprise funds including the Wastewater Enterprise, Water Enterprise, and Hetch Hetchy Enterprise.
1 This report represents results of Crowe’s physical count of the Hetch Hetchy Enterprise - Hetchy Power In-City inventory.

A. Scope of Inventory Work

The SFPUC requested Crowe to perform an annual physical inventory of these enterprise funds to test whether significant variances exist between the physical count and the records in the SFPUC’s Maximo Inventory Tracking System (Maximo), to determine the cause of variances, and to determine amounts that need to be adjusted in Maximo. The SFPUC requested reporting of the analysis, findings and recommendations consistent with the format requested by SFPUC’s Financial Services Bureau.

B. Warehouse Location(s)

The location for the Hetchy Power In-City physical inventory was:

Hetchy Power In-City Warehouse
Pier 23
San Francisco, California 94111
On June 21, 2022, Crowe visited the above site and conducted the inventory observation that is the subject of this report. The last full inventory count was performed by Crowe for Post FY 2020-2021 on August 24, 2021, at the above identified Hetchy Power warehouse.

The warehouse has controls in place for security, including a gated warehouse entry where Hetchy Power allows only authorized personnel access. In Table 1 below, we provide key data 2 from Crowe’s FY2021-

1 Note that some of the enterprises have multiple warehouse locations. 2 Crowe did not perform any procedures related to the assigned value in the Maximo system.

Page 2 ends

2022 inventory count as well as the results from the Post FY 2020-2021 physical inventory count conducted on August 24, 2021.

Table 1
San Francisco Public Utilities Commission
Hetchy Power In-City Warehouse Inventory Count
Comparison with Prior Physical Inventory Counts
(As of June 21, 2022)

Results

As indicated in Table 1, discrepancies between Crowe’s results from the full count and the Maximo system were identified. The two findings that follow explain the discrepancies in counts and difficulty in locating inventory items. Crowe’s recommendations related to those findings are also included. Management responses to each finding were provided by the Hetchy Power management team on August 23, 2022.

Finding #1 – Physical Inventory Counts Differed from Maximo Counts

Condition:

We identified a total of 570 items with a difference between the physical count and the items reported in Maximo. The total variance in the number of items counted was 42 percent (570 items divided by 1,364 items). This represents similar discrepancy levels to those observed for fiscal year 2020-2021. We provided Hetchy Power In-City warehouse personnel with an opportunity to review/confirm the count figures and they agreed with the variances. This represents a recurring finding. Based on the physical count of the discrepancies, and using the average cost in Maximo, we determined that the total value of the warehouse inventory variance should be $80,244, or 5.6 percent, below the current total asset value of $1,439,946 in Maximo.

We found 194 positive variances (where the physical count exceeded the Maximo inventory count) which totaled $65,549. We found 376 negative variances (where the physical count fell below the Maximo inventory count) which totaled ($145,773). In Table 2 below we list 20 items with either positive or negative variances above $2,000. The combination of these 20 items represented ($44,002), or about half of the ($80,224) variance.

It ems Value It ems Value
Count with no Discrepancies 794 1,359,722$ 829 1,292,773$ Count Discrepancies 570 80,224 534 139,041 Total Inventory Counted 1,364 1,439,946$ 1,363 1,431,814$ Percentage of Inventory Sampled 100% 100% 100% 100%
Percentage of Discrepancies 42% 5.6% 39% 9.7%
Total Inventory per Maximo 1,364 1,439,946$ 1,363 1,431,814$ Description
FY 2021-2022 (Crowe) FY 2020-2021 (Crowe)

Page 3 ends

Table 2
San Francisco Public Utilities Commission
Hetchy Power In-City Warehouse Inventory Count
List of Items with Variance Above or Below $2,000

Criteria:

Chapter 6 of the City and County of San Francisco Office of the Controller’s Accounting Policies and Procedures identifies that Departments shall conduct a physical inventory at least annually and ensure that the inventory management system records accurately reflect inventory on hand. Additionally, Section 6.1 (Policy Guidelines) indicates that inventory system records must be promptly adjusted for any discrepancies identified in the physical counts, Departments must periodically review the inventory records for reasonableness and appropriateness of any changes to inventory unit cost, inventory valuation, and unit of measure, and Departments must periodically review the inventory for obsolescence and reasonableness of inventory classification. The Policy Guidelines also indicate that Departments should investigate significant discrepancies between the physical count, the inventory system records, and the accounting records; document the reasons for discrepancy; and take corrective actions to reduce future discrepancies.

Cause:

Hetchy Power In-City warehouse staff identified several reasons for the differences in this physical count compared to the count in Maximo, including:

• System posting errors, by the individual entering the data, where the quantity entered was incorrect. • Some storeroom areas (e.g., Streetlights 1 and 2) were incorrectly included in Maximo when the items were obsolete and should be removed from Maximo.
• Some inventory items were used on a project/job, and updates for these items were not reflected in Maximo.

Item
Num be r Description Item N umber
Maximo
Count
Crowe
Count Variance
Average
Cost Value Difference
1 BUSS FUSEHOLDER, HEX-AA PE-10-0067 62 152 90 67.56 6,080$ 2 POLE, 16FT, ROUND STEEL BOTTLENECK POLE, WITH GFI, LUMEC, BLACK FINISH PE-10-1918 10 12 2 1,940.25 3,881$ 3 Bird Flight Diverter PGE 561455 PE-10-1756 50 105 55 62.08 3,415$ 4 STEEL POLE, 28.6FT, DOUBLE ARM PE-10-0485 14 16 2 1,550.81 3,102$ 5 LU100SBY XL ECO 100W LAMP PE-10-0621 13 66 53 52.82 2,800$ 6 60 AMP FUSES PE-10-0083 146 359 213 11.21 2,388$ 7 POLES, WOOD 50" PE-10-0771 14 12 (2) 1,059.30 (2,119)$ 8 25Kv Plugged Elbow 200A LB ELM 262LR-C-CS2078 PGE 301585 - REPLACE INSULATING CAP PE-10-1152 49 32 (17) 133.12 (2,263)$ 9 Cable Support Kit PE-10-1166 126 86 (40) 59.88 (2,395)$ 10 N16 STREET LIGHTING CHRISTY BOX LID OLD PE-10-0351 1,204 1,105 (99) 24.50 (2,426)$ 11 LED Luminaire , Cobra Head, 108W, ROAD FOCUS, PHILLIP, LUMEC RFM- 108W48LED3K-G2--R3M-UNV-DMG-[API-226]-AP12011-RCD7-GY3 PE-10-1548 30 28 (2) 1,222.86 (2,446)$ 12 Transformer, 50 KVA, Pole Bolt 12KV/120/240 PE-10-1091 2 1 (1) 2,595.32 (2,595)$ 13 1000MCMCOP2H LUG PG&E #303461 PE-5935-303461 100 - (100) 28.55 (2,855)$ 14 4 FOOT METAL ARM NEW PE-10-0458 22 - (22) 140.00 (3,080)$ 15 GVU6N GLASS POST TOP FOR HOLOPHANE FIXTURES PE-10-0661 9 1 (8) 400.00 (3,200)$ 16 TX AL Plate 'SFPUC' machined on part PE-10-1872 208 108 (100) 35.26 (3,526)$ 17 MVR 175/V/BU/PA LAMP PE-10-0639 111 89 (22) 250.00 (5,500)$ 18 1100 MCM Straight Splice PGE 301434 PE-10-1925 15 (15) 556.18 (8,343)$ 19 POLE, 16FT, ROUND STEEL BOTTLENECK POLE, LUMEC, BLACK FINISH PE-10-1916 15 9 (6) 1,778.04 (10,668)$ 20 26 FT SINGLE POLE PE-10-0483 19 - (19) 750.00 (14,250)$ 21 Sum of Cost of Variance of 559 Other Items Whose Individual Variances Each Have an Absolute Value less Than $2,000 (36,222)$
Total (80,224)$

Page 4 ends

Effect:

The Hetchy Power In-City inventory value in the Maximo system is overstated by $80,224.

Recommendation:

Hetchy Power In-City warehouse staff should update the asset inventory counts in Maximo after management approval. The SFPUC Financial Reporting and Analysis division should adjust the General Ledger to reflect the actual cost of inventory on hand. Hetchy Power In-City should investigate the significant discrepancies between the physical counts noted in Tables 1 and 2, the Maximo system records, and the accounting records; document the reasons for discrepancies; and implement corrective actions to reduce future discrepancies. Finally, inventory warehouse staff should conduct regular physical inventory cycle counts and compare results with those in Maximo to ensure Maximo data is up- to-date and accurate.

Management Response:

Management concurs with Finding #1 and is working on correcting physical counts through cycle counts, the use of the Maximo Mobile Inventory app, implemented August 2022, and tighten inventory pick and use procedures to obtain accurate and real time information to reflect into Maximo. The field crews must implement better record keeping of materials used on a job. We note that although the number of discrepancies is still high, the financial discrepancy is lower from previous year – 5.6% this year.

Finding #2 – Some Inventory Was Difficult to Locate

Condition:

While performing the physical count, we had difficulty locating some of the inventory items identified in the Maximo system. Some of the items were located behind other items and recessed which limited access and other items were not clearly marked with their item numbers.

Criteria:

Chapter 6 of the City and County of San Francisco Office of the Controller’s Accounting Policies and Procedures identifies that Department’s should prepare and organize storage for the inventory count.

Cause:

Hetchy Power In-City moved relatively recently to Pier 23 and has been transitioning its entire inventory to this new storage facility. Additionally, several items that were recently determined to be obsolete were commingled with the current inventory items in Maximo (e.g., significant quantities of light bulbs).

Effect:

A third-party cannot easily perform the full inventory counts without the assistance of storekeeper staff.

There also is the potential for some inventory items not to be identified.

Recommendation:

For the next annual inventory, Hetchy Power In-City should clearly label and organize all its inventory, particularly the items outside of the storage shelves, so that a third party can easily identify and count the inventory.

Page 5 ends

Management Response:

Management concurs with Finding #2. The location information in Maximo needs to be cleaned up to reflect what is on the floor. Conducting more cycle counts will assist in verification of locations and result in accurate location. Due to the variations of materials, any 3 rd party will have trouble identifying materials
unless staff is present. Also, keeping necessary quantities can lead to overflow boxes or bins that we line up with initial bins. A new ID process and number system is being developed to create a uniform ID and Labeling process to improve ID and improve inventory levels.

Other Matters

The inventory services did not contemplate obtaining the understanding of internal controls or assessing control risk, tests of accounting records and responses to inquiries by obtaining corroborating evidential matter, and certain other procedures ordinarily performed during an audit or examination. Thus, this engagement was not intended to provide assurance that we would become aware of significant matters that would be disclosed in an audit or examination.

As part of this inventory, the SFPUC agreed to be responsible to make all management decisions and perform all management functions; designate an individual who possesses suitable skill, knowledge, and/or experience, preferably within senior management to oversee our services; evaluate the adequacy and results of the services performed; accept responsibility for the results of the services; and establish and maintain internal controls, including monitoring ongoing activities. The SFPUC has ultimate authority for making changes to inventory reported in its Maximo system and on its financial statements.

Crowe‘s fees are not dependent upon the outcome of this report.

We appreciate the contribution of SFPUC management and your input and direction on this project. We also thank Hetchy Power In-City management and staff for assisting providing access to the inventory and for timely responses to our requests.

Crowe LLP

Crowe LLP
Independent Member Crowe Global
575 Market Street, Suite 3300
San Francisco, California 94105-5829
Tel 415.576.1100
Fax 415.576.1110
www.crowe.com

San Francisco Public Utilities Commission
525 Golden Gate Avenue, 13th Floor
San Francisco, CA 94102

September 7, 2022

To: Steve Ritchie, Assistant General Manager, Water Enterprise Margaret Hannaford, Division Manager, Hetch Hetchy Water/Moccasin Cheryl Sperry, Administrative Services Manager
Ray Emerald, Assistant Materials Coordinator

Subject: Fiscal Year End 2021-2022
Inventory Count – Hetch Hetchy Enterprise, Moccasin Warehouse

Dear Mr. Ritchie, Ms. Hannaford, Ms. Sperry, and Mr. Emerald:

The San Francisco Public Utilities Commission (SFPUC) engaged Crowe LLP (Crowe) to perform physical inventory count services for three enterprise funds including the Wastewater Enterprise, Water Enterprise, and Hetch Hetchy Enterprise.
1 This report represents results of Crowe’s physical count of the Hetch Hetchy Enterprise – Moccasin Warehouse inventory.

A. Scope of Inventory Work

The SFPUC requested Crowe to perform an annual physical inventory of these enterprises to test whether significant variances exist between the physical count and the records in the SFPUC’s Maximo Inventory Tracking System (Maximo), to determine the cause of variances, and to determine amounts that need to be adjusted in Maximo. The SFPUC requested reporting of the analysis, findings, and recommendations consistent with the format requested by SFPUC’s Financial Services Bureau.

B. Warehouse Location(s)

The location for the Hetch Hetchy Enterprise physical inventory was:

Moccasin Warehouse
10390 Moccasin Switchback Road
Moccasin, CA 95347

On June 28, 2022, Crowe visited the above site and conducted the inventory observation that is the subject of this report. The last full inventory count was performed by Crowe for Post FY 2020-2021 on August 12, 2021 at the above identified Moccasin warehouse.

1 Note that some of the enterprises have multiple warehouse locations.

Page 2 ends

The Moccasin Warehouse has controls in place for security, including a closed warehouse where only the Moccasin Warehouse allows only authorized personnel access. In Table 1 below, we provide key data2 from Crowe’s inventory count as well as the results from the Post FY2020-2021 physical inventory count conducted by Crowe.

Table 1
San Francisco Public Utilities Commission
Hetch Hetchy Moccasin Warehouse Inventory Count
Comparison with Prior Physical Inventory Counts
(As of June 28, 2022)

Results
As indicated in Table 1, minor discrepancies between Crowe’s results from the count and the Maximo system were identified. The following observation explains the discrepancies in counts. Crowe’s recommendation related to this observation is also included. Management responses were provided by the Moccasin management team on August 17, 2022.

Observation #1 – Physical Inventory Counts Differed from Maximo Report Counts

Condition:

We identified a total of 20 items with a difference between the physical count and the items reported in Maximo. The total variance in the number of items counted was 0.8 percent (20 items divided by 2,360 items). Based on the physical count, and using the average cost in Maximo, we determined that the total value of the Moccasin Warehouse asset inventory variance was $85, or 0.02 percent, above the total asset value of $422,044 in Maximo.

We found 10 positive variances (where the physical count exceeded the Maximo inventory count) which totaled $660. We found 10 negative variances (where the physical count fell below the Maximo inventory count) which totaled ($575). In Table 2 below we provide a summary of variances.

2 Crowe did not perform any procedures related to the assigned value in the Maximo system.

It ems Value It ems Va lue
Count with no Discrepancies 2,350 $422,129 2,308 430,262 $ Count Discrepancies 20 (85) 45 496 Total Inventory Counted 2,370 422,044 $ 2,353 430,758 $ Percentage of Inventory Sampled 100% 100% 100% 100%
Per
centage of Discrepancies 0.8% -0.02% 1.9% 0.1%
Total Inventory per Maximo 2,370 422,044 $ 2,353 430,758 $ FY 2020-2021FY 2021-2022
Description

Page 3 ends

Table 2
San Francisco Public Utilities Commission
Hetch Hetchy Moccasin Warehouse Inventory Count
Summary of Variances

As shown in Table 3 below, based on the fuel dip procedures Crowe performed for this physical count, we found the actual quantities of fuel contained in the eleven tanks varied slightly, or by $27, from those in the Maximo system. Table 4 provides the other $58 in non-fuel count variances.

Table 3
San Francisco Public Utilities Commission
Hetch Hetchy Moccasin Warehouse Inventory Count
Summary of Fuel Count Variances

Description Item Count Am ount
Positive 6 $317
Negative 5 ($290)
Total Fuel Variances 11 $27
Positive 4 $343
Negative 5 ($285)
Total Other Variances 9 $58
Total Variances 20 $85
Other Variances
Fuel Variances
Item
Number Description Item Code
Maximo
Count
Crowe
Count Variance
Average
Cost
Cost of
Variance
1 GASOLINE, UNLEADED,CHERRY VLY.,#11. 091-30-3185 248 249 1.0 $5.18 $5 2 FUEL,RENEWABLE DIESEL,EARLY INTAKE (CHANGED FROM #2
DIESEL 4/12/16)
091-40-3149 130 136 6.0 $4.11 $25
3 GASOLINE, UNLEADED, MOCCASIN,#01.-2 091-30-3270 2381 2407 26.0 $5.47 $142 4 FUEL, RENEWABLE DIESEL, LOCATION: MOCC,#03 091-40-3148 856 854 (2.0) $6.01 ($12) 5 GASOLINE, MOCCASIN , # 4-5 tanks 091-30-3271 1844 1860 16.0 $5.69 $91 6 FUEL, RENEWABLE DIESEL MOCCASIN - LOCATION - #6 091-40-3171 1795 1773 (21.5) $4.11 ($88) 7 GASOLINE, UNLEADED, O'S,#04 091-30-3180 215 215 (0.4) $3.75 ($2) 8 GASOLINE,UNLEAD,SOUTH FORK,#09. 091-30-3240 205 208 3.4 $4.51 $15 9 FUEL, RENEWABLE DIESEL,SOUTH FORK,#10 (CHANGED FROM
#2 DIESEL 4/12/16)
091-40-3150 302 280 (21.5) $4.46 ($96)
10 GASOLINE, UNLEADED, OAKDALE,#7. 091-30-3195 711 692 (19.0) $4.85 ($92) 11 FUEL, RENEWABLE DIESEL OAKDALE (renamed from BIO DIESEL 3/3/16)
091-40-3170 897 905 8.0 $4.81 $39
Total $27

Page 4 ends

Table 4
San Francisco Public Utilities Commission
Hetch Hetchy Moccasin Warehouse Inventory Count
Summary of Non-Fuel Count Variances

Criteria:

Cause:

Moccasin Warehouse staff indicated two reasons for the differences in this physical count compared to the count in Maximo, including:

• Fuel system communication problems and rounding differences (11 items) • Data entry errors (9 items).

Regarding the minor fuel volumes variance valued at $27, we determined Moccasin Warehouse uses the automated fuel tracking information system, E.J. Ward, to record fuel usage. The SFPUC information technology department developed an interface designed to periodically upload the fuel usage data to the Maximo system. This system is reliable, however, there may be delays in updating data because of poor network communication systems. The reason for the small variances from the quantities we observed during our physical count and those current in Maximo, was based on rounding errors as a result of using a manual dip process and a conversion formula to determine fuel quantities.

Effect:

The Moccasin Warehouse inventory value in the Maximo system is understated by $85, which is considered de minimis.

Recommendation:

The Moccasin warehouse staff should update the asset inventory counts in Maximo after management approval. The SFPUC Financial Reporting and Analysis division should adjust the General Ledger to reflect the actual cost of inventory on hand.

Item
Number Description Item Code
Maximo
Count
Crowe
Count Variance
Average
Cost
Cost of
Variance
1 1" RIGID 2 HOLE STRAP 059-76-1623 23 12 (11.0) $0.43 ($5) 2 8-32 NUT, MACH. SCREW, 10/PKG. (1WA91) 053-01-832N 200 100 (100.0) $0.03 ($3) 3 BOLT, CAP, NC., 5/16-18 x 2, 50/PKG., (22TA88) 053-01-0022 50 49 (1.0) $0.09 ($0) 4 WASHER, LOCK, 1/2" SPLIT, 25/PKG. (22UH82) 053-01-2119 106 131 25.0 $0.09 $2 5 NAPA# 9883 AIR FILTER (FORD OEM# FA-1883) 023-82-9883 6 7 1.0 $19.40 $19 6 CONNECTOR, WR379, ALUM. COMPRESSION 059-77-WR379 200 0 (200.0) $1.38 ($277) 7 INSULATOR PIN, LOW VOLTAGE, 5/8" PIN K7791Z (2.4 KV) 059-77-5923 0 11 11.0 $13.78 $152 8 TOWEL, BATH, COTTON, WHITE 072-10-6800 3 4 1.0 $143.86 $144 9 SAFETY VEST, CLASS 3, LARGE, LIME, ZIP FRONT 084-15-1512 15 16 1.0 $25.43 $26 Total $58

Page 5 ends

Management Response:

Hetch Hetchy Water concurs with the recommendation and we have made the corresponding adjustments.

Other Matters

The inventory services did not contemplate obtaining the understanding of internal controls or assessing control risk, tests of accounting records and responses to inquiries by obtaining corroborating evidential matter, and certain other procedures ordinarily performed during an audit or examination. Thus, this engagement was not intended to provide assurance that we would become aware of significant matters that would be disclosed in an audit or examination.

Crowe‘s fees are not dependent upon the outcome of this report.

We appreciate the contribution of SFPUC management and your input and direction on this project. We also thank Moccasin Warehouse management and staff for providing access to the inventory and for timely responses to our requests.

Crowe LLP

Crowe LLP
Independent Member Crowe Global
575 Market Street, Suite 3300
San Francisco, California 94105-5829
Tel 415.576.1100
Fax 415.576.1110
www.crowe.com

San Francisco Public Utilities Commission
525 Golden Gate Ave
San Francisco, CA 94102

September 7, 2022

To: Greg Norby, Assistant General Manager, Wastewater Enterprise Joel Prather, Maintenance Manager
Christian Losno, Materials Coordinator
Jorge Gonzalez, Assistant Supervisor of Stores & Equipment

Subject: Fiscal Year End 2021-2022
Inventory Count - Wastewater Enterprise Warehouse

Dear Mr. Norby, Prather, Losno, and Gonzalez:

The San Francisco Public Utilities Commission (SFPUC) engaged Crowe LLP (Crowe) to perform physical inventory count services for three enterprises including the Wastewater Enterprise, Water Enterprise, and Hetch Hetchy Enterprise.
1 This report represents results of Crowe’s physical count of the Wastewater Enterprise inventory (Inventory).

A. Scope of Inventory Work

The SFPUC requested Crowe to perform an annual physical inventory of these enterprises to test whether significant variances exist between the physical count and the records in the SFPUC’s Maximo Inventory Tracking System (Maximo), to determine the cause of variances, and to determine amounts that need to be adjusted in Maximo. The SFPUC requested reporting of the analysis findings, and recommendations consistent with the format requested by SFPUC’s Financial Services Bureau.

B. Warehouse Location(s)

The location for the Wastewater Enterprise physical inventory was:
2725 Oakdale Avenue
San Francisco, California 94124

On June 23, 2022, Crowe visited the above site and conducted the inventory observation that is the subject of this report. The last full inventory count was performed by Crowe for Post FY 2020-2021 on July 23, 2021 at the above identified Wastewater warehouse.

The Wastewater Enterprise has controls in place for security, including a closed warehouse where the Wastewater Enterprise allows only authorized personnel access. Additionally, most of the small to mid-

1 Note that some of the enterprises have multiple warehouse locations.

Page 2 ends

sized inventory items are maintained in secure cabinets with pull out drawers under lock and key. In Table 1 below, we provide key data2 from Crowe’s FY2021-2022 inventory count as well as well as the results from the last Post FY2020-2021 physical inventory count conducted by Crowe.

Table 1
San Francisco Public Utilities Commission
Wastewater Inventory Count
Comparison with Prior Physical Inventory Counts
(As of June 23, 2022)

Results
As indicated in Table 1, discrepancies between Crowe’s results from the sample count and the Maximo system were identified. The following finding explain the discrepancies in counts. Crowe’s recommendations related to those findings are also included. Management responses to each finding were provided by the Wastewater management team on August 31, 2022.

Finding #1 – Physical Inventory Counts Differed from Maximo Report Counts

Condition:

We identified a total of 109 items with a difference between the physical count and the count in Maximo.

The total variance in the number of items counted was 2.8 percent (109 divided by 3,871 items). Based on the physical count, and using the average cost in Maximo, we determined that the total value of the Wastewater Enterprise inventory count discrepancies to be $22,955, or 0.8 percent, below the current total asset value of $2,957,510 in Maximo.

We found 19 positive variances (where the physical count exceeded the Maximo inventory count) which totaled $10,130. We found 90 negative variances (where the physical count fell below the Maximo inventory count) which totaled ($33,085). In Table 2 below we list 7 items with either positive or negative variances above $1,000. The combination of these 7 items represented ($11,938), or about half of the ($22,955) variance.

2 Crowe did not perform any procedures related to the assigned value in the Maximo system.

It ems Value It ems Value
Co
unt with no Discrepancies 3,762 2,934,555$ 3,820 2,644,448$ Count Discrepancies 109 22,955 93 33,513 Total Inventory Counted 3,871 2,957,510$ 3,913 2,677,961$ Percentage of Inventory Sampled 100% 100% 100% 100%
Percentage of Discrepancies 2.8% 0.8% 2.4% 1.3%
Total Inventory per Maximo 3,871 2,957,510$ 3,913 2,677,961$ Description
FY 2021-2022 FY 2020-2021

Page 3 ends

Table 2
San Francisco Public Utilities Commission
Wastewater Inventory Count
List of Items with Variance Above or Below $1,000

Criteria:

Chapter 6 of the City and County of San Francisco Office of the Controller’s Accounting Policies and Procedures identifies that Department’s shall conduct a physical inventory at least annually and ensure that the inventory management system records accurately reflect inventory on hand. Additionally, Section

6.1 (Policy Guidelines) indicates that inventory system records must be promptly adjusted for any discrepancies identified in the physical counts, Departments must periodically review the inventory records for reasonableness and appropriateness of any changes to inventory unit cost, inventory valuation, and unit of measure, and Departments must periodically review the inventory for obsolescence and reasonableness of inventory classification. The Policy Guidelines also indicate that Department’s should investigate significant discrepancies between the physical count, the inventory system records, and the accounting records; document the reasons for discrepancy and take corrective actions to reduce future discrepancies.

Cause:

Variations were caused by several factors including an item that was misplaced in a bin, returned items not updated in the system, and minor miscounts during periodic cycle counts.

Effect:

The Wastewater Enterprise inventory value in the Maximo system is overstated by $22,955.

Recommendation:

The Wastewater warehouse staff should update the asset inventory counts in Maximo system after management approval. The SFPUC Financial Reporting and Analysis division should adjust the General Ledger to reflect the actual cost of inventory on hand. The Wastewater Enterprise should investigate the significant discrepancies between the physical counts noted in Tables 1 and 2, the Maximo system records, and the accounting records; document the reasons for discrepancies; and implement corrective actions to reduce future discrepancies.

It em
Numbe r Item Description It em Code
Maximo
Count
Crowe
Count Variance
Average
Cost
Cost of
Variance
1 WEAR, RING; "ALLIS-CHALMERS" PN: 08-218-196-
022, GRI NG-0320 1 2 1 8,809.2$ 8,809$ 2 PUMP, LUBE, TYPE 18, ROPER, 18AM21
MPUMP-0002 2 - (2) 502.2$ (1,004)$ 3 ELECTRICAL DEGREASER, AEROSOL, 17 OZ ACLEA-0110 72 10 (62) 18.7$ (1,158)$ 4 V-BELT, 8V1600 HUMBLT CENT ( ISSUE AS 5 EA) ABLTE-1600 20 5 (15) 174.7$ (2,620)$ 5 BEARING KIT, "MOYNO" PN:4220107000 (KPG291) MKITS-7623 4 2 (2) 1,888.6$ (3,777)$ 6 BOARD, INTERFACE, 600HP, ROBICON P/#460E04.00T
EROBI-BD06 1 - (1) 3,853.9$ (3,854)$ 7 WEAR, RING; "ALLIS-CHALMERS" PN: 08-218-196-
021, GRI NG-0319 2 1 (1) 8,333.7$ (8,334)$ 8 Sum of Cost of Variance of 102 Other Items Whose
Individual Variances Each Have an Absolute Value less
Than $1,000
(11,017)$
Tot
al (22,955)$

Page 4 ends

Management Response:

We concur that our cycle counts need to improve, in particular we should be focusing more on our high dollar inventory during these counts. We are currently revising the standard operating procedures for the cycle count and inventory issue/return processes.

However, after we had a chance to review the spreadsheet listing the discrepancy findings provided by Crowe, of the 109 identified items, our staff were able to confirm that only 79 items actually had discrepancies. The 30 items that we were able to identify had total calculated value of $9,435 and thus should result in a total revised overstatement of $13,520.

For the next audit, WWE requests that our staff be able to accompany the auditors during their evaluation of our inventory so that these issues can be confirmed as they are identified.

Another factor that contributes to these issues is staffing. Our WWE warehouse staff positions currently consist of (1) 1938 Stores and Equipment Assistant Supervisor, (1) 1936 Senior Storekeeper, and (3) 1934 Storekeepers. Of those positions, we currently only have two 1934 Storekeeper positions filled. We do have a recruitment underway with SFPUC-HRS for the 1938 Stores and Equipment Assistant Supervisor, and we are in the process of setting up interviews for the third 1934 Storekeeper position.

Crowe Response:

Crowe will work with WWE to coordinate the efforts and review exceptions during the next inventory count.

As Crowe did not perform any procedures on the additional WWE inventory count data provided in the table above within the management response, Crowe cannot comment on the validity of that inventory count data.

Status of Findings from Prior Physical Inventory Count

As shown in Table 3 below, two of the findings from Crowe’s prior inventory report dated November 10, 2021, titled “Some Items Identified in the Physical Count Were Not Identified in the Maximo System” and “Some Inventory Items Appeared Obsolete” were remediated during the last fiscal year.

Table 3
San Francisco Public Utilities Commission
Wastewater Inventory Count
FY 2020-2021 Findings Update

It ems Value It ems Value
Count with no Discrepancies 3,762 2,934,555$ 3,792 2,943,990$ Count Discrepancies 109 22,955 79 13,520 Total Inventory Counted 3,871 2,957,510$ 3,871 2,957,510$ Percentage of Inventory Sampled 100% 100% 100% 100%
Percentage of Discrepancies 2.8% 0.8% 2.0% 0.5%
Total Inventory per Maximo 3,871 2,957,510$ 3,871 2,957,510$ Description
FY 2021-2022 FY 2021-2022

CROWE SFPUC
2021 Findings Remediated
Finding #1 – Physical Inventory Counts Differed from Maximo Counts Finding #2 – Some Items Identified in the Physical Count Were Not Identified in the Maximo System √ Fi
nding #3 – Some Inventory Items Appeared Obsolete √

Page 5 ends

Other Matters

This Inventory did not constitute an audit, examination, review, or compilation of the historical and prospective financial information conducted in accordance with generally accepted auditing standards or with other standards established by the AICPA. Accordingly, Crowe is unable to express an opinion or any other form of assurance with respect to any of the historical or prospective financial information. Any and all amounts referred to in this letter are preliminary and could change should additional procedures be performed. Additionally, issues that are not mentioned here could develop subsequent to the date of this letter that may have impacted Crowe’s analysis or that Crowe may cite should additional procedures be performed.

Crowe‘s fees are not dependent upon the outcome of this report and Crowe is independent with respect to any other economic interests.

We appreciate the contribution of SFPUC management and your input and direction on this project. We also thank Wastewater Enterprise management and staff for assisting providing access to the inventory and for timely responses to our requests.

Crowe LLP

Crowe LLP
Independent Member Crowe Global
575 Market Street, Suite 3300
San Francisco, California 94105-5829
Tel 415.576.1100
Fax 415.576.1110
www.crowe.com

San Francisco Public Utilities Commission
525 Golden Gate Avenue, 13th Floor
San Francisco, CA 94102

September 7, 2022

To: Steve Ritchie, Assistant General Manager, Water Enterprise Ellen Levin, Deputy Assistant General Manager, Water Enterprise William Teahan, CDD Division Manager
William Toman, CDD Materials Coordinator
Andy O’Brien, CDD Materials Coordinator

Subject: Fiscal Year End 2021-2022 Inventory Count –
Water Enterprise, CDD Main Warehouse, AWSS Warehouse, and 6 Satellite Warehouses (Auto, Carpenter, Electrical, Ground, LMPS, and Machine Shops)

Dear Mr. Ritchie, Ms. Levin, Mr. Teahan, Ms. Pohl, Mr. Toman and Mr. O’Brien:

The San Francisco Public Utilities Commission (SFPUC) engaged Crowe LLP (Crowe) to perform physical inventory count services for three enterprise funds including the Wastewater Enterprise, Water Enterprise, and Hetch Hetchy Enterprise.
1 This report represents results of Crowe’s physical count of the Water Enterprise inventory (CDD Main and AWSS Warehouses and 6 Satellite Warehouses).

A. Scope of Inventory Work

B. Warehouse Location(s)

The locations for the Water Enterprise physical inventory were:

CDD Main (and 5 Satellites)
1990 Newcomb Ave
San Francisco, California 94124

1 Note that some of the enterprises have multiple warehouse locations.

Page 2 ends

AWSS
1980 Oakdale Ave
Twin Peaks Reservoir
San Francisco, California 94124
LPMS Satellite
991 Lake Merced Blvd.

San Francisco, California 94132
Crowe performed inventory site visits of the Satellite shops, the MAIN storeroom and the AWSS storeroom between June 7 and June 9, 2022. The last full inventory count of CDD MAIN and AWSS storerooms was performed July 20 and 21, 2021 and August 10, 2021 by Crowe for Post FY 2020-2021.

The Water Enterprise has controls in place for security, including a closed parking lot and warehouse where the Water Enterprise allows only authorized personnel access. In Table 1 below, we provide key data2 from Crowe’s FY2021-2022 inventory count as well as the results from the last full FY2020-2021 inventory count conducted by Crowe. Note that the counts for FY 2021-2022 are higher than Post FY 2020-2021 as a result of the addition of the six satellites to the inventory count this year.

Table 1
San Francisco Public Utilities Commission
Water CDD (MAIN, AWSS, and 6 Satellite Shops) Inventory Count Summary of Key Data
(As of June 9, 2022)

Results

As indicated in Table 1, discrepancies between Crowe’s results from the sample count and the Maximo system were identified. The finding that follows explains the discrepancies in counts. Crowe’s recommendations related to the finding is also included. Management responses to the finding were provided by the Water Enterprise management team on August 18, 2022.

Finding #1 – Physical Inventory Counts Differed from Maximo Counts

Condition:

We identified a total of 472 items with a difference between the physical count and the items reported in Maximo. The total variance in the number of items counted was 23 percent (472 items divided by 2,030 items). This is a repeat finding. Based on the physical count, and using the average cost in Maximo, we determined that the total value of the Water Enterprise asset inventory with discrepancies to be $235,043, or 4 percent, above the current total asset value of $5,859,909 in Maximo.

2 Crowe did not perform any procedures related to the assigned value in the Maximo system.

It ems Value It ems Value
Co
unt with no Discrepancies 1,558 5,624,865$ 1,129 4,754,265$ Count Discrepancies 472 235,043 413 161,134 Total Inventory Counted 2,030 5,859,909$ 1,542 4,915,399$ Percentage of Inventory Sampled 100% 100% 100% 100%
Percentage of Discrepancies 23% 4.0% 27% 3.3%
Total Inventory per Maximo 2,030 5,859,909$ 1,363 4,915,399$ *Does not include counts for 6 satelitte shops.

Description
FY 2021-2022 (Crowe) FY 2020-2021 (Crowe)*

Page 3 ends

We found 258 positive variances (where the physical count exceeded the Maximo inventory count) which totaled $380,412. We found 214 negative variances (where the physical count fell below the Maximo inventory count) which totaled ($145,369). In Table 2 below we list 14 items with either positive or negative variances above $5,000. The combination of these 14 items represented $228,473 (($22,066) plus $250,539), or the majority of the total $235,043 variance (see Value of Discrepancies in Table1).

Table 2
San Francisco Public Utilities Commission
Water (CDD and AWSS and 6 Satellite Shops) Inventory Count List of Items with Variance Above or Below $5,000

Finally, as shown in Table 3 below, based on the fuel dip procedures Crowe performed for this physical count, we found the actual quantities of fuel contained in the two tanks varied from those in the Maximo system. We believe that the quantities we observed during our physical count using a manual dip process and a conversion formula were reasonably accurate as they closely matched quantities registered by an electronic measurement system contained in the fuel tanks that the Water Enterprise generated that day (referred to as the System Status Report).

Table 3
San Francisco Public Utilities Commission
Water (CDD and AWSS and 6 Satellite Shops) Inventory Count Summary of Fuel Gallon and Cost Variances

It em
Number
Description Shop It em Code Issue Unit Maximo
Count
Crowe
Count
Variance Average Cost Variance
1 GASOLINE, AUTO, UNLEADED, C.D.D. CD-MAIN 091-30-3250 GAL 5,862.1 3,697. 0 (2,165.10) 5.1933 (11,244)$ 2 DIESEL, RENEWABLE (99%) CD-MAIN 091-40-3151 GAL 3,976.8 2,090. 0 (1,886.80) 5.7357 (10,822)$ Subtotal Gasoline Related
(Variance +-$5000)
(22,066)$
3 TEE, 10" MJB X 8" MJB CD-AWSS 047-34-6404 EA 8 0 (8.00) 3,500 (28,000)$ 4 SENSOR, TOTAL CHLORINE, CTE 1-
MA, 0.1-10 PM (FOR D1C, D2C)
CD-LMPS 068-20-4078 EA 9 0 (9.00) 1,222 ( 10,999)$ 5 BEND, MJ X BELL, 90 DEG., 8" CD-AWSS 047-31-2490 EA 5 0 (5.00) 1,418 (7,090)$ 6 PIPE, D.I., CLASS 53, BELL & SPIGOT,
TYPE 2, 6" PUSH ON, ZINC COATING
CD-MAIN 047-10-4566 FT 565 320 (245.50) 27 ( 6,747)$ 7 TEE, 18" MJB X 8" FLANGE CD-AWSS 047-34-6411 EA 1 0 (1.00) 6,314 (6,314)$ 8 COLLAR, BELL, 18" CD-AWSS 047-33-9118 EA 8 2 (6.00) 874 ( 5,244)$ 9 COLLAR, STOP, 18" CD-AWSS 047-33-9018 EA 3 10 7.00 904 6, 328$ 10 ADAPTER, 16" SPIGOT GH X 16" MJ CD-AWSS 047-30-1044 EA 0 2 2.00 4,157 8,313$ 11 ADAPTER, 20" SPIGOT MJ X GH CD-AWSS 047-30-1052 EA 5 7 2.00 4,964 9,928$ 12 ADAPTER, 16" MJB X GH SPIGOT CD-AWSS 047-30-1248 EA 0 2 2.00 5,300 10,600$ 13 PLATE, STEEL, 1/4" X 48" X 96" CD-MACH 095-15-2150 SF 10 160 150.00 687 102, 977$ 14 PLATE, STEEL, DIAMOND, 1/4" X 4" X
8"
CD-MACH 095-15-0614 SF 6 192 186.00 950 176, 787$ 15 Subtotal Other than Gasoline
Related (Variance +-$5000)
250,539$
16 All Other Variances with Absolute
Value Less than $5,000
6,570$
Total 235,043$
It em
Number Description It em Code Issue Unit
Maximo
Count
(6.9.22)
Crowe
Count
(6.9.22) Variance Average Cost Variance
1 GASOLINE, AUTO, UNLEADED, C.D.D. 091-30-3250 GAL 5,862 3, 697 (2,165) 5.1933$ (11,244)$ 2 DIESEL, RENEWABLE (99%) 091-40-3151 GAL 3,977 2, 090 (1,887) 5.7357$ (10,822)$ Total (22,066)$

Page 4 ends

Criteria:

Chapter 6 of the City and County of San Francisco Office of the Controller’s Accounting Policies and Procedures identifies that Departments shall conduct a physical inventory at least annually and ensure that the inventory management system records accurately reflect inventory on hand. Additionally, Section 6.1 (Policy Guidelines) indicates that inventory system records must be promptly adjusted for any discrepancies identified in the physical counts, Departments must periodically review the inventory records for reasonableness and appropriateness of any changes to inventory unit cost, inventory valuation, and unit of measure, and Departments must periodically review the inventory for obsolescence and reasonableness of inventory classification. The Policy Guidelines also indicate that Departments should investigate significant discrepancies between the physical count, the inventory system records, and the accounting records; document the reasons for discrepancy; and take corrective actions to reduce future discrepancies.

Cause:

The Water Enterprise indicated several reasons for the differences in this physical count compared to the count in Maximo, including:

• Occasional system posting manual errors by the individual entering the data where the quantity or unit of measure entered was incorrect.
• Miscounted items from periodic cycle counts, including use of different counting methods and items not being fully separated to make accurate counts.
• S ome of the Satellite shop counts had never had previously cycle counts. • For some Satellite shop counts, the inventory users had not submitted issue tags to warehouse management when items were used.
• For the large variances associated with the Satellite Machine Shop counts, the Maximo counts were incorrect due to a unit of measure data entry error. For example, some items were recorded in Maximo either in square footage, or as individual sheets, when purchased and received, but then these items when issued were entered into Maximo by square feet rather than as individual sheets. • Fuel variances due to known existing system interface issues between SFPUC’s Maximo & EJ Ward software.

Regarding the overstated fuel volumes observed, we determined that the Water Enterprise uses the automated fuel tracking information system, E.J. Ward, to capture fuel usage and the SFPUC information technology department has developed an interface designed to periodically upload the E.J. Ward fuel usage data to the Maximo system. However, based on problems with the system interface between the E.J. Ward system and the Maximo system, actual Water Enterprise fuel usage data is not consistently and accurately uploaded into Maximo causing the variances.

Effect:

The Water Enterprise inventory value in the Maximo system is understated by $235,043. Regarding the fuel usage discrepancies, absent periodic monitoring of the fuel levels, the Water Enterprise will have difficultly reconciling future inventories and identifying variances that are the result of missing fuel (e.g., theft), delivery errors, data entry errors, tank leaks, or shrinkage.

Recommendation:

The Water warehouse staff should update the asset inventory counts in Maximo after management approval.

The SFPUC Financial Reporting and Analysis division should adjust the General Ledger to reflect the actual cost of inventory on hand. The Water Enterprise should investigate the significant discrepancies between the physical counts noted in Tables 1 and 2, the Maximo system records, and the accounting records; document the reasons for discrepancies; and implement corrective actions to reduce future discrepancies.

Page 5 ends

Regarding the fuel discrepancies, we recommend that in the short-term, the Water Enterprise develop a procedure to perform periodic manual reconciliations between actual fuel usage (either using manual handwritten logs or via system generated E.J. Ward reports showing amounts dispensed) with fuel usage amounts reflected in the Maximo system. The Water Enterprise should record fuel usage in cases where the data is not reflected in the Maximo system due to problems with the system interface. Over the longer term the Water Enterprise and the SFPUC Information Technology Services bureau should work toward fixing the fuel usage interface between the E.J. Ward system and the Maximo system such that manual reconciliations are not necessary.

Management Response:

The CDD Warehouse team concurs with these findings and commits to taking corrective actions to reduce future discrepancies. The CDD Warehouse Team will be extra careful in observing unit of measure discrepancies and correcting the conversions in the Maximo system before issue transactions post.

Meetings will be held to address with storekeepers of various count methods best for certain inventory items. Satellite shop supervisors will begin to conduct cycle counts in their respective areas. The CDD Warehouse Team will work with Satellite Shop Supervisors on having shop employees submit issue tags in a timely manner and on a consistent basis to accurately reflect the inventory that has been removed from the respective storage bins. Finally, fuel variances will be addressed by collaborative efforts between the CDD Management Team, the CDD Warehouse, and the Maximo IT Team. Units of measure variances have been identified and corrected for the Machine Shop inventory.

Other Matters

The inventory services did not contemplate obtaining the understanding of internal controls or assessing control risk, tests of accounting records and responses to inquiries by obtaining corroborating evidential matter, and certain other procedures ordinarily performed during an audit or examination. Thus, this engagement was not intended to provide assurance that we would become aware of sign ificant matters that would be disclosed in an audit or examination.

Crowe‘s fees are not dependent upon the outcome of this report.

We appreciate the contribution of SFPUC management and your input and direction on this project. We also thank Water Enterprise management and staff for assisting providing access to the inventory and for timely responses to our requests.

Crowe LLP

CleanPowerSF Sfpuc CommissionNovember 8, 2022

CleanPowerSF Sfpuc Commission
November 8, 2022