Chapter 1: The Role of Analytics in Accounting

Accounting Analytics: An Integrated Approach

In this chapter

Part I of V: Foundations of Accounting Analytics · Chapter 1 of 3 in this Part

• 1.1 The Changing Landscape of Accounting Work
• 1.2 Defining Accounting Analytics
• 1.3 Analytics Across Accounting Roles
• 1.4 The Accounting Analytics Workflow
• 1.5 The Tools of This Book
• 1.6 The Charles River Accounting Dataset
• Guided Tutorial 1.1
• 1.9 Looking Ahead

This chapter sets up everything the book builds on: the questions analytics answers, the six-stage workflow, the three tools, and the Charles River dataset. Each module on this slide matches a section of the chapter, so students can read along in the same order.

Learning objectives

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.

Read the five objectives aloud. The summary at the end of the deck returns to each one, so students can check what they can now do.

Your first week at Charles River

• You are the new staff accountant at a home furnishings company near Boston
• The controller asks: why did Furniture margins decline in the latest quarter?
• The data sits in several tables: sales orders, product costs, the general ledger
• Answering takes more than accounting knowledge: you must extract, prepare, analyze and communicate data

This is the question Part I prepares students to answer, and Chapter 6 answers it with Excel. Ask what they would need to know before they could even start, then point out that this chapter is that starting point.

1.1 The Changing Landscape of Accounting Work

From samples to whole populations

• Ledgers once held a few thousand entries a year; today’s systems record millions
• Sampling 50 of 500,000 transactions examines one hundredth of one percent of the transactions
• The technology that creates the data can also test every transaction

Growth in data is both the challenge and the opportunity. Manual review cannot keep pace, but a test of every recorded transaction removes the risk that a sample misses an exception the test looks for. It does not make up for the wrong test, incomplete or false records, or a misread result.

The work shifts toward judgment

• Software now matches invoices, prepares reconciliations and drafts analyses
• Accountants frame the question, judge whether the data is reliable, and decide
• The profession expects analytics: the CPA Evolution model curriculum, the IMA competency framework, IFAC’s International Education Standards

Automation has not removed the need for accountants; it changes what they contribute. Early field evidence associates generative AI use with less routine data entry and more client communication and review. In a separate experiment, AI help improved accuracy on average, but relying on incorrect suggestions could raise the risk of error. A survey of finance leaders names a lack of skills and talent as the leading barrier to adopting new technology.

1.2 Defining Accounting Analytics

What accounting analytics means

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.

• Data analytics: the same kind of work in any field
• Business intelligence: the tools that collect, store and present data for decisions
• Big data: data too large or complex for traditional processing

Accounting analytics draws on all three related terms but applies them to the data, questions and standards of the accounting profession. The definition comes from the chapter’s key terms.

Four types of analytics answer four questions

Figure 1.1 · The Analytics Continuum

Descriptive asks what happened, diagnostic why it happened, predictive what might happen, and prescriptive what we should do. The types form a continuum of increasing complexity, and most real projects combine more than one.

Check your understanding

Which type of analytics does each question call for?

1. What was revenue by item group last quarter?
2. Why did the Furniture margin fall?
3. What will revenue be next quarter?
4. Which product mix maximizes contribution margin, given machine hours?

Give students a minute to classify each question, alone or in pairs, before moving to the answers.

Each question has its type

1. Descriptive: a summary of what happened
2. Diagnostic: the controller’s question, broken into its drivers
3. Predictive: a forecast built from historical trends
4. Prescriptive: an optimization subject to constraints, as with Excel’s Solver

Descriptive is not “basic”: much accounting analytics is descriptive, and a poorly built summary misleads as easily as a sophisticated model.

The value of analytics lies in the quality of the question and the rigor of the process, not in complexity. The book emphasizes descriptive, diagnostic and predictive work because accountants do it most often.

1.3 Analytics Across Accounting Roles

Three perspectives use the same skills

• Financial reporting: the close, reconciliations, and fluctuation analysis before the statements are issued
• Managerial accounting: explaining results against plan, forecasting, finding operational drivers
• Auditing: testing whole populations to find items to investigate, continuous monitoring

The book’s exercises are organized around these three perspectives. One technique can serve all three: a duplicate-payment analysis supports controls assurance in an audit, vendor management in procurement, and cash flow review in controllership.

The controller’s question is diagnostic

Table 1.1 · Examples of Analytics Types by Accounting Perspective

Type	Financial Accounting	Managerial Accounting	Auditing
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

The chapter’s table gives an example for every type in every perspective; this is its Diagnostic row. The managerial cell is the controller’s question: decomposing the Furniture margin decline into its price, volume, mix and cost effects.

1.4 The Accounting Analytics Workflow

Six stages take a question to a recommendation

Figure 1.2 · The Accounting Analytics Workflow

Every project in the book follows these six stages, whatever the tool. The note under the figure shows which Parts of the book develop each stage.

The controller’s question, stage by stage

Stage	For the Furniture margin
1. Define the question	Why did Furniture margins decline in the latest quarter?
2. Access the data	Invoiced sales, product costs, the revenue and cost of goods sold accounts
3. Prepare and clean	Link every invoice line to a product; treat returns consistently
4. Analyze	Margin by quarter and item group, split into volume, mix, price and cost
5. Visualize	One chart of margin by quarter, each driver highlighted
6. Communicate	A short memo to the controller

Walk the question down the stages. A vague question produces a vague answer, so the first stage shapes every decision that follows, from the data to the chart.

Check your understanding

Which stage of the workflow usually takes the most time, and why does it matter so much?

Let students answer before advancing. Many will guess the analysis stage.

Preparing and cleaning the data

• Raw data has missing values, duplicates, inconsistent formats and wrong types
• Analysis performed on dirty data produces unreliable results
• Parts II and III both teach preparation techniques

Preparation is often the most time-consuming stage and among the most important. The workflow parallels the data analysis process of data science; what makes accounting analytics distinctive is its data, its questions and the professional standards that govern it.

1.5 The Tools of This Book

Each tool fits different stages

Table 1.2 · Excel, SQL, and Power BI at a Glance

Tool	Strongest For	Main Workflow Stages	Where in This Book
Microsoft Excel	Ad hoc analysis, modeling, and workpapers a reviewer can trace	Prepare, analyze	Part II
SQL	Large datasets that span related tables, and procedures that must be repeated	Access, prepare, analyze	Part III
Microsoft Power BI	Interactive reporting for audiences that explore the data themselves	Analyze, visualize, communicate	Part IV

Excel, SQL and Power BI are widely used in practice and need no programming background. Part of becoming an effective analyst is knowing which one fits which task.

The three tools across the workflow

Figure 1.3 · The Three Tools and Their Roles in the Analytics Workflow

SQL handles most of the data access and preparation, Excel dominates preparation and analysis, and Power BI leads in visualization. All three contribute to communicating results.

Check your understanding

Which tool would you reach for first?

1. Join a year of invoices to their shipments and test every line
2. Build a forecasting model a reviewer can trace cell by cell
3. Share a budget report that each manager filters to their own cost center

Ask students to name the tool and the reason, using the table from two slides back.

Choosing the tool

1. SQL: a whole population, joined across related tables
2. Excel: modeling and workpapers a reviewer can trace
3. Power BI: a report its audience explores on its own

The capstone cases of Part V prescribe no fixed order of tools. A step that needs a whole population or a join goes to SQL, a model that needs judgment to Excel, and a result a reader needs to explore to Power BI.

1.6 The Charles River Accounting Dataset

Meet Charles River

• A fictional mid-size home furnishings company in the greater Boston area
• Sells furniture, lighting, textiles and accessories to wholesale and business customers
• Manufactures some product lines and runs a design practice billed by the hour
• One integrated database, used in every chapter of the book

Working with one company means learning one business deeply rather than several superficially. The About the Dataset section of the front matter and the dataset website document every table.

Table groups connect through the general ledger

Figure 1.4 · Charles River High-Level Architecture Diagram

Each business cycle posts its financial activity to GLEntry in the Accounting Core. Shared keys such as ItemID, AccountID, EmployeeID and CostCenterID connect the groups to one another.

Five table groups record the business cycles

Table 1.3 · Charles River Table Groups

Table Group	Primary Accounting Use
Order-to-Cash (O2C)	Revenue analysis, receivables management, design services margin analysis, sales commissions
Procure-to-Pay (P2P)	Purchasing analytics, three-way matching, vendor evaluation, duplicate payment detection
Manufacturing	Standard versus actual cost analysis, production performance, material usage variance
Payroll and Time	Labor cost analysis, payroll compliance testing, workforce analytics
Fixed Assets and Financing	Depreciation analysis, asset lifecycle tracking, debt amortization

Each business cycle has its own table group, and each serves different accounting work. The design services tables sit within Order-to-Cash, giving a service revenue cycle alongside the product cycle.

Four hold the ledger, master data and plans

Table 1.3 · Charles River Table Groups

Table Group	Primary Accounting Use
Accounting Core	Financial reporting, audit analytics, period-close analysis
Master Data	Shared reference data for products, locations, and people across all groups
Organizational Planning	Departmental budgetary control, responsibility accounting, budget-versus-actual analysis
Demand Planning and MRP	Demand forecasting, inventory planning, production planning

The Accounting Core holds the general ledger every cycle posts to; the other three groups supply reference data and plans. The book introduces the groups progressively, from Order-to-Cash and the Accounting Core in the Excel chapters to every group in the capstone cases.

Order-to-Cash, from order to ledger

Figure 1.5 · Simplified O2C Process Flow

Follow the path from the customer’s order to the shipment, the invoice and the cash receipt. Each stage creates its own records, and the shipment, the invoice, the receipt and its application each post to the general ledger.

Every posting names its source

Figure 1.6 · One Ledger Posting Traced to Its Source

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.

Here one revenue credit leads back to its invoice and invoice line; Tutorial 3.2 traces the same invoice through every table it touches.

Watch out: real ERP data is messier

• Charles River is fictional, built to show realistic structures and problems
• Real systems can hold hundreds of tables, custom fields and harder data quality problems
• The skills transfer, but practice calls for more adaptation and judgment

Set expectations early: the dataset is clean enough to learn on, so students should expect real data to need more preparation and more judgment about what it means.

Guided Tutorial 1.1: Exploring the Charles River Database

Explore the workbook in seven steps

1. Open the workbook and browse its worksheets
2. Explore the Customer worksheet
3. Find the link between orders and customers
4. Explore the Item worksheet
5. Explore the chart of accounts
6. Explore the general ledger
7. Look beyond sales and accounting

Students need Excel and CharlesRiver.xlsx from the Downloads page. The goal is familiarity with the tables, not analysis; allow ten to fifteen minutes and point them to the book for each step’s details.

Keys link the worksheets

Figure 1.7 · The Charles River SalesOrder Worksheet

CustomerID is the primary key of the Customer worksheet and a foreign key in SalesOrder, linking each order to the customer who placed it. Chapter 3 studies keys in depth, and Part III uses them in every SQL join.

Checkpoint

• Which four product groups make up the finished goods that Charles River sells?
• Which columns in GLEntry trace each entry back to its source document?
• Which worksheet contains the chart of accounts?

The answers: Furniture, Lighting, Textiles and Accessories; SourceDocumentType, SourceDocumentID and SourceLineID; and the Account worksheet. Students who can answer all three are ready for the exercises.

1.9 Looking Ahead

Five parts, one dataset

Figure 1.8 · Book Organization Map

Part I lays the foundations, Parts II to IV teach one tool each, and Part V’s capstone cases use all three. Each Part introduces more of the Charles River table groups, and a comprehensive case closes each of Parts I to IV.

Summary

• Data growth, automation and AI move accountants toward questions, judgment and communication
• Four types of analytics: descriptive, diagnostic, predictive and prescriptive
• Six stages take a project from a question to a recommendation, sometimes looping back
• SQL extracts, Excel prepares and models, Power BI presents
• Charles River’s table groups post to one ledger that traces back to its sources

Return to the learning objectives and ask students which one they feel least sure about. That is the section to reread before the exercises.

Key terms

Accounting analytics · Analytics workflow · Big data · Business cycle · Business intelligence · Chart of accounts · Data analytics · Descriptive analytics · Diagnostic analytics · Enterprise resource planning (ERP) system · Foreign key · General ledger · Microsoft Excel · Microsoft Power BI · Predictive analytics · Prescriptive analytics · Primary key · Relational database · Source-to-ledger traceability · SQL (Structured Query Language) · SQLite · Table group

Every term is defined at the end of the chapter. Ask students to pick three and explain them to a partner in their own words.

Practice and next steps

• Financial Accounting
  • Exercise 1.1: Mapping Table Groups to the Financial Reporting Cycle
  • Exercise 1.2: Tracing the Revenue Path from Order to Ledger
• Managerial Accounting
  • Exercise 1.3: Evaluating Table Groups for Cost Analysis
  • Exercise 1.4: Framing Production Questions with the Four Types of Analytics
• Auditing
  • Exercise 1.5: Assessing Data Availability for Audit Testing
  • Exercise 1.6: Evaluating Journal Entry Data for Audit Purposes

Next: Chapter 2: Understanding Data in Accounting

Assign one exercise from each perspective. The next chapter looks at accounting data itself, its types and its quality; the controller’s question returns in Chapter 6, answered with Excel.

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/01-role-of-analytics/chapter.html

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