Event Overview

Financial reporting often becomes inefficient when data is fragmented across spreadsheets, formulas are difficult to maintain, and reports require heavy manual updating. This practical course helps finance and accounting professionals modernise the reporting cycle by applying a structured three-block approach: the Data Empire for raw data input, the Logic Block for processing and modelling, and the Presentation Block for dashboard reporting.

Participants will learn how to strengthen Excel-based reporting models using modern Microsoft 365 functions, Power Query, PivotTables, dashboards, and AI-assisted VBA automation. The course also introduces Generative AI as a practical co-pilot for formula development, macro generation, variance analysis, and management commentary. By the end of the course, participants will be able to build more reliable, automated, and insight-driven financial dashboards that not only present results, but also explain performance drivers clearly.

Event Programme

Module 1: Structured Financial Reporting Architecture

• Understanding the three-block reporting model: Data Empire, Logic Block, and Presentation Block
• Separating raw data, calculation logic, and dashboard outputs to reduce spreadsheet risk
• Designing scalable reporting models that support repeatable monthly reporting
• Establishing good spreadsheet structure, naming conventions, and control checks

Module 2: Automated Data Intake with Power Query

• Importing raw financial data from Excel, CSV, and ERP-style exports
• Cleaning and transforming messy data using Power Query
• Standardising account codes, periods, departments, and cost centres
• Building a refreshable Data Empire for recurring financial reporting

Module 3: Modern Excel Functions for Financial Reporting

• Replacing VLOOKUP and complex INDEX-MATCH formulas with XLOOKUP
• Using FILTER, SORT, and UNIQUE to create dynamic analysis views
• Applying LET to simplify complex financial formulas
• Building a dynamic Logic Block for trial balance, variance, and trend analysis

Module 4: Dashboard Design and Interactivity

• Selecting appropriate visuals for financial performance, trends, and variances
• Creating PivotTables and PivotCharts for management reporting
• Using Slicers and Report Connections to control multiple dashboard views
• Applying Form Controls and dynamic ranges for interactive reporting navigation

Module 5: Generative AI as an Excel Formula Co-Pilot

• Translating business questions into Excel formulas using AI prompts
• Using AI to troubleshoot formula errors such as #REF!, #VALUE!, and incorrect logic
• Developing formulas for rolling averages, scenario analysis, variance calculations, and rankings
• Refining AI-generated formulas for clarity, auditability, and business relevance

Module 6: VBA Automation with AI Assistance

• Understanding basic VBA concepts: macros, modules, procedures, and buttons
• Prompting AI to generate VBA code for common finance reporting tasks
• Automating repetitive work such as formatting, worksheet looping, PDF generation, and dashboard refresh
• Debugging and improving AI-generated VBA code safely and systematically

Module 7: AI-Powered Management Commentary and Variance Analysis

• Converting financial results into management narratives and executive summaries
• Using structured prompts to generate variance explanations from financial data
• Identifying key performance drivers, outliers, and trends with AI assistance
• Embedding dynamic commentary into dashboards for interactive financial storytelling

Module 8: Capstone Financial Dashboard Project

• Cleaning and preparing a raw financial dataset using Power Query
• Building a Logic Block with modern Excel formulas and dynamic calculations
• Creating an executive dashboard with charts, slicers, controls, and refresh buttons
• Generating AI-assisted commentary to explain financial variances and business implications

Key Takeaways

By the end of this workshop, participants will be able to:

• Design reliable Excel reporting models using the Data Empire, Logic Block, and Presentation Block framework
• Apply modern Excel functions such as XLOOKUP, FILTER, SORT, UNIQUE, and LET to improve reporting accuracy and flexibility
• Use Power Query to clean, transform, and structure raw financial data for analysis
• Prompt Generative AI tools to create, debug, and refine Excel formulas and VBA macros
• Build interactive dashboards using PivotTables, PivotCharts, Slicers, Form Controls, and dynamic ranges
• Generate AI-assisted variance commentary, executive summaries, and scenario explanations for management reporting
• Apply practical governance considerations when using AI for financial analysis and reporting
• Leave with a complete capstone dashboard — cleaned data, dynamic calculations, interactive visuals, and AI-assisted commentary — ready to adapt to your own reporting

Who should attend

This workshop is suitable for finance professionals, accounting professionals, FP&A analysts, controllers, finance managers, internal auditors, and data-driven business analysts who want to modernise financial reporting, automate repetitive Excel tasks, and develop AI-assisted dashboards for management decision-making.

Prerequisites

• Intermediate Excel knowledge, including familiarity with basic formulas, PivotTables, and worksheet navigation
• Prior VBA or programming knowledge is not required, as the course uses Generative AI to support macro development and automation
• Participants should have access to Microsoft 365 Excel or Excel 365, as modern dynamic array functions are central to the course
• Access to a Generative AI tool such as ChatGPT, Microsoft Copilot, or Claude is also recommended for formula generation, VBA assistance, data analysis, and management commentary development