AuditNet®

CAATT Tales

 

 

Accounts Receivable Analysis Using CAATTs

By Don Sparks, CIA, CISA, ARM

Audimation Services, Inc.

 

Experts in Data Analysis
Volume 1 Issue 6

 

In recent years, many internal auditing organizations have recognized the need to sit back and reevaluate what is in their "audit universe".  Are we hitting the right sized risk areas with the right tools and techniques?  One area that is being pulled into focus is the financial statement reporting process often considered to be the sole territory of the external accounting firm auditors.

 

Nearly every company large or small uses an accounting system containing the general ledger.  These accounting systems often times have integrated ledgers for areas such as accounts payable, accounts receivable, payroll, and inventory.  

 

Below is a summary I prepared recently on the Accounts Receivable area.  While there are many audit tests that can and often times should be conducted on a periodic basis, I pulled together 8 analytical tests that can easily be conducted by audit.

 

I also make reference to an automated software package that we offer our clients called Smart Analyzer.  Smart Analyzer allows the auditor to match and tag data field names that appear in their accounting system to the names that are already programmed in the automated script.  In the table below I have the general data field name of each data element required to complete the appropriate test.

 

This package has 55 tests in the above areas already programmed and ready to run.  Once the IDEA auditor has imported the data files and "tagged" each element to the already named data element in the package, they are ready to run these tests.  This level of support allows IDEA clients to be sure every auditor is conducting the same test the same way on every engagement.

 

If your current external auditors have not been running these tests you may want to inquire "why not" or consider running them yourself.

If you would like to learn more about these continuous auditing solutions check out our web site at Audimation or contact me directly at dons@audimation.com.

 

Using IDEA Smart Analyzer

 

Routine

Data Fields Required

Potential Problems that could create a material misstatement or Fraud Situations

1. Aging by Due Date

Debtor Code
Debtor Due Date
Debtor Amount
  • Aged balances [over 60 or 90 days old] need to be turned over to collections or legal.
  • Allowance for bad debt is inadequate.
  • Receivable aging may be incorrect indicating weak internal controls.  Compare to prior year results.
  • Faulty work or defective  product shipped resulting in complaints and returns.
  • Debtors exceeding credit limits may be related parties or collusion with an inside employee.
  • The receivables and sales transactions could be false. Amounts were recorded to manipulate reported amount of income. False sales are motivated: income reported is down for the period;  employee compensation or bonuses are based on profits; or, the company plans to issue capital stock or borrow money from a bank in the near future.
  • Cash paid on balance is stolen/hidden. Employees may write off to bad debt to hide actions.
  • Lapping. Cash from one receivable is stolen and covered with cash received from a second customer shortly thereafter.
  • The year-end cut-off of transactions is incorrect. Transactions occurring before the end of year could be recorded in the subsequent period.
  • Transactions after the end of year could be recorded prematurely in the initial year.
  • Customer is billed incorrectly because of math errors, wrong quantity, wrong price, or wrong items.
  • Customer may not be billed at all for goods that were shipped.  Confirm from inventory if unexplained balances.
  • Transaction is with a related party and may require disclosure.
  • Sales holding onto credit balances to be diverted or misapplied in future transactions.

 

2. Aging by Invoice Date

Debtor Code
Debtor Invoice Date
Debtor Amount

3. Debtors with Total Amount Greater than Credit Limit

Debtor Code
Debtor Amount
Debtor Credit Limit

4. Debtors with Balances Greater than Credit Limit

Debtor Code
Debtor Credit Limit
Debtor Current Balance

5. Debtors with Net Credit Balances

Debtor Code
Debtor Amount

6. Debtor Transaction Summary

Debtor Name
Debtor Code

7. Transactions Around a Specified Date

Debtor Invoice Date

8. AR Duplicate Field Search

Any tag from the Accounting or Accounts Receivable module

 

Smart Analyzer Tests

1. & 2. Example:  The Aging by Due Date routine was performed on the following database:

DEBTOR_ID

DUE_DATE

REF_NO

VAL

Beaver-01

15/03/2004

INV501

568.91

Moose-01

15/05/2004

INV512

601.50

SnowBird-01

15/09/2004

INV670

637.45

Moose-01

00/00/0000

INV741

1000.00

SnowBird-01

31/07/2004

INV788

3581.85

View extracts from History detailing the operations carried out on the database.  

 

Tagged Fields

Field

Tag

DEBTOR_ID

Debtor code

DUE_DATE

Debtor Due Date

REF_NO

No tag

VAL

Debtor Amount

 

Accounts Receivable Analysis

Routine:

Aging by Due Date

Folder:

Example

Aged as of:

2004/06/30

Aging interval type:

Days

Aging interval:

30, 60, 90, 0, 0, 0

 

The output database Aging by Due Date displays the total amount owing for each debtor aged by due date.

DEBTOR_ID

VAL

NO_OF_
RECS

AGE_
LE_0

AGE_
LE_30

AGE_
LE_60

AGE_
LE_90

AGE_
GT_90

AGE_
EQ_ERR

Beaver-01

568.91

1

0.00

0.00

0.00

0.00

568.91

0.00

Moose-01

1601.50

2

0.00

0.00

601.50

0.00

0.00

1000.00

SnowBird-01

4219.30

3

4219.30

0.00

0.00

0.00

0.00

0.00

 

 

3.  Example:  The Debtors with Total Amount Greater Than Credit Limit routine was performed on the following database:

DEBTOR_ID

REF_NO

VAL

LIMIT

18Oak

6541

4187.87

10000.00

52Pine

3240

521.54

5000.00

52Pine

6435

-2482.00

5000.00

18Oak

8563

9999.99

10000.00

52Pine

2110

783.55

5000.00

29Cedar

3584

254.88

750.00

29Cedar

3768

495.00

750.00

81Maple

5464

9684.54

2000.00

View extracts from History detailing the operations carried out on the database. 

Tagged Fields

Field

Tag

DEBTOR_ID

Debtor Code

REF_NO

No tag

VAL

Debtor Amount

LIMIT

Debtor Credit Limit

 

Accounts Receivable Analysis

Routine:

Debtor with Total Amount Greater Than Credit Limit

Folder:

Example

 

The output database lists the debtors whose total amount owing is greater than their credit limit.

DEBTOR_NO

NO_OF_RECS

CALCULATED_BALANCE

LIMIT

18Oak

2

14187.86

10000

81Maple

1

9684.54

2000

 


 

 

 

4.  Example:  The Debtors with Balances Greater Than Credit Limit routine was performed on the following database:

DEBTOR_ID

BALANCE

LIMIT

18Oak

15684.00

10000.00

52Pine

3518.61

5000.00

29Cedar

-581.15

750.00

81Maple

4587.54

2500.00

View extracts from History detailing the operations carried out on the database.

 

Tagged Fields

Field

Tag

DEBTOR_ID

Debtor Code

BALANCE

Debtor Current Balance

LIMIT

Debtor Credit Limit

 

Accounts Receivable Analysis

Routine:

Debtors with Balances Greater Than Credit Limit

Folder:

Example

 

The output database lists the debtors whose balance is greater than the credit limit.

DEBTOR_ID

NO_OF_RECS

LIMIT

BALANCE

18Oak

1

10000.00

15684.00

81Maple

1

2000.00

4587.54

 

 

 

5.  Example:  The Debtors With Net Credit Balances routine was performed on the following database:

DEBTOR_ID

REF_NO

VAL

18Oak

6541

4187.87

52Pine

3240

521.54

52Pine

6435

-484.00

18Oak

8563

9999.99

52Pine

2110

783.55

29Cedar

3584

254.88

29Cedar

3768

495.00

81Maple

5464

-9684.54

View extracts from History detailing the operations carried out on the database.

Tagged Fields

Field

Tag

DEBTOR_ID

Debtor Code

REF_NO

No tag

VAL

Debtor Amount

 

Accounts Receivable Analysis

Routine:

Debtors with Net Credit Balances

Folder:

Example

 

The output Debtors With Net Credit Balances database displays the debtor with a negative balance.

DEBTOR_ID

CALCULATED_BALANCE

81 Maple

-9684.54

 


 

 

6.  Example:  The Debtor Transaction Summary routine was performed on the following database:

TRANS_DATE

NAME

DEBTOR_ID

AMOUNT

2003-12-9

Ace Auto Repair

52061

690.00

2003-12-9

Ace Auto Repair

52061

-690.00

2004-2-17

Sister Company

52074

418.25

2004-3-12

Downtown Suppliers

52074

335.00

2004-3-4

Georgian Bay Gallery

52074

255.65

2004-3-14

Georgian Bay Gallery

52074

119.70

View extracts from History detailing the operations carried out on the database.

 

Tagged Fields

Field

Tag

DEBTOR_ID

Debtor Code

NAME

Debtor Name

 

Accounts Receivable Analysis

Routine:

Debtors Transaction Summary

Folder:

Example

 

The output database displays the number of transactions for each Debtor Code.

DEBTOR_ID

NO_OF_RECS

NAME

52061

2

Ace Auto Repair

52074

4

Georgian Bay Gallery

 

7.  Example:  The Transactions Around a Specific Date routine was performed on the following database:

ID

DATE

REF

VAL

18Oak

11/14/04

C0020

75.41

52Pine

12/24/04

A0011

998.45

52Pine

12/31/04

A0019

1500.00

18Oak

01/05/05

A0041

2500.00

52Pine

01/10/05

B0001

-4000.00

29Cedar

01/30/05

E0084

924.99

 

Click here for extracts from History detailing the operations carried out on the database.

Tagged Fields

Field

Tag

ID

Debtor Code

DATE

Debtor Invoice Date

REF

No tag

VAL

No tag

 

Accounts Receivable Analysis

Routine:

Transaction Around a Specified Date

Folder:

Example

Date:

2004/12/31

Days before:

15

Days after:

15

 

The output Transactions Around a Specified Date database displays list the transactions on and around the Debtor invoice date specified.

ID

DATE

REF

VAL

52Pine

12/24/04

A0011

998.45

52Pine

12/31/04

A0019

1500.00

18Oak

01/05/05

A0041

2500.00

52Pine

01/10/05

B0001

-4000.00

 

 

8.  Example:  The Duplicate Field Search routine was performed on the following database:

ID

DATE

REF

VAL

Beaver-01

15/03/2004

INV501

568.91

Moose-01

15/05/2004

INV512

7601.50

SnowBird-01

15/05/2004

INV670

637.45

Moose-01

25/07/2004

INV741

1000.00

SnowBird-01

31/07/2004

INV512

7601.50

View extracts from History detailing the operations carried out on the database.

 

Tagged Fields

Field

Tag

ID

Debtor Code

DATE

Debtor Invoice Date

REF

Debtor Reference Number

VAL

Debtor Amount

 

Accounts Receivable Analysis

Routine:

Duplicate Field Search

Folder:

Example

Date:

2004/12/31

Tag key for duplicate search:

Debtor reference number

Tag key for duplicate search:

Debtor amount

 

The output Duplicate Field Search database displays list the transactions with duplicate value in the field(s) specified.

ID

DATE

REF

VAL

Moose-01

15/05/2004

INV512

7601.50

SnowBird-01

31/07/2004

INV512

7601.50

 

 

To respond to this solution or provide data analysis questions you would like answered in future newsletter articles, please send an email to dons@audimation.com 

The opinions, beliefs and viewpoints expressed by the various authors and forum participants on this web site do not necessarily reflect the opinions, beliefs and viewpoints of AuditNet®