The CEWS provides a subsidy of 75% of employees’ wages (up to $847 per week per employee) for up to 12 weeks (from March 15 to June 6, 2020) for individuals, corporations, charities, and partnerships who have seen a 15-to-30% decrease in revenues. For answers to a lot of the frequently-asked questions about the CEWS, check out this blog post by LiveCA’s Kim Slater.
The Canada Revenue Agency (CRA) website provides steps for calculating the subsidy here. The CRA included an Excel template for calculating the eligible amounts. At first glance, this file seems pretty helpful. However, when you look at the Instructions and results tab, you’ll see a recommendation to use the Weekly (52) tab and “adjust your payroll figures accordingly if you use an alternative pay period, such as monthly.”
The calculator will only calculate your company’s eligible amounts if you run weekly or bi-weekly payroll. If you run monthly or semi-monthly payroll, you’re out of luck.
So, how frequent are these “alternative pay periods”? Among LiveCA’s clients, 71% run either monthly or semi-monthly payroll, with 88% of those clients running semi-monthly payroll. This means the CRA’s calculator is useful for 29% of our clients.
Your first thought might be something along the lines of “so what? Just convert your semi-monthly to weekly and use the calculator” — mine was. Then I started digging into the calculations that are required for this conversion, and, well, it’s a lot of math. The 12 eligible weeks span seven semi-monthly pay periods, and the semi-monthly pay periods can consist of either 15 or 16 days:
[If you’re thinking about stopping reading this already, I don’t blame you. But, if you’re responsible for your company’s CEWS application, give LiveCA a shout and we’ll see if we can help you out.]
The above means we need to calculate the weekly pay as: the semi-monthly pay divided by the number of days the employee was employed for during the semi-monthly pay period multiplied by the number of days the employee was employed for during the week.
For weeks that span multiple pay periods, we’ll need to split this out between pay periods. For example, week 3 (from March 29 to April 4) pay is calculated as 3/16 of semi-monthly period 2 (the pay run ending March 31) and 4/15 of semi-monthly period 3 (the pay run ending April 15).
If the employee was not employed for the entire semi-monthly pay period or the entire week, you have to further prorate the pay. For example, if an employee worked every day up to and including, but was terminated on March 30, their week 3 pay would be calculated as 2/15 of the pay period ending March 31.
This all means you have to calculate your way around 19 different date changes:
Oh, and the penalties for miscalculating the subsidy may be very significant, if the 25% penalty for an ineligible claim is anything to go by.
I spent most of a full day building a Google Sheets template that will automate the conversion. Following are all of the steps required to create this template. There are a lot.
To perform the calculations, we’ll need the following data from our payroll system:
• A list of all employees, containing their names, hire dates, and termination dates (if applicable)
• A list of every pay type and corresponding amount paid to each employee, dating from January 1st, 2020, to our most recent pay run
For these examples, I’ll be using Wagepoint data, as that’s the payroll system used by the majority of our clients. The calculations would be similar if you’re using QuickBooks Online (QBO) Payroll, Payworks, Rise, Humi, Wave, SimplePay, Knit, Deluxe or any of the other payroll apps used in Canada.
In Wagepoint, the employee list is called the Employee Details Report and it can be downloaded by clicking Reports -> More -> Reports Library -> Employee Details Report.
Wagepoint’s Payroll by Cycles report contains everything else we need. It can be found under Reports -> More -> Reports Library -> Payroll by Cycles.
First, we need to build a list of employees who were employed during the claim periods (from March 15 to June 6, 2020). Wagepoint’s Employee Details Report contains all employees, including those terminated before the current year.
After creating a new Google Sheets file, in a tab named EmployeeDetails, we paste the Employee ID, Status (because this field contains a termination date), and Hire Date fields.
Using the value in the Status field, we can calculate the termination date, if applicable:
.png)
In a new tab named PayrollByCycles, we paste the contents of Wagepoint’s Payroll by Cycles report:
In another new tab, named Mapping, we can pull all the unique pay element types from the PayrollByCycles tab:
To calculate the “eligible remuneration” for section 2(c) of the CRA’s online calculator, we’ll need to separate the types of pay that are eligible for the subsidy in a new list:
.png)
Same thing with eligible premiums, which are used in section 3 of the CRA’s online calculator:
In a new tab, named Calculation, we can then pull all of the employees who were paid in the 2020 calendar year and remove the ones who were terminated before March 15, 2020, the first day of CEWS eligibility. We want each employee’s ID, full name, hire date, and termination date (if applicable). After we’ve added that data, we’ll flag each non-arm’s-length employee, since their eligible amounts are calculated separately:
.png)
We’ll then pull the total of the eligible remuneration element types for each employee for each semi-monthly pay period. My formula covers up to 30 possible element types, which is very likely overkill:
We’ll also calculate the number of days each employee was employed for during each semi-monthly pay period:
And the number of days each employee was employed for during each week:
Using these numbers, we can calculate the average weekly gross pay between January 1 and March 15, 2020, which is factored into the eligibility calculations later on:
Finally, we have what we need to calculate the weekly gross pay.
As briefly discussed above, we need to factor in the following for each week:
• Does the week span multiple pay periods?
• How many days was the employee employed for during the week?
• How many days was the employee employed for during the semi-monthly pay period?
• Which days was the employee employed for during the week?
• Which days was the employee employed for during the semi-monthly pay period?
• Which dates ranges do we need to calculate separately within the week?
• How much was the employee paid during each semi-monthly pay period the week spans?
With all those factors considered, we can calculate each week’s gross pay (with what is likely a very long formula for the weeks that span multiple pay periods — or, alternatively, by breaking out all 84 days contained in the claim periods to individual columns and then adding those up):
Lastly (for this tab), I like to add a check figure to compare our “Data for CRA Calculator” section with the “Data from Wagepoint” section to make sure everything’s calculating correctly:
We can duplicate this tab and use it as a base for our CPP/QPP and EI/QPIP calculations. One thing we’ll need to add is a flag to mark the specific full weeks each employee was furloughed but still being paid for:
We should only calculate the amounts if the employee was furloughed for the full week:
Now that we have our weekly gross wages, we can calculate the eligible amounts for the CEWS. This is based on the weekly gross wage amount and whether or not the employee has an arm’s-length or non-arm’s-length relationship with the company. The maximum eligible amount for each employee each week is the lower of $847 and 75% of the employee’s gross pay:
We can perform the same calculations for the eligible CPP/QPP and EI/QPIP amounts:
And finally, we can summarize the eligible amounts in fields that can be pasted into the CRA’s online calculator:
After plugging these numbers into the CRA’s online calculator, we can calculate our eligible CEWS claim:
The above is all the extra work required to convert semi-monthly pay to weekly pay. Without performing these steps, we would be unable to file an application for the CEWS.
After creating the template, using it for a client is as simple as:
1. Making a copy of the template Google Sheet
2. Exporting reports from your payroll system
3. Pasting your payroll data into the Google Sheet
4. Compiling a list of eligible remuneration types
5. Flagging any non-arm’s-length or furloughed employees
6. Entering the resulting data in the CRA’s online calculator
Once you’ve got a handle on these steps, the process should take about ten minutes to complete.
It’s entirely possible the CRA rights their wrong and provides a working calculator for semi-monthly pay periods, making the above obsolete. But, until then, we’ll be using our template.
