Excel Macros and VBA for Financial Reporting and Business Analysis (Melbourne)

Marcus Small
TheSmallman.com

Marcus Small, TheSmallman.com

Experienced financial modeler and VBA developer who has more than 20 years of experience and developed his website, Thesmallman.com to be awarded for the Top 30 Excel Blogs on the web

CPA Australia’s preferred Excel trainer with Microsoft Office Specialist Excel Certification and presented for both CPA Australia and ACCA for over 100 days of Excel, VBA and the groups of Microsoft power tools training

Regular speaker, seasonal trainer and moderator of Ozgrid and Chandoo Excel Forum whom is amongst the web’s largest Excel forum

Marcus Small is a financial modeller and VBA developer with an ANU Masters degree in Finance. He lived in the UK for about a decade working at the Big 4 for years, then in banking industry for the last few years, before returning to Australia. Marcus has a training business which has blossomed and he is the preferred Excel trainer for CPA Australia. This is a huge undertaking and he is on the road more than he is home at times – he goes on road shows and conduct face to face training on all things Excel, VBA and the Microsoft suite of Power BI tools.

The Excel space is very competitive and RankedBlogs.com named this site in the top 10 Excel blogs in the world. He is humbled and ever so proud of his website. In the past he has moderated both Ozgrid and Chandoo Excel forums. It was a period when he was time rich and helped thousands of individuals through online forums. Now, he focuses his attention on training accountants on how to use Excel better through financial modelling, dashboard design, VBA and the effective use of the power suite of Microsoft Excel tools.

Program Summary

This intensive 2-day program is designed for finance leaders and managers to use VBA and Excel macro skills to automate your financial reporting and business analysis. Led by Marcus Small, CPA Australia’s preferred Excel trainer, you will learn how to write Excel macros in VBA to automate multiple financial tasks including:

Forecasting and Budgeting

Analyzing Business Data

Automating and Create Advanced Financial Reports and Charts with Macros

Use Advanced Excel tools to run Sophisticated Data and Business Analysis

Automate Repetitive Tasks With VBA and Macros

Transition from Excel Basics to Sophisticated Analytics

By the end of this course, you will have a wealth of practical information in running a what-if analysis at a programming level as well as the most important reporting and looping techniques with VBA that you can incorporate into a practical application. By understanding all these functions, you will be able to create a well written, easy to read code which is seamless to manipulate and implement in your organization.

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

According to SAP CFO Research, the majority of financial executives emphasize that the importance of improving efficiency in financial planning is a key concern as 83% 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. VBA is a key solution to such automation where it is the programming language of Excel and a fast programming language. Once an individual could grasped hold of such skill, he/she can automate almost every functions that is required in the finance and accounting space.

Led by our experienced financial modeler and VBA developer, Mr. Marcus Small, who has developed one of the top 30 Excel blog, this interactive session will provide you with a comprehensive guide on how to layout and structure VBA with a key deliverable being best practice modelling for finance analyst and management accountants. You will be taught to uncover skills in leveraging VBA effectively to import source data, export output reports, consolidate financial data and create interactive dashboard sensitivities.

You will also gain practical insights on creating many common Excel tasks with VBA which can help you save time in production of reports. Besides that, you will be guided by Marcus to explore the practicalities of generating your customized coding from scratch to manipulate and manage your data set.

By the end of this course, you will have a wealth of practical information in running a what-if analysis at a programming level as well as the most important looping techniques with VBA that you can incorporate into a practical application. By understanding all these functions, you will be able to create a well written, easy to read code which is seamless to manipulate and implement in your organization.

What You Can Expect

By the end of this program, participants will be able to:

Gain insights on what makes up VBA best practice, how to generate VBA code in a practical and repeatable way

Discover how to refer to ranges and worksheets so changing sheet names will not affect the smooth operation of processes

Generate common Excel tasks with VBA which can save hours or days in the production of reports

Explore the practicalities of creating your very own custom coding and generate everything from scratch to manipulate and manage data sets

Who Will Benefit Most

This program is designed for middle to senior level finance and accounting professionals, including but not limited to:

Chief Finance Officer

EVP / SVP / VP of Finance

Chief Business Analyst

Global / Regional / Financial Controller

Senior Accounting Manager / Accountants

Senior Financial Planning & Analysis Manager

Senior Financial Analyst / Financial Modelers

Senior Business Performance Manager / Analyst

Senior Finance Manager

Business & Finance Analysts

Investment Managers / Analysts

Project Managers

Project Accountants

Investment & Corporate Bankers

Program Outline

Day 1 | 08:30 – 18:00

Session 1:

Understand the theory behind VBA, what is VBA used for and how can it make your life easier, what is the object hierarchy and how does it work, what are variables and why are they so important when it comes to streamlining your coding.

An introduction to the Visual Basic Editor – how it works and the tools at your disposal. In relation to recording Excel macros, how is it done and how can it help you formulate code by yourself? How to turn rigid recorded code into flexible and reusable processes

Session 2:

The session will focus on practical everyday examples where VBA can be used to streamline tasks in an efficient and repeatable manner even as the source data changes.

The three methods for referring to ranges in VBA and what are the advantages of each.

Recording code to copying data from one range to another on the same sheet.

Changing the recorded code to copy data from one sheet to another ensuring the code is flexible enough to handle a sheet name change.

Documenting your code so others can read and understand what is attempted.

Understanding break points and stepping through VBA one line at a time.

Working with common Excel variables, text, integers, ranges, worksheets.

Methods for generating dynamic ranges and generating these ranges based on both rows and on columns.

Session 3:

The session will focus on special cells and the autofilter, there are many different variants of both and the two tools can be used to streamline VBA coding.

Sorting data in ascending and descending order, sorting data based on multiple criteria.

The various methods of using special cells in VBA to change the nature of datasets.

Using the autofilter with VBA to isolate and manipulate data.

Exploring the autofilter to change datasets based on date, based on cell colour, based on multiple criteria. The autofilter will allow data to be manipulated with speed and ease.

Session 4:

The session will focus on using functions to generate summaries, finding and replacing data and the introduction of looping constructs to manipulate data a specified number of times.

Using Excel worksheet functions inside VBA, SUM, VLOOKUP and COUNT.

Replacing data in an entire sheet in a smooth seamless coding trick.

Using the advanced filter to add criteria to isolate data within a report. This little used tool is highly powerful and can generate advanced reports with little coding.

Looping constructs – how they work and what options do you have when creating a customized loop in VBA.

Day 2 | 08:30 – 18:00

Session 1:

The session will focus on the creation of a custom function and using goal seek with a looping construct to generate financial targets.

Generating a custom function inside VBA to work with the hyperlink formula to help update an Excel dashboard.

Creation of goal seek reporting VBA loop which calculates the correct profit margin based on a set of parameters.

Looping through sheets to consolidate dynamic ranges into a summary report.

Session 2:

The session will focus on looping regimes, looping through ranges and worksheets to generate financial reports in a matter of moments. The process has the potential to save a great deal of time.

Looping through external workbooks to consolidate reports.

Looping through cost centres to create unique stand-alone dashboards saved to directory for each cost centre, from 10s of reports to hundreds of reports in seconds.

Creation of slicer report which changes based on selection with unique VBA technique.

Session 3:

The session will focus on charting in a VBA context and the acute focus on change events and how they can help run process when a specified item changes.

Charting with VBA to create both cost centre level charts and consolidation charts.

Working with change events, cell change method, and sheet change method to aid in running code.

How to incorporate the IF statement in VBA, to give your coding flexibility where multiple conditions are possible.

Session 4:

The session will focus on the VBA array, pivot table updates based on changing the dataset and creating PDF reports from an Excel file.

Working with arrays in VBA – what are they how can they be used to manipulate and manage data.

Generating VBA code to control a pivot table, update the pivot table as new data is entered to the source sheet with a worksheet active event.

Turning Excel workbooks into PDF reports and saving them to a specified directory with a single line of code.

Summarizing what has been covered over the two days – from theory to the practicalities of writing clear concise coding that is well documented.

CFOs Leadership :

Experience Clariden Discover how our leadership program has shaped the perspectives of CFOs across Asia