59|TABLEAU – One for the Ages

Challenges
, , ,
1

BYU Student Author: @MitchFrei
Reviewers: @Erick_Sizilio, @Donovon
Estimated Time to Solve: 45 Minutes

We provide the solution to this challenge using:

  • Tableau

Need a program? Click here.

Overview
Congratulations! You have been working for Amazing Auditors (AA) for two years and you were just promoted to Senior. Today is January 13th, 2023 and it’s time for another busy season. You are auditing the 2022 financial statements of a fairly large client who does upwards of $75M in revenue each year. With your newly added responsibilities, you are taking the lead on assessing the client’s stated amount for the Allowance for Doubtful Accounts (AFDA). You have been provided with the client’s invoice information which you can use to replicate an Accounts Receivable (AR) Aging report and compute your best estimate of the AFDA amount. The client’s stated amount is $116,388 and you would be comfortable with a difference of $30,000 between the stated amount and your computed estimate without further testing. What does the data say? Is more testing required?

Instructions
Using Tableau, replicate an AR Aging report and compute your best estimate of the AFDA amount for the client’s 2022 financial statements. Click here for more information on AFDA and AR Aging reports. Follow these guidelines as you create your report:

  • The client provided you with invoice information that includes the Invoice Date, Invoice Amount, Date Paid, and Customer for 2022.
  • Invoices are considered outstanding if the Date Paid column is blank, indicating that the customer has not paid the invoice as of December 31, 2022.
  • The client’s credit terms are net 30, meaning the invoice is due 30 days from the invoice date. - If the invoice has not been paid and is not yet due as of December 31, it should be labeled as “Current.” If the invoice is past due as of December 31 and the client has not paid, the invoice amount should be placed into one of five aging buckets: 1-30, 31-60, 61-90, 91-120, and 120+ depending on the number of days outstanding as of December 31.
  • The client estimates the following percentages of these aging buckets will be uncollectible: 1% of the 1-30 bucket, 5% for 31-60, 15% for 61-90, 50% for 91-120, and 90% for 120+.
  • Create one dashboard containing two sheets: one for the aging report, and one for the AFDA estimate.
  • Your aging report should have six columns (one for each aging bucket plus one for “Current”) and one row for each customer.
  • Format the Invoice Amount as currency with 0 decimal places, make the aging buckets different colors to differentiate them, and include column and row totals.
  • Your AFDA estimate sheet should include 3 labeled numbers: AFDA Estimate, Stated Amount ($116,388), and Computed Difference.

Data Files

Suggestions and Hints
  • Consider creating the following calculated fields: Year End, Due Date, Days Outstanding, Aging Buckets, AFDA Estimate, Stated Amount, and Computed Difference.
  • Make sure to filter for Invoices where the Date Paid field is blank. Put Date Paid on the filter shelf, select individual dates and times, and select Null.
  • You can add days to a date using DATEADD, or by simply using “+”
  • You can subtract dates from each other to get the number of days between them.

Solution

Solution Image

Challenge59_Solution_Image
Challenge59_Solution_Image1252×795 60.7 KB

Challenge59_Solution.tbwx
Solution Video: Challenge 59|TABLEAU – One for the Ages

2

This was a fun challenge! One thing that I really like about creating an AR Aging report is that we can update it automatically by just updating data source.

3

What a neat challenge. You know, I never would have thought to go to Tableau to create an AR aging chart, but I learned plenty of new tools to use in the future. The names of the calculated fields you gave in the hints section was super helpful to me. Thanks Mitch!

image
image1600×910 92.8 KB

4

Great Challenge! I can see this being super useful for management or even auditors!

image
image1970×1318 226 KB