Stratify

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.

 

 

Leave a comment