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:
US SUPERSTORE PRODUCTS
FURNITURE OFFICE SUPPLIES TECHNOLOGY
Bookcases Appliances Art Accessories
Chairs Binders Envelopes Copiers
A full list of all US Superstore dataset variables, as shown in Exhibit 2 and demonstrated by the first-row data entry:
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
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.
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.