Payroll costs report: obtain the data of your choice
This article is intended for:
Module concerned:
This article explains how to enter HR data (departments, salaries, bonuses) in the employee record so that it is correctly included, and then how to export and interpret the payroll costs report over several years.
In this article:
- Enter the data
- Export the payroll costs report
- Why is a salary not included?
- Why is a bonus not included?
Enter the data
Access: Admin area > General settings > Employees and users
Enter the departments
Warning: a department must always be entered in the employee record for each period during which the employee was present in the company, so that all their data is included.
open your employee’s record
in the “Employee record” tab, add an item by clicking “+”
select “Department” from the “Category” drop-down menu
enter a start date and an end date if necessary
enter a label/description explaining, for example, the reason for the change
enter a value: the exact department label (your list of departments is available by exporting the departments in the Organisation tab of the company record)
save
Have you deployed the new employee record? Go to the Other HR data tab:
Enter salary-related data
All the data shown in the payroll costs report comes from the employee record. If data is missing, it should be added to the employee record of the relevant employee.
The salaries included in this report are those with the category “Gross monthly salary”.
The “Gross monthly salary” category refers to an item in the value list (Admin area menu) with the list type “userDirectoryCategory” and value code “monthlyGrossSalary”.
Warning: the “Monthly salary”, “Gross daily salary” and “Gross annual salary” fields in the employee record are not taken into account by this export.
Enter bonus data
All bonuses are retrieved from the employee record > Employment tab.
They can be entered directly in each employee record:
click “
”,
enter the label (which must be exactly identical to the description of the EVP field if this bonus is to be included in your EVP grid),
enter the start/end dates of the application period and the amount.
You can bulk import this data into employee records from Admin area > General settings > Manual data import:
- click “
New”,
- select the import type “Bonuses”,
- download the import template,
- complete your file:
- EMAIL_USER: employee’s email address (mandatory unless an employee number is provided)
- REF_USER: employee number (mandatory unless an email address is provided)
- LABEL: label (mandatory)
- START_DATE / END_DATE: start/end date of the application period (mandatory)
- AMOUNT: value
- then simply import your file.
- the “Cancel and replace all existing bonuses” box should only be ticked if you want to delete all the old data.
Export the payroll costs report
Access: HR portal > HR dashboard
- in the drop-down menu at the top left, select: Excel reports > Payroll costs
- a new interface is then displayed
- use the filters to select the data you want:
- period
- department
- organisation
- employees
- click “display”
- click “Excel export”
Warning: if you do not select a complete year, this will not be clearly visible in the report. For example, the columns relating to the annual salary will not change their label, even though they will not contain all the salaries for the year.
Calculation of the “prorated salary” column
The prorated salary is calculated by department, based on the periods of assignment to the department.
If an employee has a salary of €2,000/month throughout the year, but belonged to a given department for only two months, the prorated salary for the department will be €4,000.
Likewise, if they belonged to the same department throughout the year but the €2,000 salary is defined for only two months and there is no other salary during the year, the prorated salary for the department will be €4,000.
The following calculation is used to determine the salary amount for a period, bearing in mind that a period cannot span two years:
- S = monthly salary
- the activity percentage data comes from the employee record.
Let’s take an example with the following data:
The only salary that can be applied to Marc APITOLE is €2,581/month for the period from 06/11/2019 to 31/12/2019.
The calculation is therefore:
((2581 * (12 - 11 - 1)) + (2581 * ((30 - (6 - 1)) / 30)) + (2581 * ((31 / 31))) * (100 / 100) = €4,723.23
Note: the result of the divisions is rounded directly to two decimal places.
Calculation of the “non-prorated salary” column
The non-prorated salary is the salary for one year, regardless of whether the employee belonged to the same department throughout the year and whether one or more salaries are defined for the entire year.
The system therefore checks, for each department, whether salaries exist. There are three possible calculation scenarios:
- there is no salary corresponding to the department’s period during the year: the non-prorated salary amount will be 0
- there is only one salary corresponding (partly or completely) to the department during the year: the non-prorated salary amount will be 12 x the salary
- there are several salaries corresponding (partly or completely) to the department during the year: the amount will be calculated based on the defined dates (using the same calculation as for the prorated salary). If the whole year is not covered, the system will fill the gaps by applying the following or preceding bonus.
Example:
The “Marketing & Commercial” department is in case 1: there is no salary for its period, so the “Non-prorated salary” column will contain 0.
The “Production” department is in case 2: there is only one salary for its period, so the “Non-prorated salary” column will contain 30,972 (12 x 2,581).
For case 3, let’s take the following data:
Marc APITOLE’s employee record indicates that he has:
- a monthly salary of €2,000 from 05/10 to 24/10
- a monthly salary of €2,200 from 25/10 to 05/11
- a monthly salary of €2,581 from 06/11 to 31/12
The system will apply a salary of €2,000 from 01/01 to 24/10, because there is no salary before 05/10 and it fills the gap from 05/10 to 25/10 with the preceding salary.
The calculation is therefore:
For the €2,000 salary:
(2000 * 8) + (2000 * ((31 - (1-1)) / 31)) + (2000 * (24 / 31)) = 19,540
For the €2,200 salary:
(2200 * 0) + (2200 * ((31 - (25 - 1)) / 31)) + (2200 * (5 / 30)) = 880
For the €2,581 salary:
(2581 * 0) + (2581 * ((30 - (6 - 1)) / 30)) + (2581 * (31/31)) = 4723.23
Total: €25,143.23
Calculation of the bonus columns
The number of bonus columns varies depending on the company.
The system searches for all employee record data with the category “Bonuses” for the requested period. It then checks the “Description” field of all these items and creates one column per value.
You must therefore be careful not to make any typing errors when entering bonuses in the employee record.
Lastly, the column is completed for each user by adding together the bonuses by description, for the year and the department.
Note: if a bonus belongs to several departments, it will be assigned to all of those departments. For example, if Marc APITOLE worked in two different departments in 2019 and had a bonus for the whole year, the bonus will appear twice in the export.
Why is a salary not included?
For a salary to be included in the export, the employee record must have at least one entry with the category “Department” and, in “Value”, the exact label of a department.
If the salary is still not included, check that it has been correctly entered in the “Gross monthly salary” category.
Lastly, check that the “Value” field does not contain any letters.
Why is a bonus not included?
For a bonus to be included, it must fall within a period for which a department is defined in the employee record.
If the bonus is still not included, check that it belongs to the “Bonuses” category.
Lastly, check that the “Value” field does not contain any letters.
Keywords: payroll costs · export · Excel · bonuses · department · proration · HR dashboard · salary · budget
Comments
Article is closed for comments.