BYU Student Author: @Alex_Garrett
Reviewers: @Jae, @klayton
Estimated Time to Solve: 50 Minutes
We provide the solution to this challenge using:
- Alteryx
Need a program? Click here.
Overview
Auditors exercise various audit procedures to ensure that management’s assertions on the financial statements are not materially misstated due to fraud or error. One of these procedures is “recalculation” which describes how auditors recalculate financial statement line items to ensure accuracy and precision.
After the 20X2 year-end, you are assigned to audit the transactions of Steven’s Sweet Shop. Steven’s is a gummy candy wholesaler, and you have been assigned to audit the vehicle division (note that these are gummy vehicles). You are given some data generated by the company, and your task is to recalculate certain line items to see if your numbers match the company’s numbers. Make sure to read the data dictionary the company provided to better understand the data before starting.
Instructions
The company also gave your firm a copy of its calculations, which your manager is skeptical about. To eliminate bias, your manager wants you to calculate these values “blind,” meaning she doesn’t want the company’s numbers to influence your calculations. Use three “Browse” tool to display your solutions for the following tasks.
- Calculate the total sales (in $) for each product by quarter. Your table should have the following headers: Product_Name, Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales.
- Calculate the average revenue per order for each product in 20X2. Treat every unique combination of “Customer_ID”, “Product_ID”, and Quarter as a separate order (where sales > $0). Your table should have the following headers: Product_Name, Avg_Revenue
- Typically, a customer will order at least one pallet of each product during the year. When this does not happen, it can be an early sign of an uncollectible account. Create a workflow that displays all customers that did not order each of the 4 products during the year. Your table should have the following headers: Customer_ID, Customer_Name, Product_ID, Product_Name
Data Files
Suggestions and Hints
- Task 1. “Motorcycles” have the highest Q1 Sales at $120,897,964
- Task 2. “Classic Cars” have the highest average revenue per order at $1,547,413 (rounded to the nearest dollar)
- Task 3. Try using the “Append Fields” Tool to list all possible combinations of “Customer_ID” and “Product_ID” then outer join your table to the “Packing” table. Note this is only 1 of the many ways to complete this task.
Solution
