Accounting task

Data Analytics for Accounting
Richardson | Teeter | Terrell 3e
Chapter 1
Labs
© McGraw Hill
• Lab 1-0 How to complete labs
• Lab 1-1 Data Analytics Questions in
Financial Accounting
• Lab 1-2 Data Analytics Questions in
Managerial Accounting
• Lab 1-3 Data Analytics Questions in
Auditing
• Lab 1-4 Comprehensive Case: Questions
about Dillard’s Store Data
• Lab 1-5 Comprehensive Case: Connect
to Dillard’s Store Data
© McGraw Hill LLC. All rights reserved. No reproduction or distribution without the prior written consent of McGraw Hill LLC.
1
Lab 1-0 How to Complete
Labs
© McGraw Hill
2
Lab 1-0 How to Complete Labs
The labs in this book will provide valuable hands-on experience
in generating and analyzing accounting problems. Each lab will
provide a company summary with relevant facts, techniques that
you will use to complete your analysis, software that you’ll need,
and an overview of the lab steps.
© McGraw Hill
3
Lab 1-0 Objectives
Part 1 Explore Different Tool
Tracks
Part 2 Take Screenshots of
Your Tools
© McGraw Hill
To take screenshots:
Windows: Use the Snipping
Tool or PrtScn
Mac: Press Cmd + Shift + 4
and draw a rectangle on your
screen.
4
Lab 1-0 Part 1 Objective Questions
(LO 1-1, 1-5)
OQ1. According to your
instructor, which track(s) will
you be completing this
semester? (Answer this on
Connect or write your response
in your lab document.)
© McGraw Hill
OQ2. Where should you
answer objective lab
questions? (Answer this on
Connect or write your response
in your lab document.)
5
Lab 1-0 Part 1 Analysis Questions (LO
1-1, 1-5)
AQ1. What is the purpose of
taking screenshots of your
progress through the labs?
(Answer this on Connect or
write your response in your lab
document.)
© McGraw Hill
6
Lab 1-0 Part 2 Objective
Questions (LO 1-1, 1-5)
OQ1. Where did you go to
complete this lab
activity? (Answer this on
Connect or write your response
in your lab document.)
© McGraw Hill
OQ2. What type of computer
operating system do you
normally use? (Answer this on
Connect or write your response
in your lab document.)
7
Lab 1-0 Part 2 Analysis Questions (LO
1-1, 1-5)
AQ3. Compare and Contrast: If
you completed both tracks in
this lab, which tool are you
most interested in learning and
why? (This question does not
appear in Connect. Write your
response in your lab
document.)
© McGraw Hill
8
Lab 1-1 Data Analytics
Questions in Financial
Accounting
© McGraw Hill
9
Lab 1-1 Objectives
Part 1 Identify the Questions
Part 2 Master the data
© McGraw Hill
10
Lab 1-1 About XBRL Tags
Taxonomy Viewer:
1. Go to xbrlview.fasb.org and
open a recent US GAAP > All
taxonomy.
2. Navigate through the
statements and click on an
account.
3. The XBRL tag is in the
Properties pane, next to Name
© McGraw Hill
Note: XBRL tags use
CamelCase, meaning there are
no spaces between words, but
the first letter of each word is
capitalized.
If you get an error: When your
tag doesn’t load data, it is likely
that the company you chose
uses a different tag. Try an
alternative tag.
11
Lab 1-1 Part 1 Analysis Questions1
AQ1. Use what you know about financial statement analysis (or
search the web if you need a refresher) to generate three
different metrics for evaluating financial performance. For
example, if you wanted to evaluate a company’s profit margin
from one year to the next your question might be, “Has Apple
Inc’s gross margin increased in the last three years?”
© McGraw Hill
12
Lab 1-1 Part 1 Analysis Questions2
AQ2. Next to each question generate a hypothetical answer to
the question to help you identify what your expected output would
be. You may use some insight or intuition or search for industry
averages to inform your hypothesis. For example: “Hypothesis:
Apple Inc’s gross margin has increased slightly in the past 3
years.”
© McGraw Hill
13
Lab 1-1 Part 1 Analysis Questions3
AQ3. Evaluate each question from Part 1. There are specific data
attributes that will help you find the answer you’re looking for. For
example, if your question was “Has [Company X’s] gross margin
increased in the last 3 years?” and the expected answer is “Apple
Inc’s gross margin has increased slightly in the past 3 years,” this
tells you what attributes (or fields) to look for: company name,
gross margin (sales revenues – cost of goods sold), year.
© McGraw Hill
14
Lab 1-1 Part 2 Analysis Questions
AQ1. For each of your questions, identify the account or data
attribute you need to answer your question. Then use FASB’s
XBRL taxonomy to identify the specific XBRL tags that represent
those accounts.
© McGraw Hill
15
Lab 1-2 Data Analytics
Questions in Managerial
Accounting
© McGraw Hill
16
Lab 1-2 Objectives
Part 1 Identify the Questions
Part 2 Master the data
© McGraw Hill
Remember: Start with a
question and hypothesize an
answer; that way you’ll know if
the data and your analysis
match what you expect.
Note: Sometimes the data
won’t support your question. In
that case, ask a different
question or try to collect
additional data.
17
Lab 1-2 Part 1 Analysis Questions (LO
1-3, 1-4) 1
AQ1. Use what you know about loan risk (or search the web if
you need a refresher) to identify three different questions that
might influence risk. For example, if you suspect risky customers
live in a certain location, your question might be “Where do the
customers with highest risk live?”
© McGraw Hill
18
Lab 1-2 Part 1 Analysis Questions (LO
1-3, 1-4) 2
AQ2. For each question you identified in Q1, generate a
hypothetical answer to each question to help you identify what
your expected output would be. You may use some insight or
intuition or search the Internet for ideas on how to inform your
hypothesis. For example: “Hypothesis: High-risk customers likely
live in coastal towns.”
© McGraw Hill
19
Lab 1-2 Part 1 Analysis Questions (LO
1-3, 1-4) 3
AQ3. Finally, identify the data that you would need to answer
each of your questions. For example, to determine customer
location, you might need the city, state, and zip code. Additionally,
if you hypothesize a specific region, you’d need to know which
cities, state, and/or zip codes belong to that region.
© McGraw Hill
20
Lab 1-2 Part 2 Analysis Questions (LO
1-3, 1-4)
AQ1. Evaluate each of your
questions from Part 1. Do the
data you identified in your
questions exist in the table
provided? If so, write the
applicable fields next to each
question in your document.
© McGraw Hill
AQ2. Are there data values you
identified in Part 1 that don’t
exist in the table? Explain how
you might collect the missing
data or where you might locate
it.
21
Lab 1-3 Data Analytics
Qeustions in Auditing
© McGraw Hill
22
Lab 1-3 Objectives
Part 1 Identify the Questions
Part 2 Master the data
© McGraw Hill
Remember: Start with a
question and hypothesize an
answer; that way you’ll know if
the data and your analysis
match what you expect.
Note: Sometimes the data
won’t support your question. In
that case, ask a different
question or try to collect
additional data.
23
Lab 1-3 About Audit Data Standards
The AICPA prepared a
standard data model for
companies to prepare data for
the audit.
If companies adopt the
standard, auditors can use
analytical tools more effectively.
© McGraw Hill
Current Audit Data Standards
include:
Base
General Ledger
Order to Cash
Procure to Pay
Inventory
Fixed Asset
24
Lab 1-3 Part 1 Analysis Questions (LO
1-3, 1-4) 1
AQ1. Use what you know about internal controls over the orderto-cash process (or search the web if you need a refresher) to
identify three different questions that might indicate internal
control weakness. For example, if you suspect that a manager
may be delaying approval of shipments sent to customers, your
question might be “Are any shipping managers approving
shipments more than 2 days after they are received?”
© McGraw Hill
25
Lab 1-3 Part 1 Analysis Questions (LO
1-3, 1-4) 2
AQ2. Next to each question
generate a hypothetical answer
to each question to help you
identify what your expected
output would be. You may use
some insight or intuition or
search the Internet for ideas on
how to inform your hypothesis.
For example: “Hypothesis: Only
one or two shipping managers
are approving shipments more
than 2 days after they are
© McGraw Hill
received.”
AQ3. Finally, identify the data
that you would need to answer
each of your questions. For
example, to determine the
timing of approval and who is
involved, you might need the
approver ID, the order date,
and the approval date.
26
Lab 1-3 Part 2 Analysis Questions (LO
1-3, 1-4) 3
AQ1. List some of the tables
and fields from the audit data
standard that relate to each
question you identified in Part
1. For example, if you’re
looking for the shipment timing
and approval data, you would
need the
Shipments_Made_YYYYMMD
D_YYYYMMDD table and
Approved_By, Entered_Date,
and Approved_Date fields.
© McGraw Hill
AQ2. Are there data values you
identified in Part 1 that don’t
exist in the tables? Explain how
you might collect the missing
data or where you might locate
it.
27
Lab 1-4 Comprehensive
Case: Questions About
Dillard’s Store Data
© McGraw Hill
28
Lab 1-4 Objectives
Part 1 Identify the Questions
Part 2 Master the data
Note: You do NOT need to
connect to the remote desktop
to complete this lab.
Remember: You don’t need all
the fields or data to formulate
your questions. Think about
how you would collect
additional data.
© McGraw Hill
29
Lab 1-4 Part 1 Analysis Questions (LO
1-1, 1-3, 1-4)
AQ1. Assume that Dillard’s
management is interested in
improving profitability. Write
three questions that could be
asked to assess current
profitability levels for each
product and how profitability
could be improved in the near
future.
© McGraw Hill
AQ2. Assume that
Dillard’s management wishes
to improve its online sales and
profitability on those sales.
What three questions could be
asked to see where Dillard’s
stands on its online sales?
30
Lab 1-4 Part 2 Objective Questions
(LO 1-1, 1-3, 1-4)
OQ1. What table and fields
could address the question of
the profit margin (sales price
less cost) on each product
(SKU) available for sale?
© McGraw Hill
OQ2. If you’re interested in
learning which product is sold
most often at each store, which
tables and fields would you
consider?
31
Lab 1-4 Part 2 Analysis Questions (LO
1-1, 1-3, 1-4)
AQ1. You’re trying to learn
about where Dillard’s stores
are located to identify locations
for the next additional store.
Consider the STORE table.
What questions could be asked
about store location given data
availability?
© McGraw Hill
AQ2. What questions would
you have regarding data fields
in the SKU table that could be
used to help address the cost
of shipping? What additional
information would be helpful to
address this question?
32
Lab 1-5 Comprehensive Case:
Connect to Dillard’s Store
Data
© McGraw Hill
33
Lab 1-5 Objectives
Part 1 Excel + Power Query
and Tableau Prep
Part 2 Part 2 Tableau Desktop
and Power BI Desktop
© McGraw Hill
Note: You NEED to connect to
the remote desktop to complete
this lab.
34
Lab 1-5 Part 1 Analysis Questions (LO
1-3, 1-4)
AQ1. Why would you want to
filter the date field before
loading data into your model for
analysis?
© McGraw Hill
AQ2. What are some
limitations introduced into your
analysis by filtering on such a
small date range?
35
Lab 1-5 Part 2 Analysis Questions (LO
1-3, 1-4)
AQ1. Compare the tools you used in Part 2 with the tools you
used in Part 1. What are some of the differences between these
visualization tools (Power BI Desktop or Tableau Desktop) and
those data prep tools (Power Query or Tableau Prep)?
© McGraw Hill
36