Overview
You are auditing Porter’s Plates, a company that sells glassware throughout the United States. Porter has been a good client of your audit firm for a few years now and the partner over the engagement has never given a qualified audit opinion on the company’s financial statements. Because Porter’s Plates has consistently reported financial results in compliance with accounting standards and the company is a good client, there is some pressure to give another good report. It’s also possible that the team is used to Porter’s Plates reporting fairly that they have lost some professional skepticism. As a new member of the revenue auditing team for Porter’s Plates, you have access to their employee list, customer list, inventory files, sales orders, invoices and cash receipts. However, you do not have any of last year’s financial data, so SALY (same as last year) isn’t here to save you. Use the Excel formulas and functions you’ve learned to connect all of these data tables and find insights worthy of sharing with the senior members of the audit team.
Instructions
Connect all the worksheets so that you can compare the data from all sheets to one another. When you are done, you should be able to compare things like the sales order total with the invoice total, for example.
Use the skills and tools you have to identify risk areas within Porter’s financial data. There’s no limit to what you can do! See the hints for ideas as needed.
Find a way to summarize or present your data for other people on your team. See the hints for ideas as needed.
The solution does this within the same sheet using XLOOKUP.
The solution uses PivotTables to perform the analysis. Some examples of risk areas are increasing revenue at the end of the period (month or year), creating fake transactions, creating fake customers, and premature invoicing.
The solution uses PivotTables for the presentation. Some things to consider: are there salespeople that change behavior over time or for different customers? Could you compare items that should reconcile but don’t currently reconcile? You could use PivotTables or graphs to show any trends or outliers.
This was a good challenge. I spent most of my time getting all of the tables connected. I followed the instructions and compared Sales Order totals, Invoice totals, and Cash Receipts. I also did some of the suggested risk areas for premature invoicing and sales made on the last day of the year. I found 3 employees that share addresses with customers, 13 invoice amounts that don’t match the SO total, $300,000 in last day sales, and 0 invoices made before the Sale/Delivery date. The 3 employees and 13 invoices are definitely concerning, but the last day sales make up less than 0.5% of their annual sales and not an issue at all.
This challenge really made me think through each data table and how it connected to the others. I also found 3 employees with similar addresses to customers. I really like how @Jonathan_Weston looked for sales made on the last day of the month. This inspired me to check the job title breakout of employees that made sales on the last day of the month. My thought was if “Sales Representatives” made up a larger portion of sales on the last day of the month than normal, this could be evidence of pressure from managers to meet sales goals. However, last day of the month sales by job title percentage is fairly consistent with normal sales by job title percentage (only a slight increase of 71% to 75% for sales representatives).
Really good challenge. This really got me thinking about where I ought to investigate to find potential audit risks. Here are some of the things I looked at!
Here is my pivot table for sale people with addresses on the invoice that match their personal address!
I wanted to investigate your comment regarding whether salespeople change their behavior over time. It seems like the total number of sales from month to month by employee is relatively similar. No obvious patterns that stand out.
I was also curious if the amount of discounts would go up at the end of the year, because salepeople are trying to reach goals, but discounts seem pretty evenly distributed throughout the year.
Also wondered about the relationship between the total amount of discounts given and total amount of sales by salesperson. There might be a slight correlation, but nothing warranting further investigation. If anything, offering discounts seems to be more of a sales tactic rather than suspicious employee behavior.
Creative challenge! I liked having to link all the tables together because it got me thinking of the connections between the data. I made a couple extra columns that returned either a one or zero depending on if the employee and customer had the same address and if the invoice amount equaled the receipt amount. That is how I used the pivot tables to find places to focus on.
In addition to what’s already been shared, I looked at various other metrics across employees to see if anyone was different from the norm. For example, here’s a very cramped chart showing the number of sales each employee had in state, which seems pretty consistent.
I loaded the excel file into SQL Server and using T-SQL wrote this query to combine all the data:
SELECT
*
FROM Sales_Order_Summary sos
JOIN Sales_Order_Detail sod ON sos.SO_Num = sod.SO_Num
JOIN Employees e ON sos.Emp_ID = e.Emp_ID
JOIN Customers c ON sos.Cust_Num = c.Cust_Num
LEFT JOIN Invoice_Summary invs ON sos.SO_Num = invs.SO_Num
LEFT JOIN Invoice_Detail id ON invs.Invoice_Num=id.Invoice_Num AND sod.Brand_ID = id.Brand_ID
JOIN Inventory i ON sod.Brand_ID = i.Brand_ID
LEFT JOIN Cash_Receipts cr ON invs.Invoice_Num = cr.Invoice_Num
It should be noted that not all tables should be combined at all times. For example, the cash receipts table shouldn’t be combined with the invoice detail table because there is no logical way of allocating cash receipts across each individual invoiced item.
This was an interesting challenge but my experience was severely hindered by the sheer size of the data set. After crashing my computer and restarting the process of combining the data 3 times, I decided it might be better to just download the solution file and work from there . Probably time for a new computer.
. Probably time for a new computer.