Analytics and Big Data for Accountants

187 Pages·2018·17.24 MB·English

by  Lindell

A B D
NALYTICS AND IG ATA
A
FOR CCOUNTANTS
BY JIM LINDELL, MBA, CPA, CGMA
Notice to Readers
Analytics and Big Data for Accountants is intended solely for use in continuing professional
education and not as a reference. It does not represent an official position of the Association of
International Certified Professional Accountants, and it is distributed with the understanding that
the author and publisher are not rendering legal, accounting, or other professional services in the
publication. This course is intended to be an overview of the topics discussed within, and the
author has made every attempt to verify the completeness and accuracy of the information
herein. However, neither the author nor publisher can guarantee the applicability of the
information found herein. If legal advice or other expert assistance is required, the services of a
competent professional should be sought.
You can qualify to earn free CPE through our pilot testing program.
If interested, please visit aicpa.org at http://apps.aicpa.org/secure/CPESurvey.aspx.
© 2017 Association of International Certified Professional Accountants, Inc. All rights reserved.
For information about the procedure for requesting permission to make copies of any part of
this work, please email [email protected] with your request. Otherwise, requests should
be written and mailed to Permissions Department, 220 Leigh Farm Road, Durham, NC 27707-
8110 USA.
Course Code: 746272
DAAN GS-0417-0A
Revised: April 2017
T C
ABLE OF ONTENTS

Overview .............................................................................................................. Overview 1
Chapter 1........................................................................................................................... 1-1
What Are Big Data and Analytics?..................................................................................... 1-1
Practice Questions ................................................................................................................... 1-13
Chapter 2........................................................................................................................... 2-1
Big Data History Big Data Sources and Characteristics .................................................. 2-1
Practice Questions ................................................................................................................... 2-19
Chapter 3........................................................................................................................... 3-1
What Are the Trends in Big Data? ..................................................................................... 3-1
Practice Questions ................................................................................................................... 3-12
Chapter 4........................................................................................................................... 4-1
What Are the Strategy and Business Applications of Big Data? ....................................... 4-1
Practice Questions ................................................................................................................... 4-13
Chapter 5........................................................................................................................... 5-1
Big Data Platforms and Operating Tools........................................................................... 5-1
Practice Questions ................................................................................................................... 5-21
Chapter 6........................................................................................................................... 6-1
Big Data End User and Accounting Tools ......................................................................... 6-1
Practice Questions ................................................................................................................... 6-26
Copyright 2017 Table of Contents 1
Chapter 7 ........................................................................................................................... 7-1
Examples of Big Data ........................................................................................................ 7-1
Practice Questions ................................................................................................................... 7-17
Chapter 8 ........................................................................................................................... 8-1
Big Data in the Accounting Department ........................................................................... 8-1
Practice Questions ................................................................................................................... 8-14
Chapter 9 ........................................................................................................................... 9-1
Ethics and Privacy With Big Data ....................................................................................... 9-1
Practice Questions ................................................................................................................... 9-17
Index ........................................................................................................................... Index 1
Solutions ............................................................................................................... Solutions 1
Chapter 1 ..................................................................................................................... Solutions 1
Chapter 2 ..................................................................................................................... Solutions 2
Chapter 3 ..................................................................................................................... Solutions 4
Chapter 4 ..................................................................................................................... Solutions 5
Chapter 5 ..................................................................................................................... Solutions 6
Chapter 6 ..................................................................................................................... Solutions 8
Chapter 7 ................................................................................................................... Solutions 10
Chapter 8 ................................................................................................................... Solutions 12
Chapter 9 ................................................................................................................... Solutions 14

2 Table of Contents Copyright 2017 AICPA Unauthorized Copying Prohibited
O
VERVIEW
c.
n
s, I
nt
a
nt
u
o
c
Ac WELCOME TO ANALYTICS AND BIG DATA FOR ACCOUNTANTS!
al
n
o
si Analytics is a new force driving business. Tools have been created to measure program effects and return
s
fe on investment, visualize data and business processes, and uncover the relationship between key
o
r
P performance indicators many using the unprecedented amount of data now moving into organizations.
d
fie In this course, you will discuss leading-edge topics in analytics and finance in a session that is packed with
nts Certi useful tips and practical guidance that you can apply immediately.
ccounta ational
A n
r er
ata fo of Int INTRODUCTORY COMMENTS
D n
s and Big Lindell Associatio OCreFsntOer uo cffot utrh raee , l faairnrgsdet itnti mownea-ssp traholsefo iat ureetashrtolryur icwntu ahsrii sne xgcp airtoess eeirnd.f Totorhm eth aoetri ogcaonnn ticezecaphtitno oonfl o wBgaiygs igDnofarinatasg t wrtuhacrsot uwurghehe a nan hdme t ahsjeeor rsv ooefrdtg waasna ritzeha etto ioonls
AnalyticBy Jim © 2017 ugesende rtaoti opnro lvaindgeu iangfeo rdmataatbioanse t epcrhongoralomg.y T sheerv eixcepse.r At hs aad r ae swulitf,e twheh oag wenacs ys ubfrfoeurignhgt firno amn ae xspeveertr eo nill na efsosu. rth-
The expert was adamant that if he could obtain a large number of patient records, he would find a cure
for his wife’s illness. is contention was that a relationship between the illness and the cure was hidden
within other patient records. He certainly had the skills to find a connection. The author saw the concept
of Big Data first-hand without realizing how it would evolve. The author is more convinced than ever
that the accountant position is morphing daily. Transaction analysis, variance analysis, and ratio analysis
are all being performed by software packages. The accountant role will continue its migration from data
creator, manipulator, and archivist to one of data scientist and storyteller. It is imperative that the
accountant of today keep pace with technological change and recognize the need to move from historical
analysis to predictive change and ultimately, prescriptive change.
It’s against this backdrop that the advent of Big Data and analytics has already affected many
organizations and will play a much more significant role in the future.
Copyright 2017 AICPA Unauthorized Copying Prohibited Overview 1
TOPICS DISCUSSED
Evidence-based techniques for finding or generating data, selecting key performance indicators,
isolating program effects
Relating data to return on investment, financial values, and executive decision-making
Data sources including surveys, interviews, customer satisfaction, engagement, and operational data
Visualizing and presenting complex results
THE CHAPTERS IN THIS COURSE
Chapter 1: What Are Big Data and Analytics?
Chapter 2: Big Data History Big Data Sources and Characteristics
Chapter 3: What Are the Trends in Big Data?
Chapter 4: What Are the Strategy and Business Applications of Big Data?
Chapter 5: Big Data Platforms and Operating Tools
Chapter 6: Big Data End User and Accounting Tools
Chapter 7: Examples of Big Data
Chapter 8: Big Data in the Accounting Department
Chapter 9: Ethics and Privacy With Big Data
OPENING DISCUSSION
As you progress through the course, consider the following challenges:
What information does your organization currently have access to that is already in your systems and
available for use?
What information could you have access to that exists in your organization but is not captured?
What information exists on the Internet in regards to industry databases or government databases
that you could access?
What information is available through sensors and machines that could provide insights into the
operational and strategic dynamics of your business?
EXERCISE
It would be helpful to spend a couple of minutes to jot down the type of information your organization
currently has available. This analysis will be helpful as you consider different applications of analytics and
Big Data. Use table 0-1 to gather your information.
2 Overview Copyright 2017 AICPA Unauthorized Copying Prohibited
Table 0-1 What Information Does Your Organization Have Available?
What Information Do You Have?
Customer Vendor Employee Strategic Operational Other
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3 3
4 4 4 4 4 4
5 5 5 5 5 5
6 6 6 6 6 6
7 7 7 7 7 7
8 8 8 8 8 8
9 9 9 9 9 9
10 10 10 10 10 10
Before becoming immersed in the overall topic, it may be useful to discuss a broad picture of Big Data so
that all participants have an initial understanding. Consider illustration 0-1:
Illustration 0-1

Copyright 2017 AICPA Unauthorized Copying Prohibited Overview 3
TEUs Twenty-Foot Equivalent Units
IPS Industrial Production Statistics
S&P SSttaanndard & Poor’s
NYMEX New York Mercantile Exchange
MPP Massively Parallel Processing
Data originates in a variety of places and in a variety of forms. Data from individual companies,
organizations, streaming data, and so on accumulate in the cloud, as shown in illustration 0-1.
Organizations access the information via in-house servers, laptops, tablets and other mobile devices.
Organizations may desire a unique combination of external databases and internal databases, including
calculations, projections, and so on. That information is sent to multiple computers with individual
processors to analyze the vast amounts of data. These multiple computers with their processors are
known as massively parallel processing (MPP).
EXAMPLE
Next, we will consider a simple example of how large amounts of data can be processed to help you
create a narrative to supplement your accounting processes. Let us assume that our company is in the
lumber industry or is affected by lumber prices. The industrial production statistics are available at
www.federalreserve.gov/RELEASES/g17/ipdisk/alltables.txt. The Federal Reserve’s monthly index of
industrial production rates covers manufacturing, mining, and electric and gas utilities. The production
index measures real output and is expressed as a percentage of real output.
The initial data found on that website looks like that in table 0-2. This raw data is overwhelming and not
easy to interpret in its current form.
Table 0-2

4 Overview Copyright 2017 AICPA Unauthorized Copying Prohibited
I selected the IPS data for the NAICS codes B500001(Total Index Statistics for all industrial production)
and G321(Wood Products Industry Statistics) to begin the analysis, as in table 0-3:
Table 0-3
IPS.B50001 2000 94.7615 95.0664 95.4649 96.1873 96.3963 96.488 96.386 96.0455 96.4354 96.1096 96.1276 95.8441
IPS.B50001 2001 95.1747 94.5581 94.2954 94.0294 93.3526 92.7435 92.249 92.05 91.7355 91.3042 90.8141 90.8243
IPS.B50001 2002 91.403 91.402 92.1252 92.5335 92.9145 93.8071 93.5978 93.6123 93.7211 93.4327 93.9229 93.4505
IPS.B50001 2003 94.0868 94.3655 94.1543 93.444 93.4875 93.5856 94.0002 93.8015 94.3862 94.4645 95.2175 95.1269
IPS.B50001 2004 95.3012 95.8359 95.3155 95.6825 96.4212 95.6445 96.3852 96.445 96.5044 97.4167 97.6225 98.3132
IPS.B50001 2005 98.7886 99.4359 99.3247 99.4595 99.6415 100.0262 99.7447 99.9372 98.0478 99.3189 100.2981 100.8948
IPS.B50001 2006 101.0256 100.9981 101.2658 101.6644 101.5254 101.9173 101.8981 102.252 102.1093 102.0843 101.9683 102.9751
IPS.B50001 2007 102.4954 103.6101 103.7575 104.5126 104.564 104.5525 104.5442 104.7213 105.0936 104.537 105.1581 105.1322
IPS.B50001 2008 104.8595 104.5926 104.2989 103.53 103.0519 102.8476 102.3093 100.7832 96.4822 97.3647 96.1487 93.3954
IPS.B50001 2009 91.2227 90.6371 89.2352 88.4857 87.5811 87.214 88.1641 89.119 89.7943 90.1731 90.4311 90.7512
IPS.B50001 2010 91.8162 92.0991 92.7191 93.0609 94.4844 94.6828 95.1542 95.4866 95.715 95.4803 95.518 96.3941
IPS.B50001 2011 96.3665 95.8776 96.6955 96.2901 96.5438 96.7612 97.1711 97.7699 97.7762 98.4296 98.2829 98.7841
IPS.B50001 2012 99.5096 99.7389 99.0887 99.928 100.0508 99.9691 100.2736 99.833 99.9189 100.1878 100.6435 100.858
IPS.B50001 2013 100.933 101.3425 101.561 101.5385 101.4689 101.6621 101.2685 102.0442 102.6361 102.6534 102.9163 103.1889
IPS.B50001 2014 103.0047 103.8079 104.6615 104.8595 105.2461 105.716 106.0803 106.1138 106.6776 106.8463 107.7996 107.9108
IPS.B50001 2015 107.6003 107.4368 107.2374 107.0599 106.6799 106.6628 107.4746 107.4973 107.5331 107.4001 106.5811 105.8689
IPS.B50001 2016 106.8477
IPS.G321 2000 143.972 143.4722 143.9109 143.2324 140.9417 138.436 137.5802 135.2912 136.5014 133.6298 133.283 129.321
IPS.G321 2001 126.5031 125.8307 128.3142 128.6762 129.9331 130.9957 129.5036 131.7801 132.8763 129.2955 130.1209 131.9857
IPS.G321 2002 133.3057 132.8523 135.8989 135.6197 135.6325 137.4395 135.6227 136.501 135.7389 135.9186 134.1315 133.5371
IPS.G321 2003 134.3941 134.3887 132.4979 132.858 132.6237 133.6178 134.9538 134.4597 135.2376 136.3176 140.5582 137.6886
IPS.G321 2004 137.9976 138.0842 136.9454 138.3816 138.9288 136.5499 138.691 138.9603 136.4546 140.4624 140.2499 139.8354
IPS.G321 2005 146.1386 142.86 142.2799 142.6249 143.0869 143.4326 144.3807 144.0517 149.3065 156.8229 158.6932 158.6398
IPS.G321 2006 158.764 155.9862 155.0613 152.576 151.3601 149.1037 149.5498 147.233 145.8068 139.3438 138.9545 142.9898
IPS.G321 2007 140.0055 139.8647 141.9422 139.9414 141.1664 142.9497 142.5319 139.8908 138.6741 135.9987 133.9249 134.3439
IPS.G321 2008 131.3716 128.5247 127.1709 124.6709 123.2955 122.3217 121.8669 119.398 115.8094 109.9483 106.6985 96.6497
IPS.G321 2009 93.6668 93.3626 89.9492 88.3143 87.4935 90.724 90.5585 92.0024 92.1858 90.6401 91.2439 90.7279
IPS.G321 2010 92.6534 91.2404 92.4849 96.417 97.872 95.2164 94.6381 93.8519 92.8365 93.9712 94.0948 94.4015
IPS.G321 2011 94.1239 93.5283 95.8471 93.7046 94.7942 93.4226 93.6132 92.5363 94.9918 94.7417 94.766 95.8602
IPS.G321 2012 98.0903 97.4725 98.2069 99.7776 101.1851 99.1884 99.5748 99.8404 98.921 100.6659 103.2503 103.8267
IPS.G321 2013 104.9279 106.5673 105.4279 103.602 104.0396 103.9955 104.0478 105.6103 107.0795 107.7638 109.0048 107.3474
IPS.G321 2014 105.0321 104.9346 107.1353 107.6058 109.3083 109.5686 111.385 112.1464 111.1789 112.4458 112.0506 113.368
IPS.G321 2015 111.1515 110.2591 109.7365 109.574 108.836 108.8611 111.2143 111.6601 112.5653 113.3287 112.8498 114.869
IPS.G321 2016 116.2468
Next, the monthly data was obtained for Louisiana-Pacific Corporation (LPX) from Finance.Yahoo.com,
and then all three data series were combined utilizing Excel. Once the data series had been established,
the graph in illustration 0-2 was generated.
Copyright 2017 AICPA Unauthorized Copying Prohibited Overview 5