What I want, you’ve got
Ever feel like what you’re doing must have been done by someone else before so you don’t have to? That you’re manually adding to lookup lists when they’ve obviously been added somewhere else first for you to need to add them? Well it’s about time you stopped what you’re doing and started to leverage the power of the your existing systems’ data.
This blog is the second 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/1LTJoO)
In order to check whether something is right you’re either using your own brain as a lookup tool to check whether something is what you expect it to be or you’re using an electronic version of your memory/experience to programmatically do lookups quicker than you can. This electronic version is a frame of reference. Doing this well can be extremely powerful and beneficial.
The most powerful way this has worked for me is in payroll corrections. You can set up a staffing list which forms your frame of reference and use it to do the following:
• Automatically generate payroll journals to correct coding errors
• Tell you exactly why someone has been coded wrong and how to fix it without you having to work it out
• Calculate incremental drift costs instantly
• Vacancy/establishment control
• Cost vacancies and restructures according to accurately anticipated costs (taking into account enhancements, CEAs, APAs, OnCall% etc.)
I could spend days talking about how to set up something that does all of the above but this is about making system data work for you so I’ve chosen just the second point from above to discuss which you should find fascinating if you analyse/recode staff costs at all.
Firstly, you should be aware that ESR has two Business Intelligence platforms, the outdated but still frequently used Discoverer and the more current ESR BI. You can access them through the ESR navigator. If you don’t have access please request permission from your line manager to send your ESR systems team a request to grant you access to both of these tools.
Secondly, you should know that position numbers in ESR dictate where people are coded but this is superceded by assignment costings and pay element coding. Have you ever had someone’s position number changed but they still get coded to the wrong place? Incorrect assignment costing is 95% likely to be the reason why. Assignment costing in ESR is used to code a proportion of an employee’s pay to one or more code combinations (cost centres and subjective codes). It is useful for employees to be split coded and still only get one payslip. 99.99% of employees will have 100% of their pay (at least their basic pay anyway) going to a single code combination but some may be split coded like 50% to cost centre X and 50% to cost centre Y (always adds up to 100%).
So if you don’t know, now you know.
In Discoverer or ESR BI you will find a workbook or dashboard called NHS Position Analysis which should be a collection of 4 worksheets (in Discoverer) or 4 reports (in ESR BI) containing everything you’ll ever want to see on position numbers. You want to export all 4 sheets/reports to Excel for a recent effective date.
Inside your exported spreadsheet you’ll find 4 sheets and the first is the position summary sheet which tells you where all the position numbers in ESR are coded to (columns called Cost Centre and Subjective Code). In the fourth sheet you’ll find organisation detail which shows every employee and something called their Assignment Costing (columns called Cost Centre (Assignment) and Subjective Code).
Create 4 columns to the right of the organisation detail (fourth sheet) subjective code column and in the first new column bring back the cost centre from the position summary sheet (first sheet) using something like
=INDEX(‘Position Summary’!$B:$B,MATCH(LEFT(J2,SEARCH(“|”,J2,1)-1),’Position Summary’!$N:$N,0),1)
where ‘Position Summary’!$B:$B is the column on the first sheet which has the cost centre in, J2 is the cell on the fourth sheet with the position number in (it’s actually trapped before some other text in so that’s what the LEFT-SEARCH formula deals with) and ‘Position Summary’!$N:$N is the column on the first sheet which has the position number in.
Repeat the same formula in the second new column to bring back the subjective code from the first sheet except just change the column you want to return from cost centre to subjective code like
=INDEX(‘Position Summary’!$F:$F,MATCH(LEFT(J2,SEARCH(“|”,J2,1)-1),’Position Summary’!$N:$N,0),1)
where ‘Position Summary’!$F:$F is the column on the first sheet which has the subjective code in.
In the next columns you want to compare the assignment costings to the position number coding. If an assignment costing is blank (which you’ll find many cost centres are) then that just means it will default to whatever the position number cost centre is. For these, we can ignore so our formula is
=IF(OR(V2=””,V2=X2),”OK”,”Check”)
where V2 is the cost centre (assignment) and X2 is the position number cost centre we just brought in.
You can just drag this formula across to the next column and it’ll automatically check whether the subjective code costing matches the position number subjective code.
Drag or double click your formulas down and now you can see all the employees who have mismatching assignment costings to position number codings by simply filtering on any checks in either of the columns or doing a pivot and filtering on the checks in there (you could use an advanced filter – if you want to know how to use one ask @NHS_FFF). You’ll probably be able to find those pesky employees you’ve tried to have coded correctly for months but HR have exclaimed that they’re on the position number they should be on so they should be right.
All the mismatches you find are wrong if their Costing Apportionment is 100% or 1. Now, how do you know whether it’s the position number that’s wrong ot whether it’s the costing that’s wrong? That’s where a staffing list comes into play.
To put it simply, a staffing list is, to the best of your knowledge thanks to budget holder meetings and other discussions, where employees should be coded. It is the most fluid frame of reference but also the most lucrative in my experience and can be setup using the following data/steps
1. Start with the most recent payfile data
2. Apply your regular adjustments
3. Ask budget holders what the staff list should look like
4. Apply changes as a result of budget holder meetings
You should have the following columns present at the very least in your staffing list
• Cost Centre
• Subjective Code
• Employee Name
• Assignment Number
• Contracted Hours/WTE
• Paypoint (e.g. XR05:04)
• Increment Date
• Notes
• Cost Centre & Subjective Code (if Cost Centre is in cell A2 and Subjective code is in cell B2 then this is just =A2&B2)
Now you just see which is right, the position number coding, the assignment costing or neither. If neither or assignment costing is correct then the position number needs to be changed, if the position number is correct then the assignment costing needs to be end dated in ESR or overwritten by a blank cost centre and the correct subjective code. End dating assignment costings just deletes the current costing and replaces it with a blank cost centre and the current position number’s subjective code.
To do the checks just use Vlookup or Index-Match formulas between your staffing list and your organisation detail sheet with all the formulas you added earlier so again you’re not looking anything line by line.
Once all of that is setup, you’re good to go for the rest of the year being able to identify bad position numbers or assignment costings as your formulas are all going to do the work for you. It reduces the number of recodes you need to do, helps HR to report correctly and helps budget holders have the right staff in the right place on their budget statements. Your job now is to maintain the staffing list so your frame of reference is as accurate as possible and change the references on your staffing list to look at new position analysis workbooks. You can get an ESR BI or Discoverer superuser to even add those 4 columns we created in the organisation detail sheet for you or you can just copy and paste the formulas from one month’s position analysis to the next.
Phew, that was a biggy right? Well that’s just a small part of what a fully operational staffing list can do as I mentioned at the start. Other frames of reference exist such as
• Keeping a database of the trust cost centres with their associated budget holders, service managers and divisional managers
• Connecting to your finance system to always be able to select existing cost centres without you having to maintain a lookup list when new cost centres are added
• Using an NPR in Meditech or equivalent to export all excluded devices matched to patient detail so you don’t miss any opportunity to recharge for high cost drugs and devices
That last one is a juicy one for anyone doing that job at month end but everyone can find their own way to make existing system data work for them.
Next time: Mapping – how to report on ad hoc structures such as NHSI returns and corporare report headings in minutes rather than hours or days: https://shar.es/an3w9C
If you have any questions or want to discuss what’s featured in this blog, please feel free to contact me on @NHS_FFF