Chapter 2: Understanding Data in Accounting

Accounting Analytics: An Integrated Approach

In this chapter

Part I of V: Foundations of Accounting Analytics

Chapter 2 of 3 in this Part

• 2.1 Why Data Quality Comes First
• 2.2 Data Types in Accounting
• 2.3 Sources of Accounting Data
• 2.4 Data Quality Dimensions

• 2.5 Common Data Quality Problems in Accounting Datasets
• Guided Tutorial 2.1
• 2.7 The Concept of Tidy Data
• 2.8 Source-to-Ledger Traceability
• Guided Tutorial 2.2
• 2.10 Data Quality Across the Charles River Table Groups
• 2.11 Looking Ahead

This chapter asks whether accounting data is fit for analysis before any analysis begins. The modules follow the chapter’s sections, with two guided tutorials that test real Charles River tables in Excel.

Learning objectives

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.

The first three objectives give the vocabulary: types of data, where accounting data comes from, and the four quality dimensions.

Learning objectives (continued)

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.

The last three objectives put the vocabulary to work: finding problems in the data, organizing it tidily, and using traceability to test the ledger.

Is the data reliable enough to analyze?

• You are a newly hired internal auditor at Charles River
• Before the year-end audit, your supervisor asks for an assessment of the accounting data
• Two customers share a name; hundreds of shipments have no tracking number; old shipments still read “In Transit”
• Some ledger entries carry no amount, and the extract runs into a new fiscal year

None of these observations proves an error yet, and that is the point of the chapter. Ask students which of them worries them most, and why, before any test is run.

2.1 Why Data Quality Comes First

Every result is only as good as its data

• A PivotTable on revenue with missing dates understates period totals
• A SQL test for duplicate payments misses duplicates if vendor names differ
• A budget dashboard misleads when budget and actual use different accounts
• Financial statement auditors must evaluate the data they use as evidence

No technique, however sophisticated, can repair the data it runs on. For accountants, data quality is a professional responsibility. The AICPA’s standards for audits of financial statements require it for audit evidence, and anyone presenting numbers is vouching for them. Wang and Strong showed that data quality can be measured along defined dimensions.

2.2 Data Types in Accounting

Quantitative data measures and counts

Table 2.1 · Examples of Quantitative and Qualitative Data in Accounting

Subtype	Accounting Example	Charles River Source
Continuous	Sales invoice line amount	SalesInvoiceLine.LineTotal (O2C group)
Continuous	Standard cost of a product	Item.StandardCost (Master Data group)
Continuous	Freight cost on a shipment	Shipment.FreightCost (O2C group)
Discrete	Number of line items on a sales invoice	Count of SalesInvoiceLine rows per invoice (O2C group)
Discrete	Number of GLEntry rows posted in a fiscal period	GLEntry (Accounting Core group)

Quantitative values support arithmetic. Continuous values can take any amount, such as a line total; discrete values are counts, such as entries posted in a period. An amount stored as text, because of a currency symbol or a comma, cannot be added until it is converted.

Qualitative data names, classifies and orders

Table 2.1 · Examples of Quantitative and Qualitative Data in Accounting

Subtype	Accounting Example	Charles River Source
Nominal	Account name (“Office Supplies Expense”)	Account.AccountName (Accounting Core group)
Nominal	Customer name (“Walters PLC”)	Customer.CustomerName (O2C group)
Nominal	Item group classification (“Furniture”)	Item.ItemGroup (Master Data group)
Nominal	Source document type (“SalesInvoice”)	GLEntry.SourceDocumentType (Accounting Core group)
Nominal	Sales order status (Open, Backordered, Partially Invoiced, Invoiced, Closed)	SalesOrder.Status (O2C group)
Ordinal	Supplier risk rating (Low, Medium, High)	Supplier.SupplierRiskRating (P2P group)

Qualitative values are categories and labels. Nominal categories have no order, such as account names or a sales order’s status. Ordinal categories do, such as a supplier risk rating. You can count them, but you cannot average them, and the same goes for an ID stored as a number.

Structure runs from tables to free text

Figure 2.1 · The Spectrum of Data Structure

Structured data sits in rows and columns with defined types, like every Charles River table. Semi-structured data, such as an XBRL filing, has tags but no table; unstructured data, such as contracts or scanned invoices, needs other tools. The ledgers and transaction records this book analyzes are structured, and structured skills are the foundation for the rest.

Check your understanding

What type of data is each value?

1. The total of a sales invoice line
2. An item’s group, such as Furniture
3. A sales order’s status, such as Open or Backordered
4. The number of ledger entries posted in a month

Ask for two labels per value: quantitative or qualitative, then the subtype.

Each value has its type

1. Quantitative, continuous: an amount
2. Qualitative, nominal: a category with no order
3. Qualitative, nominal: status categories with no single defined ranking
4. Quantitative, discrete: a count

The type decides which analysis makes sense: you can total line amounts and count statuses, but an average status or a sum of item groups means nothing.

2.3 Sources of Accounting Data

Accounting data comes from five kinds of source

Figure 2.2 · Common Sources of Accounting Data

Knowing where data comes from tells you what to expect of it. Each source has its own strengths and its own typical problems, which shape the quality checks you run.

Sub-ledgers must agree with the ledger

• The general ledger records every debit and credit; at Charles River, the GLEntry table
• Sub-ledgers hold the detail: the O2C tables for receivables, the P2P tables for payables
• A trial balance is a report that sums the ledger by account
• ERP data is integrated but not immune to entry, setup and validation errors; external data gives you less control

When a sub-ledger and the ledger disagree, the difference is investigated. A documented timing or scope difference is not an error, while an unexplained one may point to an error or a control problem. In one field study, analyzing a bank’s procurement event logs revealed unapproved payments and segregation-of-duties violations that conventional procedures had missed.

2.4 Data Quality Dimensions

Accuracy and completeness at Charles River

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.

Accuracy asks whether a value is true; completeness asks whether every record and value is there. Auditors test completeness closely, because missing data can understate liabilities or overstate assets, by design or by accident.

Consistency and timeliness at Charles River

Table 2.2 · The Four Data Quality Dimensions with Accounting Examples

Dimension	Definition	Charles River Example
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.

Consistency problems make results silently wrong rather than obviously wrong, as when one customer appears under three names. Timeliness depends on the question: data current enough for a monthly report may not be for a daily cash position.

Watch out: one dimension can pass while another fails

• A ledger can be accurate and consistent yet incomplete, if a subsidiary was not consolidated
• Test all four dimensions, not only the easiest one to check

A clean result on one dimension says nothing about the others. The checklist later in this module assigns tests to each dimension so none is skipped.

2.5 Common Data Quality Problems in Accounting Datasets

Four problems recur in accounting data

Figure 2.3 · Common Data Quality Problems

Each panel uses real Charles River records: blank tracking numbers, a supplier invoice number used twice, shipments still marked In Transit years after their delivery date, and an unusually large freight cost. Tutorial 2.1 practices these problem types, with same-name customers for the duplicate screen, and Exercise 2.6 investigates the repeated supplier invoice number shown here.

Blanks and outliers call for judgment

• Some tools skip missing values and others treat them as zero; both distort totals
• Not every blank is an error: items that are never sold have no list price by design
• A blank tracking number, by contrast, is information that should exist
• An outlier is a question to investigate, not a value to delete

Deciding whether a value is missing by design or missing by error is part of every assessment. The highest Charles River freight costs are several times the average, yet nothing in the data marks them as errors.

Matching across records and tables

• Duplicates: a supplier invoice number used twice; customers with identical names
• Inconsistent formats: dates, names and amounts written differently break lookups and joins
• Referential integrity: every reference that is filled in must point to a record that exists
• A record whose parent is missing is an orphaned record

Whether a same-name pair is one customer or two can only be decided by comparing the rest of each record, such as the city and segment. Charles River stores every date in one format, but data combined from other systems often does not.

Check your understanding

Name the problem and the quality dimension of each finding:

1. Shipments with no tracking number
2. Shipments still “In Transit” after their delivery date
3. One supplier’s invoice number appearing on two invoices
4. Raw materials with no list price

Give students a minute; ask them to note which finding might not be an error at all.

Problems, dimensions and judgment

1. Missing values: completeness
2. If DeliveryDate is the delivery, fields that disagree: consistency, and timeliness
3. A possible duplicate: an accuracy test, and a duplicate-payment risk
4. Missing by design: not an error, because raw materials are not sold

The last finding is the trap: a blank is a problem only when the information should exist. The second depends on what DeliveryDate records. If it is only the expected date, the shipments are overdue, not contradictory, so confirm the meaning first. Recording why a finding is not an error is as useful as recording an error.

Tests for accuracy and completeness

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

The chapter’s checklist assigns tests to each dimension, with the Excel technique for each. A test that finds no problem is also a finding, because it tells you which tested relationships held in the population you tested.

Tests for consistency and timeliness

Table 2.3 · A Data Quality Checklist

Dimension	Common Tests	Excel Techniques
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

Consistency tests compare fields and tables with each other; the referential integrity test checks every reference against its parent table. Timeliness tests read the date range of the extract and its partial periods.

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

Inspect the O2C and Item tables in eight steps

1. Get to know the SalesOrder worksheet
2. Find shipments with no tracking number
3. Find shipments still marked In Transit after their delivery date
4. Look for duplicate customers
5. Look for freight outliers
6. Find the earliest shipment for each invoice
7. Flag invoices dated before their first shipment
8. Distinguish values missing by design from missing information

Students first copy CharlesRiver.xlsx to CharlesRiver_Work.xlsx and work in the copy for the rest of Part I, so the download stays as provided. Each step applies one test from the checklist; the book has every click.

MINIFS finds each invoice’s first shipment

Figure 2.5 · The SalesInvoice Worksheet with Date Check Helper Columns

Because an order can ship in parts, each invoice is compared with the earliest shipment on its order. A handful of invoices are dated before any shipment, a potential accuracy problem that also matters for revenue cutoff; the No shipment rows are design services, which never ship.

Checkpoint

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

The first three findings need follow-up evidence. The last three need judgment, and one of them turns out to be expected. The In Transit rows conflict with their dates only if DeliveryDate records the actual delivery. Students should list the table, column, problem type, count and conclusion for each.

2.7 The Concept of Tidy Data

Tidy data has one fact in one place

• Each variable in its own column
• Each observation in its own row
• Each type of observational unit in its own table
• Common violations: several values packed into one column; months as column headers

Wickham formalized these principles. A column such as “4100-Sales-Domestic” hides three variables, and a budget with months across the top stores a variable in its headers.

Wide reports reshape into long tables

Figure 2.6 · Messy Versus Tidy Data

The wide layout is easy to read but harder to analyze; PivotTables, SQL grouping and Power BI models work most easily with the long layout, where a new month is a new row. Charles River’s budget tables are already long; the reports managers read are usually wide, and Power Query in Part II moves between the two.

Watch out: tidy is not clean

• Tidy describes structure; clean describes quality
• Data can be tidy but dirty, or clean but messy
• Both must be fixed before analysis can proceed

Relational tables like Charles River’s are tidy by design, which is why the next chapter’s database concepts will feel familiar. Tidiness says nothing about errors inside the values.

2.8 Source-to-Ledger Traceability

Traceability tests completeness and accuracy at once

• Completeness: every recorded sales invoice produced its ledger postings
• Accuracy: the amounts posted for each invoice agree with its total
• At Charles River, every sales invoice reconciles to its postings
• Reconciling a sub-ledger to the ledger is a common audit test

Source-to-ledger traceability. The ability to trace a general ledger entry back to the business document that created it. In the Charles River database, the SourceDocumentType, SourceDocumentID, and SourceLineID columns in GLEntry provide this traceability.

The test starts from the invoices, so it cannot show that every sale was invoiced; that takes a population from outside the invoices, such as the shipments. In the audit chapters, the same source column separates the accounting team’s journal entries from postings generated by invoices and payroll.

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

Test the Accounting Core in eight steps

1. Get to know the chart of accounts
2. Check the chart of accounts for completeness
3. Get to know the general ledger
4. Check that total debits equal total credits
5. Check that every ledger account exists in the chart of accounts
6. Check the periods the ledger covers
7. Look for entries with no amount
8. Check that every business cycle posts to the ledger

Students continue in their working copy. The ledger fills most of an Excel worksheet, so formulas copied down every row slow Excel down; summarizing first with a PivotTable keeps it responsive, and Part III turns to SQL when data outgrows Excel.

Debits equal credits across the whole ledger

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

Totals at the bottom of the GLEntry worksheet test consistency at the aggregate level, starting from A2 so Ctrl+Down reaches the last row, with one blank row so the Table does not grow. Rounded to the cent, the difference is zero, a clean result worth recording, just like the referential integrity test of the next step. Equal totals cannot reveal an entry left out with both its sides, or errors that offset.

Checkpoint

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

The small periods sit at the edges of the extract. The first month belongs to the first fiscal year and stays in its analysis, while the latest fiscal year holds only supplier payments that settle prior-year invoices, which stay out of the annual totals of the years under review. The zero-amount rows all come from one source document type.

2.10 Data Quality Across the Charles River Table Groups

The business cycles carry the transaction problems

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

Table Group	Issues to Watch For
Order-to-Cash (O2C)	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)	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	Work orders with no routing operations
Payroll and Time	Missing clock-outs, duplicate time-clock days, employees paid without time records, payroll registers without a payment
Fixed Assets and Financing	Asset events and debt schedules that must agree with the ledger

Transaction tables accumulate records every day, so most quality problems appear here, along with control problems such as the same person creating and approving a document. The book’s table also names the real-world system each group stands for.

The ledger, reference data and plans

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

Table Group	Issues to Watch For
Accounting Core	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
Master Data	Item names shared by several items, missing catalog attributes, list prices left blank for items that are not sold
Organizational Planning	Budget lines and ledger entries with no cost center
Demand Planning and MRP	Forecast overrides far outside the normal range

Reference and planning tables change rarely, so their problems are gaps and duplicates. The stakes are highest in the Accounting Core, because errors in the ledger flow directly into the financial statements.

Check your understanding

Which table group would you test for each risk?

1. A supplier paid twice for one invoice
2. An employee paid without time records
3. A budget line with no cost center
4. A journal entry created and approved by the same person

Students can use the two previous slides. Ask them to name the test they would run as well as the group.

Each risk has its table group

1. Procure-to-Pay: duplicate supplier invoice numbers and payment references
2. Payroll and Time: employees paid without time records
3. Organizational Planning: budget lines with no cost center
4. Accounting Core: entries created and approved by the same person

Many of these issues go beyond data quality into internal control, and the audit chapters of Parts II and III test them across whole populations.

2.11 Looking Ahead

Summary

• Data types decide which analysis makes sense; structured data is what the tools expect
• Ledgers, sub-ledgers, ERP systems and external feeds each bring their own risks
• Accuracy, completeness, consistency and timeliness are tested one by one
• Missing values, duplicates, inconsistencies and outliers call for tests and judgment
• Tidy structure and traceability make the data ready to analyze and to trust

Return to the opening scenario: each observation there now has a dimension, a test and a conclusion. Ask students which objective they feel least sure about before the exercises.

Key terms

Accuracy · Completeness · Consistency · Data quality · Duplicate record · External data · General ledger · Messy data · Missing value · Orphaned record · Outlier · Qualitative data · Quantitative data · Referential integrity · Semi-structured data · Structured data · Sub-ledger · Tidy data · Timeliness · Trial balance · Unstructured data

Every term is defined at the end of the chapter. Referential integrity and orphaned record return in Chapter 3, where keys make them precise.

Practice and next steps

• Financial Accounting
  • Exercise 2.1: Assessing Revenue Data Quality in the Charles River O2C Tables
  • Exercise 2.2: Evaluating General Ledger Completeness at Charles River
• Managerial Accounting
  • Exercise 2.3: Data Quality Assessment for Product Cost Analysis
  • Exercise 2.4: Evaluating Budget Data for Budget-Versus-Actual Analysis
• Auditing
  • Exercise 2.5: Data Quality Evaluation for Audit Planning
  • Exercise 2.6: Testing for Duplicate Payments

Next: Chapter 3: The Accounting Data Environment

Assign one exercise from each perspective; Exercise 2.6 tests for duplicate payments. The next chapter examines the tables, keys and relationships that connect the Charles River data.

About these slides

These slides accompany Accounting Analytics: An Integrated Approach, licensed under CC BY-SA 4.0. Every figure and table comes from the chapter:

https://aa.accountinganalyticshub.com/chapters/02-understanding-data/chapter.html

Students can find each figure and table, with the full explanation, in the online chapter.