Application of VBA and Advanced Excel to Financial Modeling and Analysis

Introduction

Many analysts and modellers 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 5-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-5 are focused on using VBA in a variety of analysis, modelling and decision-support contexts.

Objectives

• 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

Training Methodology

This is an interactive course. There will be open question and answer sessions, regular group exercises and activities, videos, case studies, and presentations on best practices. Participants will have the opportunity to share with the facilitator and other participants on what works well and not so well for them, as well as work on issues from their own organizations. The online course is conducted online using MS-Teams/ClickMeeting.

Who Should Attend?

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

Course Outline

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 use 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 the VBA approach
• 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 data in specified 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 

Day 5: Practical Cases