Top 10 Data Analytics for Fraud Detection – Part 1

Home » Knowledge Center » Articles » Top 10 Data Analytics for Fraud Detection – Part 1

Number one of our top 10 is going to be illegitimate journal entries

I’m going to show you a demo of a really cool dashboard that we have that we can actually use to look for journal entries in a snap that are a bit strange, okay? And it’s just way better than anything I ever did at Deloitte, which is crazy, because now, like sometimes, you know, when you do something and you think, oh, that’s just so obvious, right? Why did I never think about that before? For me, the number one most important data analytic is illegitimate journal entries. And the reason for that is because it has the highest financial impact in case of fraud. That hardly anyone is doing it.

Play Video

I don’t mean hardly anyone is doing illegitimate journal entries. I mean that hardly anybody is properly looking at their journal entries, even though we have Sapin Deux in France, for those of you who might be in France, which is a law that says that you basically you need to go through and be aware of all of your journal entry postings. Despite that, and despite things like the UK Bribery Act and all that stuff, people are still not looking at illegitimate journal entries.

And despite the fact that in 2010, we had the SAS 99 law that we were looking at at Deloitte that said we had to look at journal entries as part of external audit. And we were still not really doing it very well. So hardly anyone is doing it.

And I think that it’s, you know, it’s so important, just like the customer that we had when they went over to Brazil and they found a huge fraud case that it almost caused them to shut down the entire group, because it was such a misstatement over a few years. So it is really important if we can find issues of it. And sometimes we find huge issues.

And sometimes we find little things like people, they might do a credit debit refresh of their accounts receivable, just to make sure their accounts receivable looks correct. Or they might even do it for their stock, you know, they might do a sort of a fictitious, like, take all the stock out and put all the stock in again. So the stock looks like it just got there. And it doesn’t go into slow rotation, things like that. So there are little cases. And then there are big cases where we really do say, like, you know, we’ve got 20 million of revenue this year. And it’s not actually true at all. So it’s also a really key one for me in legitimate general entries, because it covers so many different areas and so many different fraud schemes. And it’s also used to cover up on fraud as well.

So another thing about it is that you don’t actually need that much data. So we need a lot of data in terms of volume, but you don’t actually need that many tables, which is nice, because you don’t have to understand too much about SAP before you start doing it. And that is really good.

So you only need the general ledger table. And when you’re doing it, you do, though, need to do a little bit of processing. So here, I put down here, you need to do a little bit of processing, need to create some lists of debit and credit accounts.

And you can create some lists of, like, debit and credit account indicators and things like that. And this would basically help you to see for one journal entry, what were all the debit accounts that got debited and what all the accounts that got credited. And that way, you get to see what was the person actually doing.

When we are doing analysis of illegitimate journal entries, we are going to take our general ledger table, and we’re going to add a load of fields that are going to really help us to be able to analyse this general ledger table really easily. So when we get on with our accounts, you know, if we’re in France, and we know that a six account is for cost, and a seven account is for profit, and a five account is for bank and things like that, it’s much easier, right? You’ve got 401 for suppliers, you’ve got your 411 for customers, but not everybody has that kind of rule of their accounting.

So we also have other things that can help us. So we have these things that help you to see in SAP, is your account to do with balance sheet? Is it to do with profit and loss? Is it to do with bank or disbursements or something like that? And is it something that’s used in a payment transaction? And we can also add into here the list of the types of suppliers. So something strange that SAP does is that in your supplier list, you also have a whole load of employees.

And that’s because employees get travel and expense, right? So and you’ve also got suppliers that are intercompany, and then just normal suppliers. So you don’t want to call something a supplier invoice if it’s actually an employee travel and expense. So you can also do the list of supplier account types, and also lists of customer account types. This is really going to help us. And if you have something like one of our customers, they have general ledger hierarchies. And it basically describes the account in a bit more of a synthetic way.

And those are really helpful as well. So we make all these lists. We basically make for each journal entry, we make all these lists. Then we make a summary table. Once we’ve made a summary table, we get something like this. We say here, I’ve got an account type K and account type S. And I know that this is profit and loss because it’s got GB type. Oh, no, it’s not. It’s not profit and loss, because it doesn’t have GB type.

It is bank because I flagged it as a bank. And this kind of information is what we use to actually categorise our journal entries. And then you can also do something like this.

I’m going to show you this is the list of accounting schemes that we typically would use to check for things. So, for example, we want to look for things that are hitting profit and loss.

And they’re hitting something opposite, which you wouldn’t normally expect them to, right? So, for example, this one, also what we would normally put in here is like, okay, you’ve got profit and loss on debit. And maybe you’ve got here, we’ve got VAT on credit. But you could also say, and what if you had bank on credit? So, if you went from loss to bank, and that in one journal entry, and you don’t bother going to the supplier first.

So, you don’t bother saying, I’m going to credit my supplier and debit loss. And then when I pay the supplier, I’m going to debit the supplier and credit the bank, because that way I know who I’m actually paying. But if you don’t want anyone to know who you’re paying, you could just like put something on the loss account and take it out of the bank, right? So, that would be a bit strange.

And these are the kind of things that we also look for. So, these typical, atypical accounting schemes are things that we actually use when we want to make filters, okay? So, now I’m going to show you dashboard. So, we’ve got like a picture of dashboard here, but I’m actually going to show you in Power BI.

So, this is a Power BI dashboard. So, Amrit is saying, should I be logging in changes made to these tables? There’s a feature in SAP called read access logging, which changes made to certain critical fields. So, we shouldn’t actually be changing any of the tables, right? So, we’re only going to get them by read-only access, hopefully, because we don’t want to change anything.

It is bank because I flagged it as a bank. And this kind of information is what we use to actually categorise our journal entries. And then you can also do something like this.

I’m going to show you this is the list of accounting schemes that we typically would use to check for things. So, for example, I’m going to show you this in a minute. We want to look for things that are hitting profit and loss.

And if we’re looking for other people that made changes, then we’re going to look at the CDHGR CDPOS tables for that. We do recommend a list of tables that should be monitored. And it’s actually, if you go into the audit data leaders group, you will see there is a free download in Excel, and it’s all of the SAP tables and fields that you need for each and every one of 300 recommended must-have data analytics.

So, really recommend that you get that Excel spreadsheet, because it’s going to help you be able to see, and there’s about 2000 lines. And you can see which SAP table and field is mapping to which data audit test. How do you take into account if there is a debit and a credit to revenue, or if you’ve mixed entry where you have debit to revenue and other accounts, and you have credits to a bunch of accounts? How do you know if debit revenue has the right offsetting credit?

So, usually what we will do is just take the first account, because otherwise it gets too complicated. Here you can see we’ve got something underscore, something underscore. And actually, if we look at the list of accounts we’ll show you in here, is we, you can see that you’ve got debit, credit, right? These are the accounts, debited and credited. Then sometimes you’ve got more than two lines in a journal entry. So, here we are debiting three accounts and crediting three accounts. So, what we do when we are doing this analysis, just to be simple, right? Because otherwise it’s going to be too complicated.

We first are going to sort the entire table by order of amount on debit. And then we sort the entire table by order of amount on credit, so that we can actually see what was the first account for that journal entry in terms of value on debit, and what was the first account on credit in terms of value. And that helps us to just know, okay, if I’m looking at this accounting scheme, let’s just take this one.

The 421 account is the most important account, because they’re the only one on credit. But on the debit side, the 153600 account is the most important one in terms of value for that particular accounting journal entry, okay? So, that’s the way that we do it in here, just to be, like, just to make things simple. And it’s actually what SAP does in some of their reports when they are saying, what’s the offsetting account? And the offsetting account will be the one that holds the most value, okay? So, having a look at this analysis of accounting scheme dashboard, this is our first data analytic.

In this data analytic, what we want to do is we can see, you know, if we’re interested in any particular transaction codes, we can actually have a quick look at them. But what we can also do is say, all right, I want to see everything that’s manual, first of all. And then if I click on manual, then we see that the graph gets updated.

Then what we want to do is we can say, all right, have we got anything that looks a bit odd? So, when I was doing the categorising of our journal entries before, so that was when we are in here, and we’re doing this category, and I’m filling in this manually, I’m saying this is a supplier payment. When I’m doing that, I’ve also got another column on the side where I just say, this is odd, this is odd, this is odd. So, if I see anything that I think is odd while I’m doing that, I flag it in there.

That flag will come through into here. So, I can say, I want to see anything that somebody has actually flagged for checking. And then we also have our potential risk. So, this potential risk is what’s coming through from the PDF that I showed you where we have like, you know, if you, let me just open it up again, this one, if you debit this account and you credit this account, what’s the risk to do with that? So, if these rules, and this is just an example, but you can actually set up your own rules based on your own knowledge of your group.

But these rules are the things that we are putting into this filter. So, once you have actually already sorted out those rules, and you’ve already made your general ledger, one line per general entry, the list of debit accounts, the list of credit accounts, and you’ve got your description of what you think the general entry might be, which is what we put up here. And then you can quite easily filter based on these rules.

So, over here, it’s getting quite exciting, because now we’ve got manual things. So, we know it’s manual. We know it’s something that somebody wanted to check, because it looked a bit strange when we’re looking at it. We know it’s flagged as a potential specific risk. And in this one, we can see the general entry value.

The size of the dot is the number of general entries. So, we’ve got three general entries for which we’ve given the description, Extraordinary Revenue Reversal Bankout. And this is the description that we also see up here. It’s also the description that I’m adding in here. This is the manual description that I added to categorise these things. So, once we’ve done that description, we can click on this one, because this one is a high value compared to the others.

So, I’m quite interested in this one. I click on that one, and then I can go down and I can see a little bit more about the detail. So, here I can see I’ve got Extraordinary Revenue 7788, that’s Extraordinary Revenue, on debit with Bankout on credit.

So, for me, that’s a little bit strange. And I was wanting to check, you know, what is somebody doing with that general entry? And there’s one of them here, which is quite a high value. So, it might not be material for the entire company, but it’s quite a high value for an individual to receive that much money if they manage to get that much money out without mentioning who they’re actually paying.

So, the point about this one is, in this general entry, you can see that we don’t see the third party. We just see Bankout and Extraordinary Revenue. So, that means we don’t know who we are paying for this, or who the Bankout is going to. And it’s quite an unusual general entry as well, because there’s not that many cases of it actually happening. You can see there’s only one dot here of this thing actually happening. So, this is a way that we can look at financial statements, fraud, and have a look around them.

So, you can see there’s a little bit of work in the beginning. One last thing, just before I go away, is that once you’ve done all of that mapping and the categorising, you can have a little thing here to say, have I seen this before? So, you might do quite a lot of work in the beginning, setting up the categories of your general entries. But once you’ve done that once, you can then run this again, like maybe every quarter or something like that.

And you can just check if you have any new general entry accounting schemes. So, if in the next quarter, all of the accounting schemes are the same as in the last quarter, then you don’t have any more mapping to do again. But if you get something new, then you know that it’s worth having a look.

So, hopefully you invest, maybe it takes me about one day to do the mapping. And then after that, it’s fine. It’s going to take me an hour or so just to look at the few general entries that are new for the next quarter.

So, a little bit of investment upfront, but after that, it’s fine.

1.Join Data Leaders in Internal Audit Group on LinkedIn

2. Get SAP Data Analytics Secret Book

3. Get ASAP Tool

Top 10 Data Analytics for Fraud Detection – Part 1

Number one of our top 10 is going to be illegitimate journal entries

Products

Solutions

Knowledge Center

Legal