Develop a bespoke mental health resource allocation tool

Develop a bespoke mental health resource allocation tool

Mental Health Care - Develop a bespoke mental health resource allocation tool


Assist local authority develop standalone RAS process and sustainable allocation for mental health users.  Model successfully piloted followed by live roll out.



Following previous projects, the local authority (LA) requested assistance with adapting the main RAS for the requirements of mental health users. This was required as it was felt the general RAS did not adequately address the needs of these users, and on this basis that mental health users should use a customised Self Assessment Questionnaire (SAQ). This would need to be tested it on a desktop basis, and the funding allocation calibrated to available funding (which would be ring fenced). As this was a new assessment, the points within each domain would also need to be determined.


Our first task involved finalising the SAQ which involved sessions with the working group to address issues including format and allocation of point to domains. Once this had been agreed we assisted the authority to develop a new mental health SAQ which was aligned aesthetically and functionally to other SAQ’s. This was based in Excel and incorporated the questions provided by the LA. This produced an automated, accessible form which was used originally for testing but then subsequently adapted to the requirements of live use with service users.

Once the original desktop testing was completed (around 120 forms) we developed a VBA routine to automatically pull through the results into a central sheet. This saved the client significant amounts of time having to manually cut and paste responses and also provided a good system of validating responses for correctness and referring incomplete questionnaires back to the LA. As we captured responses to each question it also enabled the MH RAT to be flexible to different scoring frameworks.

It was agreed that the functionality in the main RAS was desirable and should be adapted for the mental health RAT (MH RAT). This reduced time developing a bespoke solution, ensured the project could be turned around a lot quicker, and the LA were already familiar and comfortable with the format and functionality of the RAT.


Once the format of the SAQ had been agreed, and the testing phase was completed, we were able to deliver a working version of the MH RAT quickly, and spend time refining the assumptions behind the allocation of funding to points – such as the funding pool, number of users, method of allocation, and contingency. After a few iterations we arrived at a version producing an allocation table the LA were comfortable with.

We have subsequently assisted with some minor updates to the assessment form and MH RAT. The LA were pleased with how the tool worked and have proceeded to use it within a live environment.