The Department of Health have overall responsibility for the Health Service in the United Kingdom. They had a requirement to be able to validate some 100,000 lines of data a month through an automated process.
The Challenge
In attempt to reduce the size of waiting lists within the NHS, the Department set up Partnership agreements with private health providers such as BUPA and Inhealth.
These arrangements would allow patients to be treated privately but the procedures would be funded by the government. In effect patients were being paid by the government to “go private”.
The government would pay the private providers for all the procedures that they have taken. However the providers needed to submit a return detailing all the procedures that they had to undertake.
The government would only pay the providers once they were certain that the return was completely free of errors. The department identified some forty checks that had to be undertaken on each line of data e.g :-
All the codes that were used were valid (e valid Procedure and Primary Care Trust Codes)
All the fields were correctly supplied (i.e there was no missing data)
The procedure code and the price were consistent with each other
Given that some schemes have in excess of 10,000 lines of data, and that the data had to be re-run until it was clean, it was clear that an automated (as opposed to a manual) solution was necessary.
The Solution
Simply Spreadsheets Ltd designed an automated solution that would the provider return and automatically analyse the data irrespective of how many lines it contained. For each line of data, each of the 40 tests were performed.
If a test failed then it would show up in red automatically. If the test passed then it was shown in green. Hence by analysing the red cells it was possible to quickly identify the errors in the return.
In addition there was a summary page showing the overall numbers of each type of error.
The user only had to press a button and a 5,000 line return could be analysed in a few minutes.
Benefits
The solution offered the following benefits to the Department of Health :-
It was possible to analyse a very large amount of data in a very short space of time
The tool allowed the user to identify errors very quickly – by looking for the red cells
The Department benefited from the consistency in the process i.e knowing that all the data would be analysed in exactly the same way
In the medium term, the Department moved to an online portal. However the Excel solution was an excellent prototype that helped facilitate the creation of the portal.
Organisation Profile
The Department of Health are responsible for overseeing the implementation of all health care in the United Kingdom.
Customer Testimonial
"Kaps knowledge of Excel meant that the Department of Health could accurately process and report on over 100,000 lines of data in minutes."
Paul Dakin - Commercial Manager, Department of Health
The Department of Health have overall responsibility for the Health Service in the United Kingdom. They had a requirement to be able to validate some 100,000 lines of data a month through an automated process.
