Loading...
C7M-Approve Sole Source Purchase Mobile Inventory Management System From MotorolCOMMISSION ITEM SUMMARY Condensed Title: A Resolution Of The Mayor And City Commission Of The City Of Miami Beach, Florida Approving, Pursuant To Section 2-367 (d) Of The Miami Beach City Code, The Sole Source Purchase Of A Mobile Inventory Management System From Motorola Solutions Inc., Which Integrates with CityWorks, In The Estimated Total Amount Of $137,067. Ke_y Intended Outcome Supported: Maintain City's Infrastructure and Improve Storm Drainage Citywide Supporting Data {Surveys, Environmental Scan, etc.):N/A Issue: I Shall the Mayor and City Commission adopt the Resolution? Item Summary/Recommendation: The implementation of Cityworks was part of the original proposal for an Infrastructure Management System (IMS) in the Public Works Department, which included a Computerized Maintenance Management System (CMMS) integrated with a Geographic Information System (GIS). Cityworks is operational and has been in use by the following Public Works' Divisions since May 2006, and has been in use in Engineering since June 2010. Cityworks has fulfilled the Public Works Department's requirements for the past five (5) years. Azteca Systems, Inc. designs, creates, and owns the Cityworks software, including the copyright and trademark ownership. Azteca Systems, Inc. is the sole source provider and licensor of the software and therefore, the sole source provider for software maintenance services within the United States. Motorola Solutions has represented the Cityworks product over nine (9) years. Motorola Solutions commitment to the product has allowed the establishment of a strong relationship with Azteca Systems, and that success has been expanded into the current relationship they have in place with Miles Technology. Miles Technologies INVision Software is used to interface the Motorola's handheld mobile computers to Azteca Cityworks StoreRoom Module. This partnership is unique and provides the Mobile Inventory Management System offering exclusive to Cityworks, which is the only experienced implanter of the Mobile Inventory Management System that integrates with Cityworks Storeroom. This system has been installed in the maintenance warehouse of O'Hare Airport in Chicago and it has increased the efficiency and reliability of the asset management system. The Mobile Inventory Management System will solve these problems by bringing the data entry device and bar coding to the location of the materials. The total inventory of Public Works Operations Warehouse is on an average $2,600,000.00 with a total of $1,100,000.00 materials issued to work orders in a year. THE ADMINISTRATION RECOMMENDS ADOPTING THE RESOLUTION. Adviso Board Recommendation: The case was presented to the IT Steering Committee on July 20, 2011 and it was approved without any further recommendations. Financial Information: Source of Amount Account Funds: 1 $ 45,689 Public Works Funding-425-0410-000343 2 $ 45,689 Public Works Funding-425-0420-000343 qp 3 $ 45,689 Public Works Funding-427-0427-000343 Total $137,067 Financial Impact Summary: City Clerk's Office Legislative Tracking: j Raul Aguila Si n·Offs: Department Director MIAMI BEACH 255 AGENDA ITEM {!. 7J'1 DATE 7-IK-(1...- m MIAMI BEACH City of Miami Beach, 1700 Convention Center Drive, Miami Beach, Florida 33139, www.miamibeachA.gov COMMISSION MEMORANDUM TO: FROM: DATE: SUBJECT: A RESOLUTION OF THE MAYOR AND CITY COMMISSION OF THE CITY OF MIAMI BEACH, FLORIDA APPROVING, PURSUANT TO SECTION 2-367 (d) OF THE MIAMI BEACH CITY CODE, THE SOLE SOURCE PURCHASE OF A MOBILE INVENTORY MANAGEMENT SYSTEM FROM MOTOROLA SOLUTIONS INC, WHICH INTEGRATES WITH CITYWORKS, IN THE ESTIMATED TOTAL AMOUNT OF $137,067. ADMINISTRATION RECOMMENDATION Adopt the Resolution. KEY INTENTDED OUTCOME SUPPORTED Maintain City's Infrastructure and Improve Storm Drainage Citywide. FUNDING The funding of $137,067 will be evenly split between the following accounts: 425-041 0-000343 -$ 45,689 425-0420-000343 -$ 45,689 427-0427-000343 -$ 45,689 $137,067 ANALYSIS The implementation of Cityworks from Azteca Systems, Inc. was part of the original proposal for an Infrastructure Management System {IMS) in the Public Works Department, which included a Computerized Maintenance Management System {CMMS) integrated with a Geographic Information System {GIS). A Request for Proposals {RFP) was issued in 2004. The contract for the development of the CMMS portion of the project was awarded to Camper Dresser, McKee, Inc. {COM) in 2005. Cityworks from Azteca was selected to be the best software system based on this RFP. Cityworks software was purchased for the City as part of the CMMS project. 256 Commission Memorandum -Cityworks Mobile Inventory Management System Sole Source July 18, 2012 Page2 Cityworks is operational and has been in use by the following Public Works' Divisions: Water, Storm Water, Sewer, Streets, Street Lights, Sanitation and GIS since May 2006, and has been in use in Engineering since June 2010. Cityworks has fulfilled the Public Works Department's requirements for the past five (5) years. Azteca Systems, Inc. designs, creates, and owns the Cityworks software, including the copyright and trademark ownership. Azteca Systems, Inc. is the sole source provider and licensor of the software and therefore, the sole source provider for software maintenance services within the United States. Motorola Solutions has represented the Cityworks product over nine (9) years. Motorola Solutions commitment to the product has allowed the establishment of a strong relationship with Azteca Systems, and that success has been expanded into the current relationship they have in place with Miles Technology. Miles Technologies INVision Software is used to interface the Motorola's handheld mobile computers to Azteca Cityworks StoreRoom Module. This partnership is unique and provides the Mobile Inventory Management System offering exclusive to Cityworks, which is the only experienced implanter of the Mobile Inventory Management System that integrates with Cityworks Storeroom. This system has been installed in the maintenance warehouse of O'Hare Airport in Chicago and it has increased the efficiency and reliability of the asset management system. This purchase will obligate the City to continue using Cityworks and the Mobile Inventory Management System from Azteca System Inc. and pay our yearly maintenance fee. The City's site license allows for unlimited amount of users of the software. If the City would decide purchasing other available software modules, these modules will have to be purchased from Azteca Systems, Inc. and/or Motorola Solutions. Sole Source Justification : After an in-depth investigation of the business needs the GIS Manager together with the Executive Director Enterprise Solutions, Mr. George Mastakas of Azteca, Inc. created the technical specification and presented these specifications to Motorola Solutions, Inc only. Based on these specifications Motorola Solutions, Inc. presented the Cityworks Mobile Inventory Management Implementation Proposal. The City's GIS Manager and Azteca Systems Inc. compared this proposal to other systems on price and capabilities. Since no other system exists with the unique integration of this system, a full comparison analysis could not be done, however, it was concluded that the price for this system is fair and reasonable. Since January 27, 2011, the date the first budgetary proposal was received, the GIS Manager had several conversations and e-mail exchanges with the Enterprise Asset Management (EAM) Product Manager from Motorola Solutions, Inc to discuss the total cost of the proposal and the different cost components of the total solution. With the use of the Miami Beach WIFI network and development support of the GIS development 2 257 Commission Memorandum -Cityworks Mobile Inventory Management System Sole Source July 18, 2012 Page 3 staff, we were able to contain the cost of the system under the available budget for this project. The City's internal Auditor reports indicated that the current process to issue materials in the Public Works Operations Warehouse had some major problems. To illustrate, issued materials are not directly entered into the system, but recorded on a paper form. The data from this form is later entered into our Computerized Maintenance Management System (CMMS) Cityworks; this process can lead to omissions and errors. Please refer to the "automation of forms" in findings 1 and 2 of the enclosed Audit Report. The Mobile Inventory Management System will solve these problems by bringing the data entry device and bar coding to the location of the materials. The total inventory of Public Works Operations Warehouse is on an average $2,600,000.00 with a total of $1,100,000.00 materials issued to work orders in a year. Motorola Solutions Inc.'s proposal includes the lnvision for Cityworks Site License, On- site and Off-site Implementation Services, purchase of six (6) Motorola Solution MC9090 Mobile Handheld Computer devices, One (1) Bar Code Printer, Wireless Infrastructure Hardware, and three (3) years of annual support and maintenance. The total amount including the three (3) years of support and maintenance is $137,067.00. Please see attached Motorola Solutions Inc.'s proposal and letters. CONCLUSION The Administration recommends that the Mayor and City Commission of the City of Miami Beach, Florida, adopt the attached resolution, herein approving pursuant to Section 2-367 (d) of the Miami Beach City code, the sole source purchase of a Mobile Inventory Management System from Motorola Solutions Inc, which integrates with Cityworks, in the estimated total amount of $137,067. T:\AGENDA\2012\7-18-12\Mobile Inventory Management System-Memo 1a final.docx 3 258 RESOLUTION NO. ----- A RESOLUTION OF THE MAYOR AND CITY COMMISSION OF THE CITY OF MIAMI BEACH, FLORIDA APPROVING, PURSUANT TO SECTION .2- 367 (d) OF THE MIAMI BEACH CITY CODE, THE SOLE SOURCE PURCHASE OF A MOBILE INVENTORY MANAGEMENT SYSTEM FROM MOTOROLA SOLUTIONS INC, WHICH INTEGRATES WITH CITYWORKS, IN THE ESTIMATED TOTAL AMOUNT OF $137,067. WHEREAS, the implementation of Cityworks from Azteca Systems, Inc. was part of the original proposal for an Infrastructure Management System (IMS) in the Public Works Department, which included a Computerized Maintenance Management System (CMMS) integrated with a Geographic Information System (GIS). A Request for Proposals (RFP) was issued in 2004; and WHEREAS, the contract for the development of the CMMS portion of the project was awarded to Camper Dresser, McKee, Inc. (CDM) in 2005. Cityworks from Azteca was selected to be the best software system based on this RFP; Cityworks software was purchased for the City as part of the CMMS project; and WHEREAS, Cityworks is operational and has been in use by the following Public Works' Divisions: Water, Storm Water, Sewer, Streets, Street Lights, Sanitation and GIS since May 2006, and has been in use in Engineering since June 201 0; and WHEREAS, . Cityworks has fulfilled the Public Works Department's requirements for the past five (5) years. Recently, Cityworks was successfully implemented for the Convention Center, and is currently being implemented for the Property Management Division of Public Works; and WHEREAS, Motorola Solutions has represented the Cityworks product over nine (9) years. Motorola Solutions commitment to the product has allowed the establishment of a strong relationship with Azteca Systems, and that success has been expanded into the current relationship they have in place with Miles Technology; and WHEREAS, Miles Technologies INVision Software is used to interface the Motorola's handheld mobile computers to Azteca Cityworks StoreRoom Module; and WHEREAS, this partnership is .unique and provides the Mobile Inventory Management System offering exclusive to Cityworks, which is the only experienced implanter of the Mobile Inventory Management System that integrates with Cityworks Storeroom. WHEREAS, the Mobile Inventory Management System will solve these problems by bringing the data entry device and bar coding to the location of the materials; the total inventory of Public Works Operations Warehouse is on an average $2,600,000.00 with a total of $1,100,000.00 materials issued to work orders in a year; and WHEREAS, Motorola Solutions Inc.'s proposal includes the lnvision for Cityworks Site License, On-site and Off-site Implementation Services, purchase of six (6) Motorola Solution MC9090 Mobile Handheld Computer devices, One (1) Bar Code Printer, Wireless 259 Infrastructure Hardware, and three (3) years of annual support and maintenance. The total amount including the three (3) years of support and maintenance is $137,067.00; and NOW, THEREFORE, BE IT RESOLVED BY THE MAYOR AND CITY COMMISSION OF THE CITY OF MIAMI BEACH, FLORIDA, that the Mayor and City Commission hereby accept the recommendation of the City Manager approving pursuant to Section 2-367 (d) of the Miami Beach City code, the sole source purchase of a Mobile Inventory Management System from Motorola Solutions Inc, which integrates with Cityworks, in the estimated total amount of $137,067. PASSED AND ADOPTED THIS ___ DAY OF ____ 2012. ATTEST: CITY CLERK MAYOR T:\AGENDA\2012\7-18-12\Mobile Inventory Sole Source-Resolution.doc 260 CITYWORKS MOBILE INVENTORY MANAGEMENT IMPLEMENTATION PROPOSAL FOR THE CITY OF MIAMI BEACH, FL March 22, 2012 The design, technical, and cost information furnished with this proposal is to be considered proprietary information of Motorola Solutions, Inc. Such information is submitted with the restriction that it is to be used only for the evaluation of the Proposal, and is not to be disclosed publicly or in any manner to anyone other than those required to evaluate the Proposal without the express written permission of Motorola Solutions, Inc. MOTOROLA, MOTOROLA SOLUTIONS, MOTO and the Stylized M Logo are registered in the U.S. Patent & Trademark Office. All other product or service names are the property of their respective owners. ©MOTOROLA SOLUTIONS. Inc. 2012 261 • MO.,.OROLA SOLUTIONS March 22,2012 Bert Dorrestyn GIS Manager, Public Works Department City of Miami Beach 1700 Convention Center Drive Miami Beach, FL 33139 Tel: 305-673-7000 Ext 6347 Subject: Mobile Inventory Management Proposal for the City of Miami Beach. Dear Mr. Dorrestyn: Motorola Solutions is pleased to present the City of Miami Beach the following proposal for the expansion of Cityworks Storeroom to including mobile bar-coding technology into the warehouse. Cityworks is the market leading GIS-based asset and work order management system, with a fully integrated inventory management module, deployed in over four hundred (400) unique customer locations worldwide. The implementation ofMotorola's Mobile Inventory Management with bar-coding technology will offer improved efficiencies for materials management and work order management within the City's warehouse operations. Motorola has represented the Cityworks product for over nine years. Our commitment to the product has allowed us to establish a strong relationship with Azteca Systems, and that success has been expanded into the current relationship we have in place with Miles Technologies. These partnerships are unique in that they come together to provide the Mobile Inventory Management offering exclusive to Cityworks that is described within this proposal. As part of these relationships, Motorola remains as ihe only authorized reseller of Cityworks software and to date is the only experienced implementer of the Mobile Inventory Management system that integrates with Cityworks Storeroom. We invite you to review the content of the Mobile Inventory Management and bar-coding proposal and look forward to discussing the details of it with you in the near future. This proposal is being submitted on a firm-fixed price basis and is subject to the terms and conditions ofMotorola Solutions' existing contract (number BW8127-5/12-0TR) in place with Miami Dade County. This Motorola Solutions proposal is valid for sixty (60) days. If you have any questions regarding this proposal, please contact Jeff Cegielski, 262-253-1416, or at jeff.cegielski@motorolasolutions.com. Sincerely, ~ Motorola SolM Inc. ~d: ~ Director, Advanced Solutions Team Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 262 1 ~MOTOROLASOLUTIONS BACKGROUND Miami Beach has an enterprise-wide labor and materials tracking solution called Cityworks, created by Azteca Systems. While Cityworks handles the material details, it does not offer an out-of-the-box wireless solution for inventory management. INVision for Cityworks is a wireless Mobile Inventory Control solution that bridges that gap and enables Miami Beach to use barcode scanning and real-time transaction and materials tracking in their warehouse. The INVision developers and Motorola Solutions have partnered up to create the INVision for Cityworks solution to add mobile wireless real-time inventory control to Cityworks' Storeroom functionality. INVision for Cityworks has been fully implemented and successfully deployed at the second largest airport facility in the U.S. by the City of Chicago Department of Aviation. Cityworks will track the PO's, the receipts, the transfers, the requisitions, and the labor for the work orders they apply to while the Storeroom application will act as a repository for the available inventory within Cityworks. INVision will enable Cityworks to maintain an accurate inventory of what is in the warehouse through barcode scanning to enable real-time posting of Purchase Order receipts, Material Requisition issues to persons or Work Orders, and Transfers of materials within the warehouse or from Warehouse to satellite Storerooms, as the transactions occur. It will also enable a free- standing cycle and physical count process to maintain inventory accuracy. INVISION SOFTWARE INTRODUCTION INVision is a real-time, Windows-based, inventory control system that addresses all of the commonly used functions in a typical warehouse environment. It incorporates the latest in wireless data collection technologies and a powerful PC database to provide timely and accurate inventory data. While INVision was created to manage the complexities of inventory control, it was designed to make those tasks very simple. INVision is a state-of-the-art, feature-rich Inventory Control Solution that is priced to be the most affordable solution in the industry. With a comprehensible GUI interface for PC workstations and an easy-to-use prompt-and- response interface for wireless data collection terminals, INVision was designed to manage and record all inventory stock movements, such as Receiving, Put Away, Moves and Picks. The system also includes Locator and Cycle Counting functionality and provides historical ~and real- time reporting for all of these transactions. INVision makes use ofboth wireless data collection terminals and PC workstations. Warehouse transactions are performed on wireless handheld terminals, and data queries, reporting and adjustments are managed on PC workstations. INVision will provide the visibility and inventory accuracy required to make informed business decisions. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 263 • MOTOROLA SOLUTIONS The INVision system is comprised of three major components: • INVision Client (Workstation PC App) • INVision Server (Database, RF Communication Server, Integration Server Software) • Radio Frequency Terminals Wireless· Devices INVlsion Server Host System Access Point Client PC Client PC Client PC Client PC Client PC Client PC's are used to run the INVision Desktop Client program. From this program, a user can lookup inventory, modify system settings, run reports, and adjust quantities. This part of the application was designed to allow multiple users to query the same database. Each client PC must be connected to the network to allow the program to function properly. 2 The INVision server has multiple responsibilities. First, the INVision database will reside on this machine, allowing multiple RF and Client programs to query for results. Second, the INVision RF Communications Program allows you to configure handheld behavior, monitor RF activity, and ensure database connectivity from each of the handheld devices. Finally, their may also be an INVision Server program which is responsible for importing and exporting to your host system. The Radio Frequency (RF) terminals. These devices run the INVision mobile application. This application or program allows users to Receive, Put Away, Pick, Ship, Move, Count, and Lookup inventory from tlip warehouse floor via radio connection. Although the devices are not physically connected to your network, the RF connection allows these devices to update your database in real-time, giving the Desktop Client program instant and real inventory results. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 264 3 • MOTOROLA SOLUTIONS SCOPE OF WORK The intent of this section is to initially identify the responsibilities of Motorola Solutions and the City regarding the scope of work, deliverables and schedule of the project described herein. This Scope of Work describes the work for professional services to be performed by Motorola Solutions for the City of Miami Beach (Customer). It delineates responsibilities between Motorola Solutions and the Customer as agreed to by the contract. Services shall be provided in accordance with a mutually agreed to consulting timeframe. The final Statement of Work, project timeframe, cost, and starting date are contingent on agreement from both parties. Motorola Solutions shall provide on-site Business Systems Analyst consulting time to the City of Miami Beach for deployment of the Mobile Inventory Management system. The on-site consulting must take place in consecutive weeks. Details of the type of work to be performed can be found under "Anticipated Project Tasks". GENERAL REQUIREMENTS Motorola Solutions and the Customer will each provide a single point of contact for all administrative, technical, and scheduling issues related to the project. MOTOROLA'S PROJECT RESPONSIBILITIES (GENERAL) ~ Provide a single point of contact responsible for managing all Motorola Solutions activities, deliverables, and schedule during the period of the contract, ~ Schedule the delivery of professional services consulting in agreement with the Customer, ~ Work with the Customer Project Manager in the resolution of project issues, ~ Motorola Solutions will provide a summary of the hours expended on the project, ~ Maintain and report overall project action items for Motorola Solutions and Customer required activities, and ~ Bring Azteca Systems in to review the fmal configuration. CITY OF MIAMJ BEACH PROJECT RESPONSIBILITIES (GENERAL) The City of Miami Beach shall be responsible for the performance of all equipment and software provided by the Customer for use by Motorola Solutions in the performance of this contract. Customer's responsibilities on this project are further defined in the SOW. If the Customer Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 265 "' MOTOROLA SOLUTIONS employs an outsourced IT vendor to perform certain Customer identified responsibilities, the Customer is responsible for managing the outsourced IT vendor and assuring delivery according to the project plan. Customer's general responsibilities include the following: ~ Provide a customer Project Manager as a primary point of contact, ~ Provide access to key personnel in a timely manner, ~ Provide access to facilities and equipment and, ~ Provide timely responses to Motorola Solutions requests for information necessary for the performance of this contract, ~ Cooperate in the scheduling any system changes so as to minimize the downtime of the system and the impact to the Customer, ~ Support a safe work environment for all activities, ~ Work with the Motorola Solutions point of contact in the resolution of project issues, ~ Participate in and support overall project scheduling, ~ Be responsible for timely execution of Customer responsibilities as defmed in this SOW, ~ Provide all required IT infrastructure to include servers, Oracle or SQL Server and ESRI ~ Other specific tasks as defined in this SOW. PROJECT TASKS 4 "Out-of-the-box", the baseline INVision inventory control software is designed for integration with host system that supplies the Purchase Order and Sales Order information, and processes the receipts and issues acknowledgements that INVision provides. INVision retains the knowledge of precisely where in the warehouse all inventories are located, and the host system is only updated with information pertaining to receipts and issues. Originally, to simplifY the implementation costs, streamline deployment, and avoid feature- changes to the baseline system, INVision , as proposed, will be implemented at Miami Beach with this baseline functionality for the desktop application and device screens, with only the receipts/issues interface with Cityworks as "CUSTOM" development. • INVision RF Client and Desktop will be used for searching, cycle counting and maintaining the physical inventory in the Warehouse, and in the Storerooms (when applicable). .... • All inventory transactions in INVision are reflected real-time in Cityworks, and historically recorded there. • Cityworks is the interface for performing Inventory Adjustments and Work Order posting (and back-flushing the Storeroom inventories in Cityworks and INVision for the Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 266 • MOTOROLA SOLUTIONS materials consumed "on the job"). Any such adjustments in Cityworks will be reflected near-real-time in INVision. 5 • All Purchase Orders are maintained by and within Cityworks; INVision validates receipts against those PO's and reports the receipts back to Cityworks for auditing. • Cityworks will be the primary reporting interface for the warehouse data for those associates and tradesmen of Miami Beach who do not work in the warehouses. INVISION INTEGRATION There are two primary areas of system design changes between INVision "baseline" and "INVision for Cityworks." The first involves the multiple kinds of orders that INVision system needs to support in conjunction with Cityworks, and the necessity to post all inventory moves and adjustments from INVision to Cityworks. Cityworks and INVision are both built on SQL databases, so sharing data between the two systems is a matter of utilizing queries, stored procedures, views, triggers, and SOAP methods to ensure that all inventory transactions and information is bi-directionally shared (when applicable). For example, adds and changes to the MATERIALLEAF table in Cityworks should trigger an insert or update into the ItemMaster table in INVision. This integration is subject to the limitations of the ODBC links between the platforms; Cityworks SQL services supported by Oracle, INVision is supported by MS-SQL. Communications between the two systems is managed by an INVision Data Server application that manages the required bidirectional polling. Receipts Cityworks has record ofPurchase Order Requisitions (POReq) which are be "exposed" for the INVision system to query against when performing a Receiving Transaction in the Main Warehouse. When materials are received in the main Warehouse though INVision, the receipts will be posted against the POReq in Cityworks. Cityworks' Storeroom will be updated with the additional stock quantities for the Warehouse/Storeroom for the default Bin Location for that item. All receipts will be put away by Part Number to random Bin Locations in the Warehouse in INVision. Moves Moves performed using INVision within the warehouse from one bin location to another will be reflected as a Transaction in Cityworks, but the Cityworks STORERMSTOCK tables will not be updated because there is no net change in material quantity for an intra-warehouse move. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 267 6 • MOTOROLA SOLUTION$ Transfers When material is transferred from the Warehouse to a Storeroom using INVision, the inventory will automatically transfer within INVision to the default Bin Location for that storeroom as defmed in the Cityworks STORERMSTOCK table, and will also post the transfer transaction for Cityworks to make a parallel update. Issues INVision has a transaction called ISSUE which enables INVision to capture the transfer of materials to a Person (as defined as a Cityworks EMPNAME), and/or to a Work Order (explicitly defmed in Cityworks) directly from the warehouse or storeroom, based on a Manual Material Requisition form. The MaterialCostiD for this transaction to be posted against will have to be audited by the Work Management Center via the submitted paper work after the fact. Issues do not have a "bill of materials" (BOM) to validate against until the material is actually pulled. So all issues are being "recorded" as the materials are being pulled for the issue; inventory is not decremented in INVision or posted to Cityworks until the Issue is "closed" as completed in INVision-any items can be returned to stock before closure via the Put-Away function after the items has been "rejected" instead of issued. Work Order Back Flush The inventory quantities in INVision and Cityworks would remain up-to-date ifiNVision's ISSUE transaction was used to post ALL material issues, including Work Orders. However, this would require a serious change in workflow for the Trades, the Work Management Center, and for the Warehouse. Work Order issues will be the exception and not the rule. Currently all issues are material issues to a Person, not a Work Order. When posting a Materials Issue in Cityworks (for a Work Order or to an individual) against the inventory in a storeroom, this should decrement the inventory in the Cityworks STORERMSTOCK table and trigger a parallel decrement of the INVision Inventory table for the Part Number at the Bin Location recorded in Cityworks STORERMSTOC via an XML Document transaction posted through integration. If INVision has only one bin location for that item in that store room, the back flush will result in identical and accurate inventory records in both INVision and Cityworks; if the inventory is stored in more than one location in INVision and the inventory was not pulled from the Primary Location (which is specific to each warehouse), then the back flush will incorrectly decrement the entire issued quantity from the storeroom's prilrtt!.ry location and possibly drive the inventory negative for that bin location--however, the aggregate quantity of that part number in the warehouse should match exactly with Cityworks Storeroom qty for that part number in the warehouse. Any discrepancies in INVision will be cleared up during cycle counting and reconciliation via moves to correct the INVision variance reports. Any Adjustments required to account for overall discrepancies between the aggregate physical count and the aggregate Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 268 • MOTOROLA SOLUTIONS recorded inventory will need to be manually adjusted in Cityworks; such adjustments to the material quantities in Cityworks' Storeroom will be reflected in a parallel adjustment in JNVision for the inventory count primary location for the storeroom via the XML integration. Sales Orders 7 INVision has existing functionality to support a sales order process for shipping inventory out of the warehouse to an end-user customer. This functionality is useful to a distribution center, but is not applicable to the warehouse operations via Cityworks, and is suppressed and not available in the lNVision for Cityworks. IMPLEMENTATION PLAN PHASE I: START-UP JNVision for Cityworks may be run in a stand-alone fashion, where the POR's and Issues are handled in a "blind" manner, where they are recorded and tracked in lNVision, but not validated or posted against the host system. This enables JNVision to build and maintain an accurate inventory separate from any host system. It lacks the richness of the full product, but is sufficient to get the warehouse operations up and running. This is also used to test the implementation prior to completing the integration hookups with Cityworks. In order for Cityworks to function it needs, as a baseline, current, complete and accurate inventory information for the warehouse. Until they have accurate inventory information, they cannot even ''turn the key" to get the Cityworks system started. This will require the Cityworks developers to supply a one-time (or reproducible) export ofthe Item, Warehouse and Location master files from the existing databases to set the baseline. The warehouse users will then need to perform a complete physical count of all materials in the warehouse, Audit the inventory using lNVision' s Variance Report and perform any adjustments required in Cityworks to handle the discrepancies. Then Miami Beach can begin using JNVision to track all of their receipts and issues and moves into, within and out of the warehouse. PHASE II: INVISJON AND CITYWORKS Once the inventory information is deemed accurate The JNVision Implementers will return and assist Motorola with transferring a copy of the current inventory information into Cityworks, and then apply the integration tools to link up JNVision and Cityworks via the issues/receipts transactions, so that all Work Orders and Purchase Orders in Cityworks will be visible and validated against in lNVision. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 269 • MOTOROLA SOLUTION$ Alternatively, Cityworks could export the current inventory-by-location information (if it is available), and Miami Beach's main warehouse could begin using INVision in for inventory control, using cycle-counting over a periods of weeks, and gradually work towards acceptable inventory accuracy. All transactions would be performed using INVision, but the inventory would NOT be accurate at the beginning. 8 At this point in time, Cityworks and INVision will be integrated, and in such a manner that either system could be temporarily shut down for maintenance without causing undue or unacceptable harm to either system .... Files and transactions would just queue up until such time as the shut- down was ended and normal processing would occur. This can be implemented using XML transfers via stored procedure calls to INVision from Cityworks. SYSTEM COMPONENTS: Hardware: Software: Applications: Implementation Services: Pre-Req's: WinServer or a dedicated WinXPPro Applications Server Motorola /Symbol WLAN 6 x Motorola I Symbol MC909x Mobile Computers MCL-Net-10 6 x MCL-Clients Seagull Scientific's BarTender Enterprise Automation (BT-EA-3) INVision RF Device Application (s) (MCL Project) INVision Desktop Client Custom Integration with Cityworks System Requirements Defmition System Integration, Implementation, Installation and Training SQL Server Cityworks Developer Cooperation Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 270 9 • MOTOROLA SOLUTIONS PROJECT OVERVIEW AND SCOPE RF-WIRELESS NETIVORK Miami Beach has a municipal WIFI network in place that may supply sufficient wireless coverage for this application. However, an infrastructure specific to the warehouse may be required for to ensure proper implementation of the mobile computing system in the warehouse. Regardless, a cursory RF Site Survey is required to validate the existing wireless infrastructure to assure proper 802.11 b/g wireless coverage. (The INVision Implementers is not responsible for cell phone coverage-see the cellular network provider.) The INVision lmplementers will perform this as an implementation step to assure that the warehouse has complete RF coverage, and to alert Miami Beach of any dead spots or holes in the coverage. Any additional wireless infrastructure or services will be quoted separately. WIRELESS INvENTORY CONTROL SOFTWARE • INVision -Deliver, install, site specifically configure, test and load customer data into the INVision Inventory Control software on Miami Beach's supplied server. Install, configure and test up to 3 Windows 2000 or above client PC's. • Cityworks Integration -Install, configure and test bi-directional transactions between INVision and Cityworks using SQL and SOAP-based methods. • Seagull Scientific's BarTender-Deliver, install, configure and test BarTender Label Printing Software for customer, for "Part Number" label automation at receiving. PROFESSIONAL SERVICES • Project Management Services -The scope of this project includes all the services related to successfully implementing INVision in Miami Beach's warehouse operations. This includes system requirements definition (SRD), solution engineering, project kick off meeting, site specific configuration, data base loading, testing, system certification, resource coordination, training, process automation consulting, and on-going support. • Cityworks Integration -Install, configure and test bi-directional transactions between INVision and Cityworks using SQL and SOAP-based methodologies. HARDWARE • Included in the scope of this project is the recommendation of wireless bar code printers, related labeling stock and supplies, scanners and wireless terminals for use with INVision for Cityworks. Also included is the delivery, set up, configuration of Bar Code Printers, Scanners, Mobile Computers, and Wireless Network components. • Please note that hardware provided under this proposal will support current industry standards for wired Ethernet using CATS cabling and Wireless 802.11 b/g standards. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 271 10 • MOTOROLA SOLUTIONS IMPLEMENTATION STEPS There are four steps to the basic INVision for Cityworks implementation plan: 1. System Requirement Definition (SRD) -is a critical step in a successful implementation. The Project Leader will detail the software configuration requirements, data flow with other systems involved and RF site survey for layout of the Wireless network to be used in the inventory control process, as a part of the SRD. This also details how the integration between INVision and Cityworks will occur. (Includes certification of customer supplied infrastructure such as servers, PC's, network and software component such as MS SQL Server.) This process includes a Gap Analysis to ensure that INVision for Cityworks will meet the functional needs for warehouse inventory control for Miami Beach. 2. Project Kick Off Meeting will be established with leaders and qualified participants Miami Beach, and Motorola's INVision for Cityworks implementers. The meeting will focus on tasks, responsibilities, milestones with associated dates for the project. 3. Coordination of all activities; warehouse prep, any setup and or process improvements, IT infrastructure coordination, installation, testing, training, certification and sign-off. 4. System Live a. Standalone INVision implementation phase. On the cut over day, your INVision Project Leader will be on-site the first day the system goes live, to assist with learning curve and system performance issues. b. Cityworks Integration implementation phase. On the cut over day, your INVision Project Leader will be on-site the first day the system goes live, to assist with learning curve, integration, and system performance issues. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 272 11 • MOTOROLA SOLUTIONS ASSUMPTIONS AND CUSTOMER RESPONSIBILITIES The following are issues that must be addressed by Miami Beach: SYSTEM INFRASTRUCTURE It is the responsibility of the customer to acquire all the additional hardware, software (including MS SQL Server and client licenses) and network components required by the INVision Inventory Control Solution. PC clients must be MS Windows 2000 or above and free of defects or operational anomalies as The INVision Implementers cannot be responsible for reconfiguring or reloading OS and end user applications. INVISION SERVER REQUIREMENTS A server running Windows 2003 (or higher) is required for INVision and the supporting utilities. This PC should be dedicated to INVision with the following minimum hardware profile; Pentium 4 processor, 256MB of system RAM, lOGBytes ofhard disk, CD, Fast Ethernet Network adapter, Super VGA graphics and Monitor, Mouse, Keyboard, Modem and Microsoft Office 2007/XP. Please note that a tape back up system is not included, it is assumed that the IT group (not Motorola) will be responsible for backup and recovery procedures with their existing processes. Please check with IT to make sure that this can be done and that they have the 'open database' tape backup agents, which allows for backup of the database while it is open or being used. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 273 12 • MOTOROLA SOLUTIONS HOST AND NETWORK INTERFACE From time to time the project team will need to consult with the organization or persons responsible for setting up the customer's host system and IT infrastructure. The customer is responsible for providing access to these resources. Additionally, the INVision implementers request 'supervisor-level' network access security clearance or the customer must provide equivalent support to the INVision implementers during installation and all site work. PROJECT ASSUMPTIONS: );;.> An 802.11 alb/g Wireless roaming-supporting infrastructure with complete and necessary coverage is required to support this project. Quantities and configurations presented above are for estimates, and may change based upon the results of the RF Site Survey, and assume coverage requirements for a small warehouse. );;.> The City will have all hardware available and loaded with any necessary software required for Cityworks and/or Storeroom. );;.> The consulting will take place within an eight (8) week period. );;.> The City will work with Motorola Solutions to determine a schedule for the project and will give a minimum of three weeks advance notice for all work to be performed. Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 274 13 • MOTOROLA SOLUTIONS PRICING TABLE Component Units Description Price INVision for Site License INVision for Cityworks (Site License)--The fully $25,000.00 Cityworks Customized MCL-RF Client with INVision Desktop and Seagull's BT -EA-3 On-site On-site implementation services for INVision $37,000.00 Implementation implementation including: Services • SRD • RF coverage validation • Installation • Training • Travel Off-site Off-site implementation services for INVision $ 18,800.00 Implementation implementation including: Services • Development of bidirectional interface • Testing Annual Support Three (3) Annual maintenance agreement for INVision $ 9,600.00 and Years software. Support includes software upgrades, Maintenance technical phone support and bug fixes. Agreement Motorola Six (6) Six (6) Motorola Solutions MC9090 Mobile $28,667.00 Solutions Motorola Handheld Computers Including: MC9090 Mobile Solutions • 3-year bronze level service Handheld MC9090 • Spare Battery Computers devices • Cradle Kit 0 Cables INVision software installed and prepared to be configured for use with Cityworks Storeroom BarCode One (1) Bar Zebra 1 05SL Printer w/ Internal Print Server and $ 3,000.00 Printer Code Printer US Line Cord Wireless Wireless Infrastructure Hardware and on-site $ 15,000.00 Infrastructure Services to include: • Site Survey --• Wireless 802.11 Switch " • Access Port • Antennas • Installation Services • Travel Total Mobile Inventory Management Implementation Proposal $ 137,067.00 Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 275 14 • MOTOROLA SOLUTIONS PAYMENT SCHEDULE The following payment schedule describes three (3) payment milestones. Payment is due Net 30 days from the date of invoice. 1 Contract Signing TBD $35,000.00 2 Software Delivery TBD $35,000.00 3 On-Site Hardware and TBD Infrastructure Installation $67,067.00 Total 100% Use or disclosure of this proposal is subject to the restrictions on the title page. Motorola Solutions Confidential Proprietary 276 • MOTOROLA SOLUTIONS May 10,2012 Patricia Walker Chief Financial Officer City of Miami Beach 1700 Convention Center Drive Miami Beach, Fl 33139 Dear Ms. Walker: Azteca systems, Inc. provides an Enterprise-GIS asset maintenance management system, Cityworks to The City of Miami Beach. Cityworks is the market leading GIS-based asset and work order management system, with a fully integrated inventory management module, deployed in over four hundred ( 400) unique customer locations worldwide. The implementation of Motorola's Mobile Inventory Management with bar-coding technology will offer improved efficiencies for materials management and work order management within the City's warehouse operations. Motorola has represented the Cityworks product for over nine years. Our commitment to the product has allowed us to establish a strong relationship with Azteca Systems, and that success has been expanded into the current relationship we have in place with Miles Technologies. These partnerships are unique in that they come together to provide the Mobile Inventory Management offering exclusive to Cityworks. Motorola is the only implementer of the Mobile Inventory Management system that integrates with Cityworks Storeroom. Please feel free to contact Jeff Cegielski at 262-253-1416 or jeff.cegielski@motorolasolutions.com if you have any questions or need any additional information. Regards, ot la Solutions, Inc. John Thompson -MSSSI Vice President Motorola Solutions Confidential Proprietary 277 May 9, 2012 Patricia Walker Chief Financial Officer City of Miami Beach 1700 Convention Center Drive Miami Beach, Fl 33139 Dear Ms. Walker, Azteca Systems, Inc. 11075 S. State Street Suite 24 Sandy, Utah 84070 (801) 523-2751 FAX: (801) 523-3734 www.citvworks.com Azteca systems, Inc. provides an Enterprise-GIS asset maintenance management system, Cityworks to the City of Miami Beach. As with all software applications developed by Azteca Systems, Inc., Azteca retains ownership of the copyright and trademark of such software and is in the sole source provider and licensor ofthe software and maintenance services. The City of Miami Beach has been a licensed user of Cityworks since May 1, 2006. Your license includes an add-on module named Cityworks Storeroom for inventory management. This module is a desktop application and to enhance its use we have partnered with Motorola, who is the only provider of Azteca approved software that enables users of Cityworks Storeroom, to perform inventory tasks through wireless mobile devices using bar-coding technology. Feel free to contact me with any questions. Regards, -George George M. Mastakas-VP, Enterprise Solutions Azteca Systems Inc. 11075 South State Street, #24 Sandy, UT 84070 main: 801.523.2751 fax: 801.523.3734 georgem@cityworks.com www.cityworks.com 278 & -~·:: ;_ .. . ' M_-lA-------M--_I B. ___ -_---E---___ Ac· • •H----_ 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 Prer:zance Improvement Director James J. Sutter, Internal Audito~ 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 at451 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 are commllled to providing excellent public se!Vice and safety to all who live, work. one/ ploy in our vibrant, tropical, histo1ic community. 279 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 51 Street and Hawthorne Avenue. Examples of inventory stored in these facilities are selected bends, sleeves, light poles and concrete bases. Page 2 of 8 280 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 281 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 282 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 201 0. 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 Z2580 dated 03/27/09 listed a $.30 less unit cost than entered into Cityworks for propane. MX-2 number 22584 dated 04/10/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 283 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 ofthe 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 284 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 senttothevendorfor 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 10/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 285 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-041 0-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\DOCOB-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 286