Financial Modelling 360: Excel, Power BI & AI for Strategic Finance

Day 1 — Build: Best-Practice Excel Financial Modelling
Model Architecture, Standards and Modern Excel
• The purpose and anatomy of a decision-ready financial model
• Separation of inputs, calculations, outputs and checks
• Consistent timelines, sign conventions, units, colour conventions and documentation
• Modern Excel tools: Tables, XLOOKUP, INDEX/MATCH, SUMIFS, dynamic arrays, LET and LAMBDA
• Assumption management, version discipline and model navigation
Applied workshop: Build the architecture and assumptions module for a driver-based planning model.
Driver-Based Forecasting and Integrated Statements
• Revenue, volume, price, cost, working-capital and capital-expenditure drivers
• Building supporting schedules and linking the income statement, balance sheet and cash flow statement
• Timing logic, debt, interest, tax, depreciation and retained earnings
• Managing circularity safely and understanding iterative calculations
• Balance, cash-flow and roll-forward integrity checks
Applied workshop: Construct and reconcile the core three-statement model.
Scenario, Sensitivity and Risk Analysis
• Base, upside, downside and management-case design
• One- and two-variable Data Tables, Goal Seek and Scenario Manager
• Break-even, covenant and liquidity analysis
• Stress testing and introductory Monte Carlo simulation concepts
• Interpreting probability, range and downside exposure without false precision
Applied workshop: Evaluate forecast resilience under alternative commercial and financing assumptions.
Controlled Excel Automation
• Choosing among formulas, Power Query, Office Scripts and VBA
• Automating repetitive refresh, reporting and output routines
• User controls, error handling, audit trails and safe execution
• Maintaining transparency and avoiding fragile “black-box” automation
Applied workshop: Automate selected model and reporting steps with embedded controls.

Day 2 — Connect: Power BI for Finance Analytics and Reporting
Data Acquisition and Transformation with Power Query
• Excel and Power BI: complementary roles in the finance technology stack
• Connecting to workbooks, folders, CSV files and structured data sources
• Cleaning, typing, merging, appending and unpivoting finance data
• Building reusable transformation logic and data-quality checks
• Refresh strategy, data ownership and lineage
Applied workshop: Create a repeatable actuals-and-budget data preparation pipeline.
Financial Data Modelling
• Star-schema principles for finance
• Fact tables, dimensions, chart of accounts, calendars and organisational hierarchies
• Relationships, granularity, filter direction and common modelling traps
• Mapping management-account structures and supporting multiple reporting views
Applied workshop: Build a scalable semantic model for multi-period performance reporting.
DAX for Finance
• Measures versus calculated columns and evaluation context
• CALCULATE, FILTER, DIVIDE, SUMX and variables
• Time intelligence: YTD, prior period, rolling periods and trailing twelve months
• Actual versus budget, price/volume/mix, margins, cash conversion and financial ratios
• Reusable measure design, formatting and validation
Applied workshop: Create a controlled suite of management-reporting measures.
Executive Dashboards and Financial Storytelling
• Selecting visuals for performance, trend, variance and risk
• Drill-through, tooltips, bookmarks and exception-focused navigation
• Dynamic commentary and clear display of units, definitions and status
• Dashboard accessibility, mobile considerations and executive usability
• Publication, permissions and refresh considerations
Applied workshop: Build an executive finance dashboard and deliver a concise variance story.

Day 3 — Predict: AI-Enhanced Modelling, Forecasting and Automation
Generative AI for Finance Professionals
• Where generative AI adds value across the modelling lifecycle
• Prompt patterns for formula support, model review, scenario design and explanation
• Using ChatGPT, Microsoft Copilot, Gemini, Claude or approved enterprise tools
• Hallucination, bias, data leakage, confidentiality and intellectual-property risk
• A verification workflow: source, test, reconcile, challenge, document and approve
Applied workshop: Use structured prompts to develop, critique and validate modelling logic without exposing sensitive data.
Python in Excel and Predictive Analytics
• When Python complements—not replaces—Excel
• Preparing time-series data and selecting suitable forecasting approaches
• Trend, seasonality, moving averages and introductory regression
• Forecast accuracy and back-testing using MAE, RMSE and MAPE
• Anomaly detection and diagnostic review of unexpected movements
Applied workshop: Produce and compare a traditional Excel forecast with a Python-assisted forecast.
AI-Enhanced Analysis and Narrative Reporting
• AI-assisted variance investigation and root-cause hypotheses
• Generating management commentary grounded in approved model outputs
• Creating scenario questions, risk registers and sensitivity narratives
• Separating facts, calculations, assumptions and recommendations
• Human review and sign-off standards for AI-generated content
Applied workshop: Create verified, audience-specific commentary from a finance dataset.
Workflow Automation and Exception Management
• Mapping manual finance workflows and prioritising automation opportunities
• Power Automate concepts for approvals, alerts and recurring reporting
• Thresholds, exception rules, escalation and audit evidence
• Overview of Power Platform and Azure AI integration pathways
• Operating controls, fallback procedures and ongoing monitoring
Applied workshop: Design an exception-based alert and approval workflow for a reporting cycle.

Day 4 — Assure: Governance, Stress Testing and Executive Decision Support
Model Review, Validation and Audit Readiness
• Typical spreadsheet errors, hidden risks and model failure modes
• Formula consistency, reasonableness tests, control totals and reconciliations
• Testing inputs, outputs, edge cases and extreme assumptions
• Peer review, documentation, change logs and issue resolution
• Model risk classification, ownership, approval and periodic review
Applied workshop: Perform a structured model audit and document findings by severity.
Performance, Scalability and Tool Choice
• Calculation modes, volatile functions, file size and model performance
• Refactoring formulas and using Power Query for larger datasets
• When to use Excel, Power Query, Power BI, DAX, VBA, Office Scripts, Python or AI
• Designing maintainable handoffs and avoiding unnecessary complexity
• Migration triggers from desktop models to governed enterprise solutions
Applied workshop: Refactor a slow or fragile model and justify the chosen technology stack.
Decision-Focused Modelling and Communication
• Connecting assumptions and KPIs to the decision being made
• Designing management outputs, board-ready summaries and action thresholds
• Scenario storytelling: what changed, why it matters and what to do next
• Communicating uncertainty, limitations and confidence responsibly
• Challenging assumptions and responding to stakeholder questions
Applied workshop: Prepare a one-page executive summary that links evidence to action.
Integrated Capstone Challenge
• Integrate source data, model logic, scenarios, dashboard and AI-assisted analysis
• Apply validation, governance and responsible-AI controls
• Present the recommendation and defend key assumptions
• Peer and facilitator feedback against a practical quality rubric
Applied workshop: Deliver a decision-ready finance pack for an investment, budget or performance case.