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.
Eligible employees are those that have not missed 14 consecutive days in a claim period. This is difficult for Easypay to determine and is something that you will need to manually check. 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' in this column for employees that are EI exempt.
If the employee is not EI Exempt, Easypay will enter a 'Yes' 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 of Jan. 1 to Mar. 15, 2020.
*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 and Example 3 below.
Easypay’s 75% Canada Emergency Wage Subsidy Report / Export
From the Easypay menu, select: Reports > Wage Subsidies (COVID-19) > 75% Emergency Subsidy Report
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 (Jan. 1 to Mar. 15, 2020) 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 Jan. 1 to Mar. 15, 2020 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 of Jan. 1 to Mar. 15, 2020. 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 period 2 to 12 are used to calculate the average pre-crisis gross pay. Unless some large year-end bonuses were included in pay period 1 of 2020, having a few days from 2019 in pay period 1 would not significantly affect the average. It probably would have been acceptable to use pay period 1 to 12 to calculate the average pay (January 1 to March 15), but we choose to start at pay period 2.
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 13, 14, 15 and 16. 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. In this example, the bi-weekly pay periods 7 and 8 correspond to earnings during March 15 to 28 and March 29 to April 11, respectively. Pay periods 1 to 6 will be used to calculate the pre-crisis average gross pay. Pay period 7 gross pay will be entered into 2-week period 1 of the claim period. Pay period 8 gross pay will be entered into 2-week period 2 of the claim period.
Example 3: Bi-weekly - Not aligned - Prorate option
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 for Jan. 1 to Mar. 15, 2020.
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”). All other employees, who are not EI exempt, are flagged as being at arm’s length (“Yes”).
- 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.
- 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. If they have 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), and
- who did not miss 14 consecutive days during the claim period,
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 75% subsidy. This should be discussed with your accountant to ensure that your company is eligible for the wage subsidy.