Advanced Certificate in Financial Modelling and Data Analysis

Integrated Course Covering Modules A, B, C & D

 

Download Course Booklet
certificate

Course Description

This intensive 10-day course covers the entire content of the four  modules of the CinFM program namely:

  • Excel Modelling and Analysis (Module A)
  • Modelling in Corporate Finance, Valuation and Markets (Module B)
  • Data Analysis and Manipulation using Excel, PowerQuery and PowerPivot (Module C)
  • Automation and Algorithms for Advanced Modelling, Valuation, Risk and Data Analysis (Module D)

The 10-day duration is possible to achieve for several reasons (when compared to the 12 days required to take the four modules individually): First, the materials can be covered in the most optimal order. For example, in the integrated program macros and automation are introduced early on, so that they can be applied directly to relevant areas throughout the course, rather than be treated as a separate subject (as they would be in Module D). Second, there is some saving in administrative time (e.g. course initiation, personal introductions etc.). Third, any content overlap between the course modules can be reduced or optimised.

Thus, the Learning Objectives and Key Topics covered are equivalent to the totality of all the course modules (A, B, C and D) – see below and/or individual modules for more details.

The course is suitable for all levels and is aimed at those who wish to develop their expertise extremely quickly. Participants who complete the course are awarded the Advanced Certificate in Financial Modelling and Data Analysis.

Learning Objectives

The learning objectives consist of all the objectives on the individual modules, as can be seen in the boxes below (or by looking at the details of each course)

Key Topics Overview

The key topics consist of all those covered on the individual modules, as can be seen in the boxes below (or by looking at the details of each course).

 Course Dates

 Course Agenda in Detail

Day 1: Modelling Principles, Planning and Structures

1.1 Key Modelling Considerations, Common Structures and Components

  • Course overview and introduction
  • Modelling overview: Definition, scope, and skills required
  • Introduction to “decision quality”
  • Model context, scope, objectives and requirements planning
    • Assessing a business’ environment, economics, and strategy
    • Ratio analysis and performance metrics
    • Supporting high quality decisions using models: Key issues to address
  • Mapping of decision structures, logic flow and relationships: Processes and tools
    • Decision trees, influence diagrams and logic reversal
    • Sensitivity analysis thinking and report-based planning
    • Data-driven modelling
  • Core components: Overview of frequent logic structures and model building blocks

1.2 Hands-on Exercises with Selected Modelling Components

  • Excel essentials: Structure, formulas, functions, and best practices, examples
  • Hands-on exercises: Core components and Excel functions (I)
    • Calculating historical ratios and growth rates
    • Converting between time periods of different lengths (e.g. average and compound growth)
    • Forecasting using growth rates (e.g. revenues, costs)
    • Forecasting with inflation; nominal and real terms
    • Forecasting using ratios and scaling factors (e.g. balance sheet items, stock days)
    • Linear forecasting of fixed and variable costs

1.3 Sensitivity Analysis and Results Presentation

  • Sensitivity analysis, its variations and uses (e.g. absolute and relative sensitivities, single and multi-variable, role in model testing, creating scenarios, variance analysis, GoalSeek, introduction to optimisation, overview of advanced topics)
  • Hands-on exercises:
    • Creating sensitivity analysis in one and two variables
    • Building relative-form sensitivities
  • Principles of effective visual communication
  • Hands-on exercises:
    • Creation of graphs and charts

1.4 Hands-on Exercises with Selected Modelling Components

  • Hands-on exercises: Core components and Excel functions (II)
    • Comparison and logical calculations
    • Conditional calculations
  • Hands-on exercise:
    • Using sensitivities to develop and test complex model formulae (e.g. multi-rate taxation and financing structures)
    • Calculating break-even points
  • Day 1 Recap, Q&A, Discussion, Close

Day 2: Further Model Building Blocks, Excel Functions and Exercises

2.1 Hands-on Exercises with Selected Modelling Components

  • Recap of Day 1, including core building blocks required
  • Hands-on exercises: Core components and Excel functions (III)
    • Linear allocations across categories
    • Non-linear allocation to categories (for tax, financing and other calculations)
    • Time transposition and triangles (e.g. for depreciation)
  • Hands-on exercises: Core components and Excel functions (IV)
    • Aggregation of range data
    • Use of distributed data sets (multi-sheet approaches)

2.2 Lookup Functions and their Applications

  • Overview of functions and uses
  • Hands-on exercises: Core components and Excel functions (VI)
    • Building scenario and variance analysis
    • Capturing model events and linking items
    • Dynamic ranges and flexible worksheet structures
    • Time-shifting
    • Distributed worksheet-based scenario modelling and selection
  • Best practices in function selection (e.g. reasons to avoid using V- and HLOOKUP and better alternatives, use of Volatile functions etc.)
  • Introduction to approaches to link model components (e.g. corkscrew structures, separate reference or input/output ranges, Lookup functions).

2.3 Hands-on Exercises: Integrating Several Building Blocks into a Realistic Model

  • Introduction and description of modelling exercise
  • Excel short-cuts for creating formulae, model calculations and formatting
  • Hands-on exercise:
    • Creation of model components
    • Integration and linking of components
    • Sensitivity analysis and testing

Model Auditing, Best Practices, and Results Presentation

  • Auditing and troubleshooting models: key approaches and tools
  • Hands-on exercises:
    • Model auditing and dependency/precedence tracing
    • Formula evaluation and correction
    • Short-cuts
  • Introduction to core themes in best practices
    • Planning the optimum level of granularity and detail for the time axis
    • Model layout and flow, modularity and linking
    • Workbook and data structures, centralized/decentralized approaches
    • Transparency; individual calculation steps, formatting and labelling
    • Use of named ranges
  • Review of best practices in context of completed hands-on exercise
  • Day 2 Recap, Q&A, Discussion, Close

Day 3: Further Functions and Financial Calculations

3.1 Date, Logical and Array Functions and Their Applications

  • Overview of Date, Logical, array functions, and matrix calculations
  • Hands-on exercises e.g.
    • Creation of flexible time axis
    • Calculation of specific types of dates (last Friday in month)
    • Quarters, identifying and labelling months; date formats
    • Matrix calculations for cost allocation and portfolio risk and returns analysis

3.2 Interest Rates and Related Calculations

  • Interest rates, and differences to standard growth calculations
    • Interest rates, compounding types and accrual
    • Interactions of interest compounding dates with model time periods; applicable interest rates
    • Methods to calculate ending balances: advantages, disadvantages of each
    • Introduction to management of circular references
  • Hands-on exercises: Core components and Excel functions (V)
    • Calculation of interest rates
    • Examples of model with circularities

3.3: Financial Functions and Their Applications

  • Discounting and internal rate of return; meaning, comparison and uses
  • Key financial functions
  • Hands-on exercises
    • Loan amortization and mortgage calculations (various uses, variations and pitfalls)
    • Discounting and internal rate of return calculations and comparisons
    • Project evaluation
    • Analysis of a lease/buy-decision

3.4 Information and Text Functions and Their Applications

  • Overview of Information and Text functions
  • Hands-on exercises e.g.
    • Checking data integrity and consistency
    • Splitting and combining data sets
    • Models that update with actuals or as additional data is available
  • Day 3 Recap, Q&A, Discussion, Course Close