GST/HST Data Conversion Audit

Final Report

Corporate Audit and Evaluation Branch
May 2008


Executive Summary

The Canada Revenue Agency (CRA) launched a GST/HST Redesign project in 2002 to address deficiencies and technical limitations with the existing GST/HST processing system. One of the main goals of this project was to migrate the program to a corporate suite of business systems. The project is a large scale, complex set of initiatives involving multiple releases to be implemented over a seven-year timeframe. The Assessment & Benefit Services Branch (ABSB) and Information Technology Branch (ITB) have joint responsibility for the project.

The systems releases in April 2007 lead to the implementation of new GST/HST business processing applications and the migration of the new systems onto the corporate suite of platforms. This included shutting down the accounting sub-system of the old GST/HST production (GSTPRD) system and moving this functionality to the corporate Standardized Accounting (SA) system. This conversion required close to one billion data elements to be moved from the legacy systems to the new applications.

The audit was carried out between July 2006 and September 2007 in two stages. Prior to the planned systems release, set originally for October 2006, a review was conducted of the methodology used by ABSB and ITB in planning the migration and the testing, cleanup and conversion of existing GST/HST data. After the conversion in April 2007, the results were validated through an independent reconciliation of the account balances in the old and new systems and reviews of the reconciliation and remedial processes performed by ABSB, ITB and the Finance and Administration Branch (F&A).

Objective:  The objective of the audit was to review the data conversion and migration of the account balances from GSTPRD to the SA platform and determine whether the posting of this data to the Revenue Ledger (RL) [Footnote 1] was complete and accurate, and supported by the aggregate account sub-ledger balances in SA. The review covered the period leading up to and including the actual conversion of the data. The scope did not include the post-implementation processing of the GST/HST data.

The audit was conducted in accordance with the International Standards for the Professional Practice of Internal Auditing.

Conclusion:  The migration from the GSTPRD to the SA platform and the transfer of data from SA to the RL was successfully carried out in the April 2007 systems release. The data transferred to RL was generally consistent with what had been transferred to SA from GSTPRD and supported by the detailed client account sub-ledger GST/HST balances in SA. Clean-up activities prior to conversion eliminated a considerable amount of invalid and redundant data and a minimal number of accounts were rejected at conversion as a result. The few variances identified during account reconciliations could be reasonably explained and remedial action was taken to address them.

Introduction

The Canada Revenue Agency (CRA) administers the Goods and Services Tax (GST) and Harmonized Sales Tax (HST) programs for the Government of Canada (GoC), excluding the province of Quebec. Since 1992, an agreement between the GoC and Quebec has allowed Revenue Quebec (RQ) to collectively administer the GST and the Quebec Sales Tax (QST) for businesses in that province. Net revenues of just under $11 billion were reported by CRA for GST in the Agency Financial Statements for fiscal year 2006-2007.

A GST/HST Redesign project was launched by CRA in 2002 to address deficiencies and technical limitations with the processing system and to migrate the program to a corporate suite of business systems. This project is a large scale, complex set of initiatives involving multiple releases to be implemented over a seven-year timeframe. The Assessment & Benefit Services Branch (ABSB) and Information Technology Branch (ITB) have joint responsibility for the project.

The systems releases in April 2007 led to the implementation of new GST/HST business processing applications and the migration of the new systems onto the corporate suite of platforms. This included shutting down the accounting sub-system of the old GST/HST production (GSTPRD) system and moving this functionality to the Standardized Accounting (SA) system. The SA platform allows for accounting and payment information for different business revenue types to be presented in a standardized format and provide more timely and detailed information for use in meeting GoC financial management and reporting requirements.

With this systems release close to one billion data elements covering more than 15 years of historical data were moved from the legacy systems to the new applications and in excess of 100 million returns for all filing periods were converted. More than 400,000 of the more than 5 million accounts that needed to be converted had data that was corrected by ABSB and ITB before the systems release, which contributed to the successful conversion when the application was actually run.

Accuracy and reliability risks are inherent in any kind of system conversion or migration of data or accounting processes from one system to another. For that reason, an audit of the GST/HST data conversion and planned system migration was included in the approved Corporate Audit and Evaluation Branch (CAEB), 2006–2009 Business Plan.

Focus of the Audit

The objective of the audit was to review the data conversion and migration of the account balances from GSTPRD to the SA platform and determine whether the posting
of this data to the Revenue Ledger (RL) [Footnote 2] was complete and accurate, and supported by the aggregate account sub-ledger balances in SA.

The audit was carried out between July 2006 and September 2007 in two stages. Prior to the planned systems release date, set originally for October 2006, a review was conducted of the methodology used by ABSB and ITB in planning the migration and the testing, cleanup and conversion of existing GST/HST data. Audit testing after the actual April 2007 release included a review of the reconciliation and remedial processes performed by ABSB, ITB and the Finance and Administration Branch (F&A), an independent reconciliation of the GST/HST balances in the old and new systems, as well reconciliation of a random sample of client account balances. Sources for the account balances information were the Filing Period (FP) Table, the SA, the GST/HST Returns and Adjustment Processing System (GHRAPS), the GST/HST Rebates Processing System (RPS) and the GST Historical Database.

The audit work did not include a comprehensive review of the flow of GST/HST transactional data from source documents to the new systems through to the RL. A more detailed review of the data flow-through process will be the focus of a separate audit at a later date. The scope of the audit also did not include verification of individual assessments within the GST/HST systems or the processing of GST/HST data after the new production systems were implemented.

The audit was conducted in accordance with the International Standards for the Professional Practice of Internal Auditing.

Findings, Recommendations and Action Plans

1.0 Reliability of the converted GST/HST balances in the sub-ledger (SA)

Audit testing completed after the April 2007 systems release confirmed that the data posted to RL was generally consistent with what had been transferred to SA from the GSTPRD and supported by the detailed client account sub-ledger GST/HST balances in SA. In addition, the detailed client account sub-ledgers in SA supported the GST/HST balances.

In the few instances where there were differences between the GSTPRD system and SA, it did not have an impact on the accuracy of the conversion process or the client accounting information found in SA relative to the old system. A brief overview of the data conversion and reconciliation processes and the audit results are provided below.

Data Conversion - Overview

For the April 2007 systems release, only client accounts with accounts receivable (A/R) balances were to be converted to SA with all related accounting detail [Footnote 3]. Return and rebate information were converted from the old GSTPRD system to the new processing systems, GHRAPS and RPS, and to the Program Account Information System (PAIS). After conversion the total amounts recorded for completed assessments and validated credit returns (from GHRAPS) as well as approved rebates (from RPS) were then to be sent to SA. The returns not yet assessed, credit returns not validated and rebates processed but not approved were to remain in the respective GHRAPS or RPS system as work-in-progress or pending status until assessed, validated or approved. Linkages were built in the new systems to provide access to the past history of accounts in the old GSTPRD system.

Data clean up was performed before the conversion to eliminate invalid and corrupt data in the old system. Production dry runs were used to simulate the conversion process and test and measure the performance of the software. These were also used to validate the data and to uncover any data conditions not identified during previous testing. As of March 29, 2007, 95.39% of the planned testing for the April 2007 release had been reported completed at the project level with an overall success rate of 98.06% being achieved.

SA Balances Following Conversion

Testing of the accuracy of the transferred balances included comparing the sum of the A/R account balances from FP Table in the old GST system (produced after data clean up) with the combined total of the new A/R account balances in SA, GHRAPS and RPS. At the time of conversion there was an initial variance identified of $93 million, representing amounts that had not been converted to the new systems. This was attributed by F&A and ABSB primarily to rebates still in process or in-transit ($69 million) that would in time be processed, with the data then transferred to the new systems. The remaining amount of $24 million that could not be reconciled was posted to the Temporary Equity Adjustment account in RL for the fiscal year-ending March 31, 2007.

In addition, there were 193 client accounts (less than 0.01%) that were rejected by SA out of the more than five million that underwent the conversion process in April 2007. By the end of August, solutions had been identified by ABSB for 176 of these accounts (91%) and these were then transferred to SA in batches. Issues with the remaining 17 accounts that were locked at conversion were resolved in January 2008 and have since also been converted.

For audit testing of closing balances, a representative sample of 157 individual GST client accounts was identified to verify accuracy of the detailed client data following conversion. The accounts were selected randomly, prior to conversion, from the FP Table produced after data clean up.

It was confirmed that for all samples having a finalized assessment (144/157), transactions flowed accurately through the GHRAPS and RPS systems to SA. Though there were inconsistencies noted in some data for the 13 remaining samples, these did not have an impact on the accuracy of the balances in SA. With four of the accounts that had differences, period end dates had not been converted and for three others, they were pending review and would not have been expected to transfer to SA.

The remaining six sample accounts had entries resulting from joint GST/provincial tax returns filed with the RQ in Quebec that were still awaiting confirmation (referred to as Autocompensation –autocomp transactions). At the time of conversion approximately $1 million unconfirmed debit autocomp entries still existed in the old GSTPRD system. A review by ABSB to evaluate the appropriateness of the outstanding RQ transfers was ongoing at the time of the audit. A report was drafted in November 2007 and after a final internal review is completed it will then be shared with the RQ.

Unidentified payments residing in GST suspense accounts because of incorrect or incomplete account information or payments made in error were also successfully converted to corporate suspense accounts in SA. As of February 1st, 2008, of the 2,308 corporate suspense cases created in SA at the time of at conversion, 86 cases (3.7%) remain to be resolved and cleared from corporate suspense.

2.0  Accuracy and completeness of the converted client account aggregate GST/HST balances

Reconciliations were performed by ABSB and F&A on a regular basis both before and after the conversion to ensure there was an accurate and complete transfer of data. The audit team reviewed the key account balance reconciliations completed by F&A and confirmed that mostly positive results were achieved.

The opening balances posted to the five new accounts receivable (A/R) accounts in RL could be matched to the aggregate amount processed and transferred to SA after conversion. The accuracy of individual account balances making up the aggregate postings from SA to RL at the time of conversion were also tested and verified by tracing the amounts of the previous audit sample (144 accounts) to the RL accounts to which they were posted.

To confirm the accuracy of the post-conversion transfer of data between the accounting and assessing systems, a separate sample of 30 accounts (five each of debit, credit, zero, RQ and rebate returns) were selected for review. Transaction data for twenty-seven of the samples selected (90%) flowed accurately and completely from the GHRAPS and RPS assessing systems into the SA accounting system. The assessed amounts from GHRAPS and RPS and the accounting amounts from SA were compared and the amounts were correct in value and posted to the appropriate RL accounts. The three remaining samples were awaiting correction or pending review in the GHRAPS system at the time of the review and so were not posted to the RL.

Nonetheless, there was one notable difference between the old A/R account balance in RL and the A/R balance in the FP Table (after clean-up) used for the conversion. Reconciliations carried out by F&A indicated that the FP table balance was accurate and in line with the data converted to SA and the other systems but an $82 million difference existed in the RL account. F&A has since recorded an entry to offset this amount from the old GST Arrears (A/R) account by crediting the Temporary Equity Adjustment Account for the year-ending March 31, 2007.

3.0  Adequacy of process controls for the conversion and migration of GST/HST data

Appropriate mechanisms, including systematic testing, were established and applied to plan and manage the GST/HST data conversion and system migration for the  April 2007 implementation. Procedures for monitoring and reporting on the progress of conversion and for problem management were in place and roles and responsibilities were clearly defined and documented. A conversion strategy has been developed to mitigate risks and closely controlled processes were established for the clean up of invalid data prior to conversion and for clearing rejected transactions.

The number of rejected transactions was minimal due to the clean up done in conjunction with the production dry runs. An account locking strategy was used to deal with the fall out from conversion due to problems with the data; thereby leaving the locked account available to other systems for other processing. The rejected transactions were to be resolved by a team with priority to work on resolving identified problems at the account level.

Status updates were regularly prepared at the project level. They were provided to senior management and other stakeholders to advise on progress, issues and problem reporting and resolution.

Conclusion

The migration from the old GST/HST production system to the SA platform and the transfer of data from SA to the RL was successfully carried out in the April 2007 systems release. The data transferred to RL was found to be generally consistent with what had been transferred to SA from the GSTPRD; accurate and supported by the detailed client account sub-ledger GST/HST balances in SA. Clean-up activities prior to conversion eliminated a considerable amount of invalid and redundant data and a minimal number of accounts were rejected at conversion as a result. The few variances identified during account reconciliations could be reasonably explained and remedial action was taken to address them.


Footnotes

Report a problem or mistake on this page
Please select all that apply:

Thank you for your help!

You will not receive a reply. For enquiries, contact us.

Date modified: