Internal Audit / Toolkit
Excel Lab — Audit Universe and Coverage Tracker
Measure distinct process-location coverage without counting repeat engagements twice. Practise COUNTIFS, period boundaries and clear limits on what coverage means.
Return to your library resultsHow to use this toolkit.
Workflow
- Set the reporting dates on Coverage.
- Review the six process-location units and their stable IDs.
- Edit amber fields on Engagements, including completion dates and status.
- Resolve row warnings and compare covered units with the universe.
Assumptions & limitations
Original synthetic universe of six process-location units. Completed engagements count only when their completion date falls in the selected period. Repeated engagements count once per unit. The workbook holds 100 engagement rows, 8–107; adding universe units requires extending formulas and reference ranges. It does not weight risk, evaluate engagement scope, allocate resources or determine assurance adequacy.
Example scenario
Two completed engagements cover U01 and one covers U03 in 2026. A prior-year engagement and planned or in-progress work are excluded. Two of six unique units are covered, or 33.3%.
| Unit | Completed audits in 2026 | Covered units |
|---|---|---|
| U01 Purchasing Jakarta | 2 | 1 |
| U02 Inventory Jakarta | 0 | 0 |
| U03 Sales Jakarta | 1 | 1 |
| U04 Purchasing Bandung | 0 | 0 |
| U05 Payroll Jakarta | 0 | 0 |
| U06 Treasury Jakarta | 0 | 0 |
Sample data from the downloadable file; values are illustrative.
## The question behind the spreadsheet
If the same area is audited twice, have you covered two parts of the business? Only if your coverage definition supports that conclusion. This lab starts with one clear unit: a particular process at a particular location.
You will count completed work within a reporting period, reduce repeat engagements to one covered unit and identify areas with no completed work. Review the audit universe and engagement scope concepts in the Internal Audit map first.
## Define the denominator
Coverage contains six fictional process-location units, each with a stable ID and owner. Purchasing in Jakarta and Purchasing in Bandung are different units. The denominator is six units. It is not six visits, six reports or a risk-weighted score.
The reporting period runs from 1 January to 31 December 2026. These are example controls, not a statement about the actual Inside Assurance audit plan.
## Follow the engagement records
Engagements stores an engagement ID, unit ID, status, completion date, hours and a scope note. U01 has two completed engagements during 2026. U03 has one. U05 has a completed engagement dated 31 December 2025, which is outside the reporting period. Planned and in-progress work do not count as completed coverage.
## Walk through COUNTIFS
Coverage E19 uses:
=COUNTIFS('Engagements'!$B$8:$B$107,A19,'Engagements'!$C$8:$C$107,"Completed",'Engagements'!$D$8:$D$107,">="&$E$7,'Engagements'!$D$8:$D$107,"<"&$E$8+1)
The paired ranges match unit ID, Completed status, period start and the day after period end. The exclusive upper boundary includes the whole final day even when a date contains a time. Keep all ranges the same size. Change the reporting dates in their single control area.
F19 uses =IF(E19>0,1,0) to convert one or several completed engagements into one covered unit. Summing this column gives two covered units. Two divided by six gives 33.3% coverage and leaves four unreviewed units.
## Check the records before interpreting the rate
Duplicate engagement IDs, missing completion dates for Completed work, unknown unit IDs and invalid statuses receive row warnings. Duplicate universe IDs are also flagged. Resolve these issues before using the totals. A visit count alone would overstate the example: three completed engagements cover two unique units.
The tracker counts activity. It does not establish whether audit scope addressed the right risks, evidence was sufficient, or another assurance provider already covered the area. Document those judgments separately.
## Practise at the boundary
1. Change E04 on Engagements row 11 from Planned to Completed and enter 31 December 2026. Covered units rise to three and the rate becomes 50%.
2. Change that date to 1 January 2027. It is outside the period, so coverage returns to two of six. Restore the original Planned row and blank date.
3. Change one unit ID to UNKNOWN. Find the row warning, identify the correct process-location and repair the key rather than inventing a new coverage unit.
## Self-check
Why does auditing U01 again leave the coverage rate unchanged? U01 is already one covered unit under this definition. Explain what a risk-based assessment would need beyond this percentage.
## Continue through the pillar
Return to Internal Audit Apply. Then use Connect to discuss the relationship between audit scope, risk ownership and other assurance work.
Put the perspective to work.
Use these questions to connect the resource to your own business context.
Which objective and exposure need assurance?
Where could the process or control fail?
What evidence supports the conclusion and its limits?
What response addresses the cause and can be followed up?
References & context.
Use the framework and jurisdiction applicable to your work. Original examples and templates do not imply endorsement by the referenced organisations.
A question, correction or missing source?
The resource link will be included in your message form.