What happens when the one person who has all the knowledge on a certain process leaves your team? Panic? That “this is fine” meme (you all know the one)? Or take a breath and look for any opportunities to review and modernise old processes? You may think it’s the third option but, it’s all of the above!
Every year the department for education requires us to forecast school pupil numbers for the next seven years to inform future provision. The processes we’ve used to produce these forecasts have been largely the same for the past 10 years and in picking up the mantle on this gargantuan task Louis Yong Yang Yen took the opportunity to automate the process and learnt a few things on the way…
Understanding the data that goes into the processes:
The schools forecast requires different data sources so it’s important to try and understand all the different sources especially things like who owns the data, how often its updated, where does it come in from, is it raw data or processed data, where is it stored? Where possible we tried to have the data sources as raw as possible without relying too much on processed data. It goes without saying but it’s important to understand how data is collected and who maintains it.
What outputs need to look like?
In order to have as little disruption as possible in replicating the outputs required, we needed to ensure we had processes in place that could produce the same outputs that were previously provided manually. Without getting too technical, a lot of the outputs we needed were excel sheets and we used an R package (openxlsx), which basically allows us to write instructions in R to create excel spreadsheets.
How will the automation actually work?
We had the data sources, and we knew what we needed but how did we get from A-B? With this we needed to think about what we meant by automation!
In many scenarios it is 'possible' to automate every single stage of the process so that final outputs can be churned out at the merest push of a button. But just because its possible doesn't mean we should be doing this. Its important to think through the entire process and consider the components which can be automated (perhaps simple data transformational steps that do not require elements of professional judgement) and those which need to remain manual. Automation vs Manual Labour is a conversation for another day!
*However, as we have discussed on these blogs several times before - there is usually a sweet spot in the middle that can remove arbitrary transactional processes whilst maintaining the human professional judgement where needed.
So we could ‘write the code and automatically push out the schools forecast every year’, but this is not possible (nor entirely desirable) as the schools forecast requires us to be flexible at times and some data sources are very time sensitive. Instead, the way we’ve automated things was to create different scripts to run different parts of the forecast which also allows us to check the forecast at key milestones and if errors were to occur it is clear for which parts of the code it occurs, allowing for easy troubleshooting. Another quirk of the schools forecast is that sometimes manual intervention is required, in our scripts we’ve left places for where we can input manually into the forecast as required.
Perhaps the most valuable lesson, going forward we should try and regularly review our processes and software to ensure we are as efficient as possible.
And now do it all for specialist SEND places!
Alongside the statutory mainstream schools forecast this year, Dfe also requested the first SEND forecast, to understand the expected trend and potential gaps in future specialist provision. Senior Analyst, Caroline Murphy shares her
In Essex, we’ve had a SEND forecast for a couple of years which forecasts the number of children we expect to have an Education and Health Care Plan over the next five years based on historical patterns (see a previous blog on this here). So, when the DfE issued draft guidance to LAs in October 2022 requesting we submit an (age and setting type level) SEND forecast for the first time in 2023, along with the mainstream forecasts, I hoped we were in a good position to use our existing processes and data.
After trawling through the DfE draft guidance, it seemed we were in a good place with the historical EHCP data and format already in place. However, the process and forecast methodology proposed by DfE was different to the one previously used in Essex. As much as possible, the SEND forecast was to follow similar processes to the mainstream forecast and use some of the same datasets. However, this became more challenging than expected as there were also some key differences between the mainstream and SEND data and processes. And it soon became apparent that the way in which the population data for the mainstream and SEND forecasts needed to be processed was quite different. We therefore managed these two forecasts in one overarching project, with regular progress meetings and sharing of data, but accepted we then needed to go away and work differently on the two different forecasts.
As this is the first time attempting this new process automation was not explored as we expect changes after this first trial year. In the autumn 2023 all LAs are due to receive queries from DfE based on our first SEND forecast submission, when compared with other LAs submissions across the country. The guidance is also expected to change, as a result of different LAs experiences, and outputs. But once this becomes an established process, the next challenge will also be to automate the SEND forecast in line with the mainstream process, for a more efficient, replicable process in future!
1 comment
Comment by Lokesh Palaniraj posted on
Very Insightful about the automation and the way you handled two different projects with a lot of difficulties, but my question is it actually worth all the effort on automating the process rather doing it manually and when you said automation what are the technologies and languages that you used to automate it, please elaborate it technically, as I am so curious and fascinated about your research, thank you!