Data and Analytics in Accounting: An Integrated Approach, An Indian Adaptation

Data and Analytics for Accounting: An Integrated Approach, Indian

Adaptation (An Indian Adaptation) by Ann Dzuranin, Guido Geerts,

Margarita Lenk

1 Data and Analytics in the Accounting Profession

1.1 How Are Data and Analytics Transforming the Accounting Profession?

Data and Technology

Analytics and Accounting Professional Practice

Auditing

Financial Accounting

Managerial Accounting

Tax Accounting

Apply It 1.1 Match Data Analytics to Accounting Areas

1.2 What Are the Stages of the Data Analysis Process?

Stage 1: Plan

Understand Motivation

Determine the Objective

Design the Data and Analysis Strategy

Stage 2: Analyze

Prepare Data

Build Information Models

Explore Data

Stage 3: Report

Interpret Results

Communicate Results

MOSAIC: Putting It All Together

Apply It 1.2 Explain the Data Analysis Process for a Fraud Risk Assessment

1.3 What Is a Data Analytics Mindset?

Critical Thinking

Data Literacy

Technology Skills

Communication Skills

Apply It 1.3 Evaluate the Relationships Between Skills and Tasks

1.4 How Is a Data Analytics Mindset Applied?

Understand the Stakeholders

Identify the Purpose

Consider Alternatives

Assess Risks

Identify Knowledge

Perform Self-Reflection

SPARKS: A Critical Thinking Toolkit

Apply It 1.4 Integrate Critical Thinking with the Data Analysis Process

2 Foundational Data Analysis Skills

2.1 How Does Understanding Data Storage Help Answer Questions?

Relational Databases

Joining Tables

Apply It 2.1 Identify Primary and Foreign Keys

2.2 How Do Spreadsheet Functions Analyze Large Amounts of Data?

Basic Functions for Data Analysis

Applying Excel Basic Functions

Apply It 2.2 Analyze Sales Transactions with Excel Functions

2.3 How Do We Organize Data Sets for Analysis?

Using PivotTables

Create a Microsoft Excel PivotTable

Find the Total Balance by Category

Determine Total Count of Assets

Filtering PivotTables

Apply Filter Criteria to the Filter Field Box

Use a Row Auto Filter

Use Slicers to Filter Data

Apply It 2.3 Analyze Sales with Excel PivotTables

2.4 Which Descriptive Measures Help Us Understand Data?

Measures of Location

Mean, Median, and Mode

Calculate Measures of Location

Measures of Dispersion

Variance and Standard Deviation

Calculate Measures of Dispersion

Measures of Shape

Skewness and Kurtosis

Frequency Distributions and Histograms

Descriptive Statistics Tools

Correlation Analysis

Interpret Correlation Coefficients

Perform Correlation Analysis

Apply It 2.4 Use Descriptive Statistics to Audit Warranty Expense

2.5 How Is Visualization Used in Data Analysis?

Making Sense of Large Data Sets

Visualizations and When to Use Them

Common Visualizations

Choosing Visualizations

Microsoft Excel Visualizations

Apply It 2.5 Analyze Product Costs with Data Visualization

How To 2.1 Format and Show Values as Options in PivotTables

How To 2.2 Create a Bar Chart Using Tableau

3 Motivations and Objectives for Data Analysis

3.1 How Does Motivation Inform Objective-Based Data Analysis Questions?

Understanding Motivation

Clear Objectives Lead to Focused Data Analysis Questions

Determine the Objective

Articulate Questions

The Role of Critical Thinking

Apply It 3.1 Link Motivation to Objectives

3.2 What Are Descriptive Objectives?

Develop Descriptive Questions

Descriptive Analyses Examples

Apply It 3.2 Describe Customers’ Buying Behaviors

3.3 What Are Diagnostic Objectives?

Develop Diagnostic Questions

Diagnostic Analyses Examples

Apply It 3.3 Determine the Risk of Material Misstatement of Sales

3.4 What Are Predictive Objectives?

Develop Predictive Questions

Predictive Analyses Examples

Trendlines

Linear Regression

Apply It 3.4 Plan a Sales Trend Analysis

3.5 What Are Prescriptive Objectives?

Develop Prescriptive Questions

Prescriptive Analyses Examples

Linear Optimization

What-If Analyses

Apply It 3.5 Prescribe Optimal Sales Mix

3.6 What Are Data Analytics Motivations and Objectives in Professional Practice?

Accounting Information Systems

Auditing

Financial Accounting

Managerial Accounting

Tax Accounting

Apply It 3.6 Match Motivations to Professional Practice Areas

How To 3.1 Create a Highlight Table in Tableau

How To 3.2 Perform a Regression in Microsoft Excel

4 Planning Data and Analysis Strategies

4.1 How Do Accountants Design Data Analysis Projects?

Create a Data Analysis Project Plan

Step 1: Focus on the Objective

Step 2: Select a Data Strategy

Step 3: Select an Analysis Strategy

Step 4: Consider Risks

Step 5: Embed Controls

Sample Data Analysis Project Plan

Step 1: Focus on the Objective

Step 2: Select the Data Strategy

Step 3: Select the Analysis Strategy

Steps 4 and 5: Data Strategy Risks and Controls

Steps 4 and 5: Analysis Strategy Risks and Controls

Apply It 4.1 Build a Project Plan for Inventory Costs

4.2 What Should We Consider When Selecting Data for Analysis?

Identify Appropriate Data

Evaluate Data Fields and Sources

Consider Data Strategy Risks and Implement Controls

Apply It 4.2 Identify Data Characteristics

4.3 What Should We Consider When Selecting an Analysis?

Designing Analyses to Describe and Diagnose

Descriptive Analysis Strategies

Diagnostic Analysis Strategies

Designing Analyses to Predict and Prescribe

Analysis Strategy Risks and Suggested Controls

Apply It 4.3 Create a Predictive Data Analysis Project Plan

4.4 How Do Data and Analysis Strategies Differ Across Practice Areas?

Accounting Information Systems

Auditing

Financial Accounting

Managerial Accounting

Tax Accounting

Apply It 4.4 Match Strategies with Professional Practice Areas

How To 4.1 Calculate Year-End Bad Debts Estimation Using Excel

How To 4.2 Create a Frequency Bar Chart in Power BI

5 Analysis: Data Preparation

5.1 What Is Data Profiling?

Investigate Data Quality

Correctness

Validity

Consistency

Completeness

Investigate Data Structure

Unambiguous Descriptions

Table Structures That Make Analysis Easier

Data Models That Make Analysis Easier

Decide and Inform

Apply It 5.1 Identify Data Quality Issues

5.2 What Does It Mean to Extract-Transform-Load (ETL) Data?

Extract Data

Transform Data

Cleaning Data

Restructuring Data

Integrating Data

Load Data

Apply It 5.2 Combine Tables for Analysis

5.3 Which Patterns Extract Data?

Data Preparation Pattern 1: Incomplete Data Transfer

Compare Row Counts

Add Missing Rows

Data Preparation Pattern 2: Incorrect Data Transfer

Compare Control Amounts

Modify Incorrect Values

Apply It 5.3 Extract Data with Patterns

5.4 Which Patterns Transform Columns?

Data Preparation Pattern 3: Irrelevant and Unreliable Data

Scan Columns for Irrelevant and Unreliable Data

Remove Columns with Irrelevant or Unreliable Data

Data Preparation Pattern 4: Incorrect and Ambiguous Column Names

Scan Columns for Incorrect or Ambiguous Names

Rename Columns

Data Preparation Pattern 5: Incorrect Data Types

Inspect the Data Type

Change the Data Type

Data Preparation Pattern 6: Composite and Multi-Valued Columns

Scan for Composite and Multi-Valued Columns

Restructure the Data

Data Preparation Pattern 7: Incorrect Values

Detect Incorrect Values with Outliers

Modify Incorrect Values

Data Preparation Pattern 8: Inconsistent Values

Identify Inconsistent Values

Modify Inconsistent Values

Data Preparation Pattern 9: Incomplete Values

Investigate Null Values

Remove the Column or Replace the Null Values

Data Preparation Pattern 10: Invalid Values

Create and Apply Validation Rules

Modify Invalid Values

Apply It 5.4 Use Column Transformation Patterns

5.5 Which Patterns Transform Tables?

Data Preparation Pattern 11: Nonintuitive and Ambiguous Table Names

Scan Tables for Incorrect or Ambiguous Names

Rename Tables

Data Preparation Pattern 12: Missing Primary Keys

Identify Tables with a Primary Key Missing

Create a Primary Key

Data Preparation Pattern 13: Redundant Content Across Columns

Perform Column-By-Column Comparisons

Delete Redundant and Dependent Columns

Data Preparation Pattern 14: Find Invalid Values with Intra-Table Rules

Create and Apply Intra-Table Validation Rules

Modify Invalid Values

Apply It 5.5 Transform Tables with Patterns

5.6 Which Patterns Transform Models?

Data Preparation Pattern 15: Data Spread Across Tables

Identify Similarly Structured Tables/Tables Describing Different Characteristics of the Same

Entity

Combine Tables

Data Preparation Pattern 16: Data Models Do Not Comply with Principles of Dimensional

Modeling

Analyze a Data Model’s Compliance with Dimensional Modeling Principles

Reconfigure the Data Model as a Star/ Snowflake Schema

Data Exploration Pattern 17: Find Invalid Values with Inter-Table Rules

Create and Apply Inter-Table Validation Rules

Modify Invalid Values

Apply It 5.6 Draw a Star Schema

5.7 Which Patterns Apply to Data Loading?

Data Preparation Pattern 18: Incomplete Data Loading

Compare Row Counts

Add Missing Rows

Data Preparation Pattern 19: Incorrect Data Loading

Compare Control Amounts

Modify Incorrect Values

Data Preparation Pattern 20: Missing or Incorrect Data Relationships

Investigate the Completeness and Accuracy of the Data Model

Modify the Data Model

Apply It 5.7 Evaluate Relationships Between Tables

How To 5.1 Profile Data with Power Query

How To 5.2 Merge Tables with Power Query

How To 5.3 Implement Referential Integrity with Microsoft Access

6 Analysis: Information Modeling

6.1 What Is Information Modeling?

The Information Modeling Process

A Structured Approach

From Data Model to Information Model

Create Measures and Dimensions

Apply It 6.1 Complete a Star Schema

6.2 Which Patterns Implement Information Modeling Algorithms?

Information Modeling Pattern 1: Within-Table Numeric Calculation

Information Modeling Pattern 2: Within-Table Text Calculation

Information Modeling Pattern 3: Within-Table Classification

Information Modeling Pattern 4: Across-Table Calculation

Information Modeling Pattern 5: Single-Column Aggregation

Information Modeling Pattern 6: Filtered Aggregation

Information Modeling Pattern 7: Measure Hierarchies

Apply It 6.2 Use Algorithms to Calculate Net Revenue

6.3 Which Patterns Help Develop and Implement Accounting Information Models?

Information Modeling Pattern 8: How Many

Information Model

Analysis

Information Modeling Pattern 9: Participates | Transaction—Who

Information Model

Analysis

Information Modeling Pattern 10: Flows | Transaction—What

Information Model

Analysis

Information Modeling Pattern 11: Occurs | Transaction—When

Information Model

Analysis

Information Modeling Pattern 12: Who-What-When Star Schema

Information Model

Analysis

Information Modeling Pattern 13: Integrated Star Schemas

Information Model

Analysis

Apply It 6.3 Answer Questions with a Star Schema

How To 6.1 Create Calculated Columns and Measures with Power BI

How To 6.2 Implement a Filtered Aggregation with SQL

7 Analysis: Data Exploration

7.1 What Is Data Exploration?

The Process of Data Exploration

Identify Questions

Identify Data Relationships

Explore Data Relationships

Generate Insights

Exploring Data with PivotTables

Fields

Values

Rows and Columns

Filters

Creating Relationships, Filtering, and Dragging and Dropping

Data Exploration Across Tools

Apply It 7.1 Explore Data with PivotTables

7.2 How Are Data Relationships Visualized for Exploration?

Data Exploration Pattern 1: Nominal Comparison

Visualizations

Exploration and Insights

Data Exploration Pattern 2: Distribution

Visualizations

Exploration and Insights

Data Exploration Pattern 3: Deviation

Visualizations

Exploration and Insights

Data Exploration Pattern 4: Ranking

Visualizations

Exploration and Insights

Data Exploration Pattern 5: Part-to-Whole

Visualizations

Exploration and Insights

Data Exploration Pattern 6: Correlation

Visualizations

Exploration and Insights

Data Exploration Pattern 7: Time Series

Visualizations

Exploration and Insights

Data Exploration Pattern 8: Geospatial

Visualizations

Exploration and Insights

Apply It 7.2 Visualize Part-to-Whole Relationships with Excel

7.3 How Are Data Explored by Integrating Data Relationships?

Data Exploration Pattern 9: Composite Trends

Visualizations

Exploration and Insights

Data Exploration Pattern 10: Pareto Analysis

Visualizations

Exploration and Insights

Reports Using Multiple Visualizations

Visualizations

Exploration and Insights

Apply It 7.3 Build an Interactive Report

How To 7.1 Create a Box-and-Whisker Chart with Excel

How To 7.2 Create a Pareto Chart with Excel and Power BI

8 Interpreting Data Analysis Results

8.1 How Do We Draw Conclusions from Data Analysis?

Data Analysis Interpretation Versus Data Exploration

Data Analysis Interpretation

Apply It 8.1 Interpret Revenue Visualization

8.2 What Is the Relationship Between Critical Thinking and Data Analysis Interpretation?

Stakeholders: Understand the Context

Purpose: Define the “Why” of the Analysis

Alternatives: Investigate Other Interpretations

Risks: Consider Data, Analysis, and Bias

Knowledge: What We Need to Know

Self-Reflection: Think About Lessons Learned

Apply It 8.2 Evaluate a Cost Analysis

8.3 How Do We Know the Analysis Makes Sense?

Evaluate the Data and Methods

Examine the Results

Determine If More Information or Analyses Are Necessary

Apply It 8.3 Interpret a Refund Analysis

8.4 How Are Validity and Reliability Determined in Descriptive and Diagnostic Analyses?

Descriptive Analytics

Understand Categories of Data

Summarize by Categories of Data

Identify an Average Observation in the Data

Evaluate the Distribution of Data

Diagnostic Analytics

Find Anomalies

Examine Data Relationships

Identify Patterns

Apply It 8.4 Interpret a Scatterplot for Outliers

8.5 How Are Validity and Reliability Assessed in Predictive and Prescriptive Analyses?

Predictive Analytics

Modeling Relationships

Reliability of the Regression Model

Prescriptive Analytics

What-If Analysis: Scenario Manager

What-If Analysis: Goal Seek

Apply It 8.5 Interpret Regression Results

How To 8.1 Create a Frequency Distribution with Power BI

How To 8.2 Calculate Descriptive Statistics in Microsoft Excel

9 Communicating Data Analysis Results

9.1 How Do We Tell a Data Story?

Develop Data Literacy

Communicate Effectively

Understand the Audience

Focus on the Message

Put It in Context

Strive for Clarity

Tell a Data Story

Data Story Elements

Data Story Structure

Apply It 9.1 Communicate Accounting Information

9.2 What Are the Steps for Creating Effective Data Visualizations?

Verify the Data

Accurate Data

Complete and Consistent Data

Fresh and Timely Data

Consider the Audience

Novice Audiences

Managerial Audiences

Expert Audiences

Executive Audiences

Define the Objective

Apply It 9.2 Match Objectives to Visualization Types

9.3 What Are the Characteristics of Effective Visualizations?

Use Principles of Visual Perception

Continuity

Similarity

Proximity

Focal Point

Consider Preattentive Attributes

Size

Color

Position

Titles

Avoid Clutter

Use Visualization-Specific Best Practices

Apply It 9.3 Evaluate Visualizations

9.4 What Makes Data Visualizations Misleading?

Omitting the Baseline

Manipulating the y-Axis

Going Against Conventions

Selectively Choosing the Data

Using the Wrong Type of Graph

Apply It 9.4 Identify Misleading Data Visualizations

9.5 How Are Data Used in Live Presentations?

Best Practices for Live Presentations

Creating Interactive Data Visualizations

Apply It 9.5 Create an Interactive Visualization

How To 9.1 Create a Dashboard in Tableau

How To 9.2 Create an Interactive Dashboard in Tableau

10 Recent Data and Analyses Developments in Accounting

10.1 Which Data Trends Are Impacting Accounting Practice?

Data Volume

Increases in Data Volume

Developments in Data Storage

Data Variety

Data Velocity

Data Veracity

Data Value

Data Ethics

Privacy Laws

Ethical Standards and Privacy Policies

Ethics and the Effects of Data Velocity and Volume

Apply It 10.1 Identify Data Characteristics

10.2 Which Recent Analyses Developments Are Accountants Adopting?

Value Creation

Data Mining and Smart Contracts

Data Mining

Smart Contracts

Process Automation and Process Mining

Robotic Process Automation

Process Mining

Continuous Auditing

Textual Analysis

Cognitive Technologies

Use of Generative AI in Accounting

What Is Generative AI?

The Transformative Impact of AI in Accounting

How Can Accounting Firms Leverage AI?

Is Artificial Intelligence an Opportunity or a Threat to Accountants?

Apply It 10.2 Use Process Automation to Analyze Financial Statements

10.3 How Are Data and Analyses Developments Impacting the Professional Practice Areas?

Accounting Information Systems

Auditing

Financial Accounting

Managerial Accounting

Tax Accounting

Apply It 10.3 Match Professional Practice Areas to Analyses and Technologies

How To 10.1 Create a Comparative Income Statement in Excel

How To 10.2 Use Process Automation to Analyze Product Contributions to Net Income

DATA ANALYTICS AND DECISION-MAKING

GLOSSARY

INDEX