 Hello, and welcome to this session. This is Professor Farhad. In 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 resource. To illustrate the concept, I'm gonna 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 and my in this company. The first thing I'm gonna do is to basically find out a little bit more about the data. So what I'm gonna do, I'm gonna create a pivot table per employee to see how much each employee return 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 gonna show you here, but by all means, I have a recording about how to create a pivot table. So I'm gonna go ahead and click on insert, pivot table, and I'm gonna select my data at selecting everything. That's fine. I don't need the sales return, but it's selected. Notice the sheet is already selected and I want a new worksheet. So I work, you know, and a new worksheet, it's easier to do. So click on okay. And what I'm gonna do, I want the rep name on one side and I want the amount on the other. Okay? So the first thing 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, 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 say anything unusual. It seems the employees 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 see anything unusual. The next thing I'm gonna 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 gonna click on the sum of amount and turn this into value field setting. I'm gonna 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. 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 percent as the total return count is a little bit lower than the other ones. But the dollar amount is almost the same, but the percentage is lower. The percentage is lower. That's a little bit unusual, but I can't really say anything yet. Let me remove the percentages. What I'm gonna do now, I'm gonna look at the per average return. So each individual on average, what was the total return? Total sales return. So I'm gonna click on again, count of amount, value field setting. I'm gonna click on the average. I'm gonna 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. There are 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, Bell, how about Bell? Let's look at Bell. Bell had 12, almost 13% of the return, but his average return per dollar amount is $34.24, way above, not way above, yeah, it's way above the average because everybody is clustered around the average. Notice 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? Only return 63% wise was 13%. Below all the others, but his average sales return is more than all of them. Guess what? I'm gonna 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 gonna start to work with the data for Bill. I'm gonna 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 gonna 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 transaction. There's a function in Excel that allows me to do so. So I'm gonna, it's called the left function. I'm gonna activate this, activate it, type left, open parentheses, I'm gonna click on the character and I want them to pull the first character in this text, which is four. Four, the second one is two, one, you know, 13, one, $30, $3, $26, two, so on and so forth. Now I have this data. What can I do? I'm gonna count how many returns did Bill have as 10, 20, 30s and 40s? Well, let's do that. So I'm gonna do the count. Count one, two, three and four. You know, this is the digits, this is the digits and I'm gonna do the count. How many transaction did Bill had at $10, $20, $30 and $40? Again, I can do so with the count if. So I'm gonna click on count if and I'm gonna click on this column, right? Count if, let me just activate it. Okay. The range that I want is this range right here. How many fours, how many ones, how many twos, so on and so forth. And the criteria is any number I want, for example, select the number one here, the digit one. Click on okay. He had nine transaction with 10. I'm gonna pull this. So let's see the total. 13 transaction with $20, 10 transaction with $30, was 10 transaction with $30 and 31 transaction with $40. Now I'm gonna click to add the total. I'm sorry, I'm gonna sum it, yeah, the total. Let me choose the right function. Sum and I'm gonna sum the total. So in total, he had 63 transaction. Let's look percentage wise. Percentage wise, it's nine divided by 63 and I wanna divide everything by 63. So I'm gonna put a dollar sign around the letter to keep it fixed, scroll down, turn these into percentages. And what I notice is the majority, the majority of bills 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 digit. If I had nine digit, I would have showed you the whole thing. If I had nine digit, I would have showed you Benford's expectation, but I don't have all of them. That's why I'm not gonna use Benford, Benford's law fully, but I do have in the description 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 gonna 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 gonna go back to my data. I'm gonna filter my data. I'm gonna keep everyone except Bill and I'm gonna do the same thing. I'm gonna run the digit count for them. So I'm gonna click on. I'm gonna click on left, open parentheses. I'm gonna choose this digit and I want you to pull the first digit, which is three. I'm gonna take this and I'm gonna apply it to all the others, all the other employees. And I'm gonna do the same thing. I'm gonna do a count and see what happened here. So we have digit, one, two, three and four. And I'm gonna do the count. I'm gonna activate 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. Total sum. And let's sum them. And let's do percentages. We have 415 transaction 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 and the $40. So if we look at 31 divided by 88, they represent the majority, 35% of the transaction that are within $40 or above were handled by Bill. Now, what do you think? Should we look into Bill? You know, 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 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 bills 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.