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-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 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

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

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

BTS attendance certificate will be issued to all attendees completing minimum of 75% of the total course duration.