Get Started

Financial Modelling with Excel and Power BI
Incorporating elements of Artificial Intelligence

Introduction

This course is highly interactive from the very beginning to the very end. At every stage delegates will be in no doubt as to the reason for everything we are doing as well as appreciating the ways in which they can apply their new knowledge and skills when they return to work.

The course begins by ensuring all delegates are aware of and can apply the most important and fundamental concepts and techniques using both Microsoft Excel and Microsoft Power BI for the desktop.

Delegates will appreciate this course most of all if they come to it not only with Excel 2021 or Office 365 fully installed but also with the mindset that is ready to contribute to all discussions and being ready to illustrate their discussions with examples and suggestions of their own.

Before the course begins, delegates will be provided with detailed instructions on setting up Excel and downloading and installing power BI and anything else that is appropriate at the time.

The sub title of the course reflects the inclusion of Artificial Intelligence in the curriculum. AI will be included at various stages throughout the course by way of examples and suggestions.

Who Should Attend?

This course has been designed for a wide range of participants

  • Financial Accountants
  • Management Accountants
  • Professors and other Teachers
  • Auditors
  • Sales and Marketing Staff
  • Production Managers
  • Statisticians

Outline in Summary

  • Reviewing Excel and Pivot Tables
  • Critical Excel functions
  • Business Intelligence begins with Power BI for the Desktop
  • More Advanced Business Intelligence
  • Interactive Controls
  • Analysing The Data
  • Automating Your Dashboard
  • Putting It All Together

MODULE 1: Reviewing Excel and Pivot Tables

*** This module will be covered by way of Pre Course Materials that will be shared with delegates one week in advance of the beginning of the course ***

High quality dashboards rely on a number of ideas and skills being used. In this first four modules, we will review the following key skills that everyone needs to understand and apply in order to create and use effective dashboards.

Excel Tables

  • Appreciating why we need them
  • Maximising their power

Pivot Tables

  • Effectively using
  • Filter
  • Row
  • Column
  • Value
  • Slicers

Communicating with Charts and Graphs

Best practice visualisations: including linking charts with

  • Excel Tables
  • Pivot Tables
  • Power Query
  • Power Pivot

MODULE 2: Critical Excel functions

Including AI

In addition to exploring the most basic functions in Excel, we will explore and apply the following, among others:

  • IF … and IF(AND()) … IF(OR()) … IF(AND(OR)))
  • SUMIFS()
  • SUMPRODUCT()
  • VLOOKUP
  • FREQUENCY()
  • TRANSPOSE()
  • IFERROR()
  • Dynamic Array Functions: AI and Machine Learning
  • UNIQUE()
  • SORT()
  • SORTBY()
  • RANDARRAY()
  • FILTER()
  • SEQUENCE()
  • Flash Fill
  • Data Types
  • Forecast Sheet

MODULE 3: Business Intelligence begins with Power BI for the Desktop

  • Importing data from
  • Excel
  • CSV Files
  • The Web
  • Databases
  • Basic Visualisations
  • Tabulation of data
  • Manipulating data: some basic calculation and measures

MODULE 4: More Advanced Business Intelligence

Including AI and Machine Learning

In this module we will use Power BI, Power Query and Power Pivot as we start to unleash our importing, manipulating, cleaning and analytical skills.

Taking data from a variety of sources to ensure your data are clean and ready to use

We will use a variety of techniques in this module including

  • Manually cleaning, by inspection
  • Using Power Query to help with data cleaning
  • By using the following data importing techniques, we will use Power Query to optimise our data, create basic models, break down large tables into smaller, more manageable tables
  • Importing data from
  • A worksheet
  • Another Excel file
  • A CSV file
  • A database
  • A web page
  • Unpivoting data
  • Manage data types that Excel sometimes cannot manage
  • Combine tables from within a folder
  • Split complex columns into multiple columns
  • Create functions
  • Create ratio analysis tables
  • And more

The following Power Pivot/Power BI work cannot be done in basic or ordinary Excel and yet it is so powerful yet simple to do!

  • Power Pivot: Creating data models to create relationships between Excel and database tables
  • Power BI can be used as well as and instead of Power Pivot in this context

MODULE 5: Interactive Controls

  • Form Controls Introduction
  • Check Boxes
  • List Boxes
  • Option Buttons
  • Group Boxes
  • Scroll Bar
  • Buttons
  • Combo Boxes
  • INDIRECT Function
  • Dynamic Named Range with OFFSET
  • Dynamic Ranges for Charts

MODULE 6: Analysing The Data

Including AI and Machine Learning
  • Pivot Charts
  • Slicers
  • Power Pivot
  • LET() and LAMBDA() functions
  • The development of our own User Defined Functions (UDF)

MODULE 7: Automating Your Dashboard

  • Linking to various data sources through
  • Power Query
  • Power Pivot and
  • Power BI
  • Incorporating New Data Automatically
  • Auto Refresh Pivot Tables

MODULE 8: Putting It All Together

  • Choosing the appropriate visualisation(s)
  • Column Chart
  • Line Chart
  • Pie Chart
  • Scatter Chart
  • Step Graphs
  • Pyramid Chart
  • Heatmaps
  • Sunburst chart
  • Histograms
  • Box & Whisker Plots
  • Array Formula Alternative to SUMIFS()
  • LARGE() and SMALL()
  • Array and DAVERAGE
  • Rank Values with Pivot Table
  • Formatting Quickly

MODULE 9: Case Studies

  • A dynamic View of an Asset Register
  • Building a condo: budgeting and modelling procurement
  • Budgeting and modelling the cost of an aircraft
  • Modelling the flight schedules of an international airline