Stratified Sampling for Audit Testing in Google Sheets

Sample selection is one of the most repeated tasks on any audit engagement — journal entries, revenue transactions, accounts payable, SOX control operations, and substantive procedures across the board. When the population contains meaningful subgroups with different risk profiles, a simple random sample can under-represent the parts of the population you actually care about. Stratified random sampling solves that problem: you divide the population into strata based on risk-relevant characteristics, then pull a random sample from each stratum. The result is defensible, reproducible, and aligned with a risk-based approach to testing.

When Stratified Sampling Fits an Audit Engagement

Stratified sampling is most useful when the population is not homogeneous and specific subgroups warrant separate consideration. On a typical engagement, that shows up in several places:

• Journal entry testing. Manual entries carry different risk than automated ones. Period-end entries carry different risk than throughout-period entries. Entries posted by senior management or with round-dollar amounts often warrant separate attention. Stratifying by any of these dimensions ensures you don’t miss high-risk populations by chance.
• Revenue and accounts receivable testing. A revenue population dominated by a handful of large customers benefits from stratifying by customer size, product line, or geography — often with full testing of items above a scoping threshold and a random sample from the remainder.
• SOX controls testing. When a control operates across quarters, multiple business units, or several control operators, stratifying by those dimensions supports a defensible conclusion about operating effectiveness for the population as a whole.
• Accounts payable and vendor testing. New vendors, related parties, and large disbursements each carry distinct risk. Stratifying by vendor category or payment size lets you allocate coverage where it matters.
• Monetary unit sampling (MUS). MUS is itself a specialized form of stratified sampling — the “strata” are effectively defined by dollar magnitude, with larger balances proportionally more likely to be selected.

Common Stratification Variables for Audit Populations

The right stratification variable depends on the risk you’re addressing. Some patterns show up often enough to be a starting checklist:

• Dollar magnitude — split the population into “above scoping threshold” (usually 100% tested) and one or more bands below, with random selection from each band
• Period — quarters, months, or pre-/post-implementation dates for a control or system change
• Preparer or control operator — the person who initiated, approved, or performed the control
• Transaction type — manual vs. automated, standard vs. non-standard, recurring vs. one-off
• Business unit, location, or entity — often required for multi-location engagements
• Risk classification — a fraud-risk flag, a new-customer flag, an account with prior audit findings

Proportionate vs. Disproportionate: The Audit Case for Oversampling

Textbook treatments of stratified sampling often default to proportionate allocation — sample sizes track the size of each stratum in the population. In audit work, disproportionate allocation is frequently the more appropriate choice. If 5% of your journal entries are manual and period-end but they carry the bulk of the misstatement risk, a proportionate sample would put 95% of your effort on the low-risk automated entries. Deliberately oversampling the high-risk stratum, and undersampling the low-risk one, is consistent with a risk-based approach.

Random Sampler supports both patterns from the same sidebar — assign each stratum its own percentage or count, and the add-on pulls a random selection from each stratum accordingly.

Running Stratified Sampling in Google Sheets

1. Confirm population completeness. Before you sample, foot the population to a reliable source — a GL trial balance, a system report tied to a control total, or a source system extract with row counts. Sample selection from an incomplete population is not defensible regardless of technique.
2. Add a stratification column. If your population doesn’t already have the variable you want to stratify by, add a helper column — a formula bucketing dollar amounts into bands, a lookup that classifies vendors by category, or a formula flagging period-end entries.
3. Determine sample size per stratum. This should reflect your firm methodology, materiality, tolerable misstatement, expected error, and confidence level. Firm-specific tables and audit software often drive these numbers directly.
4. Select samples randomly within each stratum. Random Sampler takes your stratification column and per-stratum counts (or percentages) and returns a highlighted selection you can copy, filter, or export.
5. Document the selection. Record the population, source, stratification rationale, sample sizes, selection method, and the specific items selected — see the next section.

Documentation and Workpaper Trail

Whatever technique you use, the workpaper needs to show enough for a reviewer to reperform the selection. That typically means:

• Source of the population and how it was tied to a reliable total
• Definition of each stratum and the rationale for the split
• Sample size per stratum and how it was determined
• Selection method (random, systematic, MUS) and any seed or timestamp for reproducibility
• Tickmarks tying each selected item back to the population and to the test performed

If your workpapers live in Google Sheets or Google Docs, Tickmark Pro adds standard audit tickmarks and a legend directly to the sheet or document — useful for annotating each selected item and cross-referencing to the test conclusions.

Considerations Under Audit Standards

Both AICPA AU-C 530 and PCAOB AS 2315 address audit sampling and explicitly recognize stratification as a technique for improving audit efficiency by concentrating testing on higher-risk portions of the population. Neither standard prescribes a specific approach — your firm methodology, engagement risk assessment, and applicable standards should drive sample size, stratification design, and evaluation of results. The techniques described here are tools; they are not a substitute for judgment or firm methodology, and should be applied in coordination with your engagement team.

Common Pitfalls

• Population completeness never verified. The most common workpaper review comment on sampling. Fix it upstream, not downstream.
• Overlapping strata. If a transaction can fall into more than one stratum, the selection is no longer clean random. Make strata mutually exclusive and collectively exhaustive.
• Items above the scoping threshold treated as “sampled” instead of “fully tested.” Above-threshold items are usually 100% tested; they aren’t part of the sample and should be documented separately.
• Judgmental selection labeled as random. If you cherry-picked based on knowledge of the items, that’s not random sampling — and it changes how results can be projected to the population.
• No reproducibility. If the reviewer can’t rerun the selection, the workpaper is weaker. Record the seed, timestamp, or otherwise show how the specific items were chosen.

Conclusion

Stratified random sampling is one of the cleanest ways to align an audit sample with the risk profile of the population — better coverage of the parts that matter, less wasted effort on the parts that don’t. In Google Sheets, the mechanics can be handled directly in the sidebar without formulas or add-in installers.

Related Articles