Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
b. Print out a report that shows sales by month for each salesperson.
16-1
©2018 Pearson Education, Inc.
Accounting Information Systems
Step 2: Then click on the salesperson entry and move it from the “Row Labels” window
Step 3: Highlight all the cells in the PivotTable and format them to display currency with
16-2
©2018 Pearson Education, Inc.
Accounting Information Systems
c. Assume that Brown and David are in sales group 1 and the other three
salespeople are in sales group 2. Print out a report that shows monthly sales for
each group.
Step 1: To separate the sales people into groups, click on the sales person row in the
16-3
©2018 Pearson Education, Inc.
Accounting Information Systems
Step 2: Right click the two highlighted cells and select “Group”. They will now have a
16-4
©2018 Pearson Education, Inc.
Accounting Information Systems
Step 3: You can collapse and display the groups by clicking on the button to the left of
16-5
©2018 Pearson Education, Inc.
Accounting Information Systems
16.10 Excel Problem Objective: How to do what-if analysis with graphs.
a. Read the article “Tweaking the Numbers,” by Theo Callahan in the June 2001
issue of the Journal of Accountancy (either the print edition, likely available at
your school’s library, or access the Journal of Accountancy archives at
www.aicpa.org). Follow the instructions in the article to create a spreadsheet
with graphs that do what-if analysis.
Most of the steps in the article can be done as indicated. One difference is finding the
If the developer tab is not available, follow these steps (for Excel 2007):
1. Click the Microsoft Office Button (in far upper left corner – see prior
2. Click Excel Options
3. In the “Popular” category, under “Top options for working with Excel” select
16-6
©2018 Pearson Education, Inc.
The Developer tab normally appears to the right of
the View tab
The Microsoft Office Button is in the far upper left
Click on “Design Mode” to toggle
Click on Insert to add spin buttons
and other Active X controls
Accounting Information Systems
On the Developer tab you then click insert and select the “spin box” option from the list of
choices of Active X controls. Then position your cursor in the cell where you want to insert a
b. Now create a spreadsheet to do graphical what-if analysis for the “cash gap.”
Cash gap represents the number of days between when a company has to pay its
suppliers and when it gets paid by its customers. Thus, Cash gap = Inventory
days on hand + Receivables collection period – Accounts payable period.
The “cash gap” formula indicates how much of a cushion a company has, given a set
of assumptions about inventory, receivables, and payables. If the projected cash gap is
16-7
©2018 Pearson Education, Inc.
Accounting Information Systems
The purpose of your spreadsheet is to display visually what happens to cash gap
when you “tweak” policies concerning inventory, receivables, and payables.
Thus, you will create a spreadsheet that looks like Figure 16-11
c. Set the three spin buttons to have the following values:
Spin button for
Inventory
Spin button for
Receivables
Spin button for
Payables
Linked cell C2 C3 C4
16-8
©2018 Pearson Education, Inc.
Accounting Information Systems
d. The article "Analyzing Liquidity: Using the Cash Conversion Cycle" by C. S. Cagle,
S. N. Campbell, and K. T. Jones in the Journal of Accountancy (May 2013), pp. 44-48
calls the “Cash Gap” the “Currency Conversion Cycle” and explains that bigger
values are bad because they indicate less liquidity (because cash needed to pay
suppliers is tied up in receivables and inventory). Indeed, the “cash gap” can even be
negative for companies, like Dell, that collect payment from customers in advance and
stretch out payments to suppliers as long as possible. Given that background, collect
the information from annual reports needed to calculate the “cash gap” for at least 3
years for Dell and 3 or more other companies. Enter that data in a spreadsheet and
create a graph that you think best highlights the trend in cash gap across the different
companies.
16.11 Answer all of the following multiple-choice problems:
1. The theory underlying the Balanced Scorecard is that improvements in the _____
section will lead to improvements in the _____ section leading to improvements in
the _____ section, ultimately creating better results in the financial section.
a. customer, learning & innovation, internal
b. learning & innovation, internal, customer
c. internal, customer, learning & innovation
2. Journal entries made by either the treasurer or controller should be subject to input
edit and processing controls. A data entry application control that is designed to
ensure that the total debits in a journal entry equal the total credits is called a
__________.
a. Sign check
b. Equality check
c. Reasonableness check
d. Zero-balance check
3. Violating which of the following principles of graph design would result in distorting
the magnitude of a trend in sales?
a. Displaying trend data in chronological sequence from left-to-right on the
x-axis
b. Starting the y-axis at zero
c. Neither of the above
d. Both of the above
16-9
©2018 Pearson Education, Inc.
Accounting Information Systems
4. Fraudulent financial reporting is a concern in the GL/reporting cycle. The best
control to deal with that potential problem is
a. Proper segregation of duties
b. Processing integrity controls such as validity checks
c. An independent audit of all adjusting entries
d. Requiring mandatory vacations for all managers
e. Prenumbering of all documents
5. Creation and review of an audit trail is a detective control that can enable
organizations to find and correct problems arising from
a. Inaccurate updating of the general ledger
b. Unauthorized adjusting entries
c. Both a and b
d. Neither a nor b
6. An adjusting entry to record bad debt expense is an example of a(n)
a. Accrual
b. Deferral
c. Estimate
d. Revaluation
e. Correction
7. Which of the following XBRL components contains information about which items
should be summed to create a category total (e.g., which items comprise current
liabilities)?
a. Instance document
b. Style sheet
c. Taxonomy
d. Linkbase
e. Schema
16-10
©2018 Pearson Education, Inc.
Accounting Information Systems
8. Which of the following XBRL components can, if used too much, limit the potential
benefits of comparability across organizations?
a. Presentation linkbases
b. Taxonomy extensions
c. Style sheets
d. Schemas
9. Which section of the Balanced Scorecard would benefit most from collecting data
from external parties, rather than relying on internally-generated data?
a. Financial
b. Customer
c. Internal Operations
d. Innovation and Learning
16-11
©2018 Pearson Education, Inc.
Accounting Information Systems
SUGGESTED ANSWERS TO THE CASES
Case 16-1 Exploring XBRL Tools
Each year companies release new software tools designed to simplify the process of
interacting with XBRL. Obtain a free trial (demo) version of two of the following tools and
write a brief report comparing them:
Potential tools (your professor may suggest others):
Altova—available at http://www.altova.com/solutions/xbrl.html
MapForce—available at http://www.altova.com/xml_tools.html
Various free tools from XBRL US—available at https://xbrl.us
CalcBench spreadsheet tool—available at http://www.calcbench.com
16-12
©2018 Pearson Education, Inc.
Accounting Information Systems
Case 16-3 Visualization tools for Big Data
Traditional graphs (bar charts, line graphs, pie charts) help decision makers see patterns
and relationships in data contained in typical spreadsheets. However, more advanced
virtualization techniques are required to understand Big Data. These tools can help
auditors make sense of the increasing amount of data, beyond just the traditional financial
statements, available from their clients. Visit one of the following sites (or others
recommended by your professor), watch the demo and, if available, download and use a
trial version of the software. Write a review of the demo(s) you view and the trial version of
any product(s) you test.
Virtualization tools:
Tableau—available from www.tableausoftware.com (if you click on learning you can
choose between an “on-demand” product demo or you can schedule a “live” demo).
Spotfire—available from Spotfire.tibco.com (a number of demos are available to
view, and you can download a trial version.
Grading suggestion: grade on thoroughness, completeness, and use of screenshots. Can
16-13
©2018 Pearson Education, Inc.
