Resource Futures was commissioned by the Welsh Local Government Association (WLGA) to develop an Excel-based tool to collate and analyse financial data. The tool enables the WLGA to monitor spending on waste collection by all 22 unitary authorities in Wales. It is used to produce reports to Welsh Government, and helps to deliver the Waste Improvement Programme in Wales.
Resource Futures worked closely with the WLGA to develop a technical specification for a tool which met their requirements. The tool was developed using advanced Visual Basic (VBA) techniques to produce a powerful yet user-friendly tool. It automatically processes raw data from WasteDataFlow and can be used to generate customisable charts.
“We were very pleased to have worked with Resource Futures to develop the WLGA Waste Finance Data Tool. The tool allows us to import, process, validate and analyse key performance data for the 22 local authorities in Wales and, with the press of a button, produce the analysis required for the annual financial reports for the Welsh Government’s Minister for Natural Resources. Resource Futures were very professional in their approach, ensuring that our requirements were fully met throughout the design, development and testing of the tool.”Gwyndaf Parry, Welsh Local Government Association
Part of the WLGA’s role is to analyse spending on waste collection by the 22 unitary authorities in Wales. They required a tool which would automate the otherwise time-consuming task of processing and analysing raw WasteDataFlow data, as well as incorporating other reference data such as collection scheme information. The tool would replace an existing spreadsheet which was becoming outdated.
A sophisticated functionality was required. First, the tool had to automate the processing of raw WasteDataFlow data. A second requirement was to validate the data, whereby data is highlighted if it exceeds a user-specified threshold. Finally, the tool had to be able to generate and export a range of charts for use in reports. This included charts showing the spending and recycling performance by local authority and by waste stream. The charts had to be customisable to show national performance, differences between collection systems and the influence of Welsh Government grants. Time series analysis was also an objective.
There were three phases to the project: design, development and testing.
The design phase involved the production of a specification which would guide the development of the tool. A specification was produced which achieved all the objectives set out by the WLGA. The WLGA were involved throughout the design phase.
The development of the tool was undertaken in Excel. A well-managed and carefully planned process ensured that the tool was developed according to the specification, on time and to budget.
The functionality in the tool was developed using Visual Basic (VBA), which allows Excel procedures to be automated, thereby saving the user time. The development team – Stuart Clouth, David Bowes and Will French – are skilled VBA programmers and wrote innovative and efficient code to achieve the objectives. The focus was always to ensure a fully user-friendly interface which could be used to drive a range of complex data processing and analysis functions. The tool was designed to be robust, and resistant to errors or bugs.
The testing phase ensured that the tool was functioning properly. The WLGA was involved in this process, and their feedback was used to design modifications to the final version.
The finished product was well-received by the WLGA. It achieved all the aims requested by the client. Full training was provided on handover of the Tool, although thanks to the WLGA’s involvement throughout its development they were already familiar with much of the tool. It is designed to be future-proof and will be functional up to 2020. Resource Futures are providing additional support as necessary.
The project is an excellent example of Resource Futures’ expertise in developing Excel tools to achieve efficiencies for their clients.
The project delivered a reliable tool with sophisticated functionality. The complex data processing and validation tasks are achievable at the click of a button. The data analysis interface supports the user in developing bespoke charts to display a range of key performance indicators. The tool can be used to show where spending is most efficient, where recycling rates are highest, and how these factors are linked to authority characteristics such as collection system or geography.