We drew a map to a better place
How tiresome it must be to fill in the NHSI Finance return only to find you’ve got lots of changes to make and you haven’t got anything set up to just have the figures updated as they change. The 2 minutes it takes me to do staff costs and operating expenditure is gruelling…
Oh, 2 minutes is not long at all? Well I guess but I have to first run a report for the latest changes which takes 5 seconds from the click of a button…
Still think that’s quick? Well I think so too 😉
This blog is the third part of a series on various ways in which you can go about doing things differently this year to free up your time, reduce maintenance and increase the quality of your work.
Part 1) https://shar.es/anmZTb
Part 2) https://shar.es/anmZVn
Each organisation in the NHS uses a chart of accounts (CoA) in their finance system to code their income, expenditure, assets and liabilities to an appropriate place so they can be analysed or easily identified at a later date. For example, coding the cost of a band 5 nurse on critical care to the critical care cost centre and the band 5 nurse subjective code. The cost centre will have a hierarchy which places it under a service group or division for instance and that enables the surgery finance team to filter on all the surgery related cost centres. The same can be said of the subjective code which could be under the nursing and midwifery group under substantive staff. These are effectively mappings to analyse costs and revenues better.
When it comes to external or internal reporting, the CoA in your finance system may not have the most suitable mappings that are required for your particular report. For instance, a corporate report could have a section for ‘Other Non Pay’ which is what’s left over on Non Pay Expenditure (a CoA grouping in your finance system) after taking into account other areas in your corporate report such as Drugs, Clinical Supplies etc.
To deal with these situations we create mappings outside our finance system to do vlookups to to get our data equipped with appropriately named reporting areas. To set up these mappings, you could have a list of all your subjective codes and add a column next to them all to map them to the place you want them to go but this takes a lot of maintenance when new subjectives are used. Instead, right down exactly what you do manually and try to tell the computer to follow your logic within the reports you write directly from the finance system.
We can [build it]. We have the technology.
An example of this could be with Excess Bed Days. Now I know I have no subjective codes specifically for excess bed days in our ledger but I do have sub analysis codes (again, part of your CoA) that have been set up to split out income between different types of income streams. I therefore write into a report which gives me my balances for the trust by code combination a new column which, amongst many other things, says that whenever Sub Analysis 1 contains the phrase ‘Excess Bed Days’ call this line Excess Bed Days. In discoverer or OTBI, this would look like
CASE WHEN Instr(‘Sub Analysis 1 Desc’,’Excess Bed Day’,1,1) > 0 THEN ‘Excess Bed Days’ ELSE NULL END
where CASE WHEN is like the start of an if statement; between WHEN and THEN is your expression that you want to evaluate to be either TRUE or FALSE; between THEN and ELSE is what you want to return if your expression was TRUE and between ELSE and END is what you want to return if your expression was FALSE. Instr looks in the sub analysis 1 description for the phrase excess bed day starting at the first character of the sub analysis 1 description and returning the number of characters along the sub analysis 1 description where excess bed day occurs for the first time. I’ve returned ‘Excess Bed Days’ when this is greater than 0 because if it is greater than 0 then it’s found the phrase I’m looking for and then I’ve returned nothing or NULL when it hasn’t found anything.
Now each time I run the report, my new column always tells me which lines are for excess bed days in an easily filterable column – which can be used in pivot tables or other Excel formulas to pick out Excess Bed Day income.
All you have to do is take the time to build up this ‘new column formula’ to include all the elements of your external or internal reports and you will end up with a report which can just instantly give you what you need without any adding of specific rows together manually or adding to lookup tables when new code combinations arise or whatever it is you normally do to map to your monthly reports will no longer be required. It is a half day’s worth of work that could reduce your monthly reporting time dramatically.
Below is a formula I use to map to the corporate report. You can see how many layers of the CoA it utilises (from cost centre codes to sub analysis descriptions).
CASE WHEN “GL – NHS Code Book”.Final Accounts Line = ‘PAY EXPENDITURE’ THEN ‘Pay Costs’ WHEN “GL – NHS Code Book”.Subjective Group IN (‘DRUGS’,’BLOOD PRODUCTS’) THEN ‘Drugs’ WHEN “GL – NHS Code Book”.Subjective Summary = ‘CLINICAL SERVICE AND SUPPLIES’ THEN ‘Clinical Supplies’ WHEN “GL – NHS Code Book”.Monitor Template = ‘DEPRECIATION ON OWNED/LEASED ASSETS’ THEN ‘Depreciation’ WHEN “GL – NHS Code Book”.Monitor Template = ‘DEPRECIATION ON DONATED ASSETS’ THEN ‘Depreciation on Donated Assets’ WHEN “GL – NHS Code Book”.Monitor Planning Model = ‘INTEREST EXPENSE’ THEN ‘Interest Expense’ WHEN “GL – NHS Code Book”.”Subjective Code” = ‘745000’ THEN ‘MASS/Restructuring’ WHEN “GL – NHS Code Book”.”Annual Plan Soci 3″ = ‘IMPAIRMENT LOSSES (REVERSALS) NET’ THEN ‘Fixed Asset Impairment’ WHEN “GL – NHS Code Book”.Final Accounts Line = ‘NON PAY EXPENDITURE’ THEN ‘Other Non Pay’ WHEN “GL – NHS Code Book”.Monitor Planning Model = ‘INTEREST INCOME’ THEN ‘Finance Income’ WHEN “GL – NHS Code Book”.”Annual Plan Soci 3″ = ‘NON CLINICAL INCOME’ THEN ‘Government Grants/Donated Income’ WHEN “GL – NHS Code Book”.Monitor Template = ‘PROFIT/(LOSS) ON ASSET DISPOSALS’ THEN ‘Gains/(Losses) on Asset Disposals’ WHEN INSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,’EXCESS BED DAYS’,1,1) > 0 THEN ‘Excess Bed Days’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,2) = ’41’ THEN ‘Elective’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,2) = ’42’ THEN ‘Non Elective’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,2) = ’44’ THEN ‘Outpatients’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,4) = ‘4300’ THEN ‘A&E’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,2) = ’45’ THEN ‘Critical Care’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,2) = ’46’ THEN ‘Non PbR Drugs & Devices’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,4) = ‘4701’ AND “GL – NHS Code Book”.”Cost Center And Desc” <> ‘910004 INCOME CONTROL’ THEN ‘CQUIN’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,4) = ‘4711’ THEN ‘Private Patients’ WHEN SUBSTR(“GL – NHS Code Book”.”Sub Analysis Code1 And Desc”,1,4) IN (‘4709′,’4714’) AND “GL – NHS Code Book”.”Cost Center And Desc” = ‘910001 OTHER INCOME & EXPENDITURE’ THEN ‘Contract Sanctions’ WHEN “GL – NHS Code Book”.Final Accounts Line = ‘INCOME’ THEN ‘OTHER INCOME’ ELSE ‘CHECK WITH SAMMIR’ END
So how do I get all my NHSI returns done so quickly? Well partly to the above and most people will have to re-run reports as your finance system will not provide for what I’m about to write. However, before I get to that bit, I include my mapping to corporate report areas and NHSI areas in most reports I write so that anyone can analyse/pivot on those mappings for any work they are doing. It is particularly useful in my forecasting model I built as I bring together everyone’s forecast in one summary spreadsheet (using Power Query – speak to me about this on @NHS_FFF) and can show forecasts and actual and budgets and WTE all together in one place and have a corporate report which just populates upon refresh and similarly NHSI staff and NHSI op ex sheets which give me YTD and forecast figures together instantly on refresh.
The optional extra I noted above, refreshing my figures at the click of a button, comes with Oracle Smart View. This is an Excel Add-in to interrogate Oracle systems. NEP Cloud currently offers access to this but ESR does not have it on their agenda for security reasons. It allows you to pull data straight to excel without running reports in one place and exporting it to excel afterwards. With excel formatted in such a way to accomodate for new code combinations and balances, you can simply press refresh on your report, it bring live data to your spreadsheet and you just copy and the data to your NHSI return. Last minute changes to the ledger? Just press refresh and copy and paste, no long processes, just two, refresh then copy and paste, done.
If you are exporting your financial data to a data warehouse or have access to your finance system via some sort of data connection you can just use Excel’s standard data connection capabilities to perform the same task but it may not be as user friendly as Oracle Smart View.
If you have any questions or want to discuss what’s featured in this blog, please feel free to contact me on @NHS_FFF