VBA for cashflow models

See the course schedule for this course

In an increasingly complex financial environment the importance of transparency and accuracy are paramount. Visual Basic (or VBA) has become an important feature in today’s Financial Models and provides us with an efficient way to solve advanced numerical problems and create user friendly and transparent analysis. However if it is not used appropriately it can cause more problems than it solves.

Do you need VBA macros in your financial models?

This course gives you the skills and confidence to write your own code and expand your analytical skill set beyond the standard Excel functionality. It is targeted at analysts who need to build complex but efficient financial models or who want to improve their ability to work with models developed by others.

Over two full days we will take you through the most frequently used financial applications of VBA. The VBA environment is thoroughly investigated and the level is gradually increased over numerous hands-on practical applications. As with all our of courses there is a strong focus on transparency and efficient coding which minimises the risk of error and also the model being perceived as a black box by other users. 

Day One

Learn how to efficiently use VBA to build, debug and analyse financial models

Overview of core VBA skills

• The Visual Basic Environment
• Common elements of the VBA syntax
• Generating your own code versus recording a macro
• Programming standards
• Application: Navigating a model using VBA
• User Defined Functions ('UDF')

How to test and debug VBA applications

• Break Points
• Immediate Window
• Watch Window
• Stack
• Application : Debug a client’s Project Finance VBA code

Coding VBA – Building blocks

• Difference between sub/function/macro
• Interacting with an Excel spreadsheet
• Different types of Looping Operations
• Explanation of ‘Option Explicit’
• Application: Solving a circular reference using a cut & paste macro.

Day Two

Applications in financial models

Application session

• Automated Sensitivity and Scenario analysis using VBA
• Sculpting a Debt Repayment to a Target Ratio
• Debt sizing / Debt sculpting optimisation
• Automated execution of Macros
• Presentation applications
• Disclaimer Coding
• Security discussion on passwords and protected/hidden sheets
• Tariff break-even to a target IRR

Advanced Applications

• ‘Automation’ – controlling ALL of Windows other components
• ‘Explode’ your Graphs – exceptional presentation functionality
• Error Handling – what is it and how should you be using it?

Register now

Michael Moran
+61 2 9229 7409

Next Courses Available (see all courses) Back to Top