Loading...
Public Works Department's Warehouse Inventory Audit (Period 10-1-16 to 10-30-17) 9-19-18M IAMI BEACH City of Miami Beach , 1700 Convention Center Drive , Miami Beach , Florido 33139, www.miomibeochfl .gov Office of Internal Audit Tel : 305-673-7020 TO : FROM : DATE : AUDIT : PERIOD : INTERNAL AUDIT REPORT Jimmy L. Morales , City Manager ~ Mark D. Coolidge, Interim Internal Au \j Mb~ September 19, 2018 Public Works Department's Warehouse Inventory Audit October 1, 2016 -October 30, 2017 This report is the result of a regularly scheduled audit of the Public Works Department's warehouse inventory procedures, practices and valuation as of October 30, 2017, performed in conjunction with the external auditor's annual financial audit of the City. INTRODUCTION The Public Works Department's warehouse located at 451 Dade Boulevard supplies materials to all phases of Public Works operations (Water, Sewer, Storm Water, Engineering, Environmental, Streets and Street Lighting). These materials are received for and issued to the various divisions where they are either charged to specifically assigned job orders or individual vehicles . As part of the inventory monitoring process, job order numbers are 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. Small materials and equipment that are not job specific are charged to the individual truck numbers where the crew is assigned for such personal items as tools, raincoats, etc. to record disbursement at no charge. These items cannot be charged to a specific job and their issuance can be monitored by warehouse personnel to help ensure that departmental employees are not being careless with City property. Rece ived parts are accepted by warehouse staff and compared to the packing slip for completeness. The charged pr ices are also checked against those quoted to confirm their agreement. Once approved, the inventory is accepted, entered into the Cityworks Storeroom module (Cityworks) and is paid through normal procurement channels. This infrastructure management software has been used by Public Works since April 1, 2006. Parts issued to authorized personnel are recorded on a Cityworks generated form which is then signed by the appropriate parties. Among other information, these forms contain the date, job order number, part number, quantity needed, part description, job location and issuer name . The unit cost assigned to each item is based on the Cityworks' calculated weighted average cost which factors in each made purchase of the item since 1984. In 2012, the Public Works Department purchased a mob ile inventory management system from Motorola Solutions Inc. Additionally, Miles Technologies INVis ion Software was used to interface the Motorola 's handheld mobile computers to Cityworks by bringing the data entry device and bar coding to the location of the materials which helps eliminate the usage of the MX-1 form and its subsequent entry by warehouse staff into Cityworks . Although INVision was implemented in April 2014, some transitional shortcomings occurred whereby bar coding could not be used for processing received parts or to facilitate the physical inventory counts . We are committed to providing excellent public service and safety to all who live , work, and play in our vibrant, tropical , historic community . Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 In addition to material receipt, storage and distribution, the warehouse occasionally orders, maintains and supplies parts to outside contractors in support of other City capital projects. This may occur in instances where either the outside contractor damages City materials during his work (the contractor pays for materials ordered through a special budget code and work order), or when the City ag·rees to supply the materials and the contractor provides the labor (the City pays for both the materials and the labor). Warehouse personnel consisted of a Warehouse Supervisor, a Storekeeper Ill, a Storekeeper II, a Buyer and an Administrative Aide I. These personnel also interact with the Procurement Division, Finance Department, and vendors in the purchase and delivery of goods, inventory issuances and payments. A majority of the inventory is stored inside the Public Works Department's warehouse at 451 Dade Boulevard. It is the department's largest and most secure facility as it is safeguarded by cameras and locked alarmed doors. Other inventory storage locations on the property also protected by cameras include the following: • Yard 1 -A secure fenced area with a covered roof located directly south of the warehouse at the bottom of the loading dock ramp used to safeguard such larger, frequently issued parts as selected clamps, valves, glands, etc. • Yard 2 -A secure fenced area located south of Yard 1 behind the water tanks bordering Miami Beach Senior High School. The parts stored in this area consist primarily of light poles, hydrants, pipe, offsets, and caps. • Central Yard Area -City vehicles as well as assorted equipment and inventory (sand, rock, asphalt, smaller pipes, etc.) are stored here. Meanwhile, larger slower moving inventory was transferred to separate fenced facilities located at 75th Street and Dickens Avenue and 81 st Street and Hawthorne Avenue. Examples of inventory stored in these facilities are pipes, bends, sleeves, crosses, offsets, light poles and concrete bases. A full physical count is performed at the end of each fiscal year by designated Public Works Department personnel. This year's count was delayed by approximately one month due to Hurricane Irma and its adverse impact on the local community. Internal Audit subsequently conducts random test counts to verify the accuracy of these counts, with any noted differences re-counted with an assigned staff member until a consensus is reached as to the correct total. Once the final count is agreed upon, staff records the quantity on hand figures accordingly in Cityworks. Upon completion of all needed entries, a new fiscal year end value is calculated through the generation of a system report. Finally, this total value is forwarded to the City's Finance Department which prepares a journal entry to adjust the applicable general ledger inventory balances. The Public Works Department's warehouse inventory was valued at the following rounded amounts for the five most recent fiscal years: 9/30/2013 9/30/2014 9/30/2015 9/30/2016 10/30/2017 $1,899,775 $2,023,836 $2,315,686 $2,084,662 $1,689,014 Page 2of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 This October 30, 2017 inventory valuation of $1,689,014 was 18.98% less than the previous fiscal year's total due primarily to the issuance of parts due to Hurricane Irma and the surplusing of 729 deemed obsolete parts totaling $429,020.14 according to warehouse management. Internal Audit conducted test counts of 211 part numbers totaling 9, 706 units on hand with a cumulative value of $663,461.65 or 39.28% of the physical inventory count'svaluation. OVERALL OPINION Based upon the results of the test counts and the furnished inventory reports, Internal Audit recommends that the valuation of $1,689,014.40 be used as the October 30, 2017 inventory value. The annual physical inventory was conducted effectively with the corresponding valuation deemed reasonable. In conducting our physical test counts and reviewing the implemented internal controls, the following areas were identified that may be able to be improved upon: • A comparison of the Cityworks Storeroom module's (Cityworks) perpetual book counts with Internal Audit's verified sampled physical counts identified 12 differences totaling a net of $1,359.40 which at 5.69% (12/211) is significantly below last fiscal year's 31.50% difference and the desired internal goal of 10.00%. • Used parts returned to the warehouse having minimal value were occasionally stored in more secure areas and in close proximity to new parts. • Cityworks and INVision Software System shortcomings need to be addressed to better satisfy the warehouse's operational needs. • Shortcomings in the camera system monitoring the City's assets stored at 451 Dade Boulevard were noted. • Security deficiencies are identified that could allow unsupervised and/or unauthorized access to City assets thereby increasing the likelihood that they could be damaged or misappropriated. PURPOSE The purpose of this audit is to determine whether the Public Works Department's warehouse inventory is sufficiently counted, valued and recorded; properly safeguarded from theft and breakage; and that tested entries are correctly recorded in the City's Financial System. SCOPE 1. Confirm that documented standard operating procedures exist that are followed to help ensure efficient and effective administration of the warehouse. 2. Confirm that tested inventory parts are stored in an orderly manner, are clearly labeled, are properly safeguarded and are reasonably valued. Page 3of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 3. Confirm that the physical inventory was performed in an efficient, effective and timely manner with sufficient personnel and adequate procedures to ensure an accurate count. 4. Confirm by test counts that the final inventory report accurately reflects the final inventory valuation and that this information is correctly reported in the City's Financial System. FINDINGS, RECOMMENDATIONS AND MANAGEMENT RESPONSES 1. Finding -Sampled Part Numbers' Verified Counts Differed from Cityworks Perpetual Book Count Totals for 5.69% of the time which is Below the Desired Internal Maximum Goal of 10.00% The Warehouse Supervisor generated a Cityworks Storeroom module (Cityworks) report on October 24, 2016 of all Public Works inventory that was supposedly present. Theoretically, the pending physical counts should be identical to the units on hand as Cityworks uses the perpetual inventory method meaning that each part's number of units is increased or decreased accordingly as they are either received or issued. After stratifying this report's population by extended value (units on hand x unit cost), 211 part numbers were randomly selected for testing which consisted of 9, 706 units on hand with a total extended value of $663,461.65. Internal Audit's test counts for these parts were then compared to Public Works staff's physical counts and if they agreed then the corresponding total was used as the final verified count. If they differed, then the part was counted again with a designated warehouse employee until a consensus was reached as to the total. This verified count was then used as the basis for the sampled part's inventory valuation. Public Works Department staff conducted their physical inventory counts between Thursday October 24, 2017 and Saturday October 28, 2017 with Internal Audit's test counts completed on Sunday October 29, 2017. A comparison of the verified counts to the purported number of units on hand in Cityworks found differences for 12 of the 211 sampled part numbers or 5.69%, which falls below the desired 10.00% maximum internal goal. In addition, the corresponding net valuation difference for these 12 parts was ($1,359.40) as sometimes the Cityworks reported quantity was higher and other times the verified physical count was higher. As a result, the Warehouse Supervisor adjusted the Cityworks reported inventory totals when necessary to reflect the results of the full inventory physical counts, including Internal Audit's verified test counts, to help ensure that valuation is reasonably correct as of October 30, 2017. The following table provides an annual summary of the calculated percentage and net dollar differences for Internal Audit's sampled test counts during the past five fiscal years: FY 2012/13 FY 2013/14 FY 2014/15 FY 2015/16 FY 2016/17 Percentaae Difference 29.94% 21.29% 17.71% 31.50% 5.69% Net Dollar Difference ($5,323.90) ($7,915.68) $10,818.52 ($28,691.17) ($1,359.40) The correction of the following factors noted during previous audits most likely has contributed to the significant percentage difference decrease from last year's 31.50% to this year's 5.69%: Page 4of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 a. The implementation of cycle counts throughout the fiscal year has helped identify these differences earlier so that their underlying cause(s) can be determined and the necessary corrections made. Public Works Department management receives exception reports listing any materials quantities that have been adjusted due to the cycle count results. b. Warehouse staff may have initially assigned part numbers logically but over time as new parts were added they have become confusing, not necessarily consistent and can be easily mistaken. For example, there were previously ten different parts starting with ZPAC-01 which more concisely includes ZPAC-01, ZPAC-01.2, ZPAC-01.25, etc. whereby the inadvertent omission or inclusion of a number could result in the incorrect part number being received or charged out. As a result, two different part numbers' physical counts and the extended values charged out when the parts are used would be affected. To help correct this issue, warehouse staff has been creating new distinct part numbers as these parts' quantities reach zero so as not to impact valuations. c. Another previous concern noted was that most parts are charged out on a per unit basis but some are based on the whole box, the number of feet, etc. which can lead to inaccurate entries. For example, some pipes such as PIP-17.2, PIP-20.1, PIP-20.8, PIP-35, PIP-40.8, PIP-58, etc. were previously priced by the foot while others were based by the full-length piece (PIP-19, PIP-20.5, PIP-30.C, PIP-35.C, PIP-41.2, etc.). Warehouse staff has corrected this issue by making parts consistently measured to better ensure that all transactions will be correctly recorded. Recommendation( s ): Warehouse staff should be commended for significantly lowering the stratified sample's calculated percentage difference to 5.69% from last year's 31.50%, which is below the desired 10.00% maximum internal goal. Going forward, warehouse personnel should continue to implement these and other changes to try and further reduce this percentage difference. Lastly, departmental management should continue to closely review the exception reports documenting the changing of any parts' quantities/unit costs to determine their underlying cause(s) so that the necessary systemic corrections can be made to help prevent future differences from occurring. Management Response (Public Works Department): Public Works Warehouse staff continuously works hard on the performance of cycle counts and making necessary changes to part numbers in an effort to minimize errors or confusion between part numbers. We continue adding actual pictures of the parts to make sure we are signing out the correct parts. All cycle counts conducted by the warehouse staffs are reviewed for correctness by the Warehouse Supervisor. The Supervisor will then make necessary changes in the system as needed and determine where a mistake may have been made. Cycle counts and audits with respective comments are provided to the Warehouse Superintendent. Page 5of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 2. Finding -Used Parts Returned to the Warehouse having Minimal Value were Occasionally Stored in More Secure Areas and in Close Proximity to New Parts Occasionally, previously charged out parts that were used in the field are returned to the warehouse for safekeeping as they still have some value. These used parts are typically used only as a stop gap in case of an emergency until new parts are received. Several of these used parts were noted during the physical inventory count but inquiries found that they were excluded due to valuation concerns, potential adverse impacts on the weighted average costing of the applicable units as discussed in finding #3a, the part was already fully charged out, etc. To determine the materiality level of these used parts, Public Works staff subsequently provided a listing on September 6, 2018 of all the safeguarded used parts which totaled only an estimated $10,445. In addition, this listing included five different parts containing more than 50 items that are designated to be sold for scrap metal or have no value and can be disposed of prior to the upcoming 2017/18 fiscal year inventory. Due to space limitations, some new parts having higher values were stored in the less secure Central Yard area. Meanwhile, several of the used parts were found stored in more secure areas like Yard 2. Also, these used parts were occasionally stored near the same new part which increases the likelihood that they could be confused and the used part could be issued by mistake. Since the inventory process was completed, Public Works staff have moved all new parts from the less secure 75th Street and Dickens Avenue (?5th Street) plus the 81st Street and Hawthorne Avenue (81st Street) locations to more secure areas at 451 Dade Boulevard. This practice will provide better assurance that City assets are properly protected, will help free up warehouse staff's time for other more important duties as they no longer must travel to these remote locations to issue parts, etc. Recommendation( s ): Public Works Administrative staff should continue to update their listing of used parts to help facilitate usage in an emergency and to improve accountability. Any parts deemed being obsolete and having no value should be scrapped prior to the upcoming fiscal year inventory. Division supervisors should also closely monitor the usage of any used parts to help ensure that they are timely replaced with new parts. In addition, all used parts should be kept separate from new parts in a less secure area so that all new parts having higher values can be adequately safeguarded. Management Response (Public Works Department): Presently, all used parts have been removed from the Public Works Yards at 451 Dade Blvd and from Hawthorne Avenue (81 st Street) locations and relocated to the Dickens Avenue (?5th Street) pump station yard for safe keeping. Public Works Operations will continue to update the list of used parts and require for Supervisor's to report to the warehouse staff of any parts used in the field, or used parts coming in to the yard for storage. Further, the Public Works Warehouse Supervisor will continue its efforts to scrap existing and future parts deemed unusable following established City Procedures. Page 6of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 3. Finding -Inventory Software Shortcomings Need to be Addressed to Better Satisfy the Warehouse's Operational Needs The Public Works warehouse implemented a mobile inventory management system purchased from Motorola Solutions Inc. in April 2014 called INVision to interface the handheld mobile computers to Cityworks (implemented on April 1, 2006) by bringing the data entry device and bar coding to the location of the materials. Inquiries with warehouse staff and a cursory review of the two software systems identified the following shortcomings during testing in the Cityworks and INVision sections: Cityworks a. Parts continue to be valued using a weighted average costing approach that supposedly includes all corresponding purchases made 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. For example, it was determined that 34 of the 211 sampled parts or 16.11 % have not been purchased since April 1, 2006's Cityworks inception. Of the remaining 177 sampled part numbers, a comparison of the most recent purchase price with the weighted average price listed in Cityworks found that 14 parts' percentage difference exceeded 20.00%, either higher or lower. The largest difference noted was 99.25% for part number CRP-02.3 as its most recent purchase price on February 19, 2009 of $34.45 was $17.16 more than the listed Cityworks weighted average cost of $17.29. b. Maximum and minimum quantities are not currently recorded in Cityworks to aid staff in ordering. The warehouse supervisor or his/her designee could then generate a daily report alerting them as to what part numbers have fallen below the minimum threshold and as to the corresponding quantity to be ordered. In its absence, ordering continues to be done manually as staff becomes aware which could lead to inadvertent stock outs or overstocks thereby negatively affecting carrying costs and inventory turns. c. Cityworks does not currently track whether each part number in inventory is to be recorded in the general ledger as belonging to water and sewer; streets and streetlights; storm water or tools and supplies. As a result, staff has to undertake the time consuming annual process of reviewing each part number and project who the end user will be so that the proper accounts can be impacted. INVision d. The INVision System only recognizes the charging out of whole numbers (1, 2, 3, etc.) and not decimals. Therefore, parts such as rock, asphalt, sand, etc. that are usually disbursed in other than whole numbers (ex. 1.25, 2.52, etc.) have to be issued on a manually prepared MX-1 form and directly entered into Cityworks bypassing INVision. e. The INVision System does not transmit the receipt of goods through either delivery or return of previously issued inventory properly to Cityworks. The identified short- term solution has been to enter the received parts directly into Cityworks which will Page 7of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 then communicate the corresponding entries to INVision. Similarly, the bar coding function cannot be used yet to facilitate the labor intensive physical inventory count completed annually. Recommendation( s ): Plans to convert from the current continuous weighted average inventory valuation method to FIFO (First-In, First-Out), LIFO (Last-In, First-Out), specific identification, etc. should be pursued so that the listed valuations more accurately reflect the actual value of the units on hand. Also, all desired Cityworks and INVision changes should be prioritized and sent to the City's GIS Manager and the vendor for their review and comments. Staff may also need additional software training to help implement these changes and to learn about the availability of other system functions not known or utilized. Finally, the proposed conversion to a new web based version of Cityworks and the elimination of the INVision software should be completed as soon as possible to aid warehouse staff in the completion of their duties. Management Response (Public Works Department): Since December 8, 2017 Public Works Warehouse inventory system (Cityworks) has been upgraded to a new web data base version and no longer uses INVision. Since the upgrade, all inventory has been converted from weighted average inventory to FIFO inventory using the Cityworks data base. The new upgrade is capable of reading barcodes without the need of a third-party system. 4. Finding -Security Cameras Deficiencies Were Noted Which Require Attention The Public Works facility located at 451 Dade Boulevard is monitored by forty-one digital cameras to record daily activities. These high definition dome or bullet cameras were purchased in April 2017 and are an improvement in number and clarity over the prior camera system. For example, several new cameras were added inside the warehouse where most of the inventory is stored to help provide better coverage and the picture is much clearer thereby making it easier to identify individuals, license plates, etc. The system hardware, computer monitors, etc. are adequately safeguarded in the continuously staffed Control Room. These cameras are primarily used as a detective measure to be reviewed after an incident has occurred to help determine fault. An unannounced site visit was performed on November 2, 2017 to review these cameras positioning and effectiveness. Twenty-five (25) cameras' activities can be viewed on "Monitor 1" with another sixteen (16) visible on "Monitor 2". All cameras reviewed were functioning properly except for the one on "Monitor 1, Camera 15" which was not working as the screen was blacked out. Finally, there is no blueprint or template documenting the cameras location and areas covered to facilitate identifying which if any are not functioning properly, where they are positioned, what areas they cover, etc. Recommendation( s ): "Monitor 1, Camera 15" should be repaired as soon as possible to better ensure complete coverage of the facilities. The positioning and effectiveness of the cameras should be continually reviewed to maximize their benefit to the City. In addition, a blueprint or a template should be maintained showing the location of each camera and the area covered. Page 8of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 Management Response (Public Works Department): The camera service contractor has been contacted to service camera No. 15 on monitor 1. In addition, Public Works Operation is currently working on the design of the blueprint for the 41 cameras in the Public Works Yard. 5. Finding -Security Deficiencies are Identified that Could Allow Unsupervised and/or Unauthorized Access to City Assets Site visits during inventory to the different Public Works Department inventory locations found the following fencing and lock deficiencies that increase the possibility that City assets may be misappropriated, damaged or vandalized: a. The front gate at 451 Dade Boulevard was not functioning during Internal Audit's November 2, 2017 site visit. As a result, it was continuously left open thereby placing greater reliance on the facility's compensating controls (cameras, interior fencing, etc.). b. The northern gate located behind the control room that opens to the old par 3 golf course was found closed but unlocked and there were no departmental employees present. c. Numerous fencing repairs are needed at 451 Dade Boulevard, 75th Street and 81st Street facilities which most likely were caused by Hurricane Irma. Purchase Order No. 20172245-00 was created for $38,531.00 to repair damage at these and other Public Works Department operated facilities but the work had not yet been completed. Recommendation( s ): The implementation of the following recommendations should help better protect the City's assets stored at the tested Public Works facilities: a. The front gate should be repaired and kept locked during nights, weekends, etc. so as to help deter unauthorized entry. b. The northern gate should be locked whenever it is not being used by Public Works Department staff. c. The fencing repairs should be completed as soon as possible to better safeguard City assets. Management Response (Public Works Department): a. The front gate has been repaired since the audit inspection and it is kept locked during nights and weekends. b. Warehouse staff has been instructed to lock the northern gate after use. c. As of December 13, 2017, all fencing repairs have been completed on all locations. Page 9of10 Internal Audit Report Public Works Department's Warehouse Inventory Audit September 19, 2018 EXIT CONFERENCE An exit conference was held at 451 Dade Boulevard's 2nd floor's conference room. Participants included Roy Coley (Public Works Department Director), Rodolfo De La Torre (Infrastructure Director) and Mark Coolidge (Interim Internal Auditor). All parties were in agreement as to the contents of this report. Management responses were solicited and included in our report. F:\OBPl\$AUD\INTERNAL AUDIT FILES\DOC17-18\REPORTS -FINAL\PUBLIC WORKS INVENTORY RPT 2017 09-19-18.docx cc: Eric Carpenter, Assistant City Manager Roy Coley, Public Works Department Director Rodolfo De La Torre, Infrastructure Director John Woodruff, Chief Financial Officer Page 10of10