Overview
This challenge works in connection with Challenge 52|ALTERYX – Balance Sheet Boost. 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 the 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.
You decide to use SQL to identify balance sheet accounts that require the most attention during the audit. After meeting with others on the engagement team, you determine that accounts that have 1,000 transactions or more during the month and have an ending debit or credit balance of 50,000 or more will require the most attention. You notify the other members of the audit engagement that you will find which accounts meet these criteria.
Instructions
First, import all the sheets of data into access or whichever program you choose to practice SQL.
Second, verify that the relationships between the various excel sheets are correct. If there aren’t any relationships identified, create the relationships yourself.
Third, write the SQL code to identify which accounts meet the aforementioned criteria. Remember, accounts must have 1,000 transactions or more and have an ending debit or credit balance of 50,000 or more. Sort by the number of transactions in descending order and be sure to sort out the data that has a null account number. After running your SQL code, the table you return should have the following columns: Acct, GLAcctDesc, EndingDebitBalances, NumOfTransactions.
When creating the relationships between the various tables, be sure to connect them on the account number
When trying to find the accounts that follow the criteria of having an ending debit or credit balance of $50,000 or more, remember that we are given the ending debit balance. I wrote an absolute value in my code to find the accounts with an ending credit balance of $50,000 or more because they will be written as negative numbers in our data.
This was a good quick SQL review! Here’s the solution I came up with:
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE (((Abs([e].[EndingDebitBalances]))>=50000) AND ((e.Acct) Is Not Null))
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING (((Count(g.ID))>=1000));
select e.Acct, e.GLAcctDesc, e.EndingDebitBalances, count(g.ID) as NumOfTransactions from EndingBalances e left join GeneralJournal g on g.Acct = e.Acct where ABS(EndingBalances.EndingDebitBalances) >= 50000 group by g.Acct having NumOfTransactions >= 1000 order by NumOfTransactions desc;
Thanks for the challenge and SQL refresher! Here is the Solution that I came up with for this challenge:
a. SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e INNER JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE Abs(e.EndingDebitBalances)>=50000 AND e.Acct IS NOT NULL
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >= 1000
ORDER BY COUNT(g.ID) DESC;
SELECT e.Acct, e.GLAcctDesc, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE Abs(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, Count(g.ID) AS NumOfTransactions
FROM EndingBalances AS e INNER JOIN GeneralJournal1 AS g ON e.Acct = g.Acct
WHERE (((e.EndingDebitBalances) Not Between -50000 And 50000))
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING (((Count(g.ID))>1000))
ORDER BY Count(g.ID) DESC;
SELECT e.Acct, e.GLAcctDesc, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE Abs(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
This challenge was great. I learned how to use the SQL Server import & export wizard to import the excel file to my local sql server instance.
Here is my solution code:
SELECT
eb.Acct
, GLAcctDesc
, EndingDebitBalances
, a_count AS NumOfTransactions
FROM
dbo.EndingBalances$ eb
LEFT JOIN (
SELECT Acct, COUNT(Acct) AS a_count
FROM GeneralJournal$
GROUP BY Acct ) gj
ON eb.Acct = gj.Acct
WHERE
ABS(EndingDebitBalances) > 50000
AND a_count > 1000
ORDER BY a_count DESC
Really enjoyed seeing an example that is relevant to reality!
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;
SELECT e.Acct, e.GLAccDesc, e.EndingDebitBalances, COUNT (g.ID) AS NumberOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE Abs(e.EndingDebitBalances) >= 50000
GROUP BY g.Acct
HAVING NumberOfTransactions >= 1000
ORDER BY NumberOfTransactions DESC
Access was really picky so it took me longer than I’d like to admit. Great practice!
SELECT e Acct, e GLAcctDesc, e EndingDebitBalances, COUNT(g ID) AS NumOfTransactions FROM EndingBalances AS e LEFT JOIN Generallournal AS g ONe Acct=g Acct WHERE e AcctIS NOT NULL AND ABS(e EndingDebitBalances) > = 50000 GROUP BY e Acct, e GLAcctDese, e EndingDebitBalances HAVING COUNT(g.ID) » = 1000 ORDER BY COUNTIg.ID) DESC
Here is my solution. I won’t lie I couldn’t quite get the count to work on my own so I followed along after the first few lines. Great project though. Thanks for giving us a good starting challenge!
This challenge helped me better understand the importance of the absolute value function as an accountant when analyzing ending credit and debit balances.
Here is the query I generated for this challenge:
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;
I found that the Machinery Inventory ending debit balance was the highest at 46,762,453.09.
This means that Machinery Inventory is an account that the audit engagement team should focus on. Great challenge!
This was my solution! This was an awesome exercise and helped me better understand Access and practice SQL.
SELECT g.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM GeneralJournal1 as g RIGHT JOIN EndingBalances as e ON g.Acct=e.Acct
WHERE g.Acct IS NOT NULL AND ABS(e.EndingDebitBalances) >= 50000
GROUP BY g.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;
