Matching Items (1)

134899-Thumbnail Image.png

Creating a Tool to Simplify and Automate the Vendor of Record Program for a Public Institution

Description

A public North American university needed a tool to automate their Vendor of Record rotation program. The goal was to give each vendor in the program fair opportunities and to

A public North American university needed a tool to automate their Vendor of Record rotation program. The goal was to give each vendor in the program fair opportunities and to be open and honest with how vendors were selected. This tool was created in Microsoft Excel with VBA to code macros. The three major Excel sheets are the Masters, the FM Interface, and the Executive Interface. The Masters act as a database and house data for each project. It is a plain Excel worksheet populated with tables. The FM Interface is a macro-enabled Excel workbook. It is distributed to every Facilities Manager in the program. The FM Interface communicates with the Masters in the background. The FMs use the various inputs and buttons to go through every step of procuring a project. They start by initializing a project, and are given a generated list of vendors who are qualified. This list is automatically rotated for every project. Then they fill in an RFQ and it is automatically emailed to the required vendors. When the Facilities Manager receives every quote, they input them into the program and they are saved to the database. When the project is complete, the manager must fill out a satisfaction survey to measure safety, cost, schedule, and quality of work. This is the main method of tracking vendor performance. The last sheet, the Executive Interface, is given only to the administrator of the program. It generates various reports, such as vendor performance, FM performance, and insurance status. It also has many administrative functions including insurance updating, vendor input, and delete project/vendor. The automated tool has been used without error on over 50 projects by 11 different Facilities Managers. The university's Facilities Management team is pleased with the automated process.

Contributors

Agent

Created

Date Created
  • 2016-12