Browse Courses

Home > Financial Planning Courses > Advanced Financial Modelling In Excel
Want more information about this course? Request a Free Info Pack

Advanced Financial Modelling In Excel

In the ever-changing business environment, being able to accurately model and forecast the volatile economic inputs is a critical skill for business professionals. Build on your existing modelling skills and apply new techniques to better analyse financial data, predict revenues and cost and assess risks to justify business decisions in the most time-efficient and effective way.

Each participant on this course receives a copy of the book Using Excel for Business Analysis. Got several participants or want to book on multiple courses? Consider making a bulk booking and save.

Prerequisites

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users. It is designed for users who do use Excel on a regular basis, and are comfortable with using its tools and functions.

At minimum, it is assumed that participants will know how to:

  • Navigate confidently in Excel
  • Use absolute cell references (e.g. =$A$1)
  • Create and use Excel functions on a regular basis

If you are unsure about your level of Excel, you might also consider joining the online Excel School prior to coming on the course. This course builds on students' advanced knowledge of Excel tools and functions and incorporates these into complex and dynamic financial models.

Software used

Public courses are run in a computer laboratory with workstations provided. Participants may bring their own laptops if they prefer. The course is demonstrated using Microsoft Excel 2010, but students can use Excel 2003 or 2007 if they prefer. Course materials contain instructions for all versions, and Excel 2011 for Mac users are also catered for.

Learning Objectives

Short-cuts and techniques to build a financial model in less time

Become super-efficient in Excel through intensive use of keyboard shortcuts and best practices to efficiently build an effective and robust model.

Experience designing solutions to real-world examples

Create a financial model based on real-world financial modelling challenges. Incorporate current economic uncertainties into the model in order to take more calculated risks and make more informed business decisions.

Analytical thought on fluctuating economic assumptions in financial modelling

Identify correct economic inputs to model and deal with fluctuations. Quantify uncertainty in forecasting models with stress-testing, what-if analysis and risk analysis techniques.

Knowledge on how to deal with uncertainty and risk in financial modelling

Explore the several different ways to perform scenario and sensitivity analysis in Excel and learn to model in detail the complex but most commonly used methods of showing scenarios using a combination of formulas and drop-down boxes.

In addition, you will learn how to:

  • Streamline model building by applying best practice functions, tools and techniques
  • Apply commonly used formulas in new and different ways
  • Deal with risk and uncertainty by creating scenarios and performing sensitivity analysis
  • Build stress-tests, what-if and sensitivity analysis into your model by incorporating economic fluctuations
  • Minimise manual labour and automate common tasks by building macros into your models
  • Measure and interpret the performance of your company using Excel modelling
  • Master key steps to get the most out of Excel functionality to improve your financial modelling
  • Learn how to expediate and enhance the decision-making process
  • Maximise your analytical abilities by learning conventional and new approaches of modelling
  • Translate business concepts into logically structured models and formats
  • Select which advanced formulas are appropriate in different situations
  • Perform various advanced scenario modelling techniques

Course Content

Financial Modelling Theory and Best Practice

  • Overview of best practice financial modelling techniques
  • Model planning structure and steps in building your model
  • The technical, design, business and industry knowledge required for financial modelling
  • Selecting the formula or tool which is most appropriate for each modelling situation
  • Attributes of a good financial model
  • How long should a formula be? Deciding when to break a complex formula in several steps to maintain transparency and allowing ease of model auditing

Advanced Financial Modelling Techniques

  • Maintaining model integrity and avoiding Excel errors
  • Rebuilding an inherited model
  • Building self-balancing error checks
  • Correcting and suppressing errors
  • Fixing circular references

Advanced Financial Modelling Tools and Functions

  • Pros and cons of using array functions
  • Useful functions in financial modelling such as INDEX, MATCH, CHOOSE , OFFSET, LOOKUP and TRANSPOSE
  • Nesting formulas
  • Using aggregation and array functions to perform complex conditional calculations
  • Learn how to have Excel automate variance analysis using complex conditional formatting
  • Automate your financial model for the user with Macros. Recording and modifying VBA code and creating macro buttons
  • Pivot tables – summarise, dissect and analyse large amounts of data
  • Shortcuts for data manipulation in Excel

Bullet-proofing Your Model

  • Protect your model so that it can be used by anyone with a limited use of Excel
  • Locking and protecting cells
  • Restriction incorrect data entry with data validations
  • Creating error validation messages
  • Use of form controls such as spin buttons and combo boxes

Stress testing to deal with uncertainty and risk in Financial Modelling

  • Economic inputs to model and modelling fluctuations in external factors
  • Use of stress testing to validate your underlying assumptions and risk calculations
  • Advanced What-if analysis
  • Importance of assumptions when assessing risk

Scenarios and sensitivity analysis

Learn different methods of what-if and scenario analysis in Excel using:

  • Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables
  • Using the Scenario Manager
  • Manual scenario building
    • In-cell drop-down boxes
    • Combo-box drop down boxes

Practical Exercise: At each step during the course, participants build and practice each formula, tool and technique. Record your own macro with buttons, build a pivot table, and create drop-down boxes.

Practical Financial Modelling

Building on the tools and techniques covered, we will learn to apply advanced modelling skills to build complex, yet robust and user-friendly financial models.

Tiering Tables

One of the more complex and widely used calculations in financial modelling. Application of two different methods of calculations; a simple and progressive table.

Financial Model Critque

Participants will be given a financial model to critique and identify why it does not follow financial modelling best practice.

Factory Rollout Model

Working on a startup funding model from start to finish we will evaluate input assumptions, calculate revenue and costs of production to calculate cash requirements. From this, we build a full set of financial statement with particular emphasis on linking the profit & loss statement, cash flow statement and balance sheet together.

Practical Exercises: Utilising the tools and techniques covered in the first part of the day, participants will build their own financial model case study, performing stress testing, scenario and sensitivity analysis on their model.

Comments from past students

"Great course - well worth the time. Wish I'd done this course years ago!"

"The course was a good mix of theory & practice, with a good coverage of topics. The presenter had a good knowledge, was easy to understand and follow and the venue was great."

"This was a jam-packed course with practical content and very well-presented."

"Evaluating this course is one of the few times I could tick "strongly agree" on all the boxes!"

For More information, or to enrol simply request a Free information pack or enquire below.




Home | About Us | News | Privacy Policy | Terms of Use | Sitemap | List Your Courses | Search Courses | Online Courses