1  The Role of Analytics in Accounting

Learning Objectives

After completing this chapter, you will be able to:

  1. Explain how growth in data, new technology, and changing professional expectations have reshaped the work accountants perform.
  2. Distinguish descriptive, diagnostic, predictive, and prescriptive analytics and identify examples of each in financial accounting, managerial accounting, and auditing.
  3. Describe the six stages of the accounting analytics workflow, from defining the question through communicating results.
  4. Compare Excel, SQL, and Power BI and identify the workflow stages each tool supports best.
  5. Describe the Charles River business, its table groups, and how operational activity reaches the general ledger.
Chapter slides View slides Download PowerPoint

Opening Scenario

You have just started a position as a staff accountant at Charles River, a mid-size home furnishings company in the greater Boston area. During your first week, the controller asks you to investigate why gross margins on the Furniture product line declined in the most recent quarter. She tells you the data you need is spread across several tables in the company’s database, including sales orders, product cost records, and the general ledger. Your accounting coursework prepared you to interpret financial statements, calculate ratios, and apply standards, but you have never pulled data directly from a database, cleaned it for analysis, or built an interactive report that the management team can explore on their own. You realize that answering the controller’s question will require more than accounting knowledge. It will require the ability to extract, prepare, analyze, and communicate data. This chapter introduces the analytical mindset and the toolkit you will develop throughout this book to handle exactly this kind of challenge.

1.1 The Changing Landscape of Accounting Work

The accounting profession has always been built on data. Accountants record transactions, classify them into accounts, summarize them in financial statements, and interpret the results for stakeholders. For most of the profession’s history, the volume of data involved in these tasks was manageable through manual methods and later through basic spreadsheets. A general ledger might contain a few thousand entries per year, and an auditor could review a reasonable portion of those entries by hand. That era has ended.

Modern organizations generate transaction data at a scale that makes manual review impractical. A single retail company may process millions of sales transactions per month, each one recorded with a timestamp, a product identifier, a customer identifier, a store location, a payment method, and a discount code. An enterprise resource planning system at a manufacturing firm captures not only financial transactions but also production orders, material movements, quality inspections, and employee time records. The data that accountants must work with has grown by orders of magnitude, and it continues to grow (Vasarhelyi et al., 2015).

This growth in data has created both a challenge and an opportunity for the profession. The challenge is that traditional methods of sampling and manual review cannot keep pace with the volume and complexity of modern datasets. An auditor who selects 50 transactions from a population of 500,000 is examining one hundredth of one percent of the transactions. The opportunity is that the same technology that generates massive datasets also provides tools to analyze entire populations rather than small samples. A test applied to every recorded transaction removes the risk that a sample misses an exception the test is designed to find. It does not make up for a test that asks the wrong question, for records that are incomplete or not genuine, or for results that are misread, questions the later chapters return to. Accountants who can use these tools effectively can test whole populations, identify patterns that a sample might miss, and deliver insights that go beyond compliance and reporting (Barr-Pulliam et al., 2022; Earley, 2015).

Automation is also changing the work itself. Software now performs many routine tasks, such as matching invoices to purchase orders and preparing standard reconciliations, and artificial intelligence tools, including generative AI, can draft analyses and flag unusual transactions in seconds. These tools have not removed the need for accountants. They shift the accountant’s contribution toward framing the right questions, judging whether the underlying data is reliable, and exercising the professional judgment that software cannot supply (Kokina et al., 2025; Law & Shen, 2025). Early field evidence suggests how this division of labor can work. A study that combined a survey of professional accountants with data from an AI-enabled accounting platform for small and medium-sized businesses found greater use of generative AI associated with less of the accountants’ time on routine data entry and more on client communication and quality review. On the platform, the accountants corrected the AI’s classifications more often when the AI was less confident in them, and in a separate experiment, AI assistance improved classification accuracy on average, but relying on incorrect AI suggestions could increase the risk of error (Choi & Xie, 2026). An accountant who understands how data is structured and analyzed is far better placed to use these tools well and to recognize when their output is wrong. The further reading at the end of this chapter follows these developments.

Professional bodies have recognized this shift and updated the competencies they expect. The American Institute of Certified Public Accountants (AICPA) and the National Association of State Boards of Accountancy (NASBA) added accounting data analytics, digital acumen, and IT governance to the model curriculum that prepares aspiring CPAs for the CPA Exam (AICPA & NASBA, 2021). The Institute of Management Accountants (IMA) expanded its competency framework in 2025, with domains titled Data Analytics and Insights and Emerging Technologies in Accounting (IMA, 2025). Internationally, the International Education Standards, now issued by the International Federation of Accountants (IFAC), list information and communications technologies among the areas of technical competence that aspiring professional accountants should develop by the end of their initial professional development, in a revision effective from July 1, 2026 (IFAC, 2025). These developments signal that analytics is not a niche specialization within accounting. It is a core competency that every practitioner will need.

TipIn Practice

Analytics and technology skills remain difficult for finance organizations to develop and recruit. In a 2025 survey by the AICPA and the Chartered Institute of Management Accountants (CIMA), half of the 1,446 finance and accounting leaders and managers who responded worldwide named a lack of human capital, skills, and talent as the leading barrier to adopting new technology. Respondents also reported skills gaps in data and analytics; communication, influencing, and critical thinking; and business partnering (Stroe & Teneva, 2025). An analysis of Australian job listings that combined accounting and data analytics tasks found that employers wanted people who could extract insights from data, solve complex business problems, communicate and visualize findings, and use tools such as Excel, SQL, and Power BI (Askary & Askarany, 2024). Together, these findings suggest that accounting professionals increasingly need to combine traditional accounting expertise with analytical, technological, and communication skills.

The purpose of this book is to help you develop these skills in a structured and practical way. You will learn to work with data using Microsoft Excel, SQL, and Microsoft Power BI, three tools that are widely used in practice. You will apply these tools to realistic accounting data drawn from the Charles River Accounting Dataset, a single integrated database that you will use throughout the entire book. And you will practice solving problems from the perspectives of financial accounting, managerial accounting, and auditing. By the end of this book, you will be able to extract data from a database, prepare it for analysis, summarize and model it, visualize the results, and communicate your findings to professional audiences.

1.2 Defining Accounting Analytics

Analytics is the process of examining data to draw conclusions, identify patterns, and support decisions. In accounting, analytics applies this process to financial and operational data for purposes such as reporting, planning, control, and assurance. In this book, accounting analytics means extracting, preparing, analyzing, and visualizing accounting data to produce information that supports financial reporting, managerial decisions, and audit assurance.

Accounting analytics overlaps with several related terms you are likely to encounter. “Data analytics” is a broad term that encompasses analytical work in any domain, from marketing to healthcare to logistics. “Business intelligence” typically refers to the tools and infrastructure that organizations use to collect, store, and present data for operational and strategic decisions. “Big data” refers to datasets that are too large or complex for traditional data processing methods and is usually associated with cloud data platforms and distributed processing. Accounting analytics draws on all of these concepts but applies them specifically to the data, questions, and standards that define the accounting profession (Richins et al., 2017).

1.2.1 Types of Analytics

A useful framework for understanding the scope of analytics divides it into four types: descriptive, diagnostic, predictive, and prescriptive. Each type answers a different question, and together they form a continuum of increasing analytical complexity, as Figure 1.1 shows. The types are not mutually exclusive. Most real-world accounting analytics projects involve more than one.

A horizontal diagram showing four stages from left to right: Descriptive Analytics (What happened?), Diagnostic Analytics (Why did it happen?), Predictive Analytics (What might happen?), and Prescriptive Analytics (What should we do?). An arrow beneath them indicates increasing analytical complexity, and each stage has an accounting example: revenue summary by quarter, drill-down of a margin decline, regression forecast of sales, and optimal product mix via Solver.
Figure 1.1: The Analytics Continuum.

Descriptive analytics answers the question “what happened?” It involves summarizing historical data to understand past performance, identify trends, and describe the current state of affairs. Descriptive analytics is the foundation of most accounting work. When a financial analyst prepares a comparative income statement showing revenue by product line for the past four quarters, that is descriptive analytics. When an auditor stratifies accounts receivable by aging bucket to assess the adequacy of the allowance for doubtful accounts, that is also descriptive analytics. The tools of descriptive analytics include aggregation, grouping, sorting, filtering, and basic statistical measures such as totals, averages, and counts.

In this book, descriptive analytics appears most prominently in the Excel chapters of Part II and the introductory SQL chapters of Part III. You will build PivotTables that summarize Charles River invoiced revenue by item group and quarter, write SQL queries that calculate average order values by customer, and create Power BI dashboards that display key performance indicators from the general ledger. These are all descriptive tasks, and they form the base on which more advanced analysis is built.

Diagnostic analytics answers the question “why did it happen?” It picks up where descriptive analytics leaves off, investigating the causes behind a result by drilling down into the details, comparing segments, and breaking a change into its components. The controller’s question in the opening scenario is a diagnostic question. A descriptive summary can show that the gross margin on Furniture fell in the most recent quarter, but explaining why requires decomposing the decline into its drivers: lower selling prices, a shift in the mix of products sold, higher product costs, or some combination of the three. Diagnostic analytics is central to accounting work. Management accountants use it to explain variances from budget, and auditors use it to investigate unusual fluctuations identified during analytical procedures. The tools of diagnostic analytics include drill-down, variance decomposition, segment comparison, and tracing exceptions back to the transactions that caused them.

Diagnostic work appears throughout this book, wherever a summary raises a question that the underlying transactions can answer. In the Excel chapters, you will drill into PivotTables to locate the source of a change. In the SQL chapters, you will join tables to trace an unusual balance back to individual documents. In the Power BI chapters, you will build drill-through reports that let managers explore causes on their own.

Predictive analytics answers the question “what might happen?” It involves using historical data to make forecasts, estimate probabilities, and identify the factors that drive outcomes. Predictive analytics moves beyond explaining the past to estimating what is not yet known, most often the future. When a management accountant builds a regression model to forecast next quarter’s revenue based on historical trends, that is predictive analytics. When an auditor uses statistical analysis to set an expectation for an account balance and then compares the actual balance to the expectation as a substantive analytical procedure, that too involves prediction. The tools of predictive analytics include regression analysis, trend extrapolation, time series modeling, and classification techniques.

In this book, predictive analytics appears in the modeling work of the Excel chapters in Part II and in the expectations and forecasts of the SQL chapters in Part III and the Power BI chapters in Part IV. Those chapters also compute period-over-period comparisons, trailing twelve-month measures, and rolling averages, which describe past trends and become predictive only when an expectation is built on them. You will build forecasting models using Charles River sales data, test how sensitive a promotion’s payoff is to its assumptions with what-if tools, and set analytical expectations that mirror the procedures used in professional practice.

Prescriptive analytics answers the question “what should we do?” It involves using data analysis to recommend a course of action, often by optimizing an objective subject to constraints. Prescriptive analytics is the most advanced of the four types and is less common in current accounting practice than the others. When a cost accountant uses Excel’s Solver tool to determine the optimal product mix that maximizes contribution margin given production capacity constraints, that is prescriptive analytics. When a tax advisor uses scenario modeling to recommend a filing strategy that minimizes a client’s tax liability across multiple jurisdictions, that also has prescriptive elements.

This book introduces prescriptive analytics through optimization exercises in the Excel chapters of Part II, using Solver and Scenario Manager, and through the decisions that the capstone cases of Part V ask you to recommend. The emphasis throughout the book, however, is on descriptive, diagnostic, and predictive analytics because these represent the analytical work that accounting professionals perform most frequently. A review of management accounting research and an interview study in 20 Canadian organizations, for example, both find that management accountants’ analytics work remains largely descriptive (Appelbaum et al., 2017; Spraakman et al., 2021).

WarningWatch out

It is tempting to assume that descriptive analytics is “basic” and that the goal is to reach prescriptive analytics as quickly as possible. In practice, much accounting analytics work is descriptive, and doing it well requires substantial skill. A poorly constructed summary can mislead decision makers just as easily as a sophisticated model can. The value of analytics lies not in its complexity but in the quality of the questions it answers and the rigor of the process behind it.

1.3 Analytics Across Accounting Roles

The demand for analytics skills comes from every area of the profession, and the applied exercises in this book are organized around three of them: financial accounting, managerial accounting, and auditing. Seeing how each area uses data helps explain why the skills in this book matter for your career, whichever path you choose.

In financial reporting, analytics supports the close, reconciliation, and disclosure processes. Reporting teams extract general ledger detail to reconcile subsidiary ledgers to control accounts, analyze fluctuations in account balances before the financial statements are issued, and validate the data that flows from many interconnected tables into the statements. A survey of finance professionals found that finance functions pursuing efficiency make greater use of both automation and analytics when their firms emphasize digitalization (Bedford et al., 2025). On one AI-enabled platform serving small and medium-sized businesses, using generative AI to record and classify transactions was associated with more detailed ledgers and a faster month-end close (Choi & Xie, 2026). Accountants who can access and analyze the data directly, rather than waiting for other departments to produce reports, can answer questions as they arise.

In managerial accounting, the accountant’s role has expanded beyond preparer and verifier to include advisor and analyst. Organizations expect their accounting and finance teams to provide forward-looking analysis, scenario planning, and strategic insight, not just historical reports. Management accountants are asked to explain why results differ from plan, to forecast future performance under different assumptions, and to identify the operational drivers behind financial outcomes. These tasks require analytical skills that go beyond debits and credits (Appelbaum et al., 2017; Bedford et al., 2025). A survey of US firms found that broader data sources and a data-driven culture were associated with higher perceived decision quality only where management accountants’ data analytics skills were highly developed (Franke & Hiebl, 2023).

In auditing, analytics is changing the nature of audit evidence. Analyzing an entire population of transactions rather than a sample can help auditors identify items for investigation that a sample might miss, focus their risk assessments, and give audit committees more relevant findings. Auditing standards are adapting to this shift. In 2024, the Public Company Accounting Oversight Board (PCAOB) amended its standards on audit evidence and on responses to the risks of material misstatement to clarify the auditor’s responsibilities when using technology-assisted analysis, and the amendments took effect for audits under PCAOB standards of fiscal years beginning on or after December 15, 2025 (PCAOB, 2024). Internal auditors apply the same techniques to monitor controls and transactions continuously throughout the year.

Across all three areas, employers look for graduates who combine accounting knowledge with data skills. Studies of accounting job advertisements find employers asking for advanced spreadsheet skills and the ability to work with enterprise resource planning and cloud accounting systems, along with a growing demand for information and communication technology skills, especially in entry- and mid-level positions (Elo et al., 2026; Suarta et al., 2024). Audit firms report recruiting technology-savvy graduates and expect the auditors of the future to bring information technology and data analytics skills (Liew et al., 2022; Vitali & Giuliani, 2024). The ability to write a SQL query, build a PivotTable, or create an interactive dashboard can set you apart in a hiring process and open career paths that were once reserved for specialists in information systems or data science.

Table 1.1 shows how each of the four types of analytics appears in financial accounting, managerial accounting, and auditing, using examples drawn from the Charles River data you will analyze in this book.

Table 1.1: Examples of Analytics Types by Accounting Perspective
Type Financial Accounting Managerial Accounting Auditing
Descriptive Comparative income statement showing revenue by item group for the past four quarters Ranking of item groups by the margin of list price over standard cost, from the Item table Stratification of Charles River sales invoices by aging bucket to evaluate the allowance for doubtful accounts
Diagnostic Drill-down of a quarterly revenue shortfall by customer segment and item group to explain the change from the prior quarter Decomposition of the decline in Furniture gross margin into price, volume, mix, and cost effects Investigation of sales invoices dated before the goods were shipped to determine whether revenue was recorded in the wrong period
Predictive Revenue forecast for the next fiscal quarter using trend extrapolation of historical Charles River sales data Cost-volume-profit model projecting break-even points for the Furniture product line under three pricing scenarios Regression-based expectation for monthly revenue used as a substantive analytical procedure
Prescriptive Recommended sequence of period-end close tasks that minimizes the days needed to close, given staff availability Product mix recommendation that maximizes total contribution margin across the four product groups, subject to constraints on machine hours and raw material availability Allocation of substantive testing hours across audit areas that minimizes residual risk within a given engagement budget
TipIn Practice

The three-perspective exercise structure in this book reflects how analytics is used across the accounting profession. The same technique can serve different purposes depending on the role. A duplicate-payment analysis, for example, supports controls assurance in an audit context, vendor management in a procurement context, and cash flow review in a controllership context. Seeing these connections will help you understand why a single analytical skill has broad professional value.

1.4 The Accounting Analytics Workflow

Before learning specific tools, it is helpful to understand the general process that accounting analytics projects follow. Whether you are building a revenue summary in Excel, writing an audit query in SQL, or designing a dashboard in Power BI, the work progresses through the six stages shown in Figure 1.2. This book is organized around these stages, and the controller’s question from the opening scenario shows how they fit together.

A process flowchart showing six stages in sequence: (1) Define the Question, (2) Identify and Access the Data, (3) Prepare and Clean the Data, (4) Analyze the Data, (5) Visualize and Present the Results, (6) Communicate Findings and Recommendations. Each stage includes a one-sentence description, and a note indicates which parts of the book develop each stage.
Figure 1.2: The Accounting Analytics Workflow.

The first stage is defining the question. Every analytics project begins with a clear statement of the problem or question to be addressed. In the opening scenario, the controller asked a specific question: why did gross margins on the Furniture product line decline in the most recent quarter? A well-defined question guides every subsequent decision, from which data to collect to which visualizations to create. Vague questions produce vague answers. The ability to translate a business concern into an analytical question is one of the most important skills an accountant can develop.

The second stage is identifying and accessing the data. Once the question is defined, you must determine which data is needed and where it resides. In many organizations, the relevant data is stored in databases that are part of the company’s ERP system or accounting software, and accessing it may require writing SQL queries to extract the relevant tables and columns. In other cases, the data may be available in spreadsheet exports or flat files. To answer the controller’s question, you would need invoiced sales by product and quarter, the cost of the products sold, and the general ledger accounts where revenue and cost of goods sold are recorded. Chapters 2 and 3 prepare you for this stage by explaining how accounting data is structured and stored within the Charles River database.

The third stage is preparing and cleaning the data. Raw data almost always contains problems that must be resolved before analysis can begin. Missing values, duplicate records, inconsistent formatting, and incorrect data types are common in accounting datasets. Before calculating margins, for example, you would confirm that every invoice line is linked to a product and that returns and credit memos are treated consistently. Data preparation is often the most time-consuming stage of an analytics project, and it is also among the most important, because analysis performed on dirty data produces unreliable results. The Excel chapters of Part II and the SQL chapters of Part III both teach data preparation techniques.

The fourth stage is analyzing the data. This is where you apply techniques such as summarization, comparison, trend analysis, regression, and anomaly detection to answer the question defined in the first stage. The choice of technique depends on the question and the type of data available. For the controller, you would calculate gross margin by quarter for each item group and then decompose the Furniture decline into its volume, mix, price, and cost effects, which is diagnostic analysis. Analysis techniques are taught in Excel in Part II, in SQL in Part III, and in Power BI in Part IV.

The fifth stage is visualizing and presenting the results. Data visualization transforms analytical results into visual formats that audiences can interpret quickly and accurately. A well-designed chart, table, or dashboard communicates the answer to the original question more effectively than a spreadsheet full of numbers. In one experiment, managers who received graphs alongside tables made better performance evaluations than managers who received tables alone (Hirsch et al., 2015). A single chart showing Furniture margin by quarter, with the contribution of each driver highlighted, would let the controller see the answer at a glance. Part IV covers visualization principles and teaches you to build interactive reports and dashboards in Power BI.

The sixth stage is communicating findings and recommendations. The final product of an analytics project is not a spreadsheet or a dashboard but a communication to decision makers. That communication might take the form of a written memorandum to an audit committee, a presentation to management, or an interactive report that stakeholders can explore. In the opening scenario, it would be a short memorandum to the controller that explains why the Furniture margin fell and what management might do about it. The ability to translate analytical results into clear, professional language is essential. In a survey of accounting professionals, respondents ranked data interpretation and communication above any individual technical or statistical skill for new graduates (Brink & Stoel, 2019). You will practice this ability throughout the book in the applied exercises and comprehensive cases, each of which asks for a written interpretation alongside the technical work.

NoteConnecting the Dots

The six-stage workflow described here is not unique to accounting. It closely parallels the data analysis process described in data science and business intelligence literature (Provost & Fawcett, 2013). What makes accounting analytics distinctive is not the process itself but the data it works with (financial and operational transactions), the questions it addresses (reporting, control, assurance), and the professional standards that govern the work (GAAP, GAAS, IMA ethical standards). Throughout this book, the workflow provides the thread that connects every chapter and every tool.

1.5 The Tools of This Book

This book uses three tools: Microsoft Excel, SQL (Structured Query Language), and Microsoft Power BI. They were selected because they are widely used in accounting practice, they are accessible to readers without programming backgrounds, and together they cover the full analytics workflow from data access through communication. Each tool has distinct strengths, and part of becoming an effective analyst is knowing which tool fits which task.

Microsoft Excel is widely used for analysis in accounting. Its grid of rows and columns makes it natural for organizing data, building calculations, and preparing workpapers, and features such as PivotTables, lookup functions, Power Query, and Solver extend it well beyond simple arithmetic. Excel is strongest for ad hoc analysis, financial modeling, and work that a reviewer needs to trace cell by cell. It becomes harder to manage when data grows to hundreds of thousands of rows or spans many related tables.

SQL is the standard language for retrieving and combining data stored in relational databases. Rather than copying data into a spreadsheet, you write a query that describes the data you want, and the database returns it. SQL is strongest when data is large, when it spans multiple related tables that must be joined, and when a procedure needs to be saved and run again, as with a recurring audit test. In this book, you will write SQL against a SQLite version of the Charles River database.

Microsoft Power BI is a business intelligence platform for building data models, interactive reports, and dashboards. It connects to multiple data sources, relates tables to one another in a data model, and uses a formula language called DAX to define calculated measures. Power BI is strongest when results must be shared with an audience that wants to explore the data on its own, such as department managers filtering a dashboard to their own cost center.

Table 1.2 summarizes how the three tools compare.

Table 1.2: Excel, SQL, and Power BI at a Glance
Tool Strongest For Typical Accounting Uses Main Workflow Stages Where in This Book
Microsoft Excel Ad hoc analysis, modeling, and workpapers a reviewer can trace Reconciliations, PivotTable summaries, forecasting models, audit workpapers Prepare, analyze Part II
SQL Large datasets that span related tables, and procedures that must be repeated Extracting ledger detail, joining documents across a business cycle, population-level audit tests Access, prepare, analyze Part III
Microsoft Power BI Interactive reporting for audiences that explore the data themselves Financial and operational dashboards, budget-versus-actual reports, audit monitoring Analyze, visualize, communicate Part IV

Figure 1.3 maps each tool to the workflow stages introduced in the previous section. SQL handles most of the work in data access and preparation, and much of the analysis when the data is large or spread across related tables. Excel dominates preparation and analysis. Power BI takes the lead in visualization and presentation. All three tools contribute to the communication of results.

A diagram showing Excel, SQL, and Power BI mapped to the six stages of the analytics workflow, indicating where each tool is primarily used and the part of the book that teaches it. SQL (Part III) maps primarily to stages 2 through 4 (accessing, preparing, and analyzing data). Excel (Part II) maps primarily to stages 3 and 4 (preparing and analyzing data). Power BI (Part IV) maps primarily to stages 4 and 5 (analyzing and visualizing data). All three tools contribute to stage 6 (communication).
Figure 1.3: The Three Tools and Their Roles in the Analytics Workflow.
NoteConnecting the Dots

Part V of this book integrates all three tools using the full Charles River database. Its chapters are capstone cases that prescribe no fixed order of tools. A step that needs a whole population or a join across tables goes to SQL, a schedule or a model that needs judgment goes to Excel, and a result that a reader needs to explore goes to Power BI. This mirrors how analytics projects work in practice, where no single tool handles every stage of the workflow.

1.6 The Charles River Accounting Dataset

This book is built around one integrated dataset that you will use in every chapter. The Charles River Accounting Dataset follows a single fictional mid-size home furnishings company in the greater Boston area that designs and sells furniture, lighting, textiles, and decorative accessories through wholesale and direct-to-business channels, manufactures selected product lines in-house, and operates an interior design services practice that bills customers by the hour. Working with one company throughout the book means you learn one business deeply rather than several superficially, and every chapter builds on the same data environment. The About the Dataset section in the front matter introduces the company and its business cycles in more detail.

The database is organized into table groups, each covering one area of business activity, from customer orders and supplier payments through manufacturing, payroll, and general ledger posting. Table 1.3 summarizes the groups, the key tables in each, and their primary accounting uses. The book introduces these groups progressively, starting with the most accessible tables in the early Excel chapters and expanding into the more complex groups as your skills develop. Figure 1.4 shows how the groups connect through the general ledger, where the financial activity of every business cycle is ultimately posted. Complete documentation of every table and column is available on the dataset website.

Table 1.3: Charles River Table Groups
Table Group Key Tables Primary Accounting Use
Accounting Core Account, JournalEntry, GLEntry Financial reporting, audit analytics, period-close analysis
Order-to-Cash (O2C) Customer, SalesOrder, SalesOrderLine, Shipment, SalesInvoice, CashReceipt, ServiceEngagement Revenue analysis, receivables management, design services margin analysis, sales commissions
Procure-to-Pay (P2P) PurchaseRequisition, PurchaseOrder, GoodsReceipt, PurchaseInvoice, DisbursementPayment Purchasing analytics, three-way matching, vendor evaluation, duplicate payment detection
Manufacturing BillOfMaterial, WorkOrder, MaterialIssue, ProductionCompletion, WorkOrderClose Standard versus actual cost analysis, production performance, material usage variance
Payroll and Time TimeClockEntry, LaborTimeEntry, PayrollRegister, PayrollPayment Labor cost analysis, payroll compliance testing, workforce analytics
Fixed Assets and Financing FixedAsset, FixedAssetEvent, DebtAgreement, DebtScheduleLine Depreciation analysis, asset lifecycle tracking, debt amortization
Master Data Item, Warehouse, Employee Shared reference data for products, locations, and people across all groups
Organizational Planning CostCenter, Budget, BudgetLine Departmental budgetary control, responsibility accounting, budget-versus-actual analysis
Demand Planning and MRP DemandForecast, InventoryPolicy, SupplyPlanRecommendation, MaterialRequirementPlan, RoughCutCapacityPlan Demand forecasting, inventory planning, production planning

The design services practice does not have a table group of its own. Its tables, such as ServiceEngagement, ServiceTimeEntry, and ServiceBillingLine, sit within the Order-to-Cash group, giving you a service revenue cycle alongside the product revenue cycle.

A diagram showing the Charles River table groups as labeled blocks around the Accounting Core, with GLEntry at the center. Dashed arrows show how each business cycle posts its financial activity to the general ledger: Order-to-Cash through invoices, shipments, and cash receipts; Procure-to-Pay through goods receipts, invoices, and payments; Manufacturing through material issues, production completions, and work order closes; Payroll and Time through payroll summaries, payments, and remittances; and Fixed Assets and Financing through purchase invoices for asset acquisitions and journal entries for depreciation, debt service, and disposals. Design Services appears as a dashed block within Order-to-Cash. Heavier solid arrows show that Master Data, Organizational Planning, and Demand Planning and MRP connect to the rest of the data through shared keys such as ItemID, EmployeeID, CostCenterID, and AccountID. Some of these keys, such as ItemID, reach the general ledger only through the tables of the business cycles.
Figure 1.4: Charles River High-Level Architecture Diagram.

Two features make it possible to connect the groups. First, shared key columns link related tables across groups. ItemID connects product-level activity in sales, purchasing, manufacturing, and planning. AccountID connects the general ledger, budgets, and the chart of accounts. EmployeeID connects payroll, time records, and service engagements, and CostCenterID connects operating activity, labor, and budgets by organizational unit. Second, every entry in the GLEntry table records the source document that created it. The SourceDocumentType column identifies the kind of document, such as a SalesInvoice, a Shipment, or a JournalEntry. The SourceDocumentID column identifies the specific document, and the SourceLineID column identifies the line within that document when the posting comes from a single line. Together, these three columns let you trace a ledger posting back to the business event behind it. This source-to-ledger traceability is one of the most valuable features of the dataset, and you will use it repeatedly in this book.

Two visual examples illustrate the structure you will work with throughout the book. Figure 1.5 traces the Order-to-Cash cycle from a sales order through shipment, invoice, and cash receipt. Each stage creates records in its own tables, and the shipment, the invoice, the cash receipt, and its application to the invoice each post to the general ledger. Figure 1.6 follows a single posting in the opposite direction. The GLEntry row that records $688.42 of furniture revenue names its source through SourceDocumentType, SourceDocumentID, and SourceLineID, which lead back to invoice SI-2025-007947 and its line for two Alder desks. Its AccountID leads to account 4010, Sales Revenue - Furniture. Chapter 3 explains how columns like these connect tables, and Tutorial 3.2 traces the same invoice through every table it touches. GLEntry is the central record of the company’s posted financial activity, and the financial statements are built from it. That does not make it complete by itself: later chapters reconcile it to the source documents and find events that reached it late or not at all. Operational records that have no financial effect, such as open sales orders or budgets, remain outside the ledger.

A two-row flowchart of the Order-to-Cash path. In the first row, a Customer places a SalesOrder, which has SalesOrderLines that draw product details from the Item table in Master Data. The path continues in the second row: a Shipment fulfills the order, a SalesInvoice bills it, the customer pays through a CashReceipt, and CashReceiptApplication matches the receipt to the invoices it pays. Dashed arrows from the shipment, the invoice, the cash receipt, and the application lead down to GLEntry in the Accounting Core, labeled COGS and inventory, revenue and AR, cash, and clears AR.
Figure 1.5: Simplified O2C Process Flow.
A GLEntry row shown as a list of columns and values: GLEntryID 126312, PostingDate 2025-03-05, AccountID 42, a credit of $688.42, SourceDocumentType SalesInvoice, SourceDocumentID 7947, and SourceLineID 10645. A solid arrow labeled which account leads from AccountID to the Account row for 4010 Sales Revenue - Furniture. Dashed arrows labeled which table, which row, and which line lead from the three source columns to the SalesInvoice row for SI-2025-007947 and to invoice line 10645, which bills two units of item FUR-DSK-0075 at $344.21 for $688.42.
Figure 1.6: One Ledger Posting Traced to Its Source.
WarningWatch out

The Charles River dataset is a fictional company created for educational purposes. It is designed to illustrate realistic data structures and analytical challenges, but it does not represent the full complexity or messiness of real-world data. Real ERP systems may contain hundreds of tables, custom fields, and data quality problems that exceed what you will encounter in this dataset. The skills you learn here will transfer to real-world environments, but you should expect that professional practice will require additional adaptation and judgment.

1.7 Guided Tutorial 1.1: Exploring the Charles River Database

Context and objective. In this tutorial, you will open the Charles River dataset in Excel and examine its structure across several table groups. The goal is not to perform any analysis but to build familiarity with the tables, columns, and business context of the database. By the end of this tutorial, you will know what data is available and where to find it.

Prerequisites. You need Microsoft Excel installed on your computer and access to the Charles River Excel workbook file provided with this textbook: CharlesRiver.xlsx.

Step-by-step instructions.

Step 1. Open the workbook and browse its worksheets.

  1. Open the file CharlesRiver.xlsx in Excel.
  2. Click through several worksheet tabs at the bottom of the screen and note the variety of table names, from Customer and SalesOrder through WorkOrder and PayrollRegister.
  3. Use Table 1.3 to match the worksheets you see to their table groups.

The workbook contains a large number of worksheets, each representing one table in the Charles River database. Their order follows the business cycles only roughly, and some master data tables, such as Item and Employee, sit among them, so a worksheet’s position does not tell you its group.

Step 2. Explore the Customer worksheet.

  1. Click the Customer worksheet tab and examine the column headers in row 1. You should see columns including CustomerID, CustomerName, CustomerSegment, Region, CreditLimit, and PaymentTerms.
  2. Scroll down to get a sense of the number of records.
  3. Click the filter button on the CustomerSegment column header. The worksheet stores its data as an Excel Table, so the filter buttons are already in place.

You should see segments such as Wholesale, Small Business, Design Trade, Strategic, and Design Services, which classify customers by the way Charles River sells to them.

Step 3. Find the link between orders and customers. Click the SalesOrder worksheet tab. Examine the columns, which include SalesOrderID, CustomerID, OrderDate, RequestedDeliveryDate, SalesRepEmployeeID, and Status, as shown in Figure 1.7.

Notice that CustomerID appears in both the Customer and SalesOrder worksheets. In the Customer table, CustomerID is the primary key, the column whose value uniquely identifies each row. In the SalesOrder table, CustomerID acts as a foreign key, a column that refers to the primary key of another table and so links each order to the customer who placed it. You will study keys and relationships in depth in Chapter 3 and use them constantly when writing SQL joins in Part III.

A mock of the Charles River SalesOrder worksheet in Excel, showing the columns from SalesOrderID through SalesRepEmployeeID for the first orders of 2024, with the SalesOrder sheet tab selected. The CustomerID column, which links each order to the customer who placed it, is outlined.
Figure 1.7: The Charles River SalesOrder Worksheet.

Step 4. Explore the Item worksheet. Click the Item worksheet tab. Examine the columns, which include ItemID, ItemName, ItemGroup, SupplyMode, StandardCost, and ListPrice. Then click the filter button on the ItemGroup column header.

The finished goods that Charles River sells fall into four product groups: Furniture, Lighting, Textiles, and Accessories. You will also see the Services, Raw Materials, Packaging, and Capex groups. Services holds both the design services that Charles River sells and operating services that it buys, such as insurance and IT support. Raw materials and packaging support manufacturing, and Capex holds capital equipment. Notice also the SupplyMode column, which indicates whether each item is Purchased or Manufactured (produced in-house). These distinctions will be important for cost analysis and manufacturing exercises in later chapters.

Step 5. Explore the chart of accounts. Click the Account worksheet tab. Examine the columns, which include AccountID, AccountNumber, AccountName, AccountType, and ParentAccountID.

This is the chart of accounts for Charles River, and it forms the backbone of the Accounting Core. The AccountType column classifies each account as an asset, liability, equity, revenue, or expense account. The ParentAccountID column nests detailed accounts under summary accounts such as Assets, Liabilities, Revenue, and Cost of Goods Sold, creating the same hierarchy you see in a set of financial statements.

Step 6. Explore the general ledger. Click the GLEntry worksheet tab. Examine the columns, which include GLEntryID, PostingDate, AccountID, Debit, Credit, SourceDocumentType, SourceDocumentID, SourceLineID, and CostCenterID. Then click the filter button on the SourceDocumentType column header.

This table contains the individual debit and credit entries that make up the general ledger. The filter list shows document types such as SalesInvoice, Shipment, PurchaseInvoice, PayrollPayment, and JournalEntry, each identifying the kind of business event that generated the entry. Together with SourceDocumentID and SourceLineID, this column traces a posting back to the document that created it, and to the line when a single line created it. You will use this table extensively in the audit and financial reporting chapters.

Step 7. Look beyond sales and accounting. Click on one manufacturing table (such as WorkOrder) and one payroll table (such as PayrollRegister), and examine the columns briefly.

Notice that the database contains far more than sales and accounting data. Work orders track production with columns for the item produced, the planned quantity, the released, due, and completed dates, and the status. Payroll registers record each employee’s pay for a period with columns for gross pay, employee withholdings, employer taxes and benefits, and net pay. These table groups will become important in the SQL and Power BI parts of the book.

Checkpoint. At this point, you should have examined worksheets from five table groups (Order-to-Cash, Master Data, Accounting Core, Manufacturing, and Payroll and Time). You should be able to answer the following questions:

  • Which four product groups make up the finished goods that Charles River sells? (Furniture, Lighting, Textiles, and Accessories.)
  • Which columns in GLEntry trace each entry back to its source document? (SourceDocumentType, which identifies the kind of document, together with SourceDocumentID, which identifies the specific document, and SourceLineID, which identifies the line when the posting comes from a single line.)
  • Which worksheet contains the chart of accounts? (Account.)

If you can answer these three questions, you are ready to proceed.

1.8 How This Book Is Organized

This book is organized into five parts that follow the analytics workflow and introduce the tools in a sequence designed for readers with no prior analytics or programming experience. As Figure 1.8 shows, the parts also introduce the Charles River table groups progressively, starting with the most accessible tables and expanding as your skills develop.

Part I, Foundations of Accounting Analytics, establishes the conceptual groundwork, and its tutorials already work in the Excel workbook. You are in Part I now. Chapter 2 will teach you to think critically about accounting data, including its types, sources, and quality, and Chapter 3 will introduce the relational database model and show you how the Charles River database is organized. A comprehensive case at the end of Part I then asks you to use all three chapters to get the data ready for the controller’s question from the opening scenario.

Part II, Accounting Analytics with Excel, teaches you to use Excel as an analytical tool, from data organization and cleaning through summarization, modeling, and audit-specific analytics. It works primarily with the Order-to-Cash and Accounting Core groups and the Item table from Master Data, and it draws on the planning and purchasing tables for budgeting and audit tests.

Part III, Accounting Analytics with SQL, teaches you to query relational databases directly, from the basic SELECT statement through joins, aggregation, window functions, and audit analytics queries. It expands into the Procure-to-Pay and Manufacturing groups, where relational joins become essential.

Part IV, Data Visualization and Power BI, teaches you to build interactive reports and dashboards, from visualization principles through data modeling with DAX and full dashboard design, using data models that span multiple table groups.

Part V, Capstone Cases, applies the full toolkit in three independent cases: one in financial reporting, one in managerial accounting, and one in auditing. A course can assign any one of them as a final project. Each case requires you to move between Excel, SQL, and Power BI within a single project, choosing the tool each step calls for, just as you would in professional practice. Parts II to IV also close with a comprehensive case, as Part I does.

A visual roadmap showing the five parts of the book as a progression from left to right. Part I (Foundations, all groups at overview) leads to Part II (Excel, sales and ledger tables, then planning and purchasing), Part III (SQL, expands into P2P and Manufacturing), and Part IV (Power BI, cross-group models), which converge into Part V (Capstone Cases, three independent cases that use all three tools and all table groups). A note indicates that a comprehensive case closes each of Parts I to IV.
Figure 1.8: Book Organization Map.

1.9 Looking Ahead

This chapter introduced the concept of accounting analytics, defined the four types of analytics, examined how analytics supports each area of the profession, and outlined the workflow that connects every chapter. It also introduced the tools and the Charles River dataset you will use throughout the book. You will answer the controller’s question from the opening scenario in Chapter 6, with Excel. By the time you reach the capstone cases of Part V, you will be able to answer questions like it with all three tools, choosing for each step the tool that fits: SQL to extract and reconcile the invoice and cost data, Excel to model it, and Power BI to present your findings.

In the next chapter, you will take a closer look at accounting data itself. You will learn to distinguish different data types, identify common data quality problems, and understand why the quality of your data determines the quality of your analysis. The skills you begin building in Chapter 2 will support everything that follows.

1.10 Chapter Summary

The accounting profession is undergoing a fundamental shift in the skills it demands from practitioners. The growth of transaction data, the spread of automation and artificial intelligence, and the expansion of the accountant’s role from preparer to analyst have created strong demand for data analytics competencies. Professional bodies including the AICPA, NASBA, IMA, and IFAC have responded by building analytics and technology into their model curricula, education standards, and competency frameworks, signaling that these skills are essential rather than optional.

Analytics in accounting takes four forms. Descriptive analytics summarizes historical data to explain what happened. Diagnostic analytics investigates why it happened by drilling into details and decomposing results into their drivers. Predictive analytics uses historical patterns to forecast outcomes, usually future ones, or to set expectations. Prescriptive analytics recommends actions by optimizing objectives subject to constraints. Most accounting analytics work is descriptive, diagnostic, or predictive, and doing this work well requires both technical skill and professional judgment. Each type appears in financial accounting, managerial accounting, and auditing, the three perspectives around which the applied exercises in this book are organized.

Analytics projects move through a consistent workflow, although later stages can send the work back to earlier ones. The accountant defines the question, accesses the data, prepares and cleans the data, analyzes it, visualizes the results, and communicates findings. This workflow provides the organizing thread for the entire book.

This book teaches accounting analytics using three complementary tools. SQL is strongest for extracting and combining data from relational databases, Excel for preparation, analysis, and modeling, and Power BI for interactive visualization and communication. All three are applied to the Charles River Accounting Dataset, a single integrated database organized into table groups that correspond to the company’s business processes, with source-to-ledger traceability in the GLEntry table designed to connect each posting to the business event that created it. The book introduces the table groups progressively, starting with the Order-to-Cash and Accounting Core groups in the Excel chapters of Part II, expanding into Procure-to-Pay and Manufacturing in the SQL chapters of Part III, building cross-group models in Power BI in Part IV, and integrating all groups in the capstone cases of Part V.

1.11 Key Terms

Accounting analytics. The application of data extraction, preparation, analysis, and visualization techniques to financial and operational data for the purpose of supporting financial reporting, managerial decision-making, and audit assurance.

Analytics workflow. The sequence of stages that an accounting analytics project follows, from defining the question through communicating findings. The six stages are question definition, data identification and access, data preparation, analysis, visualization, and communication.

Big data. Datasets that are too large or complex for traditional data processing methods, usually stored and processed on cloud data platforms. Accounting analytics draws on big data concepts but applies them to accounting data and questions.

Business cycle. A sequence of related business activities that form a complete process. Charles River’s business cycles, such as Order-to-Cash, Procure-to-Pay, Manufacturing, and Payroll, each generate records in one or more table groups, and their financial activity is ultimately posted to the general ledger.

Business intelligence. The tools and infrastructure that organizations use to collect, store, and present data for operational and strategic decisions. Microsoft Power BI is an example of a business intelligence platform.

Chart of accounts. A structured list of all accounts used by an organization to record financial transactions, organized by account type (assets, liabilities, equity, revenue, expenses). In the Charles River database, the chart of accounts is stored in the Account table within the Accounting Core group.

Data analytics. A broad term for the process of examining data to draw conclusions, identify patterns, and support decisions. Accounting analytics is a specific application of data analytics to accounting data and questions.

Descriptive analytics. The type of analytics that summarizes and describes historical data to answer the question “what happened?” Examples in accounting include financial statement preparation, variance reports, and aging analyses.

Diagnostic analytics. The type of analytics that investigates the causes of a result to answer the question “why did it happen?” Examples in accounting include decomposing a margin decline into price, mix, and cost effects, drilling into an unexpected change in an account balance, and tracing exceptions back to their source transactions.

Enterprise resource planning (ERP) system. An integrated software platform that organizations use to manage business processes across departments, including accounting, sales, purchasing, production, and human resources. The Charles River database is organized like the database behind an ERP system, with table groups that correspond to the functional modules found in commercial ERP platforms.

Foreign key. A column in one table that references the primary key of another table, creating a relationship between the two tables. For example, the CustomerID column in the Charles River SalesOrder table is a foreign key that references the Customer table.

General ledger. The central record of an organization’s posted financial transactions, organized by account. In the Charles River database, the general ledger is the GLEntry table within the Accounting Core group.

Microsoft Excel. A spreadsheet application developed by Microsoft that organizes data in rows and columns and supports calculations, PivotTables, lookup functions, Power Query, and optimization with Solver. Part II of this book teaches Excel for accounting analytics.

Microsoft Power BI. A business intelligence platform developed by Microsoft that enables users to connect to data sources, build data models, and create interactive visualizations and dashboards. Part IV of this book teaches Power BI.

Predictive analytics. The type of analytics that uses historical data to forecast outcomes, usually future ones, to set expectations, and to estimate probabilities. Examples in accounting include revenue forecasting, regression-based analytical procedures, and credit risk assessment.

Prescriptive analytics. The type of analytics that recommends actions by optimizing an objective subject to constraints. Examples in accounting include product mix optimization, tax strategy planning, and resource allocation modeling.

Primary key. A column, or combination of columns, that uniquely identifies each row in a table. For example, SalesOrderID is the primary key of the Charles River SalesOrder table.

Relational database. A system that stores data in tables connected to one another through primary and foreign keys. The Charles River dataset is provided as a relational database in SQLite format, as well as in Excel workbook and CSV formats.

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.

SQL (Structured Query Language). The standard language for accessing, querying, and manipulating data stored in relational databases. Part III of this book teaches SQL using the Charles River SQLite database.

SQLite. A lightweight relational database system that stores the entire database in a single file. This book uses SQLite because it requires no server installation and runs on any operating system.

Table group. A named set of related tables within the Charles River database that corresponds to a business process or functional area. Examples include Order-to-Cash, Procure-to-Pay, and the Accounting Core, and Table 1.3 lists every group with its key tables.

1.12 Multiple Choice Questions

1. Which of the following best describes the primary reason that analytics skills have become essential for accounting professionals?

  1. Accounting standards now require the use of data visualization tools in all financial reports.
  2. Analytics skills have replaced the need for accountants to understand generally accepted accounting principles.
  3. Employers prefer to hire data scientists rather than accountants for financial reporting roles.
  4. The volume and complexity of data have outgrown what manual methods can analyze effectively.

2. A management accountant prepares a report showing total manufacturing costs by product line for the past four quarters and calculates the percentage change from quarter to quarter. This activity is best classified as which type of analytics?

  1. Descriptive analytics
  2. Diagnostic analytics
  3. Predictive analytics
  4. Prescriptive analytics

3. An auditor builds a regression model using monthly revenue data from the prior year to establish an expected range for current-year monthly revenue, against which actual results will later be compared. Building this expectation is an example of which type of analytics?

  1. Descriptive analytics
  2. Diagnostic analytics
  3. Predictive analytics
  4. Prescriptive analytics

4. A cost accountant uses Excel’s Solver tool to determine the combination of products that maximizes total contribution margin given constraints on machine hours and raw material availability. This activity is best classified as which type of analytics?

  1. Descriptive analytics
  2. Diagnostic analytics
  3. Predictive analytics
  4. Prescriptive analytics

5. Which of the following is the correct sequence of stages in the accounting analytics workflow as described in this chapter?

  1. Define the question, access data, prepare data, analyze, visualize, communicate
  2. Access data, define the question, analyze, clean, visualize, communicate
  3. Analyze data, define the question, visualize, clean, communicate, access data
  4. Define the question, visualize, access data, analyze, clean, communicate

6. Which Charles River table group contains the chart of accounts, journal entries, and posted ledger detail?

  1. Order-to-Cash
  2. Master Data
  3. Accounting Core
  4. Organizational Planning

7. Which tool covered in this book is most appropriate for extracting data directly from a relational database?

  1. Microsoft Excel
  2. SQL
  3. Microsoft Power BI
  4. The Data Analysis ToolPak

8. The book introduces the Order-to-Cash and Accounting Core table groups before the Manufacturing and Payroll and Time groups primarily because:

  1. The book starts with the most accessible table groups and builds toward the more complex ones.
  2. Manufacturing and payroll data is less important for accounting analysis than sales and ledger data.
  3. Manufacturing and payroll tables use a different database format that requires advanced SQL skills.
  4. Students must complete all O2C exercises before the Manufacturing tables become available in the dataset.

9. Which professional organization’s competency framework, expanded in 2025, includes domains titled Data Analytics and Insights and Emerging Technologies in Accounting?

  1. The Financial Accounting Standards Board (FASB)
  2. The Securities and Exchange Commission (SEC)
  3. The Institute of Management Accountants (IMA)
  4. The International Accounting Standards Board (IASB)

10. An accountant needs to create an interactive dashboard that allows multiple department managers to filter financial results to their own cost center and drill down into individual line items. Which tool is best suited for this purpose?

  1. A SQL query
  2. A static Excel spreadsheet
  3. The Excel Data Analysis ToolPak
  4. Microsoft Power BI

11. An auditor selects a row in the Charles River GLEntry table and wants to find the business event that created it. Which columns make this possible?

  1. AccountID and CostCenterID
  2. SourceDocumentType, SourceDocumentID, and SourceLineID
  3. FiscalYear and FiscalPeriod
  4. Debit and Credit

12. According to this chapter, which stage of the analytics workflow is often the most time-consuming?

  1. Data preparation and cleaning
  2. Defining the question
  3. Visualization
  4. Communication of findings

13. Which Charles River table group is most valuable for managerial accounting exercises involving production cost variance analysis?

  1. Order-to-Cash
  2. Accounting Core
  3. Manufacturing
  4. Master Data

14. Which of the following best describes why this book uses a single dataset in every chapter rather than multiple separate databases?

  1. A single database requires less storage space on student computers than multiple databases.
  2. Students learn one business in depth and meet the same transactions in every tool.
  3. The Charles River database is the only dataset available in both Excel and SQLite formats.
  4. Using a single dataset allows the book to avoid teaching SQL joins, which require multiple tables.

15. A financial analyst uses a PivotTable to summarize invoiced revenue and gross margin by item group and fiscal quarter, using the Charles River invoice, item, and customer tables. The summary shows that the gross margin on Furniture declined in the most recent quarter. The analyst then breaks the decline down by product and customer segment and traces the largest changes to the underlying invoices to determine their cause. What type of analytics does this investigation represent?

  1. Descriptive analytics
  2. Diagnostic analytics
  3. Predictive analytics
  4. Prescriptive analytics

1.13 Applied Exercises

1.13.1 Financial Accounting Exercises

Exercise 1.1: Mapping Table Groups to the Financial Reporting Cycle

Table group: All groups (overview level)

Scenario. You are a financial reporting analyst at Charles River. Your supervisor has asked you to prepare a memorandum that maps the company’s data environment to common financial reporting needs. The supervisor wants to understand which table groups and specific tables contain the data needed for four reporting areas: revenue, accounts receivable, inventory and cost of goods sold, and general ledger reporting.

Requirements.

  1. Open the Charles River Excel workbook and examine worksheets from at least four table groups: Order-to-Cash, Master Data, Accounting Core, and Manufacturing (Table 1.3 lists the key tables in each group). For each group, identify which tables contain data that relates to each of the four reporting areas.

  2. For each of the four reporting areas, identify the specific tables and columns you would use. Write a brief explanation (two to three sentences) for each area.

  3. Identify one financial reporting task that requires combining data from the O2C group with data from the Accounting Core group, and explain which tables you would need from each group and why.

  4. Prepare a one-page summary memorandum presenting your findings in a format suitable for your supervisor.

Deliverable. A one-page written memorandum that maps Charles River table groups to financial reporting needs, identifying specific tables and explaining your reasoning.

Exercise 1.2: Tracing the Revenue Path from Order to Ledger

Table group: O2C and Accounting Core

Scenario. You are a staff accountant preparing for the quarterly close. Your manager has asked you to document the complete path that a revenue transaction follows through the Charles River database, from the initial customer order through cash collection and general ledger posting.

Requirements.

  1. Open the Charles River Excel workbook and examine the following worksheets in order: SalesOrder, SalesOrderLine, Shipment, SalesInvoice, CashReceipt, CashReceiptApplication, and GLEntry. For each table, identify the business event that creates a new row, such as a customer placing an order or the warehouse shipping goods. You do not need to trace the key columns that link the tables, because Chapter 3 teaches that skill.

  2. Write a narrative description (one paragraph per table) explaining what each table records and how it connects to the next step in the revenue process.

  3. Identify the column in GLEntry that tells you the entry originated from a sales invoice rather than a journal entry or a payroll transaction.

  4. Write a brief comparison (three to five sentences) explaining what information is available in the SalesInvoice table that is not available in the GLEntry table, and why an analyst might need to access both.

Deliverable. A written description of the revenue transaction path through the Charles River database, with specific table and column references.

1.13.2 Managerial Accounting Exercises

Exercise 1.3: Evaluating Table Groups for Cost Analysis

Table group: Manufacturing, Organizational Planning, and Payroll and Time, with GLEntry from the Accounting Core

Scenario. You are a management accountant at Charles River. Your controller has asked you to assess which table groups contain the information needed for a product cost analysis and a departmental budget review.

Requirements.

  1. Open the Charles River Excel workbook and examine worksheets from the Manufacturing group (such as WorkOrder, BillOfMaterial, MaterialIssue, ProductionCompletion, and WorkOrderClose). List the table names and the columns most relevant to understanding product costs.

  2. Examine worksheets from the Organizational Planning group (CostCenter, Budget, BudgetLine). List the table names and columns relevant to departmental budgeting. These tables hold only the plan, so also name the GLEntry columns that would supply the actual spending for the same cost center, account, and period. You do not need to join the tables.

  3. Determine which group is better suited for product-level cost analysis (comparing standard cost to actual production cost) and which is better suited for departmental budget analysis (comparing budgeted amounts to actual spending by department). Explain your reasoning in three to five sentences.

  4. Identify one managerial accounting question that requires data from both the Manufacturing group and the Payroll and Time group, and explain what tables from each group you would need.

Deliverable. A written assessment that compares the cost analysis capabilities of the Manufacturing and Organizational Planning table groups, with specific references to tables and columns.

Exercise 1.4: Framing Production Questions with the Four Types of Analytics

Table group: Manufacturing and Master Data

Scenario. A new operations manager at Charles River wants to use the company’s data to evaluate production performance but is not sure which questions to ask first. You have been asked to help the manager frame questions that analytics can answer.

Requirements.

  1. Write one production-related question for each type of analytics: descriptive, diagnostic, predictive, and prescriptive. For example, a descriptive question might ask how many units of each product were completed last quarter.

  2. For each question, identify the table group that holds the data needed to answer it, using Table 1.3 as a starting point. Open the Charles River Excel workbook and name one or two worksheets that look relevant to each question.

  3. Explain in two to three sentences why the prescriptive question is the hardest of the four to answer, considering the data, the assumptions, and the judgment it requires.

  4. Prepare a brief memorandum (one half to one page) that presents the four questions to the manager in order of analytical complexity and recommends which one to tackle first.

Deliverable. A memorandum presenting four production questions, one for each type of analytics, with the table groups and worksheets that would support each.

1.13.3 Auditing Exercises

Exercise 1.5: Assessing Data Availability for Audit Testing

Table group: O2C, P2P, and Accounting Core

Scenario. You are a first-year auditor who has been assigned to plan the data analytics procedures for the Charles River engagement. Your senior has asked you to assess what data is available for three common audit tests: aging of receivables, testing for duplicate payments, and journal entry testing for management override of controls, in which managers use their authority to bypass the controls that apply to everyone else.

Requirements.

  1. For each of the three audit tests listed above, identify which Charles River table group and specific tables contain the data needed to perform the test. For aging of receivables, look at the O2C group: SalesInvoice and Customer for what was billed and to whom, CashReceiptApplication for the part of each receipt applied to each invoice (CashReceipt records the receipt itself), and CreditMemo for the credits that reduce an invoice. An aging is taken at a reporting date, so name the date columns that decide which applications and credits count by then, and the Accounting Core table you would use to check whether the open invoices explain the whole receivables balance. You do not need to calculate the aging. For duplicate payment detection, look at the P2P group (DisbursementPayment). For journal entry testing, look at the Accounting Core group (JournalEntry and GLEntry). Be specific about which columns you would use.

  2. Explain how the SourceDocumentType column in GLEntry allows an auditor to distinguish between journal entries made by the accounting team and entries generated automatically by business transactions such as sales invoices or payroll.

  3. Identify one audit test that would require combining data from the O2C group with data from the Accounting Core group, and explain why neither group alone would be sufficient.

  4. Write a planning memorandum (one page) that documents the data available for each test and identifies the specific tables and columns to be used.

Deliverable. A one-page audit planning memorandum documenting data availability for three specified audit procedures within the Charles River database.

Exercise 1.6: Evaluating Journal Entry Data for Audit Purposes

Table group: Accounting Core

Scenario. You are preparing for journal entry testing as part of the Charles River audit engagement. Before writing any queries or performing any analysis, you need to understand the structure of the journal entry data in the company’s system.

Requirements.

  1. Open the Charles River Excel workbook and examine the JournalEntry and GLEntry worksheets. Identify all columns available in each table.

  2. Determine which columns would be most useful for identifying journal entries that warrant audit attention. Consider columns related to the people who created and approved the entry, its posting date and any timestamps the data carry, the amount, and any descriptions or references. Look for columns such as CreatedByEmployeeID, ApprovedByEmployeeID, CreatedDate, PostingDate, EntryType, TotalAmount, and Description in JournalEntry, and Debit and Credit in GLEntry.

  3. Write a brief assessment (three to five sentences) of whether the Charles River data provides sufficient information to test for three common fraud risk indicators: entries posted outside of normal business hours, entries with round-dollar amounts, and entries posted just below an approval threshold. For any indicator where the data may be insufficient, explain what additional information would be needed.

  4. The JournalEntry table includes a ReversesJournalEntryID column. Write one paragraph explaining how this column could be used in an audit test for window dressing, entries near the end of a period that make its results look better and are reversed soon after it closes.

Deliverable. A written assessment of the journal entry data structure in the Charles River Accounting Core group, evaluating its suitability for audit testing of management override of controls.

1.14 Further Reading

Appelbaum, D., Kogan, A., & Vasarhelyi, M. A. (2017). Big data and analytics in the modern audit engagement: Research needs. Auditing: A Journal of Practice & Theory, 36(4), 1–27. https://doi.org/10.2308/ajpt-51684 This paper outlines a research agenda for data analytics in auditing and provides a framework for understanding how the analytical techniques taught in this book relate to the evolving audit process. The paper’s discussion of population-based testing versus sampling is particularly relevant to the arguments presented in this chapter.

Choi, J. H., & Xie, C. L. (2026). Human + AI in accounting: Early evidence from the field. Journal of Accounting Research, 64(3), 1333–1373. https://doi.org/10.1111/1475-679x.70052 This open-access study combines a survey of professional accountants, transaction-level data from an AI-enabled accounting platform, and a field experiment to examine how generative AI is changing day-to-day accounting work. It finds that greater AI use is associated with less routine data entry and more client communication and quality review, and that AI assistance improves classification accuracy on average while reliance on incorrect AI suggestions can introduce errors. It is a useful companion to this chapter’s discussion of why accountants still need to judge whether data and automated output are reliable.

Earley, C. E. (2015). Data analytics in auditing: Opportunities and challenges. Business Horizons, 58(5), 493–500. https://doi.org/10.1016/j.bushor.2015.05.002 This article provides an accessible overview of how audit practice is changing due to data analytics and identifies specific opportunities for auditors who develop analytical skills. It is useful reading if you are interested in the audit perspective.

International Federation of Accountants. (2025). International Education Standard (IES) 2: Initial professional development—Technical competence (2026). https://education.ifac.org/part/ies-2 IFAC’s revised standard on technical competence, effective from July 1, 2026, lists information and communications technologies among the competence areas that aspiring professional accountants develop by the end of their initial professional development, with learning outcomes that include using technology to analyze data and evaluating the adequacy of technology processes and controls. It shows that international standard setters treat technology and analytics as part of the core preparation of professional accountants.

Kokina, J., Blanchette, S., Davenport, T. H., & Pachamanova, D. (2025). Challenges and opportunities for artificial intelligence in auditing: Evidence from the field. International Journal of Accounting Information Systems, 56, Article 100734. https://doi.org/10.1016/j.accinf.2025.100734 Based on interviews at large public accounting firms, this study describes how artificial intelligence is being adopted in auditing. It finds that basic tools such as data extraction and optical character recognition are widely used while more advanced AI remains mostly in development, and it discusses the challenges of transparency, reliability, and auditor overreliance on AI. It provides useful background for the discussion of automation and artificial intelligence in this chapter and is a good starting point for following how these tools develop.

Provost, F., & Fawcett, T. (2013). Data science and its relationship to big data and data-driven decision making. Big Data, 1(1), 51–59. https://doi.org/10.1089/big.2013.1508 This paper defines data science as a discipline and describes the analytical workflow that underlies data-driven decision making. Although it addresses data science broadly rather than accounting specifically, the workflow it describes closely parallels the accounting analytics workflow presented in this chapter.

Richins, G., Stapleton, A., Stratopoulos, T. C., & Wong, C. (2017). Big data analytics: Opportunity or threat for the accounting profession? Journal of Information Systems, 31(3), 63–79. https://doi.org/10.2308/isys-51805 This paper examines the implications of big data for the accounting profession and discusses both the opportunities for accountants who develop analytics skills and the threats for those who do not. Its discussion gives useful context for the career arguments presented in this chapter.

Vasarhelyi, M. A., Kogan, A., & Tuttle, B. M. (2015). Big data in accounting: An overview. Accounting Horizons, 29(2), 381–396. https://doi.org/10.2308/acch-51071 This paper provides a comprehensive overview of how big data and analytics are affecting accounting practice, research, and education. It is a foundational overview of accounting analytics and offers valuable context for understanding why this book exists.

1.15 References

American Institute of Certified Public Accountants & National Association of State Boards of Accountancy. (2021). CPA Evolution model curriculum. https://www.thiswaytocpa.com/collectedmedia/files/cpa-evolution-model-curriculum-update.pdf

Appelbaum, D., Kogan, A., Vasarhelyi, M., & Yan, Z. (2017). Impact of business analytics and enterprise systems on managerial accounting. International Journal of Accounting Information Systems, 25, 29–44. https://doi.org/10.1016/j.accinf.2017.03.003

Askary, S., & Askarany, D. (2024). Analytical skills for accounting students in a data-driven job market: Australian evidence. Accounting Research Journal, 37(6), 635–654. https://doi.org/10.1108/ARJ-05-2024-0184

Barr-Pulliam, D., Brown-Liburd, H. L., & Munoko, I. (2022). The effects of person-specific, task, and environmental factors on digital transformation and innovation in auditing: A review of the literature. Journal of International Financial Management & Accounting, 33(2), 337–374. https://doi.org/10.1111/jifm.12148

Bedford, D. S., Derichs, D., Hoozée, S., Malmi, T., Messner, M., Sinha, V. K., Van der Kolk, B., & Verbeeten, F. (2025). Digitalization of the finance function: Automation, analytics, and finance function effectiveness. Management Accounting Research, 67, Article 100942. https://doi.org/10.1016/j.mar.2025.100942

Brink, W. D., & Stoel, M. D. (2019). Analytics knowledge, skills, and abilities for accounting graduates. In T. G. Calderon (Ed.), Advances in accounting education: Teaching and curriculum innovations (Vol. 22, pp. 23–43). Emerald Publishing. https://doi.org/10.1108/S1085-462220190000022002

Choi, J. H., & Xie, C. L. (2026). Human + AI in accounting: Early evidence from the field. Journal of Accounting Research, 64(3), 1333–1373. https://doi.org/10.1111/1475-679x.70052

Earley, C. E. (2015). Data analytics in auditing: Opportunities and challenges. Business Horizons, 58(5), 493–500. https://doi.org/10.1016/j.bushor.2015.05.002

Elo, T., Kimpimäki, J.-P., Puumalainen, K., Sjögrén, H., & Pätäri, S. (2026). Mapping the skill landscape: Insights from accounting job advertisements. Accounting Education, 35(5), 595–636. https://doi.org/10.1080/09639284.2025.2488979

Franke, F., & Hiebl, M. R. W. (2023). Big data and decision quality: The role of management accountants’ data analytics skills. International Journal of Accounting & Information Management, 31(1), 93–127. https://doi.org/10.1108/IJAIM-12-2021-0246

Hirsch, B., Seubert, A., & Sohn, M. (2015). Visualisation of data in management accounting reports: How supplementary graphs improve every-day management judgments. Journal of Applied Accounting Research, 16(2), 221–239. https://doi.org/10.1108/JAAR-08-2012-0059

Institute of Management Accountants. (2025). IMA expanded management accounting competency framework: Advancing competencies for the 21st-century management accountant. https://mc-69e30ef4-758e-4371-ac6f-2657-cdn-endpoint.azureedge.net/-/media/IMA/Files/Home/Career-Resources/Competency-Framework/Competency-Framework-White-Paper.ashx

International Federation of Accountants. (2025). International Education Standard (IES) 2: Initial professional development—Technical competence (2026). https://education.ifac.org/part/ies-2

Kokina, J., Blanchette, S., Davenport, T. H., & Pachamanova, D. (2025). Challenges and opportunities for artificial intelligence in auditing: Evidence from the field. International Journal of Accounting Information Systems, 56, Article 100734. https://doi.org/10.1016/j.accinf.2025.100734

Law, K. K. F., & Shen, M. (2025). How does artificial intelligence shape audit firms? Management Science, 71(5), 3641–3666. https://doi.org/10.1287/mnsc.2022.04040

Liew, A., Boxall, P., & Setiawan, D. (2022). The transformation to data analytics in Big-Four financial audit: What, why and how? Pacific Accounting Review, 34(4), 569–584. https://doi.org/10.1108/PAR-06-2021-0105

Provost, F., & Fawcett, T. (2013). Data science and its relationship to big data and data-driven decision making. Big Data, 1(1), 51–59. https://doi.org/10.1089/big.2013.1508

Public Company Accounting Oversight Board. (2024). Amendments related to aspects of designing and performing audit procedures that involve technology-assisted analysis of information in electronic form (PCAOB Release No. 2024-007). https://assets.pcaobus.org/pcaob-dev/docs/default-source/rulemaking/docket-052/2024-007-adoptingrelease.pdf

Richins, G., Stapleton, A., Stratopoulos, T. C., & Wong, C. (2017). Big data analytics: Opportunity or threat for the accounting profession? Journal of Information Systems, 31(3), 63–79. https://doi.org/10.2308/isys-51805

Spraakman, G., Sanchez-Rodriguez, C., & Tuck-Riggs, C. A. (2021). Data analytics by management accountants. Qualitative Research in Accounting & Management, 18(1), 127–147. https://doi.org/10.1108/QRAM-11-2019-0122

Stroe, R., & Teneva, I. (2025). Future-ready finance: Technology, productivity, and skills survey report. Association of International Certified Professional Accountants. https://www.aicpa-cima.com/resources/download/future-ready-finance-technology-productivity-and-skills-survey-report

Suarta, I. M., Suwintana, I. K., Sudiadnyani, I. G. A. O., & Sintadevi, N. P. R. (2024). Employability and digital technology: What skills employers want from accounting workers? Accounting Education, 33(3), 274–295. https://doi.org/10.1080/09639284.2023.2196665

Vasarhelyi, M. A., Kogan, A., & Tuttle, B. M. (2015). Big data in accounting: An overview. Accounting Horizons, 29(2), 381–396. https://doi.org/10.2308/acch-51071

Vitali, S., & Giuliani, M. (2024). Emerging digital technologies and auditing firms: Opportunities and challenges. International Journal of Accounting Information Systems, 53, Article 100676. https://doi.org/10.1016/j.accinf.2024.100676