Powerful Financial Modelling using VBA Macros

Timetable for 'Powerful Financial Modelling using VBA Macros' courses

Duration: 2 days

In an increasingly complex and fast paced transactional environment the importance of accurate, efficient and powerful financial forecast models is paramount. Microsoft Excel Macros written in Visual Basic for Applications (VBA) has become an important feature in today's financial forecast models as it provides financial analysts with an efficient way to solve advanced numerical problems. If not used appropriately however, VBA can cause more problems than it solves!

Powerful Financial Modelling using VBA Macros, will take you through the most frequently used financial applications of VBA with a focus on financial forecast modelling. The VBA environment is thoroughly investigated and the level is gradually increased through numerous hands-on practical examples. There is a strong focus on transparency and efficient coding which minimises the risk of error or your financial model being perceived as a ‘black box’ by other users and stakeholders.

Fast tracking your VBA skills will help you and your team to

• Solve complex modelling challenges you didn’t think possible in Excel
• Save hours of your day by automating repetitive tasks
• Impress stakeholders with efficient and powerful financial analysis
• Optimise analysis of tariffs, prices, debt repayments, capital structures and more
• Review and understand VBA code in third party financial models

Who should attend this course?

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

Course outline

Day one

Introduction to the VBA environment

• Learn where the essential tools are in the Visual Basic Editor (VBE)
• Explore the use of each essential tool within VBE
• Place or rearrange the tools in an efficient manner

Core VBA skills

• Understand how programming works from a general (non-VBA) standpoint
• Learn and understand the “object-oriented” programming style in VBA
• Discover how VBA and Excel communicate to one another

Reading and debugging VBA code

• Learn and understand the “vocabulary” and “grammar” of VBA programming language
• Know the common errors within VBA programming
• Use the tools to help identify erroneous codes

The building blocks of VBA coding

• Create and define/dimension variables required for a code
• Understand and use various syntaxes within VBA
• Explore the nature of looping within VBA

Day two

Applying VBA macros to your financial models

• Learn common VBA used within financial models, i.e. debt sizing/interest calculation
• Integrate checks to ensure convergence
• Discuss the various methods to speed up calculation within larger models

Create your own special excel functions

• Learn to create your own specialised functions that can be used in Excel
• Simplify complex formula into a simple generic function within Excel
• Master the otherwise impossible functions within Excel

Make your financial model more user friendly

• Link a macro to any object within Excel
• Explore specific macros for navigation
• Build a communicative macro via a message box

Advanced Applications of VBA

• Master the use of automation within Excel
• Learn the various “passing variable” types and understand local and global variables
• Discover various “hidden” functionalities within Excel

Timetable for 'Powerful Financial Modelling using VBA Macros'

View entire training schedule

Back to top