How to Calculate Average Salary for Employment Center in Excel

The report on average earnings for the employment center is submitted in any form (there is no approved form). Typically, a three-month period is taken as the calculation period. Only payments specified in employment contracts are considered. How can one calculate the average earnings for the employment center in Excel?

 

Calculation Period

To calculate, payments from the three calendar months preceding the termination are taken into account. For example, if an employee was terminated on October 10, 2023, calculations are made for the period from July 1 to September 30 of the same year.

The following periods are excluded from the calculation:

  • Days on business trips, paid vacations, and other periods when the employee was paid average rates;
  • Days when the individual was on sick leave or parental leave;
  • Days when the employee was absent from work for any reason, with or without pay.

Special Cases:

  1. The three months preceding the termination consist only of excluded days – the closest three calendar months with working days are considered.
  2. If the employee hasn't worked even for a month at the enterprise – the days worked from the first day to the termination date are taken into account.

Data to determine the boundaries of the calculation period are taken from the HR department (date of hiring, termination, timekeeping) and the accounting department.

Which Payments Are Included in the Calculation

All types of payments related to labor remuneration are considered. Social and other payments cannot be included:

  • Subsidized meals;
  • Reimbursement of transportation expenses, communication services;
  • Vacation pay;
  • Business trip allowances;
  • Payment for sick leave, additional days off for mothers of disabled children, etc.

Special notes on bonus accounting:

  1. Monthly – fully, one for each indicator. For instance, if two bonuses are awarded for the maximum number of contracts, only the one with the highest amount is included.
  2. Quarterly and semi-annual – as a monthly portion. One for each indicator.
  3. Annual (for the calendar year preceding the termination) – for each indicator (even if received outside the calculation period).

If the calculation period is less than three months or if there are excluded days, monthly bonuses are included in the calculation as usual. All others are proportional to the actual worked time. The calculation formula is:

Calculation of the bonus amount = bonus earned during the calculation months / number of working days in the calculation period (as per the schedule) * actual number of worked days.

Indexation of Payments

If there was a salary increase in the analyzed three-month period or after it but before the termination date, all earnings for the average wage calculation need to be indexed:

  1. If the salary increased during the analyzed months, payments from the months preceding the salary increase are indexed. For example, if someone left in mid-February, and salaries were raised from January 1, payments for November and December are indexed.
  2. If the administration increased the salary after the calculation period, the average wage is indexed. If an employee left in mid-January and the salaries were raised from January 1, the average earnings for the three-month calculation period are indexed.

Calculation formula for the raise coefficient:

New salary amount / Old salary amount.

How to Calculate Average Earnings for the Employment Center

Calculation formula:

AE = TP / NDW * (NDW / 3)

Where:

  • AE – average earnings;
  • TP – total payments included in the calculation;
  • NDW – number of days worked;
  • NDW – number of working days (according to the enterprise's schedule);
  • 3 – the number of calendar months preceding the termination.

Charlotte resigned from the organization on 1/31/2023, Evelyn on 3/31/2023, and Jacob on 7/31/2023. Now, let's create a calculator for calculating average earnings for the employment center in Excel. Then enter all necessary data into the blue cells of the Excel table and calculate the average earnings for the employment center.

Calculator.

US Manufacturing Calendar 2023 sheet with holiday dates:

Manufacturing Calendar.

Formula for calculating the average earnings for Dobrovolsky N.S., who has no worked days in the calculation period:

Formula.

download file Download an example of calculating average earnings for the employment center in Excel

You've now created a simple calculator for calculating average earnings for the employment center.


en ru