 Hello and welcome to this session. This is Professor Farhad and this session we would look at a vendor fraud using conditional formatting a minor data Visualization and the pivot table this topic could be covered in an introduction to data analytics and accounting or simply data analytics course auditing internal auditing or external auditing Managerial accounting for internal control purposes as always I would like to remind you to connect with me only then if you haven't done So youtubers where you would need to subscribe. I have 1,700 plus Accounting auditing funny and stacks and Excel tutorial if you like my tutorial, please like them share them It doesn't cost you anything Subscribe to the channel if they help you it means they might help other people connect with me on Instagram on my website Farhad lectures calm you will find additional resources to supplement your accounting education to have access to those excel files Especially to study for your CPA exam CMA exam enrolled agent and your accounting courses I strongly suggest you check out my website So to illustrate this concept we're gonna be working with this fictitious file where we have accounts payable clerk name The vendor the invoice amount the invoice date and the payment and this could be a large file Thousands of files that we are working with it doesn't matter or hundreds One of the most common or classic fraud scheme is when the accounts payable clerk and the vendor form an unusual relationship What happened is the vendor Will get preference treatment in terms of payment or discounts from the accounts payable clerk and in return The vendor will give some sort of a bribery or some sort of kickback to the vendor after the accounts payable clerk All this happening at the expense of the company So we're gonna try to see to find out if there's any unusual relationship between the accounts payable clerk and the vendor So the first thing we're gonna look at is Since we have the invoice date and the payment date We're gonna find out how many days it's taken us to pay vendors So basically if we take the difference between the invoice date and the payment date notice here for example The payment date was February the 22nd the invoice was February the 1st and we see it's taken 21 days to pay so first we're gonna compute days To pay we're gonna we're gonna just gonna take this formula and scroll down So we have it for all the data so we can measure days to pay So that's one way to start our analysis because that's relevant if if we are paying this vendor early It means we might be giving them some preference treatment because cash is skinned The company wants to preserve cash the company wants to delay the cash payment as As much time as possible and the vendor at the same time They want to accelerate the payment as early as possible So is there any unusual relationship between the two? So the first thing we're gonna look at is just gonna look at an overview about the vent the accounts payable clerk Vendors and amount first. Let's see if the vendors are buying a lot from a certain vendor That's the first thing we're gonna look at before we look at the days to pay. So how do we do so? We're gonna create The pit we're gonna be using the pivot table now if you don't know how to use the pivot table Please look in the in the description or in the In the card above here in the video and it you'll have access to all the videos. So I'm gonna go to pivot table and Select the range it automatically select the range for the sheet because my sheet is ready and complete I'm gonna do a new worksheet and I'm gonna select the accounts payable clerk Which is the name of my clerk Adam Ashley avi bella so on and so forth. I'm gonna look at the vendor and The vendor amount and I'm gonna put the vendor as column this way it looks It it it's it's what I really wanted to see the The accounts payable clerk versus the vendor in the amount. So this is the table that I built now again tables and dollar amount Oftentimes they're not as useful. It's what you want to do You want to turn this into a percentage and there are many ways you can do that So one way I'm gonna do so if I'm gonna turn those numbers into percentages to find out if the if the the dollar amount is Are concentrated for one vendor if there's any special relationship at these it appears if there's any special relationship between one vendor in The account payable clerk a certain account payable clerk so I'm gonna click in here anywhere and I'm gonna set the value to Percentage of column total so turn everything into a percentage. It's way better because it's gonna show you the size now Again, this is not gonna give me much what I'm gonna be restoring to now is kind of a minor a minor Minor feature of data visualization. So this is I can I can select all this data. I can go to home conditional formatting and what I want to do is I want to look at color scales and the color scale I'm gonna be using is the red yellow and green because we're all familiar with red yellow and green and I'm gonna select this and what's gonna happen is this it's gonna look at my data And it's gonna show me the color and if if if if if a percentage is large more than 50% Well, it's gonna show it in darker than the others and if a number that's very large like 100% maximum It's gonna show it to me in red and notice here. It seems Bill bill our account our accounts payable bill buys the only person that buys from SLC ink That's unusual. Why why aren't other accounts payable for buying from SLC link? Also, we can see that Justin buys almost 60 percent 58 percent From dizzy 58 percent, but also we have to we all see we only have a Justin and Maggie So this is what we have from the screen that that doesn't mean much We just have maybe we want to look at this a little bit further But now we know that Bills is concentrated at with SLC ink So let's see if there's any if there's any unusual relationship between bill and SLC ink the next thing I'm gonna look at if I'm gonna I'm gonna be creating a pivot table to find out how many days it's taken to pay SLC SLC ink so I'm gonna go back to my information to my original information insert pivot table I'm gonna do the same thing for now. I'm gonna do the accounts payable clerk the vendor and I'm gonna put the vendor as column and I'm gonna measure it by the days to pay because this is what I'm measuring Some of the days it's not it's useless. I want to know the average days at stake and to pay a vendor So I'm gonna click on value Field setting here and I'm gonna be looking at the average. I can also do so from from here Okay, but now I need to reformat this so it makes more sense because it's per days I'm you reformat this and take out all the zeros format and number No decimal because it stays it doesn't make any sense. I formatted my data now remember I'm looking for bill here I'm looking for bill and I'm looking for bill and the relationship between bill and SLC if you remember we've we identified that SLC could be a suspicious Transaction so let's take a look So bill that takes both 32 days on average to pay his vendors But he's paying SLC in 14 days. So I see something unusual here now I can also do a data visual Visualization as well. Let me just kind of show you what I can, you know It's kind of this is why this is a powerful tool because it shows you immediately if there's anything unusual So if I look at this, let me go to conditional formatting again Because I know what I'm looking for but let's assume there's something else. I'm not seeing let's go to home data visualization conditional formatting Red yellow and green click on it notice here. It's very low. It so bill is Paying SLC very quickly. It's taking bill. It's cutting the check every two weeks. However on average Bill pays the employees every the vendors every 32 days. The company pays on average 36 days Notice here the company on average as we just highlighted in maybe write something here So here's what I'm here's what I'm looking at. I'm seeing that on average the company pays On average 36 days, you know, we notice here that Tom pays on average 19 days 15 days pays Journeys in 15 days in facade LLC in 37 days. Also, this is made might be a little bit unusual Why 115 why 137 but definitely definitely bill if his average payment is 32 days Why is he paying? SLC in 14 in days so that this is this is unusual. Also, we see here Bala 25 days But 29 days on average others others are 38 days So if we look here, we see that on average So this is also a little bit unusual. Why is he paying cold entertainment? Maybe maybe cold entertainment do give us a discount And that's why we pay early. I'm not saying anything wrong I'm just saying those are the things we we should be looking at. Also Adam pays cold entertainment Little bit early 27 days and his average on average It's state it takes Adam 42 days to pay his bill, but I'm gonna be focusing specifically on SLC in the 14 days once I know that's the case I can click on it and Here's the all the invoices from SLC if I'm doing this investigation The first thing I do I'm gonna look for duplicate So I'm gonna highlight the invoice amount and see if there's any duplicate payments because duplicate payments are the first Are the first sign of something, you know, something could be wrong It doesn't mean we may not have duplicate value So I'm gonna click on conditional formatting and go to duplicate values and it's gonna highlight my duplicate value So I have two duplicate values one check for amount 273 twice and one for 405 now They're on a different dates. That's fine. They're on different dates But what what's the possibility that we're buying the same amount that would be an invoice the same amount It could be the case, but it could be suspicious that we're cutting two checks for the same bill Is that could be the case that there's fraud going on We're just basically double-billing the company and paying the invoice twice. Is that the case or not? Well, that's a starting point in our investigation. What else can I look at in terms of? In terms of average days if there's anything unusual, I mean, this is this is for you But this is where this is the starting point of the investigation So notice here we use the pivot table We use conditional formatting conditional formatting is very powerful either to identify the duplicate real quick and identify the duplicate checks The duplicate amount the duplicate amount that doesn't mean the really duplicate They could be legitimate, but you can find a duplicate and the pivot table help us organize the data in a way that make sense To us so make sure if you don't know how to use conditional formatting if you're not use the pivot table I do have tutorial you really want to be comfortable with these items and this is basically a Minor form of data visualization minor minor just coloring. This is basically a coloring scheme going through conditional formatting as always I would like to remind you to Check out my website for additional resources Especially if you are accounting student or CPA candidate Farhat lectures calm provide additional resources for you to succeed in your education Especially pass your professional certification exam. Stay safe and good luck