Auditing with data analytics

A traditional audit looks at only 60 out of thousands of documents and trusts a sample for the rest. The method works, but it has a weakness: an error or abuse may never land in the sample. Data analytics removes that limit: a computer sweeps through a beverage distributor's 52,000-line movement log in minutes, and the auditor checks the strangest lines by hand. Data analytics puts every entry through the tests, and the person only interprets the result.

First confirm the data is complete

Analysis relies on an exported file, so the first step is to confirm the file is complete. Take the movement log from 1C or another system: date, document number, item, quantity, amount, counterparty, user and the time the entry was made. Then check:

• whether the row count and date range match the period requested;
• whether the equation opening balance + receipts − issues = closing balance holds for the export as well;
• whether there are blank fields, numbers stored as text or repeated headers.

If the equation fails, analysis does not begin: the defect in the export is found first.

Six simple tests

1. Duplicates: entries made twice on the same day with the same item, quantity, amount and counterparty.
2. Timing: entries made on a day off or at night, outside working hours.
3. Round numbers: stock adjustments rounded one after another to 100, 500 or 1,000. Genuine differences are usually uneven.
4. Gaps in numbering: documents run in order from 1 to 2,400, but 1187–1195 are missing. Were they deleted?
5. Negative balances: days when a sale was posted although the system showed no stock.
6. Just under a limit: if the approval limit is 5 million so'm and many entries sit near 4.9 million, the amount is being split.

In Excel most of this is done with a pivot table, COUNTIFS and sorting. No special software is required.

An example: a beverage distributor

The three-month log has 52,380 rows. The tests return 41 duplicate entries (3.7 million so'm in total), 118 entries made on a Sunday (96 of them stock adjustments) and two gaps in document numbering (31 numbers in all).

After manual checks the picture changes. Of the 41 duplicates, 36 turned out to be two separate deliveries: two different batches loaded on the same day. Only 5 (0.9 million so'm) were one delivery entered twice. The Sunday adjustments raised serious questions: the warehouse is closed on days off, yet one user made the 96 entries. One gap in numbering was explained by a system setting, the other was not. In the end, instead of 50,000-plus rows the auditor focused on a few dozen entries.

The view by user

Grouping test results by user often gives the most information. In the table, each employee's counts of write-offs, stock adjustments and deleted documents stand side by side. If one person's share is several times the average, that is not an accusation, but it is grounds for a conversation. The cause is often simple: one person books all the returns, or a password is shared. So check as well whether shared accounts are in use.

Results and limitations: a list, not a verdict

An entry flagged by software is not necessarily an error. It is a signal, to be confirmed by a document, an interview or observation. False signals are normal: in the example above most duplicates were ordinary. Rank the list by risk, taking into account the amount, recurrence and whether one user is behind it.

Limitations

• Data quality: a wrongly entered log cannot give a correct conclusion.
• Analysis sees only what was entered in the system. Movement without documents, such as goods taken outside the system, is absent from the log.
• Access to data: work on an export copy rather than the live database, with read-only rights, and respect confidentiality.
• Settings: if formulas and filters are not documented, the result cannot be repeated.
• Internal data only: without comparing against a bank statement, a supplier statement or a marketplace report, agreement inside the system cannot be fully trusted.

Conclusion

Analytics does not replace sampling but complements it: the computer reads every row, and the person decides what matters. OmborAudit works with data exported from 1C, Excel and other systems, finds unusual entries and confirms them with documents and a count.