In today’s blog post, I will show you how to use Excel to implement the analytics of high incoming invoice amounts from Part II of this series. It is easy to understand, so everybody can use this.
Part 3 of the series: “Very high incoming invoice amount”
1. Where Topmanagers should keep an eye on
2. Getting serious: What are high incoming invoices?
3. Analytics of high incoming invoices
4. Two graphics for evaluating high incoming invoices
Short Recap
At first glance, high incoming invoices are not unusual for an auditor. But this is not what this analytics is about. It is rather about identifying unusually high incoming invoices, which we would generally call statistical outliers. In Part II of this series, our PhD Professor already explained how a statistical outlier is defined in this context:
An incoming invoice from a vendor is unusually high if the invoice amount is above the mean value of the vendor’s invoice amounts plus six times the standard deviation of the vendor’s invoice amounts.
Instructions for the analytics of high invoice amounts
In the following Excel instructions, I would like to show you in a clear way that the math formulas from last post are not as unintelligible as they may seem.
If we look at the definition mentioned above, we can easily divide the evaluation into subtasks. At this point, I feel sent back to my schooldays, where various algebraic story problems had to be resolved, where one was constantly asking himself who produced such nonsense, e.g.:
“Klaus and Martina are travelling from 110 km away towards each other at the same time. Klaus is covering a distance of 120 km per hour and Martina one of 100 km. Where will they meet?”
The mathematically predisposed person among you will probably think briefly about the problem or take notes and conclude that Klaus will drive 10 km farther than Martina. Everyone else will presumably answer: At the arranged meeting place, of course.
All right, enough nostalgia for now. Let us turn back to the mathematical examination of incoming invoices with high invoice amounts and divide the definition into subtasks:
- Calculation of the mean value for all incoming invoices within a vendor.
- Calculation of the standard deviation of all the invoice amounts within a vendor.
- Addition of the mean value and the sixfold standard deviation.
Before you start searching in the depths of your math books, you will find the formulas for the mean value and the standard deviation here:
The mean value is commonly known as the “average”. All values are summarized and divided through the number of the values, or:
Where:
n is the number of values
xi is the i-th value
The standard deviation, however, describes how much a value variates around the mean value and is calculated as follows:
Where:
n is the number of values
xi is the i-th value
x is the mean value
At this point, of course, you do not have to calculate every value individually in Excel, but you can use the functions =AVERAGE() and =STDEV() instead. Therefore, the formula in Excel does not look as bad as shown before:
=AVERAGE(Selection area)+6*STDEVP(Selection area)
The 6 in this equation describes the sixfold standard deviation, but you can define yourself how high an outlier is to you. With the help of the table in Part II of this series, you can adjust this value to suit your needs or perception, and thus define your own marginal value.
Of course, you should always examine only one vendor at a time for this analytics and view a sufficient number of incoming invoices. The reason is that only one statistical outlier in e.g. 10 invoices can influence the mean value so strongly that the analytics is invalid.
What are your experiences in purchase auditing and the analytics of high invoice amounts? Was it already a component or was it rather neglected?
