AC442 Unit 5 Journal Entry Query Problem Example

Six Journal Entry Tests on 52,630 Ledger Lines: A Unit 5 Query Writing Problem With Counts and Dollars

[Student Name]

Purdue University Global

AC442: Data Management and Analysis

Unit 5 Query Writing Problem

[Instructor Name]

[Date]

Cobble Hill Estate Winery, its ledger and every result are composite; the guide, standard and article cited are real; queries are written in PostgreSQL with equivalents named.

The Table

gl_line: line_id, je_number, post_date, period_end, account_no, amount, source_code (AUTO for system feeds, MAN for manual), entered_by, line_desc. Fiscal year 2025, 52,630 rows.

Problem 1: Manual Entries

Question: how many lines were keyed by hand?

SELECT COUNT(*), SUM(ABS(amount)) FROM gl_line WHERE source_code = 'MAN';

Result: 1,214 lines, $3,962,480 in absolute value. Manual lines are only 2.3 percent of the ledger but carry the judgment, which is why the analytics guide points auditors to them first (American Institute of Certified Public Accountants, 2017). The fraud standard goes further and requires auditors to examine whether ledger entries were proper, looking hardest at those made outside the ordinary flow of business (American Institute of Certified Public Accountants, 2023).

Problem 2: Manual Entries on Weekends

SELECT je_number, post_date, entered_by, amount FROM gl_line WHERE source_code = 'MAN' AND EXTRACT(DOW FROM post_date) IN (0, 6);

Result: 38 lines, $186,420. EXTRACT(DOW ...) returns 0 for Sunday and 6 for Saturday in PostgreSQL; SQL Server uses DATEPART(weekday, post_date), whose numbering depends on a server setting. Thirty-one of the lines fall on the two Saturdays after quarter ends, consistent with the controller finishing the close.

Problem 3: Round Amounts

SELECT je_number, account_no, amount FROM gl_line WHERE source_code = 'MAN' AND ABS(amount) >= 5000 AND MOD(ABS(amount), 1000) = 0;

Result: 22 lines, $214,000. A round figure can be a legitimate estimate, such as an accrual for an audit fee, but invented entries also tend to be round.

Problem 4: Blank Descriptions, Twice

First version: WHERE line_desc = '' returns 44 lines. Second version: WHERE line_desc IS NULL OR TRIM(line_desc) = '' returns 61 lines.

The first query missed 17 lines because a null is not equal to anything, even an empty string, and descriptions holding only spaces fail an equality test too, so a blank-field test that ignores both understates the problem it is looking for.

Total of the 61 lines: $97,315.

Problem 5: Late Postings

SELECT je_number, post_date, period_end, amount FROM gl_line WHERE post_date > period_end + INTERVAL '10 days';

Result: 96 lines, $412,760. Most are year-end adjustments posted in January. Late postings matter because they can move results between periods after managers have seen them. The ten-day window is a judgment; the close calendar allows eight working days, and a reviewer could tighten it.

Problem 6: Who Posted to Revenue

SELECT entered_by, COUNT(*), SUM(amount) FROM gl_line WHERE account_no BETWEEN 4000 AND 4999 AND source_code = 'MAN' GROUP BY entered_by;

Result: three users, 41 lines. The bookkeeper posted 33, the controller 6, and the tasting room manager 2, worth negative $612.40. The manager has no reason to post to revenue directly, and her access should be reviewed.

What the Counts Establish

Every result is paired with a dollar total because accounting judgments turn on amounts. None of the flagged lines is evidence of an error by itself; each is a list for follow-up. Research on journal entry data has found that combinations of such attributes, rather than any single test, are most useful in focusing review (Debreceny & Gray, 2010).

References

American Institute of Certified Public Accountants. (2017). Guide to audit data analytics. AICPA.

American Institute of Certified Public Accountants. (2023). AU-C section 240: Consideration of fraud in a financial statement audit. In AICPA professional standards. AICPA.

Debreceny, R. S., & Gray, G. L. (2010). Data mining journal entries for fraud detection: An exploratory study. International Journal of Accounting Information Systems, 11(3), 157-181. https://doi.org/10.1016/j.accinf.2010.08.001