 Hello and welcome to the session. This is Professor Farhad and this session we would look at an introduction to PivotTable using Excel. This topic could be covered in the Data Analytics for Accounting or an Intro to Data Analytics course. Be in the future also tested on the CPA exam as the CPA exam will be integrating data analytics into the field. As always I would like to remind you to connect with me on LinkedIn if you haven't done so. YouTube is what you would need to subscribe. I have 1,700 plus accounting, auditing, finance and tax lectures. This is a list of all the courses that I cover. If you like my recording please like them, share them, put them in playlists, subscribe to the channel. If it helps you it means it might help other so share the wealth connect with me on Instagram. On my website farhadlectures.com you will find additional resources to supplement your accounting education or if you are studying for your CPA exam, enrolled agent exam or CMA exam. If you're looking for those extra points to pass your professional certification by all means check out my website. We're going to be using this data to illustrate how PivotTable works. Now the good thing about Excel it can contain literally millions if not billions of cells. That's the good news. The bad news is you have so much information that it's all information overload. So what do we need to do? We need the way to be able to summarize and manipulate the data and extract information from the data that's meaningful for business decision and this is basically what data analytics is. So what we're looking at here is rejected loans by a bank or a financial institution. So we have the rejected loans in one column and I'm going to highlight what we are giving is you need to understand the data before you start to work with the data. We have the rejected loans each one with A with an application number, the amount requested, how much did the individual request it as an amount, the dollar amount, application date, when did the application took place, loan title, basically loan title is basically the purpose of the loan. Is it for a car, for a vacation, home loan, so on and so forth. Risk score and this is important. This is basically your credit score and your credit score could be up to 850 the maximum and it could be low as 400, 300 who knows. Okay, so they have the risk score and that's important. I'm going to highlight the risk score because that's an important factor in giving out a loan and what they did for this data, they kind of broke the risk score into different categories. For example, between 850 and 750, it's an excellent below 750, down to 650 is very good, so on and so forth. So we also have what's called a risk score. Risk score, risk score bucket, risk score bucket and risk score, they're related, but you're going to see why did we do a risk score bucket shortly. Also another important consideration when you are giving out a loan, something called debt to income. How much debt do you have relative to your income and what they did for this example, they have three categories, either high, low or medium and how did we determine high, low or medium. So simply put, if your debt is $100 and you have income of $1,000, well let's assume that of $90, income of $1,000, your debt to income is 9%. Well, if it's below 10, you have a low debt to income ratio, below 10. So below 10, below 10%. Let's assume you have $150 in debt and you have an income of $1,000, so if we take 150, the debt relative to the income, that's 15%. Well, if you are between 10 and 20, you are considered to have a medium, so that's how they tag you, 15. And let's assume you have $320 in debt relative to $1,000 of relative to $1,000 of income, your debt to income ratio here is obviously 32%. Now you have a high, so it's above 20. So it's below 10, 10 to 20 and above 20 is high, above 20%. And this is what they did here. I'm going to need to delete this because you cannot have extra data when you're doing the pivot table. So we're done with this. So here they have the number for you and they have a bucket as either high, low or medium and you will see shortly why this is necessary. They have the zip code, your zip code, the state for the customer and the employment wealth. That's also extremely important when you're given out alone. First of all, the person has to be employed, too. It matters how long you've been employed. That's an important factor. I'm not sure if I mentioned at the beginning of this recording, I forgot that I was a loan officer myself in the beginning of my career and believe me, those are the three factors we used to take into account, mainly the credit score, your debt to income and the employment plan, how long you've been at that place. Now having this information, now we need to learn a little bit more. So we know, we already know this, all these applications and we have here around 625,000 application. I mean, I cannot keep scrolling down, but we have 625,000 application. Now as the manager, risk analysis, the CEO, the CFO, somebody who wants to buy this company, whatever the reason is, we want to learn a little bit more, a little bit more about this company, a little bit more about their applications. How do they process their application? How do they manage risk? Just learn a little bit more. So the first thing we're going to do, we want to know how much, how much of their debt to income buckets, debt to income buckets are high, low, medium that they were rejected. They were all rejected, but we want to know how much of them and we're interested in the low because if their debt to income low, why were they rejected and how many were they rejected. So we won't just kind of get an idea. Well, one way to do it is to sort this copy and paste and do all of this, or we could run the pivot table. So let's go ahead and show you how you would run the pivot table. You'll go to the insert button here, you would click on pivot table and what's going to happen, it's going to select a table range and just while I know this data is already cleaned and sorted. So the data is, you don't have to do anything with the data, maybe in another session would learn about how to clean, make sure your data is clean and sorted, but now it's good. So notice it's selecting the data that I want to analyze. Notice we're up to 645,000 cells completed, rows completed in this data. So it's a lot of data. It's already selected. What do you want your output? And I'm going to put a new worksheet. Usually you put it in a worksheet because you have more room to do. So you click okay. Now it's going to open up a new worksheet and on the right hand side you're going to have the pivot table field and notice here all the titles or the headings of the Excel sheet are listed here. So what are we looking for here? We want to know how many of our rejected applications have a high, low, or medium, medium debt to income bucket. So the first thing I'm going to select, notice here we have rows. So the first thing is I want to select my DTI bucket and it's going to go down to the row. Okay, that's what I'm looking for. So notice here the row label, high, low, and medium. And I want to compare this to the rejected loans. How many of the loans were basically to find out. So I can click on rejected loans and this is basically or it's already knows it has a value because it has numbers. Therefore, notice it has some of the rejected loans. I don't care about the sum. This is the sum of the application. This is useless for me. What I want to know is the count. So you would click here and change the field into the value of the field setting. You click on it and I want the count. In other words, how many number of applications are within each category, high, low, or medium, click on okay. Now I have the numbers. I mean, I can read them clearly. I'm going to use, I'm going to reformat it. No, that's the most necessary. And we see here that of the 645,000 application rejected 312,000 almost 313 where they have a high debt to income, which is that's understandable. But we notice 171,000 has a low debt to income ratio. Also, if you want to kind of get an idea, I like percentages. So if you select a number, you right click, and you click value as a percentage of the grand total, you will find out the percentages. So the majority, I mean, half of the loans, they have a high debt to income, which is if you have a high debt to income, you're going to be rejected. But we also have 26, 1 fourth of them, they have a low debt to income, they were rejected for some other reason, because that's not the only factor that they took into account. But that's one of the main factor. So let's hold on this factor. Let's go back to the data. What could be another important factor? Well, employment wealth. So basically we looked at this factor. Employment wealth could be an important factor too. How long you've been working is determined. So if you've been at your job, for example, less than a year, I remember if you just get a job, we just more likely would not get a loan, because especially if you are young or you change a lot of jobs before or you were unemployed for a period of time. So we want to know how does the unemployment affect the rejection of the loan? So we can do the same thing. We can look at the rejection loan versus the unemployment wealth. So I'm going to go to insert. Now it's selecting everything. Okay. Now I need the employment length, and that's going to be my row, less than a year, one year, 10 years plus two years, so on and so forth. And I'm going to look at the rejected loans. That's what I'm interested in. That's basically what I'm looking at. Again, I have to change the rejected loans into change the value into account. Now I have the account. Let me change the format of the numbers. Right click, or you could just have this one. Take out the decimal points. Okay. Notice again, once again, of the 645,000 application, almost half a million were less they had, they held employment less than one year. And the 34,000, they were rejected, although they had 10 plus year employment. Now there could be other reasons why they were rejected. That's fine. Also, I like to change this into a percentages. I just, that's what I like to do, percent of the total. So let me, I just, I like to see percentages. So 76%, the best majority were, they were there for one year or less. So that's why it did not work. That's why they were rejected. But I'm interested in those 5.36, the 0.979. Well, why weren't they, why weren't they, so the third important factor in determining whether you qualify for a loan or not is your credit score. And the highest credit score is 850. You have, you have excellent. And as your score goes down, you go from excellent to very good, good, very good, good, fair, poor and very bad because there's a risk score bucket. Again, let's see, based on the risk score bucket, how many application were rejected, based on that factor alone. Again, we're going to run a pivot table, insert pivot table. Again, the data is selected, click on new. Okay, I'm going to look at the risk score bucket, okay, which is going to be excellent, fair, good, poor, very bad, and very good. So this is like 850. This is like, good is 800, so on and so forth. Now I'm going to click on the rejected loans and again, change the value and to count. And as expected, the majority is fair or yeah, fair, fair is and poor, actually poor and fair. But let's look at percentages. Again, I like to look at percentages just to see what percentages were rejected. The majority should be fair and poor. Yeah, fair is a lot poor, very good, those were rejected. I'm sorry, very bad. But notice we have 0.39 of the applicant, they had an excellent credit but they were rejected. Well, it could be because they either did not have less than a year of unemployment or high debt to income ratio. But again, what you can do, you can click on this and it's going to take you to those 0.39. Notice they have excellent credit but notice here, for example, this individual here, they have a 66.87 debt to income ratio, this individual here, the first row. I would know it's this one has 0.45. This is a low, it's still a lot 0.2. This is a lot, I'm sorry, 0.45 is a lot, 66 is a lot, 29, 25. For example, this is 0.45. So this one has an excellent credit, low debt to income ratio, it must have been the unemployment, well, it's not. So here, notice the unemployment is five years, the unemployment length is five years. For example here, maybe the amount requested is a lot, maybe if we add this amount, the $5,000, maybe if we add it, maybe it will bring the debt to equity ratio to a higher number. That could be the case that if we added the amount that requested, that could be a reason too, because after we add the $5,000, maybe the debt to income will jump over 20. That could be a possibility. For example, this individual here, 0.0052, debt to income, which is low, excellent credit score, but maybe it's the $18,500 and they have a good employment, maybe it's they're requesting too much money. We don't know, but at least we're getting an idea of what's going on here. Now we can study these numbers further, if need be, if need be. So let's go back. Now we have all those three data analyzes. What we can do also, we can put them all in one analysis. So let's take a look at this analysis to get an idea about what we're looking at, what are the factors, the main factors that are rejecting someone from getting along with our company. So let's go back to the data and this is the beauty. You don't have to change the data. Let's do a pivot table again, run a pivot table, insert pivot table. The tables are selected. We're going to look at the rejected loans. We're going to look at risk score bucket, so rejected loan, risk score bucket. We're going to look at the DTI bucket. Now we're going to look at the employment length all together. Okay, let's change this to the count, value to count. Now it looks better. Now let's take a look only at the excellent score. This way we can filter down and focus only on one section. Notice here what you can do. You can click on filter, select the field and I'm going to only keep the excellent people with the excellent score because that's what I'm interested in. Why were they rejected? So there you go. We have 2,494 application with excellent score that they were rejected. Now some of them they had a high debt-to-income ratio, 7.62. That's understandable because they had a high debt-to-income ratio. Well, that's understandable. They were rejected. Then here within high debt-to-income ratio, they tell us the years of experience. Just high to that income ratio is good enough to reject them. Here they have low debt-to-income ratios. So they have an excellent credit. So of the 24, 94, 1,339, they had a low debt-to-income credit. Of this amount, 457 had less than one year. Okay, we rejected them one year. We rejected them. But how about the 10 year or the nine year? Since we have only 30 applicants in the nine year, let's take a look at them and see why. Let's examine this data further. Let's click on it. 30 applicants. And here are the 30 applicants. Notice here, they have an excellent score bucket. The credit score is above 800, which is excellent. That too, debt-to-income ratio also low. Notice it's low and it's low bucket. So everything is good and employment length is not. So those are, they're supposed to be, based on this information, they're supposed to be good. But why they could be rejected is because they are asking for a large amount relative to their income. So that's, that's why, maybe once we add this amount, once we add 35,000 to their current debt, maybe their debt-to-income goes above 20% because once they're above 20%, we definitely don't want those individuals. We don't want those loans. So that could be the reason. But the point is now we can drill down and see those 30 applicants and maybe, you know, look at maybe some common features, analyze it further, work with them, maybe down the road. I don't know, but now we have the data to work with. And this is the beauty of, the beauty of the pivot table. I was able to drill down into the data very quickly and analyze the applications very, very quickly. Again, here we have seven applications. The problem with those, they have excellent credit, high debt-to-income, but they have nine years. So notice their credit score is good. The problem is debt-to-income. It's already above 20%, above 20%. So that's why they were rejected. So I hope you find, I hope you found this tutorial pretty helpful. You could use this pivot table for any type of data as long as you understand the data, as long as you know what you are looking for. Now I can run so many different, so many different studies on this data. I just selected those three because those are the most important in determining a loan. As always, I would like to remind you if you are an accounting student, please check out my website, farhatlectures.com, subscribe, study hard, stay safe, especially during coronavirus days, and good luck. And please like this video.