M6 – Project 3 Revenue AnalysisSubmission [Individual]
Scenario
You have been assigned as the associate on an audit engagement with BCGG, Inc. They are a
wholesaler, selling wine and spirits to retail liquor stores in Maine, Connecticut, Rhode
Island, and Massachusetts. The client is based in Worcester, MA, and has a fiscal year-end of
June 30.
The client was asked by their bank to provide audited financial statements for the year
ending June 30, 2019. The prior year’s financial statements were reviewed by a small CPA
firm in Holden, MA. The bank does not need the 2018 financial statements audited;
therefore, PwC will disclaim any opinion on the June 30, 2018 financial information.
The CFO has provided some financial information for the company as well as process
flowcharts for sales and purchases for the company. Additionally, you have been provided
with several data tables for BCGG, Inc. for June 30, 2019. Data definitions for each table
are included.
Read the email from the auditing manager on your engagement to learn more about your
task.
EMAIL
To: Audit Associate
Subject: BCGG audit engagement
Dear Colleague,
I recently learned about your having completed training in Auditing in a Big
Data Environment. Given your experience, I’d like for you to experiment
with some audit tests with our client, BCGG, Inc.
I’m sharing with you the data files from the Controller and the Chief
Information Officer for beginning and ending inventory, purchases, sales,
summary sales by brand, summary purchases by brand, sales by invoice,
purchases by invoice, purchase prices, customer list, vendor list, and
product classification.
•
•
•
BCGG Sales Process Manual 2017Download BCGG Sales Process
Manual 2017
BCGG Purchase Process Manual 2017Download BCGG Purchase
Process Manual 2017
BCGG Data Tables Download BCGG Data Tables
You need to examine BCGG’s internal controls around the sales cycle. BCGG
hired a different accounting firm to perform its internal audit function. The
internal audit team has performed some tests of controls for the audit team,
and PwC would like to rely on their testing, if possible, to make the audit
more efficient. The internal audit team cannot plan the tests for PwC;
however, the external audit team believes that the internal audit team is
trained properly to conduct some tests of controls. The internal audit team
indicated that each customer remained below the pre-established credit limit
or received proper approval. You need to see if the internal audit team
performed the test properly. In addition to the test of controls, PwC’s audit
team wants you to perform a risk screening revenue analysis to identify
sales transactions that exhibit anomalous behavior.
I’m looking forward to receiving a report from you that includes the tests
you carried out.
Thank you for your help on this important project,
PwC Senior Associate
Copyright of Hub of Analytics Education. All rights reserved. For Educational Purposes Only. No
part of the Bibitor, LLC dataset or BCGG, Inc. dataset nor the supporting materials may be
reproduced, distributed, or transmitted in any form or by any means, including photocopying,
recording, or other electronic or mechanical methods, without the prior written permission of
The Hub of Analytics Education, except in the case of brief quotations embodied in critical
reviews and certain other noncommercial uses permitted by copyright law. Indirect use for
commercial purposes (i.e., materials referencing or using these materials) is also prohibited
without the prior written permission of The Hub of Analytics Education. For permission requests,
email Charles Bame-Aldred, at c.bamealdred@hubae.org.
Directions
For this assignment, you will prepare two work papers: “Evaluation of the Test of Controls
for Anomalous Sales” and “Risk Screening Revenue Analysis”, along with associated Excel
documents. Templates, which includes detailed steps are provided for both work papers.
Evaluation of the Test of Controls for Anomalous Sales
You will evaluate if the internal audit team appropriately performed a test of controls. You
will perform a test of controls that tests whether all BCGG’s customers were within their
credit limit or had proper approval for each of BCGG’s sales transactions.
To mitigate the risk of uncollectible accounts, BCGG has controls in place that establish
credit limits for customers. If customers want to exceed their pre-established credit limit,
the customer will need proper approval from the appropriate BCGG personnel (review
the process manuals for more details). The internal audit team indicated that all
customers either stayed below their credit limit or had proper approval from BCGG
personnel. Before continuing to rely on the internal audit team’s work, PwC wants to test
the internal audit team’s conclusion. You will verify that all customers had a running
balance owed to BCGG of less than or equal to their credit limit or that the appropriate
BCGG personnel approved the sale on credit.
Risk Screening Revenue Analysis
Once you complete the evaluation of the test of controls, PwC’s audit team wants you to
perform a risk screening revenue analytic to identify sales transactions that exhibit
anomalous behavior. You will perform substantive analytical procedures to create a “large
sales” table and a “large sales near quarter-end” table.
Anomalous sales transactions are not inherently incorrect; however, they pose a greater
risk to the auditor as they are more likely to cause a material misstatement. The results of
your tests will require additional audit testing, but this analytic will help identify
transactions to examine in more detail (risk-based auditing approach). The external audit
teams want you to provide a “large sales” table and a “large sales near quarter-end” table.
The large sales near the end of the quarter are of particular risk and is a test that is
performed consistently during the audit process. The external audit team wants you to
create an automatic workflow to identify these anomalous sales. The audit manager wants
you to evaluate the distribution of the sales by customer. For the purpose of this audit
procedure, the audit team’s manager has determined large sales to be defined as either
sales > 75th percentile + 1.5*Interquartile Range by customer or sales > 3 standard
deviations above the mean by customer. The audit manager wants you to choose the
appropriate definition of large sales based on your judgment of the sales
distributions. For large sales near-end of the quarter, analyze sales that are within 5 days
of the quarter end. (Hint: You’ll need to calculate date differentials and remember that
this client has a 6/30 year end.)
Key Tasks
•
•
Learn about the Company:
o Make sure you reviewed information provided in the Scenario section of this
assignment, along with attached documents in that section.
o Review Project 3 data filesLinks to an external site. (Note: You must login with
Northeastern credentials to view).
Work through “Project 3 Risk Screening Revenue Analysis Download Project 3
Risk Screening Revenue Analysis” and “Project 3 Evaluation of the Test of
Controls for Anomalous Sales Download Project 3 Evaluation of the Test of
Controls for Anomalous Sales” Templates as your guide to complete this
assignment.
o
▪
•
•
Please note that not all steps in the template may be applicable. For
example, if you perform population testing, you are not sampling. In
this instance, you write “Not applicable because…”
Work on Excel for the Risk Screening Revenue Analysis. Make sure to save your files
to submit.
For audit documentation purposes, assume BCGG is publicly traded.
Audit Standards
AS 1215: Audit DocumentationLinks to an external site. (30 minutes) — This standard
establishes general requirements for documentation the auditor should prepare and
retain in connection with engagements conducted pursuant to the standards of the Public
Company Accounting Oversight Board (PCAOB).
What to Submit
•
Submit following files in Canvas:
1- Submit your completed Project 3 Risk Screening Revenue Analysis Download Project
3 Risk Screening Revenue Analysis, which is a substantive test associated with
anomalous sales. Make sure your document follows the audit steps listed in the Key Tasks.
2- Submit your completed Project 3 Evaluation of the Test of Controls for Anomalous
Sales Download Project 3 Evaluation of the Test of Controls for Anomalous Sales, which
is a test of controls associated with credit limits. This will also follow the audit steps listed
in the Key Tasks.
3- Submit Excel files related to Project 3, so the test can be replicable.
•
Length Requirement: There is no length requirement for this assignment, instead,
you need to ensure you address all the components of the Revenue Audit.
Rubric
Please review the rubric below to ensure that your submission covers all requirements.
Due 6/18, Tuesday by 11:59 PM EST
Project 3 Rubric (1)
Project 3 Rubric (1)
Criteria
This criterion is linked to a
Learning OutcomeAssertion
Level Correctness and Test
Design
[C8O1|C8O6|C1C1|C1C12]
This criterion is linked to a
Learning OutcomeAudit
Test(s) Procedures and
Reproducibility
[C8O1|C8O6|C1C1|C1C12]
Ratings
25 to >20.0 pts
Standard
Identifies and
properly designs an
audit procedure(s),
which if performed
correctly, will
provide evidence
regarding the
appropriateness of
the financial
statement account
or disclosure
20 to >17.5 pts
Approaching
Standard
Identifies and
properly designs an
audit procedure(s),
which if performed
correctly, will
provide evidence
regarding the
appropriateness of
the financial
statement account or
disclosure, but with
gaps in detail or
logic
25 to >20.0 pts
Standard
Properly executes
the test(s) given the
design, and
documents the
procedures
completely so the
procedures could be
replicated by
another reasonably
trained auditor
20 to >17.5 pts
Approaching Standard
Properly executes the
test(s) given the design,
and documents the
procedures completely
so the procedures could
be replicated by another
reasonably trained
auditor, but with gaps in
detail or logic
Pts
17.5 to >0.0 pts
Below Standard
Does not identify or
properly design an
audit procedure(s),
which if performed
correctly, will
provide evidence
regarding the
appropriateness of
the financial
statement account
or disclosure
0 pts
No
Marks
17.5 to >0.0 pts
Below
Standard
Does not
properly execute
the test(s) given
the design, and
does not
document the
procedures
completely
0 pts
No
Marks
25 pts
25 pts
Project 3 Rubric (1)
Criteria
This criterion is linked to a
Learning
OutcomeTechnology
Application
[C8O1|C8O6|C1C1|C1C12]
This criterion is linked to a
Learning
OutcomeInterpretation of
Results and Conclusion
[C8O1|C8O6|C1C1|C1C12]
This criterion is linked to a
Learning
OutcomeMechanics
Ratings
10 to >8.0 pts
Standard
Excel file
contains the
audit test
performed.
25 to >20.0 pts
Standard
Properly interprets
the result(s) of the
audit procedures,
including
identifying errors
or items needing
further
investigation
5 to >4.0 pts
Standard
Grammar,
spelling, and
punctuation
adhere to
professional
standards
Pts
8 to >7.0 pts
Approaching Standard
Excel file contains the
audit test performed but
with gaps in detail or
logic.
7 to >0.0 pts
Below Standard
Does not provide an
excel file that
includes audit tests
performed.
0 pts
No
Marks
20 to >17.5 pts
Approaching
Standard
Properly interprets the
result(s) of the audit
procedures, including
identifying errors or
items needing further
investigation, but with
gaps in detail or logic
17.5 to >0.0 pts
Below Standard
Does not properly
interpret the
result(s) of the
audit procedures,
nor identify errors
or items needing
further
investigation
0 pts
No
Marks
3.5 to >0.0 pts
Below Standard
Grammar, spelling,
and punctuation
impedes the
understanding of the
ideas/concepts
0 pts
No
Marks
4 to >3.5 pts
Approaching
Standard
Grammar, spelling,
and punctuation
negatively impact the
professionalism of the
submission
10 pts
25 pts
5 pts
Project 3 Rubric (1)
Criteria
Ratings
Pts
This criterion is linked to a
Learning
OutcomePresentation
10 to >8.0 pts
Standard
Articulation of
information, ideas,
and concepts is
well-organized and
professional, and
demonstrates indepth understanding
of the target
audience’s needs.
This includes
internal workpaper
consistency,
including but not
limited to, that each
documented test
corresponds to a test
performed, and the
results documented
Total Points: 100
8 to >7.0 pts
Approaching
Standard
Articulation of
information, ideas,
and concepts is
drawn-out, unclear,
disorganized, and/or
does not consider
the target audience.
This includes
internal workpaper
inconsistency,
including but not
limited to, that each
documented test
does not correspond
to a test performed,
or the results
documented
7 to >0.0 pts
Below Standard
Articulation of
information, ideas,
and concepts is
disorganized,
unprofessional, AND
does not consider the
target audience. This
includes internal
workpaper
inconsistency,
including but not
limited to, that each
documented test
does not correspond
to a test performed,
AND the results
documented
0 pts
No
Marks
10 pts
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
