Bank Forecasting & Valuation Modeling – Building Robust Financial Models

The overall goal of this course is to provide participants with the skills to produce a bank-forecasting model and run scenario analysis. After the program, it is expected that delegates would have improved insights into planning and designing bank valuation models, extracting and normalizing financial data, modeling detailed balance sheets and income statements, and integrating scenario flexibility with KPI’s and equity analysis within regulatory capital requirements.

Venues & Dates

Dublin: TBD
London: TBD
Dubai: TBD
Edmonton (CAN):
Lagos/Abuja:

Cost: $4,000 per participant (USD) (=N=)

For Whom

Equity analysts
Financial investors
M&A and finance professionals working on model building and analyzing banks

Modules

Analytic Overview

Parameters for building and running a flexible, robust financial model
Key steps to setting up a model: good and bad practice
Model structure: inputs, workings, outputs
Scope limitations: output requirements vs input sources
Using group edit tools for consistency and efficiency
Input sheets: assumptions and forecasts
Model plan and workbook setup
Inputting base data
Setting up date flexibility with functions

Building Financials

Selecting key lines for financial statements
Entering and normalizing historic data
Reviewing model structure
Reconciling financials for robust forecasting foundations

Building the Balance Sheet

Loan portfolio establishment and growth drivers
Non-performing loans and provisions
Trading assets & investments
Derivatives under accounting treatments
Securities held for trading vs investment
Trading liabilities: derivatives vs short sold securities
Cash, central bank balances, and liquid assets
Deposits and growth drivers
Wholesale funding: money vs capital markets
Subordinated debt and hybrid capital
Equity and regulatory capital requirements
Reconciling the balance sheet

Deriving the Income Statement

Using balance sheet outputs to build income statements
Net interest margin and rate scenarios
Hedging and margin setting for assets & liabilities
Fee & commission income drivers
Trading income modeling and volatility
Credit losses: provisioning, write-offs, recoveries
Cost drivers and forecasts
Other items: tax rates, dividends, extraordinary items
Linking income statement to balance sheet and retained earnings

Key Ratios & KPI’s

Asset quality: NPL ratio, coverage, provisioning ratios
Liquidity: risk assets, liquid assets, cross-balance sheet ratios
Capital: BIS ratios, leverage, capital formation
Profitability: net interest margin, ROA, ROE, efficiency ratios
Graphical representation of KPIs and outputs

Bank Valuation & Cost of Equity

Equity valuation: multiples vs cash flow
Price-to-book multiples, DDM, FCFE, terminal value
Sanity checks on valuations
CAPM inputs: risk-free rates, market risk premiums, betas
Internal regression and quality controls
Checks and error flags in financial models
Charts as quality controls

Scenario Analysis & Stress Testing

Testing model inputs with sensitivity analysis
1D and 2D data tables, self-centering data tables
Building a scenario manager
Switching scenarios with Excel functions (CHOOSE, INDEX, MATCH, etc.)
Drop-down menus and VBA tools
Adding dashboards, text summaries, and graphs
Benchmarking forecasts vs consensus

Model Debugging Skills

Building diagnostics and balance sheet checks
Using ratios to debug
Auditing skills and watch windows
Link elimination and circularity issues
Avoiding unnecessary macros
Using F5 special functionality