Pay spines (Employer): Salary formula

As part of the employer set up process for pay spines, you can define a formula to calculate the salary. This is optional, if no formula is entered the employees working pattern will be used. The formula is calculated in the same way as an Excel spreadsheet.

Variables:

The variable returns a value from the employee and employer configuration. As standard, the employer default values are used for the employee, however this can be changed on an employee by employee basis.

Item Description
POINTVALUE The values set in Spine Points
HOURSWORKED The number of hours worked
FTHOURS The full time hours value
WEEKSWORKED The number of weeks worked
FTWEEKS The full time weeks value
BASEHOLIDAY This will be the value of the pay spine base holiday.
GRADEHOLIDAY This will be the value of the pay grade holiday entitlement amount.
SERVICEHOLIDAY This will be the value of the pay spine service entailment with total additional days holiday. Based on the years service calculation.
ALLHOLIDAY This will be the value of BASEHOLIDAY, GRADEHOLIDAY & SERVICEHOLIDAY added together.

Functions to use:

Item Description
ROUND Round the value.
ROUNDUP Round the value up, no matter the value.
ROUNDDOWN Round the value down, no matter the value.
TRUNC Truncate the value and ignore all rounding.
MIN(x, y) Returns the minimum value from the list.
MAX(x, y) Returns the maximum value from the list.

Example...

Example 1

POINTVALUE*(HOURSWORKED/FTHOURS)*(WEEKSWORKED/FTWEEKS)

28001 x ((37 / 37) x (43.6 / 52.143)

28001 x 0.6 x 0.836162 = 23413.37

The formula POINTVALUE * (HOURSWORKED / FTHOURS) * (WEEKSWORKED / FTWEEKS) is being used to calculate the total payment received by an employee, based on their hourly wage.

  • (POINTVALUE)

  • (HOURSWORKED): The number of hours they worked during a specific period

  • (FTHOURS): The standard hours per week.

  • Additionally, the formula takes into account the number of weeks they worked (WSWORKED) and the total standard number of weeks they were expected to work (FTWEEKS).

In the given scenario, the calculation is based on an employee with an annual salary point value of £28,001. The employee worked a total of 37 hours during a week, in which the standard hours required were also 37. However, the employee worked for a total of 43.6 weeks, compared to the usual 52.143 standard working weeks. When the calculation is applied, the result is a total payment of £23413.37.

Example 2

POINTVALUE*ROUND((HOURSWORKED*WEEKSWORKED*(7/365)),3)/FTHOURS

28001 x ROUND (( 37 x 43.6 x (0.019178),3) / 37

28001 x 30.938 / 37 = 23413.38 (1p rounding difference)

The formula POINTVALUE * ROUND((HOURSWORKED * WEEKSWORKED (7/365)),3) / FTHOURS is being used to calculate the total amount of payment an employee is owed based on their hourly rate.

  • (POINTVALUE)

  • (HOURSWORKED): The number of hours they worked during a specific period.

  • (WEEKSWORKED): The number of weeks they worked during that same period.

  • (FTHOURS): The standard hours per week.

In this scenario, the calculation based on an employee with an annual salary point value of £28,001 who worked for 37 hours per week. They worked for a total of 43.6 weeks during the year, which is converted to 160.27 days or 0.439178 years, using the formula WEEKSWORKED * (7/365) to determine the proportion of the year that the employee worked. By rounding this to three decimal places, the total working time is calculated to be 0.439.

Substituting these values into the formula gives 28001 x ROUND((37 x 43.6 x (7/365)),3) / 37. This simplifies to 28001 x 0.439 / 37 x 160.27 = £23413.38`, which is the amount an employee with the given hourly rate would be owed for working the specified number of hours and weeks. The difference of 1 penny can be attributed to rounding.

Example 3

POINTVALUE*ROUND((HOURSWORKED/FTHOURS)*(WEEKSWORKED/FTWEEKS),4)

28001 x round((37 / 37) x (43.6 / 52.143),4)

28001 x 0.8362 = 23414.44 (£1.07 rounding difference)

The formula POINTVALUE * ROUND((HOURSWORKED / FTHOURS)(WEEKSWORKED / FTWEEKS),4) is being used to calculate the total earnings of an employee based on their hourly wage.

  • (POINTVALUE)

  • (HOURSWORKED): The number of hours they worked during a specific period.

  • (FTHOURS): The standard hours per week.

  • (FTHOURS): The standard weeks in a year.

The formula calculates the ratio of the hours the employee worked to the standard full-time hours and multiplies it with the ratio of weeks the employee worked to the standard full-time weeks. This ratio is rounded to the fourth decimal place, and then multiplied with the point value to get the total earnings.

In the given example, the calculation is based on an employee with a annual salary point value of £28,001 who worked for 37 hours in a week and worked for a total of 43.6 weeks in a year, where the standard full-time schedule is 37 hours per week and 52.143 weeks in a year.

The computed result of the formula is 323414.44. It is worth noting that there is a £1.07 rounding difference, which is likely due to rounding during the calculation.

Example 4

POINTVALUE*TRUNC((HOURSWORKED/FTHOURS)*(WEEKSWORKED/FTWEEKS),3)

28001 x trunc((37 / 37) x (43.6 / 52.143),3)

28001 x 0.836 = £23408.84 (£-4.53 rounding difference)

Using the formula POINTVALUE * TRUNC((HOURSWORKED / FTHOURS)*(WEEKSWORKED / FTWEEKS),3), we calculate an employee's total earnings based on their hourly wage.

  • (POINTVALUE)

  • (HOURSWORKED): The number of hours they worked during a specific period.

  • (FTHOURS): The standard hours per week.

  • (FTHOURS): The standard weeks in a year.

(POINTVALUE), the number of hours they worked in a specific period (HOURSWORKED), and the predetermined full-time hours and weeks (FTHOURS and FTWEEKS).

The formula calculates the ratio of the hours the employee worked to the standard full hours and multiplies it with the ratio of weeks the employee worked to the standard full-time weeks. This ratio is truncated, or cut off, to three decimal and then multiplied with the point value to get the total earnings.

In the given example, we have an employee with a point value of 28001 who worked for 37 hours in a week and for a total of 43.6 weeks in a year, where the standard full-time schedule is 37 hours per week and 52.143 weeks a year. Plugging these values into the formula, we get 28001 x trunc((37 / 37) x (43.6 / 52.143),3). Simplifying this expression, we have 28001 x 0.836 = 23408.84.

Therefore, this employee's gross earnings would be £23,408.84. However, because of rounding, the actual amount may differ slightly in practice. In this case, there is a £-4.53 rounding difference.

Good to know...

Related Topics Link IconRelated topics: