search
I would like to
SHARE
  • Overview
  • Objectives & Outline
  • Methodology
  • Participant Profile
  • Trainer
  • Overview
    PROGRAMME DETAILS
    DATE
    26 – 27 July 2017
    TIME
    9:00 am – 5:00 pm
    VENUE
    Asian Banking School
    SIDC CPE
    10
    CPE Points
    This 2-day programme focuses on enabling participants to develop powerful spreadsheets for advanced financial models. Built-in Excel functions as well as user defined functions will be included. The curriculum follows a very structured approach to financial modelling which will enable the participants to develop complex spreadsheets in a step-by-step manner.

    Participants will also learn how to use Excel & VBA for modelling complex real world financial structures. These include performing Monte Carlo simulation, creating dynamic goal seeking functions and user defined functions to price contingent claims.
    LEARNING LEVEL
    Advanced
    Programme Fee*
    AICB / Miba member
    MYR
    5,500
    / PAX
    Non-member
    MYR
    6,000
    / PAX
    *inclusive of GST
    Jointly with:
  • Objectives & Outline
    LEARNING OBJECTIVES
    Upon completion of the programme, participants will be able to:

    • Create practical financial models using a structured approach
    • Develop insights into built-in Excel functions and how to use them
    • Analyze market data using Excel
    • Perform sensitive analysis using Pivot Tables and What-Ifs
    • Build user defined functions using VBA
    • Apply mathematical and statistical Excel functions to financial modelling
    • Perform iterative operations in Excel using Goal Seek
    • Apply What-If analysis using Pivot Tables and Excel’s What If tool kit
    • Create visualization of data using charts

    PROGRAMME OUTLINE
    OVERVIEW : STRUCTURED APPROACH TO ADVANCED FINANCIAL MODELING

    • Step 1: Defining and Structuring the what the Model will do
    • Step 2: Defining the Input and Output Variables of the Model
    • Step 3: Deciding Who Will Use the Model and How Often
    • Step 4: Understanding the Financial and Mathematical Aspects of the Model
    • Step 5: Designing the Model
    • Step 6: Creating the Spreadsheets and/or Writing the VBA Codes
    • Step 7: Testing Model
    • Step 8: Protecting Model
    • Step 9: Documenting Model
    • Step 10: Updating Models


    BUILT-IN EXCEL FUNCTIONS FOR ADVANCED FINANCIAL MODELING:

    • COUNT, SUM and AVERAGE
    • COUNTIF, SUMIF
    • IF and Nested IF
    • STDEV and VAR
    • NORMSDIST
    • MAX and MIN
    • COVAR and CORREL
    • DATE
    • RAND
    • INTERCEPT
    • LINEST
    • TRANSPOSE
    • SLOPE
    • DURATION
    • MMULT
    • EXP and LOG
    • MINVERSE


    CELL REFERENCING

    • Absolute Cell Referencing
    • Relative Cell Referencing
    • Working with R1C1 Reference Style


    WORKING WITH FINANCIAL DATA

    • Getting Data from the Internet directly into Excel
    • VLOOKUP and HLOOKUP


    ITERATIVE and SENSITIVITY ANALYSIS

    • GOAL SEEK
    • PIVOT TABLES


    VISUALIZATION OF DATA AND ANALYSIS

    • Stock Charts
    • Dual Axis Charts


    SUMMARY : STATISTICAL DISTRIBUTIONS TO MARKET PRICE DATA

    • Normal and Log Normal Distribution
    • Binomial Distribution
    • Bernoulli Distribution
    • Poisson Distribution


    UNIVERSAL BOND PRICER AND ANALYZER IN EXCEL

    • Develop a Model for pricing bonds with bullet Principal repayment
    • Extend the model to include bonds with amortizing Principal repayment
    • Determine and Graph relationship between Bond Prices and the factors that influence bond prices
    • Create Risk Sensitivity measures like Macaulay Duration, Modified Duration and DV01
    • Computation of Bond Convexity


    MONTE CARLO SIMULATION IN EXCEL

    • Understand Brownian motion and its application to Financial Markets
    • Create a single path of a stock’s stochastic price process
    • Create multiple stock price processes
    • Use the stock price paths to value options


    MODEL TO COMPUTE A STOCK’s ALPHA AND BETA FROM MARKET DATA

    • Apply Excel to the Capital Asset Pricing Model (CAPM)
    • Download Stock Index and individual stock price data to model CAPM
    • Compute the Alpha and Beta of individual stocks
    • Plot scatter graphs of Index Returns and Stock Returns
    • Draw regression lines to compute stock’s Alpha and Beta


    PRICING OPTIONS USING A BINOMIAL TREE IN EXCEL & VBA

    • Set up the parameters of the Cox-Ross-Rubinstein (CRR) Binomial Option Pricing model
    • Create a binomial Stock Price Tree
    • Evaluate the Option Value for each final node of the binomial tree on the Expiry Date
    • Use Backward Induction to value the Option on Transaction Date
  • Methodology
    Pre-Requisites: All participants must come with a laptop, which has Excel and internet access as they will have to download data from the internet to build models and perform market analysis.

    Very hands-on, instructor-led classroom training using Excel and VBA to develop sophisticated financial models
  • Participant Profile
    • Corporate Finance Professionals;
    • Business Analysts, Business Development / Planning Executives;
    • Banking Sector: Professionals from Corporate Banking and Credit Department;
    • Investment Bankers and other Financial Intermediaries; and
    • M&A Consultants, Chartered Accountants and Legal Experts

  • Trainer
    Rajat Bhatia
    Mr Rajat Bhatia is an international investment banker with 27+ years of global experience with the world’s top tier investment banks. He has worked for Lehman Brothers, London, Merrill Lynch Capital Markets, Hong Kong, Citigroup Global Asset Management, London, Citigroup India, Williams Energy in the USA and Booz Allen & Hamilton, Sydney. Mr Bhatia is the Founder & CEO of Neural Capital, a boutique firm that he started in the United States in 2006. Neural Capital specializes in developing quantitative trading strategies for the global financial markets and advising early stage and start-up companies on raising capital from venture capitalists and private equity firms. He is also the Dean of the School of Financial Engineering at IIQF and the Chairman of the Board of Advisors of Invenzo Labs.
    He has taught courses on Financial Modeling, Corporate Valuation, Derivatives, Portfolio Management, Alternative Investments, Equity Markets, Fixed Income Markets, Swaps and Commodity Markets at global financial institutions and business schools in New York, India, Singapore and London.
REGISTER now

Please complete the following form to register with us. Alternatively, you can email us here or call us at +603 2742 7822 if you have any questions.

payment process
1
Please download the Payment Form for our Banking details and if making company sponsored payments
2
Upload your Payment Form/Receipt and submit with your online registration
3
You will receive a confirmation email upon successful registration
I'm Applying For
Programme Details
Select Programme
Please select a time slot.
FULL NAME
(as per NRIC/Passport) *
New NRIC No/Passport No*
Nationality*
Email*
Contact No*
Job Title/ Designation*
Department*
Company Name*
Mailing Address*
City*
Postcode*
Country*
AICB Membership No
(If Applicable)
Special Remarks
(IF APPLICABLE, PLEASE FILL IN YOUR FMAM (PPKM) MEMBERSHIP NUMBER HERE)
Upload Payment Receipt or Form*
*Required fields
Captcha validation failed.
Thank you for your submission. You should receive an automated email shortly at the email address provided if your submission had gone through successfully. If you do not receive the email, please contact us at +603-2742 7822 or email training@asianbankingschool.com
There are currently no upcoming Programme slots.
Please email us here or call us at +603 2742 7822 if you want to check on future slots.
Copyright © Asian Banking School (ABS). All rights reserved.
CONNECT WITH US
Ooops!
Generic Popup