Advanced Financial Modelling

Course Outline:

• Definition of financial modeling ·
• The 6 distinct components in Financial Modelling:
  1. Process
  2. Situation
  3. Variables
  4. Relationships
  5. Dimensions
  6. Decision-Making
• What is not a Financial Model
• Advantages of the Financial Modeling Framework
• The IPO Framework (Inputs, Processing, Outputs)
• Examples of Variables and Relationships
• Layout issues in building a financial model
• Examples of Types of Models
• Examples of Models in non-finance areas: Marketing, Human Resources.

• Difference between a Database and a Spreadsheet
• Role of a Database vis-à-vis 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 Flat File Database
• Excel Architecture Overview
• Databases wrap-up

• New Features
• The Ribbon
• Excel 2007 Statistics

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

• 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

• 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.

• Arrays
• Functions
• Names
• Number Formatting
• Data validation
• Excel Controls
• Report Manager Addin
• Data management
• What-if analysis
• Grabbing Data from the Internet

• Examples of use of Arrays to solve Advanced Problems
• Defining arrays
• Entering array constants
• Cells holding an array formula

• Sources of Excel's functionality
• Types of Excel functions: built-in, analysis ToolPak, user-defined, add-ins
• 9 categories of built-in Excel functions
• Functions: User Defined
• Functions: Add-ins
• Using a function and determining function parameters
• Function return value
• Creating a user defined function
• Examples of some important functions
• Using the Edit/Go To / Special

• Importance of Names in Excel
• Naming Cells
• Naming Constants
• Naming Formulas
• 3-D Names
• Naming Benefits
• Names Examples

• Formatting values
• Number Formatting Options
• Custom Number Format
• Examples of Custom Format
• Formatting Codes
• Date & Time Formatting Codes
• Formatting Examples

• Data Validation / Basics
• Data Validation / Defaults
• Data Validation / Lists
• Data Validation / Custom
• Data Validation / Prompts

• Types of Excel Controls
• Placing Controls on a Worksheet
• Linking Controls to Cells
• Attaching Procedures to Controls

• Data management features in Excel
• Five Stages of Data Manipulation
• The 3 I's of Sources of Data: Imported, Inputted, Internet
• Data Filter
• Operators for filtering
• Advanced Filtering Procedures
• D Functions
• Data Management Examples

• What is a Pivot Table?
• Pivot table Basics
• Pivot Table Examples

• Types of What If Analysis in Excel: Data Tables, Goal Seek, Scenario Manager, Solver
• Customized What if Analysis
• Data Table / One Variable
• Data Table / Two Variables
• Goal Seek
• Solver
• Solver Algorithms and Examples
• Scenarios

• Functionality of Report Manager
• Examples

• Web Queries
• Import Commands
• Creating a new Web Query

• MS OFFICE 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
• Benefits of Code
• Executing Visual Basic Code
• Manipulating Excel with objects, properties and methods

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

• Events supported by Excel
• Using Events to hook code

• Excel Macro Recorder
• Hands-On Programming of simple tasks in Excel

• Overview of Visual Basic Code
• Parts of a VB Procedure

• Visual Basic Decision Structures
• Visual Basic Control Structures

• Visual Basic Controls
• Visual Basic User Forms
• Visual Basic Toolbox
• Alternatives to Forms

• 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
  • VB: Variables and Constants
  • VB Arrays
  • VB Operators

Day 4
Modeling 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 models:

• Securitization Model
  Illustrating an example of a educational institute in India, faced with the situation of either taking a loan from a bank or securitizing its fee income, to finance a school building ·
• Share Price Forecasting Model
  Illustrating the 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 ·
• Monte Carlo Simulation
  An Investment Scenario: Illustrating the development of a Monte Carlo Simulation Model to carry out an analysis of a Project Rate of Return
• Simulating a HP Financial Calculator
  This model simulates the financial functions in a HP Financial Calculator using Visual Basic Code to illustrate the fact that Excel's built-in financial functions such as Present Value, PMT and the others alone cannot build the simulator - these functions can generate an answer given information on the remaining 4 variables but auto-calculation of any of the functions given info on the remaining variables is ruled out because a cell can be used for entering either a formula or a constant (but not both, as required by the simulator).
• Risk-Return Pricing Model
  Illustrating the use of a financial model that assists financial institutions in developing pricing for loans, leases and hire purchases
• Vehicle Financing Model
  Illustrating the development of an "error-resistant' model to price vehicle financing transactions using the five discounted cash flow components
• Project Appraisal Model of a Leasing Investment in Ukraine
  Provides insights into a sophisticated Project Appraisal Model, LeaseEx, that assists financial institutions in forecasting leasing operations of a start-up leasing company over a 10 year horizon, constantly updating more than 180 reports in response to changes to any of the more than 2000 variables that constitute the eco-system of this model