Our client had conducted a study of potential ways that cost savings could be delivered through framework contracts. Main contractors would be prepared to reduce their rates, providing contracts are signed in advance for a defined number of years, based on the projected workload of upcoming projects.
The monitoring and reporting of efficiency savings for projects is handled through Efficiency Scorecard spreadsheets built in Microsoft Excel 2003. Separate scorecards are maintained for each regional area, which generates a significant amount of duplicate data that required manually updating on each spreadsheet. In order to ensure that these scorecards provide a complete and accurate project efficiency and reporting mechanism, a number of modifications were required.
Having experience of working with some of our client’s other tools, alongside previous experience of redesigning similar spreadsheet documents for some of our other clients, Zircon was in the perfect position to perform these modifications.
Zircon’s initial responsibility was to focus on removing the redundant information for the Scorecard document and updating the system reference data to acknowledge any changes made during this process. Upon completing this redesign activity Zircon was to perform an audit of all the calculations in the spreadsheet to confirm that they were correct and would refer back to the appropriate reference data.
Throughout the audit process each time a defect was identified and corrected a detailed log was made to ensure the client was fully aware of each change. In addition to the documentation of defects the client had asked Zircon to document every calculation performed by the spreadsheets, and the data they use, to assist our client with understanding and maintaining the altered document.
Prior to the conclusion of the first development stage a review of the upgraded Scorecard was carried out by the client so that Zircon could make any further alterations to ensure the tool operates as specified. Following the completion of the first phase the Efficiency Scorecard spreadsheet was delivered to the client with the data from each separate regional spreadsheet document collated into one place.
The second development phase found Zircon creating a reporting tool that would extract data from the redesigned Scorecard spreadsheet to display information on regional and national efficiency savings. The client had also asked Zircon to integrate a separate Lead Development Manager’s Efficiency Tracker spreadsheet into the Scorecard document so as to avoid the duplication of effort when populating data.