Module 1: Financial Data Analytics in FP&A

• The analytical stack: Positioning SQL, Power Query, and Excel in the modern finance workflow
• Understanding key financial data types: General Ledger (GL), Transactional, and Master Data
• Data governance and integrity: Ensuring accuracy and auditability of data extracts
• Defining the scope of self-service analysis versus data warehouse requirements
• Practical session: Mapping the Financial Data Flow: Identifying key data sources and potential bottlenecks in the monthly reporting cycle.

Module 2: SQL Fundamentals: Retrieval and Filtering

• Basic query structure: The mandatory components of a well-formed SELECT statement
• The WHERE clause mastery: Using comparison operators (<, >), range operators (BETWEEN), and pattern matching (LIKE)
• Advanced filtering techniques: Leveraging IN, NOT IN, and combining multiple conditions with AND/OR
• Utilizing the ORDER BY clause for meaningful presentation of financial results
• Practical session: Querying the General Ledger: Extracting all transactions for a specific period, account, and regional code, ordered by transaction date.

Module 3: SQL Aggregation and Grouping

• Core Aggregate Functions: Calculating SUM, AVG, COUNT, MIN, and MAX on financial metrics (e.g., calculating average invoice value)
• The power of the GROUP BY clause: Consolidating transactions into meaningful dimensions (e.g., grouping expenses by cost center)
• Filtering aggregated results: Using the HAVING clause to filter after grouping (e.g., filtering for cost centers with aggregate expenses over $100k)
• Handling NULL values during aggregation and its impact on financial accuracy
• Practical session: Creating an Executive Summary Report: Grouping GL data by department and calculating the total, average, and count of transactions, filtering out minor departments.

Module 4: Joining Financial Datasets

• Introduction to Relational Integrity: Understanding Primary and Foreign Keys in a finance database schema
• Mastering the INNER JOIN: Accurately linking GL transactions to Cost Center/Department Master Data
• The essential LEFT JOIN: Preserving all financial records while matching available master data
• Understanding RIGHT JOIN and when it is strategically preferable to LEFT JOIN
• Practical session: Linking Personnel and Payroll Data: Joining employee master data to monthly payroll transactions to analyze headcount-to-expense ratios.

Module 5: Advanced SQL Set Operations and Subqueries

• Using UNION and UNION ALL to combine result sets from different, yet structurally similar, financial tables (e.g., combining budgeted and actual expense data)
• Subquery Fundamentals: Using a SELECT statement within a WHERE or FROM clause to solve complex filtering problems
• Correlated vs. Non-Correlated Subqueries and their performance implications in large databases
• Using EXISTS and NOT EXISTS to check for the presence or absence of related financial records
• Practical session: Identifying Unbudgeted Spending: Using a subquery to find all expense items that do not have a corresponding entry in the Budget Master table.

Module 6: Common Table Expressions (CTEs) for Multi-Step Logic

• The structure and benefits of Common Table Expressions (WITH clause) for code readability and modularity
• Using multiple CTEs to stage complex calculations (e.g., calculating departmental subtotals before final company-wide totals)
• Recursive CTEs: Introduction to concepts for traversing hierarchical financial structures (e.g., organizational or product hierarchies)
• CTEs as a powerful alternative to nested subqueries for improved performance and debugging
• Practical session: Building a Multi-Step Financial Report: Defining CTEs for (1) calculating total revenue, (2) calculating total COGS, and (3) computing gross margin percentage in the final SELECT statement.

Module 7: Window Functions for Financial Metrics

• Introduction to Window Functions: Calculating metrics across a set of table rows related to the current row, without collapsing the groups
• Calculating Running Totals: Using SUM() OVER() for cumulative revenue or expense tracking over time
• Period-over-Period Analysis: Employing LAG() and LEAD() to retrieve previous or subsequent period values (e.g., calculating month-on-month growth)
• Ranking and Numbering: Utilizing ROW_NUMBER(), RANK(), and DENSE_RANK() for top-N analysis (e.g., top 10 cost drivers)
• Practical session: Calculating Trailing Twelve Month (TTM) Revenue: Using the SUM() OVER() window function to calculate the TTM metric for every month-end date.

Module 8: SQL for Data Cleansing and Transformation

• Using CASE statements for conditional logic and categorizing financial transactions (e.g., classifying expenses as fixed vs. variable)
• String manipulation and standardization: Cleaning up vendor names and product descriptions using functions like TRIM and REPLACE
• Date and Time Conversion: Ensuring consistency between database date formats and reporting requirements using CAST or CONVERT
• Best practices for handling currency and scaling issues at the database level
• Practical session: Categorizing Expenses using CASE: Writing a query that automatically assigns a high-level category (e.g., "Marketing," "G&A," "R&D") based on low-level GL account codes.

Module 9: Direct Database Connection and Excel Integration

• Setting up ODBC/OLeDB connections to various database platforms (e.g., SQL Server, PostgreSQL)
• Connecting Excel to a database using the native Data Tab functionality
• Refreshing data models efficiently: Understanding query execution and performance impact on Excel workbooks
• Managing parameters within Excel connections to create dynamic data requests (e.g., allowing the user to input the reporting period)
• Practical session: Building a Parameterized Data Connection: Creating an Excel connection to a SQL database that queries the last 90 days of data based on a user-inputted start date cell.

Module 10: Mastering Power Query for Data Transformation (ETL)

• Introduction to the Power Query Editor (Get & Transform Data) as a powerful ETL tool
• Key transformation steps: Unpivoting data (from wide to tall), merging tables, and grouping/aggregating data
• Custom Column creation: Writing simple custom formulas directly within the Power Query Editor
• Data Profiling: Identifying quality issues (e.g., nulls, blanks, errors) before loading data into the Excel grid
• Practical session: Transforming a Complex Trial Balance: Using Power Query to unpivot the data from a month-column format into a standard transaction-row format ready for a pivot table.

Module 11: Advanced Power Query M Language and Custom Functions

• The M Language fundamentals: Understanding the syntax and structure behind recorded Power Query steps
• Writing M code manually for complex transformations not available through the GUI
• Creating reusable Custom Functions in M to standardize recurring data cleansing tasks (e.g., a function to clean all text fields)
• Handling errors and dynamic data sources using M language functions (Try...Otherwise, Text.Split)
• Practical session: Developing a Reusable M Function: Creating a function that takes a GL Account and returns its higher-level category, which can be applied across multiple transaction queries.

Module 12: Introduction to Data Modeling with Power Pivot

• What is the Data Model? Understanding the difference between a simple table and a relational data model in Excel
• Using the Power Pivot Window: Importing multiple data tables (Facts and Dimensions)
• Establishing and managing Relationships between tables (e.g., linking a transaction table to a date table)
• Introduction to DAX (Data Analysis Expressions): Writing simple explicit measures (e.g., Total Sales, Total Costs)
• Practical session: Building a Star Schema: Importing GL data, Cost Center Master, and a Date Dimension into Power Pivot and establishing correct relationships.

Module 13: Dynamic Array Formulas for Modern Analysis

• Introduction to modern Excel array behavior: Spilling ranges and dynamic outputs
• Mastering FILTER: Dynamically extracting subsets of financial data based on multiple criteria (e.g., all Q4 expenses for two departments)
• Using SORT and SORTBY: Creating ranked, sorted lists of data without traditional helper columns
• Advanced lookup with XLOOKUP: Handling multiple match criteria and searching from bottom-to-top
• Practical session: Creating a Dynamic Income Statement: Using FILTER and SUM to dynamically pull and sum data based on a user-selected month and department.

Module 14: Advanced Lookup and Conditional Functions

• Complex conditional aggregations: Mastery of SUMIFS, COUNTIFS, and AVERAGEIFS for segmented reporting
• The power of INDEX and MATCH: Implementing a flexible, two-way lookup system for complex data matrix navigation
• Advanced conditional logic: Nesting IF, AND, and OR statements for complex financial rules and flagging
• Using OFFSET and INDIRECT (with caution) for highly flexible, but volatile, range referencing in reporting
• Practical session: Building an Advanced Commission Calculation: Using SUMIFS and nested IF statements to calculate sales commission based on tiered volume targets and product margins.

Module 15: Scenario and Sensitivity Analysis Tools

• Building clear Input, Calculation, and Output sections for robust financial models
• Using Data Tables (One-Way and Two-Way) for structured sensitivity analysis (e.g., assessing the impact of interest rates vs. volume)
• Goal Seek utility: Automatically finding the required input (e.g., sales growth rate) to achieve a target output (e.g., a specific EBITDA)
• Managing multiple scenarios with the Scenario Manager tool
• Practical session: Implementing a Two-Variable Data Table: Testing the impact of changes to both Price and Cost of Goods Sold on the projected Gross Profit.

Module 16: Time Value of Money (TVM) and Valuation Functions

• Understanding the core TVM concepts: Present Value (PV), Future Value (FV), and compounding
• Mastering the NPV and XNPV functions for calculating the value of future cash flows
• The IRR and XIRR functions: Determining the rate of return on an investment with irregular cash flow timing
• Advanced debt analysis: Using PMT, IPMT, and PPMT to build loan amortization schedules
• Practical session: Performing a Capital Budgeting Analysis: Calculating the NPV and IRR for a new equipment purchase, sourcing cash flow data directly from the modeled P&L.

Module 17: Interactive Dashboard Design and Visualization

• The principles of effective financial visualization: Pre-attentive attributes, data-ink ratio, and visual hierarchy
• Creating dynamic charts: Using Named Ranges and the CHART object to link visualizations to user-selected metrics
• Implementing Slicers and Timelines: Creating intuitive, single-click filters for Pivot Tables and Power Pivot models
• Designing a compelling, multi-screen executive summary dashboard layout
• Practical session: Designing an Integrated KPI Dashboard: Creating a 5-KPI dashboard utilizing sparklines, color-coded conditional formatting, and Slicers linked to the underlying data model.

Module 18: Automation, VBA, and Strategic Data Storytelling

• Introduction to VBA for financial users: The Visual Basic Editor and Macro Recorder
• Writing simple VBA code for repetitive tasks (e.g., automatically formatting reports, clearing input cells, or exporting)
• Strategic Storytelling: Framing data insights using the Minto Pyramid Principle
• Translating complex analytical findings (e.g., regression results) into clear, actionable business recommendations
• Practical session: Developing an Executive Summary Presentation: Drafting a presentation structure and creating the first three slides that translate the dashboard's findings into a strategic narrative and next steps.

Requirements:

·       Participants should be reasonably proficient in English.

·       Applicants must live up to Armstrong Global Institute admission criteria.

Terms and Conditions

1. Discounts: Organizations sponsoring Four Participants will have the 5th attend Free

2. What is catered for by the Course Fees: Fees cater for all requirements for the training – Learning materials, Lunches, Teas, Snacks and Certification. All participants will additionally cater for their travel and accommodation expenses, visa application, insurance, and other personal expenses.

3. Certificate Awarded: Participants are awarded Certificates of Participation at the end of the training.

4. The program content shown here is for guidance purposes only. Our continuous course improvement process may lead to changes in topics and course structure.

5. Approval of Course: Our Programs are NITA Approved. Participating organizations can therefore claim reimbursement on fees paid in accordance with NITA Rules.

Booking for Training

Simply send an email to the Training Officer on training@armstrongglobalinstitute.com and we will send you a registration form. We advise you to book early to avoid missing a seat to this training.

Or call us on +254720272325 / +254725012095 / +254724452588

Payment Options

We provide 3 payment options, choose one for your convenience, and kindly make payments at least 5 days before the Training start date to reserve your seat:

1. Groups of 5 People and Above – Cheque Payments to: Armstrong Global Training & Development Center Limited should be paid in advance, 5 days to the training.

2. Invoice: We can send a bill directly to you or your company.

3. Deposit directly into Bank Account (Account details provided upon request)

Cancellation Policy

1. Payment for all courses includes a registration fee, which is non-refundable, and equals 15% of the total sum of the course fee.

2. Participants may cancel attendance 14 days or more prior to the training commencement date.

3. No refunds will be made 14 days or less before the training commencement date. However, participants who are unable to attend may opt to attend a similar training course at a later date or send a substitute participant provided the participation criteria have been met.

Tailor Made Courses

This training course can also be customized for your institution upon request for a minimum of 5 participants. You can have it conducted at our Training Centre or at a convenient location. For further inquiries, please contact us on Tel: +254720272325 / +254725012095 / +254724452588 or Email training@armstrongglobalinstitute.com

Accommodation and Airport Transfer

Accommodation and Airport Transfer is arranged upon request and at extra cost. For reservations contact the Training Officer on Email: training@armstrongglobalinstitute.com or on Tel: +254720272325 / +254725012095 / +254724452588