Template Library
Control Test Sampling Log Template
An Excel workbook for testing internal controls with attribute samples: a sample size table, a control inventory, and a test log that calculates the upper limit of the deviation rate.
The Sample Sizes tab takes a confidence level and a tolerable deviation rate and shows the sample size needed for zero to five expected deviations, using the binomial distribution. The Control Inventory tab lists each control with its risk, owner, frequency, population, and planned sample size.
The Test Log tab records the sample size tested and the deviations found for each control. It calculates the sample deviation rate, the one-sided upper limit of the deviation rate at your confidence level, and whether that limit is within your tolerable rate. Example controls and results are included, including one where a single deviation in 59 items does not support reliance, so you can see the formulas working before you replace them.
Download Control Test Sampling Log Template Back to Templates
What Is Included in the Workbook
| Sheet | Purpose | What Teams Capture |
|---|---|---|
| How-To | Guidance and disclaimer | Steps and notes |
| Sample Sizes | Sample size table | Confidence, tolerable rate, sample size for 0 to 5 expected deviations, and a helper table |
| Control Inventory | Controls to test (Excel Table) | Control, process, risk, owner, frequency, population, planned sample |
| Test Log | Results and evaluation (Excel Table) | Sample size tested, deviations, sample rate, upper limit, conclusion, and follow-up |
Key Features Inside the Template
Sample Sizes from Your Own Settings
Change the confidence level and tolerable rate and the table of sample sizes recalculates.
An Upper Limit, Not Just a Rate
The log calculates the one-sided upper limit of the deviation rate, which is what tells you whether the result supports reliance.
Conclusions That Flag Themselves
Each test shows Supports reliance or Does not support reliance, with colors and a summary count.
Space for Cause and Follow-Up
A column for the cause of each deviation and the follow-up keeps the log useful beyond the number.
Best Use Cases
- Planning and documenting attribute tests of internal controls
- Explaining why a small sample with one deviation may not support reliance
- Training staff in statistical sampling for control testing
- Keeping a consistent record of what was tested and what was found
How to Use the Template Effectively
- Agree the confidence level and tolerable rate with your methodology before testing, and enter them on the Sample Sizes tab.
- List the controls to test, the population sizes, and the planned sample sizes.
- Select samples randomly or systematically from the whole population and test each item.
- Enter the sample size tested and the deviations found, and read the upper limit and conclusion.
- Investigate every deviation for cause and record the follow-up.
Who Should Use This Template
- Internal control, compliance, and risk teams
- Finance and accounting managers testing their own controls
- Students and trainees learning attribute sampling
Common Mistakes to Avoid
- Judging a result by the sample rate alone and ignoring the upper limit
- Choosing the confidence level or tolerable rate after seeing the results
- Sampling only convenient items instead of the whole population
- Moving on from a deviation without asking why it happened
Important Notes
Control Test Sampling Log Template Frequently Asked Questions
How does the workbook calculate the upper limit?
It uses the one-sided binomial (Clopper-Pearson) upper limit, BETAINV(confidence, deviations + 1, sample size - deviations). For 59 items with no deviations at 95% confidence it is 4.95%, and with one deviation it is 7.79%.
How does the sample size table work?
The helper table on the Sample Sizes tab lists the chance of at most k deviations in a sample of n if the true rate equals the tolerable rate. The sample size is the smallest n where that chance is at most one minus the confidence level. For zero deviations it matches the formula LN(1 - confidence) / LN(1 - tolerable rate).
Can I use this instead of my audit methodology?
No. It illustrates the statistics of attribute sampling. Your organization's methodology and the standards that apply to you define the sample sizes, selection methods, and evaluation rules you must follow.