PracticeAUDFree practice exam

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.

The ruleBefore using transformed or combined data as audit evidence, evaluate completeness, accuracy, and detail for the intended purpose. Address relevant sources, interfaces, mappings, timing, and report settings; a general ledger tie-out alone is insufficient.

Try one first

An auditor plans to perform data-analytic substantive procedures on the client's sales master file extract to test revenue completeness and cutoff for Year 2. The client implemented an e-commerce web module during Year 2; web sales are recorded in a separate 'web_sales' table that is integrated nightly into the main sales master file by an automated interface. The auditor assessed IT general controls over change management as weak and noted a documented one-week web module outage in Year 2; client staff manually entered the missing web transactions into the main master file after the module was restored. The auditor extracted only the main sales master file (monthly totals from that extract agree with the GL) and plans to use that extract as the population for analytics without obtaining or testing the web_sales table, the interface, or the reconciliation between them. What is the most appropriate next step?
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.

Step by step

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

6 more, each from a different angle

0 of 6 answered · 0 correct

Question 2

During the Year 1 audit of Meridian Co., the engagement team used data analytics on a payroll dataset exported from Meridian's HRIS and identified 45 payments that appear to be duplicate disbursements totaling $180,000. Overall materiality for the financial statements is $500,000. The client performed a major HRIS migration during Year 1 and engaged a third-party payroll processor to convert historical payroll records; the engagement team also received a payroll summary directly from that third-party processor that differs from the client's HRIS export for several pay periods. The engagement partner is concerned about the reliability of the HRIS data. What is the most appropriate next action?
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.

Question 3

In auditing the allowance for credit losses of a manufacturer, the auditor plans to use a year-end "loss-rate dashboard" as substantive audit evidence. The dashboard combines (1) invoice-level A/R data drawn from an ERP system that was converted in October 20X6, (2) customer credit ratings imported monthly from a reputable outside vendor, and (3) manual dispute flags added by the credit manager. The dashboard's gross receivable total agrees to the year-end A/R subledger, and the subledger agrees to the general ledger. However, the auditor has not yet evaluated whether all legacy-system invoices migrated into the dashboard, how the dashboard logic assigns aging buckets after credit memos, or whether manual dispute flags can be overwritten. What is the primary issue the auditor should address before using the dashboard as audit evidence?
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.

Question 4

During the Year 2 audit of ABC Co., the auditor noted that revenue summaries in the general ledger are populated from a data warehouse that receives a third-party sales feed. An ETL process applies product-code mappings and transformations before data reach the warehouse. The auditor observed an unexplained spike in July that coincides with a documented code-mapping change implemented mid-year. Management could not provide documentation of the ETL transformation logic or reconciliations between the vendor's raw feed and the general ledger; the auditor only received the transformed warehouse extracts. What is the auditor's best action?
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.

Question 5

During the Year 20X2 audit of Novus Manufacturing, the engagement team used data analytics to identify anomalies in revenue and to select transactions for substantive testing. The analytics used three data sources: (1) an electronic sales transaction file extracted from Novus's ERP (system-generated export containing invoice numbers, dates, amounts), (2) third‑party carrier shipping logs obtained directly from the carrier, and (3) a manually compiled client spreadsheet that summarized customer returns and credit memos prepared from paper tickets. The team plans to rely on the analytics outputs to select items for testing. Which set of additional procedures is most appropriate to perform before relying on the analytics outputs?
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.

Question 6

During the Year 2 audit of BrightCo, the auditor used data-analytic software to perform a 100% match of sales invoices to shipping records using a client-provided electronic extract (the primary sales system). The analytic found no mismatches for the included transactions. During planning the auditor learned that a separate legacy sales system was not included in the extract; the client estimates that legacy-system transactions account for about 5% of total revenue. Management provided a written representation that legacy transactions were processed similarly. Which is the best classification of this issue and the auditor's next step under generally accepted auditing requirements?
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.

Question 7

During Year 1 interim procedures an auditor ran data analytics on the client's accounts payable (AP) population for quarters 1-3 and found a noticeably higher rate of duplicate invoice numbers paid to the same vendor in Q3 (1.2% of Q3 payments versus 0.1% in the prior-year comparable period). The client converted its AP system at the end of Q2, and the auditor has not yet tested or obtained evidence about the completeness and accuracy of the migration or the reliability of the new system's extracted AP data. Which of the following is the auditor's most appropriate immediate response?
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.

Drill all 120 Use of Data and Information questionsMixed across every rule in the topic, so you have to spot which one applies. That is how the exam does it.

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

All Use of Data and Information practice →

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.