B7: Advanced Budgeting & Forecasting for Oil & Gas (Modelling) (3 days)
Learn the secrets of building budgets and forecasts in oil and gas companies that are robust, easy to use and flexible.
Course Length: 3 days
|Register your interest and we’ll let you know about future courses|
|Run this course in-house at your company|
What you get from this course
- Discover a range of techniques to improve the usability and reliability of your budget and forecast models
- Learn how to integrate budgeting and forecasting and align these processes with your business strategy
- Create forecasts that properly incorporate imprecise data and allow for uncertainties
- Gain the confidence that comes from knowing that your budgets and forecasts are robust, accurate, and reliable
Examine and learn the following:
- The need for change: global forces driving companies to update their budgeting and forecasting processes
- How to allow for uncertainty
- Most applicable types of budgets/ forecasts for each area of the business
- How to improve the reliability of forecasts whilst also decreasing the time taken to prepare these
- Alternate budget methodologies and how to determine which methodology applies best in different situations
- How to optimise budgets & forecasts to achieve maximum efficiency within given constraints.
Key learning outcomes
- Understand the role of budgeting and forecasting within the enterprise.
- Understand how and when to go beyond Excel for budgeting and forecasting solutions, and understand the enterprise-level technologies that enable this to occur.
- Discover how to make powerful, flexible budget models that can be used to analyse actual and budget data many different ways at different levels of consolidation.
- Learn how to apply the latest Microsoft tools and techniques to make your budgets and forecasts faster and easier to build, update and analyse.
- To provide an introduction to Budgeting and Forecasting framework within the enterprise: how this can be used to improve performance and gain a competitive advantage.
- To provide an understanding of the budgeting and forecasting best practices that are being used in today’s O&G companies.
- To enable managers to understand the budgets and forecasts that are prepared for them.
To stay on top, companies must constantly re-examine the budgeting and forecasting models they use in their decision making processes to ensure they are up-to-date and incorporate the latest in budgeting and forecasting best practices.
Is there an opportunity to expand take advantage of a weaker competitor? Where can costs be optimised? How does a drop of 10% in oil price affect profit? To support all these decisions, sound budgeting and forecasting are essential.
This course provides participants with an understanding of both the principles of budgeting and forecasting and the practical application of these to real-world situations through the use of the most modern tools now built into Excel and Power BI.
The course then builds upon the principles and practices learned to apply these to build sophisticated budgets and forecasts that are robust, flexible and user-friendly.
This course covers budgeting and forecasting for both upstream and downstream businesses.
Day 1: Introduction to Budgeting (morning)
- What is budgeting?
- Who reads a budget?
- Why do organisations make a budget?
- Steps in creating a budget
- Parts of a budget
Group discussions & activities
The Budgeting Process
- Budgeting in large companies
- Budgeting in small/medium companies
- Role of the budget in corporate governance
- Sources of information
Group discussions & activities
Building the Budget
- Where to start?
- Key accounting concepts
- Forecasting revenue
- Types of costs
Group discussions & activities
Budgeting & Forecasting Re-cap
Overview of the budgeting process and model considerations:
- Types of budget models
- Short-term & long-term models
- Upstream vs downstream models
- Budgeting vs forecasting vs targets
Activity: Identify Improvements
Planning the Model
Compare and contrast the different budgeting methods. Determine which model is best suited to various applications.
- Bottom-up budget models
- Model development framework
- Zero-based budgeting & cost drivers
- Rolling (dynamic) Budgets
- Capital expenditure budgeting
- Determination of Model and Forecast scope and time horizons
Day 1: Key Tools for Building O&G Budgets and Forecasts (afternoon) + Days 2 & 3
PowerQuery and PowerPivot (built into Excel 2016 and available as separate downloads for 2013) provide amazing functionality for creating and maintaining budgets and forecasts.
To try and produce a budget or forecast in Excel without using these tools would be far more difficult and time-consuming.
As such, the remainder of the course is focused on real-world techniques that utilise the amazing capabilities of PowerQuery, PowerPivot and Power BI to build and present a budget or forecast model.
We will focus on using PowerQuery and PowerPivot in Excel however the same tools and techniques can be easily applied to Power BI Desktop as well with only minor adjustments.
Introduction to PowerQuery
- Techniques for cleaning up messy data e.g. data from a customer/supplier/partner
- How to transform data from one format to another
- How to integrate data sources that have different time scales and different formats e.g. daily transactions integrated with a monthly budget
- How to merge multiple queries together e.g. data from different accounting systems or charts of accounts
- How to read multiple files from a folder and easily combine them together e.g. data outputs from other systems or daily/weekly transaction files
- How to use mapping tables to translate codes to readable names, or allow grouping of accounts/customers/products to create hierarchies that allow different views of your budget e.g. by country, state, or city; or by product group, product class and individual product etc.
Introduction to PowerPivot
- What is PowerPivot and how does it work?
- Key concepts for understanding and power of PowerPivot
- How is it applied to budgets and forecasts?
- Introduction to writing formulas in DAX to create calculated columns and measures e.g. for the current year budget, re-forecast figures, year-to-date numbers, comparison to last year/month, % or $ variance between actual and budget, reporting actual & forecast in one report etc.
- Comparing PowerPivot techniques to Excel: what’s the same and what’s different?
- How to build checks and balances e.g. to find new accounts or missing codes
Putting it all Together
- Creating reports and charts with drill-down functionality that allow users to quickly find the information they’re looking for
- Creating dashboards where every element on the page is controlled by one click
- Transferring your budget/forecast model into Power BI Desktop and using this to present your results in a very graphical, intuitive way
The End Result
Participants will apply their knowledge to create a very flexible, powerful budget/forecast that incorporates the following features:
- Import actuals from an accounting system up to whatever month is available
- Import budget data from templates that have been completed by different departments
- Combine actuals with budget/forecast data to produce:
- $ and % variance to budget reports, automatically highlighting significant variances
- Full-year forecasts with actuals to a point then budget or forecast numbers for future months
- Comparison to last year and last month to identify trends
- Charts and dashboards of the results
- Ability to view reports at different levels of consolidation:
- Day, month, quarter, year
- By country, area and individual project
- By company, department, and cost centre
The model created is flexible and can be adapted to suit almost any organisation.
Introduction to Automated Budgeting/Forecasting Systems
- Compare and contrast to an Excel budgeting/forecasting system
Excel 2010: please upgrade to Excel 2016 or Office 365. Although PowerQuery and PowerPivot are available for this version, they don’t work very well so you will need to upgrade!
Excel 2016 or Office 365: please ensure the PowerPivot add-in is installed and enabled (PowerQuery is already built-in to Excel 2016).
Note: PowerQuery and PowerPivot are only available on the Windows versions of Excel. Currently, they are NOT AVAILABLE on any Mac version.
Everyone: please also download and install the free Microsoft Power BI Desktop software. This is not available for Macs either.
Who should attend?
The typical attendee is someone who is very “hands-on” in building their budget and forecast models. Typically, they must also interact with other managers and people throughout the business regarding the budgeting and forecasting process.
- Budget Analysts
- Costs Analysts
- Business Planning & Forecasting staff
- Finance Managers & Financial Controllers
- Project Managers
- Budgeting/Forecasting Managers
- Financial Advisors & Managers
- Business unit & Departmental Managers who want to better understand budgeting & forecasting
Facilitator: Jeff Robson
Masters (Applied Finance), BCom (Accounting & Information Systems), FAIM, F Fin, MAICD, CISA, MS Excel Expert
Jeff Robson is the Principal and Founding Director of Access Analytic, a specialist consultancy that focuses on financial modelling, forecasting, valuation, model auditing, and management reporting for clients throughout the world.
He is skilled in the development and maintenance of analytical tools and financial models for middle-market companies to large corporates, at all levels of complexity, in both domestic and international settings.
He has worked extensively with Chevron plus many other producers and explorers over many years. He has trained oil and gas specialists from a wide variety of companies including Chevron, Woodside, BHP Billiton, Shell, Nippon Oil, National Iran Oil Company (NIOC), Southern Oil Company, Kuwait Petroleum Company, Petronas, PetroVietnam, Carigali, Hess, Mitsui, Strike Energy, Neon Energy, Nido, and Otto Energy.
He provides training in financial modelling for companies throughout the world.
Jeff is the author of a number of white papers on financial modelling on subjects such as Financial Modelling Best Practices and Financial Model Auditing.
Highlights from his oil and gas experience include:
- Development of economic models to assist Decision Analysts modelling a wide range of scenarios for major oil & gas assets
- Auditing and development of 20-year strategic planning models for large oil & gas fields.
- Development of a business plan and budgeting models for oil & gas assets.
- Development of cash flow, production-sharing and taxation models for oil & gas clients.
- Consulting on Sarbanes Oxley spreadsheet remediation and risk assessment.
Benefits & Inclusions
|Practical: Highly experienced facilitators who actively and regularly consult to clients also.|
|Applicable: apply what you learn to your job to further your career.|
|Hands-on: the course is designed to provide maximum hands-on experience.|
|Passionate: Our facilitators love this subject!|
|Clear: we specialise in translating complex subjects into concepts that are easy to understand.|
|On-going help: you can always e-mail the facilitator.|
|Files: include worked examples, suggested solutions, reference materials, and a large variety of templates that can be immediately applied to kick-start your projects.|
|Comprehensive course notes.|
|Networking: meet industry peers and hear how they approach this subject.|
|Certificate of completion.|
|… and much more!|
Clients who have 3-4 or more people interested in this course normally prefer to run this course on an in-house basis in their offices.
We can bring all the equipment for up to 10 people so all you need is a room!
- Flexibility: we can combine content from multiple courses to create a custom course just for you, add-on a workshop or some mentoring sessions then run everything at the times that suit you.
- Timing options: if you’re in Perth, we can split courses into half or single days and schedule these at times to suit you
- No hidden extras: our fixed fee includes all materials and equipment costs for up to 10 participants
- Cost effective: no extra fees for extra participants (maximum of 8 per session)
In-House Details & Pricing
- Structure: half-day or full-day sessions
- Location: all courses are available in-house, many are also available in open sessions
- Provided: projector, laptops, manual, sample files
- In-house Fees: contact us today to discuss your requirements
In-house Course Fees
Call us on +61 8 6210 8500 to discuss your requirements.
– We are happy to customise our courses to suit your requirements
– A 10% discount applies when booking 10+ full days of training in any 30 day period
– Many courses can also be split into half-days (please enquire)
– Fees for locations outside of Perth do not include equipment
|B6||Advanced Budgeting and Forecasting (3 days)
Learn how to build sophisticated budgets and forecasts that are robust, flexible and user-friendly
|B5||Financial Modelling for Oil & Gas (4 days)
This course covers oil and gas financial modelling and project evaluation concepts, combined with practical experience in designing solutions to real-world financial modelling challenges. Learn how to build sophisticated analytical models that are robust, flexible and user-friendly and then use these to analyse oil & gas projects for potential investment.