How to calculate average earnings during layoffs in Excel
For many enterprises, reducing staff or even an entire team is the only way to "stay afloat" during challenging crisis times. However, it's essential to lay off employees correctly. They are entitled to receive all outstanding wages, compensation for unused vacation, and severance pay.
Let's explore how to calculate the average earnings for severance pay in Excel.
Calculation of Average Employee Earnings in Excel During Staff Reduction
An employee subject to staff reduction receives severance pay equal to their average monthly salary. The average monthly salary remains for the duration of employment but not exceeding two months from the termination date. The calculation of the average earnings for severance in the second month is made without considering the already paid severance pay.
The calculation period typically includes the 12 months preceding the date of termination of the employment contract. For instance, if an employee is terminated in March 2016, the calculation period is from March 1, 2015, to February 28, 2016.
If an employee has been with the company for less than a year, the worked time is considered.
Included Payments
The calculation includes:
- Salary (hourly or per-task pay of the employee);
- Incentive payments (bonuses, allowances, incentives, etc.);
- Compensation payments (reimbursing the employee for expenses related to job duties).
Exclusions:
- Vacation pay;
- Sickness benefits;
- Compensation payments for unused vacation and other monetary compensations unrelated to job performance.
How to Calculate Average Earnings When Reducing Staff
An employee is terminated due to staff reduction on March 14, 2016. The termination date is the last working day. The company's schedule is a five-day workweek.
Calculation Period and Number of Working Days
In the calculation period, the employee was sick for 5 days in February. In May 2015, the employee took a 14-day vacation. The number of days worked by the employee was:
The payments considered in the average earnings calculation for staff reduction are specified in the range of cells D2:D13.
US Manufacturing Calendar for accounting for holidays in calculations:
Formulas for Calculating Average Earnings When Terminating an Employee
We will calculate the average daily earnings for severance pay:
The first month following the termination date falls between March 15 and April 14. Within this period, there are 22 working days.
The severance pay for the first month amounts to:
To receive the average monthly earnings for the second month following termination, the employee must present a copy and the original (for verification) of their employment record to the accounting department. The accountant verifies whether the terminated employee has found new employment. Based on existing records, severance pay for the second month is calculated.
The average daily earnings remain the same. Compensation already paid post-termination is not considered. The calculation period is from April 15 to May 14, amounting to 18 working days.
The severance pay for the second month is:
Download an example of calculating average earnings during staff reduction
To determine the average earnings when terminating due to staff reduction for the third month, one must provide a certificate from the employment center confirming registration and job loss.
