 Hello and welcome to this session in which we will keep working in step one of the audit data analytics ADA. In step one we plan the ADA and step two we access and prepare the data. In step three we make sure that we review the relevance and reliability of the data. Step four we perform, we execute the steps and in step five we evaluate the results. And within step one we said one of the purpose or objective of the ADA is to either we can use it to assess risk assessment which is we learn about this in the prior session. So basically we're done with this step. In this step we're going to see how do we use ADA in test of control, substantive testing and evaluating conclusion. Now before I proceed I should have mentioned this in the prior session which I did not. In order to understand how to use ADA in test of control first you have to have a good understanding of test of control. The same concept applied to substantive testing and analytical procedures the same thing applied to evaluating the final results which is called the final review. And the same thing applies when you study risk assessment. Don't try to understand how ADA helps in risk assessment if you don't understand risk assessment from the get go. So that's what I'm trying to say because in this session I'm going to be showing how does the ADA help in this process. So you have to understand, you have to have a good understanding of the process. Now whether you are an accounting student or a CPA candidate I do have resources that's going to help you understand these topics test of control, substantive testing, conducting the final review. You can keep your CPA review course. My material is supplemental, it's useful additional information to your CPA review course. I teach the material differently. I provide you alternative resources which will help you understand the material which will help you do better on the CPA exam. Your risk is one month of subscription, your potential gain is passing the exam. I do have resources for an auditing course as well as other accounting courses. My CPA supplemental resources are aligned to work with your CPA course whether it's Becker, Roger, Wiley or Gleam. I also have access, I give you access to all the AI CPA previously released questions all of them for the past eight years which is approximately 1500 questions with detailed solution. If you have not connected with me on LinkedIn please do so. Take a look at my LinkedIn recommendation, like this recording, share it with other connect with me on Instagram, Facebook, Twitter and Reddit. So we're going to start to dig deep little more into how to use ADA and test of control, substantive testing, evaluating the results, the conclusion, starting with test of control. The first thing I want to let you know which is you're going to be surprised or maybe positively surprised, neither the blueprint nor the AI CPA provide any specific guidelines for for using ADA and testing testing of control. However, just basic common sense will tell you that you could use ADA and test of control just like you can use it in risk assessment and substantive testing. Test of control is basically steps that you'll undertake with basically what says test of control to evaluate whether the client's internal controls works effectively in preventing and detecting material misstatement at the assertion level. So you could use ADA techniques in test of control although they don't tell you you can do it but we know that you can do it. So how can we use it? Of course we can. We can do the performance. What is the performance? When we independently execute the procedures or control that were originally performed by the client personnel? For example, we can run some numbers through our accounting system, through our own software. Let the software re-perform the material. This is part of ADA. We can check for anomalies as a result of control failure. Just take a look at some exceptions just by running some statistics, run statistical computation, whether to validate or dispute the effectiveness of control. So you could use ADA in various format. Another example would be let's assume we want to check whether the payments of customers are properly posted to their account. So when a customer makes a payment they're properly posted. We can download the sales file, download the cash receipts file, upload both files into a software like IDEA or ACL and match sales file with cash receipts from customers. And what we're doing we want to see that when we make the sales each customer will have an account number and when we have a cash receipt each cash receipt is posted to a customer. Match those two and whatever is left only keep sales invoices with greater than zero with an outstanding balance. And this way we know we have an idea about our account receivable and we want to make sure that we are posting the payment to the appropriate account. This is basically kind of a matching what we talked about in the prior session about risk assessment. This is a matching technique we can use when we do test of control. This is an example of it. I'm just showing you that you can use it in test of control. We also can use it in substantive procedures. What is substantive procedures? It's procedures to detect material statements and the financial statements and in the notes. How can we use it? Many different ways. We can search for missing serial numbers for sales invoices for gaps in the checks. We can run descriptive statistics on data on certain accounts, certain transactions looking for amount that are three standard deviation above the mean or below the mean. We can run regression model on different figures. We can compare internal data like from the general ledger to external data for example from bank information similar to risk assessment procedure but here we are dealing with substantive procedures so we can use the same techniques. An example would be for example we can run regression to predict cost of goods sold given direct material and direct labor assuming here directly labor and direct material are a large proportion of cost of goods sold. Also we're assuming here that if this is a manufacturing environment the manufacturing overhead is some percentage of direct material or direct labor so we can make some adjustment to the formula and find out whether our cost of goods sold is properly computed or come close to it using analytical procedures. I'm sorry not audit data analytics. Analytical procedure is part of substantive procedures. We can use analytical procedures. Analytical procedures are really a natural extension of audit data analytics. They just it's just analytical procedures. They work hand in hand. They're very natural together and by the way analytical procedures or AP are covered in depth in a separate lesson so this is an important topic and analytical procedures are an important topic on the CPA exam. So on my course I have one whole lesson about the ratios and I have sub lessons for each cycle. How can you use analytical procedure for sales, for payroll, for inventory so you have to have a good understanding. Where can you have this good understanding? Go to farhatlectures.com in my CPA review course or in my auditing course if you are an accounting student but you need to understand this topic inside out because remember APs can be used at the beginning of the audit during a risk assessment during the audit while during the substantive procedures and at the end of the audit so they're used in many different places. And what are analytical procedures? It's when we develop expectation or prediction of the balances okay or the ratios using prior period figures. So we look at prior year and we see that the ratios should be current ratio should be 2.0 that's the expectation or 2.1 or 1.5. Now we're going to see if we're going to get that number if not we need to understand why not. So we anticipate resolved based on relationship among financial statement that's also part of analytical procedures. For example if we have a million dollar in debt and our average interest rate is 10% we expect to have interest expense of 100,000 just this is basically based on the relationship. We can compare our company information to industry data if the current ratio for the data is 2.5 we can take a look at our current ratio and see how much it is. So comparing data it will tell us whether we are within the industry above or below but it's going to tell us something about our figures whether they are reasonable or not reasonableness. We can examine part of analytical procedures financial with non-financial information for example we can look at our sales and website visits maybe there's some relationship between them okay this is some sort of analytical procedures comparing two things that should make sense okay comparing things that should make sense the more website visits we have the more sales we have the less website visits we have the less sales we have generally speaking we could look at trend analysis look over the years over time how the account is behaving and try to kind of draw a conclusion or make a judgment. APs analytical procedures are best used when there's a plausibility and protectability of the relationship the best example I can give you is that in interest expense there's a relationship between those that's not the only thing you could have a relationship between sales and account receivable for example every year 20 percent you know account receivable represent 20 percent of sales and some years they might be a little bit higher some some years could be a little bit lower but should be around 20 percent if it deviates we need to know why there's a plausible relationship there should be a plausible relationship between account receivable and bet that expense bet that expense is usually 5 percent of receivable well why is it more than five why is it less than five it should be around five it just it gives you an idea where to focus on also it works better in stable environment you know for example it makes a difference whether your sales are spread out evenly throughout the year or your sales are seasonal you have to you have to take into account that environment availability of the data for example availability of industry data whether it's available or not it makes a difference if there is no data what are you going to compare yourself to okay also numbers coming from good control will help with that and basically analytical procedures you don't need even a fancy software for ADA excel can perform analytical procedures and frankly every time I talk about analytical procedures I say this again and again that when I was in practice because we did a lot of reviews in my CPA firm I did a lot a lot of reviews and part of reviews is to run ratios horizontal analysis vertical analysis so I'm I used to call myself an expert in and political procedures I like this topic because it makes sense it tells you where the red flags are really once you understand the business and political procedures are a powerful powerful tool in the hands of the especially the people that knows understand the business the big picture and here comes looking at the evaluating results which looking at the big picture at the end when you're evaluating the results you should have more knowledge about the company because you went through the audit you learned a lot so far maybe it took you three months six months to complete this audit you have a deeper knowledge about the client and the deeper knowledge you have the more effective your ADA now you have tools plus knowledge you have the tools you have the resources plus you have the knowledge so now you're working with reliable numbers you understand the business and you have the tools to run what you need to run and here what you're doing is when you run numbers using ADA you are looking for comfort something something that confirm your expectation things should make sense or it's going to reveal unusual or troubling accounts or figures and usually when I started my my when I started my career I was impressed with the partners like wow how did he know how did he know this immediately I mean I would submit the work and immediately the partner would say take a look at this account take a look at this number those don't make any sense I was like wow this guy is really good mark his name his name was mark I was like oh how did he know this three months later as I was doing this I was like yes now it makes sense this doesn't make any sense you know I should I should take a look at this a little bit further so things will start to make sense as you learn more about the business because things are predictable in the business world and if they're not predictable you need to know why so ratio analysis you can conduct ratio analysis at the end to make to make sure numbers make sense you can run vertical analysis horizontal analysis and what's vertical analysis basically looking at the income statement for example an income statement you have sales you have cost of goods sold you have various expenses so what you do is you compare everything to sales this is what a vertical analysis is or horizontal analysis basically looking at an income statement across across time horizontally from period to period to see what's you know the changes in sales the changes in cost of goods sold from period to period as a dollar amount and as a percentage so those are for example you can do the same thing on the balance sheet compare everything to assets relationship to asset and a vertical analysis or compare the balance sheet from period to period which is horizontal analysis and those are powerful tools analytical procedures but you could use some sort of a software audit analytical procedures you could use acl you could use um you could use obviously excel that's the easiest one to use you should all you we should all know how to use excel acl all that command language can do it idea the software idea can do it um you know you don't need r or python to do vertical and horizontal analysis it's pretty straightforward but this is basically how we could use ada in those various stages at the end of this recording once again i'm going to remind you that whether you are an accounting student or a cpa candidate take a look at my website i might be able to help you i have i provide you with alternative explanation alternative resources multiple choice cpa questions that's going to help you improve your knowledge you pass your cpa once in your lifetime give it everything throw everything on it it's worth it good luck study hard and of course stay safe