Download Solution manual for data analytics for accounting 3rd edition richardson.pdf and more Study Guides, Projects, Research Advanced Education in PDF only on Docsity!
Solution Manual For
Data Analytics for Accounting, 3rd Edition Richardson
Chapter 1-
Solutions Manual – Chapter 1
Solutions to Multiple Choice Questions
1. (LO 1-1) Big Data is often described by the four Vs, or
a. volume, velocity, veracity, and variability.
b. volume, velocity, veracity, and variety.
c. volume, volatility, veracity, and variability.
d. variability, velocity, veracity, and variety.
Answer: b
2. LO 1-4) Which data approach attempts to assign each unit in a population into a small set of
classes (or groups) where the unit best fits?
a. Regression
b. Similarity matching
c. Co-occurrence grouping
d. Classification
Answer: d
3. (LO 1-4) Which data approach attempts to identify similar individuals based on data known
about them?
a. Classification
b. Regression
c. Similarity matching
d. Data reduction
Answer: c
4. (LO 1-4) Which data approach attempts to predict connections between two data items?
a. Profiling
b. Classification
c. Link prediction
d. Regression
Answer: c
5. (LO 1-6) Which of these terms is defined as being a central repository of descriptions for all of
the data attributes of the dataset?
a. Big Data
b. Data warehouse
c. Data dictionary
d. Data Analytics
Answer: c
6. (LO 1-5) Which skills were not emphasized that analytic-minded accountants should have?
a. Developed an analytics mindset
b. Data scrubbing and data preparation
c. Classification of test approaches
d. Statistical data analysis competency
Answer: c
7. (LO 1-5) In which areas were skills not emphasized for analytic-minded accountants?
a. Data quality
b. Descriptive data analysis
c. Data visualization and data reporting
d. Data and systems analysis and design
Answer: d
8. (LO 1-4) The IMPACT cycle includes all except the following steps:
a. perform test plan.
b. visualize the data.
c. master the data.
d. track outcomes.
Answer: b
9. (LO 1-4) The IMPACT cycle specifically includes all except the following steps:
a. data preparation.
b. communicate insights.
c. address and refine results.
d. perform test plan.
analyzing the company’s data similar to the way a business analyst would help management make better business decisions. In this way, data analytics offers value to the audit function.
- There are many potential answers. For example, data analytics associated with financial reporting may help accountants determine if any of their inventory obsolete? It may also help the company benchmark on the financial statements and financial reporting of other similar companies and understand their accounting practices to help infer their own.
- Management accountants address the information needs of management. They will often see what questions management has, find applicable data to address those questions, conduct analysis of the data, and report the results to management to help them make data-driven decisions. This is consistent with the data analytics process and the IMPACT model.
- The IMPACT cycle suggests an order of 1) Identifying the Questions; 2) Mastering the Data; 3) Performing the test plan; 4) Addressing and refining results; 5) Communicating insights and 6) Tracking outcomes. The cycle starts with a question and then identifying data and test plan that might address that question. The results of the data analysis are communicated and tracked which may lead to additional, possibly more refined questions that then restart the cycle.
- Data analysis is most effective when a question is identified that needs to be addressed. That will focus the analysis on which data and which test method might be most effective in addressing or answering the question.
- Mastering the data requires one to know what data is available and whether it might be able to help address the business problem. We need to know everything about the data, including how to access it, its availability, how reliable it is (if there are errors), and what time periods it covers to make sure it coincides with the timing of our business problem, etc.
- Facebook uses link prediction to predict a relationship between two people when it suggests people that one likely knows due to similar other friends, extended family, high schools, college or work locations, etc.
- While sampling is useful, it is still just that, sampling. By looking at all of the transactions and testing them in a way that will highlight the ones that are the biggest dollar items, or are most unusual, that will allow auditors to focus on specific items that might be of material significance.
- There are several correct answers. One data approach might be regression analysis where, given a balance of total accounts receivable held by a firm, how long it has been outstanding, if they have paid debts in the past all will help predict the appropriate level of allowance for doubtful accounts for bad debts.
- The Debt-to-Income ratio might suggest to LendingClub that the person asking for the loan was simply asking for too big of a loan and they would have little ability to repay it. The lower the credit score, the less likely the potential borrower would be able to repay the loan.
- There are many other potential predictors of whether the LendingClub would pay a loan. Here are a few possibilities: What other debt do they have? How much is their disposable income? Do
they have a clean criminal record? Have they had a loan with LendingClub before and did they repay it? Do they rent or own their house?
Solutions to Problems
Note: Some problems and solutions may be altered in Connect for auto grading purposes.
- (LO 1-4) Match each specific Data Analytics test to a specific test approach, as part of performing test plan: Classification Regression Similarity Matching Clustering Co-occurrence Grouping Profiling Link Prediction Data Reduction
Specific Data Analytics Test Test Approach
- Predict which firms will go bankrupt and which firms will not go bankrupt.
Classification
- Use stratified sampling to focus audit effort on transactions with greatest risk.
Data Reduction
- Work to understand normal behavior, to then be able identify abnormal behavior (such as fraud).
Profiling
- Look for relationships between related parties that are not otherwise disclosed.
Link Prediction
- Predict which new customers resemble the company’s best customers.
Similarity Matching
- Predict the relationship between an investment in advertising expenditures and subsequent operating income.
Regression
- Segment all of the company’s customers into groups that will allow further specific analysis.
Clustering
- The customers who buy product X will be most likely to be also interested in product Y.
Co-occurrence Grouping
Specific Analysis Need/Characteristic Tableau Track Tool
- Advanced Visualization Tableau Desktop
- Analyze and share public datasets Tableau Public
- Data joining Tableau Prep Builder
- Presentations Tableau Desktop
- Data transformation Tableau Prep Builder
- Dashboards Tableau Desktop
- Data cleaning Tableau Prep Builder
- (LO 1-6) Here are the predictive attributes and whether they would be applicable to predicting which loans would be delinquent and which loans will ultimately be fully repaid.
Predictive Attributes Predictive? (Yes/No)
- date (Date when the borrower accepted the offer) No
- desc (Loan description provided by borrower) No
- dti (A ratio of debt owed to income earned) Yes
- grade (LC assigned loan grade) Yes
- home_ownership (Values include Rent, Own, Mortgage, Other) Yes
- loanAmnt (Amount of the loan) Yes
- next_pymnt_d (Next scheduled payment date) No
- term (Number of payments on the loan) No
- tot_cur_bal (Total current balance of all accounts) Yes
- (LO 1-6) Navigate to the Connect Additional Student Resources page. Under Chapter 1 Data Files, download and consider the rejected loans dataset of LendingClub data titled “DAA Chapter 1- Data”. Choose among these attributes in the data dictionary, and indicate which are likely to be predictive of loan rejection, and which are not.
Predictive Attributes Predictive? (Yes/No)
- Amount Requested Yes
- Zip Code Yes
- Loan Title No
- Debt-To-Income Ratio Yes
- Application Date No
- Risk_Score Yes
- Employment Length Yes
7A) Multiple Choice: What is the percentage of total loans rejected in the United States that
came from Arkansas?
a. Less than 1%.
b. Between 1% and 2%.
c. More than 2%.
Answer: b
Percentage of total loans rejected that live in Arkansas = 1.219%
7B) Multiple Choice: Is this loan rejection percentage greater than the percent of the U.S.
population that lives in Arkansas (per 2010 census)?
a. Loan rejection percentage is greater than the population.
b. Loan rejection percentage is less than the population.
Answer: a
2,915,918 population in Arkansas divided by USA population of 308,745,538 = 0.9444%
The loan rejection percentage is greater than the percent of the USA population that lives in Arkansas (per 2010 census), but is reasonably close.
- (LO 1-6) Download the rejected loans dataset of LendingClub data titled “DAA Chapter 1-1 Data” from Connect Additional Student Resources and do an Excel PivotTable by state; then figure out the number of rejected applications for each state.
8A) Put the following states in order of their loan rejection percentage based on the count of rejected loans (from high [1] to low [11]) of the total rejected loans.
State Rank 1 (High) to 11 (Low)
- Arkansas (AR) 2
- Hawaii (HI) 9
- Kansas (KS) 5
- New Hampshire (NH)
- New Mexico (NM) 8
- Nevada (NV) 1
- Oklahoma (OK) 3
- Oregon (OR) 4
- Rhode Island (RI) 11
- Utah (UT) 6
- West Virginia (WV) 7
RI (11) 0.
DE 0.
MT 0.
VT 0.
AK 0.
DC 0.
SD 0.
WY 0.
IN 0.
MS 0.
TN 0.
NE 0.
IA 0.
ME 0.
ID 8.0568E- 05
ND 4.6482E- 05
8D) Analysis: Does each state’s loan rejection percentage roughly correspond to their relative proportion of the U.S. population (by 2010 U.S. census at https://en.wikipedia.org/wiki/2010_United_States_census)?
The loan rejection percentage roughly corresponds with the population of each state. However, there is still substantial variation between the rejection percentage of each state.
For Problems 9, 1 0, and 1 1, we will be cleaning a data file in preparation for subsequent analysis.
0
CA NY PA OH GA MI MA AZ WA AL SC WI KY AR OR UT NM NH DE VT DC WY MS NE ME ND
Percentage of Rejected Loans by State
Here is the pivot table by risk score grouping:
Row Labels
Count of Loan Title
Excellent 2931 Fair 236669 Good 83543 Poor 189621 Very Bad 145322 Very Good 11907 Grand Total 669993
Question Group
Which group had the most observations? Fair
Which group had the least observations? Excellent
The Excellent category had the smallest group, whereas the Fair group had the biggest group. Arguably there is a greater population of Fair, even though Very Bad has a smaller count, it is clearly the worst of the group.
The results from the 2013 data are similar to the data from 2007-2012.
Here is the pivot table by Debt-to-Income (DTI) grouping:
Row Labels
Count of Amount Requested High 340615 Low 159464 Medium 169914
Grand Total 669993
Question Group
Which group had the most observations? High
Which group had the least observations? Low
Low DTI is the smallest grouping whereas High DTI has the largest grouping.
The higher the debt as compared to income, the less likely the potential borrower would be able to pay back the loan from Lending Club. Since this dataset is for rejected loans, the higher the DTI ratio, the more likely to be rejected for a loan.
Solutions Manual – Chapter 2
Solutions to Multiple Choice Questions
1. (LO 2-3) Mastering the data can also be described via the ETL process. The ETL process
stands for:
a. extract, total, and load data.
b. enter, transform, and load data.
c. extract, transform, and load data.
d. enter, total, and load data.
Answer: C
2. (LO 2-3) Which of the following describes part of the goal of the ETL process?
1. Identify which approach to Data Analytics should be used.
2. Load the data into a relational database for storage.
3. Communicate the results and insights found through the analysis.
4. Identify and obtain the data needed for solving the problem.
Answer: D
3. (LO 2-2) The advantages of storing data in a relational database include which of the
following?
a. Help in enforcing business rules.
b. Increased information redundancy.
c. Integrating business processes.
d. Help in enforcing business rules and integrating business processes.
Answer: D
4. LO 2-3) The purpose of transforming data is:
a. to validate the data for completeness and integrity.
b. to load the data into the appropriate tool for analysis.
c. to obtain the data from the appropriate source.
d. to identify which data are necessary to complete the analysis.
Answer: A
5. (LO 2-2) Which attribute is required to exist in each table of a relational database and serves
as the “unique identifier” for each record in a table?
a. Foreign key
b. Unique identifier
c. Primary key
d. Key attribute
Answer: C
6. (LO 2-2) The metadata that describe each attribute in a database is which of the following?
a. Composite primary key
b. Data dictionary
c. Descriptive attributes
d. Flat file
Answer: B
7. (LO 2-3) As mentioned in the chapter, which of the following is not a common way that data
will need to be cleaned after extraction and validation?
a. Remove headings and subtotals.
b. Format negative numbers.
c. Clean up trailing zeroes.
d. Correct inconsistencies across data.
Answer: C
8. (LO 2-2) Why is Supplier ID considered to be a primary key for a Supplier table?
a. It contains a unique identifier for each supplier.
b. It is a 10-digit number.
c. It can either be for a vendor or miscellaneous provider.
d. It is used to identify different supplier categories.
Answer: A
9. (LO 2-2) What are attributes that exist in a relational database that are neither primary nor
foreign keys?
a. Nondescript attributes
b. Descriptive attributes
c. Composite keys
d. Relational table attributes
Answer: B
and the suppliers table would be related, thus creating a preventive control to keep a purchase order from being created with a supplier that is not in the approved supplier table.
- The data dictionary is a centralized repository of descriptions for all of the data attributes of the data set. Attributes of a data dictionary for each field might include a variable name, a brief description, whether the field is made up of numbers or text or alphanumerics, the size (or number of digits) of the field, whether it serves as a primary or foreign key and notes, etc.
It is particularly useful during the “Master the Data” steps because it describes what data is stored, how it is stored, and how different tables are related. This information is critical when extracting data from a database.
- Before extracting the data, it is important to be able to answer these questions:
a. What is the purpose of the data request? What do you need the data to solve? What business problem will it address?
b. What risk exists in data integrity (e.g., reliability, usefulness)? What is the mitigation plan?
c. What other information will impact the nature, timing and extent of the data analysis?
When you begin to extract the data, you need to do the following:
a. Identify the tables that contain the information you need.
b. Identify which attributes hold the information you need in each table.
c. Identify how those tables are related to each other.
- The analyst needs to know what data is available, how it is organized, what is possible to include, and how reliable the data is to be able to answer the central question which was the reason for the analysis. The better that the analysts understands the raw data and how it is organized, the better they can formulate questions to expedite the data request and communication process with those that will do the extraction
- The more frequent the requested report, the more the database administrator will set it up for automatic extraction and delivery. It may also be a question of how often the data changes. If the data is updated weekly and the data is extracted daily, that may not make any sense.
- The database administrator is most familiar with the data and may be able to help the analyst get the data needed to address the question. There also might be some sensitivities to who gets what data to ensure that the data gets to the intended analyst and audience. The analyst may suggest other data or data sources that the data requestor is not aware of to help answer that question.
- The impact of transforming data to work with NULL, N/A and zero values in the dataset might have an impact on programs like Excel.
a. Transforming NULL and N/A values into blanks.
i. The COUNT and AVERAGE functions would not include these fields in their computation for these variables.
b. Transforming NULL and N/A values into zeroes.
i. The COUNT and AVERAGE functions would incorporate these zeroes and would be included in their computation for these variables. It would have an impact particularly on the computation of the average since it would have the value of zero.
c. Deleting records that have NULL and N/A values from your dataset.
i. The COUNT and AVERAGE functions would not include these fields in their computation for these variables. If they are deleted all of the other fields and variables would be deleted as well, thus having a bigger impact on the overall dataset.
- The first of the six questions suggested by the Institute of Business Ethics looks to assess whether the purpose of the data is to enhance firm strategy. Questions 2-5 assess the company’s data collection and data use and whether potentially sensitive data is adequately protected from misuse or privacy breach. The final question asks whether the company’s third party data providers follow similar ethical standards.
As a reminder, the six questions introduced in the chapter are as follows:
How does the company use data, and to what extent is it integrated
into firm strategy? What is the purpose of the data? Is it accurate or
reliable? Will it benefit the customer or the employee?
Does the company send a privacy notice to individuals when their
personal data is collected? Is the request to use the data clear to the
user? Do they agree to the terms and conditions of use of their personal
data?
Does the company assess the risks linked to the specific type of data the
company uses? Have the risks of data use or data breach of potentially
sensitive data been considered?
- (LO 2-3) Identify the order sequence in the ETL process as part of mastering the data (i.e., 1 is
first; 5 is last). Steps of the ETL Process Sequence Order (1 to 5) Validate the data for completeness and integrity. 3 Sanitize the data. 4 Obtain the data. 2 Load the data in preparation for data analysis. 5 Determine the purpose and scope of the data request.
- (LO 2-3) Identify which ETL tasks would be considered “Validate” the data, and which would be considered “Cleaning” the data.
ETL Task Validate or Clean?
- Compare the number of records that were extracted to the number of records in the source database
Validate
- Remove headings or subtotals Clean
- Remove leading zeroes and nonprintable characters Clean
- Compare descriptive statistics for numeric fields Validate
- Format negative numbers Clean
- Compare string limits for text fields Validate
- Correct inconsistencies across data, in general Clean
- (LO 2-3) Match each ETL task to the stage of the ETL process: determine purpose obtain validate clean load
ETL Task Stage of ETL Process
- Use SQL to extract data from the source database
Obtain
- Remove headings or subtotals Clean
- Choose which database and specific data will be needed to address the accounting question
Determine purpose
- Compare the number of records extracted to the number of records in
Validating
the source database.
- Make sure all data formats start with two capital letters. Fix inconsistencies.
Clean
- Input the data into the analysis tool. Load
- (LO 2-4) For each of the six questions suggested by the Institute of Business Ethics to evaluate data privacy, categorize each question into one of these three types:
- Evaluate the company’s purpose of the data
- Evaluate the company’s use or misuse of the data
- Evaluate the due diligence of the company’s data vendors in preventing misuse of the data.
Institute of Business Ethics Questions Regarding Data Use and Privacy
Types of Data Privacy/Use Question By Number
Does the company assess the risks linked
to the specific type of data the company
uses?
Does the company send a privacy notice
to individuals when their personal data
are collected?
How does the company use data, and to
what extent are they integrated into
firm strategy?
Does our company conduct appropriate
due diligence when sharing with or
acquiring data from third parties?
Do the company have the appropriate
tools to manage the risks of misuse?
Does the company have safeguards in
place to mitigate these risks of misuse?
- (LO 2-2) Which of the following are useful, established characteristics of using a relational database?
Institute of Business Ethics Questions Regarding Data Use and Privacy
Useful, Established Characteristics of Relational Databases (Y)es/(N)o
Completeness Y
Reliable N
No redundancy Y
Communication and integration of
business processes
Y
