Loading...
Public Works Inventory ProcessingMIAMI BEACH BUDGET AND PERFORMANCE IMPROVEMENT Internal Audit Division INTERNAL AUDIT REPORT TO: VIA: FROM: DATE: AUDIT: PERIOD: Jorge M. Gonzalez, City Manager Kathie G. Brooks, Budget and Pre':Z.r ance Improvement Director James J. Sutter, Internal Audito7~ November 20, 2009 Public Works Department Inventory Processing Audit October 1, 2008-July 31, 2009 This report is the result of a specially requested audit by City Management to review the processes used in the receipt, storage and distribution of inventory by the Public Works Department's warehouse. This audit did not include an inventory physical count as that will be done at fiscal year end. INTRODUCTION The Public Works warehouse located at 451 Dade Boulevard, supplies materials for Public Works Water, Sewer, Streets, Streetlights and Stormwater divisions. These materials are received for and issued to the various divisions (water, sewer, storm water, etc.) and are either charged to specifically assigned job orders and individuals or to the receiving department's designated budget code. Job order numbers were created to track the cost of labor, materials, and equipment used in projects performed by the Public Works Department; to allow the Department to know the cost of the projects; and to recover the cost of repair or replacement of City property damaged by or caused by an outside party. Meanwhile, small materials and equipment as tools, raincoats, etc. that are not job specific as they are used repeatedly at more than one location are charged to the individual crew truck account to show disbursement at no charge to the employee. Finally, items like paper cups and towels that are used by other City departments are charged out directly to the receiving departments indicated budget code. Received parts are accepted by warehouse staff and compared to the packing slip, purchase requisition, etc. for completeness. In addition, the charged prices are checked against those quoted to confirm their agreement. Once approved, the inventory is accepted, entered into the Cityworks system and ultimately paid through normal procurement channels. Conversely, parts are issued to authorized personnel upon the proper completion of Public Works originated inventory issuance (MX-1) forms. Among other information, these pre-numbered sequential forms printed by Central Services require the preparer to enter the date, job order number, part number, quantity, part description, job location and issuer name. The cost assigned to the item is based on the Cityworks calculated weighted average cost which factors in each corresponding purchase made since 1984. The MX-1 form's white copy is maintained internally by the warehouse and filed by the associated number to help ensure that all are accounted for. The yellow copy is sent to the operations supervisors (Water, Sewer, etc.) typically at week end and the final (pink) copy is given to the employee receiving the inventory at the time the materials are charged out. The operations supervisors are to periodically match the yellow and pink copies to ensure agreement and investigate any differences. VVe ore commilled to providing oxcellent public Sclrvico one/ sofety to o/1 who /ivo, work, one/ ploy in our vibmnt, tropicol, historic l:omrnunity. Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 A similar sequential pre-numbered three part MX-2 form is occasionally used to record the receipt of goods whose exact quantity is initially unknown (rock, sand, asphalt, propane, etc.). The actual amount received is determined at delivery and is documented along with such other things as the items received, their stock numbers, items descriptions, the quantities received and their cost. Warehouse staff input directly from these MX-2 forms into Cityworks with a printout attached to confirm that the information entered was correct. This supporting documentation is stored in folders by the month in which the items were received. Warehouse personnel currently consists of a Warehouse Supervisor, a Storekeeper Ill, two Storekeeper lis, a Municipal Service Worker I and an Administrative Aide I. These personnel interact with the Procurement Division, Office of Budget and Performance Improvement, Finance Department, and vendors in the purchase, receipt, and issuance of goods as well as their corresponding payment. They all have the ability to disburse inventory from the Cityworks System implemented in April1, 2006 but only the Warehouse Supervisor and Storekeeper Ill have the ability to add inventory. The Public Works Department had 115 purchase and change orders with 80 different vendors to provide services and materials totaling $1,942,010.23 for the 2008/09 fiscal year. Examples of approved vendors include Aaron Industrial Safety, Condo Electric Motor Repair, Mercedes Electric Supply Inc., Sensus Metering Systems and Universal Signs & Accessories. The ordering of needed inventory is done by the Warehouse Supervisor and/or Storekeeper Ill upon notification by the staff, review of Cityworks listed quantities or upon his/her site inspections. All parts are received at the warehouse so that someone is present to inspect it for completeness and to attest to its receipt. Afterwards, the items are brought into inventory and stored until they are charged out. A majority of the Public Works inventory is stored inside the warehouse located at 451 Dade Boulevard. It is the largest and most secure facility as it is protected by cameras and locked alarmed doors. Other inventory locations on the property also protected by cameras include the following: • Yard 1, a secure fenced area with a covered roof, is located directly south of the warehouse at the bottom of the loading dock ramp. This secure area is used to safeguard such larger, frequently issued items as selected clamps, valves, glands, etc. • Yard 2 is another secure fenced area located further south of yard 1 by the water tanks and bordering Miami Beach Senior High School. The items stored in this area consist primarily of light poles, hydrants, offsets, and caps. • City vehicles, and assorted equipment and inventory are stored outside in the middle of the yard. Finally, large slow moving inventory is presently stored at the gated facilities located at 75th Street and Dickens Avenue and 81 st Street and Hawthorne Avenue. Examples of inventory stored in these facilities are selected bends, sleeves, light poles and concrete bases. Page 2 of 8 Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 OVERALL OPINION The Public Works warehouse maintains and safeguards approximately $2 million in inventory at any given time at several locations and is continually receiving and issuing needed items. As a result, it is imperative to keep accurate, complete documentation and to follow sound internal controls to help ensure that all inventoried goods are accounted for. Internal Audit's testing showed that operations and systems appear to be functioning as intended but a number of opportunities for improvements were discovered as shown by the following: • Completed pre-numbered sequential MX-1 forms that record the issuance of inventory were not all accounted for, were not completed in a manner so as to prevent subsequent unauthorized changes and were not properly controlled to ensure that all issued parts were delivered to and used at the job site. • A comparison of the manually prepared MX-2 forms to record the receipt of inventory with the corresponding Cityworks entries found differences in 71% (5/7) of those sampled with no known means to verify which is correct. • Cityworks has not been fully implemented and needs system revisions to better satisfy Public Works warehouse's operational needs. • The Public Works Department has not updated its policies and procedures to better detail the newly instituted warehouse and Cityworks practices. • The Finance Department prepares timely monthly manual journal entries to record the issuance of materials as the Cityworks and Eden Systems do not interface well thereby increasing the possibility of mistakes. PURPOSE The purpose of this audit is to determine whether transactions are economically and efficiently processed, accurately and promptly recorded, reported and followed up; whether City assets and key forms, records, files and systems are properly safeguarded and controlled, and access thereto is restricted in accordance with management's criteria; and whether final disposition of these properties was in compliance with department policies and procedures. SCOPE 1. Confirm that documented operating policies, standards and procedures exist to ensure efficient and effective administration of the warehouse and that there is compliance with these policies, standards and procedures, as well as all laws and regulations. 2. Confirm that the CityWorks System is being fully utilized to facilitate daily operations and to accurately compile, value, and record the inventory. 3. Confirm that a proper segregation of duties and sound internal controls exist. 4. Confirm that all tested inventory transactions (addition and removal) were accurately valued, approved and processed. 5. Confirm that well organized, complete and sufficient documentation is maintained to support all tested transactions. Page 3 of 8 Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 FINDINGS, RECOMMENDATIONS AND MANAGEMENT RESPONSES 1. Finding-MX-1 Forms Completion All the sequentially pre-numbered MX-1 s that recorded the issuance of materials on the four randomly selected dates of 12/01/08, 03/12/09, 06/26/09 and 07/08/09 by Public Works warehouse staff were tested. These forms are manually prepared to record such pertinent information as the date in which the materials were issued, the work or job order number, who issued the materials and who received them, the part number, the quantities issued and descriptions of the items, the signatures of the employees issuing and receiving the items, etc. MX-1 forms have three copies with the white being maintained by the warehouse; the yellow is given by the warehouse periodically to the corresponding operational supervisor for review and the pink is kept by the employee receiving the goods. These white copies are ultimately stored inside a warehouse cabinet attached to a printout confirming entry into the Cityworks system. The following deficiencies were noted during the locating and review of the corresponding 121 MX-1 forms sampled (includes two forms labeled as "void') which could among other things helped cause the noted discrepancies between past fiscal years' end physical counts and the recorded Cityworks counts: a. The manual completion of the MX-1 forms in their present format and their subsequent entry into Cityworks is a duplication of work, entries can be later unknowingly changed, entries on the yellow and pink carbon copies can be difficult to read, etc. b. Two forms numbered 79704 and 84809 of the 121 tested or 1.65% could not be located as of the conclusion of this audit. c. .85% or 1 of the 119 MX-1 forms reviewed (numbered 79714) was missing the issuing employee's signature, while all properly contained the signatures of the individuals authorizing the issuance. d. Such important information as the part number and/or quantity issued were changed by crossing out, writing over, whiting out, etc. the listed information in 14 of the 117 or 11.97% of the MX-1 forms reviewed. They did not contain any notations as to who made the changes and the reasons why. e. The reviewed forms did not always indicate the last item charged out to prevent materials from being subsequently added that were not issued. f. The quantities of the items issued that were entered in Cityworks appeared to differ from those listed on the MX-1 forms for 2 of the 117 reviewed or 1.71 %. The corresponding MX-1 forms numbered 85035 and 85038 were written over making it difficult to determine exactly which figure is correct. Recommendation( s): The implementation of the following recommendations concerning the completion and storage of MX-1 forms should help resolve the listed shortcomings: a. The MX-1 forms should be automated to save workers' time, to help prevent someone from later changing entries, to help ensure that all duplicate copies are Page 4 of 8 Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 easily read, etc. b. The missing forms or a copy thereof should be located and added to Public Works warehouse's existing files so that a complete set is maintained to help substantiate the issuance of materials. In the future, greater care should be taken to help ensure that all MX-1 forms are accounted for. c. All MX-1 forms should be fully completed at the time of issuance so that when both parties' sign they attest to the accuracy of the listed information and no changes should be forthcoming. d. If the MX-1 form is automated then any future changes would have to be entered into the computer so that a new form would be created requiring both parties to sign again. In the interim, any changes should be clearly noted, initialed by both parties and sufficiently explained to avoid any confusion when entered into the Cityworks system or a new form should be prepared. e. Under the current format, submitted forms should always contain a line drawn from the last items charged out to the bottom to help prevent others from subsequently adding parts. f. A designated warehouse employee independent of the entering process should periodically compare the entries in Cityworks with the data on the MX-1 forms for agreement. Any differences should be further investigated immediately. Management Response: A meeting was recently held between Public Works and the City's GIS Manager where it was agreed upon that a bar code inventory system should be implemented in the Cityworks warehouse module by October 2010. 2. Finding -MX-2 Form Entry Public Works warehouse staff occasionally orders via telephone such commonly used items as asphalt, rock, sand, propane, etc. as needed. The exact quantities to be received are not known until received. At this time, pre-numbered MX-2 forms are prepared listing among other things the items received, their stock numbers, items descriptions, the quantities received and their cost. The corresponding entries into Cityworks are then printed and attached to the applicable MX-2 form to confirm accurate input. Internal Audit tested seven completed MX-2s and compared their listed information to the Cityworks entries and found the following differences: • MX-2 number 22555 dated 11/26/08 listed six more ZREC-12 parts than were entered. Additionally, Cityworks contained entries for sand and rock that were not present on the manually prepared MX-2 form. • MX-2 number 22556 dated 11/25/08 contained sand, rock and mega lugs that were not entered into Cityworks. • MX-2 number 22580 dated 03/27/09 listed a $.30 less unit cost than entered into Cityworks for propane. • MX-2 number 22584 dated 04/1 0/09 recorded less asphalt than the amount entered into the Cityworks System. • MX-2 number 22589 dated 04/29/09 listed different quantities of rock and sand received. Furthermore, review of the Cityworks report showed other entries of asphalt, concrete, rock and sand that did not appear on the MX-2 form. Page 5 of 8 Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 Recommendation(s): As shown above, a number of differences existed in the comparison of the manually prepared MX-2 forms' data and the corresponding entries into the Cityworks system. Internal Audit had no known way to confirm which entries were correct. Going forward, the two should show agreement to facilitate independent reconciliation and they should be periodically reviewed for accuracy with any differences investigated. Finally, the MX-2 forms should also be automated to save workers' time, to help prevent someone from later changing entries, to help ensure that all duplicate copies are easily read, etc. Management Response: The incorrectly entered items specified above were minor in nature and were caused by a warehouse employee not paying attention to his work which at present time, is no longer entering items in MX or onto Cityworks. Also, the automation of MX-2 forms will be discussed with Azteca, Inc. and the City's GIS Manager to determine its feasibility. 3. Finding -Cityworks Inventory System The current Public Works warehouse inventory computer system called Cityworks was implemented April 1, 2006. Although this system contains many improvements over the previous PWK system, the following shortcomings were identified: a. Inventory items continue to be valued using a weighted average costing approach that includes all purchases made of the item since 1984. Technology advancements and component materials' price changes have caused some parts' most recent purchase prices to vary both negatively and positively from the recorded weighted average prices. As a result, fiscal year end inventory valuations continue to be impacted. Public Works Administration is currently researching the materiality of the conversion to FIFO (First In First Out) method of inventory valuation and plans to implement this new method pending the approval of the Finance Department. b. Maximum and minimum quantities have not yet been recorded in Cityworks to facilitate ordering. In their absence, ordering continues to be done manually which could lead to inadvertent stockouts or overstocks thereby negatively affecting carrying costs and inventory turns. c. Such important information as the quantity of items disbursed, the MX-1 number and the truck number are combined in one field under the "Comments" section in Cityworks. Consequently, this data cannot be easily separated so that it can be sorted and the desired information found quicker. For example, someone currently has to scan each entry to determine how many tools, rain coats, etc. have been charged out to a truck crew to try to determine if any abuses occurred. d. Cityworks does not alert the user as to a possible input error when entering inventory quantities that exceed the amounts designated in the previously prepared requisition. Conversely, the system does inform the enterer when the quantity is less and those items are on back order. Recommendation ( s): The implementation of the following recommendations should help the City better realize Cityworks capabilities: a. Plans to convert from the weighted average inventory valuation method to first-in- first-out should be continued in an effort to more accurately reflect the actual value of the inventory currently in stock. b. Ordering should be automated to help ensure that inventory levels stay within Page 6 of 8 Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 desired parameters. c & d. All known Cityworks desired changes should be prioritized and sent to the vendor for their review and comments. This listing should include separating the data entered in the "Comments" section to facilitate sorting and locating and installing warnings to alert the enterer when the quantity entered exceeds the amount listed on the requisition. Management Response: A meeting was recently held between Public Works and the City's GIS Manager to further discuss these recommendations where it was agreed that implementation should occur no later than October 2010. Azteca Corp. will be contacted for discussion on Cityworks weighted average changes to first-in-first out which may be a major task/change. 4. Finding -Policies and Procedures Although descriptive and covering a wide array of topics, the Public Works Department's policies and procedures were last revised (effective 1 0/01/02) and did not fully address warehouse operations. Some of the previous procedures changed with the implementation of the Cityworks System in April 2006. This process is known entirely by few and should be better documented to avoid further difficulties if personnel change. Recommendation(s): The Public Works Department's operating policies and procedures should be updated as soon as possible and should more descriptively outline current warehouse operations. Policies and procedures serve both as a benchmark to measure individuals' performance and as an instruction manual in the event employees' change. Management Response: Departmental policies and procedures will be reviewed and revised as needed. They are expected to be completed by mid-January 2010. 5. Finding -Financial System Entries Public Works warehouse staff runs the Cityworks created Warehouse Issue Report Summary at month end to capture the total value of inventory issued for each applicable general ledger account. This report is then forwarded to the Finance Department for review and entry into the City's Eden System as the two systems do not interface to allow entries to be automatically uploaded. Review of March 2009 through July 2009's journal entries and supporting documentation found the following shortcomings: • Up to five separate instances were found in a given month whereby general ledger account entries into Cityworks varied for the same account causing duplicate entries. For example, general ledger account number 427-0427-000342 showed a $1,229.18 balance, while there was another entry for the same account listed as 427.0427.000342 with a $1,685.50 balance. All duplicate accounts reviewed were correctly combined and entered into Eden by the Finance Department but their existence creates additional work for someone to identify and total these accounts. • June 2009 Cityworks report incorrectly reported a total of $499.95 for general ledger account number 011-0840-0000342, which contains one too many digits (an extra zero). The Finance Department identified the mistake and made the correct journal entry. • There was a $65.28 entry into a non-existent general ledger account number of 425- Page 7 of 8 Internal Audit Report Public Works Warehouse Inventory Processing Audit November 20, 2009 0342-000342 in July 2009. The Finance Department correctly identified the mistake and properly included the amount in 425-0410-000342 in journal entry numbered 010-005. Recommendation(s): Efforts should be undertaken to determine if Cityworks can be uploaded directly into Eden to prevent the future need for these journal entries. If not possible, then Cityworks should be contacted to determine if it can be revised so that all general ledger account entries are standardized and/or City personnel should be instructed on the need for uniformity. Finally, someone designated in Public Works should review the Cityworks entries for accuracy and correct any identified mistakes. Management Response: The City's GIS Manager will discuss this request with Azteca Corp., as well as Eden Corp. A follow-up response will be provided by mid-January, 2010. EXIT CONFERENCE An exit conference was held on November 4, 2009 in the Public Works Department. Participants included Michael Alvarez (Assistant Public Works Director), Elie Fakie (Water and Sewer Superintendent), Martha Torres (Warehouse Supervisor), George Corchado (Storekeeper Ill), James Sutter (Internal Auditor) and Mark Coolidge (Senior Auditor). All parties were in agreement as to the contents of this report. Management responses were solicited and included in our report. JJS:MC:mc Audit performed by Mark Coolidge (Senior Auditor) F:\obpi\$AUD\INTERNAL AUDIT FILES\DOC08-09\REPORTS-FINAL\PUBLIC WORKS INV PROCESSING.doc cc: Jorge Gomez, Assistant City Manager Fred Beckmann, Public Works Director Michael Alvarez, Assistant Public Works Director Patricia Walker, Chief Financial Officer Page 8 of 8