Financial Modelling - Advanced (& Introduction to VBA)

Overview

This two-day course is designed for those looking to expand their financial modelling skills. Numerous hands-on practical Excel exercises are used to demonstrate different functionality in a modelling context. The focus is on time-savers and other techniques which will improve the quality of your financial models.

This course aims to consolidate and enhance participants’ knowledge and skills in Excel (and VBA basics), as they apply to financial modelling. It consists primarily of hands-on exercises, with a brief recap of underlying financial theory where appropriate. The aim is to develop delegates’ ability to build models in various situations that are dynamic, error-free, flexible and robust.

Who is this for?

This course is for those who already have Excel financial modelling experience and are looking to consolidate their skills as well as learn some more advanced techniques. It is appropriate for those working in corporate finance, consulting, financial planning, financial markets, and for general users of Excel for financial applications.

The course has been developed as a natural follow-on from Financial Modelling - Comprehensive. The VBA section does not require prior experience.

What will you learn?

• Introduction
  • Recap of modelling basics
  • Review and improvement of existing model (exercise)
• Excel FINANCIAL functionality including IRR, XIRR, PMT, PPMT, RATE and others…
• LOOKUPS: Advanced uses for Match, Index, Offset, V/H/X Lookup
• Text features including LEN, MID, FIND, TEXT, VALUE and others….
• Date and time features including: TODAY, DATE, EDATE, EOMONTH, NOW and others…
• Array functions: examples, and pros and cons
• SUMIF: advanced uses
• Circularity: Finding it, using it, removing it
• Sensitivity analysis:
  • Watch window, goal seek and solver
  • Switches and scenarios: Data validation and ActiveX controls
  • Data tables: 1 and 2 dimensional
• Model review, audit, security and protection
• Introduction to VBA
  • VBA and macros: overview
  • Subroutines vs Functions
  • Recording a simple macro
  • Macro review, edit and debug
  • Triggering a macro
  • User-defined functions
  • Macro to roll-forward a financial forecasting model

Please read the following guidance carefully, which applies only if you are attending this course online:

• In addition to the Online Classroom, you will need to have your own version of Excel open to complete the exercises
• It is difficult (but not impossible) to do this with only 1 screen as you will need to be regularly switching between open windows. We recommend to either:
  • Extend from a computer to a 2nd screen, or
  • Use 2 computers, or
  • Use a tablet for the online classroom and a computer for the Excel
• You are strongly recommended to perform the exercises on a PC rather than a MAC. Using a MAC is possible but the trainer will be unable to help you with the required shortcuts.

Related courses

Financial Modelling - Comprehensive

Valuation Techniques (with Valuation Modelling)

Debt Modelling (with LBO)