We provide the solution to this challenge using:
Need a program? Click here.
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?
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.
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.