D552 Data Analytics for Accountants I Review

Data Analytics

the process of evaluating data with the purpose of drawing conclusiions to address business questions

a way to search through large structured and unstructured data to identify unknown patterns or relationships

What does data analytics provide?

Untructured Data

data that do not adhere to a predefined data model in a tabular format

Structured Data

data that are organized and reside in a fixed field with a record or a file, readily searchable by search algorithms

Big Data

datasets that are too large and complex for businessses existing systems to handle utilizing their traditional capabilities to capture, store, manage, and analyze these datasets

Volume, Velocity, Veracity, and Variety

What are the 4 V's of big data?

Volume

the sheer size of the data

Velocity

the speed of data processing

Variety

the number of the types of different data q

Veracity

the underlying quality of the data

discover the various buying patterns of their customers, investigate anomalies that were not anticipated, forecast future possibilities, etc.

Companies use data analysis to:

$2 million

The study from McKinsey Gobal institute estimated that data analytics and technology could generate how much in value per year?

1. Audits must better embrace technology

2. Technology will enhance the aulity, transparency, and accuracy of the audit

According to Forbes Insights/KPMG's report "Audit 2020: A focus on change", the vast majority of respondents believe that:

1) asked questions by management

2) find data to address those questions

3) analyze the data

4) report the results to management to aid in their decision making

Responsibilities of Management Accountants:

integrating the strategy with the IT department and gaining access to available technology tools

What do 29% of tax departments find the biggest challenge related to executing an analytics strategy?

Isson and Harriot

Who created the IMPACT cycle?

1) I - Identify the Questions

2) M - master the data

3) P - perform test plans

4) A - address and refine results

5) C - communicate insights

6) T - track outcomes

Steps to the IMPACT cycle?

Audience, Scope, and Use

What attributes should be considered when identifying the question?

everythign about the data, including how to access, availability, reliability, frequency of updates, what time periods are covered to make sure data coincide with the timing of the business problem and so on.

To master the data, you should know:

- reviewing data availability in a firm's internal systems

- reviewing data availability in a firm's external network

- examine data dictionaries and other contextual data

- evaluate and perform ETL processes and assess the time required to complete

- assess data validation and completeness to provide a sense of reliability

- evaluate and perform data normalization - to reduce data redundancy and improve data integrity

- evaluate and perform data preparation and scrubbing

To give some idea about the data questions, you may want to consider:

between 50% and 90%

How much time to data analytics professionals estimate they spend cleaning the data so it can be analyzed?

- the research question

- the model

- the data availability

- the expected statistical inference

Characeristics that might suggest use of different data approaches are:

Similarity Matching

a data approach that attempts to identify similar individuals based on data known about them

Link Prediction

attempts to predict a relationship between 2 items

- the use of executive summaries

- static reports

- digital dashboards

- data visualizations

Ways to communicate results of data analysis:

1) clearly articulate the business problem the company is facing

2) communicate with data scientists about specific data needs and understand the underlying quallity of the data

3) draw appropriate conclusions to the business problem based on the data and make recommendations on a timely basis

4) present their results to indivudal members of management (CEO's, audit managers, etc.) in an accessible manner to each member

Accountants must know how to (in relation to data analytics):

1) Developed Analytics Mindset

2) Data Scrubbing and Data Presentation

3) Data Quality

4) Descriptive Data Analysis

5) Data Analysis through Data Manipulation

6) Statistical Data Analysis Competency

7) Data Visualization and Data Reporting

What skills should analytic-minded accountants have?

Developed Analytics Mindset

know when and how data analytics can address busiuness questions

Data Scrubbing and Data Presentation

comprehend the process needed to clean and prepare the data before analysis

Data Quality

recognize what is meant by data quality, be it completeness, reliability, or validity

Descriptive Data Analysis

perform basic analysis to understand the quality of the underlying data and its ability to address the business question

Data Analysis through Data Manipulation

demonsrate ability to sort, rearrange, merge, and reconfigure data in a manner that allows enhanced analysis

Statistical Data Analysis Competency

identify and implement an approach that will use statistical data analysis to draw conclusions and make recommendations on a timely basis

Data Visualization and Data Reporting

report results of analysis in an accessible way to each varied decision maker and his or her specific needs

- Excel

- Power Query

- Power BI

- Power Automate

Microsoft offerings for data analytics and business include:

Excel

the most ubiquitous spreadheet software and most commonly used for business analysis

Power Query

a tool built into Excel and Power BI Desktop that lets Excel connect to a variety of different data sources

Power BI

an analytic platform that enables generation of simple or advanced Data Analytics models and visualizations that can be compiled into dashboards for easy sharing with relevant stakeholders

Power Automate

a tool that leverages robotics process automation (RPA) to automate routine tasks and workflows, such as scraping and collecting data from nonstructured sources, including email or other online services

- Tableau Prep

- Tableau Desktop

- Tableau Public

Tableau Offerings for Data Analytics:

Tableau Prep

used for data combination, cleaning, manipulation, and insights; it enables users to interact with data and quickly identfy data quality issues with a clear map of steps performed so others can review

Tableau Desktop

can be used to generate basic to advanced data analytics models and visualzations with an easy-to-use drag-and-drop interface

Tableau Public

a free limited edition of Tableau Desktop that is specfically tailored to sharing and analyzing public datasets

Data Dictionary

centralized repository of descriptions for all of the data attributes of the dataset

an accounting information system, supply chain management system, customer relationship management system, and human resource management system

Internal Data Sources include:

Accounting Information Systems

a system that records, processes, reports, and communicates the results of business transactions to provide financial information for decision-making purposes

Enterprise Resource Planning (ERP)

a category of business management software that integrates applications from throughout the business (such as manufacturing, accounting, HR, finance, etc.) into 1 system

Supply Chain Management System

an information system that helps manage all the company's interactions with suppliers

Customer Relationship Management System

an information system for managing all interactions between the company and its current and potential customers

Human Resource Management System

an information system for managing all interactions between the company and its current and potential employees

economic, financial, governmental, and other sources

Categories of External Sources:

either a flat file or a database

Where are data most commonly stored?

Relational Database Management Systems

a variety of applications that support relational databases

Relational Database

a means of storing data in order to ensure that the data are complete, not redundant, and to help enforce business rules

a normalized database

Where should structured data be stored?

- completeness

- no redundancy

- business rules enforcement

- communication and integration of business processes

Benefits of storing data in a normalized, relational database:

- primary key

- foreign key

- and descriptive attributes

What are the three types of columns?

Primary Key

an attribute that is required to exist in each table of a relational database and serves as the "unique identifier" for each record in a table

Foreign Key

an attribute that exists in relational databases in order to carry out the relationship between two tables

Descriptive Attibutes

attrubutes that exist in relational databases that are neither primary nor foreign keys; provide business information, but are not required to build a database

ETL

extract, transform, and load process that is integral to mastering the data

1) determining the purpose and scope of the data request

2) obtaining the data

3) validating the data for completeness and integirty

4) cleaining the data

5) loading the data for analysis

Steps of ETL Process:

to alleviate the headaches associated with data requests by serving as a guide to standardize these requests and specify the format an auditor desires from the company being audited

The aim of the Audit Data Standards (ADS) is:

1) order-to-cash sub ledger standards

2) procure-to-pay subledger standards

3) inventory subledger standards

4) general ledger standards

Auditing Data Standards include:

Data Request Form

a method for obtaining data if you do not have access to obtain the data directly yourself

Structured Query Language (SQL)

a computer language to interact with data (tables, records, and attributes) in a database by creating, updating, deleting, and extracting

1) compare the number of records that were extracted to the number of records in the source database

2) compare descriptive statistics for numeric fields

3) validate date/time fields

4) compare strong limits for text fields

4 Steps to validate data after extraction:

1) remove headings or subtotals

2) clean leading zeros and nonprintable characters

3) format negative numbers

4) correct inconcsistencies accrosss data, in general

Common ways that data need to be cleaned after extraction and validation:

loading

What should eb the simplest step of the ETL process?

1) How does the company use data, to what extent are they integrated into firm strategy?

2) Does the company send a privacy notice to individuals when their personal data are collected?

3) Does the company assess the risks linked to the specific type of data the company uses?

4) Does the company have safeguards in place to mitigate the risks of data misuse?

5) Does the company have the appropriate tools to manage the risks of data misuse?

6) Does our company conduct appropriate due dilligence when sharing with or acquiring data from third parties?

6 questions that allow a business to create value from data use and analysis, and still protect the privacy of stakeholders:

Descriptive Analytics

procedures that summarize existing data to determine what has happened in the past (helps you understand what happened)

Diagnostic Analytics

procedures that explore the current data to dertemine why something has happened the way it has, typically comparing the data to a benchmark (helps undderstand why it happened)

Predictive Analytics

procedures used to generate a model that can be used to determine what is likely to happen in the future (estimate a future category)

Prescriptive Analytics

procedures that work to identify the best possible options given constraints or changing conditions (make a recommendation for a course of action)

summary statistics and data reduction

Types of Descriptive Analytics include:

profiling, clustering, similarity matching, and co-occurrence grouping

Types of Diagnostic Analytics include:

regression, classification, and link prediction

Types of Predictive Analytics include:

decision support, and machine learning & AI

Types of Prescriptive Analytics include:

Summary Statistics

describe the location, spread, shape, and dependence of a set of observations

Data Reduction

a data approach that attemps to reduce the amount of information that needs to be considered to focus on the most critical items (highest cost, highest risk, largest impact, etc.)

1) identify the attribute you would like to reduce or focus on

2) filter the results

3) interpret the results

4) follow up on results

Steps of data reduction:

XBRL (eXtensible Business Reporting Language)

a global standard for exchanging financial reporting information that uses XML (facilitates exchange of FR between company and SEC)

Profiling

attempts to characterize "typical behavior" of a group

Clustering

a data approach that attempts to divide individuals (like customers) into groups in a useful and meaningful way

Co-occurrence Grouping

a data approach that attempts to discover associations between individuals based on transactions involving them

Regression

a data approach that attempts to estimate or predict, for each unit, the numerical value of some variable using some type of statistical model

Classification

a data approach that attempts to assign each unit in a population into a few categories potentially to help with predictions

Interquartile Range (IQR)

a measure of variability; to calculate, the data are first divided into 4 parts (quantities) and the two middle quantities that surround the median are the IQR

1) rank order your data first (the same as you would do to find the median or to find the range)

2) Quartile 1: the lowest 25% of observation

3) Quartile 2: the next 25% of observation - its cutoff is the median

4) Quartile 3: begins at the median and extends to the third 25% of observations

5) Quartile 4: the highest 25% of observations

6) the interquartile range includes quartile 2 and 3

Steps taken to create quartities and identify the IQR:

Benford's Law

the principle tha in any large, randomly produced set of natural numbers, there is an expected distribution of the first, or leading, digit with 1 being the most common, 2 the next most, and down successively to the number 9

Unsupervised Approach

appraoch used for data exploration looking for potential patterns of interest

Null Hypothesis

an assumption that the hypothesized relationship does not exist, or that there is not significant difference between two samples or populations

Alternative Hypothesis

the case that the analyst believes to be true

Target

an expected attribute or value that we want to evaluate

Class

a manually assigned category applied to a record based on an event

Supervised Approach

approach used to learn more about the basic relationships between independent and dependent variable that are hypothesized to exist

1) identify the variables that might predict an outcome

2) determine the functional form on the relationship

3) identify the parameters of the model

4)evaluate the goodness of fit

Regression analysis process:

Dummy Variable

a number value (0 or 1) to represent categorical data in statistical analysis; 1 = presence of something; 0 = absences of soemthing

Time Series Analysis

a predictive analytics technique used to predict future values based on past values of the same variable

1) identify the classes you wish to predict

2) manually classify an existing set of records

3) select a set of classficiation models

4) divide your data into training and testing sets

5) generate your model

6) interpret the results and select the "best" model

Steps of Classification: