Banks
Accounting for Financial Instruments
This session provides an overview of accounting for financial instruments including loans, bonds and derivatives.
Fundamentals of Regulatory Capital
The aim of this session is to provide delegates with an introduction to bank regulation. The regulatory environment is explained, and the key Basel accords are introduced.
Modeling Fundamentals for Banks
Delegates build their bank modeling skills through the use of a simple bank model. This model contains the key components common to all bank projection models. Delegates complete a forecast model of a bank before stress testing their assumptions. Finally, the model is documented and checked.
Forecasting and Modeling for Banks
Delegates will learn how to model and integrate the balance sheet, income statement and regulatory requirements of a real bank using Excel spreadsheets. In addition to learning the steps necessary to build a bank financial model, delegates will also cover how to build models accurately and efficiently through a series of best practice modeling rules. Delegates also learn how to stress test the assumptions used, to check their work efficiently and to document it.
Bank Valuation
Three valuation methods are examined and modeled in this session, namely: Free cash flow to equity, comparable companies’ analysis and sum or the parts valuation. The advantages and disadvantages of each method are considered.
Bank M&A
During this session, participants build a fully integrated merger model which combines financial statement forecasts for the acquirer bank and the target bank. Practical consolidation issues are addressed. The deal analysis focuses on the pricing, earnings and capital impact and value creation.
Bank M&A Fundamentals
This session covers the basic steps of analyzing an acquisition within the bank industry – covering the impact of a deal on the financial statements with a particular focus on EPS, PE and contribution analysis. By the end of the session, the class builds an accretion /dilution model using EPS forecasts and acquisition assumptions, proforma capital ratios and a proforma balance sheet.
M&A Modeling Issues for Banks
This session addresses three key complexities in M&A models: Non-coterminus year-ends, using a flexible deal date, currency translation and the creation of non-controlling interests. In order to fully benefit from the session, delegates should have knowledge of the fundamentals of M&A accounting and familiarity with financial modeling.
Regulatory Capital
This session provides delegates with a detailed tour and review of the Basel accords issued by the BIS. The evolving regulation stemming from Basel II and Basel III proposals is explored to give the delegates a strong working knowledge of regulatory capital.
Bank Financing and Capital Structure
This day covers the fundamentals of bank regulation, including the calculation of bank regulatory capital and risk-weighted assets. Delegates will learn the difference between different tiers of capital and how hybrid products are designed to simultaneously satisfy regulators, rating agencies and investors’ requirements. The day covers the entire spectrum of bank financing products and discusses the impact on banks’ liquidity ratios.
Oil and Gas
Accounting and Financial Statements Analysis
This section will allow delegates to learn accounting principles applied to oil & gas companies, review and analyse oil & gas companies’ financial reports and relevant disclosures.
Comparable Valuation Analysis
Key topics:
- Select comparable companies
- Obtain historic and projected data
- Normalise the results and exclude extraordinary charges
- Compute operations metrics: EV/1P or 2P reserves, EV/MCFE
- Compute operational metrics: EV/DACF, EV/EBITDA, EV/EBITDAX, P/CF, Price/NAV, P/E
- We will examine E&P companies listed in the US, Europe and Asia Pacific regions
- Case study: Comparable companies analysis for an independent producer
Introduction to Upstream Fiscal Regimes
This section will allow delegates to understand main components and differences between different upstream fiscal regimes.
Modeling a Concession Agreement
This section will allow delegates to learn how to evaluate and model royalty/taxation regime. The topics will be illustrated through working through a generic model (trainer led) and then delegates will practice the concepts with a case study.
Modeling a Production Sharing Agreement
This section will allow delegates to learn how to evaluate and model PSA/PSC. The topics will be illustrated through working through a generic model (trainer led) and then delegates will practice the concepts with a case study.
Whole Company Financial Modeling for E&P Companies
This section will allow delegates to learn how to build a three-statement model for an E&P company. It will highlight the key value drivers and their inter-relationships
DCF and NAV Valuation
This section will allow delegates to learn how to build a DCF valuation model for an E&P company and cover the five key stages:
Key topics:
- Calculating FCF from EBIT
- Estimating WACC
- Estimating Terminal Value (Gordon growth vs. terminal EBITDAX multiple)
- Discounting to derive an enterprise
- Reconciling enterprise value to target share price
Excel, VBA and Specialist Financial Modeling
Excel Fundamentals for Sales and Trading
Delegates begin with basic keyboard drills, navigation shortcuts and menu commands, and then rapidly progress to ideal Excel settings and efficient formula construction. The main focus of the session will be on introducing output functions as a solution to efficient data retrieval, and linking Excel to Bloomberg.
Advanced Excel for Sales and Trading
The aim of this session is to build upon the Excel Fundamentals for Sales and Trading session by providing delegates with the advanced Excel knowledge needed to perform at maximum efficiency on the job.
Introduction to Macros
This module introduces Excel users to the features in creating and managing Excel macros. Delegates will learn how to record macro, where to store macros, execute macros in variety of different methods, and creating control buttons to activate the macros.
Introduction to VBA
Many advanced Excel users know how to use Macros to automate tasks by recording them using the Macro recorder, and running them at a later date. However more advanced automation requires using VBA. This module is designed to give a good working knowledge of Visual Basic for Applications (VBA). During this VBA for Excel Course delegates will learn to create and edit macros, and gain an understanding of VBA code.
Going further with VBA
Once Excel users have built up a working knowledge of the fundamentals of VBA to automate tasks, they can go further, using VBA to increase the functionality of Excel and improve its interaction with users. This module is designed to give an intermediate to advanced knowledge of Visual Basic for Applications (VBA). During this VBA for Excel Course delegates will create and edit increasingly complex code.
Excel Power Modeling
This program covers a range of tools and techniques in Excel designed to improve effectiveness and efficiency. The program covers building scenarios into models using a variety of functions, together with adding in on-sheet controls, sensitivity analyses and flexible output tables. The topics in this menu can be chosen according to delegates’ needs.
Modeling with Speed and Style
Key topics:
- Setting up toolbars so that Excel can be used as efficiently as possible
- Setting up formatting styles
- Custom number formats
Keeping Circularities Under Control
Key topics:
- Working with intentional circular references
- Debugging a model with non-intentional circular references
- Avoiding intentional circular references with a circular reference macro
Flexible Output Tables
Key topics:
- Use functions to extract information from a model
- VLOOKUP
- HLOOKUP
- OFFSET
- INDEX
- MATCH
- Data validation
- INDIRECT
Scenarios
Key topics:
- Building scenarios into the model
- CHOOSE
- OFFSET
- INDEX
- MATCH
- On sheet controls
- Conditional Formatting
Working with Text and Dates
Key topics:
- Creating dynamic headings and footnotes in the model
- Working with dates
- Concatenation
- TEXT function
- UPPER, LOWER, PROPER functions
- TRIM and CLEAN functions
- RIGHT, LEFT & MID functions
- DATE, DAY, MONTH, YEAR functions
Sensitivity analysis
Key topics:
- Run sensitivities on model outputs
- One-input data tables
- Two-input data tables
- Three-input data tables
- Data table secrets
Lists
Key topics:
- Creating lists
- Sorting
- Basic and advanced filtering
- SUMIF and COUNTIF
- Calculate with advanced criteria with database functions
- Pivot tables
Other Functionality
Key topics:
- Printing with views
- Print macro
- Print macro with a form for easy print selection
- User defined functions
- Beauty save macro
- Protecting your work
- Charts
- Arrays
- Range names
- Shortcuts