Advanced Modeling with Excel: VBA, Data Analysis and Advanced Excel Functions (USA)
Jon von der Heyden
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
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.
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
Day One: Introduction to VBA Programming
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:
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
Recommended Forums and Blogs
Other online technical support references
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)