search

I WOULD LIKE TO

AF
Advanced Financial Modelling Using Excel & VBA
SHARE
Advanced Financial Modelling Using Excel & VBA
  • Overview
  • Objectives & Outline
  • Methodology
  • Participant Profile
  • Trainer
  • Overview
    PROGRAMME DETAILS

    DATE

    4 – 5 June 2018

    TIME

    9:00 AM – 5:00 PM

    VENUE

    Asian Banking School

    SIDC CPE

    10

    CPE Points
    This 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 in the course. The curriculum follows a very structured approach to financial modeling - which will enable the participants to develop complex spreadsheets in a step-by-step manner.

    Participants will learn how to use Excel & VBA for modelling complex real-world financial structures. These include performing Monte Carlo simulation, creating dynamic goal seek and user defined functions to price contingent claims.

    By working on a number of intensive, hands-on exercises in Excel & VBA, participants will work through the theory and application of valuation and financial modelling. After learning these Excel / VBA techniques, they will then apply them to a real-world context. By downloading data from the internet into Excel worksheets, participants will create cash flow forecasts, price fixed income instruments and analyse risks. They will also perform efficient frontier analysis for asset portfolios, write option pricing models, analyze fund performance, and compute alphas and betas for stocks and funds. A variety of financial models and Excel tools for financial analysis will be developed by the participants during this workshop which will give them the skills and techniques necessary to perform complex analysis using Excel and VBA.
    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:
    • How 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 modeling 
    • Perform iterative operations in Excel using Goal Seek 
    • Apply What-If analysis using Pivot Tables and Excel’s What If tool kit 
    • Visualization of Data using Charts
    PROGRAMME OUTLINE
    OVERVIEW : STRUCTURED APPROACH TO ADVANCED FINANCIAL MODELING
    • Step 1: Defining and Structuring 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 
    The coverage of functions listed above is dependent on how fast the class progresses

    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
    Instructor led facilitation in a workshop together with various industry case studies, simulation (where appropriate), current business analysis with interactive exercises and group discussions. Excel based discussions will be included as well.

    Requirements : 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.
  • Participant Profile
    Investment bankers, corporate finance executives, analysts, financial advisory professionals and any professional who wishes to gain a comprehensive understanding of this topic
  • Trainer

    Rajat Bhatia

    Rajat Bhatia is a veteran of the global financial markets with nearly 30 years of very broad based experience in Investment Banking, Corporate Finance, Derivative Products, Risk Management, Proprietary Trading, Alternative Investments, Hedge Funds and Actuarial Sciences.

    He is an Intuition Learning Consultant as well as Founder and CEO of a boutique investment firm that specialises in developing quantitative trading strategies for the global financial markets and in raising venture capital and private equity for early stage and growth stage firms. He is also the Dean of the Indian Institute of Quantitative Finance and the Chairman of the Board of Advisors of Invenzo Labs. During his professional banking career, Rajat worked with many international banks and consulting firms including Citibank Alternative Investment Strategies, London; Lehman Brothers International, London; Merrill Lynch Capital Markets, Hong Kong; Booz Allen Hamilton, Sydney; and Citicorp Investment Bank, India.

    He has a wide range of experience in the global financial markets that includes proprietary foreign exchange trading, hedge funds, Collateralized Debt Obligations, debt capital markets, quantitative financial modelling, neural networks based trading systems, statistical arbitrage strategies, derivatives structuring & marketing, corporate finance, M&A and raising capital from venture capital and private equity funds. In the credit space, Rajat has extensive experience in credit derivatives, credit enhancement structures, analysis of credit portfolios, portfolio assessment, portfolio composition, classification of credits and their definitions, portfolio rating factors, and credit portfolio ratings. He was part of the team that pioneered the development of credit derivatives in London and structured credit hedges for major Japanese banks in Tokyo and in Singapore. He has also worked on the securitization of insurance products via the structuring and placement of Catastrophe Bonds or “Cat Bonds”.
    Rajat has published papers in the Hedge Fund Journal, London, the Financial Express and the Economic Times, and has taught courses on Hedge Fund Strategies, Derivatives and the Bourse Game in the Middle East and in Singapore.

    He earned a Master’s degree in International Banking and Finance from Columbia University, New York where he was a Dean’s Fellow at the School of International Affairs from 1991 to 1992. He also earned an MBA with a major in Finance & Information Systems from the Indian Institute of Management, Ahmedabad (1985-87) and a first-class honours degree in Economics from St Stephen’s College, Delhi. Rajat is based in Goa, India and travels extensively to conduct seminars and speak at conferences.

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