Course Introduction

Traditional budgeting often depends on manual data preparation, repetitive spreadsheet

work, and models that are difficult to update and audit. This hands-on course equips finance

professionals to modernise the corporate budgeting workflow using Excel 365, Power Query,

VBA, and Generative AI. Participants will learn to automate data preparation, build dynamic

budgets and scenarios, use AI to generate and troubleshoot VBA code, and strengthen

spreadsheet controls—enabling them to spend less time on repetitive processing and more

time on analysis, forecasting, and decision support.

Course Outline

Module 1: Structural Foundations & Power Query ETL

Automate budgeting data preparation using Power Query to extract, clean, transform, and

consolidate financial data.

Build dynamic budgets and forecasts using modern Excel functions such as XLOOKUP,

SUMIFS, FILTER, UNIQUE, and PIVOTBY.

Use Generative AI as a coding co-pilot to generate, explain, debug, and improve VBA

macros for budgeting automation.

Perform scenario and decision analysis using scenario selectors, Data Tables, Goal Seek,

Solver, NPV, and IRR.

Strengthen budgeting governance through reconciliation checks, exception alerts,

worksheet protection, and management dashboards.

Structure budgeting models into Raw Data, Assumptions, Calculations, Audit, and

Dashboard layers.

Use Power Query to import, clean, transform, and consolidate financial data from Excel,

CSV, ledger, and other source files.

Apply XLOOKUP and reference tables to automate transaction and expenditure

categorisation.

Module 2: Advanced Calculations & Dynamic Scenario Modelling

Module 3: AI-Assisted VBA Automation & Dashboard Development

Module 4: Governance, Auditing & Capital Budgeting

Key Takeaway

By the end of the course, participants will be able to:

• Automate budgeting data preparation using Power Query to extract, clean, transform, and consolidate financial data.
• Build dynamic budgets and forecasts using modern Excel functions such as XLOOKUP, SUMIFS, FILTER, UNIQUE, and PIVOTBY.
• Use Generative AI as a coding co-pilot to generate, explain, debug, and improve VBA macros for budgeting automation.
• Perform scenario and decision analysis using scenario selectors, Data Tables, Goal Seek, Solver, NPV, and IRR.
• Strengthen budgeting governance through reconciliation checks, exception alerts, worksheet protection, and management dashboards.

Target Audience

Accountants, finance managers, controllers, financial analysts, FP&A professionals, budget

coordinators, and department managers involved in preparing, reviewing, analysing, or

consolidating budgets and forecasts.

Course Prerequisites

Working knowledge of basic accounting, budgeting, P&L, and cash flow concepts.

Intermediate familiarity with Microsoft Excel, including formulas and worksheet

management.

No prior VBA or programming experience is required.

Essential Software Requirements

Microsoft Excel 365 with Power Query and modern Excel functions.

Access to the Developer Tab and Visual Basic Editor (VBE) with permission to

create .xlsm  workbooks.

Access to a Generative AI assistant, such as ChatGPT or Claude, with internet connectivit