BYU Student Author: @James_Gerstner
Reviewers: @Andrew_Wilson, @Jacob_Dutton
Estimated Time to Solve: 75 Minutes
We provide the solution to this challenge using:
- Alteryx
Need a program? Click here.
Overview
It’s your first busy season at TH, a respected regional accounting firm. Up to this point, your job has consisted of a lot of training and constant oversight. Now, your team needs you to step up and help with a large client, Odd Bits, Inc. They are a repeat client, so you have their trial balance at the end of last year (they seem to be aptly named, as their company has accounts related to multiple lines of business). A senior associate on your team has gone through most of the transactions from the current year and updated the trial balance accordingly, but she needs you to finish the last page and make a couple of the adjusting entries (to record depreciation expense and goodwill impairment).
In the past, team members have used Excel for this type of work, but you think Alteryx would be easier to maintain on a rolling basis, as you plan to retain Odd Bits for future years. Using tools like Union, Formula, and Summarize can streamline this process as you add in new transactions to the previous trial balance data.
You will:
- Finish updating the unfinished trial balance with the remainder of the transactions.
- Make the adjusting entries for depreciation expense and goodwill impairment.
- Export your updated trial balance to an Excel file.
Can you show your team that you are up to the task?
Instructions
- Download the initial trial balance file. This has the adjusted trial balance from the end of last year as well as the current, incomplete unadjusted trial balance.
- Download the transactions file that contains the transactions you will be responsible for.
- Using the current unadjusted trial balance and the transactions file, build a workflow in Alteryx to update the final transactions to where they belong.
- Add an adjusting entry for depreciation expense using the following information you saw in a footnote: “Buildings with a total value of $282,000 have a depreciation rate of 4% for the year. Adjust the appropriate accounts accordingly.”
- Add an adjusting entry for goodwill impairment using information from another note: “A reporting unit directly related to $5,700 of goodwill was deemed obsolete in November and has shut down operations since the start of the year.”
Data Files
Suggestions and Hints
- Be careful that you credit and debit the accounts appropriately—some accounts will be used both ways, so be deliberate about how you affect each account balance.
- To record depreciation expense, take the value of the buildings ($282,000) multiplied by the depreciation rate (4%). Using this, the depreciation expense for the year is $11,280.
- To test for goodwill impairment, compare the fair value of the reporting unit to its value on the Odd Bits books. In this case, because fair value and book value are both now zero, all of the associated goodwill ($5,700) must be removed via a goodwill impairment journal entry.
- When you import the data into Alteryx, it will be pretty messy because it’s formatted visually in Excel. Use the data cleaning tool to make the data easier to read.
- In Alteryx, consider creating a column to label accounts per their types (asset, liability, equity, revenue, expense). This can help calculate the final balance by using the account type’s natural balance (debit or credit).
- Adjusting entries can be added either in the Excel transactions file or using a separate input tool in Alteryx.
Solution
Be sure to save the solution .yxmd file in the same folder as your data so the flow will be able to read it in.
Challenge213a_OddBits_TrialBalance_2023_Solution.xlsx
Challenge213b_TBflow_Solution.yxmd
Solution Video: Challenge 213|ALTERYX – Odd Bits Audit