Buckinghamshire Council’s case study demonstrates how technology can be used to support budget analysis and oversight. The council’s use of Power Query cut the time and work to compare changes in budgetary information from over a day to one to two hours. This development freed capacity, enabling more frequent analysis and a better grip on changes in care packages, outliers and errors.
Effective budget oversight is essential to financial control and improved outcomes for people who draw on care and support. It enables resources to be directed more effectively where they can have the greatest impact and improves understanding of how a council’s money is spent.
The challenge
Buckinghamshire Council uses a Power BI dashboard to show how the annual financial commitment data from our system (ContrOCC) changes daily. While a useful tool for demonstrating what is changing, the question always remains: why has it changed?
Previously, to explain why financial data was changing, this data was provided monthly through the budget monitoring process. However, this frequency was not timely enough to enable appropriate and meaningful action to be taken.
When we reported monthly, we used pivot tables to compare two sets of data and work out the reasons for the changes – namely new starters, leavers, increases in packages and reductions in packages. This was time-consuming, taking about a day’s work, and was not viable to do on a weekly basis.
The action
To find a solution and provide meaningful data on a weekly basis, we explored using Power Query. We initially held a knowledge share with finance colleagues, but this only enabled us to skim the surface. Therefore, a member of the finance team was tasked with learning Power Query (with the help of YouTube) and then teaching it to us in our planned bi-weekly knowledge share meetings. Another member of the team then applied it to the weekly comparison data.
The outcome
We implemented Power Query, which cut the task of analysing data down from over a day to one to two hours of work per week, meaning that the frequency of reporting could be increased. This enabled us to:
- highlight big changes in packages and which manager approved them
- spot errors quickly and get them resolved
- hold ‘top 20’ case panels to scrutinise outlier cases.
In turn, this meant that heads of service had the right information to take appropriate action to ensure we are providing the right size packages of care and address issues promptly.
By using Power Query to generate improved reporting, and the use of that reporting in panels and regular updates, there has been more detailed scrutiny of cost, enabling errors to be picked up and action to be taken.
By way of example, we identified that a lot of packages for the same provider had increased. This identified further work that needed to be done to reduce 1:1 costs to ensure that the provider was not overpaid.
Ultimately, the result was that there was a firm grip on care package spend throughout the year, and the adult social care budget in Buckinghamshire underspent by 0.5% compared to an overspend of 1.6% the previous year.
Reflection
The key success factors in this approach were as follows:
- Using Power Query to enable data analysis.
- Buy-in from the director of adult social services and service director for operations – with their support, this data was used to hold teams to account for what they were spending.
- Regularly sharing the data with heads of service to challenge spending ensured that it remained under control throughout the year.
The three combined factors ensured success. The system was key, but equally the culture of accountability embedded the benefits of the improved reporting.
Future plans include summarising the data, so that multiple weeks/months/years can be analysed together, and including a split by primary support reason, eg learning disability, mental health and other (includes physical support, sensory support, etc).
7 July 2025
Contact information
Emma Wilding
Head of Finance, Adult and Health
emma.wilding@buckinghamshire.gov.uk
Ruth Cover
Head of Strategy and Governance, Adult and Health
ruth.cover@buckinghamshire.gov.uk
