Entry analysis example

An audit problem

We want to know what for purposes cash paid out of cash has been used. We know that the Cash account in the general ledger is '1900'. We have got a general record file 'GL.IMD'; it contains all the accounting rercords of the fiscal year (more information about the data of the example here).

A solution

We analyse the cash account entries making use of the principal of double entry book-keeping. When money is paid out of cash the cash account is credited and another account, telling purpose of the payment (e.g. Travel expenses), is debited. On the voucher level we have necessary information about both accounts. Just identify entries where cash has been paid out, identify appropriate vouchers, and analyse their entries.

1.

Payments of out petty cash. We extracts all entries due to payments out of the petty cash, i.e. the Cash account credit entries.

Records form 'GL.IMD' are extracted to 'CASH_CR.IMD' using IDEA equation BSS_ACC="1900" .AND. CREDIT>=0.01. Fields OFFICE, VCH_TYPE, and VCH_NO are included in 'CASH_CR.IMD'.

2.

Entries associated with payments of out petty cash. Next, using Join, we identify all the debit entries associated with the 'CASH_CR.IMD' records.

'GL.IMD' is selected as the primary file for Join. All its fields will be included in the new file.

'CASH_CR.IMD' is selected as the Secondary database for Join. Only one field - whatever - will be included in the new file.

Fields OFFICE, VCH_TYPE and VCH_NO are selected in both files as the keys of Join [1].

Matching alternative 'Matches only' is selected.

The resulting new file is given a name 'CASH.IMD'.

3.

An analysis. We want to find anomalies concerning use of petty cash. A good technique here is examination of account period by period.

We create a virtual field PERIOD in 'CASH.IMD' using a DATUM, a character field ('YYMMDD'). PERIOD (charater field, lenght 2) is given value through an equation @mid(DATUM,3,2).

We use CROMM_02B.ISS with the following options: Account A = BSS_ACC; truncation: 3; Account B = PERIOD; truncation: 4; 1. Numeric value = DEBIT; 2. Numeric value = CREDIT [2].

The resulting TABLE is shown below.

Picture 1

4.

Examination of the results.

Number of records analysed is shown in column RECS.

Total sums of debit (use of money) and credit (source of money) entries during all the months are shown in columns DEBIT and CREDIT respectively.

Monthly transaction balances are shown in cells. E.g. the sum in column '01_D' on row '400' is the recorded sum of stationery expenses in January (1035) and in column '01_C' on row '190' is the sum of payments from petty cash in January (2402).

In all 149023 has been paid out of petty cash during the year. due to travel expenses account (46642, in total). But a much petty cash funds has been used in other purposes, too.

Table can tell about potential violations of rules, e.g. excessive payments from the petty cash during August (in advance payments).

Closing remarks

This technique provides quickly breakdown of any balance account [3]. Time series are useful for identifying anomalies as they supply a logical context for interpreting the results. Time series can be based on any time intervals - like years, quarters, months, weeks, days, and hours.

[1] This means that the vouchers are identified with the information of all of these three fields (OFFICE, VCH_TYPE and VCH_NO).
[2] An alternative: IDEA's Pivot table functionality.
[3] The analysis of entries can be used for many purposes, e.g. in examining, what accounts have been made counter entries from different bank accounts.