Advanced Financial Modelling Course

This course is designed for Excel users to vastly increase their financial modelling and analytical skills, particularly in the areas of accounting, finance and management. During this workshop, participants will be building a financial forecasting model which is scalable, flexible and reusable. We will use assumptions and historical data to produce a 5-year forecasting model which uses clear and straight forward formula to derive a forecast of revenue and expenses for each region/year. The model will incorporate loan repayment schedules, allocations of the costs to regional and head offices. A VBA technique will be used in conjunction with an Excel Slicer to create a fully interactive and consolidated P&L and cash flow.

This course delivers:
  • Practical guidance on how to layout and structure a set of cost centre reports to create seamless consolidation;
  • Create a sensitivity page which allows for multiple business cases and one off events which will flow through to the P&L;
  • Create a fully allocated loan repayment schedule with principal and interest;
  • Generate a 10 year fixed asset register which allows the changing of asset classes and useful lives;
  • The creation of a slicer which summarises the report by cost centre.
  • The automation of the summary sheet using VBA with an Onchange event and an Excel slicer.   All VBA coding will be provided so no prior knowledge necessary.
  • Working with GOAL SEEK to generate predetermined ROA for the model, create some VBA to automate the production of the results.

Tools Used:

  • Data Summary using SUMIFS Formula
  • OFFSET Formula
  • INDEX & MATCH Formula
  • SUMPRODUCT formula
  • LOOKUP, VLOOKUP formula
  • IPMT & PPMT formula
  • Nested IF with AND statements
  • HYPERLINK formula
  • COLUMN & ROW formula
  • RIGHT, CELL & SEARCH formula
  • Adding straight line depreciation using SLN formula
  • Creating Slicers for consolidation with VBA
  • INDIRECT & Address formula to summarise data which is isolated in the Slicer

Practical Component

Participants will create the sensitivities on the assumptions sheet, which will involve the generation of one off costs and creating a range of business case scenarios.  A depreciation schedule will be generated which will be able to change with changing drivers (useful lives, asset classes).  A fully allocated loan schedule will be created with generation of interest and principle repayments which are allocated to the various departments.  A P&L template will be calculated from scratch and the worksheet will act as a template to generate up to 20 different reports.  An Excel Slicer will be created to act as the consolidated front end of the report and we will add VBA to generate a change event which is linked to the Slicer.

Who Should Attend

Professionals who are comfortable with financial modelling and want to learn some advanced consolidation and allocation techniques.   These techniques will be helpful for finance professionals who regularly generate financial reports from scratch.  This course is for accountants and  analysts who summarise datasets and want to create reports which take the manual manipulation out of the process. 

The course material includes a 60+ page user document which takes you through the theory and practical exercises.  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.  The course offers students the opportunity to create a robust, best practice financial model which is pliable and adds some advanced financial modelling techniques to their toolset.

It would be most helpful if participants bring a USB to class so they can take the file with them upon completion.