The courses cover a wide set of topics and capabilities related to advanced modelling and data analysis in the Excel environment, including the use of VBA, risk analysis, simulation, optimisation, quantitative finance, as well as PowerPivot, advanced statistics and an introduction to machine learning.
The courses are currently offered only as instructor-led courses (on-line or classroom), and by pre-arrangement with us. The actual courses that are delivered can be customised to include aspects of any courses in the overall CertFM Program. Certificates are issued as appropriate.
Introduction to VBA and macros. Benefits, uses · Key steps to getting started · Recording and adapting code · Loops· Conditionality · Working with ranges and objects · Code execution · Best practices · Run-time inputs and alerts · Debugging and checking. Automation of common modelling tasks. Sensitivity and scenario analysis · Circular references · GoalSeek. Automating data cleaning and manipulation · Filters and extraction · Data Refresh · Removing duplicates · Worksheet consolidation · Worksheet events and changes. User-defined functions. · Bespoke calculations for statistics, financial modelling and data analysis.
Purpose · Meaning · Benefits · Risk management · Approaches and techniques · Risk identification and mapping. Business applications · Uses in finance. Getting Started with Monte Carlo Simulation. Random sampling · Repeated calculations and automation · Creating and running simulations · Results analysis and control. Further topics. Probability distributions · Inverse functions · Statistical analysis · Choice of distributions. Examples of key applications. Cost budgeting · Risk registers · Cash flow risk · Time schedule risk · Project cost-time integration · Random walks and Brownian motion. Portfolio optimisation. How optimisation arises · Methods to find optimal solutions · Applications in general business and in finance.
Portfolio risk and return · Model calibration using optimisation · Other optimisation objectives · Value-at-risk · Markowitz model · Analytic and numerical solutions · Huang-Litzenberger · Using Solver in Excel and with VBA. Introduction to sequential optimisation. Tree-based decision-making and real options· Conditional probability · Bayesian analysis · Value of test-based information. Options and derivatives. Risk-neutral valuation · Options valuation · Black-Scholes’ formulae · Binomial trees · Simulation methods · Introduction to grid-based methods and finite differences. Introduction to credit modelling. · Vasicek and Merton formula · Default probabilities · Transition matrices · Portfolio losses · Capital requirements.
Introduction to PowerPivot. Pivot Tables · Filters · Slicers · Measures · The DAX language · Understanding filter and evaluation contexts · Excel Data Model · Relational databases · Functions and iterator functions · Sensitivity analysis · CUBE functions · Time-intelligence · Complex queries. Links to Power BI.
Reinforcement of Advanced Statistics: Recap of concepts and methods in single and multi-variate statistics and their calculations and Excel functions, as well as key ideas in probability and risk assessment. Tree-based decision-making and real options. Conditional probability · Bayesian analysis. Value of test-based information.
Introduction to Machine Learning and Advanced Statistics. Machine learning concepts · Model calibration using optimisation · Value of information · Entropy · Cluster analysis · Optimal decision trees and information sequencing · Reinforcement learning · Other machine learning algorithms and methods.