AI enabled Audit Sampling - Unlocking the power of VBA Automation

Introduction

The field of auditing has significantly changed over the years, due to massive advancements in technology. The most important advancement in recent times is the use of Artificial Intelligence (AI) into different audit processes & procedures. One of the most important things which auditors need to do is to collect Audit Sample to form an effective opinion with regards to the books of accounts maintained by businesses. However, the question arises - Can we use AI to generate samples? The answer is Yes!! In this article, we will understand how we can use Artificial Intelligence to generate VBA codes that will quickly generate various kinds of samples for us which will help us to improve and enhance audit quality and accuracy.

What is VBA?

VBA (Visual Basic for Applications) is a programming language that is inbuilt in Microsoft Excel and other Office applications. VBA helps us to automate repetitive tasks, analyze data, work with multiple office applications, and complete time-taking tasks in minutes, making it a very powerful tool for increasing office & business productivity. VBA can be used by professionals to streamline tasks in their own office and provide solutions to complex client problems.

"VBA can be used by professionals to streamline tasks in their own office and provide solutions to complex client problems."

Understanding Audit Sampling

As we all know, Audit Sampling is a technique used by auditors to extract a subset from larger data to form conclusions for the entire population. While there are many techniques already available, they are time consuming and subject to human errors. There are many solutions available in the market. However, they may be costly for small and medium sized auding firms. AI has become a gamechanger in this regard.

With good prompting and skills, we can easily generate VBA codes through AI which can be used in Excel, the platform we are all friendly with. All they need to do is write a proper prompt to explain steps in a simple natural language, and AI will generate the code in VBA Language to generate various kinds of samples. This saves time and unnecessary costs to purchase software for generating samples. One important benefit is that the samples can be customized from business to business. Thus, it gives flexibility and scalability in Audit Sampling for auditing firms.

The Role of AI in Audit Sampling

Let us understand how AI can helps us to generate different kinds of sample selection.

1. Enhanced Sample Selection

AI can help us to write complex formulas and VBA codes to analyze large datasets quickly and identify the areas of concern and risk which were earlier too time consuming and resource hungry. Now, the same formulas and VBA codes can be generated through the use of different AI tools like ChatGPT, Gemini, Perplexity, and so on. We just need to explain about the Column Structure of our Master Data with AI Tools, and define the kind of sample we want. It will suggest us formulas and VBA codes that will give and generate the desired sample in new sheets. This will help us form a better opinion about the fairness of the statement of accounts and detect material misstatements, as our sample will be more representative, error-free, and regular.

Let's understand this with examples of how AI can help you create VBA codes to generate different kinds of audit samples.

We will use the following Vendor Data throughout this article.

Example 1: Random Sampling

Random Sampling is one of the most widely used methods to select an unbiased sample. This method ensures every item in the population has equal chance of getting selected. Let us understand if AI can help us to generate VBA code for random sampling.

Prompt to AI:
I have Vendor Transactions in a sheet with the name 'Transactions'. The sheet has the following columns.
1. Transaction ID
2. Date
3. Vendor
4. Amount
5. Category
6. Payment Method
7. Risk Score
Please write a VBA Code that will select random rows from this Dataset representing 10% of the entire population. These rows should be copied to a new sheet named as 'Random Sample'.

VBA Code Generated by AI:

Sub RandomSampling()
        Dim ws As Worksheet
        Dim wsSample As Worksheet
        Dim lastRow As Long
        Dim sampleSize As Long
        Dim i As Long
        Dim selectedRows As Collection
        ' Initialize collection to store selected rows
        Set selectedRows = New Collection
        'Set the worksheet containing the data
        Set ws = ThisWorkbook.Sheets("Transactions")
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        'Calculate sample size (10% of total transactions)
        sampleSize = Application.WorksheetFunction.RoundUp((lastRow - 1) * 0.1, 0)
        'Randomly select rows
        For i = 1 To sampleSize
        selectedRows.Add Int((lastRow - 1) * Rnd + 2)
        Next i
        'Create a new sheet for the random sample
        Set wsSample = ThisWorkbook.Sheets.Add
        wsSample.Name = "Random Sample"
        'Copy headers to the new sheet
        ws.Range("A1:E1").Copy Destination:=wsSample.Range("A1")
        'Copy selected rows to the new sheet
        For i = 1 To selectedRows.Count
        ws.Rows(selectedRows(i)).Copy Destination:=wsSample.Rows(i + 1)
        Next i
        MsgBox "Random sample generated successfully!", vbInformation
        End Sub
        

Output

Example 2: Stratified Sampling

Stratified Sampling is a method to select a grouped or clustered or categorized sample. We have to ensure that the Sample Data should have items from each cluster. Let us understand if AI can help us to generate a VBA Code for Stratified Sampling.

We have to choose the sample in such a manner that samples represent each category such as "Raw Materials," "Machinery," and "Transportation". Let us use AI to generate a VBA Code that will do this for us.

Prompt to AI:
"I have Vendor Transactions in a sheet with the name 'Transactions'. The sheet has the following columns.
1. Transaction ID
2. Date
3. Vendor
4. Amount
5. Category
6. Payment Method
7. Risk Score
Please write a VBA Code that will select 2 transactions from each category. These rows should be copied to a new sheet named as 'Stratified Sample'".

VBA Code Generated by AI:

Sub StratifiedSampling()
        Dim ws As Worksheet
        Dim wsSample As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim category As String
        Dim dict As Object
        Dim rng As Range
        Dim cell As Range
        'Create a dictionary to store categories and their counts
        Set dict = CreateObject("Scripting.Dictionary")
        ' Set the worksheet containing the data
        Set ws = ThisWorkbook.Sheets("Transactions")
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        'Loop through the dataset to count transactions per category
        For i = 2 To lastRow
        category = ws.Cells(i, 5).Value
        If Not dict.Exists(category) Then
        dict.Add category, 0
        End If
        dict(category) = dict(category) + 1
        Next i
        'Create a new sheet for the stratified sample
        Set wsSample = ThisWorkbook.Sheets.Add
        wsSample.Name = "Stratified Sample"
        'Copy headers to the new sheet
        ws.Range("A1:E1").Copy Destination:=wsSample.Range("A1")
        'Select 2 transactions from each category
        For Each category In dict.Keys
        Dim count As Long
        count = 0
        For i = 2 To lastRow
        If ws.Cells(i, 5).Value = category And count < 2 Then
        ws.Rows(i).Copy Destination:=wsSample.Rows(wsSample.Cells(wsSample.Rows.Count, "A").End(xlUp).Row + 1)
        count = count + 1
        End If
        Next i
        Next category
        MsgBox "Stratified sample generated successfully!", vbInformation
        End Sub
        

Output

Example 3: Risk-Based Sampling

Risk-based sampling focuses on high-risk transactions, such as large amounts, transactions with new vendors, or unusual payment methods. Risk-Based Sampling is a method to select transactions that are more risk-prone. They may represent data above a certain amount, or irregular and unusual payment methods. Let us understand if AI can help us to generate a VBA Code for Risk-Based sampling.

We have to choose the sample in such a manner that represent Amounts greater than INR 1,00,000, or Transactions with vendors with fewer than 5 transactions, or Transactions paid via cash or cheque. Let us use AI to generate a VBA Code that will do this for us.

Prompt to AI:
"I have Vendor Transactions in a sheet with the name 'Transactions'. The sheet has the following columns.
1. Transaction ID
2. Date
3. Vendor
4. Amount
5. Category (Not shown in the above table)
6. Payment Method
Please write a VBA Code that will select:
1. Transactions with Amounts greater than INR 1,00,000.
2. Transactions with vendors with fewer than 5 transactions.
3. Transactions paid via cash or cheque.
These rows should be copied to a new sheet named as 'Risk-Based Sample'".

VBA Code Generated by AI:

Sub RiskBasedSampling()
        Dim ws As Worksheet
        Dim wsSample As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim vendorCount As Long
        Dim highRiskRows As Collection
        ' Initialize collection to store high-risk rows
        Set highRiskRows = New Collection
        ' Set the worksheet containing the data
        Set ws = ThisWorkbook.Sheets("Transactions")
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        'Loop through the dataset to identify high-risk transactions
        For i = 2 To lastRow
        vendorCount = Application.WorksheetFunction.CountIf(ws.Range("C2:C" & lastRow), ws.Cells(i, 3).Value)
        If ws.Cells(i, 4).Value > 100000 Or vendorCount < 2 Or ws.Cells(i, 5).Value = "Cash" Or ws.Cells(i, 5).Value = "Cheque" Then
        highRiskRows.Add i
        End If
        Next i
        'Create a new sheet for the risk-based sample
        Set wsSample = ThisWorkbook.Sheets.Add
        wsSample.Name = "Risk-Based Sample"
        'Copy headers to the new sheet
        ws.Range("A1:E1").Copy Destination:=wsSample.Range("A1")
        'Copy high-risk transactions to the new sheet
        For i = 1 To highRiskRows.Count
        ws.Rows(highRiskRows(i)).Copy Destination:=wsSample.Rows(i + 1)
        Next i
        MsgBox "Risk-based sample generated successfully!", vbInformation
        End Sub
        

Output

"While Auditing, one of the key considerations which auditing firms need to take into account is the risks identified in the previous periods."

2. Predictive Analytics

While Auditing, one of the key considerations which auditing firms need to take into account is the risks identified in the previous periods. The risks identified in the previous years can be the most prioritized transactions which we have to select for sampling without caring for the value of the transactions. AI can help us to predict the potential material misstatements based on past data. This method ensures that risk-prone areas are always selected for sample. Let us understand if AI can help us to generate a VBA Code for such Predictive Analysis.

Prompt to AI:
"I have Vendor Transactions in a sheet with the name 'Transactions'. The sheet has the following columns.
1. Transaction ID
2. Date
3. Vendor
4. Amount
5. Category (Not shown in the above table)
6. Payment Method (Not shown in the above table)
7. Risk Score
Please write a VBA Code that will select Transactions with transactions with a risk score greater than 0.7. These rows should be copied to a new sheet named as 'High-Risk Transactions'"

VBA Code Generated by AI:

Sub PredictiveAnalytics()
        Dim ws As Worksheet
        Dim wsSample As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim highRiskRows As Collection
        'Initialize collection to store high-risk rows
        Set highRiskRows = New Collection
        'Set the worksheet containing the data
        Set ws = ThisWorkbook.Sheets("Transactions")
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        'Loop through the dataset to identify high-risk transactions
        For i = 2 To lastRow
        If ws.Cells(i, 5).Value > 0.7 Then
        highRiskRows.Add i
        End If
        Next i
        'Create a new sheet for the high-risk transactions
        Set wsSample = ThisWorkbook.Sheets.Add
        wsSample.Name = "High-Risk Transactions"
        'Copy headers to the new sheet
        ws.Range("A1:E1").Copy Destination:=wsSample.Range("A1")
        'Copy high-risk transactions to the new sheet
        For i = 1 To highRiskRows.Count
        ws.Rows(highRiskRows(i)).Copy Destination:=wsSample.Rows(i + 1)
        Next i
        MsgBox "High-risk transactions identified successfully!", vbInformation
        End Sub
        

3. Continuous Auditing

Continuous Auditing is a method to continuously monitor transactions throughout the financial year against some pre-specified criteria. This method ensures anomalies are automatically red flagged. With Continuous Auditing, the action can be taken immediately as and when the issue arises rather than waiting for the periodic audit.

Let us see if AI can help us to generate a VBA Code in Continuous Auditing.

Prompt to AI:
"I have Vendor Transactions in a sheet with the name 'Transactions'. The sheet has the following columns.
1. Transaction ID
2. Date
3. Vendor
4. Amount
5. Category (Not shown in the above table)
6. Payment Method
Please write a VBA Code that will select Transactions with Amounts greater than INR 1,00,000.
These rows should be copied to a new sheet named as "Flagged Transactions"."

VBA Code Generated by AI:

Sub ContinuousAuditing()
        Dim ws As Worksheet
        Dim wsSample As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim flaggedRows As Collection
        'Initialize collection to store flagged rows
        Set flaggedRows = New Collection
        'Set the worksheet containing the data
        Set ws = ThisWorkbook.Sheets("Transactions")
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        'Loop through the dataset to identify flagged transactions
        For i = 2 To lastRow
        If ws.Cells(i, 4).Value > 100000 Then
        flaggedRows.Add i
        End If
        Next i
        ' Create a new sheet for the flagged transactions
        Set wsSample = ThisWorkbook.Sheets.Add
        wsSample.Name = "Flagged Transactions"
        'Copy headers to the new sheet
        ws.Range("A1:E1").Copy Destination:=wsSample.Range("A1")
        'Copy flagged transactions to the new sheet
        For i = 1 To flaggedRows.Count
        ws.Rows(flaggedRows(i)).Copy Destination:=wsSample.Rows(i + 1)
        Next i
        MsgBox "Flagged transactions identified successfully!", vbInformation
        End Sub
        

Output

Challenges and Considerations

While AI offers us many benefits, there are a lot of challenges before we use it. Auditors must consider these factors:

1. Data & Prompt Quality: The output we receive from any AI tool depends on the quality of data and the efficacy & completeness of the prompt we write. If we lack in any of these, the quality of formulas and codes may not be effective and may not give the results desired by us. Auditors must ensure that they should use complete & reliable data, write proper prompts, and verify the results before actual implementation.
2. Comparative AI Tools: There is an influx of AI tools on an everyday basis. The auditors should be well verse with the capacities and capabilities of different AI tools. Some tools are good in creating content, while others are goods at writing codes. Auditors should keep themselves updated with different tools and we cannot avoid AI tools anymore, and they are here to exist whether we adapt to it or not.
3. Ethical and Privacy Concerns: We should be very particular in what we share with AI tools. Even while generating VBA Codes, we should share the Data Structure for the Excel or sample Data. We should never expose the credentials of our client as confidentiality is the core ethic for which Chartered Accountants are respected for.

Conclusion

AI is going to change the way we conduct audit. It is going to impact every aspect of audit. Audit Sampling is just one aspect. It will impact Audit Evidence, Audit Procedures, Audit Planning, and Reporting. Let us keep ourselves updated and ready to learn the different AI tools and see how we can use it for better auditing and consulting for our clients. Let's use AI to raise the quality of auditing and take it to the optimum level.