Common Data Model for Financial Auditors : Part 2

The process of creating a CDM for auditors has asked more questions than it has answered.

My initial idea was a finite set of common terms which would relate to the main general ledger followed by a complementary set of terms which would relate to the most typical sub-ledgers, day books or schedules. These sets could be expanded as additional variables were encountered which other auditors would find useful to be able to analyse immediately.

But somehow I forgot about the auditors themselves.

Audit is a marvellous world awash with terminology that does not easily skew into the average conversation in a pub, "Materiality" being the big one, in all its glorious forms and values, but also the nuances of "significant" and "non-significant, "complex" and "non-complex". This is before we even get into classifications like "Assets" and "Liabilities".

In short, what had seemed like a discrete task was no longer quite so straightforward. We will be iterating until the cows come home.

Yet doesn't this present us with an opportunity for even more automation? Instead of documenting why you didn't change your materiality based on the year end figures, you can simply put the revised materiality into the script and re-run to see what sort of a difference you get. Or a mid-audit change in component materiality doesn't require oodles of additional work when the scripts are already set up and ready for the change of one minor number.

I worked with some auditors who had a horrible time when it came to reconciling the general ledger to the financial statements. Their clients would take a little bit from here, a little bit from somewhere else, and mash that together in a wholly mysterious way to give you a disclosure note. It sounded hellish to me (I was blessed with quite organised FCs and FDs), but maybe there is a way of letting R do the heavy lifting.

The first in a series of CDMs which I will be using through this project is now uploaded to Github (https://github.com/kid-lupin/Lupin). This should allow me to make a start on the first of the data wrangling scripts. So far we have 41 terms which is very manageable, however the important point is that not every GL will include every one of these variables. A Sage GL, for example, will have less than a dozen of these. So the key auditor insight remains, as always, know what is in your GL!

Comments

Post a Comment

Popular posts from this blog

Data Wrangling : General Ledger as CSV : Import and data types

The first step of an audit is to get the financial information you are auditing from your client. At the very least this comprises a set of financial statements and a trial balance. Preferably your client will also give you a data dump of the full general ledger. At some point you will get other supporting schedules like aged debt, fixed asset registers and the ilk. We'll get to them in due course. To get us up and running, we're going to rule out working with client documentations which are PDFs, because they require additional steps to get into R. Get your client into the habit of giving you documents in a file format which you can work with immediately! The two main file formats we are going to look at are CSV and XLSX. These should cover 99% of audits. Today is all about CSVs. A CSV (Comma Separated Value) file is a common export document type for data from financial reporting software. It stores data in a tabular structure in a plain text format. This means it can be opene...
Read more

Common Data Model for Financial Auditors : Part 1

When I first approached the idea of a common data model (CDM), it was because our team and the teams in other audit agencies were all separately developing audit tools in R, and our respective directors were keen that we collaborated and achieved some efficiencies. Senior management love efficiencies. If we had a CDM, then we could swap chunks of R code with each other or easily work on one a single chunk together (for example, a fixed asset register analyser). I think this simplistic understanding of mine did a disservice to the value of devising a CDM. I should know this from my days as a philosophy student at QUB - philosophy of language (particularly Saussure, but also Quine) is one of the most interesting things anyone can spend time on. A CDM provides us with a common language that can not only easily be understood by humans but also by different chunks of the code. By creating an equivalency in definition, we create useful boundaries of meaning that allow the analysis to occur w...
Read more