OVERVIEWThe 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.
Macros, Automation and Algorithms
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.
Risk, Optimisation and Quantitative Finance
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.
Advanced Data Analysis
Further Topics in statistical analysis. Conditional probability · Bayesian analysis · Value of test-based information.
Introduction to Machine Learning. Key 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.
Further Topics in PowerPivot. More on Filters, Slicers and the Excel Data Model. Filter and Evaluation Contexts and Measures. Using Relational databases · Functions and iterator functions. Introduction to the DAX language. Sensitivity analysis · CUBE functions · Time-intelligence · Complex queries. Links to Power BI.