Auditing 010:430

Spring 2021

Group Project

INTRODUCTION

US Superstore has been very successful with sales increasing rapidly. Management has a desire to begin selling its products globally within the next five years. Revenue is recorded at the time of shipment (FOB shipping point) with company-offered discounts ranging from 0–20 percent, depending upon the products purchased and type of customer.

Regular walk-in customers receive no discounts. However, corporate and home office customers receive the following discounts: chairs: 5 percent; tables: 10 percent; appliances: 20 percent. All discounts have a payment due date of 15 days. In order to secure a loan, management has requested your services to perform a financial audit for the 2014 year-end to provide the bank and potential investors with confidence in US Superstore’s financial position. US Superstore’s files from 2011 onward are currently maintained in Excel worksheets. There are three worksheets available: order to cash, returns, and personnel. The company provides customers with three main categories of products: office furniture, office supplies, and technology. Customers are grouped into Consumers, Corporate, and Home Office. The geographical regions are Central, East, West, and South. The sales managers assigned to these regions include Kelly Williams: Central; Chuck Magee: East; Cassandra Brandow: South; and Anna Andreadi: West. Customer sales data are available for the years 2011 through 2014. Exhibit 1 includes the list of products for US Superstore:

EXHIBIT 1

US SUPERSTORE PRODUCTS

FURNITURE	OFFICE SUPPLIES		TECHNOLOGY
Bookcases	Appliances	Art		Accessories
Chairs	Binders	Envelopes		Copiers
Furnishings	Fasteners	Labels
Tables	Paper	Storage
	Supplies

A full list of all US Superstore dataset variables, as shown in Exhibit 2 and demonstrated by the first-row data entry:

EXHIBIT 2

US SUPERSTORE DATASET VARIABLES

DATASET VARIABLE	EXAMPLE	DATASET VARIABLE	EXAMPLE
Row ID Number	1	Order ID Number	CA-2013-152156
Order Date	11/9/2013	Ship Date	11/12/2013
Ship Mode	Second Class	Customer ID	CG-12520
Customer Name	Claire Gute	Segment	Consumer
Country	United States	City	Henderson
State	Kentucky	Postal Code	42420
Region	South	Product ID	FUR-BO-10001798
Category	Furniture	Product Name	Bush Somerset Collection Bookcase
Sub-Category	Bookcases	Quantity	2
Sales	261.96	Date Paid	2/15/2012
Discount	0	Cash Receipts	1250.00
Payment Date	1/23/2011

The US Superstore Excel file has 23 columns (as listed in Exhibit 2) and 9,994 transaction lines that provide the data for

order to cash information related to US Superstore’s revenue. The file has two additional worksheets that include returns and sales staff and their regional responsibilities. Appendix A provides a data hierarchy of US Superstore’s database and a summary of basic database terminology.

CASE ASSIGNMENT

US Superstore has been accepted by your firm as a new audit client. You have been assigned to undertake data analytics for the planning and substantive testing phases for the 2014 audit engagement utilizing IDEA and Tableau. The engagement partner has requested that you attempt to analyze the entire population rather than using a sample to draw inferences to the population. Therefore, your analyses and findings will be conducted and presented using both IDEA and Tableau. For example, you will use visualizations to better understand the data and potential risk areas in auditing the revenue (order to cash) cycle. The case is divided into three parts: Part 1 focuses on obtaining an understanding of data analytics and potential impact in an audit engagement; Part 2 addresses audit-planning issues with US Superstore; and Part 3 requires detailed data analytics with Tableau, with results presented in visualization form.

You will complete the case in groups. You are also required to provide evaluations of percent of contribution of the other members of your group. These percentages will be multiplied by the final grade to get your score. Thus, if you are in a group of four and your group believes you did 25% of the work, and your group score is 80—then you would receive an 80. However, if they say you only did 20% of the work, then you would get a 64. The instructions for the group evaluation can be found in the last section of this document.

Part 1—Getting Started

Read the following publications to gain a better understanding as to the importance of data analytics and its impact on auditing:

PricewaterhouseCoopers: Data Driven—What Students Need to Succeed in a Rapidly Changing Business World: https://www.pwc.com/us/en/faculty-resource/data-driven.html (PwC 2015)

EY: Analytics: Don’t Forget the Human Element: https://www.ey.com/Publication/vwLUAssets/EY-ForbesInsights-Data-and-Analytics-Impact-Index-2015/$FILE/EY-Forbes-Insights-Data-and-Analytics-Impact-Index2015.pdf (EY 2015)

Download the free Tableau software to your laptop or desktop. The instructions for the Tableau landing page (web page), as well as a key to activate the software are as follows:

Download the latest version of Tableau Desktop and Tableau Prep Builder here

Click on the link above and select “Download Tableau Desktop” and “Download Tableau Prep Builder”. On the form, enter your school email address for Business E-mail and enter the name of your school for Organization.

Activate with your product key:  TC6H-0104-2AA0-AB78-A0CC. 

If you already have a copy of Tableau Desktop installed, you can update your license in the application: Help menu → Manage Product Keys

If you are new to Tableau, you can review the introduction video, Data Analytics for University Students guide to help you get started.

Download the free IDEA software to your laptop or desktop. The instructions are as follows:

Step 1: Register for a student account on the IDEA Academic Portal to access the IDEA Academic Partnership Program features, such as the Student Dashboard where you can enroll in courses, view course information, and access course material.

Step 2: Please click here to enroll in the course. The course access code is embedded in the link. You can also use the course code KZ1NEFXP to enroll manually. The course access code is valid until the course expiration date.

Part 2—Audit Planning

For audit-planning purposes, your group needs to open the US Superstore data file and import the client’s data (i.e., Excel file) into an IDEA database. The US Superstore Excel file provides complete order to cash data (i.e., order, return, and sales personnel data) files from 2011 through 2014. Once the data is imported into IDEA, perform the following audit procedures. Please include screenshots of the IDEA output.

Determine that the customer order file is complete (i.e., completeness assertion). Create a schedule of missing customer order forms. How many missing order forms were there?

Each customer order should be entered into the system once and only once. Using IDEA, search for duplicate customer order forms. What is the total dollar amount of duplicate orders?

Prepare an analysis of sales by year, by customers, and by products.

Are there any customers that made their payments after the stated due date? How many companies, and what is the total dollar amount of the payments?

There seem to be issues with the high number of returns for certain products and with discounts provided to customers.

Prepare an analysis of returns by customer and by product.

Prepare an analysis of discounts provided to customers.

Using Tableau perform the following audit procedures:

The partner on the engagement, Daniel Detailed, has asked you to prepare three visualizations with one dimension and at least one dashboard (related dimensions) concerning the issues in part e above. (Note: A dashboard consists of two or more related visualizations.) Also, attempt to identify from your dashboards any potential risk areas for the upcoming audit. Each group will be required to discuss one of their dashboards in detail and explain its importance to the audit.

Prepare two visualizations representative of preliminary (i.e., planning) analytical procedures related to revenue. Your group will be required to discuss the visualizations in detail and explain the objective of the analytical procedure. Refer to the discussion of preliminary analytical procedures in chapter 4 (pages 137 – 143).

Identify the financial statement assertions that need to be focused on in the upcoming audit for revenue transactions (Refer to the discussion in Chapter 7, page 276). In your analysis, you will need to join the three files. To combine data files in Tableau, the data files must have the same unique primary key; for example, in the US Superstore data, one primary key is ‘‘Region’’ that will link the ‘‘Orders’’ data file with ‘‘People.’’ Appendix B (How to Import Data to Tableau) provides additional details. A concern in data analytics is whether the data need to be cleansed or scrubbed. Such cleansing or scrubbing of an entity’s data might be required before the data can be analyzed. For example, a data file might include fields or cells that have no data but should contain data, or data entries that include spelling errors. To analyze a file with missing or incorrect data often results in unreliable results. For the US Superstore case, the data have been scrubbed and all cells or fields contain the proper data to be analyzed.

Part 3—Substantive Testing of the Revenue (Order to Cash) Cycle. As revenue is considered a high-risk area for most audit engagements (PCAOB 2014), you will utilize Tableau to create data visualizations and dashboards for various aspects of the audit of US Superstore’s revenue (order to cash) cycle and related accounts. For the following issues, first, brainstorm in your group to develop questions that you need to evaluate with Tableau, and second, prepare the necessary data visualizations related to the following issues and questions you developed. (Refer to Chapter 7, Revenue and Collection Cycle). Your engagement partner provided the following initial substantive testing issues to your team that need to be evaluated:

Are there any indicators of slow-moving product?

Are there any indications of cut-off issues?

Are there any indicators of related-party transactions?

Which customers should receive confirmations?

Are there any issues or concerns with discounts and cash receipts?

What is the Accounts Receivable 12/31 balance for each year? Are there concerns as to the quality of US Superstore’s accounts receivable?

For 12/31/14, what is the total amount of accounts receivable that are more than 30, 60, and 90 days past due? Based on this analysis, identify risky items. Explain why you consider these items risky.

Are there any indicators that inventory might be overvalued?

What are shipping costs per region or product?

Are there any other issues you think are important?

Submission Instructions Due Dates

Part 1: The PWC and EY material needs to be read prior to beginning the audit procedures.

Parts 2 and 3: The completed project is due during the last week of class. Please upload the completed case to Canvas. Where necessary, include screen shots of the IDEA and Tableau output. On the last day of the semester groups will present and discuss examples of their dashboards. You should prepare your presentation as though you were discussing relevant audit issues with the engagement partner.

GROUP EVALUATION FORM

The members of your group will meet to evaluate the project and assess the contribution of the team members. Having the project done by teams is an important part of the approach. Audits are done by teams and auditors must learn to operate successfully in this mode. The team’s report determines the grade for each member of the team based on the group evaluation form on the following page. Your evaluations should represent your considered judgment on the following criteria concerning each member’s contribution:

Attendance. Was he/she always present, came promptly, and stayed for the entire period both in class and in the team’s agreed upon out of class meetings?

Preparation. How carefully and effortful did he/she study and therefore was prepared to help you learn in your in-class and out-of-class discussions?

Contribution. To what extent did he/she speak up and contribute actively and regularly to your discussions?

Respect for others. Did he/she allow others to speak and did he/she listen and respond reasonably and respectfully to others, or did he/she dominate the discussion and dismiss others’ contributions? Did he/she notify the team when he/she was going to be unavoidably absent or late for class or out-of-class meeting?

Flexibility. Could he/she adapt to others’ wishes and needs, and others’ ideas when there was disagreement in the team?

It is important that your raise the evaluations of people who truly worked hard for the benefit of the team and lower the evaluations of people you observed not to be working as hard on things that affected the team. People who worked hard and made a major contribution to your learning and team process should be given full credit. Those who did not should only receive partial credit.

Grading

Group projects will be graded to generate a group numerical score. This score will be adjusted to capture each student’s contribution to the project. The following procedure will be used to determine each individual student’s score/grade for the project.

The group must hand in ONE sheet that includes the following. This sheet is due on April 30, 2020.

Names Signatures % of total effort (must sum to 100%)

The effort assigned to each group member must be within a minimum and maximum range. The range allows for a 25% positive or negative change in each student’s effort. The range depends on the number of group members, and is as follows:

Number of Group Members Min. Effort Equal Effort Max. Effort

Three 25% 33.3% 41%

Four 19% 25.0% 31%

Five 16% 20.0% 25%

Any group member who does not sign the sheet will be assigned the minimum effort.

Each individual student’s points for the project will be calculated using the following formula:

Individual grade for the project = group score x effort x number in group

Examples:

A three person group receives 80% on the project. Everyone works equally hard and the group assigns efforts = 100/3 = 33.3

Name Group grade x Effort % x # in Group = Individual Grade

Desi 80% x 33.3% x 3 = 80%

Caleb 80% x 33.3% x 3 = 80%

Henry 80% x 33.3% x 3 = 80%

A four person group receives 80% on the project. One person works very hard and is assigned the maximum effort of 31%, two persons exert standard effort and are assigned 100/4 = 25%, and one person does not show up for group meetings and is assigned the minimum effort of 19%.

Name Group grade x Effort % x # in Group = Individual Grade

Cole 80% x 31% x 4 = 99.2%

Clinton 80% x 25% x 4 = 80%

Elizabeth 80% x 25% x 4 = 80%

Tali 80% x 19% x 4 = 60.8%

Tags: assignmentexpert, assignmenthelp, assignmenthelpaustralia, assignmenthelper, assignmenthelpuk, assignmenthelpusa, assignmentwriting, bestpriceguaranteed, bestqualityguaranteed, london🇬🇧, myassignmenthelp, plagiarismfreework