Applied Excel for AML & Financial Intelligence

Apply the Excel if function to filter transactions over 10,000, nest it with and for cash transactions, and visualize results with conditional formatting for AML/CFT monitoring.

Combine if function with Countif in Excel to flag customers with more than five transactions as high risk, apply conditional formatting, and summarize results with a pivot table for AML/CFT.

Combine if and or functions in Excel to filter transactions by criteria, including country screening, cash transactions, or high risk, and highlight attention needed results with conditional formatting and pivots.

Learn to cross-reference transactions with a high risk country list using match, isnumber, and if to flag potential aml/cft red flags, enhanced by conditional formatting.

Learn to use the max ifs and if functions to identify transactions that exceed thresholds, flag red flags, and apply a reusable results template with conditional formatting for AML/CFT data.

Learn to combine the max IFS function with the if function to identify significant transactions against thresholds, and apply a simple template with conditional formatting to flag red flags.

Explore how to use PowerPivot in Excel to build data models, relate tables, and perform calculations, using a simple exercise with transaction, customer, and country risk tables.

Open a new workbook and access the Power Pivot window to import data from other sources. Import transaction, customer, and country tables from an Excel file, using first-row headers.

Create a PowerPivot data model by linking the transaction table to the customer and country risk tables using customer ID and country, establishing relationships and a pivot table for analysis.

Create a pivot table from the Power Pivot data model on an existing worksheet, joining three tables to analyze customer IDs, transaction amounts, and AML and country risk.

Apply the Power Pivot related function to bring customer risk level, country risk level, and country region into the transaction table, enabling deeper analysis and pivot table insights.

Master the fundamentals of Power Query in Excel and Power BI to clean and transform data, automate repetitive tasks, and prepare monthly SARS reports for analysis.

Learn to perform basic data transformations in Power Query by removing the month column, splitting SA ID from customer ID, and refreshing loaded tables for analysis in Excel.

Add a transformed Power Query table to the data model and import a second Excel table, then create one-to-many relationships using customer ID and compliance officer ID for pivot-ready analysis.

Leverage Excel's Analyze Data AI to quickly generate insights from AML transaction data, including suspicious and cleared transactions. Auto-create pivot tables and charts, and tailor analyses with prompts.

Automate AML reporting in Excel by building a macro-enabled template with ChatGPT, auto-defining period from transaction dates, and highlighting top customers, suspicious amounts, transaction types, and country totals.

Build an Excel worksheet with headers and two dummy rows, craft a ChatGPT prompt to generate VBA code that outputs a summary of suspicious AML/CFT transactions for a period.