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.
By the end of this course, delegates will be able to:
- 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 practices 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
Module 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
Looked up functions
- Text, date and information functions
- Array, statistical and database functions
- Hands-on exercises
- Potential uses and benefits of VBA approaches
- Q&A, Discussion,
Module 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
Module 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 detain specified sheets of separate workbooks
- Q&A, Discussion
Module 4 - User-defined Functions, Simulation and Optimization
- 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
This course is designed for staff involved in Business & Strategic Planning, Planning, Development & Commercial, and Budgeting & Forecasting
- Business & Strategic Planners
- Planning, Development & Commercial Analysts
- Budgeting & Forecasting Staffs
- Financial Analysts
- Petroleum Economists
- Risk Managers
- Business Analysts
- Portfolio Managers