The Banking Academy - Training Programmes

“A properly designed model enables a situation to be easily examined and its dimensions intricately manipulated, to facilitate optimal decision making.”

The objective of the Financial Modelling Program is to expose participants to modeling technology and tools using ‘advanced’ features of Excel and more importantly, to its programming counterpart, ‘Visual Basic for Applications’ (VBA), for building sophisticated and useful models in all business areas: Finance, Accounting, Budgeting, Banking, Infrastructure Projects, Public policy, Hedging, Engineering, Marketing, Sales, Human Resources and so on. The actual functional area is of no consequence since it is the responsibility of the modeller to supply functionality, whether it is Accounting, Option Pricing, Statistics, Econometrics, Engineering, Finance, Relationship Building and others. The training will be of greatest use to senior executives in Government, Banking, Markets and Exchanges, Investing, Infrastructure Building and Management, Healthcare, Oil and Gas, Shipping, Airlines amongst others.

Thus, regardless of functionality, every situation is comprised of variables and the challenges in building a model are:

a. To decompose a situation into constituent variables;

b. To understand the relationships amongst variables;

c. To map variables (and relationships) to a modeling ecosystem on a PC using Excel & VBA.

This program will focus on underlying technology that is at the heart of developing powerful and sophisticated models by exposing participants to relevant techniques and tools. Participants work extensively with Excel & VBA.

Features of this training programme
•	Each section of this comprehensive programme will be supported by individual and group exercises and case studies
•	Limited class size: Class size is limited to 30 delegates to ensure effective one-to-one interactivity

Key Learning Outcomes This programme is designed to achieve the following goals:
•	Learn to construct models that apply to all functional areas: fi nance, accounting, marketing, human resources, budgeting, planning, statistical studies, forecasting, biological and chemical trials, econometrics
•	Learn to build Securitization Models, Monte Carlo Analysis Models, Share Price Forecasting Models, Risk-Return Pricing Models, Consumer Finance Models, Budgeting Models, Vehicle Finance Models, and many more
•	Examine several World Class fi nancial Models and the code behind the Models
•	Learn to employ the IPO Framework in constructing Models
•	Learn to work with both Excel and User Forms as two distinct platforms for developing sophisticated financial models


•	Finance Directors / Financial Controllers / CFOs
•	Business / Financial / Treasury / Market Analysts
•	Corporate / Structured / Project Finance Offi cers
•	Financial / Strategic Planners
•	M&A Specialists
•	Stock Brokers / Researchers
•	Research Analysts / Economic Think-Tank
•	Project / Commercial Management
•	Business Development and Marketing
•	Systems Analysts
•	Budget Managers
•	Inventory Managers
•	Credit Risk / Treasury Managers
•	Certifi ed Public Accountants and Accounting Staff
•	SAP and Oracle Users

Programme Day 1 – An overview of modelling

Introduction to Financial Modelling

Basel III: Introduction, evolution and industry overview
• Defi nition of fi nancial modelling’
• The six distinct components in fi nancial modelling:
process, situation, variables, relationships, dimensions, decision-making
• What is not a fi nancial model?
• Advantages of the fi nancial modelling framework
• The IPO (Input, Processing and Output) framework
• Examples of variables and relationships
• Layout issues in building a fi nancial model
• Examples of types of models
•Examples of models in non-fi nance areas: Marketing,Human Resources.

Databases and Spreadsheets

• Difference between a database and a spreadsheet
• Role of a database in relation to a spreadsheet
• The need to intermingle in a solution
• Examples of production quality relational databases
• Other relational databases
• Programming language for manipulating databases
• The important role of excel as a fl at fi le database
• Excel architecture overview
• Databases wrap-up

Overview of Excel 2007

• New features
• The Ribbon
• Excel 2007 statistics

Accessing Excel

• Two ways to access the functionality of Excel: GUI and Code
• Overview of code
• Objects
• Collections
• Platforms for building models: Worksheet and User Form

Modelling Platforms

•Example of employing a Worksheet as a platform for a modelling situation
•Example of employing a User Form as a platform for a modelling situation

Requirements for Developing Financial Models

•Model building concepts: variables, relationships, inputs, processing, outputs, layout issues
•Technical skills in Excel: excel object hierarchy, object properties and methods, excel events, Visual Basic for Applications.

Advanced Excel - Part I

Overview of Advanced features of Excel and itssignifi cance in
Financial Modelling
• Arrays
• Functions
• Names
• Number formatting
• Data validation
• Excel Controls
• Report Manager add-in
• Data management
• What-if analysis
• Grabbing data from the Internet

Excel Arrays

• Examples of use of arrays to solve advanced problems
• Defi ning arrays
• Entering array constants
• Cells holding an array formula

Excel Functions

• Sources of Excel’s functionality
• Types of Excel functions: built-in, Analysis ToolPak, user-defi ned, add-ins
• 9 categories of built-in Excel functions9 categories of built-in Excel functions
• Functions: user defi ned
• Functions: add-ins
• Using a function and determining function parameters
• Function return value
• Creating a user defi ned function
• Examples of some important functions
• Using Edit / Go To / Special

Excel Naming Scheme

• Importance of names in Excel
• Naming cells, constants and formulas
• 3-D Names
• Naming benefi ts
• Names examples

Excel Formatting Scheme

• Formatting values
• Number formatting options
• Custom number format
• Examples of Custom Format
• Formatting codes
• Date and time formatting codes
• Formatting examples

Data Validation

• Basics
• Defaults
• Lists
• Custom
• Prompts

Programme Day 2 – Advanced Excel – Part II

Excel Controls

• Types of Excel controls
• Placing controls on a worksheet
• Linking controls to cells
• Attaching procedures to controls

Excel Data Management Features

• Data management features in Excel
• Five stages of data manipulation
• The “Three I’s” of sources of data: Imported, Inputted, Internet
• Data fi lter
• Operators for fi ltering
• Advanced filtering procedures
• “D” functions
• Data management examples

Excel Pivot Tables Feature

• What is a Pivot Table?
• Pivot table basics
• Pivot table examples

What-if Analysis

•Types of “What If?” Analysis in Excel: Data Tables, Goal Seek, Scenario Manager, Solver
• Customized “What if?” analysis
• Data Tables: one-variable and two-variable
• Goal Seek
• Solver
• Solver algorithms and examples
• Scenarios

Report Manager Add-In

• Functionality of Report Manager
• Examples

Grabbing Data from Internet

• Web queries
• Import commands
• Creating a new Web query

Learning To Program In Excel – Part 1

Introduction to Programming
• MS Offi ce programming architecture
• Entry points into Excel
• Graphical User Interface tools vs. code
• Using objects, properties and methods to manipulate Excel
• Examples of VBA Code
• Ways to execute code
• Built in modules vs. user modules
• Benefi ts of Code
• Executing Visual Basic code
• Manipulating Excel with objects, properties and methods

Excel Object Hierarchy

• Scope of object properties and methods
• Object collections
• Microsoft Excel object models
• Object methods

Excel Event Procedures

• Events supported by Excel
• Using Events to hook code

Excel Macro Recorder

• Excel macro recorder
• Hands-on programming of simple tasks in Excel

Writing Visual Basic Code

• Overview of Visual Basic code
• Parts of a Visual Basic procedure

Visual Basic Decision Structures

• Visual Basic decision Structures
• Visual Basic control Structures

Visual Basic Controls

• Visual Basic controls
• Visual Basic user forms
• Visual Basic toolbox
• Alternatives to forms Visual Basic Functions

Useful Objects

• A Closer look at the Range Object
• The Range Object: properties and methods
• The Worksheet Object: properties, methods and events

Visual Basic / Other

• Variables and Constants
• Arrays
• Operators

Programme Day 3 – Modelling Projects

Building on the exposure to Advanced Excel and VBA in prior sessions, participants proceed to examine, interactively, the architecture of several real-life models and the code and features behind these models:

Exploring Refinancing Options For A Cash Generating going concern:
A Securitization Scenario

This case study illustrates an example of an educational institute in an emerging market country, faced with the situation of either taking a loan from a bank or securitizing its fee income to finance a school building. Participants learn the value of building a Dashboard with relevant parameters to arrive at a decision as to the amount of fee income in future years to be securitized, the rate of interest to be paid to the bank, the number of years of fee income to be securitized and so on. This case clearly illustrates how VBA can transform an executive (or in this case, an accountant) from merely preparing projected financial statements of the scenario into a decision-making executive where all the relevant information is marshaled for an optimal decision.

Share Price Forecasting Model For A Hypothetical M&A Deal:
Developing a Dashboard for an investment decision

This case illustrates the very important concept of a ‘Black Box’ in preparing a financial model that isolates important decision making information and variables to assist users to perform useful tasks without attention being diverted by large volumes of data; essentially, an executive is faced with a situation wherein he spends a considerable amount of time in building a fantastic spreadsheet that clearly isolates the IPO components of model-building: Inputs (variables), Processing (revenue forecasting engine, double-entry of items, and a trial balance) and Output (financial statements such as P&L, Balance Sheet, Sources and Application of Funds, Ratio Analysis); while the executive would have tolled endlessly in preparing this elaborate model, it is useless from a decision-making perspective. To be a true decision maker, the executive needs to visualize his spreadsheet as an ‘engine’ which requires no human intervention and then go about building a dashboard with all variables on hand and interactivity built into the dashboard via Goal Seek. Once this is accomplished, this executive is ready to take his project forward as he is now transformed from an executive into a true decision-maker.

Monte Carlo Simulation

An executive in the present-world lives in a World full of uncertainty; thus, to carry out a Project Appraisal using conventional tools is unrealistic since there is no way that the assumptions used in the Project Appraisal will remain a mute spectator. In other words, the IRR and NPV are unrealistic – to be meaningful, these parameters need to be the outcome of variables that are subject to probabilities. Enter Monte Carlo Simulation using an Investment Scenario: this case uses a simple project to focus on subjecting one of the assumption in the Project Appraisal (the revenue assumption) to a range of outcomes and then calculating the NPV of the project – this provides greater insights into the sensitivity of a project to changes in underlying variables. A Monte Carlo simulation is carried out by using a VBA Loop - participants get a ring-side view of the technicalities involved. This simulation involves 10,000 iterations with each iteration producing a NPV that corresponds to a probability value attached to Revenue.

Review and Question and Answer Session

About your course directors

Viswanath is a US based consultant who advises international organisations, including, the World Bank, the International Finance Corporation, and the Inter-- American Development Bank. He and his specialist American and other colleagues have carried out a wide range of fi nancial planning assignments across industries in more than 24 countries on behalf of institutional and private sector clients.

Viswanath holds a Masters degree in International Finance from Columbia University, New York and was awarded the prestigious ‘Maurice Feldman’ scholarship for fi nancial writers by The New York Financial Writers Association.

He is a co--author of an authoritative text on Leasing “The Principles and Practice of Leasing” published from the U.K. in 1990. He has several published articles to his credit and is a regular contributor to the World Leasing Yearbook.

Viswanath is an offi cial Beta tester for Microsoft Offi ce and he has more than 14 years of experience in Financial Modeling and Microsoft Excel. He single--handedly developed ‘CapInvest’ a sophisticated fi nancial model that is used by fi nancial institutions to create a range of fi nancial and savings products.

This is one of the best designed diploma programmes to teach participants to build and use effective models to enable situations and scenarios to be easily examined and their dimensions intricately manipulated to facilitate optimal decision making. This, regardless of the industry or topic under scrutiny.

Register today by completing the registration form below or contact Mr Gerald Rubio tel: +65 6236 6514 or email grubio@theasianbanker.com for more information.