Details: Why is this data important, and what questions or problems need to be addressed? • Julia’s Cookies must monit
Posted: Wed Jul 06, 2022 6:10 am
Details:
Why is this data important, and what questions or problems needto be addressed?
• Julia’sCookies must monitor general ledger journal entries andtransactions to check for unusual entries to the general ledgerbecause incorrect or fraudulent entries could result inmisrepresentation of the financial statements.
• Onearea to focus on is determining who approves and posts journalentries, as well as the times and days of the journal entryapprovals and postings. Consider the following questions: Who poststhe most journal entries? How many entries were posted outside ofnormal work hours? Does the same employee ever approve and post anentry?
Plan:
What data is needed, and how should it be analyzed?
• Thedata needed to answer these questions is extracted from the ERPdatabase. It is data for the 2021 fiscal year general ledger forpart of Q1 and the human resources database. In this data set, thejournal entry header information is in one table, which includesall identifying information for each journal entry number, such aswho posted the journal entry. The line items for each journal entryare in a second table. Remember, journal entries will always haveat least two line items: one debit and one credit.
Now it’s your turn to evaluate, analyze, and communicate theresults!
To answer the questions, connect the tables using their IDfields:
Hint: When connecting the HR_Employee table, connect the firstinstance on “Approved By” to “Employee Number” (primary key) andthe second connection on “Posted By” to “Employee Number” (primarykey).
Questions
1. Which employeeposted the most journal entries? How many journal entries did thisemployee post? (Hint: Make sure you are only counting each journalentry number once.)
2. On which date werethe most journal entries posted? How many journal entries wereposted that day? (Hint: Make sure you are only counting eachjournal entry number once.)
3. On which date wasthe highest total dollar amount posted? How much was posted on thisday? (Hint: Because journal entries net to zero, you should onlyconsider the debit [positive] side of the journal entries.)
4. A common journalentry fraud test is to look for journal entries posted outsidenormal business days. Julia’s Cookies’ Accounting department has anormal business week of Monday through Friday. How many journalentries were posted outside normal business days? On which day ofthe week were they posted? (Hint: Make sure you are only countingeach journal entry number once.)
5. For the day of theweek you identified in question 4, what was the total amount postedon that day? (Hint: Because journal entries net to zero, you shouldonly consider the debit [positive] side of the journalentries.)
6. A common journalentry fraud test is to look for journal entries posted outsidenormal business hours. Most of Julia’s Cookies’ accountingemployees start work no earlier than 5 a.m. and log off before 8p.m., Monday through Friday. How many transactions were posted onMonday through Friday outside these hours? (Hint: Make sure you areonly counting each journal entry number once.)
7. For the journalentries you identified in question 6, what was the total dollaramount posted? (Hint: Because journal entries net to zero, youshould only consider the debit [positive] side of the journalentries.)
8. A common journalentry fraud test is to identify journal entries posted or approvedby unexpected employees. How many employees who do not work in theAccounting department posted journal entries? For each of theseemployees, identify their employee ID, name, employee type, anddepartment, as well as the number of journal entries they posted.(Hint: Filter the HR data for an employee status of “Active” to geteach employee’s most recent department.)
Take it to the next level!
9. Because Julia’sCookies uses a double-sided accounting system, all journal entriesare expected to net to zero. A common journal entry data validationtest is to verify that all journal entries net to zero. How manyunbalanced journal entries (that is, entries that do not net tozero) are there? Identify the journal entry number, poster ID,approver ID, and net dollar amount for each of these unbalancedjournal entries.
10. A common journalentry fraud test is to identify journal entries that are bothposted and approved by the same person, which violates segregationof duties internal controls. Analyze the “Posted By” and “ApprovedBy” fields to identify any employees who are both posting andapproving journal entries. Identify the employee ID(s), employeename(s), number of journal entries posted, and total dollar amountposted for each of the employees. (Hint: Because journal entriesnet to zero, you should only consider the debit [positive] side ofthe journal entries.
Why is this data important, and what questions or problems needto be addressed?
• Julia’sCookies must monitor general ledger journal entries andtransactions to check for unusual entries to the general ledgerbecause incorrect or fraudulent entries could result inmisrepresentation of the financial statements.
• Onearea to focus on is determining who approves and posts journalentries, as well as the times and days of the journal entryapprovals and postings. Consider the following questions: Who poststhe most journal entries? How many entries were posted outside ofnormal work hours? Does the same employee ever approve and post anentry?
Plan:
What data is needed, and how should it be analyzed?
• Thedata needed to answer these questions is extracted from the ERPdatabase. It is data for the 2021 fiscal year general ledger forpart of Q1 and the human resources database. In this data set, thejournal entry header information is in one table, which includesall identifying information for each journal entry number, such aswho posted the journal entry. The line items for each journal entryare in a second table. Remember, journal entries will always haveat least two line items: one debit and one credit.
Now it’s your turn to evaluate, analyze, and communicate theresults!
To answer the questions, connect the tables using their IDfields:
Hint: When connecting the HR_Employee table, connect the firstinstance on “Approved By” to “Employee Number” (primary key) andthe second connection on “Posted By” to “Employee Number” (primarykey).
Questions
1. Which employeeposted the most journal entries? How many journal entries did thisemployee post? (Hint: Make sure you are only counting each journalentry number once.)
2. On which date werethe most journal entries posted? How many journal entries wereposted that day? (Hint: Make sure you are only counting eachjournal entry number once.)
3. On which date wasthe highest total dollar amount posted? How much was posted on thisday? (Hint: Because journal entries net to zero, you should onlyconsider the debit [positive] side of the journal entries.)
4. A common journalentry fraud test is to look for journal entries posted outsidenormal business days. Julia’s Cookies’ Accounting department has anormal business week of Monday through Friday. How many journalentries were posted outside normal business days? On which day ofthe week were they posted? (Hint: Make sure you are only countingeach journal entry number once.)
5. For the day of theweek you identified in question 4, what was the total amount postedon that day? (Hint: Because journal entries net to zero, you shouldonly consider the debit [positive] side of the journalentries.)
6. A common journalentry fraud test is to look for journal entries posted outsidenormal business hours. Most of Julia’s Cookies’ accountingemployees start work no earlier than 5 a.m. and log off before 8p.m., Monday through Friday. How many transactions were posted onMonday through Friday outside these hours? (Hint: Make sure you areonly counting each journal entry number once.)
7. For the journalentries you identified in question 6, what was the total dollaramount posted? (Hint: Because journal entries net to zero, youshould only consider the debit [positive] side of the journalentries.)
8. A common journalentry fraud test is to identify journal entries posted or approvedby unexpected employees. How many employees who do not work in theAccounting department posted journal entries? For each of theseemployees, identify their employee ID, name, employee type, anddepartment, as well as the number of journal entries they posted.(Hint: Filter the HR data for an employee status of “Active” to geteach employee’s most recent department.)
Take it to the next level!
9. Because Julia’sCookies uses a double-sided accounting system, all journal entriesare expected to net to zero. A common journal entry data validationtest is to verify that all journal entries net to zero. How manyunbalanced journal entries (that is, entries that do not net tozero) are there? Identify the journal entry number, poster ID,approver ID, and net dollar amount for each of these unbalancedjournal entries.
10. A common journalentry fraud test is to identify journal entries that are bothposted and approved by the same person, which violates segregationof duties internal controls. Analyze the “Posted By” and “ApprovedBy” fields to identify any employees who are both posting andapproving journal entries. Identify the employee ID(s), employeename(s), number of journal entries posted, and total dollar amountposted for each of the employees. (Hint: Because journal entriesnet to zero, you should only consider the debit [positive] side ofthe journal entries.