From Spreadsheets to HR Systems For An International Organisation

Our client is an international organisation which deals with the trade of different metals across the globe and which manages its staff from their HQ in London.

The problem

Our client was using Excel spreadsheets to manage the salaries, bonuses, and pension schemes of their employees. At first, this choice made sense. Excel delivers several important benefits for small companies and start-ups to organise and store business information. Firstly, spreadsheets are inexpensive; saving the company money at first. Excel is simple to use and is accessible for anyone with a computer. Data can be calculated easily, and spreadsheets can be seamlessly integrated alongside other Microsoft Office programs.

However, as companies grow and data becomes more complex, Excel starts to hold businesses like our client’s firm, back.

Spreadsheets are not adaptable. They are usually created by one individual, and are organised in a way that makes sense to them, but not necessarily to others. This was the case for our client, who struggled with the different layouts for each spreadsheet. Issues arose when different people took over the management of spreadsheets, or when spreadsheets needed to be shared.

A lack of consistency also caused problems for our clients when they wanted to match spreadsheets up. Our client’s spreadsheet containing pension information, named staff differently to the names by which they were categorised in their salary spreadsheet. This made it impossible to automatically match those spreadsheets. Additionally, our client struggled with duplication of columns and information in their pension schemes spreadsheet. This was triggered by a lack of consistency in the way pension schemes were calculated.

Spreadsheets are not scalable. As staff data grew, our client’s spreadsheets grew and became more confusing to interpret and analyse. They became even more unclear when our client created multiple new columns to track the history of staff data.

Lastly, the privacy settings in Excel are very basic, meaning that access couldn’t be restricted to some but not others. This caused issues for our client as they tried to keep the spreadsheets containing staff data private for most employees. However, each salary had to be agreed on with the staff’s line manager. This created a time-consuming and cumbersome process of determining staff salaries.

The solution

To resolve the problems that Excel was causing, our client invited us to step in to prepare and implement an IT system that would allow them to manage staff data in a more reliable, efficient, and accurate way. After discussing the problems with our client it became clear that there were several requirements to which our solution needed to adhere.

Our system needed to be:

• A web application. Our client is a worldwide company, so their system needed to be easily accessible around the world.
• Secure. This included restricting access to the system using two-factor authentication, full database encryption and VPN. Further, all reports that were generated needed to be encrypted.
• Able to drill down employee information by office and department.
• Able to automatically support and include different currencies. This is especially because our client needed to include the local currencies used by subsidiaries, as well as the GBP equivalent.
• Able to export data into XLSX and PDF formats.
• Optimised for iPads.
• Easy to share, and be edited, by others.
• Able to track the history of data.

The first steps

We knew that our project was time-sensitive and that it needed to be completed within three months. This gave us a useful framework to plan our work together, and achieve our client’s goals. Firstly, we analysed the input data and suggested that our client made the system universal so that each office had equal access. We also built a list of employees, which included both the salary and pension information. Interactive mock-ups were also created for our client to visualise what the UX would look like, ensure that the solution would meet their needs and that they would be satisfied.

The design

We divided our system into some modules. These include:

• The employee list; a list of all employees of the company, working in all of our client’s offices. This includes those who are still working and those who had left the company. This list contains basic employee details. It also contains salary information such as proposed bonuses, basic salaries, and proposed salaries. Lastly, this list includes pension details. Users can define the pension scheme opted into by each employee by selecting the appropriate choice from a predefined list.
• A ‘Summary’ page, summarising information about how budgets are being used by the company. This includes a drill-down option.
• A ‘Reports’ section, where reports can be generated.
• A ‘Settings’ section, enabling our clients to manage and organise their offices, departments, currencies, and financial years.

The development phase

Once our proposed system gained our client’s approval, we initiated the development phase. The whole process of development took about two months. We chose to adopt Scrum as a methodology framework. This allowed us to deliver and gather feedback as soon as we could. The most challenging aspect of developing our system was migrating historical data, as its format was inconsistent. We utilised a specialised mapping service to help us with this, which allowed us to re-import data. This transformed our work, especially nearing the end of our project when we received an updated set of spreadsheets with the latest information.

A story of success

We successfully delivered our project on time, within the three months we were given, despite the Christmas break. The system met our client’s needs. It improved the efficiency and reliability of their staff management responsibilities. Crucially, it has allowed our clients to focus on their business, rather than wasting time on management issues.

Project facts

• Team – Developers 3 (Team Leader 1, Backend dev 1, Angular dev 1), DevOps 1, QA 1, BA 1, PM 1
• Project management – Scrum for production and Kanban for maintenance
• Technology – Asp.Net Core, EntityFramework Core, MS SQL, Angular, Metronic, Bootstrap, Duo Mobile