Requirement: Produce bespoke Excel templates to track, monitor and report on section 106 planning obligations on developments.
Solution: Swansea Council's Commercial Development and Planning Departments wanted to replace an Access database system with a solution in Excel to hold details on section 106 obligations assigned to planning applications for large developments. The Excel solution should allow for quick and easy input of development site data but also deliver additional features for viewing and analysing the information in more detail. Also important was the ability to calculate the results of "trigger" tests on dates and the status of dwellings which would prompt the necessary actions to be taken as the development progressed.
A site template was created to hold the necessary details of an individual planning application. Excel table functionality was used to simplify data input and then the tracking and monitoring processes used a mixture of advanced formulae, conditional formatting and utility macros.
In addition, top level reports were required to aggregate and display the information from all the individual site files. Three report templates were produced to provide detail on finance and accounts, the monitoring of residential developments and a general summary. The report process was automated with macro code opening each source file in turn and capturing the relevant data into the report layout.