School Event

Introduction to Financial Modelling Using VBA

Course Overview and Outcomes:

This 3 day workshop will be covering topics ranging from Intermediate and Advanced Excel, which will enable attendees to navigate around complex financial situations. It is developed to understand best practice modelling methods and processes using MS Excel as the model building tool. Delegates will also be given the opportunity to implement the concepts through multiple practical sessions which will demonstrate how to create models for any industry group. By attending this intensive, practical workshop, delegates will also be addressing Investment concepts (PV, IRR, NPV, WACC MIRR) and Understand the basic concepts of Visual Basic for Applications (VBA)

 Financial Modelling: An Overview

o   Objectives and benefits of modelling

o   Outcomes of financial modelling

o   Principles and methods of the modelling process

o   Layout, structure, formatting and presentation of a good model


Intermediate Excel Review

  o   Customizing the Excel Environment

o   Configuring Excel Options

o   Customizing the Ribbon and the Quick Access Toolbar

o   Enabling Excel Add-Ins

                o   Creating Advanced Formulas

o   Using Range Names in Formulas, 3 D formulas

o   Using Specialized Functions

o   Using Array Formulas

                o   Analyzing Data with Functions and Conditional Formatting

o   Analyzing Data by Using Text and Logical Functions

o   Applying Advanced Conditional Formatting

               o   Organizing and Analysing Datasets and Tables

o   Creating and Modifying Tables

o   Sorting Data

o   Filtering Data

o   Using SUBTOTAL and Database Functions


Intermediate Excel Review (Continued)

o   Visualizing Data with Basic Charts

o   Creating Charts

o   Modifying and Formatting Charts

 o   Analysing Data with PivotTables and Pivot Charts

o   Creating a PivotTable

o   Analysing PivotTable Data

o   Presenting Data with PivotCharts

Review of Advanced Excel Features

      Combining Functions

     Formulas and Functions to Apply Conditional Formatting

     Tracing Cells

     Determine Potential Outcomes Using Data Tables

     Determine Potential Outcomes Using Scenarios

     Use the Goal Seek Feature

     Activate and Use the Solver Tool

     Analyse Data with Analysis ToolPak

  Accounting Concepts that Stimulate Financial Modelling

Ø  Balance Sheet



      Current and non-current assets and liabilities

      Share capital and retained earnings

Ø Income statement

      Revenue, expenses and profit

      Interest, tax and depreciation



       EBT and NPAT

 Ø  Cash flow statement




 Ø  Interrelationship between the statements

      Income statement

      Balance sheet

      Cash flow statement

 Ø  Relevant Finance Concepts and Financial Functions

      Discount rate

      Present and future value

      Internal rate of return

      Cost of debt and cost of equity

      Weighted average cost of capital

      Free cash flow to equity and to the firm

      FX rate

      PMT, RATE, PV, FV & NPER functions

      Inflation rate, real and nominal values

      IRR, MIRR, hurdle rate

      NPV, XNPV, and XlRR functions

 Practical Exercise:

Delegates will work through a capital budgeting application as an investment decision

 Practical Exercise:

An evaluation of a company using a combination of financial as well as excel functionality ( WACC Capital asset pricing

Introducing Visual Basic for Applications

Ø   Developing Macros

·         Create a Macro Using the Macro Recorder

·         Edit a Macro

·         Debug a Macro

Ø   Formatting Worksheets Using Macros

·         Insert Text

·         Format Text

·         Sort Data

·         Duplicate Data

·         Generate a Report

Ø   Creating an Interactive Worksheet

·         Determine the Dialog Box Type

·         Capture User Input

Ø   Working with Multiple Worksheets

·         Insert, Copy, and Delete Worksheets

·         Rename Worksheets

·         Modify the Order of Worksheets

·         Performing Calculations

 Ø   Understanding Objects

·         Understanding Objects

·         Navigating the Excel Object Hierarchy

·         Understanding Collections

·         Using the Object Browser

·         Working with Properties

·         Using the With Statement

·         Working with Methods


  Who should attend?

•    All managers,
Business owners
Engineers, and
•    Project managers

Dates and Times:
  • Thu 30 May 2019  08:30 to 16:30
  • Fri 31 May 2019  08:30 to 16:30
Type: Course
Venue: CESA Training Centre, Paulshof, Johannesburg , Paulshof
Fee: R 6 895.26 excl. VAT
CESA Validation Num:
CPD Points: 2
Coordinator: Blessings Banda