When preparing returns from poor or damaged corporate records, several forms of data reconciliation are required. As previously described, the spreadsheet-based method is a useful tool for preparing certain data reconciliations.

In its first form of reconciliation, the spreadsheet-based method focuses on the preparation of financial statements, specifically the income statement and balance sheet. Financial statements prepared with the spreadsheet method may then be used to complete the required T2 corporation tax returns.

In addition to Form T2, two other returns are commonly required to be prepared by the corporation: GST/HST, and T4 summary payroll returns. Depending on the corporation’s filing requirements, preparation of either return may require that data be reconciled into different time periods. For example, common periodic reconciliations include those for monthly, quarterly, or annual fiscal and/or calendar year periods.

Last resort method

Before we get started, it is important to remind ourselves that the spreadsheet-based system – by necessity – follows the cash method of revenue recognition. Revenue data is extracted from the corporation’s bank statements and, due to this limitation, does not take into account other revenue accrued during the reporting period.

As such, the spreadsheet-based method is not in compliance with GST/HST reporting guidelines as described on the Canada Revenue Agency’s (“CRA”) website.

You are liable for the GST/HST you charge on property or services on the day you receive payment or the day the payment is due, whichever is earlier. We usually consider payment to be due on the date you issue an invoice or the date specified in an agreement, whichever comes first.

In other words, CRA instructions for GST/HST reporting require that corporations follow the accrual method for line 101 revenue reporting, line 103/105 GST/HST collected or collectible, and line 106/108 GST/HST paid or payable. For this reason, it is important to stress that the spreadsheet-based method should be used for GST/HST reporting as a last resort only.

Line 101 – Total sales and other revenues

Line 101 of the GST/HST return reports to the CRA the corporation’s total sales and other revenues for a specified filing period.

If the corporation reports to the CRA annually, the GST/HST line 101 revenue amount may be extracted directly from the same period’s adjusted trial balance. For our fictional corporation, the Revenue row, Final CR column of Example 1 shows a total revenue amount of $100,000. As such, for the corporation’s annual GST/HST return, line 101 would be reported as $100,000.

Example 1 – Adjusted Trial Balance

Note that, in almost all cases, adjusted trial balance revenue should also match revenue as reported on the statement of income. Both amounts should be net of GST/HST collected (see Example 2, below).

Example 2 – Statement of Income

An all too common GST/HST reporting error occurs when the taxpayer includes GST/HST collected in line 101. Therefore, it is important to stress that GST/HST return line 101 does not included GST/HST collected (unless the taxpayer has elected to use the “quick method“).

For example, if the corporation collects $113,000 in combined revenue and HST ($100,000 in revenue and $13,000 in HST), then the GST/HST return should show line 101 of $100,000 and line 103/105 of $13,000. The error occurs when the taxpayer reports the line 101 amount as $113,000 – the CRA then expects line 103/105 to be precisely 13%^[1] of $113,000 and not 13% of $100,000.

In certain circumstances, the adjusted trial balance revenue amount may have to be adjusted for irregular transactions that occurred during the GST/HST reporting period. Irregular transactions may include the sale of corporate assets or refunds to customers.

As revenue adjustments take place before the adjusted trial balance stage, the reader is cautioned that line 101 revenue figures should not be extracted from any spreadsheet-based method worksheet prior to the adjusted trial balance.

Line 103/105 – GST/HST collected or collectible

Line 103/105 of the GST/HST return reports to the CRA the amount of excise tax collected or collectible by the corporation for a specified filing period.

Using the spreadsheet-based method, individual excise tax amounts collected are first calculated in the bank statement spreadsheet ledger, then summarized in the bank statement spreadsheet ledger summary.

For our fictional corporation, the Revenue row, CR, GST/HST column of Example 3 shows total GST/HST collected of $13,000. Note that the TOTAL row, CR, GST/HST column may be a better source for GST/HST collected if revenue is divided into multiple categories on the summary worksheet, or if corporate assets are sold during the filing period.

Example 3 – Bank Statement Spreadsheet Ledger Summary

One additional step is required to determine the corporation’s line 103/105 GST/HST collected amount. If the corporation issued customer refunds during the reporting period, GST/HST line 101 revenue must be reduced by the net amount refunded. Similarly, line 103/105 must be reduced by the GST/HST amounts refunded. These adjustment amounts may be extracted from each of the Revenue row, DR, Net and GST/HST columns of the bank statement spreadsheet ledger summary (not applicable for Example 3 data as amounts are nil).

Since the fictional corporation did not issue customer refunds in the reporting period, no line 103/105 adjustments are required. As such, the corporation’s line 103/105 GST/HST collected amount is calculated as $13,000.00.

Extra example

As the data scenario provided in Example 3, above, does not included refunded amounts, let’s imagine a situation where a refund of $1,130 including HST was provided to a customer during the reporting period. For this situation to be applicable, the corporation must have both (a) deposited the revenue received into its corporate bank account, and (b) refunded the amount to the customer from the same bank account. Given this scenario, the following calculations provide us with the correct line 101 and 103/105 reporting amounts.

To calculate line 101 revenue:

Revenue (net) Before Refunds	100,000.00
Less: Refunds	1,000.00
Total Line 101 Revenue	99,000.00

To calculate line 103/105 GST/HST collected:

GST/HST Collected Before Refunds	13,000.00
Less: GST/HST Included in Refunds	130.00
Total Line 103/105 GST/HST Collected	12,870.00

To check the accuracy of your work remember that, in most cases, Line 103/105 should be equal to 13% of Line 101 (5% in GST-only provinces). For instance, $12,870 is 13% of $99,000.

Line 106/108 – GST/HST paid or payable

Line 106/108 of the GST/HST return reports to the CRA the amount of excise tax paid or payable by the corporation during a specified filing period.

As with line 103/105, individual excise tax amounts paid out are first calculated in the bank statement spreadsheet ledger, then summarized in the bank statement spreadsheet ledger summary.

For our fictional corporation, the TOTAL row, DR, GST/HST column of Example 3, above, shows the total GST/HST paid out by the corporation of $1,188.41. This “paid out” amount is then adjusted for the following two items:

1. Less: GST/HST calculated on returned items (e.g. NSF cheques, debt card purchase refunds, bank fee refunds, etc.), if any; and,
2. Add: GST/HST calculated on purchases made from the shareholder’s personal accounts for verifiable corporate business expenses, if any.

To determine the adjustment amount for category “1.” items, the bank statement spreadsheet ledger summary CR rows should be reviewed to check if the corporation had any “Returned Items” during the filing period.

In Example 3, above, the corporation’s bank statement spreadsheet ledger summary shows only three CR rows: Revenue, Transfers In, and Returned Items. In this case, the category Returned Items pertains to supplies which the corporation returned to the vendor. However, as there is no GST/HST paid amount included, we are reminded that these particular supplies were not taxable supplies. Therefore, in this particular scenario the corporation does not require an adjustment against its line 103/105 GST/HST paid for returned items.

To determine the adjustment amount for category “2.” items, described above, the GST/HST return preparer must refer to records detailing shareholder contributions to the corporation that do not source to CRs in the corporation’s bank accounts.

If there are only a few shareholder transactions of this type, records of such transactions may consist of a ledger summarizing receipts of shareholder contribution items. However, if there is a particular personal bank account or credit card extensively used to provide cash flow to the corporation, then the preparer is encouraged to create both a spreadsheet ledger and spreadsheet ledger summary for the applicable personal accounts.

As the fictional corporation detailed in Example 3 did not incur expenses from purchases outside the corporate bank account, an adjustment for item “2.” is not required. Given this information, line 106/108 is determined as follows.

To calculate line 106/108 GST/HST paid:

GST/HST Paid Before Adjustments	1,188.41
Less: GST/HST Paid on Returned Items	0.00
Add: GST/HST Paid on Shareholder Contributions	0.00
Total Line 106/108 GST/HST Paid	1,188.41

Extra example

For illustrative purposes, let’s imagine a slightly more complicated situation. Here are the facts:

• according to the bank statement spreadsheet ledger summary, the corporation paid out $2,600.00 GST/HST during the reporting period;
• on the same summary, it shows that the corporation had returned cheques that included $130.00 GST/HST, returned purchases that included $26.00 GST/HST, and received taxable bank fee refunds including $13.00 GST/HST; and,
• a ledger of shareholder contributions, which are for valid business expenses paid for from the shareholder’s credit card, include $360.00 GST/HST.

Given the above, in this scenario the corporation’s line 106/108 may be calculated as follows:

GST/HST Paid Before Adjustments	2,600.00
Less: GST/HST Paid on Returned Items	169.00
Add: GST/HST Paid on Shareholder Contributions	360.00
Total Line 106/108 GST/HST Paid	2,791.00

Net tax

In conclusion, the fictional corporation’s annual GST/HST return would be completed using the following line amounts as presented in Example 3, above.

Line 101 – Total Sales and Other Revenues	100,000.00
Line 103/105 – GST/HST Collected or Collectible	13,000.00
Line 106/108 – GST/HST Paid or Payable	1,188.41
Line 109 – Net Tax	11,811.59

Note that the net tax amount calculated in the GST/HST annual filing period should match the +/- change in the “GST/HST Payable” row of the corporation’s adjusted trial balance (see Example 1, above). However, the net tax amount may or may not be the actual GST/HST Payable balance at year end due to carry forward amounts from prior periods, and/or GST/HST remittances made to the Receiver General throughout the reporting period.

---

Notes:

[1] Note that an excise tax rate of 13% is assumed for example only. For your purposes, use the GST/HST per cent applicable for the corporation’s operational zone.