Annual Audit Manual

COPYRIGHT NOTICE — This document is intended for internal use. It cannot be distributed to or reproduced by third parties without prior written permission from the Copyright Coordinator for the Office of the Auditor General of Canada. This includes email, fax, mail and hand delivery, or use of any other method of distribution or reproduction. CPA Canada Handbook sections and excerpts are reproduced herein for your non-commercial use with the permission of The Chartered Professional Accountants of Canada (“CPA Canada”). These may not be modified, copied or distributed in any form as this would infringe CPA Canada’s copyright. Reproduced, with permission, from the CPA Canada Handbook, The Chartered Professional Accountants of Canada, Toronto, Canada.

2043 Examples of using detailed electronic entity data
Jun-2018

In This Section

Understanding the entity’s activities

Controls related tests

Overview

This topic illustrates specific examples of using detailed electronic entity data.

Understanding the entity’s activities

OAG Guidance

Example 1

As part of understanding the entity’s purchases and payables business process, let’s say we used the following output generated from a data analytic tool. This output presents a Venn diagram of the number of users who created purchase orders, invoices and goods receipt notes for purchase transactions in the audit period. The overlapping sections of the visualization highlight the number of users who created more than one document, but these may not be the same purchase transaction. A user can “drill down” to see the transactions that comprise each overlapping section to determine if any users have created a purchase order, goods receipt note and/or invoice for a single purchase transaction.

000000000000

View actual size

Image description

This Venn diagram shows the types of user activity for a company from 1 April 2017 to 31 March 2018.

The 3 circles are for users who created a purchase order, created a goods receipt note, and created an invoice.

Overall, there were

  • 64 users
  • 16,547 purchase orders
  • 25,195 goods receipt notes
  • 43,755 invoices

The breakdown of the users’ activities is as follows:

  • 11 created a purchase order only
  • 23 created a purchase order and a goods receipt note
  • 2 created a purchase order and an invoice
  • 1 created a purchase order, a goods receipt note, and an invoice
  • 16 created a goods receipt note only
  • 2 created a goods receipt note and an invoice
  • 9 created an invoice only

In this example, assume we noted 1 instance where the same person had created a purchase order, the goods receipt note and the invoice.

The entity had previously asserted that as part of their segregation of duties controls the person creating the purchase order, the goods receipt note, and the person entering the invoice into the system are required to be different. Thus, by using the data analytic tool, we were able to achieve an understanding of the process, helping us to focus our audit effort on the areas of potential risk.

We evaluated the reasons for deviation from the entity’s expected process by obtaining management’s explanation and reviewing relevant third party documentation.  We use our judgment in evaluating the design and implementation of the entity’s purchasing controls and consider any impact on our planned level of reliance on these controls.

Example 2

By using available data analysis tools, as part of understanding the entity’s business processes and activities we generated a report that shows the totals of all bookings by G/L account and sub ledger with the possibility to drill down to view underlying totals by month and also by individual journal entry.

000000000000

View actual size

Image description

This example report shows totals from journal entries for example accounts and ledgers. The example is abbreviated for demonstration purposes only and the amounts are in thousands of dollars.

The 2500 account—Accounts receivable has the following associated ledgers:

  • an opening balance (trial balance) of 24,385,000 dollars
  • sales bookings under debits of 143,097,000 dollars
  • no purchase bookings
  • bank bookings under credits of minus 139,335,000 dollars
  • memo bookings under credits of minus 299,000 dollars
  • a closing balance (trial balance) of 27,848,000 dollars
    • A memo booking on the accounts receivable account that normally has bookings via the sales ledger (invoices) and bank accounts (payments received); further investigation revealed a temporary problem in access rights for bookings.

The 2501 account—Bad debt provision has the following associated ledgers:

  • an opening balance of minus 2,445,000 dollars
  • no sales bookings, purchase bookings, or bank bookings
  • memo bookings of 2,211,000 dollars under debits
  • memo bookings of minus 2,000,000 dollars under credits
    • An addition to the bad debt provision of 2 million. An easily performed drill-down showed 1 booking pre year-end, which could be discussed directly with management. Further, it was shown that the debit bookings on this account contained 12 bookings of which 1 was 80% of the total credit amount. The engagement team used target testing for testing this booking.
  • a closing balance of minus 2,234,000 dollars

The 3010 account—Value-added tax (VAT) 19% to be paid has the following associated ledgers:

  • no opening balance, purchase bookings, bank bookings, or memo bookings
  • sales bookings under credits of minus 21,090,000 dollars
    • An easily performed reconciliation between VAT to be paid with the related sales (=19% of net sales classified as taxable sales) which facilitated the audit of tax procedures.
  • a closing balance of minus 21,090,000 dollars

The 3011 account—VAT to be recovered has the following associated ledgers:

  • no opening balance, sales bookings, bank bookings, or memo bookings
  • purchase bookings under debits of 15,887,000 dollars
  • a closing balance of 15,887,000 dollars

The 8000 account—Sales 19% VAT has the following associated ledgers:

  • no opening balance, purchase bookings, bank bookings, or memo bookings
  • sales bookings under debits of 14,553,000 dollars
    • A significant number of sales (12%) are settled with credit notes. Further drill-down showed a) insufficient quality in sales processing resulting in subsequent handling and credit invoicing, and b) 2 credit notes just before year-end revealing a fraud related to cut-off.
  • sales bookings under credits of minus 125,556,000 dollars
  • a closing balance of minus 111,002,000 dollars

The 8001 account—Sales VAT exempted has the following associated ledgers:

  • no opening balance, purchase bookings, bank bookings, or memo bookings
  • sales bookings under credits of minus 11,005,000 dollars
    • Significant number of sales not taken into account for calculating VAT to be paid. Later investigation confirmed this as “sales abroad” that were indeed VAT exempted.
  • a closing balance of minus 11,005,000 dollars

Analysis of this report helped with / indicated the following:

  1. An easily performed reconciliation between VAT to be paid with the related sales (=19% of net sales classified as taxable sales) which facilitated the audit of tax procedures.

  2. A significant amount of sales (12%) are settled with credit notes. Further drill-down showed a) insufficient quality in sales processing resulting in subsequent handling and credit invoicing, and b) 2 credit notes just before year-end revealing a fraud related to cut-off.

  3. Significant amount of sales not taken into account for calculating VAT to be paid. Later investigation confirmed this as ‘sales abroad’ that were indeed VAT exempted.

  4. A memo booking on the accounts receivable account that normally has bookings via the sales ledger (invoices) and bank accounts (payments received); further investigation revealed a temporary problem in access rights for bookings.

  5. An addition to the bad debt provision of 2 million. An easily performed drill-down showed one booking pre year-end, which could be discussed directly with management. Further, it was shown that the debit bookings on this account contained 12 bookings of which one was 80% of the total credit amount. The engagement team used target testing for testing this booking.

Overall, such a report:

  1. Provides a general understanding of the entity’s activities (planning).

  2. Identifies (new, enhanced) audit risks with respect to specific general ledger postings / financial statement line items (planning).

  3. Identifies attention points and/or potential deficiency in the entity’s internal control environment (planning / interim).

  4. Provides insight in the quality and efficiency of administrative and period closing processes (interim / year-end audit).

Thus, this report facilitates engagement teams obtaining relevant information about the entity’s activities and their financial impact, and in approaching the audit in a more targeted, risk based way. This will enhance:

  • Our knowledge of the entity’s way of doing business, the design of their business, administrative and IT processes, and of their information and communication process, providing us with the opportunity to demonstrate our knowledge of the entity’s business and share these broader insights with the entity.

  • The quality of questions posed to the entity.

  • The effectiveness and efficiency of the audit process as less risk-carrying audit areas can receive less time and attention.

Example 3

As part of understanding the entity’s activities we used available data analysis tools to support our understanding of the entity’s information and communication systems and generated a report that analyzes the revenue account and identified credit notes. This report indicated that 13.86% of the invoices had credit notes associated with them representing 11.32% of the revenue balance.

000000000000

View actual size

Image description

This example graph shows the total amount and the credit amount for every month in 2009 and in 2019. The amounts fluctuated for both 2009 and 2019. The highest total amounts reached more than $200,000, while the highest credit amounts reached more than $40,000. The lowest total amounts were about $65,000, while the lowest credit amounts were about $0. The monthly amounts in 2009 and 2019 were as follows:

  • In January 2009, the total amount was more than $120,000, and the credit amount was about $20,000.
  • In February 2009, the total amount was more than $120,000, and the credit amount was about $20,000.
  • In March 2009, the total amount was about $140,000, and the credit amount was $0.
  • In April 2009, the total amount was more than $100,000, and the credit amount was about $20,000.
  • In May 2009, the total amount was less than $160,000, and the credit amount was about $30,000.
  • In June 2009, the total amount was more than $160,000, and the credit amount was less than $5,000.
  • In July 2009, the total amount was more than $80,000, and the credit amount was about $5,000.
  • In August 2009, the total amount was about $110,000, and the credit amount was less than $5,000.
  • In September 2009, the total amount was more than $180,000, and the credit amount was more than $40,000.
  • In October 2009, the total amount was more than $160,000, and the credit amount was about $30,000.
  • In November 2009, the total amount was more than $160,000, and the credit amount was more than $30,000.
  • In December 2009, the total amount was more than $140,000, and the credit amount was less than $5,000.
  • In January 2019, the total amount was almost $180,000, and the credit amount was $0.
  • In February 2019, the total amount was more than $140,000 and the credit amount was more than $40,000.
  • In March 2019, the total amount was more than $200,000, and the credit amount was $0.
  • In April 2019, the total amount was more than $150,000, and the credit amount was more than $40,000.
  • In May 2019, the total amount was more than $140,000, and the credit amount was about $5,000.
  • In June 2019, the total amount was about $205,000, and the credit amount was more than $5,000.
  • In July 2019, the total amount was almost $120,000, and the credit amount was about $20,000.
  • In August 2019, the total amount was almost $80,000, and the credit amount was $0.
  • In September 2019, the total amount was almost $100,000, and the credit amount was about $20,000.
  • In October 2019, the total amount was about $90,000, and the credit amount was about $0.
  • In November 2019, the total amount was about $65,000, and the credit amount was almost $40,000.
  • In December 2019, the total amount was about $80,000, and the credit amount was about $5,000.

The analysis indicated that:

  • Some of the credit notes recorded after the year end should have been accounted for in the current year.

  • The high number of credit notes in April resulted in additional testing of the cut-off around that date with no exceptions noted. However, the credit notes in April related to quality problems, which facilitated a discussion with management regarding the appropriate amount of the warranty provision.

  • The analysis identified that the approval of credit notes represents a significant sub-process within the revenue and receivables business process. Accordingly credit notes were appropriately included as part of our controls testing procedures over the revenue and receivables business process.

  • Applying professional skepticism, we inquired of management regarding the reasons for such a high level of credit notes. Management investigated this and it appeared that most credit notes related to price differences. By changing the price settings in the application, management was able to significantly reduce the number of credit notes.

Thus, by using the analysis above and underlying details we were able to achieve an understanding of the process and underlying issues, helping us to focus our audit on the areas of risk.

Controls related tests

OAG Guidance

Example 1

As part of understanding the design and testing the operation of an entity’s control activities we used the following report generated from a data analytic tool on the entity’s purchases and payables business process. This output provides an analysis of all invoices posted in the period matching them to their respective purchase orders (“PO”) and/or goods receipt notes (“GRN”) by reperforming the entity’s “three way match control”. The output attempts to match the relevant information (e.g. quantities and price per unit) appearing on the supplier’s invoice to the information on the purchase order and to quantities actually received on the GRN. All matching is performed on a line item basis. There is an option to enter a price or quantity tolerance level as it may be the case that entities apply a pre-determined tolerance level as part of their three way match control.

000000000000

View actual size

Image description

This example pie chart was generated for a company for the period from 1 April 2017 to 31 March 2018 and shows the number of invoices that matched purchase orders or goods receipt notes. Here are the details of the chart:

  • The number of invoices that matched with tolerance was 45,127, which represented roughly half of the total amount.
  • The number of differences between purchase orders and invoices was 19,002, which represented less than one quarter of the total amount.
  • The number of invoices that matched with goods receipt notes and purchase orders was 64,129, which was roughly three quarters of the total amount.
  • Less than one quarter of the total amount were invoices matched with purchase orders.
  • A small number of invoices had no linked documents, and an even smaller number of invoices matched goods receipt notes in a prior period.
  • The number of lines was 88,494.
  • The net amount (lines) was $117,401,334.05.
  • The number of invoices was 43,755.
  • The net amount was $117,401,334.05.
  • The value-added tax was $15,279,607.60.
  • The gross amount was $132,680,941.65.
  • Goods receipt notes typically do not include unit prices therefore price has not been matched between these and invoices.
  • Differences on price are presented as the maximum difference on a line basis where one invoice line is linked to multiple purchase orders.

Using no tolerances we noted that approximately 30% of items had PO and invoice differences. On reviewing the entity’s control and through inquiry of management, we noted that the entity accepts a 2% difference in price between the PO/GRN and invoice. Re-running the analysis with the appropriate 2% price tolerance, we noted only two purchases which had price differences between the PO/GRN and the invoice. We performed follow-up procedures to evaluate if these two purchases with price differences exceeding the tolerance level had been approved by the purchasing manager in accordance with the entity’s control procedures. We found that this approval had been obtained and documented.

We also performed testing on the reliability of the underlying information generated by the entity’s system and used to generate this output (including agreeing items to third party invoices and goods receipt notes) and noted no exceptions.

This output along with the additional testing performed, helped us evaluate the design and operation of the entity’s invoice matching control.

Example 2

By using available data analysis tools, as part of understanding the entity’s control activities we generated a report that identified missing journal entry numbers for a targeted account, in this case expenses/accounts payable:

000000000000

View actual size

Image description

This chart shows an example report showing the journal entry numbers that are missing in the company.

The first chart shows the following information:

  • Financial year/period from 201X-000
  • Financial year/period until 201X-012
  • Separate number ranges per journal

The second chart shows the following information:

  • The missing entry number is 258-540000; the next entry number is 540001; and the date is 30-01-201X.
  • The missing entry number is 541264-640000; the next entry number is 640001; and the date is 16-01-201X.
  • The missing entry number is 640573; the next entry number is 640574; and the date is 29-05-201X.
  • The missing entry number is 641145; the next entry number is 641146; and the date is 02-12-201X.
  • The missing entry number is 641195; the next entry number is 641196; and the date is 31-12-201X.
  • The missing entry number is 641210-641211; the next entry number is 641212; and the date is 30-11-201X.
  • The missing entry number is 641217; the next entry number is 641218; and the date is 10-12-201X.
  • The missing entry number is 641222-4000007; the next entry number is 4000008; and the date is 28-01-201X.

Analysis of this report indicated the following:

  • It is possible to remove certain entries from the information system without providing an audit trail. After investigation it appeared the missing entries related to incorrect invoices. This was communicated to management, who was not aware of the ability of employees to do this. To remediate the situation, user rights in the application were updated.

  • One instance identified that the entry was removed upon management request. Although it did not result in any material error, we considered this when assessing the risk of management override.

  • The missing entry dated 31 December should have been recorded in the current year and therefore expenses and accounts payable at the year end were understated. A correcting journal entry was prepared and subsequently recorded by management.

Thus, analysis of the detailed electronic data contributed to efficiency and effectiveness of the following audit procedures:

  1. Evaluation of the control activities
  2. Assessment of the risk of management override of controls
  3. Accounts payable cut-off testing
  4. Communication of a control deficiency