A fundamental payroll responsibility is to ensure that year-end slips are completed with 100% accuracy. It’s hard to imagine how the checking and balancing required to meet this responsibility could be done without mastering Excel fundamentals. Here’s a best practice strategy for this balancing and checking, that minimizes the Excel knowledge required.

The goal is to reconcile YTD values from your payroll registers with 3 other sets of values:

• The source deductions made during the year to the CRA and/or Revenue Quebec;
• The T4s or RL-1s you’re preparing to file; and
• The payroll expenses recognized in the company’s General Ledger.

We’re going to do this using an Excel spreadsheet that records report totals from each payroll register, as it’s produced during the year. Create one of these spreadsheets or Excel files for each tax year.

One key concept is to split these Excel files into separate ‘sheets’. By default, Excel names these ‘Sheet1’, ‘Sheet2’, ‘Sheet3’, etc., but were going to create and name these sheets as follows:

• ‘Register’, for the transaction values entered from each payroll register;
• ‘PD7A’, for the source deduction amounts remitted to the CRA;
• ‘T4’, for the T4 box totals we’ll match against your T4 slip and Summary totals;
• ‘Expense’, for the payroll expenses recognized in the company’s General Ledger.

If you’ve payrolls in Quebec, then you would add 2 other sheets, one for your ‘TPZ-1015’ remittances and the other for the ‘RL-1’ slips and summary. Similarly, you may need separate sheets if ‘T4A’ or ‘RL-2’ slips are required.

Enter payroll register totals as payrolls are run

Make it part of your payroll cycle, once a payroll is finalized, to enter the run totals into the Register sheet of the Excel file for the year concerned.

The Register sheet should have a separate column for each earning, deduction, benefit or employer contribution value you’re going to need for the other sheets listed above. How you do this will depend, in part on what information is available on your payroll registers.

Here’s one scenario, based on a payroll register that shows sub-totals for gross taxable, gross pensionable and gross insurable income. On the Register sheet, label columns A, B, and C as ‘Taxable’, ‘Pensionable’ and ‘Insurable’ respectively. Then, on the other sheets, you can build formulas that reference these values. For example, you can map the ‘Taxable’ column on the Register sheet to the ‘Box 14’ column on the T4 sheet.

However, despite the work, it’s probably going to be best to enter each needed earning, deduction, benefit or employer contribution value in a separate Register sheet column. This will make it easier to map Register values to the Other Information codes used on the T4. Make each of your transaction codes the column heading. For example, if ‘REGHRS’ is your earning code for regular wages, use this code as a column header.

I find it much easier if the 1^st sheet row is reserved for column header labels, and rows 2 onward are used for data values, one row for each set of payroll register values. Avoid blank rows as irregular data patterns will make sorting nearly impossible and will make it more difficult to build the formulas needed.

Build the formulas needed

To keep things simple, on the other sheets in the Excel file, we’re going to follow the same practice that one row represents the results of a single payroll register run. As such, each sheet in the file is going to have the same structure: the 1^st row is used for column headings, the 1^st payroll register is on row 2, the 2^nd payroll register on row 3, etc. With this structure, you can simply copy the formulas down, row by row as needed.

For example, assume we are building the formulas in the T4 sheet to reference the necessary Register sheet values. On the Register sheet, all gross earnings that are Box 14 reportable are in columns B through H, inclusive. On the T4 sheet, Box 14 is column B. To build the necessary formula for the 1^st pay period, copy the following into cell B2 on the T4 sheet: =sum(Register!B2:H2). If for example, you need to sum both gross earnings and taxable benefits into Box 14, and the taxable benefits are in Register columns N through Q, copy this into the B2 cell: =sum(Register!B2:H2, Register!N2:Q2). See how the comma character separates column or cell ranges in the Excel sum command.

Using this as your basic building block, you can map all of the Register sheet values needed for the columns on the T4, PD7A and Expense sheets.

Once you’ve built the formulas needed, sum each column on all sheets in the file. This allows us to do what in accounting is called cross-footing. In the example above, we used Register sheet columns B through H for gross taxable earnings, and columns N through Q for gross taxable benefits. On the Register sheet, sum the totals for these columns (i.e. =sum(B2:H27, N2:Q27), where there are 26 pays in the year). Then compare this value to the total of the T4 sheet B column used for Box 14. If you’ve done everything right, these should be the same.

Compare the Register, T4, PD7A and Expense sheet values

Once you’re satisfied that all sheet formulas are working correctly, now you’re ready to start balancing your payroll.

1. The first step is to compare the Register sheet column totals to the YTD values in your payroll system. Note that some payroll systems only show YTDs on the payroll register for employees with current values on that payroll register. If so, there is usually an option to print YTD values for all employees, even if this is a separate report from the payroll register itself.

If the Register sheet totals don’t match the payroll system YTDs, the most likely causes are that you’ve either missed a payroll register (i.e. an off-cycle run) in your Register sheet, you’ve made a typo when entering values off a register or payroll YTDs have been adjusted for a voided cheque or direct deposit, without adjustment on a register.

One way of handling such voided transactions, where payroll YTDs have been manually adjusted (i.e. not entered on a payroll register), would be to list them on a separate sheet, ‘Voided’, using the same structure as the Register sheet. If a direct deposit is voided in this way, enter the voided amounts as negatives. This will also mean adjusting the formulas used on the other sheets. Keeping with the same example as above, the T4 sheet formula for Box 14 gross earnings would now be: =sum(Register!B2:H2,Voided!B2:H2).

2. Balancing the Register sheet totals to payroll system YTDs proves the integrity of those payroll system YTD values. Once this balancing has been done, you’re ready to balance the other sheet values.

If your T4 sheet values don’t match the equivalent values on your year-end T4 reports, there are two possible causes, similar in nature. In the T4 sheet, the column formulas don’t sum the correct Register (or Voided) sheet columns or in your payroll system the similar mapping between earnings and deductions and T4 boxes isn’t defined correctly.

Some payroll systems sum T4 reporting values into ‘buckets’ as each payroll is run, based on the T4 mapping in effect at the time. In other words, T4 values are built every time pay is run, not at year-end. If this is how your system works and the setup for this mapping was changed mid-year, then the payroll system T4 values may not be correct.

3. Once you’ve balanced the payroll system’s T4 reports to the T4 sheet, now you’re ready to compare the source deduction remittance values on the T4 sheet to the YTDs shown on the year’s final PD7A statement. In other words, the remittance columns on the T4 sheet should match the equivalent YTD values on this PD7A statement. One possible reason for a discrepancy might be if the regular PD7A remittance forms were used to remit amounts other than for current source deductions. For example, you may have received a requirement to pay (or 3^rd party demand) from the CRA. Such amounts should be remitted separately, not on the regular PD7A remittance forms.

If your T4 sheet values don’t match the final PD7A statement, start comparing the PD7A sheet values to the equivalent remittance values on each PD7A statement. You don’t need to build formulas to do this. Hold and drag the cursor through the PD7A cells concerned (i.e. down the rows for each payroll in the remittance period). Excel will show the sum for the highlighted cells at the bottom of the page, in the far right.

4. After balancing the T4 or PD7A sheet values to the final PD7A statement, you’re now ready to balance to the wage expense in the General Ledger.

How you do this will depend on how these expenses make their way from payroll into your accounting system, so there’s no one way to describe this. One common technique is to expense gross pay, and any employer contributions for CPP, EI, etc., off of the payroll register, but to expense employee benefits via Accounts Payable, from payments to 3^rd party benefit providers. Such an approach works best if the payroll register and employee benefit costs don’t end up in the same General Ledger accounts.

If this is how payroll is expensed in your company, then you would build the Expense sheet columns to reflect gross earnings paid, plus any employer contributions for CPP, EI, QPP or QPIP.

If the Expense sheet totals don’t match the debit totals in your General Ledger wage expense accounts, look for entries in these expense accounts that don’t come from payroll. These could be voided cheques, where the corresponding adjustment to payroll system YTDs have not been made, or employee payments, not made through payroll, i.e. manual cheques.

Wrapping up

The steps above should be successfully completed before you start making any adjustments to T4 values for taxable benefits not processed in payroll, adjustments to automobile, loan or travel expense taxable benefit estimates, etc. The point is to ensure the accuracy of your payroll records, before you start making year-end adjustments proper.

Alan McEwen is a Vancouver Island-based HRIS/Payroll consultant and freelance writer with over 25 years’ experience in all aspects of the payroll industry. He can be reached at armcewen@shaw.ca or (250) 228-5280. Alan McEwen & Associates is currently offering a series of Vancouver Island payroll training seminars. For information on upcoming seminars, signup to our email list.