AUD · Performing Further Procedures and Obtaining Evidence · 7 practice questions
Audit ETL Mapping and Interface Testing for Sales Cutoff
Before relying on a combined sales report, obtain evidence that its sources, interfaces, and mappings produce complete and accurate data for cutoff testing. Below: follow Ridge’s web sales from their source through the final cutoff report.
Try one first
Hint
Before relying on analytics of an extracted dataset, consider whether the dataset is complete and accurate by tracing and testing the data source(s) and any interfaces or reconciliations that populate the extraction.
Answer C. Because web sales originate in a separate source that is integrated by an automated interface, and there was a documented outage with manual back-entry while ITGCs over change management are weak, the auditor cannot assume the main master file is complete and accurate even if totals tie to the GL. The auditor should obtain and test the web_sales extract, the automated interface, and any reconciliation to confirm the combined dataset's completeness and accuracy. Only after concluding the source and interface are reliable should the auditor rely on that dataset for analytics; if problems exist, additional substantive procedures are required.
Why not A: Agreement with the GL does not prove completeness when a separate source and an interface exist and manual entries were made; both the master file and the GL could reflect the same incomplete or adjusted records, so the interface and source must be tested.
Why not B: Expanding sampling or vouching from a potentially incomplete population cannot detect transactions that were never recorded in the main master file; completeness risk is best addressed by examining the original web_sales source and the interface/reconciliation.
Why not D: Alternative substantive procedures applied to items selected from an unverified population still risk missing unrecorded web transactions; testing the web source and interface is necessary to assess and address completeness.
Step by step
- Define the cutoff population
Ridge’s cutoff report combines web sales with its main sales file through an extract, transform, and load (ETL) process. The auditor needs transactions selected by the date relevant to cutoff, not merely their upload date.
- Map the data flow
Identify the web sales table, its interface to the main sales file, the ETL mappings, and the final report settings. Obtain the source extracts and documentation needed to follow transactions through that flow.
- Test the specific interface
Ridge uploads weekend web sales on the next business day; even with effective relevant IT general controls, obtain evidence addressing this specific interface and the report settings. Test whether the interface transfers those transactions completely and whether the report uses the date needed for cutoff testing.
- Reperform key mappings
For selected web transactions, reperform the mapping of source fields into the main sales file and cutoff report. Compare mapped dates and amounts with source records, and inspect relevant mapping changes and processing logs.
- Check report settings
Inspect the report’s selected date fields and exclusion filters to determine whether they capture the intended cutoff population. Trace selected source transactions into the report, rather than checking only records already included.
- Reconcile beyond the ledger
Reconcile report totals to the general ledger and transaction counts and amounts to the web source records; investigate differences. A ledger tie-out alone is insufficient because the report and ledger could omit the same web sales.
- Resolve gaps before reliance
If web transactions are missing, obtain the missing data and validate their inclusion or perform alternative procedures covering them. Use the report for cutoff testing only after obtaining sufficient evidence of its reliability for that purpose.
Key points
- Remember: a report can match the ledger and still omit the same transactions.
- Effective IT general controls do not establish that a specific interface or report captures the intended population.
- Testing records already included supports accuracy, but does not establish that all relevant records were included.
How the exam traps you
- Accepting the cutoff report because its sales total matches the general ledger. Reconcile to source records and test the interface, mappings, and report settings.
- Treating effective IT general controls as proof that weekend uploads preserve cutoff. Test transfer completeness and whether the report uses the correct transaction date.
- Expanding a sample drawn only from the incomplete report. Obtain the missing web data or perform alternative procedures covering those transactions.
Question 2
Hint
Before expanding substantive testing, consider whether the dataset used for analytics is sufficiently reliable and what procedures (reconciliations, test of conversion controls, external source comparison) provide evidence about its accuracy and completeness.
Answer B. The auditor must evaluate the reliability of underlying electronic data before relying on analytics results, especially after a system migration and when source data conflict. Reconciling the HRIS export to the third-party payroll processor's records and to bank disbursements and testing migration/conversion controls provides evidence about completeness and accuracy. Only after establishing that the dataset is reliable should the auditor expand targeted substantive testing of the exceptions identified.
Why not A: Tempting because it appeals to materiality, but incorrect: materiality alone doesn't justify accepting analytics results when the underlying data may be unreliable. The auditor still must obtain sufficient appropriate evidence about the data and investigate the flagged anomalies.
Why not C: Tempting because management can remediate errors quickly, but incorrect: the auditor cannot simply accept management-supplied corrections without independent verification. The auditor must test the corrections and underlying source records or controls to obtain evidence.
Why not D: Tempting as a thorough approach, but incorrect: performing a full-population audit on potentially unreliable HRIS data is inefficient and may provide misleading assurance. Verifying data reliability and source records should precede broad substantive procedures.
Question 3
Hint
Focus on what the auditor must establish when using information produced by the entity, especially when the report blends converted system data, external inputs, and manual changes.
Answer C. When an auditor uses information produced by the entity, the key issue is whether that information is sufficiently reliable and relevant for the intended audit purpose. Here, the mixed sources, system conversion, report logic, and manual overlays create specific completeness-and-accuracy risks that are not resolved merely by tying totals to the subledger or using a reputable external feed. The auditor must address those reliability issues directly or through testing relevant controls over the information's preparation.
Why not A: This is tempting because auditors may sometimes obtain evidence about information reliability by testing controls rather than only by direct substantive testing. However, effective controls over the broader allowance process do not automatically establish that this specific dashboard is complete and accurate. The auditor still must address the reliability of the data and report logic, whether through control testing focused on that information or other procedures.
Why not B: This is tempting because external information is often more reliable than internally generated information. But the dashboard is a combined report, and the outside ratings do not cure possible errors in migrated receivable data, aging logic, or manual dispute tagging. The primary issue is the reliability of the dashboard as a whole for the auditor's intended use.
Why not D: This distractor is attractive because routine use by management can make a report seem more credible and relevant. But ordinary-course use is not the main criterion under audit evidence standards. A report can be routinely used and still be incomplete, inaccurately mapped after a system conversion, or distorted by uncontrolled manual edits.
Question 4
Hint
Focus on whether you can trust the transformed dataset and what procedures give direct evidence about the ETL/mapping and completeness before relying on analytics or third‑party reports.
Answer A. Because management cannot provide ETL mapping documentation and only transformed extracts were provided, there is a risk the pipeline altered, omitted, or misclassified transactions, especially given a mapping change that coincides with the spike. Re-performing extraction and mapping for a sample and reconciling to the vendor's raw feed and the general ledger gives direct evidence about completeness and accuracy; expanding substantive tests addresses any discrepancies found. Third-party reports or analytics on transformed data do not substitute for direct testing of the transformation when mappings changed and reconciliations are absent.
Why not B: Tempting because a SOC 1 Type 2 report provides assurance about a vendor's control environment, but it addresses the vendor's controls, not the client's ETL mappings, transformation logic, or whether the data after the client's processing are accurate; it does not replace direct testing of the mapping and reconciliations.
Why not C: Tempting because integrated feeds can appear authoritative, but undocumented ETL transformations and a mid-period mapping change plus an unexplained spike mean the extracts may not accurately reflect source transactions; accepting them without testing risks material misstatement.
Why not D: Tempting because broader analytics can flag anomalies, but applying analytics to potentially unreliable transformed data can be misleading; before relying on analytics or management explanations, the auditor should obtain evidence that the underlying transformations are accurate.
Question 5
Hint
Classify each data source by likely reliability (system‑generated, external third‑party, manually compiled) and choose procedures that directly address accuracy, completeness, and transformation for that classification.
Answer B. Auditors must evaluate the reliability of each data source before relying on analytics outputs. System‑generated ERP exports require procedures that address extraction/mapping accuracy (and ITGCs if controls will be relied upon) and reconciliation to source documents. Third‑party carrier logs obtained directly are generally more reliable but should be matched to client records to corroborate completeness and occurrence. Manually compiled spreadsheets need testing of both calculations and input accuracy by tracing to original documents.
Why not A: This is tempting because system‑generated data and third‑party data can appear reliable. However, extraction/mapping errors or weak IT controls can corrupt system outputs, and carrier logs alone do not prove the client recorded or mapped shipments correctly. Verifying spreadsheet formulas alone ignores potential input errors; tracing to source documents is also required.
Why not C: Candidates may be drawn to the efficiency of recomputing analytics and relying on representations, but recomputation does not validate the underlying data's accuracy or completeness. Management representations and informal carrier summaries are not substitutes for independent verification such as tracing to originals or matching to client records.
Why not D: Testing application controls is useful but does not address risks in the extraction/mapping or data transformation used for analytics. Reentering all spreadsheet data is inefficient and still may not detect mapping/extraction errors in the ERP export; focused reconciliation and tracing are more appropriate.
Question 6
Hint
Focus on whether the auditor's evidence covered the entire population (completeness). If it did not, consider whether the auditor can obtain missing data or must perform alternative procedures or consider a scope limitation.
Answer A. The extract omitted a portion of the sales population (legacy-system transactions), creating a limiting condition because the auditor's evidence does not cover the entire population. The auditor must obtain evidence addressing completeness, ideally by obtaining the missing data or, if unobtainable, performing appropriate alternative procedures. If adequate evidence cannot be obtained and management refuses to provide it, the auditor may need to consider a scope limitation and modify the report.
Why not B: Tempting because auditors often expand testing when unexpected results appear. However, the issue is missing transactions (an incomplete population); expanding tests only within the provided extract does not address whether legacy transactions exist or are properly recorded, so it does not resolve the completeness concern.
Why not C: Tempting to view 5% as small, but materiality cannot be assumed from a percent alone and management representation is not sufficient evidence. The auditor must obtain supporting evidence or perform alternative procedures before concluding the omission is immaterial.
Why not D: Tempting because the matched results appear reassuring, but this ignores that the analysis excluded a portion of transactions. Data-quality affecting the extraction creates a sufficiency-of-evidence problem; the auditor must obtain additional evidence or perform alternative procedures rather than merely document the issue.
Question 7
Hint
Consider whether you can rely on analytics results produced from a recently migrated client system before using those results to change the timing or extent of substantive procedures.
Answer C. Because the analytics are based on data extracted from a recently migrated AP system, the auditor must first determine whether those data are complete and accurate before relying on the analytics to change the timing or extent of substantive procedures. Practical procedures include reconciling a sample of extracted transactions to source documents and testing migration controls. If those procedures support data reliability, the auditor should then design targeted substantive procedures and extend testing through year-end as needed in response to the anomaly.
Why not A: Tempting because a spike in exceptions usually calls for more testing, but incorrect because it assumes the analytics input data are reliable despite a recent system migration; expanding testing based on unverified data may misdirect effort or fail to detect migration-related data issues.
Why not B: This appears efficient, but it is inadequate: a narrow re-performance still depends on the integrity of the extracted dataset and may miss systemic migration errors or other misstatements outside the flagged items, so data-reliability procedures are needed first.
Why not D: While postponement conserves interim resources, it is incorrect here because a material change in exception rates is a trigger for prompt auditor response; deferring ignores the need to evaluate the anomaly and the underlying data now.
Common questions
Does agreeing an audit data extract to the general ledger prove completeness?
No. Ridge’s report and ledger could omit the same web sales. Compare the report with source records and investigate missing transactions.
Are effective IT general controls enough to rely on an ETL report?
No. They do not, by themselves, establish that Ridge’s specific interface and report settings capture the intended cutoff population. Obtain evidence addressing those features.
What should an auditor do if a web sales feed is missing?
Obtain the missing feed and validate its inclusion, or perform alternative procedures covering those transactions. Increasing testing within the existing report does not address omitted sales.
Watch it solved
A full CPA AUD task-based simulation on Use of Data and Information, worked step by step.
Data Extract Reliability & Duplicate Payments | CPA AUD TBS on YouTube
Practice AUD like the real exam
The free ChatCPA simulator: real exam layout, timed testlets, starting with a question on this topic. No account needed to start.
Open the free simulator →More on Use of Data and Information
Questions from the ChatCPA bank of 17,658 CPA exam questions, each with a written reason for every wrong answer. ChatCPA is built by Nicholas Miller, CPA (Oregon #14907). How these pages are made. Spot an error? Tell us.