Course Details
Your Growth, Our Mission
Course Description
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.
The Training Course Will Highlight ?
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.
Training Objective
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.
Target Audience
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
Training Methods
Daily Agenda
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
Accreditation
BTS attendance certificate will be issued to all attendees completing minimum of 75% of the total course duration.
Quick Enquiry
Request Info
Related Courses
Your Growth, Our Mission
Contact Us
Contact us to meet all your inquiries and needs, as our professional team is pleased to provide immediate support and advice to ensure you achieve your goals and facilitate your experience with us in the best possible way.
UAE
Office
Mobile
E-mail
Working
Hours