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

•       Assets

•      Liabilities

•      Current and non-current assets and liabilities

•      Share capital and retained earnings

Ø Income statement

•      Revenue, expenses and profit

•      Interest, tax and depreciation

•      EDITDA

•      EBIT

•       EBT and NPAT

 Ø  Cash flow statement

•      Operations

•      financing

•      Investment

 Ø  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

This event has been cancelled