Banks

Introduction to Banks

Through this session, the delegates build a core understanding of the key value drivers and performance indicators for a bank.  The financial statements of a bank are examined and analyzed to understand the major line items of the balance sheet and income statement.  Regulatory capital requirements are also introduced.

 

Financial Statement Analysis for Banks

This session begins with an overview and recap of bank's financial statements. After understanding the basics, the principles of both accounting for financial instruments including loans and derivatives are explained. At each point the differences between US GAAP and IFRS are considered.

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.

 

Contact Us

Oil and Gas

Industry Overview

This section will provide a brief introduction to the oil & gas industry

 

Key topics:

  • Oil & gas production and consumption growth and profile
  • Global reserves
  • Key players: NOCs, IOCs, Independents and Oil Field Service companies
  • Value chain of one barrel of crude oil (E&P, refining, retail distribution & taxes)
  • Stages in the upstream: Exploration, Appraisal, Development, Production, Abandonment
  • Volumes and conversion factors
  • Price benchmarks (Brent vs. WTI)

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

 

Contact Us

 

Excel, VBA and Specialist Financial Modeling

Excel Fundamentals

During this session, delegates are introduced to Excel keyboard shortcuts, efficient worksheet navigation, formula construction and financial functions. The session includes lots of practical, real life Excel based exercises.

 

 

Key topics:

  • Keyboard shortcuts
  • Building a simple budget
  • Formulas
  • Financial function

 

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

 

Contact Us

Learn more about our public courses

Check out the latest public course dates in Americas, APAC and EMEA, or contact us to discuss your unique in-house training needs.