Design, development and implementation
01 April 2019
This article was featured in the April 2019 issue of the magazine.
The CIPP’s Project of the Year Award always draws great nominations of amazing projects, many of which involve payroll and the design, development and implementation of software.
This article reviews two of the nominated projects for the 2018 Project of the Year Award. Both feature automation of work to achieve efficiency and better outcomes.
The worthy winner of the 2018 award is ‘Bostick’ which was nominated by Armstrong Watson LLP Payroll Services.
Armstrong Watson LLP is an innovative company always seeking to improve by embracing technology where possible. The payroll team are viewed by many in the firm as AI payroll pioneers with a human touch.
Armstrong Watson LLP runs around 1,200 payrolls at any one time. Each administrator can have up to 150 payrolls per month to process, which is time-consuming, especially in a fast-paced environment. Clients provide data in many formats and therefore there is always a risk of error, albeit rarely resulting in a client being impacted. Correcting errors takes time thus reducing productivity and efficiency; costs that cannot and should not be passed on to clients. When productivity is reduced it often creates the need to increase resource but in Cumbria (where Armstrong Watson is based) payroll specialists are very difficult to find.
As with any payroll processing function, human and system errors can occur. With automation increasingly on the agenda of businesses the payroll leadership team wanted to look at using systems to mitigate risks of errors further.
Objectives for the project named ‘Bostick’ were:
to streamline the process of data capture and extraction from clients into the payroll software
to decrease the risk of human error impacting on the payroll calculation
reduce time taken to process payrolls to minimise recruitment costs.
The project team comprised: the sponsoring director of payroll services, the head of operations, payroll support and IT specialists, and the payroll support team in conjunction with payroll processing team.
...the payroll leadership team wanted to look at using systems to mitigate risks of errors...
Existing data capture methods
Three methods, for capturing clients’ pay data are in operation, each with advantages/disadvantages (A/D):
Front-end – the client enters the data straight into payroll software and provides a backup of the system for the Armstrong Watson (AW) payroll to process. A/D: direct data input so no re-keying; requires payroll software and user knowledge at client’s site.
Ad hoc – the client provides the data in an unstructured format either by email, post or telephone. A/D: very flexible; easy for the client to integrate into their existing processes; encourages continuous stream of pay run data through the period; risk due to GDPR; duplication of data entry with high risk something is missed.
Input sheets – the client uses a bespoke spreadsheet to enter the specific data required to process the pay run (the most common). A/D: each sheet bespoke to client and encourages all required information to be provided; can be imported directly into payroll software avoiding re-keying.
Further, the inability to access payroll data from an enterprise perspective has these disadvantages: unable to monitor the overall progress of payrolls; no trigger to ensure that increases/decreases in payroll numbers are reflected in fees.
Overview of new approach
Clients that currently provide information using the ad hoc method to move to input sheets. Clients currently using the front-end method will not be moved.
Increase the use of automation in the payroll software so that once the input sheet is provided it is automatically imported into the correct client. When the payroll process has finished the automation will create the specified reports and save them into correct folders. These reports will be sent via a portal to the client (this will form part of phase 2).
In order to improve the speed of payroll processing the input sheet will be validated before it is imported into the payroll software.
The first steps above will be completed by the Payroll Support team. The third step required some development and would be delivered by the Business Systems team.
...error rates have reduced by almost 100% on transactional processes ...
Current lifecycle of an input sheet
The client is setup up with an input sheet template for the data to be gathered; the number, type and order of the columns in the sheet are defined.
At the end of each pay run the input sheet is created using a manual process in the software, and a file with the correct columns and the data from the standard pay run data populated.
The input sheet is sent to the client, and in future via the portal. The client returns the amended input sheet. The file is then manually imported into the software.
Client folder structure
When a client is added the system will automatically create the client folder structure.
We will capture the payroll frequency in the database and store the total number of employees to use as a base line moving forward.
The client will be identified by a new column (Payroll Number) that will be added to all input sheet definitions. This will provide a unique identifier for the payroll in the database.
We will create a spreadsheet Add-in that will be only available to the Payroll Team. This will have several buttons with resultant functionality as follows:
Prepare file – lock the employee name, number, NINo columns; add validation to columns so numeric only; password protect the file and save password; add text box to the sheet that will typically have the instructions, payroll person and date to be returned.
Validate file – This button will perform several functions, including:
if this is the first time the payroll has been validated the employee number, name and gross pay will be stored in the database; otherwise, system will compare the list of employees and provide a list of new starters and leavers in a new tab of the spreadsheet workbook
if changes are identified in the tax code database the individuals and their new tax code will be added to a new ‘TaxCode Change’ tab
if changes are identified in the student loan database the individuals and their loan will be added to a new ‘Student Loan’ tab
a sum total will be added to the bottom of each numeric column
update database and set this payrun as ‘Started’ with a date/time.
Create CSV file – This button will perform several functions:
create a properly formatted CSV file: UK dates; currency in ‘£’; remove column totals
save the import file, Taxcode file, student loan file to two locations
update the database and set this payrun as ‘In progress’ with date/time.
Compare output – When the payroll output file has been opened in the same instance of the spreadsheet, this button will: view both files side by side; validate the file; compare column totals turning those matching green or red if not matching; if matching save copy and data to the database to form basis for the next payrun’s validation; email Support if number of employees has changed; mark the Taxcode and Student Loans files as updated; update the database and set this payrun as ‘Completed’ with date/time.
The project system design commenced in December 2017 with phase 1 completed by March. By August 2018, 85% of payrolls had transferred to ‘Bostick’ with successful results.
Internal communications and training
All payroll processing and implementation teams have been fully trained in the new procedures, with 1-1 training happening each time payroll batches are transferred across. Clients are unaware of the changes to minimise any disruption to their normal methods of submitting data. However, where clients submit data via any method other than the spreadsheet, the administrator transfers the data to a template spreadsheet to enable Bostick to work by exception.
Payroll procedures have been amended to ensure re-accreditation with CIPP’s Payroll Assurance Scheme in 2019.
The error rates have reduced by almost 100% on transactional processes due to the automation.
The time taken to run payrolls has also reduced significantly. Across all the circa 1,100 payrolls, 180,655 minutes or 3,010 hours have been saved on payroll processing. This means the payroll team can now offer added value to clients, such as advice on national minimum wage, processing of benefits in kind via the payroll thus improving customer service and compliance. It also means these additional benefits can be provided without charge as the administrator is already employed but has additional time. It also provides capacity of three FTEs to allow the service to grow further without the need for recruitment, equating to an additional 450 payrolls of potential growth.
Customer satisfaction hasn’t been measured against this specific project as often customers do not know about the change. This project was to ensure errors were reduced, thus reducing time correcting errors and speeding up the actual transactional processing.
By improving efficiency and processing times it allows for further growth and increased customer added value; offering additional services such as compliance health checks on their payroll.
The payroll team have embraced the changes and this award would be in recognition of their engagement and hard work, along with recognition for Toby Woodhead and Alan Jardine in our IT service; without them this project wouldn’t have been possible.
THE ORBIS ROBOT
Orbis is a public sector partnership covering the three councils of East Sussex, Surrey, and Brighton and Hove, providing ‘backoffice’ or business support services. Orbis also works with a range of other public sector clients and supports over 650 schools and academies across the south east with fully managed payroll and transactional HR services, processing over one million payslips annually.
An onerous task performed by the Payroll Team was the monthly calculating and uploading to the Teachers’ Pensions website of an individual summary of the contributions paid for every school Orbis supports. The process was highly repetitive and time-consuming – taking two people around two days each month to complete – and pulled Payroll Team members away from other more valuable work.
A ‘eureka’ moment came when the Payroll Team met with the Orbis Robotics Team, who were attempting to raise awareness of the potential capabilities of robotics across the organisation. The Robotics Team were looking for a process to use as proof of concept, since RPA has the potential to bring improvements to the organisation by enabling more work, in less time, with fewer people. All the things that made the uploading process a horrible task for humans were the same qualities that made it ideal for trialling with the new robotics technology.
The Payroll Team provided details of the process, and the robotics team set about building/writing the robot. The initial phase took just two days as the Robotics Team incorporated a macro that had previously been developed to collate data, so only the final task of uploading the data to the TP website needed to be automated. The next step was to test the solution could complete the task end to end. After a thorough programme of testing, approval was given. Overall, it took a couple of months from the first conversations to the robot performing the entire upload process by itself for the month of March 2018.
...uploading to the Teachers’ Pensions website of an individual summary of the contributions paid ...
The Payroll Team were consulted and kept up to date throughout the short life of the project via weekly meetings and written progress reports. Also, to counter the idea that robots were infiltrating the organisation and taking over the jobs of humans, a wider programme of communications to highlight the benefits of RPA and dispel myths was delivered via weekly blog posts on the Orbis Linkedin page.
The project has brought a range of benefits, including:
Time saved – based on an eight-hour day, that’s 192 hours of staff time over the course of a full year.
Financial savings – estimated at £7,000 per annum for this one process.
Greater accuracy – zero chance of human error.
Greater resilience – previously only certain team members were trained in the process, with no allowance for absence, annual leave etc. Now the only training requirement is how to start the process (i.e. ‘switch the robot on’).
Team members are now free to do more value adding, customer facing work – which translates to higher levels of customer satisfaction.
Potential exists for further applications – the same robot could be put to work in any situation that requires a file to be uploaded to a website.
The project succeeded in proving robotics could work in Orbis. This is important as Orbis transforms services to provide the most efficient means of supporting the three partners, our residents and customers into the future.
Individual summary statement
We managed to build, test and implement a working robot within the space of a couple of months. We did this from a starting point of limited prior experience, no prior training, no specialist skills, no dedicated budget, and initially not even a technical platform to work on. As a result, we have dramatically cut the time required to complete a task, freed staff to concentrate on more value adding, customer focused work, whilst also fulfilling our obligations to customers. RPA has brought greater accuracy, team resilience, and could potentially lead to new business opportunities. The robot could easily be applied to other similar situations that require a file to be uploaded.
Ultimately, the project has proved that we can design and build (write) a robot and give it responsibility to complete an entire task from start to finish; all without impacting customers, except in a positive way.
Since the initial robot, Orbis now has thirteen processes automated with more ‘bots’ in development. Total savings are in the tens of thousands, and the partnership is developing a reputation as trailblazers in the field of automation in local government, with several other nominations and awards (http://bit.ly/2TT90M6).