Home  |  About Us   |   Be Our Speaker  |  Terms & Conditions  |  Connect With Us
Executive Education
Clariden Leadership Institute
Advanced Modeling with Excel: VBA, Data Analysis and Advanced Excel Functions (USA)
Jon von der Heyden
Excel Evolution

This program has been independently accredited and certified by CPD
Jon von der Heyden, Excel Evolution
  • Leading financial modeler and trainer with more than 15 years of experience in financial modeling
  • Trusted advisor for multinational corporations across various industries, such as The Economist, AXA, BG Group, Hays, Dell, NATS, British Sky Broadcasting, Baird, Stanhope PLC, Canary Wharf and Greater London Authority
  • Respected member of the Excel community and recognized as an MVP (Most Valuable Poster) and moderator on MrExcel.com, one of the largest Excel online community with more than 350,000 members

 

Jon von der Heyden is a leading and renowned financial modeler and Excel expert with more than 15 years of experience. He has been an avid and respected member of the Excel community for over a decade where he contributed to tens of thousands of helpful forum and blog posts on Excel and financial modeling topics to help others learn. Due to his continuous commitment, Jon was recognized as one of the Most Valuable Poster (MVP) by MrExcel.com, one of the largest Excel online community with more than 350,000 members. 

Jon has been a full-time trainer and consultant since 2008 where he has conducted and facilitated trainings in the Europe, Africa, the Middle-East and Australia.  Companies that Jon consulted for and trained include The Economist, AXA, BG Group, Canary Wharf, Hays, Dell, British Sky Broadcasting, Baird, NATS, Stanhope PLC as well as the Greater London Authority. As a passionate and energetic speaker, John not only provide excellent technical learning, but also ensure that participants get the best value and enjoyment from his classes.
 
Prior to consulting, Jon worked for Siemens PLC where he took on an instrumental role in the standardization of a Global Shared Services product catalogue and pricing, and a strategic and controlling role in major synergy savings projects. In recognition for his outstanding contribution, Jon earned a number of awards for his service at the company. 

This course has been independently certified as conforming to universally accepted CPD guidelines. On average, this executive program contributes 24 hours towards your CPD

Program Summary

 

According to an SAP CFO research in 2015, majority of financial executives emphasize the importance of improving efficiency in financial planning is a key concern as 83% of them agree that reducing the amount of time and resources for data migration and manual reconciliation connected with financial planning and analysis would yield substantial financial benefits.


This intensive 2-day program is designed for finance leaders and managers to build practical VBA skills to augment your financial models to increase efficiency and productivity of your financial modeling. Led by Jon von der Heyden, a leading and renowned financial modeler and Excel expert with more than 15 years of experience, you will understand the role of VBA in financial modeling, analysis and reporting that enables you to use the most effective tools in Excel for your various business analysis needs. You will also build a solid foundation for further developing VBA skills through systematical learning of VBA basics and best practices.


Though hands-on practices and group discussions, you will learn to develop VBA projects for data manipulation, migration and other repetitive tasks that help to reduce time and effort spent on manual tasks and increase efficiency. Last but not least, you will learn to use VBA to mitigate some limitations of native Excel tools such as the scenario manager, so as to maximize the value of VBA to increase model productivity and ease of use. At the end of the program, you will walk away with a comprehensive set of VBA knowledge and the skills to improve your modeling accuracy, speed and functionality with VBA.



Programs, dates and locations are subject to change. In accordance with Clariden Global policy, we do not discriminate against any person on the basis of race, color, sex, religion, age, national or disability in admission to our programs.

Introduction

 

As many organizations focus on cost reduction to increase competitiveness, the finance functions is becoming a key focal point for such cost reduction activities. According to PWC Global Finance Benchmark Report in 2015, top-tier finance departments achieved 40% lower cost of finance as a percentage of revenue than their peers, through a combination of automation and more efficient use of resources. In addition, 83% of finance executives highlighted the need to reduce manual data migration and reconciliation in FP&A processes.


However, many FP&A professionals have limited understanding and experience in using VBA, the Excel in-built automation tool, to increase the speed, accuracy and functionality of the financial models.  Therefore, it is urgent for FP&A professionals and modelers to become VBA-proficient to achieve greater performance for the entire finance function.


Designed for Financial Planning and Analysis (FP&A) professionals, this intensive 2-day program helps to build practical VBA skills to augment financial models. Jon von der Heyden, a leading and renowned financial modeler and Excel expert with more than 15 years of experience, will elaborate to you on the role of VBA in financial modeling, analysis and reporting that enables you to use the most effective tools in Excel for your various business analysis needs. You will also build a solid foundation for further developing VBA skills through systematical learning of VBA basics and best practices.


Though hands-on practices and group discussions, you will learn to develop VBA projects for data manipulation, migration and other repetitive tasks to help reduce time and effort spent on manual tasks, hence increase efficiency. Last but not least, you will learn to use VBA to mitigate some limitations of native Excel tools such as the scenario manager, so as to maximize the value of VBA to increase model productivity and ease of use. At the end of the program, you will walk away with a comprehensive set of VBA knowledge and the skills to improve your model accuracy, speed and functionality with VBA.

What You Can Expect

 

  • Understand what is VBA, its difference from Macros, its benefits and limitations
  • Build a solid foundation for VBA, including variable definition, data types, codes for specific tasks as well as best practices in VBA programming
  • Learn to write simple VBA script that allows for replication and customized data manipulation
  • Effectively utilize native Excel scenario manager and VBA to overcome its limitations
  • Systematic review of VBA project to identify error and check for sound logic, formatting and ease of use
  • Explore additional native tools such as Power Query and Power BI that can enhance the efficiency of financial modeling

Who Will Benefit Most

 

This program is designed for Financial Planning and Analysis professionals with at least intermediate proficiency in Excel and a solid foundation of accounting, finance and financial modeling knowledge, including but not limited to: 

Chief Finance Officer
Head of Finance
Chief Business Analyst
Financial Controller
Senior Accounting Manager
Senior Financial Planning& Analysis Manager
Senior Financial Analyst
Financial Modelers
Senior Business Performance Manager
Senior Finance Manager
Business & Finance Analysts
Investment Managers and Analysts
Project Managers and Accountants
Investment & Corporate Bankers

Program Outline

 

Program Outline

 

Day One: Introduction to VBA Programming

 

Introduction

  • The role of VBA in Financial Modelling, Analysis and Reporting
  • What is VBA well suited for?
  • Discuss Financial Modelling fundamentals and principles
  • What is the future of VBA?  What is the future of Microsoft Excel?

 

Primer in Excel VBA – with Exercises

A chapter intended for those who are not familiar with Excel VBA and Macros:

  • An introduction to the Excel Object Model
  • The Excel Macro Recorder (Hello World! Exercise)
  • VBA programming concepts (best practises, naming conventions)
  • An introduction to the Visual Basics Editor (VBE)

 

The Visual Basics Language (Syntax, Code Elements)

  • Procedures, including subroutines, functions and event routines.
  • Scoping (modules, variables, procedures)
  • Variable declaration and data types
  • Code branching and code blocks

 

Advanced Routines using the Macro Recorder – with Exercises

  • Open and close Workbooks
  • Cut and Copy sheets
  • Cut, copy and append ranges (data)
  • Filtering and deleting ranges (data)
  • Optimise the Recorder Code

 

Day Two: Beyond the Macro Recorder

 

Advanced Logical Branching – with Exercises

  • If…Else…End If logical branches
  • If…ElseIf…Else…End If logical branches
  • If logical statements
  • Select Case…End Select logical branches
  • Use of labels

 

Use Loops for Repetitive Tasks

Learn different loop methods such as:

  • Do While/Until to loop through files in a directory folder
  • For Each…Next to loop through items in a collection, such as Workbooks, Worksheets and Ranges
  • For…Next to loop sequentially through a set of numbers

 

Handling Reference Objects – with Exercises

Handle the core Excel objects, and discover many related methods:

  • Workbooks (creating, opening, copying, saving, closing)
  • Worksheets (creating, cutting, copying)
  • Ranges (referencing, inserting, deleting, formatting, copying, filtering, sorting)
  • Tables (creating, referencing, deleting from and appending to)
  • Names (creating, referencing and evaluating)

 

Debugging and Error Handling – with Exercises

Learn how to write bulletproof code:

  • Identifying and avoiding syntactical errors
  • Debugging and handling Runtime errors
  • Debugging and handling Logical errors

A look at Alternatives – with Exercises

A study of lesser known Excel tools that can often be used as a more suitable alternative to VBA

  • Use of Query tools (especially Power tools) to automate data import and data transformation.
  • Scenario Manager, a powerful build-in feature that facilitates the set-up of scenario models, and comparison of different scenario outputs.
  • Implementation of Custom Views to facilitate multiple interfaces and reports.

 

Day Three – Complete a Project

 

User Defined Functions (UDF’s) – with Exercises

UDF’s work like other worksheet functions, but can be written to simplify otherwise complex calculations

  • UniqueValues: Produces a list of unique items for a range of values
  • GetElement: Gets a specific element from a delimited text string
  • GetNumber: Gets a number from an alpha-numeric text string

Consolidate data – with Exercises

Build a wizard that facilitates quick and easy data consolidation from multiple files in a directory folder

  • From data contained in sheets
  • For data contained in named ranges
  • From data contained in tables

 

Split Data – with Exercises

Build a wizard that facilitates quick data separation

  • Split multiple sheets into separate workbooks, for easy distribution
  • Unique data separation, to create separate reports for each division, department of other key element of a consolidated report.

Userforms and Interfaces – with Exercises

  • Present your project to users using user-friendly userforms and ActiveX controls
  • Build a menu interface using CommandBar controls
  • Add a right-click contextual menu

 

Packaging a VBA Project – with Exercises

  • How and when to use PERSONAL.xlsbm
  • Using a default workbook
  • Package your project using a distributable Addin
  • Protect your project

 

Getting Help

  • Recommended Forums and Blogs
  • Other online technical support references
  • Recommended books

 

Surgery

The group can bring any VBA related issues to the table for group discussion.  Participants are encouraged to showcase and past efforts, for best practise review.

CFOs Leadership :
Experience Clariden
Discover how our leadership program has shaped the perspectives of CFOs across Asia
 
Venue: The Marker San Francisco
Date: 17 - 19 April 2017
Faculty: Jon von der Heyden
Early Bird 1: US$ 2,685 by 7 February 2017
Early Bird 2: US$ 2,885 by 7 March 2017
Regular Fee: US$ 2,985
Group Discount: 2nd participant get 10%, or register 3 participants and 4th participant get a complimentary seat
(1 discount scheme applies)
Contact: pravind@claridenglobal.org
 
 
More Programs
Tax Efficient Transfer Pricing and Intra-group Financing (SG)
- Matthew Andrew
Improving Strategic Key Accounts and Client Relationship Management: Techniques to Managing Your Company's Most Important Strategic Customers...
- Colly Graham, FISMM, FSII
Optimizing Tax Structures with International Tax Planning, Compliance and Risk Management (SG)
- Matthew Andrew
Strengthening Australia's Public Sector Financial Management (Melbourne)
- Eugene O'Connell
Improving Strategic Key Accounts and Client Relationship Management: Techniques to Managing Your Company's Most Important Strategic Customers...
- Colly Graham, FISMM, FSII
View All Programs >
 
 
   
Home | About Us | Be Our Speaker | Press Release | Terms & Conditions | Connect With Us
Copyright © 2011 Clariden Global, all rights reserved.