Application Of VBA & Advanced Excel To Financial Modeling & Analysis
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

  • 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

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

Download Brochure Request In house Proposal

Course Rounds : (5 -Days)


Code Date Venue Fees Register
ACC117-06 28-11-2021 Amsterdam USD 5950
ACC117-07 20-03-2022 Istanbul USD 5450
ACC117-08 12-06-2022 Riyadh USD 4950
ACC117-09 21-08-2022 Dubai USD 4950
ACC117-010 23-10-2022 Kuala-Lumpur USD 5450
Prices doesn't include VAT

UpComing Date


Details
  • Start date 28-11-2021
  • End date 02-12-2021

Venue
  • Country Nethersland
  • Venue Amsterdam

Quality Policy

 Providing services with a high quality that are satisfying the requirements
 Appling the specifications and legalizations to ensure the quality of service.
 Best utilization of resources for continually improving the business activities.

Technical Team

BTS keen to selects highly technical instructors based on professional field experience

Strengths and capabilities

Since BTS was established, it considered a training partner for world class oil & gas institution

Search For Available Course

Abu Dhabi, UAE

1st floor, Incubator Buildingو Masdar City, Abu Dhabi, UAE

00971-2-6446633

Sun to Fri 09:00 AM to 06:00 PM

info@btsconsultant.com

Contact Us anytime!