Financial Modelling in Excel Course

Financial Modelling Course

This course is designed for existing users of Excel to vastly increase their financial modelling and analytical skills, particularly in the areas of accounting, finance and management.  It is a practical full day hands‐on course designed to improve the Excel modelling and analytical skills of finance professionals for the forecast, analysis and presentation of business and financial information.  This program could easily be tailored to Optus requirements if required.

This course delivers:
  • A comprehensive overview of the theory of financial modelling in Excel;
  • A strongly defined technique that enhances model efficiency;
  • Key outcomes of the course are:
  • Identifiable improvements in the accuracy and productivity for attendees.
  • A thorough grounding in the tools and functions available in Excel including: INDEX, MATCH, LOOKUP,  SUMIFS, COUNT, and nested IF Statements;
  • Techniques to assist with spreadsheet auditing;
  • Analysis of poor Excel spread sheeting techniques, what to avoid;
  • Methods to generalise formulas for more dynamic and flexible calculations and analysis;
  • Practical guidance on the layout and structuring of workbooks for analysis and forecasting;
  • Frameworks to aid in understanding and assimilating information on complex formulas and formatting.
Key outcomes of the course are:
  • Identifiable improvements in the accuracy and productivity for attendees.

Modelling Component

The course will focus on the clear segregation of inputs, calculations and outputs, the fundamental building blocks of a robust Excel file.  The course takes participants through the methodology of a left to right spreadsheet design, with inputs pages on the left, calculations in the middle and outputs on the far right with data always flowing from left to right (like a book).  This clear delineation of data (Inputs feeding calculations feeding outputs) adds structure to workbooks making them easier to follow. 

The course looks at the 6 phases of financial model design from planning through to use as demonstrated by the process map on the right.  There is an acute focus on the continual review of a working model as changes to business logic and inputs can affect the outputs and in turn model integrity. 

This course delivers:
  • Data Summary using SUMIFS Formula.
  • INDEX & MATCH Formula.
  • SUMPRODUCT formula.
  • LOOKUP, VLOOKUP formula.
  • HYPERLINK formula.
  • RIGHT, CELL & SEARCH formula.
  • Creation of an Excel table.
  • Using the REPT formula to create a series of charts.
  • Creation of a calculated pivot table field.
  • Creation of a distrbution within a pivot table.
  • Creation of groupings within a pivot table.
  • Creation of a tool to link 5+ pivot tables

Practical Component

A set of review exercises are worked as the course goes through the setup, design and build of a financial model from scratch.  The model will incorporate sound modelling methodology with a separation of inputs, calculations and outputs.  Along with the formulas mentioned above the practical component will cover;

  • adding model integrity and avoiding errors through building check sheet.
  • nesting formulas (nested IF statements) - refresher;
  • using aggregation and SUMIFS, SUMPRODUCT to perform conditional calculations;
  • automating variance analysis using simple conditional formatting;
  • applying a slicer to data to trigger sensitivity;
  • shortcuts for everyday Excel usage
  • use of Pivots tables.

Who Should Attend

Anyone who uses Excel regularly for financial modelling, forecasting, valuation or analysis will find this course useful in their work. The course will assist in the interrogation of big data in a practical and repeatable manner.  Excel users who are already familiar with the product and building models and reports as part of their role, but who are concerned by errors, complexity or difficulties in implementing sensitivity analysis will find this course extremely beneficial.  The financial modelling course will instill a strongly defined methodology for model design that increases transparency and improves speed and reliability in their work.

The course materials include a 70+ page course text and a set of files which includes the sample versions and worked versions of the course files, as well as a number of reference documents, and a set of links to other useful resources including relevant parts of thesmallman.com.