Audit with KNIME Analytics Platform: Basics

Perform Journal Testing & Reporting

Learning objective: This exercise walks through a complete journal entry testing workflow for audit purposes. You will import journal entries, reconcile them against the trial balance, apply materiality thresholds, and perform analytical procedures to identify unusual or high-risk transactions. The workflow demonstrates how auditors can use data analytics to test 100% of journal entries rather than sampling, improving both coverage and efficiency.

You will find the instructions to the exercises in the yellow annotations. In this workflow, we are performing journal entry testing for audit purposes. Two parallel data streams prepare the trial balance and journal entries separately - both are cleansed with missing values replaced by zeros to ensure calculations work correctly. Journal entries are aggregated by account to calculate net movements, then reconciled against trial balance net balances using an inner join. The absolute difference between the two sources confirms data integrity before proceeding. A configurable materiality threshold is set, and six audit tests flag potential risks: self-approved entries, missing descriptions, round amounts, backdated journals, out-of-hours postings, and transactions exceeding half the materiality value. Flagged entries are filtered, sorted by value, and exported to a timestamped Excel file for follow-up. Finally, the workflow visualises total journal activity by account to highlight where transaction volumes are concentrated.

Activity 5: Set Materiality

1. Configure the Integer Widget node to set the materiality threshold for journal testing. This creates a flow variable called Materiality that can be adjusted at runtime. Journal lines with values exceeding 50% of this threshold will be flagged as large transactions. Adjust the range to suit your data (e.g., 0-1,000,000 for realistic monetary values).

Activity 6: Apply Journal Entry Tests

1. Use the Expression node to apply six audit tests to each journal line, plus create summary flags:

   1. Test1_SelfApproved – Posted_By equals Approved_By (segregation of duties risk)

   2. Test2_NoDescription – Missing or empty Description field

   3. Test3_RoundAmounts – Debit or Credit divisible by 1,000 (round number risk)

   4. Test4_BackdatedJournals – Posting_Date is later than Effective_Date

   5. Test5_OutOfHours – Posted before 8am or after 6pm

   6. Test6_LargeLines – Debit or Credit exceeds 50% of materiality threshold

   7. Test7_Flag returns TRUE if any test is triggered.

   8. Test8_FlagCount counts the total number of tests triggered per line.

Activity 7: Filter and Prepare Flagged Journals

1. Use the Row Filter node to retain only journal entries where Test7_Flag is TRUE - these are entries that triggered at least one audit test.

2. Use the Sorter node to sort the flagged entries by AbsValue in descending order, placing the largest value transactions at the top for review.

3. Use the Column Filter node to remove unnecessary columns and retain only the fields relevant for the final output (Journal_ID, Line_Number, Posting_Date, Posting_Time, Effective_Date, Period, Journal_Type, Description, Source, and test results).

Activity 8: Generate Timestamped Output File

1. Use the Date&Time Range Creator node to capture the current execution date and time.

2. Use the Date&Time to String node to convert the timestamp to a string in the format yyyy-MM-dd_HH-mm-ss.

3. Use the Expression node to build a dynamic file path by combining the KNIME workspace location with "JournalTest_" and the timestamp, creating a unique filename for each run.

4. Use the Table Row to Variable node to convert the Path column into a flow variable.

5. Use the String to Path (Variable) node to convert the path string variable into a valid file path for the local file system.

6. Use the Excel Writer node to export the flagged journal entries to an XLSX file with a "Results" sheet. The file path is controlled by the flow variable, ensuring each execution creates a uniquely named output file.

Activity 9: Visualise Journal Activity by Account

1. Use the GroupBy node to aggregate journal entries by Account, summing the AbsValue column to calculate total transaction value per account.

2. Use the Top k Row Filter node to select the top 10 accounts by Sum(AbsValue) in descending order - these represent the accounts with the highest transaction volumes.

3. Use the Math Formula node to convert values to millions by dividing Sum(AbsValue) by 1,000,000, creating a Value column for clearer chart presentation.

4. Use the Bar Chart node to visualise the top 10 accounts by total transaction value (£M), providing a quick overview of where journal activity is concentrated across the full population.

5. Use the Statistics node as a sanity check to review summary statistics across the journal population.

Activity 2: Import and Prepare Journal Data

1. Read the journal entries from the Journals Synthetic Dataset CSV file using the CSV Reader node. The file is located in the data folder.

2. Use the Missing Value node to handle any gaps in the data. Integer and Float columns are replaced with 0 to ensure calculations work correctly.

3. Use the Math Formula node to calculate the absolute value of each journal line by adding Debit_Amount and Credit_Amount together (since one is always zero, this gives the transaction value). Append this as a new column called AbsValue.

Activity 3: Aggregate Journals by Account

1. Use the GroupBy node to aggregate journal entries by Account. Sum the Debit_Amount and Credit_Amount columns to get totals per account.

2. Use the Math Formula node to calculate the Net Amount for each account by subtracting Sum(Credit_Amount) from Sum(Debit_Amount). This gives the net movement per account that will be reconciled against the trial balance net balances.

Activity I: Import and Prepare Trial Balance

1. Read the trial balance from the TB Synthetic Dataset CSV file using the CSV Reader node. The file is located in the data folder.

2. Use the Missing Value node to handle any gaps in the data. Integer columns are replaced with 0, and Float columns are replaced with 0.0 to ensure calculations work correctly.

3. Use the Math Formula node to calculate the Net Balance for each account by subtracting Credit_Balance from Debit_Balance.

Activity 4: Reconcile Journals to Trial Balance

1. Use the Joiner node to match the aggregated journal totals to the trial balance. Join on Account (from journals) to Account_Name (from trial balance). Include only matching rows.

2. Use the Math Formula node to calculate the Reconciliation difference by taking the absolute value of Net Amount minus Net Balance. A value of zero confirms the journal movements tie to the trial balance for that account; non-zero values indicate a discrepancy requiring investigation.