Financial Modeling with Excel VBA (CEF Approved)

CEF Course Code: 23C03605-1
CEF Institution Code: 548

Hands-on computer skills training for finance and business professionals!

Course Description
Application Procedure
Schedules/Enrol

Continuing Education Fund
The course has been included in the list of reimbursable CEF courses
CEF Courses Home

Course Objective

Financial modeling is an essential skill for finance professionals and students. Excel VBA (Visual Basic for Application, the embedded programming language in Excel) allows you to create powerful spreadsheet models by overcoming the limitations of Excel and automating spreadsheet procedures . In this course, you will learn the fundamentals of Excel VBA and to apply it to developing financial models for various purposes. Upon completion of the course, you should be able to:

  • Understand Excel VBA and its application in finance
  • Design simple spreadsheet models for financial data analysis using Excel VBA

Course Duration

39 Hours (13 lessons of 3 hours each)

Course Prerequisites

Applicants should:

  • be at least 18 years old;
  • have a good command in using simple worksheet functions such as SUM, MAX , MIN, VLOOKUP and especially the IF functions, and conversant in writing worksheet formulae for a given problem.
  • have a basic knowledge of finance.

Prior programming experience is not required.

Course Content

The programme consists of the following 2 modules:

Part I: Excel VBA Fundamentals

Participants will learn the fundamentals of Excel VBA and prepare themselves to create simple financial models in Part II. Small programs will be used to illustrate the underlying programming concepts.

  1. The VBA programming environment
  2. Variables and constants
  3. Macros
  4. Built-in VBA functions and operators
  5. User-defined functions
  6. Branching and looping
  7. Object-based concept: Objects, methods and properties
  8. The Range object
  9. Debugging
    The following additional topics may also be covered if time allows:
  10. Arrays
  11. Procedures
  12. Custom dialog boxes
  13. Workbook and worksheet events
  14. Chart objects

Part II: Financial Modeling

Participants will apply VBA programming concepts and skills to build simple yet real-world financial models. The techniques of writing good models will also be discussed. As the scope of financial modeling is huge, topics have to be selective. The following shows a list of possible topics. Actual coverage depends somewhat on the finance background of the class.

  1. Loan amortization
    a. Simple model
    b. Iteration: The bisection approach
    c. Allowance for changing interest rates or repayment period
  2. Financial planning
    a. Retirement planning
    b. Portfolio restructuring
  3. Market history analysis
    a. Normal and real growth of stocks
    b. Growth of investment in different assets
  4. Stock simulation
    a. Stock volatility estimation
    b. The geometric Brownian motion and the log-normal distribution
    c. Stock price simulation
  5. Option pricing
    a. Different option strategies
    b. The Black-Scholes option pricing model
    c. Implied volatility estimation
    d. Options portfolio
    e. The binomial models

Title of Award and Assessment Methods

There are 2 course assignments. Upon passing the assignments and meeting the requirement of 80% attendance, students will receive a certificate for the program.

Course Fee

$6,900 ($1,380 for eligible applicants after maximum 80% course fee reimbursement from CEF upon successful completion of the course)

Course Delivery Language

Cantonese-taught and English-taught courses are available. Course material is only in English.

Schedule/Enrol

(A) Evening classes, 6 hours per week, course fee in 3 instalments
Course Title Duration Fee Outline/Schedules/Enrol
Financial Modeling with Excel VBA (CEF Approved) 39 hours $2300 Outline Schedules/Enrol
$2300 x 3 monthly instalments (total $6900, $1380 after 80% CEF reimbursement), 39 hours in 13 sessions, 2 sessions per week

(B) Evening classes, 6 hours per week, course fee in 2 instalments
Course Title Duration Fee Outline/Schedules/Enrol
Financial Modeling with Excel VBA (CEF Approved) 39 hours $3450 Outline Schedules/Enrol
$3450 x 2 monthly instalments (total $6900, $1380 after 80% CEF reimbursement), 39 hours in 13 sessions, 2 sessions per week

Application Procedure

  1. Proceed with on-line registration or send the completed enrolment form (download) to us together with a cheque (made payable to Welkin Compuer Training) for the program fee.

  2. CEF applicants have to complete the CEF Application Form (download) and send it to us for certification. They will then send the form to the Office of the Continuing Education Fund, Room 916, 9/F, Kwai Hing Government Offices, 166-174 Hing Fong Road, Kwai Chung, New Territories, together with a photocopy of their HKID Card before course commencement.

  3. Applicants will receive application results from the Office of the CEF within 14 working days.

 

© Welkin Systems Limited. All rights reserved.