2  Understanding Data in Accounting

Learning Objectives

After completing this chapter, you will be able to:

1. Distinguish between quantitative and qualitative data and between structured, semi-structured, and unstructured data, and identify examples of each in accounting contexts.
2. Describe the primary sources of accounting data, including general ledgers, sub-ledgers, trial balances, enterprise resource planning systems, and external data feeds.
3. Evaluate accounting data against four quality dimensions: accuracy, completeness, consistency, and timeliness.
4. Identify common data quality problems, including missing values, duplicates, inconsistent formatting and values, and outliers, and apply basic tests to detect them in the Charles River data.
5. Explain the concept of tidy data and why it matters for accounting analytics.
6. Explain how source-to-ledger traceability tests whether recorded transactions reached the general ledger completely and accurately, and what such a test cannot show.

Chapter slides View slides Download PowerPoint

Opening Scenario

You are a newly hired internal auditor at Charles River, a mid-size home furnishings company that sells furniture, lighting, textiles, and decorative accessories through wholesale and direct-to-business channels. Your supervisor has asked you to assess the quality of the company’s accounting data before the team begins the year-end audit. You start by opening the Charles River Excel workbook and examining the Order-to-Cash tables. In the Customer table, you notice two customers listed under exactly the same name, and you cannot tell at a glance whether they are one company or two. In the Shipment table, hundreds of shipments have no tracking number, and some shipments from years ago are still marked “In Transit.” You then turn to the Accounting Core tables. In the GLEntry table, a small number of entries carry neither a debit nor a credit amount, and the extract runs several weeks into a fiscal year that has barely begun. Before the audit team can run analytical procedures on revenue, test for duplicate payments, or evaluate journal entries for signs of management override, someone needs to determine whether the underlying data is reliable. Your supervisor has asked you to prepare that assessment. This chapter will give you the vocabulary, the framework, and the practical skills to evaluate whether accounting data is fit for analysis.

2.1 Why Data Quality Comes First

Chapter 1 introduced the six-stage accounting analytics workflow, beginning with defining the question and ending with communicating findings. This chapter focuses on a concern that cuts across every stage of that workflow: the quality of the underlying data. No analytical technique, no matter how sophisticated, can produce reliable results if the data it operates on contains errors, gaps, or inconsistencies. A PivotTable built on revenue data with missing transaction dates will produce period totals that understate actual performance. A SQL query designed to detect duplicate payments will miss duplicates if vendor names are recorded inconsistently across tables. A Power BI dashboard showing budget-versus-actual comparisons will mislead managers if the budget data and the actual data use different account classifications.

The principle is straightforward. The quality of every analytical output is constrained by the quality of the data that feeds it. Researchers in information systems have formalized this idea by studying data quality as a measurable property of datasets, with dimensions that can be assessed, monitored, and improved (Wang & Strong, 1996). For accountants, data quality is not an abstract concern. It is a professional responsibility. The auditing standards that the American Institute of Certified Public Accountants (AICPA) issues for audits of financial statements require the auditor to evaluate the relevance and reliability of the information used as audit evidence, including the data behind analytical procedures (AICPA, 2020). Management accountants who present cost analyses to executives are implicitly vouching for the integrity of the numbers. Financial reporting analysts who prepare disclosures from database extracts must ensure that the underlying data supports the figures reported. Understanding what data quality means, how to assess it, and what problems to look for is therefore a prerequisite for everything else in this book.

2.2 Data Types in Accounting

Before you can evaluate the quality of a dataset, you need to understand the types of data it contains. Accounting data takes many forms, and the appropriate way to store, analyze, and interpret a piece of data depends on what type it is. Two classification schemes are particularly useful for accounting analytics: the distinction between quantitative and qualitative data, and the distinction between structured and unstructured data.

2.2.1 Quantitative and Qualitative Data

Quantitative data consists of values that represent measurements or counts and that can be meaningfully subjected to arithmetic operations. In accounting, the most familiar quantitative data includes transaction amounts (debits, credits, invoice totals, payment amounts), quantities (units produced, units sold, units in inventory), and calculated values (ratios, percentages, variances). Quantitative data can be further divided into continuous data, which can take any value within a range, and discrete data, which takes only specific values (such as the number of journal entries posted in a given month). An amount such as $1,247.83 is recorded to the cent, but analysis treats amounts as continuous.

Qualitative data consists of values that represent categories, labels, or descriptions rather than measurements. In accounting, qualitative data includes account names, customer names, vendor classifications, transaction descriptions, product categories, and status codes such as “Closed” or “Open.” Qualitative data can be nominal, meaning the categories have no natural order (such as the names of cost centers), or ordinal, meaning the categories have a meaningful sequence (such as a credit rating scale from AAA to D).

The distinction matters because it determines which analytical techniques are appropriate. You can calculate an average transaction amount, but you cannot calculate an average account name. An identifier stored as a number is a label too, so an average CustomerID describes no customer. You can count the number of transactions in each product category, but the category labels themselves are not quantities. Many data quality problems arise when qualitative data is treated as quantitative or when quantitative data is stored in a format that prevents arithmetic operations. A common example is a transaction amount stored as text rather than as a number because the original data source included a currency symbol or a comma in the value. The number looks correct to a human reader, but a sum may skip it, reject it, or convert it in an unexpected way, depending on the tool and the formula. It has to be converted to a number, with the right currency and decimal conventions, and the result checked. Changing the cell’s number format only changes how it looks. Table 2.1 gives examples of each type from the Charles River data.

Table 2.1: Examples of Quantitative and Qualitative Data in Accounting

Data Type	Subtype	Accounting Example	Charles River Source
Quantitative	Continuous	Sales invoice line amount	SalesInvoiceLine.LineTotal (O2C group)
Quantitative	Continuous	Standard cost of a product	Item.StandardCost (Master Data group)
Quantitative	Continuous	Freight cost on a shipment	Shipment.FreightCost (O2C group)
Quantitative	Discrete	Number of line items on a sales invoice	Count of SalesInvoiceLine rows per invoice (O2C group)
Quantitative	Discrete	Number of GLEntry rows posted in a fiscal period	GLEntry (Accounting Core group)
Qualitative	Nominal	Account name (“Office Supplies Expense”)	Account.AccountName (Accounting Core group)
Qualitative	Nominal	Customer name (“Walters PLC”)	Customer.CustomerName (O2C group)
Qualitative	Nominal	Item group classification (“Furniture”)	Item.ItemGroup (Master Data group)
Qualitative	Nominal	Source document type (“SalesInvoice”)	GLEntry.SourceDocumentType (Accounting Core group)
Qualitative	Nominal	Sales order status (Open, Backordered, Partially Invoiced, Invoiced, Closed)	SalesOrder.Status (O2C group)
Qualitative	Ordinal	Supplier risk rating (Low, Medium, High)	Supplier.SupplierRiskRating (P2P group)

2.2.2 Structured and Unstructured Data

A second important distinction is between structured and unstructured data. Structured data is organized into a predefined format, typically rows and columns in a table, where each column has a defined data type and each row represents a single observation or transaction. The Charles River dataset contains structured data throughout. The SalesOrder table, for example, has columns for SalesOrderID, CustomerID, OrderDate, RequestedDeliveryDate, and Status, and each row represents one customer order. Structured data is the primary input for the analytical techniques covered in this book, including PivotTables in Excel, SQL queries, and Power BI data models.

Unstructured data lacks a predefined tabular format. In accounting, unstructured data includes the text of contracts, the narrative sections of annual reports, email correspondence between auditors and clients, scanned images of invoices, and the notes that accountants attach to journal entries. Unstructured data often contains valuable information, but extracting that information requires different tools and techniques than those used for structured data. Natural language processing, a branch of artificial intelligence, is increasingly used to analyze unstructured accounting text such as contracts and regulatory filings (Bochkay et al., 2023; Yan & Moffitt, 2019). The further reading at the end of this chapter is a starting point for these techniques.

Between these two extremes lies semi-structured data, which has some organizational elements but does not fit neatly into rows and columns. An example relevant to accounting is an XBRL (eXtensible Business Reporting Language) filing, which contains financial data tagged with standardized labels but organized in a hierarchical rather than tabular structure. Another example is a bank statement in PDF format that contains tabular transaction data embedded within a document layout. The PDF is only a container. Whether its pages hold text that can be read as a table or a scanned image decides how much structure there is to use. Figure 2.1 arranges the three categories along a spectrum from more to less structure.

Figure 2.1: The Spectrum of Data Structure.

TipIn Practice

The records at the center of accounting work, such as general ledgers, trial balances, accounts receivable aging schedules, and payroll registers, are structured datasets stored in rows and columns within ERP systems or accounting software. Much of the evidence behind them is not structured, such as contracts, correspondence, scanned documents, and the narrative of disclosures, which tools for contract analysis, disclosure review, and automated document processing are designed to read. Developing strong skills with structured data, as this book teaches, provides the foundation for working with less structured data as your career progresses.

This book focuses almost entirely on structured data because the ledgers and transaction records it analyzes are structured and because the tools covered here (Excel, SQL, and Power BI) are designed for structured datasets. The Charles River database is entirely structured, with clearly defined tables, columns, and data types in every table group. Understanding this foundation is essential before you can evaluate whether a dataset is ready for analysis.

2.3 Sources of Accounting Data

Accounting data originates from many places within and outside an organization. Understanding where data comes from helps you anticipate its strengths and limitations, which in turn helps you assess its quality and suitability for a given analytical task. This section describes the most common sources of accounting data that you will encounter in practice.

2.3.1 The General Ledger and Sub-Ledgers

The general ledger is the central repository of an organization’s financial transactions. Every debit and credit entry that affects the financial statements passes through the general ledger. In a database environment, the general ledger is typically stored as a table where each row represents one side of a journal entry (a single debit or credit), and columns record the posting date, the account number, the amount, a description, and a reference to the source document. The Charles River dataset stores its general ledger in the GLEntry table, which includes columns for GLEntryID, PostingDate, AccountID, Debit, Credit, SourceDocumentType, SourceDocumentID, SourceLineID, VoucherType, VoucherNumber, FiscalYear, FiscalPeriod, and CostCenterID.

Sub-ledgers provide detailed records for specific account categories. The accounts receivable sub-ledger, for example, contains individual customer balances and the invoices and payments that make up each balance. The accounts payable sub-ledger contains individual vendor balances. The inventory sub-ledger contains records of individual stock items, their quantities, and their costs. Sub-ledgers feed summary totals to the general ledger, and the balances in the two should agree. When they do not, the accountant reconciles their dates, scope and posting timing and investigates the difference. A documented timing or scope difference is not an error, while an unexplained one may point to an error or a control problem. In the Charles River dataset, tables in the O2C group, such as SalesInvoice, CashReceipt, and CashReceiptApplication, function as the accounts receivable sub-ledger, and tables in the P2P group, such as PurchaseInvoice and DisbursementPayment, function as the accounts payable sub-ledger. They record individual transactions at a level of detail that the ledger postings alone do not capture.

2.3.2 Trial Balances

A trial balance is a listing of all general ledger account balances at a specific point in time. It serves as a control to verify that total debits equal total credits and as the starting point for preparing financial statements. In an analytics context, the trial balance is useful as a summary dataset that provides a high-level view of the organization’s financial position. Auditors frequently use the trial balance as the population from which they select accounts for testing. The trial balance is not a separate table in most databases but rather a report generated by summing the general ledger entries for each account.

2.3.3 Enterprise Resource Planning Systems

An enterprise resource planning system integrates data from across an organization’s functional areas into a single database. A typical ERP system captures not only financial transactions but also sales orders, purchase orders, production schedules, inventory movements, human resources records, and customer relationship management data. This integration means that an accountant with access to the ERP database can trace a sales transaction from the customer order through the shipment, the invoice, the revenue recognition entry in the general ledger, and the cash receipt. The Charles River dataset simulates this integrated environment, with table groups that cover the full cycle from sales orders and purchase orders through shipments, invoicing, cash receipts, manufacturing, payroll, and general ledger entries.

ERP systems are a common data source in large and mid-size organizations. They produce structured data with defined fields, data types, and validation rules. However, ERP data is not immune to quality problems. Data entry errors, system configuration mistakes, and gaps in validation rules can all introduce inaccuracies that propagate through the system, and even organizations that have implemented ERP successfully still experience data quality problems (Cao & Zhu, 2013). One of the advantages of the analytical skills taught in this book is the ability to identify these problems by examining the data directly rather than relying solely on the reports the ERP system generates. In one field study, an analysis of a bank’s procurement event logs found payments made without approval and violations of segregation of duties that the bank’s internal auditors had not detected with conventional procedures (Jans et al., 2014).

Accountants also work with data that originates outside the organization. External data sources include bank statements used for cash reconciliation, market price feeds used for fair value measurements, credit rating data used for impairment assessments, tax rate tables used for compliance calculations, and industry benchmarking data used for comparative analysis. External data introduces additional quality concerns because the accountant has less control over how the data was collected, formatted, and maintained. When external data is combined with internal data for analysis, differences in formatting, time periods, or classification schemes can create inconsistencies that must be resolved before the analysis can proceed. Figure 2.2 summarizes these sources.

Figure 2.2: Common Sources of Accounting Data.

NoteConnecting the Dots

In the Excel chapters of Part II, you will use Excel lookup functions and Power Query to merge data from different Charles River tables, such as combining SalesOrder data with Customer details from a separate worksheet. The challenges you encounter there, including mismatched identifiers and inconsistent formatting, are practical examples of the data quality issues discussed here. Understanding the sources of accounting data now will help you anticipate those challenges when you begin working with the tools.

2.4 Data Quality Dimensions

Data quality is not a single characteristic that data either has or lacks. Researchers have identified multiple dimensions of data quality, each describing a different aspect of what makes data fit for its intended use. The framework developed by Wang and Strong (1996) identified fifteen dimensions, grouped into four broader categories, and this chapter adapts four of them as practical checks for accounting analytics: accuracy, completeness, consistency, and timeliness. Here, consistency covers both a consistent representation and agreement with business rules. These four dimensions provide a practical vocabulary for describing data problems and assessing whether a dataset is suitable for a given analytical task.

2.4.1 Accuracy

Accuracy means that the recorded values correspond to the true values they are intended to represent. A transaction amount of $5,000 is accurate if the actual transaction was indeed $5,000. An account classification of “Asset” in the Account.AccountType column is accurate if the account genuinely meets the definition of an asset under the applicable accounting standards. Accuracy is the most fundamental quality dimension because inaccurate data leads directly to incorrect analytical results.

In accounting datasets, accuracy problems arise from data entry errors (typing $50,000 instead of $5,000), calculation errors (applying the wrong exchange rate to a foreign currency transaction), classification errors (posting an expense to the wrong account), and measurement errors (using an incorrect cost allocation formula). Some accuracy problems are obvious when you inspect the data, such as a negative value in a field that should always be positive. Others are subtle and can only be detected by comparing the data to an independent source or by applying analytical tests such as reasonableness checks.

2.4.2 Completeness

Completeness means that all expected data values are present. A dataset is complete if it contains all the records that should be there and if every field within each record has a value where one is expected. Completeness problems take two forms. The first is missing records, where entire transactions or entities are absent from the dataset. The second is missing values, where individual fields within existing records are blank or null.

In accounting, completeness is a significant concern for both preparers and auditors. A revenue dataset that is missing the last three days of the fiscal period will understate total revenue. An accounts payable listing that omits recently recorded invoices will understate total liabilities. Auditors specifically test for completeness because management has an incentive to understate liabilities and overstate assets, and missing data can serve that purpose either intentionally or accidentally. In the Charles River dataset, you will find Shipment records with no TrackingNumber. For those shipments the extract holds no carrier reference, so evidence that the goods left the warehouse would have to come from elsewhere, such as a dispatch log or a signed delivery receipt.

2.4.3 Consistency

Consistency means that the same fact is represented in the same way wherever it appears. A customer recorded as “Beacon Hill Interiors” in one system should not appear as “BEACON HILL INT.” in another and as “Beacon Hill Interiors, LLC” in a third. An account coded as “4100” in the chart of accounts should be coded as “4100” everywhere it is referenced, not as “4100.0” in some records and “41-00” in others. Consistency also applies to different fields within the same record. In the Charles River Shipment table, some shipments are still marked “In Transit” although their DeliveryDate falls on or before the year-end under review. If the DeliveryDate records the actual delivery, the Status column and the DeliveryDate column describe the same fact, whether the goods have arrived, and they disagree. If it records the expected delivery, the shipments are overdue instead. Which reading applies has to be confirmed before the rows are called errors.

Consistency problems are common in accounting datasets, and they are particularly troublesome because they can cause analytical results to be silently wrong rather than obviously wrong. If a SQL query groups revenue by customer name and the same customer appears under three different name variations, the query will produce three separate line items instead of one. The total revenue figure will be correct, but the per-customer breakdown will be misleading. Consistency problems are especially prevalent when data comes from multiple sources (Cai & Zhu, 2015) or when an organization has migrated its data from one system to another, a process whose success depends on data quality (Azeroual & Jha, 2021).

2.4.4 Timeliness

Timeliness means that the data reflects the current state of the business as of the relevant reporting date. A balance sheet analysis performed on general ledger data that has not been updated to include the final adjusting entries of the period will produce inaccurate results. A receivables aging analysis based on data that is two weeks old may overstate the number of delinquent accounts if payments have been received in the interim.

Timeliness is a quality dimension that depends on context. Data that is perfectly timely for a monthly management report may be inadequate for a daily cash position analysis. The analytical question determines what level of timeliness is required. Accountants must assess whether the data they are working with is current enough for the purpose at hand and, if it is not, either obtain more current data or disclose the limitation in their analysis. Table 2.2 summarizes the four dimensions with an example of each from the Charles River data.

Table 2.2: The Four Data Quality Dimensions with Accounting Examples

Dimension	Definition	Charles River Example
Accuracy	Recorded values correspond to the true values they are intended to represent.	Some SalesInvoice records are dated before any shipment on the same sales order. Either the invoice date is wrong or the customer was billed before the goods were shipped, and both possibilities matter for revenue cutoff.
Completeness	All expected records and field values are present in the dataset.	Several hundred Shipment records have no TrackingNumber. For those shipments the extract holds no carrier reference, which weakens any test of whether recorded sales were actually fulfilled.
Consistency	The same fact is represented in the same way wherever it appears in the dataset or across related datasets.	Shipments from every year are still marked “In Transit,” with a DeliveryDate on or before the year-end under review. If that date records the delivery, the two columns disagree, and a count of in-transit shipments by status overstates the goods not yet delivered.
Timeliness	The data reflects the current state of the business as of the relevant reporting date.	The general ledger extract runs several weeks into a new fiscal year, but those periods hold only supplier payments that settle the prior year’s invoices. Comparing them with complete months of activity would suggest a collapse in business that never happened.

WarningWatch out

A dataset can score well on one quality dimension while failing on another. The general ledger may agree with every source document and follow one format for every account code, yet be incomplete because transactions from a subsidiary were not yet consolidated. Evaluating data quality requires examining all four dimensions, not just the one that is easiest to check.

2.5 Common Data Quality Problems in Accounting Datasets

The four quality dimensions provide a framework for thinking about data quality, but in practice, accountants encounter specific types of problems that recur across organizations and systems. Learning to recognize these problems is the first step toward resolving them. This section describes the most common data quality problems you will encounter when working with accounting data.

2.5.1 Missing Values

Missing values occur when a field that should contain data is blank, null, or contains a placeholder such as “N/A” or “TBD.” In accounting datasets, missing values often appear in date fields (a shipment date that was never recorded), in descriptive fields (a journal entry with no description), and in classification fields (a transaction that was never assigned to a cost center). Missing values can affect analysis in several ways. Some analytical tools exclude records with missing values from calculations, which can distort totals and averages. Other tools treat missing values as zeros, which produces a different type of distortion.

Not every blank is an error. In the Charles River Item table, the ListPrice column is blank for items that the company does not sell to customers, such as raw materials, packaging, and capital equipment. A blank TrackingNumber on a shipment, by contrast, is information that should exist: nearly every shipment carries one, whichever carrier delivered it, the company’s own fleet included. Deciding whether a missing value is expected by design or a genuine gap is part of every data quality assessment.

2.5.2 Duplicate Records

Duplicate records occur when the same transaction, entity, or event appears more than once in a dataset. In accounts payable, a duplicate payment means the organization paid the same invoice twice. In a customer master file, a duplicate customer record means the same customer has two identities in the system, which splits their transaction history and makes it difficult to assess total purchasing volume or outstanding receivables. Duplicates can result from data entry errors, system integration problems, or the failure of validation controls. Detecting duplicates is an important audit analytics procedure, and you will perform duplicate detection exercises in several chapters of this book.

In the Charles River data, the PurchaseInvoice table contains supplier invoice numbers that appear twice for the same supplier, a classic duplicate-payment risk. The Customer table lists pairs of customers under identical names. Whether a same-name pair is a duplicate or two different companies can only be decided by comparing the rest of each record, such as the city and customer segment.

2.5.3 Inconsistent Formatting

Inconsistent formatting occurs when the same type of data is recorded in different formats across records or across tables. Date formatting is a frequent source of inconsistency. One table may store dates as “2024-03-15” while another stores them as “03/15/2024” or “15-Mar-2024.” When both tables hold real date values, these are only different displays of the same date, and the values still match; the problem arises when dates arrive as text written in different forms. Name formatting is another common issue. Customer names may appear with or without legal suffixes (“Inc.”, “LLC”), with different capitalization, or with abbreviated words. Currency amounts may include or exclude currency symbols, and numeric values may use commas or periods as decimal separators depending on regional settings. The Charles River dataset stores every date in the same format, but data combined from spreadsheets, legacy systems, or external sources often does not.

Inconsistent formatting is more than a cosmetic problem. When data from two tables must be matched or merged, formatting differences can prevent correct joins and lookups. An XLOOKUP formula in Excel or a JOIN clause in SQL that matches on customer name will fail to connect records if the name is spelled differently in the two tables. The Excel chapters of Part II and the SQL chapters of Part III teach specific techniques for standardizing data before analysis.

2.5.4 Outliers

Outliers are values that fall far outside the expected range for a given field. A single invoice for $2,000,000 in a dataset where the average invoice is $5,000 may be a legitimate large transaction, or it may be a data entry error where extra zeros were added accidentally. A journal entry posted at 3:00 AM may be a legitimate automated posting, or it may indicate unauthorized activity. Outliers require investigation rather than automatic removal. The appropriate response depends on the context and on the accountant’s professional judgment.

In audit analytics, outliers are often the transactions of greatest interest. Auditors look for unusual transactions because they may indicate errors, fraud, or control failures. The statistical and analytical techniques covered in later chapters, including stratification and Benford’s Law analysis in the Excel chapters of Part II and anomaly detection queries in the SQL chapters of Part III, are all designed to help you identify and evaluate outliers systematically. In the Charles River Shipment table, the highest FreightCost values are several times the average shipment. Nothing in the data marks them as errors, which is exactly the point: an outlier is a question to investigate, not a conclusion.

Figure 2.3 illustrates each of these problems with records from the Charles River data.

Figure 2.3: Common Data Quality Problems.

2.5.5 Testing for Data Quality Problems

Detecting these problems requires systematic tests rather than casual inspection. Many tests examine a table on its own: counting blanks, looking for repeated values, and checking that dates fall in a plausible order. Others compare related tables. Referential integrity is the requirement that every reference from one table to another points to a record that actually exists. Every AccountID in GLEntry should exist in the Account table, and every CustomerID in SalesOrder should exist in the Customer table. A record that fails this test is an orphaned record, because it refers to a parent record that is not there. A blank reference is a different question. Some relationships are optional: an invoice line for a design service has no shipment to point to, so a blank is judged against the business rule rather than counted as an orphan.

Table 2.3 summarizes common tests for each quality dimension and the Excel techniques you will use to perform them in the tutorials that follow. A test that finds no problems is also a finding, because it tells you which tested relationships held in the population you tested.

Table 2.3: A Data Quality Checklist

Dimension	Common Tests	Excel Techniques
Accuracy	Values within a plausible range; dates in a logical order, such as an invoice dated no earlier than its shipment; no duplicate records	Sorting, filters, COUNTIFS to count repeated values, IF and MINIFS helper columns
Completeness	Blank values in required fields; entries present in every period; every expected transaction type present	Filters for blanks, COUNTBLANK, PivotTables by period and by type
Consistency	Related fields that agree with each other; references that match a parent record (referential integrity); identical coding across tables	Filters on combinations of columns, XLOOKUP or COUNTIFS against the parent table, PivotTables of distinct values
Timeliness	Date range of the extract; partial first and last periods; entries dated outside the period under review	Sorting by date, MIN and MAX, PivotTables by fiscal year and period

TipIn Practice

In professional practice, data quality assessment is often the first task in any analytics engagement. Audit teams perform data quality checks before running analytical procedures to ensure that the results will be reliable. Management accountants validate the data behind cost reports before presenting them to executives. Practitioners value these skills highly: in a survey of accounting professionals, public accountants and those working in industry valued skills in data management, data cleansing, and correcting inaccurate or incomplete data more than accounting educators did (Holmes & Douglass, 2022). The AICPA’s Guide to Audit Data Analytics includes practical considerations for assessing the reliability of the data that an audit data analytic uses (AICPA, 2026). The data quality skills you develop in this chapter apply directly to that work.

2.6 Guided Tutorial 2.1: Inspecting Charles River O2C and Item Data for Data Quality Problems

Context and objective. In this tutorial, you will examine the Charles River O2C tables in Excel and identify specific data quality problems across several worksheets. The goal is to practice applying the four quality dimensions (accuracy, completeness, consistency, and timeliness) and the tests in Table 2.3 to a real dataset, and to document what you find. This tutorial connects to the opening scenario by showing you how a data quality assessment works in practice.

Prerequisites. You need a desktop version of Microsoft Excel (see What You Need in the Preface) and the CharlesRiver.xlsx file provided with this textbook. This tutorial adds columns to a worksheet, so work in a copy: copy CharlesRiver.xlsx in its folder, name the copy CharlesRiver_Work.xlsx, and use it for the rest of Part I. The downloaded workbook then stays as provided for Parts II and IV, whose tutorials read it with Power Query.

Step-by-step instructions.

Step 1. Get to know the SalesOrder worksheet.

1. Open CharlesRiver_Work.xlsx in Excel and click the SalesOrder worksheet tab.
2. Scroll across the columns to familiarize yourself with the available fields. You should see columns including SalesOrderID, OrderNumber, CustomerID, OrderDate, RequestedDeliveryDate, SalesRepEmployeeID, FreightTerms, and Status.
3. Scroll down to get a sense of the number of records.

Step 2. Find shipments with no tracking number.

1. Click the Shipment worksheet tab. Examine the columns, which include ShipmentID, ShipmentNumber, SalesOrderID, ShipmentDate, WarehouseID, ShippedBy, TrackingNumber, Status, DeliveryDate, FreightCost, and BillableFreightAmount.
2. Use the filter button on the TrackingNumber column header to show only blank cells, as in Figure 2.4. The status bar at the bottom of the window shows how many records match the filter.
3. Record the count, then clear the filter.

These missing values represent a completeness problem: shipments recorded without a tracking number, the only carrier reference the extract holds. The blanks occur with every carrier, the company’s own Private Fleet included, so they are not a feature of one delivery method. A blank does not show that the goods were never shipped; it means that the evidence of shipment must come from elsewhere, such as a dispatch log or a signed delivery receipt.

Figure 2.4: The Shipment Worksheet Filtered to Blank Tracking Numbers.

Step 3. Find shipments still marked In Transit after their delivery date.

1. Still on the Shipment worksheet, filter the Status column to show only “In Transit.”
2. Look at the ShipmentDate and DeliveryDate columns for these rows.
3. Record how many shipments are affected, then clear the filter.

You will find shipments from every year of the data still marked “In Transit,” and every one of them has a DeliveryDate on or before December 31, 2026, the year-end under review. What that means depends on what the DeliveryDate records, so confirm it before you judge the rows. If it records the actual delivery, the Status and DeliveryDate columns describe the same fact, whether the goods have arrived, and they disagree. That is a consistency problem, and a timeliness problem as well, because the status was not updated when the goods arrived. If it records the expected delivery, the shipments are overdue, and the date alone does not show that they arrived. Record them for follow-up with the carrier or the customer.

Step 4. Look for duplicate customers.

1. Click the Customer worksheet tab and sort the CustomerName column alphabetically.
2. Scan for customers that appear more than once. You will find pairs of customers with exactly the same name.
3. For each pair, compare the other columns, such as City, Region, and CustomerSegment.
4. Record your conclusion for each pair and the evidence behind it.

If the details differ, the records may describe two different companies that happen to share a name. If they match, the records are likely duplicates of one customer.

Step 5. Look for freight outliers.

1. Return to the Shipment worksheet and sort the FreightCost column from largest to smallest.
2. In a cell outside the Shipment Table, such as on a new worksheet, calculate the typical freight cost for a shipment with =AVERAGE(T14_Shipment[FreightCost]), and compare the highest values with it.
3. Make a note of the largest values, which are several times the average.

In a real engagement, you would investigate these outliers further, for example by comparing them with the size of the order, before deciding whether they are legitimate or errors. Nothing in the Charles River data marks them as errors, which is exactly why an outlier is a question to investigate rather than a conclusion.

Step 6. Find the earliest shipment for each invoice. Next, test whether any invoices are dated before the goods were shipped. Because a sales order can have several shipments, compare each invoice with the earliest shipment on its order.

1. Click the SalesInvoice worksheet tab.
2. In the first empty column (column M), type the header Min Date.
3. Enter a formula that uses MINIFS to retrieve the earliest ShipmentDate for the invoice’s SalesOrderID from the Shipment worksheet: =MINIFS(Shipment!D:D, Shipment!C:C, E2).
4. Format the result as a date.

Step 7. Flag invoices dated before their first shipment.

1. In the next column (column N), type the header Check and enter this formula, which compares the InvoiceDate in column C with the earliest shipment date:

   =IF(M2=0, "No shipment", IF(C2<M2, "Check", "OK"))

2. Adjust the column letters if your workbook differs. The Table fills each formula down its column, so check that the last invoice row has a result.

3. Filter the Check column for “Check” and record the count, then filter it for “No shipment”, record that count, and clear the filter.

You should find a handful of invoices dated before any shipment on their order, a potential accuracy problem that also matters for revenue cutoff. The test is a screen at the level of the order: an invoice marked OK may still bill lines that shipped later than the order’s first shipment, which later chapters test by tracing each invoice line to its own shipment. Figure 2.5 shows the result, with the columns between the keys and the helper columns hidden. The “No shipment” rows are invoices for design services, which do not involve shipments.

Figure 2.5: The SalesInvoice Worksheet with Date Check Helper Columns.

Step 8. Distinguish values missing by design from missing information.

1. Click the Item worksheet tab and filter the ListPrice column to show blank cells.
2. Look at the ItemGroup column for those rows.
3. Compare this finding with the blank TrackingNumbers from Step 2, which represent information that should exist.
4. Record both findings and the reasoning that distinguishes them.

The blank list prices belong to items that Charles River buys rather than sells: raw materials, packaging, capital equipment (the Capex group), and operating services such as insurance and IT support in the Services group. The design services that Charles River does sell, such as Design Consultation, carry an hourly list price. These values are missing by design rather than by error.

Checkpoint. At this point, you should have identified several data quality findings in the Charles River O2C and Item tables:

• shipments with no TrackingNumber (completeness);
• shipments still marked In Transit after their DeliveryDate (consistency and timeliness, if that date records the delivery);
• invoices dated before any shipment on their order (accuracy).

You should also have assessed three findings that require judgment:

• same-name customers that may or may not be duplicates;
• freight outliers that may be legitimate;
• blank list prices that turn out to be expected.

Your written list of findings should note the table, the column, the type of problem, the number of affected records, and whether you consider each finding a genuine error. This list is a simple version of the data quality assessment that auditors perform at the start of every analytics engagement.

2.7 The Concept of Tidy Data

In 2014, the statistician Hadley Wickham published a paper that formalized a set of principles for organizing datasets in a way that makes them easy to analyze. He called datasets that follow these principles “tidy” and those that violate them “messy” (Wickham, 2014). The concept of tidy data has since become widely adopted in data analysis across many fields, and it is directly relevant to accounting analytics.

A tidy dataset has three properties. First, each variable occupies its own column. A variable is any attribute that is measured or recorded, such as transaction date, account number, or debit amount. Second, each observation occupies its own row. An observation is a single instance of what is being measured, such as one line item in a journal entry or one sales transaction. Third, each type of observational unit forms its own table. Customer information belongs in a customer table, order information belongs in an order table, and product information belongs in a product table.

These principles may seem obvious, but violations are surprisingly common in accounting data. A frequent violation is storing multiple variables in a single column. For example, a spreadsheet might have a column called “Account” that contains entries like “4100-Sales-Domestic” where the account number, account name, and geographic segment are all packed into one field. Analyzing sales by geographic segment requires splitting this column into three separate columns before any aggregation can be performed.

Another common violation is storing values in column headers. A budget spreadsheet might have columns labeled “January,” “February,” “March,” and so on, with each row representing a department and the values representing that department’s budgeted amount for each month. This layout violates two principles at once. The month is a variable, yet it has no column of its own, and each row holds several observations (one for each month) instead of one. The layout is easy for humans to read but harder for analytical tools to work with. A tidy version of the same data would have three columns (Department, Month, and Budget Amount) with one row for each department-month combination. This format, sometimes called “long” format as opposed to the “wide” format of the original, is the one PivotTables, SQL GROUP BY queries, and Power BI data models handle most easily: the month becomes a field you can group, filter, and relate to the actual results, and a new month is a new row rather than a new column. The wide layout can still be analyzed, but every month added means another column and more formulas. Figure 2.6 shows the same budget data in both layouts.

Figure 2.6: Messy Versus Tidy Data.

WarningWatch out

It is easy to confuse “tidy” with “clean.” A tidy dataset is one that follows the structural principles described above: each variable in its own column, each observation in its own row, each type of observational unit in its own table. A clean dataset is one that is free of quality problems, such as missing information, duplicates, and inconsistencies, for the use you intend. A blank that is expected by design is not such a problem. A dataset can be tidy but dirty (properly structured but containing errors) or clean but messy (free of errors but organized in a way that makes analysis difficult). Both structure and quality must be addressed before analysis can proceed.

The Charles River dataset is, for the most part, already tidy. It is stored as relational database tables where each column represents a single variable and each row represents a single observation. This is a design choice rather than something a database guarantees: a relational table gives every column a name and a data type, but nothing stops a designer from creating one column for each month. The budget tables show the tidy choice. Each row of the Budget table holds one budgeted amount for one account, cost center, and month, which is exactly the long format, and the BudgetLine table holds the more detailed driver lines, by item and warehouse, behind those amounts and behind accounts that the Budget table leaves out. The reports that managers read are another matter. A budget-versus-actual report is usually laid out with months across the top, so you will often need to move between the two layouts. The Excel chapters of Part II introduce Power Query, which provides a systematic way to reshape data between messy and tidy formats.

NoteConnecting the Dots

The tidy data principles described here are closely related to the way relational databases organize data, which Chapter 3 examines. Tidy data asks that each variable have its own column and each observation its own row; a well-designed database goes further and stores each fact once, in one table. Understanding tidy data now will make the relational database concepts in the next chapter feel natural.

2.8 Source-to-Ledger Traceability

Chapter 1 introduced source-to-ledger traceability, the ability to trace a general ledger entry back to the business document that created it through the SourceDocumentType, SourceDocumentID, and SourceLineID columns of GLEntry. For data quality work, the same columns serve a second purpose. Because each ledger entry points to its source, traceability lets you test two quality dimensions at once. For completeness, you can confirm that every recorded sales invoice produced ledger postings, so that no invoice is missing from the ledger. For accuracy, you can confirm that the amounts posted to the ledger for each invoice agree with the invoice’s total. In the Charles River data, every sales invoice reconciles to its ledger postings, which is evidence that the recorded invoices reached the ledger in full, and Tutorial 3.2 traces one invoice through every posting it made. The test starts from the invoices, so it cannot show that every sale was invoiced, or invoiced in the right period; that takes a population from outside the invoices, such as the shipments, which later chapters use. In real ERP systems, this kind of reconciliation between a sub-ledger and the general ledger is one of the most common tests that auditors and controllers perform.

NoteConnecting the Dots

In the audit analytics chapters of Parts II and III, you will use the SourceDocumentType column in GLEntry to isolate journal entries made by the accounting team from entries generated automatically by sales invoices, purchase invoices, and payroll. The traceability structure you met in Chapter 1 and used here as a quality control is the foundation for those audit analytics procedures.

2.9 Guided Tutorial 2.2: Examining the Charles River Accounting Core for Completeness and Consistency

Context and objective. In this tutorial, you will examine the Charles River Accounting Core tables to assess whether the general ledger entries and chart of accounts are complete and consistent. This is a more accounting-specific data quality assessment than the O2C tutorial, because it involves checking whether the accounting records themselves are internally coherent. The skills practiced here connect directly to the audit analytics you will perform in the Excel chapters of Part II and the SQL chapters of Part III.

Prerequisites. You need Microsoft Excel installed on your computer, your working copy CharlesRiver_Work.xlsx from Tutorial 2.1, and completion of that tutorial.

Step-by-step instructions.

Step 1. Get to know the chart of accounts.

1. Open CharlesRiver_Work.xlsx in Excel (if not already open) and click the Account worksheet tab.
2. Examine the columns available. You should see fields including AccountID, AccountNumber, AccountName, AccountType, AccountSubType, and ParentAccountID.
3. Scroll through the list to get a sense of how many accounts exist and how they are organized.

This is the chart of accounts for Charles River, and it forms the backbone of the Accounting Core. The ParentAccountID column creates a hierarchical structure that organizes accounts into groups.

Step 2. Check the chart of accounts for completeness. Every major account type should be represented. Sort or filter the AccountType column and verify that the chart includes accounts classified as Asset, Liability, Equity, Revenue, and Expense. If any major category is missing, that is a completeness problem. Record your findings.

Step 3. Get to know the general ledger. Click the GLEntry worksheet tab and examine the columns available. You should see fields including GLEntryID, PostingDate, AccountID, Debit, Credit, VoucherType, VoucherNumber, SourceDocumentType, SourceDocumentID, SourceLineID, CostCenterID, FiscalYear, and FiscalPeriod. Scroll down to get a sense of the volume of data. The GLEntry worksheet holds hundreds of thousands of rows, close to the maximum that a single Excel worksheet can contain.

Step 4. Check that total debits equal total credits. For every transaction, total debits should equal total credits, a fundamental accounting consistency requirement. To test it at the aggregate level, put the totals below the data, outside the GLEntry Table:

1. Clear any filter on the worksheet, click cell A2, the first GLEntryID, and press Ctrl+Down Arrow. GLEntryID is never blank, so Excel stops at the last row of the data. In a column with blanks, such as SourceLineID, it would stop at the first blank.
2. Leave one blank row, so that the Table does not grow to take in your totals, and type Total in column C of the next row.
3. In column D of that row, type =SUM(, click the first Debit value in cell D2, press Ctrl+Shift+Down Arrow to select every Debit value, and close the parenthesis.
4. Enter the same kind of formula for the Credit column in column E.
5. In the row below, type Difference in column C. In column D, type =ROUND(, click the debit total, type ,2)-ROUND(, click the credit total, and type ,2).

Excel stores amounts as binary numbers, so two totals of many cents can differ by a tiny fraction of a cent even when the ledger balances. Rounding each to the cent removes that noise. Read the result by a fixed rule. A difference of zero, as in Figure 2.7, means the ledger balances in the aggregate, and any other difference, however small, is a consistency problem to investigate. Equal totals show only that the ledger balances as a whole: an entry left out entirely, with its debit and its credit, would leave them equal, and so would errors that offset each other.

Figure 2.7: Debit and Credit Totals at the Bottom of the GLEntry Worksheet.

Step 5. Check that every ledger account exists in the chart of accounts. This is a referential integrity test (Table 2.3). With hundreds of thousands of rows, adding a formula to every GLEntry row would be slow, so work with distinct values instead.

1. Click a cell inside the GLEntry Table and choose Insert > PivotTable, on a new worksheet. The source is the Table, T3_GLEntry, so the Total and Difference rows of Step 4 stay out. Place AccountID in the Rows area. The PivotTable lists each AccountID that appears in the ledger exactly once.
2. In the column next to the PivotTable, beside the first AccountID, enter =COUNTIFS(T1_Account[AccountID],A4), with A4 replaced by the cell of the first AccountID, and fill it down to the last account, above the Grand Total row. A result of zero marks an AccountID that is not in the chart of accounts.
3. Record the result.

Any AccountID with no match is an orphaned reference. In the Charles River data, every AccountID should match. A clean result like this is still a finding worth recording, because it tells you that every account the ledger refers to exists in the chart of accounts.

WarningWatch out

A single Excel worksheet can hold just over one million rows, and the Charles River GLEntry table already fills most of that space. Formulas copied down every row of a table this size can make Excel slow or unresponsive. Summarizing first, as the PivotTable in Step 5 does, keeps the workbook responsive. When a dataset outgrows Excel altogether, SQL becomes the better tool, which is one reason Part III teaches it.

Step 6. Check the periods the ledger covers.

1. Sort by the PostingDate column and note the earliest and latest dates.
2. Build a PivotTable with FiscalYear and FiscalPeriod in the Rows area and GLEntryID in the Values area. Excel sums a numeric field by default, so open Value Field Settings and choose Count.
3. In the PivotTable, filter FiscalYear to the latest fiscal year and add SourceDocumentType to the Rows area to see the types of its entries.
4. Record what you find and whether these entries belong in an analysis of the fiscal years under review, which is a timeliness and cutoff question.

Most periods contain a similar volume of entries, but the first period and the final periods contain far fewer. Before treating a low-volume period as a sign of missing transactions, check whether it sits at the edge of the extract. The extract begins on the first day of its first fiscal year, which does not tell you when the company or its records began, and it ends early in a new fiscal year in which only some kinds of activity have been recorded. In the latest fiscal year you will find only supplier payments, which settle invoices from the prior year. An edge explains why a period is small, but it does not show that nothing is missing from it.

Step 7. Look for entries with no amount. Examine the Debit and Credit columns for missing or unusual values. Filter for rows where both the Debit and Credit fields are zero, and record the count and the source document type. You will find a small number of such rows, all from the same source document type. An entry with no debit and no credit amount has no effect on any balance. A system may keep such a row for an operational or audit-trail reason, or it may be an artifact or a data entry error, so it should be investigated and explained.

Step 8. Check that every business cycle posts to the ledger. Use a PivotTable to count the number of GLEntry rows for each value of the SourceDocumentType column, and record the distribution. You should see types such as “SalesInvoice,” “Shipment,” “PurchaseInvoice,” “GoodsReceipt,” “MaterialIssue,” “PayrollPayment,” “JournalEntry,” and “WorkOrderClose.” If an expected source type were absent from a period in which that activity took place, that would be a potential completeness problem. Before concluding that a whole business cycle failed to post, check the period, how the extract was specified, and how that activity reaches the ledger. Finding every type present is weaker evidence: it cannot show that every transaction of each type was posted.

Checkpoint. At this point, you should have assessed the Charles River Accounting Core against the quality dimensions in Table 2.3:

• Completeness: all account types are present in the chart of accounts, every period has entries (and you know why some periods are small), and every expected source document type appears in GLEntry.
• Consistency: total debits equal total credits to the cent, and every GL account reference exists in the chart of accounts.
• Accuracy and timeliness: you identified the zero-amount rows and the entries dated beyond the fiscal years under review.

Your findings should be documented in a brief written summary that identifies each test performed, the result, and any issues discovered, including the tests that found no problems. This exercise previews the audit analytics work in Parts II and III, where you will use Excel and then SQL to automate tests like these across the entire population.

2.10 Data Quality Across the Charles River Table Groups

The Charles River table groups, summarized in Table 1.3 of Chapter 1, correspond to the kinds of data an accountant encounters in a real organization, and each kind brings its own data quality concerns. Reference and planning tables such as Item, CostCenter, and BudgetLine change infrequently, so their problems tend to involve completeness (a budget line with no cost center, or a missing product attribute) and duplication (the same product entered twice under different codes). The transactional cycle tables accumulate records every day and are the primary source of the problems discussed in this chapter: missing values, duplicate transactions, fields that contradict each other, and dates out of sequence. They are also where control problems surface, such as the same person creating and approving a transaction. The stakes are highest in the Accounting Core, because errors in GLEntry flow directly into the financial statements. Chapter 3 examines how these groups are structured, and Table 2.4 summarizes the issues to watch for in each group.

Table 2.4: Data Quality and Control Issues Across the Charles River Table Groups

Table Group	Real-World Equivalent	Issues to Watch For
Accounting Core	General ledger, chart of accounts, and journal entry system	Zero-amount postings, entries dated beyond the period under review, partial periods at the edges of an extract, journal entries created and approved by the same person or created and approved on weekends
Order-to-Cash (O2C)	Sales order management, fulfillment, invoicing, cash receipts, pricing, commissions, and returns	Missing tracking numbers, shipment statuses that conflict with delivery dates, invoices dated before shipment, customers with identical names, sales priced below the approved floor
Procure-to-Pay (P2P)	Purchasing, goods receipt, supplier invoicing, and vendor payment	Duplicate supplier invoice numbers, duplicate payment references, purchase orders created and approved by the same person, requisitions missing approval or just below approval thresholds, supplier addresses that match employee addresses
Manufacturing	Bills of material, routings, work orders, material issues, and production completion	Work orders with no routing operations
Payroll and Time	Shift scheduling, time tracking, payroll calculation, payment, and remittances	Missing clock-outs, duplicate time-clock days, employees paid without time records, payroll registers without a payment
Fixed Assets and Financing	Fixed asset register, asset events, and debt schedules	Asset events and debt schedules that must agree with the ledger
Master Data	Product catalog, warehouse locations, and employee records	Item names shared by several items, missing catalog attributes, list prices left blank for items that are not sold
Organizational Planning	Cost center hierarchy and monthly budgets	Budget lines and ledger entries with no cost center
Demand Planning and MRP	Demand forecasts, inventory policies, and supply planning	Forecast overrides far outside the normal range

Many of the issues in Table 2.4 go beyond data quality into internal control, and you will test them in the audit analytics chapters of Parts II and III. Understanding these mappings helps you connect the exercises in this book to the data environments you will encounter after graduation. When you inspected the Charles River Shipment and Customer tables for quality problems in Tutorial 2.1, you were practicing the same skills you would use when working with transaction exports from a client’s order management system. When you assessed the completeness and consistency of the GLEntry table in Tutorial 2.2, you were performing the same type of evaluation that an audit team performs at the start of every engagement.

2.11 Looking Ahead

This chapter has provided the vocabulary and the framework you need to think critically about accounting data before you begin analyzing it. You can now distinguish between quantitative and qualitative data, between structured and unstructured data, and between data that is tidy and data that needs restructuring. You know the primary sources of accounting data, the four dimensions against which data quality is measured, and the tests that reveal common problems. You understand how the Charles River table groups map to real-world data environments and how source-to-ledger traceability in GLEntry connects operational activity to the posted ledger. You have practiced inspecting the O2C and Accounting Core tables for specific quality problems and documenting your findings.

In the next chapter, you will build on this foundation by studying how accounting data is organized within relational databases. Chapter 3 examines in depth the tables, keys, and relationships that make it possible to connect data across different parts of an accounting system. Understanding these structures will prepare you for the SQL chapters in Part III and for the data modeling work you will do in Power BI in Part IV.

2.12 Chapter Summary

Every accounting analytics project depends on the quality of the data that feeds it. Before applying any analytical technique, accountants must understand what types of data they are working with, where the data came from, and whether it is fit for the intended purpose. This chapter established the conceptual foundation for that assessment.

Accounting data can be classified along two dimensions. The quantitative versus qualitative distinction determines whether arithmetic operations are appropriate. The structured versus unstructured distinction determines which analytical tools can be applied. The Charles River dataset contains structured data organized in rows and columns, the format that Excel, SQL, and Power BI are designed for. Accounting data originates from general ledgers, sub-ledgers, trial balances, enterprise resource planning systems, and external sources. Each source has characteristic strengths and limitations that affect data quality.

Data quality is assessed along four dimensions: accuracy, completeness, consistency, and timeliness. Accuracy means that recorded values correspond to true values. Completeness means that all expected records and fields are present. Consistency means that the same fact is represented the same way wherever it appears. Timeliness means that the data reflects the relevant reporting period. Common data quality problems include missing values, duplicate records, inconsistent formatting and values, and outliers. Each of these problems can distort analytical results if not identified and addressed before analysis begins, yet not every blank or unusual value is an error, and deciding which ones are is part of the assessment. Systematic tests, including referential integrity checks between related tables, turn the four dimensions into practical procedures.

The concept of tidy data provides a structural standard for how datasets should be organized: each variable in its own column, each observation in its own row, and each type of observational unit in its own table. Tidy data is easier to analyze than messy data because PivotTables, SQL queries, and Power BI data models can group, filter, and relate its variables directly. The Charles River tables follow tidy principles by design, but real-world data and the reports built from it often require restructuring.

The two guided tutorials applied these ideas to the Charles River data. In the O2C and Item tables, you found shipments with no tracking number, shipments still marked In Transit after their delivery dates (a conflict if those dates record actual deliveries), and invoices dated before any shipment on their order, and you weighed findings that require judgment, such as same-name customers, freight outliers, and list prices that are blank by design. In the Accounting Core, you confirmed that total debits equal total credits to the cent and that every account reference exists in the chart of accounts, and you identified zero-amount entries and partial periods at the edges of the extract. Source-to-ledger traceability, through the SourceDocumentType, SourceDocumentID, and SourceLineID columns in GLEntry, provides the foundation for tracing a ledger entry back to the business event that created it and for testing whether the recorded documents reached the ledger completely and accurately, a test that cannot by itself show that every transaction was recorded.

2.13 Key Terms

Accuracy. A data quality dimension indicating that recorded values correspond to the true values they are intended to represent. In accounting, accuracy problems include data entry errors, misclassifications, and incorrect calculations.

Completeness. A data quality dimension indicating that all expected records and field values are present in the dataset. Missing transactions, blank fields, and omitted time periods are examples of completeness problems.

Consistency. A data quality dimension indicating that the same fact is represented in the same way wherever it appears in a dataset or across related datasets. Inconsistent customer names, account codes, or date formats, and fields within a record that contradict each other, are common consistency problems.

Data quality. The degree to which a dataset is fit for its intended use, assessed across multiple dimensions including accuracy, completeness, consistency, and timeliness.

Duplicate record. A record that appears more than once in a dataset, representing the same underlying transaction, entity, or event. Duplicate payments and duplicate customer entries are common examples in accounting data.

External data. Data originating from outside the organization, such as bank statements, market price feeds, credit ratings, and industry benchmarks. External data introduces additional quality concerns because the accountant has less control over how it was collected and formatted.

General ledger. The central record of an organization’s posted financial transactions, organized by account. It holds the debit and credit entries that feed the financial statements, and in the Charles River dataset it is stored in the GLEntry table within the Accounting Core group.

Messy data. A dataset whose layout violates one or more tidy data principles, for example by storing values such as month names in column headers. Messy data is often easy for people to read but usually needs reshaping before analysis.

Missing value. A field within a record that is blank, null, or contains a placeholder rather than a substantive value. Missing values represent a completeness problem when the data should exist, and they can distort calculations if not handled properly.

Orphaned record. A record that refers to a related record that does not exist, such as a GLEntry row whose AccountID is not in the chart of accounts. Orphaned records are a failure of referential integrity.

Outlier. A data value that falls far outside the expected range for its field. Outliers may represent legitimate unusual transactions or data entry errors and require investigation to determine the appropriate treatment.

Qualitative data. Data consisting of categories, labels, or descriptions rather than numerical measurements. Account names, transaction descriptions, and vendor classifications are examples of qualitative data in accounting.

Quantitative data. Data consisting of numerical values that represent measurements or counts and that can be subjected to arithmetic operations. Transaction amounts, unit quantities, and financial ratios are examples of quantitative data in accounting.

Referential integrity. The requirement that every foreign key value that is not blank corresponds to an existing primary key value in the referenced table. Whether a blank is allowed depends on the relationship. In the Charles River database, for example, every AccountID in GLEntry should exist in the Account table.

Semi-structured data. Data that has some organizational elements but does not fit neatly into rows and columns. XBRL filings and bank statements whose PDF pages hold tables of transactions are examples relevant to accounting.

Structured data. Data organized into a predefined format of rows and columns, where each column has a defined data type. The Charles River dataset contains structured data throughout.

Sub-ledger. A detailed record for a specific category of accounts that feeds summary totals to the general ledger. The accounts receivable sub-ledger and accounts payable sub-ledger are common examples. In the Charles River dataset, tables such as SalesInvoice and CashReceiptApplication (accounts receivable) and PurchaseInvoice and DisbursementPayment (accounts payable) function as sub-ledgers.

Tidy data. A dataset organized so that each variable occupies its own column, each observation occupies its own row, and each type of observational unit forms its own table. Tidy data is the layout that analytical tools such as PivotTables, SQL, and Power BI handle most easily.

Timeliness. A data quality dimension indicating that the data reflects the current state of the business as of the relevant reporting date. Data that is not current enough for the intended analysis has a timeliness problem.

Trial balance. A listing of all general ledger account balances at a specific point in time, used to verify that total debits equal total credits and as the starting point for preparing financial statements.

Unstructured data. Data that lacks a predefined tabular format, such as the text of contracts, email correspondence, or scanned invoice images.

2.14 Multiple Choice Questions

1. A transaction amount of $15,000 is recorded in the general ledger as $1,500 due to a data entry error. This is an example of a problem with which data quality dimension?

1. Accuracy
2. Completeness
3. Consistency
4. Timeliness

2. An auditor discovers that several hundred records in the Charles River Shipment table have no value in the TrackingNumber field, which nearly every other shipment carries. This situation represents a problem with which data quality dimension?

1. Accuracy
2. Completeness
3. Consistency
4. Timeliness

3. Some shipments in the Charles River Shipment table are marked “In Transit” in the Status column, although the DeliveryDate column, which records the date the goods arrived, shows a date years before the year-end under review. Which data quality dimension best describes the disagreement between the two columns?

1. Accuracy
2. Completeness
3. Consistency
4. Timeliness

4. Which of the following is an example of qualitative data in an accounting context?

1. The name of the cost center to which an expense is assigned
2. The total amount of a sales invoice
3. The number of units of inventory on hand
4. The gross profit margin expressed as a percentage

5. For an analysis in which each department-month is one observation, a budget spreadsheet with months as column headers (January, February, March) and departments as rows is considered “messy.” Which tidy data principle does it violate?

1. Each variable should occupy its own column
2. Each observation should occupy its own row
3. Each type of observational unit should form its own table
4. Both A and B

6. An auditor performs analytical procedures using general ledger data extracted from the client’s ERP system on March 11 for a fiscal year ending December 31. The final three adjusting entries for the year were posted on March 12, after the extract was taken. Which data quality dimension best describes the problem with the date of the extract?

1. Accuracy
2. Completeness
3. Consistency
4. Timeliness

7. Which of the following matches an accounting source with its data structure correctly?

1. An XBRL filing is unstructured, because its data are not arranged in rows and columns.
2. A scanned supplier invoice is structured, because the ERP system stores it as a file.
3. A general ledger table is structured, and the narrative text of a contract is unstructured.
4. A payroll register is semi-structured, because each row combines amounts with employee names.

8. A management accountant discovers that the same product appears in the Item table under two different ItemID values because it was entered separately by two warehouse locations. This is an example of which data quality problem?

1. Duplicate record
2. Missing value
3. Outlier
4. Timeliness problem

9. Which of the following sources of accounting data provides the most complete picture of an organization’s financial transactions?

1. The accounts receivable sub-ledger
2. An external bank statement
3. The general ledger
4. A product inventory listing

10. In the Charles River dataset, the GLEntry table includes a SourceDocumentType column. Which of the following best describes the purpose of this column?

1. It identifies the kind of document that created the entry, such as a sales invoice.
2. It records the name of the employee who posted the entry to the ledger.
3. It stores the total amount of the transaction as text rather than as a number.
4. It indicates whether a supervisor approved the entry before it was posted.

11. A dataset is organized so that each variable occupies its own column and each observation occupies its own row, but several records contain data entry errors in the amount field. This dataset is best described as:

1. Tidy and clean
2. Tidy but not clean
3. Clean but not tidy
4. Neither tidy nor clean

12. A financial analyst summarizes Charles River revenue by customer name instead of by CustomerID. Two customers with different CustomerIDs, one located in Moniqueborough and one in Kingfurt, appear in the summary as a single line labeled “Brown LLC.” What caused this result?

1. The PivotTable or query contains an error in its formulas.
2. One of the two customers was never entered into the Customer table.
3. Grouping by name combined two different customers that share the same name.
4. The Customer table is more current than the sales data.

13. An enterprise resource planning system is distinct from a standalone accounting package primarily because an ERP system:

1. Uses a relational database to store its accounting and operating data
2. Integrates sales, production, purchasing, and human resources data in one database
3. Produces the financial statements automatically at the end of each period
4. Eliminates all data quality problems through built-in validation rules

14. Which of the following best explains why data quality assessment should be performed before any analysis begins?

1. Analytical software cannot open or process data that contains quality problems.
2. Professional standards prohibit accountants from using any data that contains missing values.
3. Data quality assessment is required only for audits, not for managerial or financial reporting work.
4. Quality problems can make results unreliable, and finding them first lets the accountant address them.

15. An auditor examining the Charles River GLEntry table uses the SourceDocumentType column to separate entries generated by SalesInvoice from entries generated by JournalEntry. This separation is most useful for which audit procedure?

1. Calculating the total revenue for the period from the sales postings
2. Selecting the journal entries to test for management override of controls
3. Verifying that the general ledger is stored in a tidy data format
4. Confirming that every customer in the Customer table has placed an order

2.15 Applied Exercises

2.15.1 Financial Accounting Exercises

Exercise 2.1: Assessing Revenue Data Quality in the Charles River O2C Tables

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. You are a financial reporting analyst at Charles River preparing to analyze the company’s revenue for fiscal 2026, the most recent complete fiscal year in the extract. Before building any reports, you need to verify that the underlying sales data is reliable enough to support accurate revenue figures.

Requirements.

1. Open your working copy, CharlesRiver_Work.xlsx, in Excel and examine the SalesOrder, SalesOrderLine, SalesInvoice, SalesInvoiceLine, Customer, and Item worksheets. For each table, identify and document any data quality problems you observe, classifying each problem by quality dimension (accuracy, completeness, consistency, or timeliness).

2. Assess whether the SalesInvoiceLine table contains the information needed to calculate revenue for each invoice. Identify the columns involved, such as Quantity, UnitPrice, Discount, and LineTotal, and test whether LineTotal agrees with the other columns. Discount is a rate, a fraction of the price rather than an amount, and LineTotal is rounded to the cent, so treat a difference of a cent or less as rounding rather than an error. LineTotal is the amount billed for the line, before freight and tax and before any later credit memo. Examine the PricingMethod column and identify the lines priced by approved override rather than by a price list, which deserve closer review.

3. Examine whether every CustomerID in the SalesOrder table corresponds to a customer in the Customer table. Use COUNTIFS or XLOOKUP to test this relationship. Document any orphaned records where an order references a customer that does not exist, and explain what a clean result tells you.

4. Prepare a brief written data quality assessment memorandum (one page) that summarizes your findings and recommends whether the data is suitable for revenue reporting or whether specific problems must be resolved first.

Deliverable. A one-page data quality assessment memorandum with specific findings organized by quality dimension.

Exercise 2.2: Evaluating General Ledger Completeness at Charles River

Table group: Accounting Core

Scenario. You are a financial reporting analyst who has been asked to verify that the Charles River general ledger is complete before the team begins preparing the financial statements for the fiscal year that ended December 31, 2026. Your supervisor is concerned that some transactions may not have been posted.

Requirements.

1. Open your working copy, CharlesRiver_Work.xlsx, and examine the GLEntry worksheet. Calculate the total debits and total credits across all entries. Determine whether the ledger is in balance.

2. Identify the date range covered by the general ledger entries. Use the FiscalYear and FiscalPeriod columns to check whether entries are present for every period within each fiscal year. Note any periods that have an unusually low number of entries compared to other periods, and explain whether each reflects missing transactions or the edges of the extract.

3. Examine the SourceDocumentType column to understand what types of transactions are recorded. Determine whether the ledger includes entries from all expected source types, such as SalesInvoice, Shipment, PurchaseInvoice, JournalEntry, PayrollPayment, and WorkOrderClose. If any expected source type is absent, document this as a potential completeness concern, and explain what finding all of them present does and does not show.

4. Write a brief assessment (one page) of the general ledger’s completeness, suitable for review by the financial reporting manager.

Deliverable. A one-page completeness assessment memorandum with quantitative evidence supporting each finding.

2.15.2 Managerial Accounting Exercises

Exercise 2.3: Data Quality Assessment for Product Cost Analysis

Table group: Master Data and Manufacturing

Scenario. You are a cost analyst at Charles River who has been asked to perform a product cost analysis. Before starting the analysis, you need to assess whether the data is clean enough to produce reliable cost figures.

Requirements.

1. Open your working copy, CharlesRiver_Work.xlsx, and examine the Item worksheet from the Master Data group. Identify any items where the StandardCost field is zero, blank, or appears unreasonable. Assess whether these entries represent data quality problems or legitimate situations, such as service items that have a StandardCost of zero by design.

2. Identify items with a blank ListPrice and use the ItemGroup column to determine whether each is an item Charles River sells. Then identify item names that appear more than once in the Item table and compare the ItemCode and StandardCost of each. Assess whether these are duplicate records or distinct items that share a name, and explain how shared names could distort a cost analysis that groups results by ItemName.

3. Examine the WorkOrder and WorkOrderOperation worksheets from the Manufacturing group. Every work order should have at least one routing operation. Use COUNTIFS to count the WorkOrderOperation rows for each WorkOrderID and identify any work orders with none. Explain why a work order without operations would make labor and overhead cost figures unreliable.

4. Write a brief assessment (one half to one page) of the data’s suitability for product cost analysis, identifying any problems that would need to be resolved before proceeding.

Deliverable. A written data quality assessment focused on cost analysis readiness, with specific item-level and work-order-level findings.

Exercise 2.4: Evaluating Budget Data for Budget-Versus-Actual Analysis

Table group: Organizational Planning, with GLEntry from the Accounting Core

Scenario. You are a management accountant at Charles River who needs to compare budgeted amounts to actual spending by cost center. Before building the comparison, you need to understand how the budget data is structured and whether it can be matched to the general ledger.

Requirements.

1. Open your working copy, CharlesRiver_Work.xlsx, and examine the Budget and BudgetLine worksheets from the Organizational Planning group. Determine whether the budget data follows tidy data principles (each variable in its own column, each observation in its own row), describe what one row of each table represents, and explain whether the amounts of the two tables can be added together.

2. Department managers often ask for budget reports with months across the top. Sketch the layout of such a report for one cost center. Explain which tidy data principles it would violate and why the tidy form stored in the workbook is better suited to a budget-versus-actual analysis.

3. Examine the CostCenterID column in BudgetLine and in GLEntry. Count the rows in each table where CostCenterID is blank. Assess how these rows would affect a comparison of budgeted and actual amounts by cost center, and recommend how they should be handled.

4. Document your findings in a brief memorandum (one half to one page) that recommends specific data preparation steps needed before a budget-versus-actual analysis can be performed.

Deliverable. A written memorandum describing the structure of the budget data, the treatment of rows without a cost center, and the preparation steps needed before a budget-versus-actual analysis.

2.15.3 Auditing Exercises

Exercise 2.5: Data Quality Evaluation for Audit Planning

Table group: Order-to-Cash, Master Data, Accounting Core, and Procure-to-Pay

Scenario. You are a first-year auditor assigned to evaluate the data that will be used for analytics procedures during the upcoming Charles River audit. Your senior has asked you to perform a preliminary data quality assessment across several table groups to determine which data sources are reliable enough for audit testing.

Requirements.

1. Working in your copy, CharlesRiver_Work.xlsx, start with the Order-to-Cash group: test for missing values in critical fields of the SalesOrder table (OrderDate, CustomerID, Status), and test for sales invoices dated before the earliest shipment on the same sales order. Document the number of records affected by each problem.

2. For the Master Data group, check the Item table for blank ListPrice values and determine which of them are expected.

3. For the Accounting Core group, verify that total debits equal total credits in GLEntry, check for GLEntry records that reference an AccountID not found in the Account table, identify rows with neither a debit nor a credit amount, and examine the SourceDocumentType column for any unexpected or missing source types.

4. For the Procure-to-Pay group, examine the PurchaseRequisition table and filter the ApprovedByEmployeeID column for blank cells. Look at the Status of those requisitions and document how many were converted to purchase orders even though no approver is recorded.

5. For each problem identified, classify it by quality dimension and assess its potential impact on audit procedures. An invoice dated before its shipment, for example, would matter for testing revenue cutoff. Prepare a one-page audit data quality assessment memorandum that your senior could use to decide which tables are suitable for audit analytics.

Deliverable. A one-page audit data quality assessment memorandum organized by table group and quality dimension.

Exercise 2.6: Testing for Duplicate Payments

Table group: Procure-to-Pay

Scenario. Your audit team has identified duplicate payments as a risk area for the current Charles River engagement. Before performing a full duplicate payment analysis, which you will learn in the SQL chapters of Part III, you have been asked to perform a preliminary check using Excel.

Requirements.

1. Open your working copy, CharlesRiver_Work.xlsx, and examine the PurchaseInvoice and DisbursementPayment worksheets from the P2P table group. Identify the fields that would be relevant for detecting duplicate invoices and duplicate payments, such as SupplierID, InvoiceNumber, CheckNumber, Amount, and PaymentDate.

2. In the PurchaseInvoice worksheet, use COUNTIFS to count how many times each combination of SupplierID and InvoiceNumber appears, and filter for counts greater than one. In the DisbursementPayment worksheet, do the same for SupplierID and CheckNumber, noting that payments made by ACH or wire transfer have no check number and cannot be tested this way. Record each matching pair you find.

3. For each pair, compare the two records: their dates, their amounts, and, for the payments, the purchase invoices they paid. Decide whether each pair looks like the same obligation paid twice, a reference number reused on two different transactions, or a question the records cannot yet settle, and describe the evidence you would request from the supplier or the accounts payable team to settle it.

4. A duplicate payment does not always share a reference number, so add a test at the level of each invoice. In the PurchaseInvoice worksheet, use SUMIFS to total the payments recorded against each invoice in the DisbursementPayment worksheet, matching on PurchaseInvoiceID, and compare that total with the invoice’s GrandTotal. Round both to the cent with ROUND before comparing them, because a sum of many amounts can differ from the total by a tiny fraction of a cent. Many invoices are paid in installments, which is normal. Identify any invoice whose payments exceed its GrandTotal, and explain what a clean result does and does not prove.

5. Assess the reliability of your approach. Consider what duplicates your tests would miss, such as the same invoice entered with a slightly different number or paid by a method that has no check number. Write two to three sentences explaining why a SQL-based approach, which you will learn in Part III, would be more effective for this task.

6. Prepare a brief memorandum (one half page, with the pairs listed in an attached schedule) documenting the potential duplicates identified, your conclusion about each pair, the result of the overpayment test, and your assessment of the methods’ limitations.

Deliverable. A written memorandum documenting potential duplicate invoices and payments and the limitations of the detection methods used.

2.16 Further Reading

American Institute of Certified Public Accountants. (2026). Guide to audit data analytics. https://www.aicpa-cima.com/cpe-learning/publication/guide-to-audit-data-analytics This professional guide explains how auditors can apply data analytics in risk assessment procedures, substantive analytical procedures, and tests of details. The current edition reflects recent auditing standards, including SAS No. 142 on audit evidence, and includes considerations for assessing the reliability of data. Those considerations align with the quality dimensions covered in this chapter and support the audit-oriented exercises throughout the book.

Bochkay, K., Brown, S. V., Leone, A. J., & Tucker, J. W. (2023). Textual analysis in accounting: What’s next? Contemporary Accounting Research, 40(2), 765–805. https://doi.org/10.1111/1911-3846.12825 This review surveys how accounting research applies natural language processing to extract information from corporate disclosures and other text, comparing conventional approaches with newer machine learning and deep learning methods. It provides useful context for the brief discussion of unstructured data in this chapter and is a starting point for reading further on textual analysis.

Bovee, M., Srivastava, R. P., & Mak, B. (2003). A conceptual framework and belief-function approach to assessing overall information quality. International Journal of Intelligent Systems, 18(1), 51–74. https://doi.org/10.1002/int.10074 This paper presents a formal framework for assessing information quality that extends the foundational work of Wang and Strong. The paper’s approach to evaluating quality dimensions is relevant to the data quality assessment exercises in this chapter, particularly if you are interested in the theoretical underpinnings of data quality measurement.

Dai, J., & Vasarhelyi, M. A. (2017). Toward blockchain-based accounting and assurance. Journal of Information Systems, 31(3), 5–21. https://doi.org/10.2308/isys-51804 While this paper focuses primarily on blockchain technology, it includes a useful discussion of how data integrity and quality assurance are handled in current accounting information systems versus emerging distributed ledger approaches. It provides forward-looking context that connects the data quality concepts in this chapter to distributed ledger technology, which this book does not cover.

Jans, M., Alles, M. G., & Vasarhelyi, M. A. (2014). A field study on the use of process mining of event logs as an analytical procedure in auditing. The Accounting Review, 89(5), 1751–1773. https://doi.org/10.2308/accr-50807 Using procurement data from a large bank, this field study shows how analyzing the event logs that a system records can reveal payments made without approval and violations of segregation of duties that conventional audit procedures missed. It illustrates the chapter’s argument that examining system data directly can surface problems that standard reports do not show.

Redman, T. C. (2001). Data quality: The field guide. Digital Press. This practitioner-oriented book provides a comprehensive treatment of data quality concepts, assessment methods, and improvement strategies. Although it addresses data quality broadly rather than in an accounting-specific context, the practical techniques it describes for identifying and resolving data quality problems are directly applicable to the exercises in this chapter and throughout the book.

Wang, R. Y., & Strong, D. M. (1996). Beyond accuracy: What data quality means to data consumers. Journal of Management Information Systems, 12(4), 5–33. https://doi.org/10.1080/07421222.1996.11518099 This paper established the foundational framework for understanding data quality as a multidimensional concept defined by the needs of data users. The chapter adapts four of its fifteen dimensions as practical checks (accuracy, completeness, consistency, and timeliness), and the paper remains essential reading for anyone who works with data quality assessment in any professional context.

Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. https://doi.org/10.18637/jss.v059.i10 This paper formalized the principles of tidy data that are discussed in this chapter. Although the examples use the R programming language, the structural principles apply to any data analysis environment, including Excel, SQL, and Power BI. The paper’s clear explanation of why each variable should occupy its own column and each observation its own row is directly relevant to the data preparation challenges you will encounter throughout this book.

2.17 References

American Institute of Certified Public Accountants. (2020). Audit evidence (Statement on Auditing Standards No. 142). https://www.aicpa-cima.com/resources/download/aicpa-statement-on-auditing-standards-no-142

American Institute of Certified Public Accountants. (2026). Guide to audit data analytics. https://www.aicpa-cima.com/cpe-learning/publication/guide-to-audit-data-analytics

Azeroual, O., & Jha, M. (2021). Without data quality, there is no data migration. Big Data and Cognitive Computing, 5(2), Article 24. https://doi.org/10.3390/bdcc5020024

Bochkay, K., Brown, S. V., Leone, A. J., & Tucker, J. W. (2023). Textual analysis in accounting: What’s next? Contemporary Accounting Research, 40(2), 765–805. https://doi.org/10.1111/1911-3846.12825

Cai, L., & Zhu, Y. (2015). The challenges of data quality and data quality assessment in the big data era. Data Science Journal, 14, Article 2. https://doi.org/10.5334/dsj-2015-002

Cao, L., & Zhu, H. (2013). Normal accidents: Data quality problems in ERP-enabled manufacturing. Journal of Data and Information Quality, 4(3), 1–26. https://doi.org/10.1145/2458517.2458519

Holmes, A. F., & Douglass, A. (2022). Artificial intelligence: Reshaping the accounting profession and the disruption to accounting education. Journal of Emerging Technologies in Accounting, 19(1), 53–68. https://doi.org/10.2308/JETA-2020-054

Jans, M., Alles, M. G., & Vasarhelyi, M. A. (2014). A field study on the use of process mining of event logs as an analytical procedure in auditing. The Accounting Review, 89(5), 1751–1773. https://doi.org/10.2308/accr-50807

Wang, R. Y., & Strong, D. M. (1996). Beyond accuracy: What data quality means to data consumers. Journal of Management Information Systems, 12(4), 5–33. https://doi.org/10.1080/07421222.1996.11518099

Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. https://doi.org/10.18637/jss.v059.i10

Yan, Z., & Moffitt, K. C. (2019). Contract analytics in auditing. Accounting Horizons, 33(3), 111–126. https://doi.org/10.2308/acch-52457