Before you test a single transaction, you should know what the population looks like. How many payments are small? Where does the money actually sit? Which items are so unusual they deserve a look on their own? Stratification and outlier analysis answer those questions in minutes, and they are among the most useful first steps in any data-driven audit, whether you are reviewing a creditors ledger at a parastatal, loan disbursements at a bank or grant payments at an NGO.
This article explains what each technique finds, walks through a worked example, and gives step-by-step Excel instructions you can use on your own data today.
What stratification and outlier analysis find
Stratification splits a population into bands (strata), usually by value, and summarises the count and total of each band. It tells you where the risk and the money are concentrated. A typical result: a large share of transactions are small, but a handful of large items make up most of the value.
Outlier analysis identifies individual items that sit far away from the rest of the data. Outliers are not automatically errors or fraud, but they are exactly the items an auditor should be able to explain.
Together, these tests help you:
- Scope the audit: focus detailed testing on high-value strata and use sampling for the rest.
- Improve sampling efficiency: ISA 530 recognises stratification as a way to reduce variability within each stratum, so smaller samples can give the same level of assurance.
- Spot threshold-splitting: clusters of payments just below an approval limit often point to orders being broken up to avoid higher-level authorisation.
- Find anomalies: unusually large, negative or round-sum transactions that need an explanation.
A worked example: payments just under the limit
Suppose you are auditing a public entity’s Q2 supplier payments. Payments of USD 10,000 or more require board approval. Here is an extract of 12 payment vouchers:
| Voucher | Supplier | Amount (USD) | Stratum |
|---|---|---|---|
| PV001 | Supplier A | 450.00 | 1: Under 1,000 |
| PV002 | Supplier B | 1,200.00 | 2: 1,000–4,999 |
| PV003 | Supplier C | 85.00 | 1: Under 1,000 |
| PV004 | Supplier D | 3,400.00 | 2: 1,000–4,999 |
| PV005 | Supplier E | 620.00 | 1: Under 1,000 |
| PV006 | Supplier F | 9,950.00 | 3: 5,000–9,999 |
| PV007 | Supplier G | 210.00 | 1: Under 1,000 |
| PV008 | Supplier H | 48,500.00 | 4: 10,000 and above |
| PV009 | Supplier I | 1,750.00 | 2: 1,000–4,999 |
| PV010 | Supplier J | 9,980.00 | 3: 5,000–9,999 |
| PV011 | Supplier K | 330.00 | 1: Under 1,000 |
| PV012 | Supplier L | 2,600.00 | 2: 1,000–4,999 |
The stratification summary looks like this:
| Stratum | Count | % of count | Total (USD) | % of value |
|---|---|---|---|---|
| 1: Under 1,000 | 5 | 41.7% | 1,695.00 | 2.1% |
| 2: 1,000–4,999 | 4 | 33.3% | 8,950.00 | 11.3% |
| 3: 5,000–9,999 | 2 | 16.7% | 19,930.00 | 25.2% |
| 4: 10,000 and above | 1 | 8.3% | 48,500.00 | 61.3% |
| Total | 12 | 100% | 79,075.00 | 100% |
Three things stand out. One payment (8% of items) carries 61% of the value, so it should be tested in full. Stratum 1 holds five items worth only 2% of the total, so it is a candidate for light sampling. And both items in stratum 3 sit within USD 50 of the board-approval limit. Neither is necessarily wrong, but both deserve a check of supporting documents, approvals and whether related invoices were split.
Step-by-step: stratification in Excel
- Prepare the data. Put your extract in a table with Amount in column C (rows 2 to 13 in the example). Remove totals and blank rows, and confirm the record count and grand total agree to the ledger.
- Add a Stratum column. In D2, enter: =IF(C2<1000,”1: Under 1,000″,IF(C2<5000,”2: 1,000–4,999″,IF(C2<10000,”3: 5,000–9,999″,”4: 10,000 and above”))) Fill down. Numbering the labels keeps them in the right order when sorted.
- Count and total each band with COUNTIFS and SUMIFS. For stratum 2, for example: =COUNTIFS($C$2:$C$13,”>=1000″,$C$2:$C$13,”<5000″) =SUMIFS($C$2:$C$13,$C$2:$C$13,”>=1000″,$C$2:$C$13,”<5000″)
- Or use a pivot table. Insert > PivotTable, put Stratum in Rows, and add Amount to Values twice. Set the first to Count and the second to Sum. Add Amount a third time and choose Show Values As > % of Column Grand Total.
- Test for threshold-splitting. Count items in the 10% just below the approval limit: =COUNTIFS($C$2:$C$13,”>=9000″,$C$2:$C$13,”<10000″) Then filter those items and sort by supplier and date to see whether the same supplier was paid several times in a short period.
Step-by-step: finding outliers in Excel
Payment data is usually heavily skewed, with many small items and a few large ones. A mean and standard deviation (z-score) approach can be distorted by the very outliers you are looking for, so the interquartile range (IQR) method is often more reliable.
- Calculate the quartiles. Q1: =QUARTILE.INC($C$2:$C$13,1) gives 420.00. Q3: =QUARTILE.INC($C$2:$C$13,3) gives 5,037.50.
- Calculate the IQR and fences. IQR = Q3 − Q1 = 4,617.50. Upper fence = Q3 + 1.5 × IQR = 11,963.75. Lower fence = Q1 − 1.5 × IQR (negative here, so no low outliers).
- Flag each item. With the upper fence in G2 and lower fence in G3, in E2 enter: =IF(OR(C2>$G$2,C2<$G$3),”Outlier”,””) In the example, only PV008 (48,500.00) is flagged.
- Highlight with conditional formatting. Select A2:E13, choose Home > Conditional Formatting > New Rule > Use a formula, and enter =$C2>$G$2. Pick a fill colour so outliers stand out when you scroll.
- Look beyond size. Also filter for negative amounts, zero values and round sums such as =MOD(C2,1000)=0. Each can reveal reversals, test entries or estimates posted as actuals.
Practical tips and common pitfalls
- Do not mix currencies. In multi-currency records, stratify ZiG and USD transactions separately, or convert to one currency at the rate on the transaction date and document the rate source. Mixing them makes the bands meaningless.
- Set bands with a purpose. Align strata with approval limits, materiality and performance materiality rather than round numbers chosen at random.
- Treat negatives separately. Credit notes and reversals belong in their own stratum; otherwise they quietly reduce totals.
- Stratify by more than value. Try supplier, user ID, branch or day of the week. Payments processed on weekends or public holidays are a classic outlier group.
- Follow up and document. An outlier only becomes a finding once you have obtained an explanation and evidence. Record the test, the parameters and the results so a reviewer can re-perform them. Tools such as Auditors Track GRC help teams link findings to working papers and evidence attachments so nothing gets lost between the analysis and the report.
Conclusion
Stratification shows you where the value and risk sit; outlier analysis shows you which individual items need explaining. Run both at the start of an engagement and you will scope your work more sharply, size your samples more efficiently and catch red flags like threshold-splitting that a random sample may miss. Try the formulas above on your next payments or journal extract.
Excel works well for a few thousand rows, but it slows down and becomes error-prone on full-year ledgers with hundreds of thousands of transactions. ProDataTool is a desktop data analysis application built for auditors, risk analysts, accountants and business analysts. It handles datasets from thousands to millions of rows, with project-based organisation, descriptive statistics, cross-tabulation, pivot analysis, summarisation and custom filtering, so you can stratify and profile an entire population without fighting your spreadsheet. Book a free ProDataTool demo and see how quickly you can run tonight’s tests on your own data.