SETUP - Sickness recording using Bradford Factor for 12 month rolling period

Hi,

I am trying to set up a spreadsheet which will calculate the rolling sickness absence for the last 12 months for 133 staff.
I don't want to 'reinvent the wheel' so wondered if anyone was willing to share ideas? Or systems that work for you?

Very much appreciated!

Thanks

Hi Dave,

The information is currently input into individual tabs within a 'Employee Record' spreadsheet....... This is ran from 1st Oct to 30th Sep so would need to merge together data from across 2 separate spreadsheets first.....

Look here for sample Excel calculator...



https://www.gov.im/lib/docs/hr/bradf...calculator.xls

Thank you John,

I understand the calculation behind the BF. I am trying to create a 'Master' spreadsheet that you have each individual employee on and pull through their BF score.

Can you give an example of current files?

If you had a sheet per calendar month (with all employees) then it is easy to sum the number of sick days across 12 sheets.

Equally you could keep a monthly total on each monthly sheet of total days sick and total periods of sickness: again you can then sum across worksheets.

I doubt if there is standard template as it often depends on who, and how, an organisation, arranges the data collection and its processing: the calculations are straight forward (as you know better than me!

Keeping running totals (year-to-date) would allow any "warning" triggers to be highlighted.

In the end, keep it simply!

Thank you everyone!

Not quite there yet but working on it!!!

With that number of employees you are better off using an Absence Management solution.

In order to properly calculate the Bradford Factor you have to take into account any holidays - public or annual and also any part-time working. If an absence starts before a holiday or before a day that is not normally worked and is still continuing on their next working day, that should only be calculated as one period. The chance of errors when doing the calculation using Excel is too high.

We've only got 35 employees and use Teamseer. Each manager gets a report each Monday detailing any absences over previous 4 weeks, quarter and year for their department, and any employee can see their own Bradford Factor 12-month score. That enabled employees to buy into the process and reduced the Monday absences that the system highlighted. One thing the system also highlighted was that the employees in our Aberdeen office have different bank holidays to those in England.