Exploring Data Analysis Techniques for Auditors Using R
In today’s dynamic business environment, the ability to analyze data efficiently is crucial for making informed decisions and providing valuable insights. As auditors, leveraging tools like R can significantly enhance our capabilities in detecting anomalies and understanding patterns within financial data. In this blog post, we’ll explore how to utilize R along with ggplot2 and dplyr packages to identify outliers and visualize our findings, all without needing to import external data.
Setting Up Your R Environment
Before we begin, ensure you have R and RStudio installed on your computer. If not, you can download them from CRAN and RStudio.
R Markdown
Generating a Sample Dataset
To illustrate the process, let’s create a mock dataset representing financial transactions. We’ll generate this data using R directly:
# Generating a sample dataset
set.seed(123) # Setting seed for reproducibility
data <- data.frame(
Transaction_ID = 1:100,
Amount = c(rnorm(90, mean = 1000, sd = 200), rnorm(10, mean = 5000, sd = 500))
)
# Adding an outlier
data$Amount[100] <- 15000
# View the dataset
head(data)
## Transaction_ID Amount
## 1 1 887.9049
## 2 2 953.9645
## 3 3 1311.7417
## 4 4 1014.1017
## 5 5 1025.8575
## 6 6 1343.0130
In this dataset: – Transaction_ID represents unique transaction identifiers. – Amount simulates transaction amounts, with an intentional outlier added for illustration.
Identifying Outliers Using dplyr
Now, let’s use the dplyr package to identify outliers based on a Z-score threshold:
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
# Calculate Z-scores
data <- data %>%
mutate(Zscore = (Amount - mean(Amount)) / sd(Amount))#equation to measure how far the transaction is from its average in terms of standard deviations
# Filter outliers (Z-score greater than 3)
outliers <- data %>%
filter(abs(Zscore) > 3)#filter any amounts greater than 3 standard deviations
# View the outliers
outliers
## Transaction_ID Amount Zscore
## 1 100 15000 7.281672
Visualizing Outliers with ggplot2
The code below uses the quantile function to divide the transactions into groups: 1. The transactions within the 25th percentile and the 75th percentile denoted by the sky blue bars in the histogram portion of the chart.
2. Outliers defined as any amount greater that the 75th percentile line or any amount less than the 25th percentile.
To visualize the outliers detected, we’ll use ggplot2 to create a histogram with a density plot overlay:
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.2.3
# Create a histogram with density plot
ggplot(data, aes(x = Amount)) +
geom_histogram(binwidth = 500, fill = "skyblue", color = "black") + #transaction amounts show in sky blue bars on the graph's histogram
geom_density(alpha = 0.6, fill = "#FFA500") +
geom_vline(aes(xintercept = mean(Amount), color = "Mean"), linetype = "dashed", size = 1) +
geom_vline(aes(xintercept = median(Amount), color = "Median"), linetype = "dashed", size = 1) +
geom_vline(aes(xintercept = quantile(Amount, 0.25), color = "Q1"), linetype = "dashed", size = 1) +
geom_vline(aes(xintercept = quantile(Amount, 0.75), color = "Q3"), linetype = "dashed", size = 1) +
geom_vline(aes(xintercept = mean(Amount) + 3*sd(Amount), color = "Z-Score Threshold"), linetype = "dashed", size = 1) +
geom_vline(aes(xintercept = mean(Amount) - 3*sd(Amount), color = "Z-Score Threshold"), linetype = "dashed", size = 1) +
geom_point(data = outliers, aes(x = Amount, y = 0), color = "red", size = 3) +
scale_color_manual(values = c("red", "blue", "darkgreen", "darkgreen", "darkgreen")) +
labs(title = "Transaction Outliers (Z-score > 3) ",
x = "Amount",
y = "Density") +
theme(plot.title = element_text(hjust = 0.5))+
theme_minimal()
Conclusion
In this blog post, we’ve demonstrated how R, along with ggplot2 and dplyr packages, can be powerful tools for internal auditors in analyzing and visualizing financial data. By generating a sample dataset directly in R, identifying outliers using Z-scores with dplyr, and visualizing the findings with ggplot2, auditors can gain deeper insights into transaction patterns and anomalies.
Understanding these techniques not only enhances audit efficiency but also strengthens the ability to identify potential risks and irregularities within financial datasets. As you continue to explore R for data analysis, remember to adapt these methods to suit your specific audit needs and datasets.
Happy auditing with R!
Note: For further learning, refer to the official documentation and tutorials provided by RStudio, ggplot2, and dplyr communities.
Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.
Responses