b. Create a scattergraph to illustrate the relationship between targeted emails and
unit sales. Display the regression equation and the R2 between the two variables
on the chart.
c. Create a scattergraph to illustrate the relationship between click ads and unit
sales. Display the regression equation and the R2 between the two variables on
the chart.
d. Which variable (targeted emails or click ads) has the greater influence on unit
sales? How do you know?
e. Use the “ =Forecast “function to display the forecasted sales for 200,000 targeted
emails and for 200 click ads.
12.13 Excel Project
Required
a. Create a spreadsheet that contains the following data:
Salesperson
Date
Calls Made
Smith
07/03/2014
100
Barnes
07/05/2014
110
Martinez
07/13/2014
95
Jackson
07/20/2014
115
Hsu
07/28/2014
120
Smith
08/01/2014
125
Barnes
08/02/2014
115
Hsu
08/10/2014
110
Barnes
08/10/2014
100
Martinez
08/14/2014
135
Jackson
08/20/2014
115
Smith
08/22/2104
90
Barnes
08/23/2014
85
Martinez
09/01/2014
100
Jackson
09/02/2014
90
Hsu
09/05/2014
95
Jackson
09/11/2014
140
Hsu
09/11/2014
150
Smith
09/15/2014
110
Barnes
09/20/2104
120
Martinez
09/30/2014
120
Jackson
09/30/2014
90
Hsu
09/30/2014
60
b. On the same worksheet, but to the right of the data above, create the following table
to show calls made by each salesperson this quarter. Use the sumproduct function to
create a formula that automatically calculates the total calls handled.
Calls Made by Salesperson This Quarter
July
August
September
Barnes
Hsu
Jackson
Martinez
Smith
(Hint: Read the article “Supercharge Your Excel Sum Operations: Add data by up to 30
criteria,” by J.D. Kern in the July 2009 issue of the Journal of Accountancy for an
explanation of the sumproduct function and the use of double dashes. The Journal of
Accountancy is available either in print or online at www.aicpa.org).
The solution spreadsheet is available on the website. Key formula: July sales for Barnes (will
12.14 The following table presents the results of using a CAAT tool to interrogate the XYZ Company’s ERP system for
revenue cycle activities. It shows the number of times each employee performed a specific task.
Take
Order
Approve
Credit
Ship
Inventory
Maintain
A/R
Issue
Credit
Memo
Bill
Customer
Deposit
Customer
Remittances
Reconcile
Bank
Account
Employee A
250
5
15
Employee B
305
100
Employee C
275
10
Employee D
85
10
5
Employee E
400
25
Employee F
430
Employee G
600
Employee H
400
20
Employee I
15
430
25
Employee J
650
1
Required: Identify five examples of improper segregation of duties and explain the nature of each problem you find.
1. Employee A takes orders and approves credit. Employee may collude with a friend to make sales that turn out to be
uncollectible.
2. Employee A takes orders and bills customers. Employee may fail to bill sales made to friends.
3. Employee B takes orders and bills customers. Employee may fail to bill sales made to friends.
4. Employee D approves credit and issues credit memos. The employee could make sales to friends on credit and then
write those sales off.
5. Employee D issues credit memos and deposits customer remittances. The employee could steal remittances and conceal
the theft by issuing a credit memo to write down the customer’s account.
6. Employee E both ships inventory and bills customers. The employee could ship merchandise to friends and not invoice
them.
7. Employee H maintains A/R and handles customer payments. The employee could commit lapping.
8. Employee I approves credit and issues credit memos. The employee could make sales to friends on credit and then issue
credit memos to write off the accounts.
9. Employee J handles customer payments and reconciles the bank account. The employee could steal payments and
conceal the theft by manipulating the bank reconciliation.
SUGGESTED ANSWERS TO THE CASES
Case 12.1: RESEARCH PROJECT: Research Project: How CPA Firms Are
Leveraging New Developments in IT
Find articles from the past year in the Journal of Accountancy, The New Accountant, and
any other magazine suggested by your instructor that explain how CPA firms are using IT
developments (e.g., the cloud, BYOD, mobile, etc.). Write a report that explains the benefits
of the new technology (cost reduction, revenue increases, customer attraction and
retention, etc.) and how the firm mitigates any new threats associated with that technology.
Answers will vary – to grade, require the student to submit a copy of the article and compare to
the report.