133|EXCEL – Audit Intern Atrocities

BYU Student Author: @Brett_Lowe
Reviewers: @Nate, @Hyrum, @Andrew
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
Your firm is about to publish an audit opinion for your client, Nebula Nexus, but you just learned that your intern forgot to check for unrecorded liabilities. If a problem exists, it’s likely material. The partner is giving you 15 minutes to find any issues (if there be any), and she threatened to take away your bonus if you miss one—material or not. Fortunately, you have enough data to perform the search, but you’re a tad rusty with this portion of the audit.

Instructions

  1. Download the data
  2. You’ll need to confirm that the payments made in January were included as liabilities on the balance sheet at year-end. Using a lookup function in column H of the Cash Disbursements sheet, find the corresponding Invoice Date for each Reference No. that’s reflected in the Vendor Invoice Detail sheet. If the reference number isn’t found, return the text “Not Found.”
  3. For each payment that isn’t found in the year-end report, you’ll need to investigate further. Copy all the missing payment information into columns G through K of the Receiving Report sheet.
  4. In column L of the Receiving Report sheet, use another lookup to find the date that the services or goods were received. The payments that are missing and received before year-end will be your unrecorded liabilities.

Data Files

Suggestions and Hints

Lookups can be powerful when used correctly. If you’re getting an error, select the cell that contains the formula and confirm that you’ve used consistent, valid inputs. If you’re still getting an error, consider asking someone else in the office or check the spoilers for help.

Solution

Thanks Brett for the short and sweet practice! I used xlookup and filter tool to accomplish the task. The preset format really helped. Thanks again.

Challenge133_Data.xlsx (23.8 KB)

Great quick challenge. I used arrays for my lookup values. This saved me time by preventing me from having to make my references absolute, and preventing me from having to copy my formula down. I also used a filter function for the missing payments section.
Challenge133_Data_JacobDutton.xlsx (23.8 KB)