Stratify has to be one of my favourite commands. It quickly gives more details regarding the distribution (Low, Medium, and High) of the selected numeric fields (e.g. number of low, medium, high dollar contracts). This can help with sampling (which I don’t really like to do). The records in the “High” categories may number only a few, but they often represent a significant portion of the amount. A stratified sampling approach may be to review all transactions over “X” (which accounts for “Y” percent of the total amount, and sample the remaining records.
Amount | Count |
% of Count | % of Amount |
Amount |
<0.00 | 189 | 0.78% | -0.21% | (987,892.96) |
0.00 – 9,999.99 | 23,031 | 94.72% | 4.06% | 18,684,099.55 |
10,000.00 – 24,999.99 | 522 | 2.15% | 1.81% | 8,304,746.14 |
25,000.00 – 49,999.99 | 204 | 0.84% | 1.58% | 7,279,022.93 |
50,000.00 – 99,999.99 | 132 | 0.54% | 1.94% | 8,920,007.86 |
100,000.00 – 249,999.99 | 103 | 0.42% | 3.36% | 15,461,975.54 |
250,000.00 – 499,999.99 | 48 | 0.2% | 3.61% | 16,611,416.45 |
500,000.00 – 999,999.99 | 30 | 0.12% | 4.78% | 21,996,545.08 |
1,000,000.00 – 4,999,999.99 | 38 | 0.16% | 16.03% | 73,739,078.50 |
5,000,000.00 – 10,000,000.00 | 7 | 0.03% | 10.77% | 49,534,086.86 |
>10,000,000.00 | 12 | 0.05% | 52.28% | 240,517,949.62 |
Totals | 24,316 | 100% | 100% | 460,061,035.57 |
In the results above, less than 0.1% of transactions (amount >=$5M) account for 63% of the total dollars. This means that I can address 63% of the value by reviewing 19 transactions. This will significantly reduce the risk.
Changing the stratas can show more detail for the amount under $10K as follows:
Amount | Count | % of Count | % of Amount |
Amount |
<(1.00) | 189 | 0.78% | -0.21% | (987,892.96) |
(1.00) – (0.01) | 0 | 0% | 0% | 0.00 |
0.00 – 99.99 | 7,023 | 28.88% | 0.07% | 320,961.18 |
100.00 – 249.99 | 4,500 | 18.51% | 0.16% | 740,839.88 |
250.00 – 499.99 | 3,335 | 13.72% | 0.26% | 1,207,319.89 |
500.00 – 999.99 | 3,384 | 13.92% | 0.52% | 2,403,587.81 |
1,000.00 – 4,999.99 | 4,177 | 17.18% | 2.13% | 9,794,679.25 |
5,000.00 – 9,999.99 | 612 | 2.52% | 0.92% | 4,216,711.54 |
10,000.00 – 24,999.99 | 522 | 2.15% | 1.81% | 8,304,746.14 |
25,000.00 – 5,000,000.00 | 555 | 2.28% | 31.3% | 144,008,046.36 |
>5,000,000.00 | 19 | 0.08% | 63.05% | 290,052,036.48 |
Totals | 24,316 | 100% | 100% | 460,061,035.57 |
These are invoices and it can easily be seen that a large percentage (47%) are less than $250. These are charges that could easily be addressed with P-Cards which have a much lower processing cost than invoices.
So two simply executions of Stratify have illustrated that there are a large number of small invoices – which could be eliminated by the use of P-Cards – and a small number of very large invoices.
In the results below, we see a significant number of contracts in the range $2,250.00 – 2,499.00 which is just below the $25K requirement to have competitive bids.
Contract Amount |
Count | % of Count | % of Amount | Amount |
<0.00 | 949 | 3.19% | -2.98% | (2,839,280.50) |
0.00 – 999.99 | 11,287 | 37.97% | 5.41% | 5,151,935.90 |
1,000.00 – 2,249.99 | 6,298 | 21.18% | 10.12% | 9,636,147.10 |
2,250.00 – 2,499.99 | 723 | 2.43% | 1.79% | 1,708,962.80 |
2,500.00 – 4,999.99 | 4,855 | 16.33% | 18.03% | 17,176,971.60 |
5,000.00 – 9,999.99 | 3,619 | 12.17% | 26.97% | 25,688,869.10 |
10,000.00 – 50,000.00 | 1,921 | 6.46% | 33.93% | 32,318,511.90 |
>50,000.00 | 78 | 0.26% | 6.72% | 6,404,340.30 |
Totals | 29,730 | 100% | 100% | 95,246,458.20 |
The results also indicate a number (949) of contracts with a negative amount – which raises questions.
Stratify can also be performed on multiple slices of the populations with a single command by selecting a Break field (e.g. Stratify on Contract Amount by contracting officer). This will help determine which contracting officers are possibly avoiding the requirement to hold competitive bids.
In summary, Stratify – like many other summary-type commands – can provide insight into the business process and help auditors to identify risks, determine scope/objectives, and sampling approaches.