Speaker 1: Hello and welcome to the session. This is Professor Farhad and this session I would use the pivot table and the Penford law to detect fraud in an actual case. This topic could be covered in an introduction to data analytics and accounting, just a data analytics course, auditing, internal auditing, or managerial accounting. As always, I would like to remind you to connect with me on LinkedIn if you haven't done so. YouTube is where you would need to subscribe. I have 1,700 plus accounting, auditing, finance, and Excel lectures. If you like these lectures, please like them, share them, subscribe to the channel. If they help you, it means they might help other people. Connect with me on Instagram. On my website, farhadlectures.com, you will find additional resources to complement and supplement your accounting education. If you are studying for your CPA exam, CMA exam, enrolled agent, if you are looking to improve your score to pass those professional certification, by all means, check out my website for additional resources. To illustrate the concept, I'm going to be working with this data. This data consists of approximately 500 sales returns transaction. The amount, I have the amount, I have the representative name, the date that the transaction took place, and the sales return transaction number. And I have eight employees that are working in this company. The first thing I'm going to do is to basically find out a little bit more about the data. So what I'm going to do, I'm going to create a pivot table per employee to see how much each employee returned in terms of a dollar amount, just to get an idea of what's happening here. So the easiest way to do this is to run a pivot table. Now, if you don't know how to run a pivot table, by all means, I'm going to show you here, but by all means, I have a recording about how to create a pivot table. So I'm going to go ahead and click on insert, pivot table, and I'm going to select my data. It's selecting everything. That's fine. That's the sales return, but it's selected. Notice the sheet is already selected. And I want a new worksheet. So I work, you know, on a new worksheet. It's easier to do. So click on. Okay. And what I'm going to do, I want the rep name on one side, and I want the amount on the other. Okay. So the first thing I'm going to, I'm looking at is how much that each individual processed in terms of return. So notice here, we have Allison, Avi, Bill, George, only $32, George is, I ask about George they told me George and Avi are new employees. Therefore I'm not really gonna, you know, it's in other words, their data, they're not as important. Justin, Savannah, Shamika, and Tom. Now what's also interesting is to look at everything in terms of percentages. So let's take a look in terms of percentages. So let's value. So value as a percentage of total, and I don't see anything unusual. It seems the employees that are, that were at the company before the sales, the percentage of sales return is approximately between 14 and 17%, except Bill, I'm sorry, except Avi and George because they're new employee, therefore they have a lower return. So a lower percentage, nothing unusual here. I don't, I don't see anything unusual. The next thing I'm going to look at is the count. How much did each employee, how many sales return transaction did they handle? Because that's relevant. So I'm going to click on this, the sum of amount and turn this into value field setting. I'm going to turn this into account, turn it into account. Again, George only had one return, Avi 22, and the remainder, Allison 82, Bill 63, Justin, Savannah. Say, so let's take a look in terms of percentages. Let's take a look at this. Allison 16, Bill 13%, Bill 13%, Justin 16, 17, 18, 15, 0.78, and 16. So the percentage wise, I don't see anything unusual, Bill's, Bill's percent as the total return count is a little bit lower than the other ones, but the dollar amount is, is almost the same, but the percentage is lower. The percentage is lower. So it's a little bit unusual, but I can't really say anything yet. Let me remove the percentages. What I'm going to do now, I'm going to look at the per average return. So each individual on average, what was the total return, total, total sales return. So I'm going to click on again, count of amount value field setting. I'm going to click on the average and I'm going to click on, okay, let me just move to two decimal points so we can see this information a little bit better. I see $29, the average return for Allison, Avi 21, Bill 34, George 32, 28, 29. So on average, on average, the return is $29. They're pretty much everyone around the average, Avi is way below average. He's no, but it's way below average. George is above average, but George doesn't really count because George had only one return. It was for $32. What I notice here, Bill, how about Bill? Look, let's look at Bill. Bill had 12, almost 13% of the return, but his average return per dollar amount is $34.24 way above, not way above yet. So above the average, because everybody is clustered around the average. This is Justin, Savannah, Shamika, and Tom. If we look at those, those are the employees that were working. Let me just highlight them in red. They are hovering around, I would say between 27.22 and 29.35. However, when it comes to Bill, Bill did not have as many returns as them because remember the count, the count for Bill, if we go back to the count, let's go back to the count. How many return did Bill return? He returned 63, percentage wise was 13% below all the others, but his average sales return is more than all of them. Guess what? I'm going to zoom in on Bill now. So let's zoom in on Bill and there we go. Now I have the data only for Bill. I'm going to start to work with the data for Bill. I'm going to start to zoom in on Bill's data. Here's what I know about this company. I know that any return below $50 does not need manager's approval. Any return above $50 will need manager's approval. So what I'm going to be looking at now is since Bill's average return is more than $32, I want to see if Bill has a lot of return closer to 50. That could be suspicious, but I don't know. Let's find out. How do I find out? Bill could have hundreds, if not thousands of transactions. Here's a function in Excel that allows me to do so. It's called the left function. I'm going to activate this, activate it, type left, open parentheses. I'm going to click on the character and I want them to pull the first character in this text, which is 4. The second one is 2, 1, 13, 1, $30, 3, $26, 2, so on and so forth. Now I have this data. What can I do? I'm going to count how many returns that Bill have as 10, 20, 30s and 40s. Well let's do that. So I'm going to do the count, count 1, 2, 3, and 4, and now this is the digits. This is the digits. And I'm going to do the count. How many transaction that Bill had at $10, $20, $30, and $40. Again, I can do so with the count if, so I'm going to click on count if, and I'm going to click on this column, count if, let me just activate it, okay. The range that I want is this range right here. How many 4s, how many 1s, how many 2s, so on and so forth. And the criteria is any number I want. For example, select the number 1 here, the digit 1. Click on okay. He had 9 transactions with 10. I'm going to pull this, so let's see the total, 13 transactions with $20, 10 transactions with $30, 10 transactions with $30, and 31 transactions with $40. Now I'm going to click to add the total. I'm sorry, I'm going to sum it, yeah, the total. Let me choose the right function, sum, and I'm going to sum the total. So in total, he had 63 transactions. Let's look percentage-wise, percentage-wise, it's 9 divided by 63, and I want to divide everything by 63, so I'm going to put a dollar sign around the letter, keep it fixed, scroll down, turn this into percentages, and what I notice is the majority, the majority of Bill's return were in the $40, 31 return were in the $40, which is half of his return. Now could this be suspicious? It could be suspicious, it may not be suspicious, now what I want to find out is what about the other guys? What about the other people that work with Bill? Are they also having the majority of their return in the $40? And this is where we use, this is what I'm using here is the Benford's Law, although I'm not going through the Benford's number because I don't have all the way nine digits. If I had nine digits, I would have showed you the whole thing, if I had nine digits, I would have showed you, you know, Benford's expectation, but I don't have all of them, that's why I'm not going to use Benford's Law fully, but I do have in the description if you want to see, if you want to see it in action. So this is what I know about Bill now, it's something a little bit suspicious, but I'm not going to be suspicious until I look at the other individuals. Maybe that's the norm in this company, maybe most of the returns are people that buy stuff at $40 and they return it. Why not? That could be the case, I don't know. Well, to find out, I'm going to go back to my data, I'm going to filter my data, I'm going to keep everyone except Bill, and I'm going to do the same thing. I'm going to run the digit count for them. So I'm going to click on, I'm going to click on left, open parentheses, I'm going to choose this digit, and I want you to pull my, the first digit, which is three, I'm going to take this and I'm going to apply it to all the others, all the other employees, and I'm going to do the same thing. I'm going to do a count and see what happened here. So we have digit one, two, three, and four, and I'm going to do the count, and I'm going the count if, count if, I want to count this data, the criteria is one, two, three, four, pull them separately, click on okay, and I want numbers, not percentages, so let me change this, I want number 103, okay, let's do the total, nope, total sum, and let's sum them, and let's do percentages, we have 415 transactions, other than, other than, other than Bill, okay, that's 24%, and let's do percentages, there we go. Now let's just make sure I add the total of percentages, they should add up to 100, and they do add up to, add up to 100, let me just make sure, all the way up. They add up to 100, everything, everything looks good. So when I look at all the other transaction, it seems they are 24% return with $10, $20, 27%, 26%, which is they're pretty much, pretty dispersed, notice they're pretty dispersed, except when I look at Bill's return, so we look, when we look at all the others at $40, they represent 21%, and Bill's $40 return represent 49%, and he had 31 of, we had 88 total returns at the $40, so if we look at 31 divided by 88, that represent the majority, 35% of the transaction that, that are within $40, $40 or above, were handled by Bill. So what do you think? Should we look into Bill? And I would say, that's a good idea, let's start our investigation there. So this is how we use the pivot table, this is how we use the Benford Law, to start, which is the starting point. Now Bill could, this could be a coincidence, so if you're conducting an investigation like this, you cannot, you know, start to accuse Bill of anything, you just have to do further research, to find out, for example, one thing I can do, I can look at what time Bill's work, maybe that's a factor, maybe when Bill's working, does he work with somebody else, is he working alone, maybe I want to monitor Bill a little bit more now, going in the next two, three months, to see what's happening. So this is what you would look at, to handle this investigation. So I hope I gave you an idea, maybe you do have other ideas for me, please type them in the text below, as always, I would like to remind you to like this recording, please subscribe to the channel, like this recording, if you are an accounting student, CPA candidate, an accounting professional, please consider visiting my website, and if you want to supplement your accounting education, or pass your exam, by all means, subscribe, you study for your CPA exam, once in your lifetime, it's a lifetime investment, good luck, study hard, and always stay safe during those coronavirus days. Good luck.