Application of VBA & Advanced Excel to Financial Modeling & Analysis

Many analysts and modelers develop a high level of competence in the use of Excel, but often find that they have a lack of awareness and capability around extending their skills to VBA  (macros).

The use of VBA in the modelling of oil and gas sector is often particularly beneficial, and sometimes a necessity: For example, macros are often found in standard applications, such as project finance models, as well as to automate sensitivity-, scenario-, simulation- and optimization-procedures that arise frequently in the sector. In addition, macros can be extremely useful in data manipulation: including where repeated manipulation of data is required, where consolidation from multiple data sources is needed (allowing larger datasets than a single workbook would permit, for example), or where data is taken from external sources before being manipulated in Excel. User-defined functions can also be used to create flexible model structures and layouts, including mechanisms to rapidly include or delete assets or data sets in a model, for example.

This 4-day course is aimed at experienced Excel practitioners who wish to become proficient in the use of VBA, as well as to refresh some of the more advanced aspects of Excel. The course focuses on learning through hands-on exercises. Day 1 is used to review advanced Excel modelling topics and functions, whilst Days 2-4 are focused on using VBA in a variety of analysis, modelling and decision-support contexts.

• CREATE more effective, flexible and powerful models using VBA(macros) and advanced Excel.

• DEVELOP an in-depth knowledge of a wide range of advanced functions in Excel.

• RECOGNIZE modeling situations where the use of VBA would be effective & worthwhile.

• CONSTRUCT highly flexible, robust and transparent models that follow best practice principles

• CREATE a solid basis to write VBA code

• ANALYZE and MANIPULATE data sets efficiently and able to CONSOLIDATE data from different workbooks.

• GAIN insight into alternative methods and best practises in model design

• GAIN exposure to a wider range of applications in financial modeling

• LEARN a variety of options ways to design, structure, layout and build models Portfolio Managers

This course is designed for staff involved in Business & Strategic Planning, Planning, Development & Commercial, Budgeting & Forecasting

• Business & Strategic Planners

• Planning, Development & Commercial Analysts

• Budgeting & Forecasting Staffs

• Financial Analysts

• Petroleum Economists

• Risk Managers

• Business Analysts

• Portfolio Managers

Day 1 - Setting the Scene for VBA: Recap of Advanced Excel Functionality

Introduction & Course Overview
* Recap of core modeling principles
* Model formulation, design and best practices (structure, layout, formatting, named ranges, circular references)
* Distinguishing and sensitivities, scenarios risks, uncertainties and optimization situations
* General limitations of Excel and the key uses of VBA within financial modeling (overview)

Recap of key advanced function areas
* Lookedup functions
* Text, date and information functions
* Array, statistical and database functions
* Hands-on exercises
* Potential uses and benefits of VBA approaches
* Q&A, Discussion, Day 1 Close

Day 2 - Getting Started with VBA for Financial Modeling

* The need for VBA and demo of pre-built examples
* Hands-on exercises
* Introduction to syntax; object orientation, ways to refer to ranges
* Creation of simple Input and Message Boxes
* Controlling execution and related topics: Use of With, Set, and Conditional statements
* Comments, indenting and formatting, data types, variable declaration, and other best practices
* Hands-on exercises
* Introduction to event code

Day 3 - Using VBA for Data Manipulation and Consolidation

* Efficient manipulation of data using VBA: An introduction
* Hands-on exercise:Consolidation into a single sheet of datainspecified sheets of separate workbooks
* Q&A, Discussion, Day 3 Close
 

Day 4 - User-defined Functions, SImulation and Optimisation

* Benefits and examples of user-defined functions
* Debugging functions: varying the position of the return statement, stepping through from a sub-routine etc
* Demo of more complex examples
* Impact of VBA on possible model layout and data structures