Excel Guide: Setting Audit Materiality & Sampling for Auditing Standards

Setting Audit Materiality, Sample Size, and Audit Sampling in Excel: A Comprehensive Guide for Compliance with Auditing Standards

Auditing involves assessing financial statements to ensure accuracy and compliance. A key component of this process is determining materiality and performance materiality, which are critical for effective audit planning and execution. This article provides a systematic approach to setting audit materiality and performance materiality using Excel, in compliance with Standards on Auditing (SAs).

Understanding Materiality

Materiality is the threshold above which missing or incorrect information in financial statements is considered to have an impact on the economic decisions of users. Performance Materiality is set at a lower level than overall materiality to reduce the risk of material misstatements.

Step-by-Step Guide to Set Materiality in Excel

Step 1: Collect Financial Data

Gather the financial statements and key financial metrics of the entity. Important figures include:

• Total Revenue
• Total Assets
• Profit Before Tax (PBT)
• Equity

Step 2: Set Benchmarks and Percentages

Determine appropriate benchmarks and percentages to calculate materiality. Common benchmarks include:

• 0.5% to 1% of Total Revenue
• 1% to 2% of Total Assets
• 5% to 10% of Profit Before Tax
• 1% to 2% of Equity

Step 3: Create an Excel Template

Set up an Excel template with the following column headers and rows for each benchmark (Revenue, Assets, PBT, Equity):

Benchmark	Financial Figure	Percentage Range	Materiality Amounts
Total Revenue	100,000,000	0.5% to 1%	=B20.005 to =B20.01
Total Assets	200,000,000	1% to 2%	=B30.01 to =B30.02
Profit Before Tax	10,000,000	5% to 10%	=B40.05 to =B40.10
Equity	150,000,000	1% to 2%	=B50.01 to =B50.02

Step 4: Input Data and Formulas

Input the financial figures into the Excel template and use formulas to calculate the materiality amounts. For instance:

Benchmark	Financial Figure	Percentage Range	Materiality Amounts
Total Revenue	100,000,000	0.5% to 1%	=B20.005 to =B20.01
Total Assets	200,000,000	1% to 2%	=B30.01 to =B30.02
Profit Before Tax	10,000,000	5% to 10%	=B40.05 to =B40.10
Equity	150,000,000	1% to 2%	=B50.01 to =B50.02

Step 5: Calculate Overall Materiality

Decide on the most appropriate materiality figure from the range calculated for each benchmark. This decision should be based on the auditor’s professional judgment and the context of the financial statements.

Step 6: Calculate Performance Materiality

Performance materiality is typically set between 50% to 75% of overall materiality to account for aggregation risk. Add a row for performance materiality and apply the percentage.

Benchmark	Financial Figure	Percentage Range	Materiality Amounts
Overall Materiality			=AVERAGE(D2 )
Performance Materiality		50% to 75%	=D60.5 to =D60.75

Step 7: Document Assumptions and Judgments

Ensure to document the rationale behind the selected benchmarks, percentages, and final materiality decisions. This documentation is crucial for compliance with SAs and for audit trail purposes.

Audit Sampling Using Excel

Audit sampling involves selecting a subset of items from a population to draw conclusions about the entire population. Here’s a step-by-step guide to perform audit sampling in Excel:

Step 1: Define the Population

Identify the population from which the sample will be drawn. This could be a list of transactions, account balances, or any other data set relevant to the audit.

Step 2: Determine Sample Size

Use professional judgment or statistical formulas to determine the appropriate sample size. Factors influencing sample size include:

• Audit risk
• Materiality
• Population size
• Expected error rate

Step 3: Collect Population Data in Excel

Input the population data into an Excel spreadsheet. Assume the data is in a column.

Step 4: Generate Random Sample

Excel provides several functions that can be used to generate a random sample. Use the RAND() function to assign random numbers to each item in the population and then select the required number of items.

Step 5: Assign Random Numbers

Add a new column next to your data and use the RAND() function to assign a random number to each item.

Transaction Amount	Random Number
100	=RAND()
200	=RAND()
300	=RAND()
…	…

Step 6: Sort by Random Number

Sort the data based on the random numbers in ascending order.

Step 7: Select the Sample

After sorting, select the top rows corresponding to your sample size.

Step 8: Document the Sample

Copy the selected sample to a new sheet or a new area in the same sheet for documentation and further analysis.

Advanced Sampling Techniques

For more advanced sampling techniques, such as stratified sampling or systematic sampling, you can use additional Excel functions and tools:

Systematic Sampling

1. Determine Interval: Calculate the sampling interval by dividing the population size by the sample size.

2. Select Starting Point: Use =RANDBETETWEEN(1, Interval) to select a random starting point within the first interval.

3. Select Items: Select every nth item from the starting point, where n is the interval.

Stratified Sampling

1. Divide Population: Split the population into strata based on characteristics (e.g., transaction size).

2. Sample from Each Stratum: Apply random sampling within each stratum.

Documenting the Sampling Process

Ensure you document the following to comply with auditing standards:

• Population definition
• Sample size determination
• Sampling method
• Steps taken to select the sample
• Results of the sample testing
• Conclusions drawn from the sample

Determining Sample Size Using Excel

Determining the sample size involves considering several factors such as audit risk, materiality, population size, and the expected error rate. Here’s a step-by-step guide:

Step 1: Understand the Factors Influencing Sample Size

1. Audit Risk: The risk that the auditor may unknowingly fail to appropriately modify their opinion on financial statements that are materially misstated.

2. Materiality: The magnitude of an omission or misstatement that makes it probable that the judgment of a reasonable person relying on the information would have been changed or influenced.

3. Population Size: The total number of items in the population from which the sample will be drawn.

4. Expected Error Rate: The rate at which errors are expected to occur in the population.

Step 2: Use a Sample Size Table or Formula

One common approach is to use sample size tables provided by auditing standards or statistical formulas.

Step 3: Input Your Parameters in Excel

Example Parameters:

• Confidence Level (CL): Typically set at 95% (corresponds to a Z-value of 1.96)
• Tolerable Error (TE): Maximum error rate the auditor is willing to accept (e.g., 5%)
• Expected Error (EE): Expected error rate based on past experience or judgment (e.g., 1%)
• Population Size (N): Total number of items in the population (e.g., 1000)

Step 4: Calculate the Sample Size

The sample size can be calculated using the formula for a proportionate sample size for a given confidence level and margin of error.

Step 5: Create an Excel Template

Here’s a step-by-step example using a simplified formula based on a proportionate sample size calculation:

1. Input Parameters in Excel: excel Copy code

Parameter	Value
Confidence Level (Z)	1.96
Tolerable Error (TE)	0.05
Expected Error (EE)	0.01
Population Size (N)	1000

2. Calculate the Sample Size (n):

The formula to calculate the sample size (n) is: n=(Z2×p×(1−p)E2)×NN+(Z2×p×(1−p)E2−1)n = \left( \frac{Z^2 \times p \times (1 – p)}{E^2} \right) \times \frac{N}{N + \left( \frac{Z^2 \times p \times (1 – p)}{E^2} – 1 \right)}n=(E2Z2×p×(1−p)​)×N+(E2Z2×p×(1−p)​−1)N​

Where:

• ZZZ is the Z-value (1.96 for 95% confidence level)
• ppp is the expected error rate (EE)
• EEE is the tolerable error (TE)
• NNN is the population size

Step-by-Step Calculation in Excel:

1. Set Up the Parameters: excel Copy code

Parameter	Value
Confidence Level (Z)	1.96
Tolerable Error (TE)	0.05
Expected Error (EE)	0.01
Population Size (N)	1000

2. Calculate Components:

• • Z2Z^2Z2: =B2^2
  • ppp: =B4
  • 1−p1 – p1−p: =1 – B4
  • p×(1−p)p \times (1 – p)p×(1−p): =B4 * (1 – B4)
  • Z2×p×(1−p)Z^2 \times p \times (1 – p)Z2×p×(1−p): =(B2^2) * (B4 * (1 – B4))
  • E2E^2E2: =B3^2
  • Z2×p×(1−p)/E2Z^2 \times p \times (1 – p) / E^2Z2×p×(1−p)/E2: =(B2^2 * B4 * (1 – B4)) / B3^2
  • N×Z2×p×(1−p)/E2N \times Z^2 \times p \times (1 – p) / E^2N×Z2×p×(1−p)/E2: =B5 * ((B2^2 * B4 * (1 – B4)) / B3^2)
  • Z2×p×(1−p)−1Z^2 \times p \times (1 – p) – 1Z2×p×(1−p)−1: =((B2^2 * B4 * (1 – B4)) / B3^2) – 1
  • N+(Z2×p×(1−p)−1)N + (Z^2 \times p \times (1 – p) – 1)N+(Z2×p×(1−p)−1): =B5 + (((B2^2 * B4 * (1 – B4)) / B3^2) – 1)

3. Calculate the Sample Size (n):

In Excel, you can set up the formula as follows: excel Copy code

Calculation	Excel Formula	Result
Z^2	=B2^2	3.8416
p	=B4	0.01
1 – p	=1-B4	0.99
p * (1 – p)	=B4*(1-B4)	0.0099
Z^2 * p * (1 – p)	=B2^2B4(1-B4)	0.0380
E^2	=B3^2	0.0025
Z^2 * p * (1 – p) / E^2	=(B2^2B4(1-B4))/B3^2	15.208
N * Z^2 * p * (1 – p) / E^2	=B5*((B2^2B4(1-B4))/B3^2)	15207.9
Z^2 * p * (1 – p) – 1	=((B2^2B4(1-B4))/B3^2) – 1	14.208
N + (Z^2 * p * (1 – p) – 1)	=B5 + (((B2^2B4(1-B4))/B3^2) – 1)	1014.208
Sample Size (n)	=((B2^2B4(1-B4))/B3^2) * (B5 / (B5 + (((B2^2B4(1-B4))/B3^2)-1)))	15.01

Documenting and Using the Sample Size

1. Document Your Assumptions:

• Confidence Level
• Tolerable Error
• Expected Error
• Population Size

2. Use the Sample Size for Audit Testing:

• Select the top nnn items from your sorted list based on the random numbers.

By following these steps, you can effectively determine the sample size in Excel in compliance with Standards on Auditing. This method helps ensure that your sample is statistically valid and that your audit findings are reliable.

Conclusion

Setting audit materiality and performance materiality, as well as determining sample sizes using Excel, is a methodical process that ensures compliance with Standards on Auditing. By following the steps outlined in this article, auditors can enhance the accuracy and reliability of their audits, ultimately supporting better financial reporting and decision-making. Documenting all steps and assumptions is crucial for audit trail purposes and ensures transparency and compliance with auditing standards.