Details:

Why is this data important, and what questions or problems need to be addressed?

Julias Cookies must monitor general ledger journal entries and transactions to check for unusual entries to the general ledger because incorrect or fraudulent entries could result in misrepresentation of the financial statements.

One area to focus on is determining who approves and posts journal entries, as well as the times and days of the journal entry approvals and postings. Consider the following questions: Who posts the most journal entries? How many entries were posted outside of normal work hours? Does the same employee ever approve and post an entry?

Plan:

What data is needed, and how should it be analyzed?

The data needed to answer these questions is extracted from the ERP database. It is data for the 2021 fiscal year general ledger for part of Q1 and the human resources database. In this data set, the journal entry header information is in one table, which includes all identifying information for each journal entry number, such as who posted the journal entry. The line items for each journal entry are in a second table. Remember, journal entries will always have at least two line items: one debit and one credit.

Now its your turn to evaluate, analyze, and communicate the results!

To answer the questions, connect the tables using their ID fields:

Hint: When connecting the HR_Employee table, connect the first instance on Approved By to Employee Number (primary key) and the second connection on Posted By to Employee Number (primary key).

Questions

1.Which employee posted the most journal entries? How many journal entries did this employee post? (Hint: Make sure you are only counting each journal entry number once.)

2.On which date were the most journal entries posted? How many journal entries were posted that day? (Hint: Make sure you are only counting each journal entry number once.)

3.On which date was the highest total dollar amount posted? How much was posted on this day? (Hint: Because journal entries net to zero, you should only consider the debit [positive] side of the journal entries.)

4.A common journal entry fraud test is to look for journal entries posted outside normal business days. Julias Cookies Accounting department has a normal business week of Monday through Friday. How many journal entries were posted outside normal business days? On which day of the week were they posted? (Hint: Make sure you are only counting each journal entry number once.)

5.For the day of the week you identified in question 4, what was the total amount posted on that day? (Hint: Because journal entries net to zero, you should only consider the debit [positive] side of the journal entries.)

6.A common journal entry fraud test is to look for journal entries posted outside normal business hours. Most of Julias Cookies accounting employees start work no earlier than 5 a.m. and log off before 8 p.m., Monday through Friday. How many transactions were posted on Monday through Friday outside these hours? (Hint: Make sure you are only counting each journal entry number once.)

7.For the journal entries you identified in question 6, what was the total dollar amount posted? (Hint: Because journal entries net to zero, you should only consider the debit [positive] side of the journal entries.)

8.A common journal entry fraud test is to identify journal entries posted or approved by unexpected employees. How many employees who do not work in the Accounting department posted journal entries? For each of these employees, identify their employee ID, name, employee type, and department, as well as the number of journal entries they posted. (Hint: Filter the HR data for an employee status of Active to get each employees most recent department.)

Take it to the next level!

9.Because Julias Cookies uses a double-sided accounting system, all journal entries are expected to net to zero. A common journal entry data validation test is to verify that all journal entries net to zero. How many unbalanced journal entries (that is, entries that do not net to zero) are there? Identify the journal entry number, poster ID, approver ID, and net dollar amount for each of these unbalanced journal entries.

10.A common journal entry fraud test is to identify journal entries that are both posted and approved by the same person, which violates segregation of duties internal controls. Analyze the Posted By and Approved By fields to identify any employees who are both posting and approving journal entries. Identify the employee ID(s), employee name(s), number of journal entries posted, and total dollar amount posted for each of the employees. (Hint: Because journal entries net to zero, you should only consider the debit [positive] side of the journal entries.