Overview
As a first-year associate who recently graduated from school, you have joined a small local accounting firm in the area. Your first assignment is to audit the Financial Statement Controls of a local chain of car repair shops. The senior member of your engagement team informed you that they had strengthened the controls around the income statement in the previous year. However, they remain concerned about some of the controls around certain balance sheet accounts that do not meet the firm’s standards.
The other senior member who interviewed you had informed them that you have expertise in using Alteryx. Therefore, they want you to create a workflow that can identify the accounts that require attention and can be used in future years to help facilitate the process of planning the audit. You have been provided with the beginning balances for the most recent period, the applicable journal entries, and the ending balances.
Instructions
The task is to create a workflow using Alteryx that performs the following actions:
Reads in the General Journal Data and summarizes the activity for each account during the specified period(summarize per account number, each account should show the changes for the corresponding account)
Computes a “Calculated Ending Balance” for each balance sheet account, representing the expected balance at the end of the period.
Compares the “Calculated Ending Balance” with the “Provided Ending Balance” and identifies any accounts with discrepancies to facilitate the audit process.
Identify each account type(Asset, Liablity, Revenue, etc.) and ensure that on a wide scale that the accounting equation holds with both the “Calculated Ending Balance” and the “Provided Ending Balance” (Assets = Liabilities + Owner’s Equity)
Here are some additional notes to help you create the workflow:
Since the data from the Income Statement is accurate, all income statement account balances can be closed out to Owner’s Equity.
The client’s system assumes that all balances are Debit Balances, so any negative balance indicates a Credit Balance.
You can assume that the journal entries have no timing concerns, so any journal entry provided should be included in the final “Calculated Ending Balance”.
The chart of accounts for the client can be summarized as follows:
Accounts with numbers less than 3000 are Asset Accounts.
Accounts with numbers between 3000 and 4000 are Liability or Owner’s Equity Accounts.
Accounts with numbers between 4000 and 6000 are Revenue Accounts.
Accounts with numbers greater than or equal to 6000 are Expense Accounts
Neat challenge! I loved seeing how the debits and credits we learn in school can be integrated with Excel. Something that I think would be helpful is including a list of what the abbreviations stand for in the “LDG” column. I’m assuming A/P stands for Accounts Payable, but I’d be curious to see what the values are for “PARTS” and “HYDRAULIC PUMPS” as a way to double check my solution. Here’s what I put together.
Great challenge! I ran into a hiccup at the end but found out that I made a mistake with data types. I was able to get to the solution without using all the append tools and union tools that you used. Here is my solution:
Great Challenge! Mine is super similar to everyone here! I thought the video was interesting because it was so much larger, but I think ours does essentially the same thing. I would have looked at the revenues, expenses, and liabilities as well, but there were no comparable data for it for the beginning or end of the period. Here’s what I got:
I had a lot of fun working on this challenge! My Alteryx installation was going a bit crazy so I decided to see if I could find the solution using Excel. It turned out that Excel has the perfect tools to solve this challenge! I used a combination of a pivot table, xlookups, and some basic math equations to single out the accounts that didn’t match up. I think it’s super awesome that we have so many tools at our fingertips that make solving complex problems like this a cinch! Here are some pictures from my solution.
My Solution in Excel
I had to create the pivot table and use the account numbers as the rows. I then had to copy the table’s values out so I could add a new sum row finding the net difference
After that I started using xlookups to create a new table that had the beginning balances, the expected end balances, and what we calculated with the addition of the data from the general journal. I also had to manually close out all of the income statement account balances to equity with a sum. Once that was done I filtered out the rows with no difference and found these two left over!
This was a nice challenge and enjoyable. I actually rain into the same problem as Dylan at the end even though I know about the data type issue. Here’s mine:
This was a great challenge. I wonder if the Parts and Hydraulic Pumps accounts got switched during some transaction. For some reason, my filter on the difference didn’t work without me rounding the calculated and given ending balances. An issue I ran into was most of my tools were adding a null account row at the top of the data. By the end I had like 6 haha! I know I can use the data cleansing tool to remove them, but I wonder if anyone has a suggestion on how to prevent it.
Super cool challenge. I used T-SQL in SQL Server to complete this challenge instead of Alteryx. I created a stored procedure to output a table with the columns:
Acct
Beginning Debit Ballance
Change
Calculated Ending Debit Balance
GL Ending Debit Balance
Variance
My code is:
CREATE PROC dbo.calcVariance
AS
SELECT
bb.Acct
, InitialDebitBalances AS BegDebitBal
, CAST(ISNULL(Change,0)AS DECIMAL(10,2)) AS Change
, CAST(InitialDebitBalances+ISNULL(Change,0) AS DECIMAL(10,2)) AS CalcEndDebitBal
, EndingDebitBalances
, CAST(InitialDebitBalances+ISNULL(Change,0)-EndingDebitBalances AS DECIMAL(10,2)) AS Variance
FROM begBal bb
LEFT JOIN
( SELECT
Acct
, ISNULL(SUM(Debit),0)-ISNULL(SUM(Credit),0) AS Change
FROM genJournal
GROUP BY Acct ) t1
ON bb.Acct = t1.Acct
JOIN endBal eb ON bb.Acct = eb.Acct
;
This was a good little challenge! Here is the query that I came up with;SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances) >=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >= 1000
ORDER BY COUNT(g.ID) DESC;
This problem was pretty hard for me and I ended up following the video. Looking at other solutions, it looks like there were simpler ways to do this but I needed the extra help to get there
Here is my solution. This was a good problem, I also fell into the trap of using the wrong data type, but once I found it, I got the right answer. Thanks for the practice! Challenge 52 Workout.yxmd (22.2 KB)
Challenge52_September_Journal_Data.yxmd (16.7 KB)
This was pretty tricky for me so I ended up referencing the video, which added a bunch of steps to articulate very clearly what each account was and what everything was doing. For this problem it’s a bit overkill, but if you wanted to do more calculations in the future it would be useful.
Well… I will say it took me longer than 10 minutes to get this, haha. My more prominent stumbling block was remembering to clean up the input data before using it.
