Financial modelling using VBA 2018 - Workshop

This course is designed to take the accountant or analyst to the next level as the world of VBA opens up to streamline repetitive report production. It will cover using VBA effectively to import source data, export output reports, consolidate financial data and create interactive dashboard sensitivities. The course will focus closely on VBA best practice and will be about creating well written, easy to read code, which is seamless to manipulate and maintain.

You will first learn what is VBA best practice and how to refer to ranges and worksheets so changing sheet names will not affect the smooth operation of processes. You will create consolidated and export procedures which will save hours or days in the production of reports. The practicalities of creating your very own custom function will be explored and you will generate your own custom function which will be used as the sensitivity for a dashboard report. The vagaries of looping with VBA will be explained and simplified as the two most important looping techniques are incorporated into practical applications.

Key topics:

• Practical guidance on how to layout and structure VBA with a key deliverable being best practice modelling for analysts and management accountants
• Proper consolidation techniques for importing large data sets from multiple sources and hand these reports to non VBA users to maintain with a minimum of fuss
• The creation of custom functions which will be used with traditional Excel formula to add sensitivity to dashboard reports
• The creation of dynamic VBA code which will account for and incorporate datasets of any length
• A wealth of practical information on the design and creation of VBA from scratch without the use of the recorder
• Working with scenarios to loop and goal seek information so reports are updated instantly

Learning objectives

• Create dynamic VBA with best practice sheet referencing and range referencing
• Run code at worksheet level VBA when worksheet is activated
• Run code at cell level VBA when a cell or data validation is changed
• Loop through sheets to consolidate dynamic ranges
• Loop through external workbooks to consolidate cost centre reports
• Loop through external workbooks and extracting only specific cost centres
• Loop through cost centres to create unique stand-alone dashboards saved to directory for each cost centre, from tens of reports to hundreds of reports in seconds
• Create a slicer report which changes based on selection with unique VBA technique
• Chart with VBA to create both cost centre level charts and consolidation charts
• Creation of goal seek reporting VBA loop which calculates a price based on a set of parameters

Click on the here for Marcus Small’s biography.

Audience

This workshop is designed specifically for finance and accounting professionals who use Excel in their day to day roles.

Participants will gain the maximum benefit from this course if they are already competent spreadsheets users. The course is designed for people who use Excel on a regular basis, and are comfortable with using its tools and functions.

Please bring a USB.

Please note enrolments close 2 working days prior to the workshop date. Please enrol at least 3 working days prior to the workshop to ensure any dietary and special requirements can be accommodated.

Looking for a customised learning solution for your organisation or team? Contact us today to discuss in-house learning options.