Model Optimisation & Control with VBA

1 day duration

Financial modellers do not have a complete skill set until they master VBA (Visual Basic for Applications). Experienced professionals in the energy and infrastructure transaction space know that while you don’t need to be a coding professional, there are certain challenges you must be able to navigate around efficiently.

Adding a comprehensive suite of VBA applications to your toolbox enables you to tackle more complex models, optimisation requirements and automation requests. This makes you faster and more precise, and most importantly – removes any hurdles between you and whatever a transaction model or a demanding client challenges you with.

VBA case studies

• Learn about the VBA environment & syntax, how to record macros for a deeper understanding of the Visual Basic language and how to implement a range of loop options in your code (e.g. While/Wend, For/Next)
• Discussion on common circularities in financial models and learn how to break model circularities in standard (e.g. tax paid) and more complex cases (e.g. debt sizing)
• Automate a goal seek of pricing to achieve a target IRR whilst meeting model restrictions and learn advanced techniques to manage this process efficiently
• Combine multiple optimisation routines (e.g. debt sizing, tax paid & pricing) and learn how to refine and improve your code & model speed
• Switch efficiently between FC and post-FC sensitivities using scenarios to control macros in order to store & call debt sizes
• Develop a Log Sheet module to record the variation of outputs as they develop over time and automate this when saving the model

Key learnings

• Learn best-practice around macro presentation, shortcuts, launch objects and developer control buttons
• Understand the principals of when to use VBA to supplement worksheet calculations and when not to, with clear real-world examples
• Learn how to create macros that are easy for the user to use, understand and modify
• Discuss which macros are useful for financial models and which should be avoided
• Explore various ways of improving the speed of macros for a more efficient model
• Understand the tools available in the VBA environment and how they assist in achieving your objectives
• Practice fundamental coding components; variables, loops, conditions, Excel interaction, properties, and a whole lot more

What you will take away from this course

If you have previous experience in project finance or transaction models, you will be aware of several situations which would be solved faster and better with VBA. This course gives you a solid foundation of knowledge and a package of tools which you can apply in challenging situations.

The course is delivered by professional financial modellers specialised in energy and infrastructure transactions, and many examples will be drawn from these sectors including renewable energy.

Is this programme for you?

If you are looking to become a complete financial modeller, then having a robust understanding of VBA is required. This course enables you to solve most of the typical project finance or transaction model challenges, while also give you the components required to develop custom solutions to your own models and tools.

We highly recommend a broad understanding of project finance or transaction modelling equivalent to Mazars Best-Practice Project Finance Modelling course.

Upcoming

Recommended Courses

Microsoft Excel Diploma Course

Microsoft Excel is an integral part of the business landscape and it is a critical...

Read more

Business Analytics Course In Excel

Excel is the world’s leading tool for data manipulation, analysis, management and reporting. The breadth...

Read more