Vendor_​Payment_​analysis

Phase 2: Data Normalization (Nodes 3-6) Before testing, the data required standardization to ensure accuracy:

• Currency Cleaning (String Manipulation): The raw "Amount" field contained text characters ($ and ,). This node stripped these characters to isolate the numerical values.

• Type Conversion (String to Number): Converted the text-based amounts into mathematical Double values for calculation.

• Date Standardization (String to Date&Time & Row Filter): Parsed the "Check Date" into a standard MM/DD/YYYY format and removed records with incomplete date information (e.g., rows containing only "2021") to prevent false positives.

Phase 1: Extraction & Population Control (Nodes 1-2)

• Data Ingestion (CSV Reader): The workflow connects directly to the raw "City of Chicago Vendor Payments" ledger.

• Population Cleaning (Row Filter): We immediately filter out roughly 80,000 blank/null rows to establish a clean, complete "Audit Population" of ~20,000 valid transactions.

Phase 3: Substantive Testing (Nodes 7-8)

The "Duplicate Payment" Test
Objective: Verify the Occurrence assertion.

• Duplicate Detection Logic (GroupBy): This is the core audit engine. The workflow groups transactions that share three identical characteristics: Same Vendor, Same Amount, and Same Date. It then counts the occurrences of these groups.

• Exception Reporting (Row Filter): The workflow filters the results to retain only groups where the Count > 1. This isolates transactions that were paid multiple times.

• Add column (String manipulation): Added a new column named Risk_Type which wil highlight the duplicate entries that might be potential risk.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  The "Round Number" Test:
  Objective: Address fraud risks related to Management Override and Estimation.
  Fraud Anomaly Detection.
  

• Logic: We applied a Math Formula node using the Modulus function ($AMOUNT$ % 100) to isolate payments that are perfect multiples of 100 (e.g., $5,000.00).

• Why: In genuine procurement activity, invoiced amounts typically include taxes and cents (e.g., $4,892.14). A high volume of "perfectly round" numbers can indicate fabricated invoices, kickbacks, or "parked" funds.

Project: Vendor Payment Integrity Monitor
Auditor: Muhammad Afaq Saeed
Tool: KNIME Analytics Platform

Executive Summary: This automated workflow ingests raw vendor ledger data to perform 100% population testing (replacing manual sampling). It validates data integrity and flags high-risk transactions for manual review.

Scope of Procedures:

1. Data Normalization: Cleaning currency strings and standardizing date formats.

2. Duplicate Analysis: Flagging potential double payments (Cash Leakage Risk).

3. Benford/Round Number Analysis: Detecting estimates and potential kickbacks (Fraud Risk).

Test 1: Duplicate Payment Detector

Objective: Identify multiple payments made to the same vendor for the same amount on the same date.

Audit Assertion (Occurrence): Verifies that the reported transactions actually occurred and are not accidental repetitions.

Risk Flag:

• System Errors: ERP glitches processing batches twice.

• Human Error: Clerk entering an invoice twice.

• Fraud: Vendors intentionally double-invoicing.

Test 1: Duplicate Payment DetectorTest 2: Round Number & Estimate Detector

Objective: Identify transactions with perfectly round amounts (e.g., $5,000.00).

Audit Logic (ISA 240): Legitimate vendor invoices usually include taxes or unit costs, resulting in irregular amounts.

Risk Flag:

• Estimates/Accruals: Amounts that are not supported by final invoices.

• Kickbacks/Bribes: Fraudsters often invent round figures to keep math simple.

• Split Invoices: Amounts kept just below approval limits.