Overview of the CRA's calculation spreadsheet
Ultimately, our objective is to obtain data that can be used to populate the CRA’s calculation spreadsheet. The spreadsheet requires certain information in order to calculate the amount of subsidy you can claim.
The CRA spreadsheet is available here: CRA: Canada Emergency Wage Subsidy (CEWS)
We are unable to offer extensive support on how to use Excel or how to import a file into a spreadsheet. However, this document does provide instructions on how to copy and paste data from Easypay into the CRA’s spreadsheet.
The CRA has updated employee eligiblity. Please see: CRA: CEWS - Which of your employees qualify
For claim periods 1 to 4, you can’t include employees who had 14 or more consecutive unpaid days in the period. However, for periods 5 and later, you no longer have to exclude these employees from your calculation.
It is difficult for Easypay to determine if an employee had 14 or more consecutive unpaid days in a period. This is something that you will need to manually check for claim periods 1 to 4. After your data has been entered into the spreadsheet, you will need to review each employee to ensure that they are eligible. For example, if an employee’s gross pay is zero in two consecutive weeks of the claim period, then that employee may have missed 14 consecutive days.
Employee at arm’s length
Easypay will use the EI Exemption flag to determine what to put in this column.
If the employee is EI exempt, it is assumed that they are not at arm’s length (owners, spouses of owners, their children, etc.). Easypay will enter a No - non-arm's length in this column for employees that are EI exempt.
If the employee is not EI Exempt, Easypay will enter a Yes - arm's-length in this column.
Please review and manually adjust this column if an employee’s arm’s length status differs from what Easypay has entered.
Average weekly gross pay
This is the average weekly remuneration* (gross pay plus certain taxable benefits) for the employee during the pre-crisis period.
*Remuneration: Please read the CRA’s definition for exactly what should be included in remuneration. Remuneration includes gross pay and certain taxable benefits. For details, please see: CRA: Frequently asked questions - Canada emergency wage subsidy
Claim Period - Weekly Gross Pay (Weeks 1 to 4):
In many scenarios, Easypay can fill in the information required for the four weeks in the claim period. Where there is insufficient data in the payroll system, it might be necessary to manually enter the information for the four weeks. Please continue reading below to determine if the weekly amounts can be filled in automatically for your pay types and frequencies.
Employees Paid Bi-weekly (every two weeks)
data from Easypay into the CRA’s spreadsheet.
The CRA spreadsheet includes a Bi-weekly worksheet that allows you to enter information for two 2-week periods instead of four individual weeks.
The 2-week period 1 column should contain the employee’s remuneration for the first two weeks of the claim period (for example, March 15 to March 28). The 2-week period 2 column is for the second two weeks of the claim period (for example, March 29 to April 11). If your pay periods do not align exactly with those dates, the Easypay report includes an option to use partial pay periods and prorate to calculate the earnings for the two 2-week claim periods.
The CRA has indicated that the Bi-weekly worksheet option is intended for bi-weekly employees whose earnings are fairly consistent from week to week. If the employee has large fluctuations in earnings from week to week, the CRA would prefer that the Weekly (52) option be used. We realize that weekly information may not be readily available for bi-weekly employees and that the bi-weekly method may be the only feasible option. Please check with your accountant to see if this reporting method is acceptable for your particular situation.
The best way to explain how to use the bi-weekly option is by using examples. Please see Example 2 below.
Easypay’s Canada Emergency Wage Subsidy (CEWS) Report / Export
From the Easypay menu, select: Reports > Wage Subsidies (COVID-19) > Canada Emergency Wage Subsidy (CEWS)
The first tab allows you pick the sort sequence, range of employees, etc. You can include one or all pay types. However, only one pay frequency (weekly, bi-weekly, etc.) is allowed per report or file export. You may need to run the report or file export several times if you have multiple pay frequencies.
Select Report Type / Pay Periods
Employees Paid Weekly or Bi-Weekly
For employees that are paid weekly or bi-weekly, choose Report option #2.
Easypay can calculate the pre-crisis average gross pay with reasonable accuracy for all frequency types.
For the bi-weekly pay frequency, you can use the Average Bi-weekly calculation option (recommended) or the Average Weekly option. For all other pay frequencies, you must use the Average Weekly calculation option.
Enter the pay periods that should be used to calculate the Average Weekly remuneration for the pre-crisis period.
For the weekly pay frequency, enter the weekly pay periods that correspond to the 4 weeks in the claim period.
Please refer to the examples below.
All other pay frequencies
For all other pay frequencies (semi-monthly, monthly, 10 and 13 pays per year), you will need to use Report option #1.
If you will be using the Export File tab to create an export file, there is an option to automatically copy the pre-crisis average weekly gross pay into Weeks 1, 2, 3 and 4. This option is applicable if the employee’s salary was the same during the pre-crisis period and the claim period.
For example: An employee earns $52,000 per year, with an average gross pay of $1,000 per week during the pre-crisis period. If that employee has remained at the pre-crisis salary during the claim period, then the gross pay for each of the 4 weeks in the claim period should be $1,000.
If there have been adjustments to the employees' remuneration (other than the weekly and bi-weekly pay frequencies) during the pre-crisis or claim period, you will need to manually enter the gross pay for the 4 weeks of the claim period into the spreadsheet.
Example 1: Weekly Payroll
For this weekly payroll example, pay periods 27 to 53 in 2019 are used to calculate the average pre-crisis gross pay.
Using Report option #2, the report and the export data will include the employee name, employee arm’s length (Yes/No), pre-crisis average gross pay and the 4 weeks for the claim period. Weeks 1, 2, 3, 4 of the claim period will be equal to the remuneration earned by the employee in pay periods 27, 28. 29 and 30. This information can be printed in a report or exported to a file.
Example 2: Bi-weekly - Aligned with CRA’s two 2-week periods
For this bi-weekly payroll example, the pay periods align with the CRA’s two 2-week periods in the claim period. Pay periods 1 to 6 in 2020 will be used to calculate the pre-crisis average gross pay. Pay period 14 gross pay will be entered into 2-week period 1 of the claim period. Pay period 15 gross pay will be entered into 2-week period 2 of the claim period.
Example 3: Bi-weekly - Not aligned - Prorate option
IMPORTANT! UPDATED CRA GUIDELINES:
Prorating pay periods DOES NOT appear to be an acceptable approach according to the CRA.
The CRA updated their guidelines after we released this report. The option to prorate pay periods was originally included in this report based on suggestions made by accountants that we had spoken to prior to the CRA's updated guidelines.
Here is part of what the CRA's updated guidelines say:
"If an employer’s payroll cycle does not align with the wage subsidy for the claim periods, they will have to do a manual calculation to reflect the remuneration paid in respect of that claim period. Employers will not be permitted to use an average of the daily wages paid. The eligible remuneration reported on an employer’s wage subsidy application must reflect the actual amount paid in respect of the claim period."
PLEASE READ paragraphs 26-1 and 26-2 at this link: CRA - Frequently asked questions - Canada emergency wage subsidy
For this bi-weekly payroll example, the pay periods DO NOT align with the CRA’s two 2-week periods of the claim period. In this example, payroll pay period 7 covers earnings from March 8 - 21, pay period 8 covers March 22 to April 4, and the last pay period 9 covers April 5 - 18.
In this example, 2-week period 1 (March 15 - 28) is calculated using 50% of each of the bi-weekly payroll pay periods 7 and 8.
A similar approach is used to calculate the 2-week period 2, using 50% of pay period 8 and 50% of pay period 9.
If prorating is not acceptable in your situation, the only other option is to fill in the weekly earnings manually on the Weekly (52) worksheet of the CRA spreadsheet. Your timeclock or data collection device may be able to provide weekly earnings - however, please remember that those would not account for taxable benefits that are eligible for the subsidy.
What percentages do I enter when using the prorate option?
Example: If the CRA’s 2-week period 1 covers 3 days from pay period 7 and 11 days from pay period 8, then calculate the percentages as follows:
3 / 14 = 0.2142 (Enter 21.42% for pay period 7)
11 / 14 = 0.7857 (Enter 78.57% for pay period 8)
Alternatively, you can divide the number of days worked in a period by the number of days in a work week. For example, employees at a company work Monday to Friday and the bi-weekly pay period is 10 working days. If one of the CRA’s 2-week periods covers 2 days from pay period 7 and 8 days from pay period 8, then calculate the percentages as follows:
2/10 = 0.20 (Enter 20% for pay period 7)
8/10 = 0.80 (Enter 80% for pay period 8)
Example 4 – Other Pay Frequencies
If your pay frequency is something other than weekly or bi-weekly, you can only run report option #1. This report will provide the employee name, employee arm’s length (Yes/No), and the pre-crisis average gross pay.
If employee salaries have NOT changed since January 1, 2020, there is an option on the report’s Export File tab that may be useful. If the option is turned on, the pre-crisis average weekly gross pay will be copied into Weeks 1, 2, 3 and 4 in the export file.
For any employees that have had a change in their salary since January 1, 2020, you will need to manually calculate and fill in Weeks 1 to 4 of the claim period.
Taxable Benefits tab
Certain taxable benefits might need to be excluded from the remuneration calculation, such as the personal use of a corporate vehicle. By default, Easypay includes all taxable benefits. To exclude a benefit, turn off the checkbox for that benefit. Before printing the report or creating the export file, please review the Taxable Benefits tab to confirm that the correct benefits are included. In the unlikely event that you add a new taxable benefit after you have run this report, you will need to update the taxable benefits tab in order for the benefit to be included.
For details, please see: CRA - FAQ CEWS - Eligible Remuneration
After you have made your selections and entered pay periods in the Report Type / Pay Periods tab, you can create the export file. Don’t worry, you can run this process multiple times if your first attempt doesn’t give you the desired results. There is no harm in experimenting with the different options.
The sample export file shown below is based on a weekly payroll, using the option to export data for Week 1 to Week 4 of the claim period.
Click the Create Export File button to create the file. Then click the Open File in Text Editor button. Your default text editor should open, displaying the contents of the export file. To copy the contents of the file to the clipboard, press Ctrl-A and then Ctrl-C.
Importing data into the CRA spreadsheet
Open the CRA spreadsheet and read the CRA’s instructions on the first page. Please also familiarize yourself with the various worksheets.
In this example, for a weekly payroll, we will use the Weekly (52) worksheet. The first step is to click on the first row of the Eligible employees column, cell B6. If you have already copied the contents of the export file to the clipboard, you can press Ctrl-V to copy those contents to the spreadsheet.
If the result of the copy and paste from the clipboard into the spreadsheet does not look correct, you can exit the spreadsheet without saving and try again after creating a new export file. If you need to start again with a fresh copy of the spreadsheet, you can always download a new copy from the CRA web site.
After you use copy and paste to import the data, the spreadsheet will automatically calculate the other cells to determine your subsidy.
Comments and Assumptions
There are certain assumptions that Easypay has had to make due to the data that is available in the payroll history. Please be sure to review the results after you export the data to the CRA’s spreadsheet.
The following are some of the things that we suggest you look at:
- Check your arm’s length employee status and adjust if necessary. Easypay uses the EI exemption flag to determine an employee's arm's length status. Easypay assumes that those that are EI exempt are owners, family, etc., and the program flags them as not at arm’s length (“No - non-arm's length”). All other employees, who are not EI exempt, are flagged as being at arm's length (“Yes - arm's-length”).
- Easypay will exclude pay periods with zero gross pay when calculating the pre-crisis average gross pay. Easypay cannot determine if an employee missed 7 consecutive days without missing a pay period. For example, if an employee missed one week of a bi-weekly pay period, Easypay would not be aware of this and a manual adjustment of the pre-crisis average may be required.
- UPDATE: For claim periods 1 to 4, you can’t include employees who had 14 or more consecutive unpaid days in the period. For periods 5 and later, you no longer have to exclude these employees from your calculation.
If the employee misses 14 consecutive days in one of the claim periods (example, March 15 to April 11), Easypay will not exclude them from the subsidy report. We recommend scanning the spreadsheet row by row. If you find a weekly employee with two consecutive pay periods with zero gross pay, or a bi-weekly employee with a pay period with zero gross pay, then you may wish to investigate that employee further. For claim periods 1 to 4, if an employee has missed 14 or more consecutive days, the employee is not eligible for the subsidy and you should remove that row from the spreadsheet.
After much deliberation, discussion, and phone calls to the CRA and accounting firms, we have made the assumption that Weeks 1 to 4 of and the two 2-week claim periods pertain to the remuneration earned by the employees in those weeks, not the amount of payment that the employee received (pay cheques) in those weeks.
For any new employees that:
- are not weekly or bi-weekly,
- who started on or after March 15, 2020,
- are at arm’s length (not owners or family)
you will need to manually enter the gross pay for Weeks 1 to 4 of the claim period.
Please ensure that you have met all the criteria for eligibility and reduction in revenue in order to qualify for the Canada Emergency Wage Subsidy. This should be discussed with your accountant to ensure that your company is eligible for the wage subsidy.