Master Class: Advanced Financial Modelling for Oil & Gas

Overview This is an interactive and hands-on 5 day course aimed at developing participants’ skills in implementing a wide range of financial modelling applications that are found in the oil and gas and related sectors. Even if you do not have a lot of modelling experience, the course is designed to take you to a high level. Key themes include: best practices in designing and structuring models, sensitivity and scenario analysis, tools to create highly flexible models (such as shifting cash flows in time, manipulation of data and data sets), and risk, uncertainty, and simulation modelling, optimisation, as well as a range of advanced Excel techniques, participants learn core elements of VBA. A summary of the agenda by day is as follows: Day 1: Foundations of financial modelling in Excel for O&G applications Day 2: Flexible and efficient modelling using index, match and date switches Day 3: Introduction to VBA for financial modelling Day 4: Simulation for risk and uncertainty modelling in O&G applications Day 5: Risk analysis and optimisation modelling Benefits of the course After this course delegates should have achieved a number of benefits and skills: Recognise situations where more advanced tools can add value in existing models Become quicker, more effective and efficient in day-to-day modelling work Learn from a hands-on practical approach on the application of financial modelling in the oil & gas industry Leverage on advanced tools in sensitivity analysis, including modelling risk and uncertainty Construct flexible, robust and transparent models that follow best practice principles Manipulate data sets efficiently, and their inclusion in a model in the most effective, flexible and transparent Develop an in-depth knowledge of a wide range of advanced functions in Excel Create a solid basis to write VBA code and recognise when its use is appropriate Develop your own solutions to challenges faced in modelling financial situations
Day 1 Foundations of financial modelling in Excel for O&G applications Objectives and types of models; the modelling process Key best practices in Excel modelling Review of core Excel functions Model auditing and short-cuts (introduction and key points) Review of core functions in Excel Tools for sensitivity analysis and optimisation Introduction to statistical analysis and database manipulation and analysis (filtering, dynamic sorting, intro. to array functions etc.) Hands-on exercises, with applications to: - Business planning and forecasting - Tax calculations - Cash flow valuation - Basic production sharing agreements - Project finance modelling Day 2 Flexible and efficient modelling using advanced Excel functions Introduction to match, index, lookup, text, date and information functions Hands-on exercises to show the power of lookup and other functions to build more flexible models and manipulate data for oil and gas applications: - Scenario analysis, variance analysis - Creating flexible start dates in models - Creating highly flexible business plans, allowing rapid input or deletion of new assets or data - Creating dynamic charts - Manipulating and cleaning data e.g. downloads of oil prices, dates, multi-currency databases, matching and reversing data, and combining databases together Day 3 Introduction to VBA for financial modelling Benefits and uses of VBA; demo of various applications Hands-on exercises to write simple sub-routines - Simple recording example - Running code and stepping through line-by-line; use of break points - Improving recorded code through direct writing of code; use of named ranges etc - Creation of simple input and message boxes - Creation of buttons to run code Hands on exercise to automate repetitive actions and work efficiently: - Consolidation of data sets into a database - Division of quarterly date into monthly Hands-on exercises: creating user-defined functions Further core topics - Data types and variable declaration - Worksheet functions and VBA functions - Use of set - Use of with - Conditional statements Use of VBA to create alternative model structures (database and multi-datasheet structures) Day 4 Simulation for risk and uncertainty modelling using VBA and RISK Core concepts in risk and simulation modelling - Applications of risk modelling in business, finance, and oil and gas (overview) - Benefits of risk modelling - The role of distributions in risk models - The use of distributions; key terms and concepts - Standard and alternate parameters Introduction to simulation using Excel and VBA - Examples - Benefits and limitations; advantages of add-ins - Overview of Excel add-ins for simulation modelling - Running a simulation and results interpretation - Tornado graphs and scatter plots - Using statistics functions for model outputs - Saving files, repeating a simulation and related aspects (incl. sampling methods, no. of iterations, convergence testing etc.) Modelling discovery risk Hands-on exercise: Volumetric uncertainty Introduction to the LogNormal distribution Distribution fitting (overview) Day 5 Further simulation modelling, decision trees and optimisation Introduction to dependency modelling Hands-on exercises: - Discovery probabilities contingent on earlier success - Dependencies in volumetric estimation - Correlation modelling in reserves aggregation - Multiple simulations Comparison of the effect of correlation and other dependency relationships Hands-on exercise: The discrete distribution - Scenario generation - Selecting scenarios for uncertain production profiles - Comparison with multiple simulations - Derivation of Swanson’s rule (time permitting) Further applications, examples and demos (time permitting) - Production decline modelling (overview and discussion) - Drill program timing and resources: uncertainty and optimisation - Cash flow models - Modelling profit share agreements and fiscal systems; optimisation aspects - Mapping volume to non-linear cost curves - Time-series modelling (crashes, mean-reversion, chains, correlated series) - Integrated economics (uncertainty in reserves, production, costs and operations) - Valuing flexibility and real options - Modelling other dependencies e.g. between porosity and oil saturation An introduction to decision trees, and to optimisation - Role and benefits of using decision trees - Implicit assumptions and limitations of trees - Introduction to modelling with precision tree (cumulative pay-off trees) - Hands-on exercise: optimising the launch dates of projects in a portfolio with portfolio Course summary and close