Introduction to Data Analytics for Accounting, Second Edition
Vernon J. Richardson, Katie L. Terrell and Ryan A. Teeter
Table of Contents
Chapter 1
Ask the Question: Using Data Analytics to Address
Accounting Questions 2
The Explosion of Data and the Impact on the
Accounting Profession 4
Accountants Need to Develop Critical Thinking
Skills 5
Data Analytics And The AMPS Model 6
The AMPS Model: Ask the Question 8
The AMPS Model: Master the Data
(Chapters 2–4) 9
The AMPS Model: Perform the Analysis (Chapters
5–9) 10
The AMPS Model: Share the Story
(Chapter 10) 10
The Recursive Nature of the AMPS Model 10
Using Visualizations to Analyze Data and
Communicate Results 11
Software Tools Available to Perform Data
Analytics 14
Summary 15
Key Words 15
Answers to Progress Checks 16
Multiple Choice Questions 16
Discussion Questions 18
Brief Exercises 18
Problems 20
Labs Associated with Chapter 1 22
Lab 1-1 Excel: Journal Entries to Trial
Balance 22
Lab 1-2 Excel: Calculating Depreciation Using
Excel Functions 30
Lab 1-3 Excel: Creating a Mortgage
Amortization Schedule 34
Chapter 2
Master the Data: An Introduction to Accounting
Data 42
Data, Data Analytics, and Accounting
Questions! 44
Master The Data: The Second Step of the AMPS
Model 45
What is Big Data? 46
Accounting Data Sources 48
Financial Accounting Data 48
Financial Accounting-Related Data 51
Managerial Accounting Data 54
Tax Data 57
Non-Accounting Data Sources 57
Data Ethics 61
Gathering Data 62
Protecting Data 63
Some Excel Basics: The Pivottable 63
Summary 67
Key Words 67
Answers to Progress Checks 69
Multiple Choice Questions 70
Discussion Questions 71
Brief Exercises 72
Problems 73
Labs Associated with Chapter 2 76
Lab 2-1 Excel: Accounts Receivable Summary
by Customer 76
Lab 2-1 Tableau: Accounts Receivable Summary
by Customer 81
Lab 2-1 Power BI: Accounts Receivable
Summary by Customer 86
Lab 2-2 Excel: Inventory Management by
Customer Profitability 91
Lab 2-2 Tableau: Inventory Management by
Customer Profitability 95
Lab 2-2 Power BI: Inventory Management by
Customer Profitability 99
Lab 2-3 Excel: Inventory Management by SKU
Profitability 102
Lab 2-3 Tableau: Inventory Management by
SKU Profitability 108
Lab 2-3 Power BI: Inventory Management by
SKU Profitability 115
Chapter 3
Master the Data: Data Types Used in
Accounting 120
Examples of Data Types 122
Introduction to Structured Data Types: Categorical
versus Numerical 122
Additional Ways to Categorize Data Based on
Tools 127
Analyzing Data Using Both Categorical and
Numerical Variables in a Pivottable 128
Accounting Data, Data Types, and Accounting
Databases 130
Simplified Product Tables 131
Data Dictionaries and Data Catalogs 132
Summary 134
Key Words 134
Answers to Progress Checks 134
Multiple Choice Questions 135
Discussion Questions 136
Brief Exercises 137
Problems 138
Labs Associated with Chapter 3 139
Lab 3-1 Excel: Identify and Work with Different
Data Types 139
Lab 3-1 Tableau: Identify and work with
Different Data Types 144
Lab 3-1 Power BI: Identify and Work with
Different Data Types 149
Lab 3-2 Excel: Visualize Different Data Types 153
Lab 3-2 Tableau: Visualize Different Data
Types 158
Lab 3-2 Power BI: Visualize Different Data
Types 164
Chapter 4
Master the Data: Preparing Data for Analysis 172
What are the Differences among a Database, Excel,
and Data Visualization Tools (Tableau and Power
BI)? 174
Relational Databases 174
Relational Database Data Dictionaries And Entity-
Relationship Diagrams 176
Relational Database Data Dictionary 176
Relational Database Diagrams 177
Data Storage: Advantages of Using Relational
Databases 178
Data Integrity Benefits of Storing Data in
Relational Databases 178
Internal Control Benefits of Storing Data in
Relational Databases 178
Extract, Transform, and Load: Using Excel, Power
BI, Tableau, and Query Tools to Access Data in
Company Databases 179
Extract, Transform, and Load 179
Extract: Connecting to Data in Excel 180
Extract: Connecting to Data in Tableau 182
Extract: Connecting to Data in Power BI 183
Extract and Transform: Connecting to a Subset of
Data from a Database Using SQL 185
Extract, Transform, and Load: Using Excel Query
Tools to Access Data in Databases External to the
Company 187
Obtaining Data from the Web through Excel 187
Summary 191
Key Words 192
Answers to Progress Checks 192
Multiple Choice Questions 194
Discussion Questions 195
Brief Exercises 195
Problems 197
Labs Associated with Chapter 4 198
Lab 4-1 Excel: Working with Data in Ranges and
Tables 198
Lab 4-2 Excel: Linking Two Tables Using
VLOOKUP for State Tax Rates 205
Lab 4-3 Excel: Linking Two Tables Using
VLOOKUP for Relational Data 209
Lab 4-4 Excel: Linking Tables with a Model 213
Lab 4-4 Tableau: Linking Tables with a
Model 221
Lab 4-4 Power BI: Linking Tables with a
Model 233
Appendix 4A SQL Queries 242
Chapter 5
Perform the Analysis: Types of Data Analytics 256
The Next Step of the AMPS Model: Perform the
Analysis 258
Matching the Analytics with the Accounting
Question 258
Descriptive Analytics 260
Statistical and Summarization Tools for Descriptive
Analytics 260
Examples of Descriptive Analytics 261
Diagnostic Analytics 262
Identify Anomalies/Outliers 262
Finding Previously Unknown Linkages, Patterns, or
Relationships Between and Among Variables 263
Predictive Analytics 264
Prescriptive Analytics 265
Summary of Analyses used to Address Accounting
Questions 267
A Review of Basic Statistics and Hypothesis
Testing 268
Population vs. Sample 268
Parameters vs. Statistics: What Is the Difference? 269
Describing the Sample by Its Central Tendency, the
Middle, or the Most Typical Value 269
Describing the Spread (or Variability) of the Data 269
Probability Distributions 270
Hypothesis Testing 271
Statistical Testing 272
Statistical Test of a Difference of Means of Two
Groups 273
Interpreting the Statistical Output from a Regression 274
Introduction to Tools used in Data Analytics 275
Perform the Analysis Using Microsoft Excel
Tools/Functions 275
The Excel Data Analysis Toolpak 275
Summary 277
Key Words 278
Answers to Progress Checks 279
Multiple Choice Questions 280
Discussion Questions 282
Brief Exercises 282
Problems 283
Labs Associated with Chapter 5 286
Lab 5-1 Excel: Descriptive Statistics for the
Retail Industry 286
Lab 5-1 Tableau: Descriptive Statistics for the
Retail Industry 291
Lab 5-1 Power BI: Descriptive Statistics for the
Retail Industry 295
Lab 5-2 Excel: Using Conditional Formatting to
Perform Bank Reconciliations 299
Chapter 6
Perform the Analysis: Descriptive Analytics 304
Defining Descriptive Analytics 306
Accounting Data used in Descriptive Analytics 306
Tools And Techniques Used In Descriptive Analytics 307
Examples of Descriptive Analytics 309
Descriptive Analytics of Financial Performance
Using Tables and Graphs 309
Considering the Right Comparison Group for
Analysis 310
Descriptive Analysis Using PivotTables and Bar
Charts for Accounts Receivable Aging 311
Horizontal, Vertical, and DuPont Analysis of
Financial Performance 313
Using Descriptive Analytics to Identify Phenomena
That Might Require Additional Analysis, Including
Diagnostic Analytics 317
Summary 319
Key Words 320
Answers to Progress Checks 320
Multiple Choice Questions 321
Discussion Questions 323
Brief Exercises 324
Problems 325
Labs Associated with Chapter 6 328
Lab 6-1 Excel: Accounts Receivable Aging 328
Lab 6-1 Tableau: Accounts Receivable
Aging 334
Lab 6-1 Power BI: Accounts Receivable
Aging 339
Lab 6-2 Excel: Horizontal Analysis of Financial
Performance with Sparklines 345
Lab 6-3 Excel: Vertical Analysis of Financial
Performance (with Sparklines) 351
Lab 6-4 Excel: DuPont Analysis of Financial
Performance 355
Chapter 7
Perform the Analysis: Diagnostic Analytics 360
Defining Diagnostic Analytics 362
Identifying Anomalies and Outliers 363
Diagnostic Analytic Techniques for Identifying
Anomalies and Outliers 364
Finding Previously Unknown Linkages, Patterns, or
Relationships Between and Among Variables 374
Performing Drill-Down, Detailed Analytics 374
Determining Statistical Linkages, Patterns and
Relationships Among Variables Using Statistical
Tools and Techniques 376
Hypothesis Testing Using a Difference in Means 377
Hypothesis Testing Using Regression 378
Summary 380
Key Words 380
Answers to Progress Checks 381
Multiple Choice Questions 382
Discussion Questions 384
Brief Exercises 385
Problems 386
Labs Associated with Chapter 7 388
Lab 7-1 Excel: Test of Separation of Duties 388
Lab 7-1 Tableau: Test of Separation of Duties 392
Lab 7-1 Power BI: Test of Separation of
Duties 394
Lab 7-2 Excel: Days of the Week Journal
Transactions 397
Lab 7-2 Tableau: Days of the Week Journal
Transactions 402
Lab 7-2 Power BI: Days of the Week Journal
Transactions 405
Lab 7-3 Excel: Using the MATCH() Function to
Perform Bank Reconciliations 408
Lab 7-4 Excel: Benford’s Law 413
Lab 7-5 Excel: Fuzzy Matching and Fake
Employees/Vendors 420
Lab 7-6 Excel: Sequence Check: Identifying
Missing Checks 425
Lab 7-7 Excel: Duplicate Payments 429
Lab 7-8 Excel: Looking for Fraud by Examining
Relationships within a Data File: Accounts
Payable Clerks and Company Vendors 434
Lab 7-8 Tableau: Looking for Fraud by
Examining Relationships within a Data
File: Accounts Payable Clerks and
Company Vendors 441
Lab 7-8 Power BI: Looking for Fraud by
Examining Relationships within a Data
File: Accounts Payable Clerks and
Company Vendors 452
Lab 7-9 Excel: Evaluating the Relationship between
Sales and Advertising Expense 458
Chapter 8
Perform the Analysis: Predictive Analytics 464
Introduction to Predictive Analytics 466
Classification 467
Bankruptcy Classification 468
Loan Extension Classification 470
Fraud/No Fraud Classification 472
Regression 473
Base Rates and Base Rate Fallacy 475
Forecasting Future Performance using Time Series
Analysis 476
Predictive Analytics and Hypothesis Testing 478
Predictive Analytics and Machine Learning 479
Summary 480
Key Words 480
Answers to Progress Checks 480
Multiple Choice Questions 481
Discussion Questions 483
Brief Exercises 484
Problems 485
Labs Associated with Chapter 8 487
Lab 8-1 Excel: Predicting Bankruptcy Using
Altman’s Z 487
Lab 8-2 Excel: Classifying Loan Acceptance
Using Lending Club Data 493
Lab 8-3 Excel: Estimating Cost Behavior Using
Regression Analysis 498
Lab 8-4 Excel: Estimating Activity-Based Costing
Drivers Using Regression Analysis 504
Lab 8-5 Excel: Estimating Borrower Interest
Rates Using Regression Analysis with
Lending Club Data 513
Lab 8-6 Excel: Forecasting Future Performance
(Sales and Earnings for IBM and
Netflix) 520
Lab 8-7 Tableau: Forecasting Future
Performance (Sales and Earnings for
IBM and Netflix) 524
Lab 8-8 Power BI: Forecasting Future
Performance (Sales and Earnings for
IBM and Netflix) 532
Chapter 9
Perform the Analysis: Prescriptive Analytics 536
Linking Back to the AMPS Model 538
Definition of Prescriptive Analytics 538
Constraints 539
Changing Conditions 539
Prescriptive Analytics Techniques 539
Marginal Analysis 540
Make-or-Buy Analysis: Making Outsourcing
Decisions 541
Cash Flow Analysis 542
Accounting Rate of Return and Payback Period 542
Net Present Value and Internal Rate of Return 542
Evaluating Future Cash Flows: Net Present Value
and Installment Payments 544
Evaluating Future Cash Flows: Capital Budgeting
and Investment Decisions 546
Goal Seek Analysis 551
Scenario Analysis 552
An Example of Scenario Analysis Using Potential Tax
Rate Scenarios 552
Sensitivity Analysis 553
Optimization 555
Summary 555
Key Words 555
Answers to Progress Checks 556
Multiple Choice Questions 557
Discussion Questions 560
Brief Exercises 560
Problems 562
Labs Associated with Chapter 9 565
Lab 9-1 Excel: Lump Sum or Annuity? 565
Lab 9-2 Excel: Evaluating Investments Using
NPV 570
Lab 9-3 Excel: Capital Budgeting Using NPV 573
Lab 9-4 Excel: Evaluating Investments Using
IRR 576
Lab 9-5 Excel: Capital Budgeting Using IRR 579
Lab 9-6 Excel: Face, Discount, or Premium? 582
Lab 9-7 Excel: What-If Analysis with Goal Seek/
Breakeven 588
Lab 9-8 Excel: What-If Analysis with Goal Seek/
Final Exam Grade 592
Lab 9-9 Excel: What-If Scenario/ Tax Rates 596
Chapter 10
Share the Story 600
The Basics of Data Visualization 602
Visualizing Descriptive Statistics and Analytics 604
Presenting Data in a Dashboard 607
Bar Charts versus Histograms 608
Visualizing Diagnostic Statistics and Analytics:
Outliers and Anomalies 610
Exploratory Diagnostic Analytics Using Data
Visualization 611
Visualizing Predictive Statistics and Analytics 612
Correlation and Regression 612
Forecasting with Time Series Data 613
Visualizing Prescriptive Statistics and Analytics 614
Sensitivity Analysis 614
Breakeven Analysis 615
Communicating your Data with Words: Executive
Summaries and Reports 616
Summary 617
Key Words 617
Answers to Progress Checks 617
Multiple Choice Questions 619
Discussion Questions 620
Brief Exercises 620
Problems 621
Labs Associated with Chapter 10 623
Lab 10-1 Excel: Create a Dashboard Using
PivotTables and Slicers 623
Lab 10-2 Tableau: Create a Dashboard 631
Lab 10-2 Power BI: Create a Dashboard 642
Chapter 11
Capstone Projects Using the AMPS Model 646
Using the AMPS Model to Address Accounting
Questions 648
Application of the AMPS Model to Your Own
Project(S) 648
Project 1: Using the AMPS Model to Address the
Question of Loan Repayment 648
Ask the Question 649
Master the Data 649
Perform the Analysis 652
Share the Story 652
Project 2: Completing Your Own Project Using the
AMPS Model 653
*Chapter 12
Financial Statement Analysis
Define Financial Statement Analysis
Ask the Financial Statement Analysis Question
Master the Data: Data Sources Used in Financial
Statement Analysis
Primary Data Sources for Financial Statement
Analysis
Perform the Analysis
Descriptive Financial Statement Analytics
Examples of Descriptive Analytics in Financial
Statement Analysis: Ratio Analysis
Diagnostic Financial Statement Analytics
Anomalies and Outliers: Comparisons to Appropriate
Benchmarks
Drill-Down Analytics to Determine Relations,
Patterns, and Linkages between Variables
Predictive Financial Statement Analytics
Regression: Predicting Market Valuation of Equity
with Net Income or Operating Cash Flows
Time Series: Predicting Levels of Business
Interruption Loss
Prescriptive Financial Analytics
Relative Market Valuation Based on Valuation of
Other Companies
Discounted Cash Flow Analysis Using Analysts’
Forecasts
Report the Results
Reporting Descriptive and Diagnostic Analytics:
Management Discussion and Analysis of Annual
Report/10-K
Reporting Predictive Analytics: Analysts’ Research
Reports, Revenue, and Earnings Forecasts
Reporting Prescriptive Analytics: Sensitivity
Analysis
Summary
Key Words
Answers to Progress Checks
Multiple Choice Questions
Discussion Questions
Brief Exercises
Problems
Labs Associated with Chapter 12
Lab 12-1 Excel: Descriptive Analytics: Ratio
Analysis
Lab 12-2 Excel: Diagnostic Analytics: Common
Size Financial Statements
Lab 12-3 Excel: Diagnostic Analytics: Find the
Unknown Company
Lab 12-4 Excel: Predictive Analytics: Net Income,
Cash Flows, and Market Value Prediction
Lab 12-5 Excel: Predictive Analytics: Amounts,
Timing, and Uncertainty: Business
Interruption Loss
Lab 12-6 Excel: Prescriptive Analytics: Relative
Market Valuation
Lab 12-7 Excel: Prescriptive Analytics: Valuing
a Company Using Yahoo! Analysts’
Forecasts
Appendix 1: Analysts’ Forecasts and Current
Stock Price for Netflix (as of
3/9/2022)
*Chapter 13
Managerial Accounting Analytics
The Role of the Management Accountant and their
use of Data Analytics
Ask the Managerial Accounting Questions
Master the Data: Data Sources Useful in
Managerial Accounting
Internal Sources of Cost Accounting Data
External Sources of Cost Accounting-Related
Data
Example of Data Sources Needed to Address
Management Questions
Perform the Analysis
Descriptive Managerial Accounting Analytics
Diagnostic Managerial Accounting Analytics
Anomalies and Outliers: Comparisons to Appropriate
Benchmarks
Drill-Down Analytics to Determine Relations,
Patterns, and Linkages between Variables
Examples of Diagnostic Analytics in Managerial
Accounting Analytics to Assess Cost Behavior
Predictive Managerial Accounting Analytics
Managerial Accounting Analytics Using Either
Regression Analysis or Time Series Analysis
Prescriptive Managerial Accounting Analytics
What-If Analysis
Sensitivity Analysis
Optimization
Steps in Setting Up an Optimization Model
Optimizing Price (Pricing Decisions)
Report the Results
Reporting Prescriptive Analytics: Sensitivity
Using a Heat Map to Communicate Sensitivity to
Inputs
Reporting Prescriptive Analytics: Using Conditional
Formatting to Highlight Variances
Summary
Key Words
Answers to Progress Checks
Multiple Choice Questions
Discussion Questions
Brief Exercises
Problems
Labs Associated with Chapter 13
Lab 13-1 Excel: Descriptive Analytics:
Evaluating Inventory Using Inventory
Turnover and Waste
Lab 13-2 Excel: Diagnostic Analytics: Variance
Calculation and Conditional Formatting
Lab 13-3 Excel: Predictive Analytics:
Forecasting Product Demand Using
Time Series Analysis
Lab 13-4 Tableau: Predictive Analytics:
Forecasting Product Demand Using
Time Series Analysis
Lab 13-5 Power BI: Predictive Analytics:
Forecasting Product Demand Using
Time Series Analysis
Lab 13-6 Excel: Predictive Analytics:
Forecasting Product Demand Using
Regression
Lab 13-7 Excel: Prescriptive Analytics:
Profitability Scenarios Using Excel’s
Data Table
Lab 13-8 Excel Prescriptive Analytics: Price
Optimization
*Appendix A
Excel Tutorial (Formatting, Sorting, Filtering,
and PivotTables)
*Appendix B
Tableau Tutorial
*Appendix C
Power BI Desktop
INDEX I