Requirement: We needed to update 20+ year old non-integrated multifunctional legacy system.
Background / problem: On January 1, 2000, I assumed the management of Support Services for the City of Alcoa, TN. The responsibilities were primarily Purchasing & Fleet Management for the city:
- Fleet Management: Then – 225 rolling stock – vehicles & fueled equipment; Now – 325 vehicles & fueled equipment
- Purchasing – City wide functional oversight for all purchases over $1K
- Warehouse management (Then – 900 + items of Inventory – $150K -$200K there was an unknown amount of obsolete material, monthly warehouse “sales” averaged about $15-$20k/month) – (Now – 1,969 items of Inventory – 2009 our inventory is valued at almost $400K of inventory and a monthly roll-over of approximately $60K per month.)
I was charged with bringing automation & general management efficiencies to all three functional areas of Support Services but I only had about $10K to make something happen. High on the list of needs were the modernization of existing Management Information System (MIS) capabilities for all three functional areas – interrelated if possible but also maintaining all Financial & General Ledger (GL) functionality of the Legacy System (LS) as well. Although the multi-module LS had been in-place for years, its functionality and architecture severely limited data quarries between modules. Other important data capture and inquiry routines either did not exist within our older systems architecture, or had never been supported by the city. Important but simple identification protocols – in affect our low-hanging fruit – involved our poorly supported warehouse management and stores reordering requirements as well as a very paper intensive preventive maintenance program updated only monthly. The city had gone to the expense of installing an automated Card refueling management system but was not even using it to its maximum potential. Although it collected fueling transactions daily it only submitted its data to the LS’s sub-routines monthly which supported even more labor intensive batch PM management processes that burned up limited staff resources far in excess of judged requirements.
Warehouse Purchases were not supported with an integrated Purchase Order system that allowed Support Services to either, segregate city wide departmental purchases of non-fleet management stock, or integrate FM stores with all other Fleet Management work order / repair order activities.
Encumbrances against the GL would only become known at the end of the month when all transactions were summarized and transferred from the warehouse module to the GL, the Fleet Management module worked the same way. In fact the Fleet Management module did not list current charges for either labor or parts until end of the month accounting routines were completed and even then they were not sufficiently detailed enough to be of much use.
Historical purchases (quantities & prices) from venders were generally supported by the GL but they lacked record detail and were not easily retrievable.
Inventory reorder points were not kept and utilization and financial trends could only be traced through hard copy records.
Data access routines were hierarchical in nature and required multi-level commands to access data files. All data files were supported with a 10-digit budget/fund codes. This resulted in very limited reporting flexibility – system-wide. Commands required the user to back out and reenter the same series of commands for data residing in each required fund. Reports then had to be concatenated by a limited number of independent variables to identify trends.
Labor intensive monthly close out routines required 6-8 straight man-hours of work just to package all monthly transactions – by departmental fund.
Inventory sales could be tracked WITHIN the Inventory module but monthly sales totals were not visible to the GL until after month end close-out routines were completed. Departments were blind to use and cost trends during any calendar month.
Because there was no viable data link between the purchasing/warehouse and fleet management database modules, retrieval of valuable historical work-order / repair order detail was never accessed. Therefore, routine shop operations such as normal preventative maintenance routines were not supported. Worse, readably accessible fuel data from the automated fuel control system only supported vehicle dollar costs. It did not capture corresponding mileage data. Simply stated, the shop never knew when it was time to perform the next maintenance routine and as a result all PM’s were recalled by the calendar and not by actual miles driven. Because warehouse inventories were likewise not encumbered, reorder sequencing and volume purchasing initiatives were not identifiable and therefore available market discount opportunities and bulk purchasing initiatives were lost. , and on hand inventories were subject to great fluctuations in availability.
Summary – The City had 2 very important but divergent information systems operating within the same unit of government, with neither readily sharing data between them. Operational efficiencies were limited at best and rendered dead in the water at worst.
In the spring of 2000 I began a search for an off the shelf software that would do all three, purchasing, warehouse and fleet management and one that would integrate them seamlessly. Although I did find purchasing and warehouse systems that talked to the GL – and many systems that had inventory as a common thread, I could not find any off the shelf systems that met all of our needs in both operational areas. In addition, the costs for the systems were quite expensive (tens to hundreds of thousands of dollars). I really needed to find a single system that allowed the creation and management of multiple inventories.
As a result I found the Fleet Management DOSSIER32 MIS and by working with the manufacturer as a Beta test site, we were able to install a fleet management system with a few design enhancements and a lot of patience by the software manufacture that allowed me to find ways to incorporate many of our purchasing and warehouse requirements into a fully functional Fleet MIS. We are now coming up on 10 years in its use where the city continues to significantly benefit from those original unique and shared capabilities – and all the others that have been introduced since: Such as:
The system has ability to merge unlimited parts and operational inventories such that they support the multi divisional requirements of the cities Support Services. Specifically, the system supports an inventory operations that includes up to 324 pieces of rolling stock, an interactive fuel data system, and currently 1969 warehoused parts (from filters for vehicles, to stainless steel tapping valves for the water department, to small boxes of tide and fire extinguishers for the fire department.) And, with a completely independent data identification and reorder system, a modern bar code stocking support system, and a fully integrated and E-compatible purchase order capability throughout.
Inventories are now combined with the fleet operations to plan the parts needed for service of the vehicles, and by recording the current mileage of the vehicles, forecast when the vehicles will need service. I am now able to submit annual “Blanket” purchase requirements for hundreds of unique stock items to many multiples of vendors. Because of the strength of the DOSSIER system we can cherry pick from all Responsive and Responsible vendors the lowest and best individual per item bid and then manage that item throughout a full 12 months of its multiple life cycles within our database. And, because of our robust records we are able to demand the maximum quantity discounts and best Just-In-Time stocking strategies. The real unsung benefit is the great working relationships that we are able to maintain with our vendor community. Again made possible due to our total grasp of our inventory needs and our ability to maintain a fair and equitable bidding payment relationship with our accounts payable department and the vendor.
With a very small staff of trained mechanics (all ASE Certified), we have been able to maintain a high level of quality fleet support services. Our city administration has supported our efforts at every turn – from the purchase over time of new and quality equipment to allowing us to remove out-of-warranty and aging equipment on a regular basis. Because we have been able to demonstrate a strong preventive maintenance management program, we have been allowed to create over the years a larger inventory of “assigned” rather than “pooled” equipment. This is a significant benefit because it increases driver responsibility and oversight while greatly reducing driver indifference and below par driving habits. Both of which have a direct and meaningful affect on operational and lifecycle unit costs. In addition, by being better able to maintain a high ratio of quality equipment to driver satisfaction we benefit from a correspondingly high level of operational performance. A performance where individual use guards against misuse and where operational satisfaction is contagious. Also over time, we have also been able to merge greater MPG strategies with a strong PM program. Driving our PM’s from the Odometer at fill-up allows us a daily best use and support strategy. Given the DOSSIER daily reminder page, a page our mechanics monitor on a continual basis from Kiosks on the shop floor, we are also able to create a paperless shop that allows these same mechanics to directly e-mail the driver informing him/her of the need for a new PM on the day that driver at fill-up passes on the preset trigger mileage for the next required service. A strategy that further shrinks the management pyramid.
Special coding for the new system provided for the billing codes that trigger the line item charges to create an excel spreadsheet of charges that was loaded into system that resulted in reducing the month end closing process from what once was 6-8 hours to a five minute activity.
With all inventory set up under the guidance of historical use date, now when an item is “sold” out if inventory it is immediately placed on our reorder board. This allows us to better manage the need and timing of delivery options and compare them against all pricing options.
As a result, this system now allows us to manage our part and vendor specific inventory, in a mouse click procedure, that creates a turn-key Purchase Order. One that pulls our out of stock inventory requirement numbers and places all data into a integrated PO suitable for faxing or emailing to the vendor of record for that stocked item.
Results: The DOSSIER system provides:
- a proactive rather than a reactive approach to parts ordering and vehicle maintenance.
- reduction of the 6-8 hour close time is now completed in 5 minutes.
- combining of warehouse inventories
- forecast of service times and parts needs
- reorder points from inventory and negotiation of improved prices from bulk ordering
- automated creation of POs that are faxed or emailed to suppliers
- bar coding to track and manage parts movement.
- supported energy management strategies for reducing MGP
- increased driver acceptance while reducing incidents of driver abuse
- significant departmental user acceptance and reliance on services
- significant cost containment strategies for operational & life cycle expenditures.
- enhance direct mechanic management of equipment
Mr. Stephen L. Hillis, MPA, CPPO, CPPB, CAFM, CAFS
823 Killion St., Louisville, Tennessee37777
Phone: (865) 970-4455 Cell: (865) 661-6674
Mr. Hillis received his BS in Education and Master’s of Public Administration from The University of Tennessee,
Knoxville in 1970 and 1986 respectively. He has also been awarded by the Universal Public Purchasing Certification Council, status as a Certified Public Purchasing Officer (CPPO) and Certified Professional Public Buyer (CPPB) – both expire 12/10/2011. This is in conjunction with his professional association with the National Institute of Governmental Purchasing (NIGP). In addition, Mr. Hillis is also a member of the National Association of Fleet Administrators (NAFA and is one of approximately 250 nationally that carries the dual titles of CAFS, or Certified Automotive Fleet Supervisor, and CAFM, or Certified Automotive Fleet Manager. As such, Mr. Hillis is the ONLY administrator nationally to carry these technical certifications in both Purchasing and Fleet Management in either the public or private sector.