Financial Modelling with VBA Course

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.  This course 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.

This course delivers:

  • 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; and
  • Working with scenarios to loop and goal seek information so reports are updated instantly.

Tools Used

  • Creating dynamic VBA with best practice sheet referencing and range referencing.
  • Worksheet level VBA to run code when worksheet is activated.
  • Cell level VBA to run your code when a cell or data validation is changed.
  • Looping through sheets to consolidate dynamic ranges.
  • Looping through external workbooks to consolidate cost centre reports.
  • Looping through external workbooks and extracting only specific cost centres.
  • 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.
  • Charting 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.

Practical Component

Students will first learn what makes up VBA best practice.  How to refer to ranges and worksheets so changing sheet names will not affect the smooth operation of processes.  Participants 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 students will generate their 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.

 

Who Should Attend

Professionals who prepare management and financial of any kind who have mastered the art of financial modelling and want to take their VBA knowledge to the next level.  These techniques will be helpful for finance professionals who regularly update reports from multiple sources on a regular basis.  Analysts who summarise datasets and want to take a great deal of the manual manipulation out of the process.  Accountants who want to learn the most effective and efficient way to code.
 

The course material includes extensive use of VBA and participants will gain the maximum benefit from this course if they are already competent spreadsheets users.  The course is designed for users who use Excel on a regular basis, and are comfortable with using its tools and functions.  Knowing how to create fully automated reporting suites will be highly beneficial in the workplace and this course will allow participants to walk away with a wealth of VBA practical knowledge.  All VBA and files will be supplied to participants.
 
It would be most helpful if participants bring a USB to class so they can take the file with them upon completion.