Excel Modeller

An introduction to the fundamentals of best practice modelling and a framework that supports the development of any model.

What is a model and why do we model - consider what a model is, how it can be defined and then a range of reasons why we might build one.

Example of financial, operational and strategic models - we define these three categories of models and then give examples of each. Emphasises why modelling is such a valuable skill and relevant to many different scenarios.

What is best practice modelling & Why is it needed - a rationale for developing modelling skills and how they can help you, your colleagues and your organisation.

The Model Development Framework - invaluable framework of stages to structure your end to end development of a model. You’ll appreciate why immediately jumping into Excel and starting your model build is not the right approach.

Why a framework is needed - a range of reasons why using the framework will help you develop a model successfully.

A comprehensive look at best practice rules, guidance and techniques for scoping, specifying, designing and building models.

Top 10 Rules of Modelling - comprehensive look at some of the most important rules, guidelines and advice in the physical development of a model.

Scoping and Specification - a slightly more in-depth look at what the stages of the model development involve and why they’re so important.

Techniques to design a model before build - practical ways in which you can organise your thoughts and plan the structure of your model before you dive into Excel.

Excel Dependency Trees & Calculation Chains - advanced knowledge of how Excel calculates and how it can help avoid creating a slow, clunky model. This is excellent value-add learning that you won’t find on many courses and after which you’ll abandon the use of certain Excel functions!

Modelling approach comparison - a look at how your approach can result in a much more concise, easier to build, easier to manage, model. This module emphasises why consideration of our design before build is so important.

Overview of functions used in modelling - a brief mention of which functions are most relevant to modelling and the context in which they are often used. This can help prioritise your learning to focus on the functions that matter the most.

The important role played by the Timeline and time-related components in models.

Why the Timeline is fundamental to the model - how the Timeline sets the structure of the model, defines its calculations and is the main consistent element throughout the model.

How to create the Timeline - theory and demonstration of how and where to create the Timeline, what to include in it and how to control it.

How to use the Timeline - ways to fully utilise the Timeline to simplify calculations.

What are flags & How they help - explanation of what flags are and how they help reduce the number and complexity of calculations in a model, making it more transparent.

Tips and techniques for creating effective and engaging outputs in models.

Designing outputs first - advantages of designing and mocking up outputs to ensure the model achieves the desired result.

Time period summaries & flow of data - how to structure and summarise your outputs in the right way, and things to avoid.

Using dummy figures in output construction - how utilising dummy figures helps in the creation of outputs.

A comprehensive look at how to develop and structure calculations, what to avoid, and how to make them more transparent and efficient.

Calculation Blocks - explanation of what Calculation Blocks are and how they’re created and used in the model.

Formula construction dos & don'ts - tips and advice to help produce calculations that are more transparent and are less prone to causing errors or confusion.

Corkscrew Calculations - how to use a special calculation technique to incorporate opening and closing balances into your workings.

Helper Cells - their benefits & when to use them - a technique to simplify calculations, their benefits and when to use them.

A guide to the structure and formatting of inputs to aid the build and usability of the model.

Using dummy inputs in model development - a recap on the use of dummy figures in your model, but in the context of inputs.

How & Where to structure your inputs - a look at how to create, organise and format your inputs, followed by the process of linking them through to the rest of the model.

How to integrate checks effectively into the model to reduce the risk of errors.

Why checks are needed - a look at why checks are vital to a model, what they can do for the user, and the risks of their omission.

Incorporating checks into a worksheet - how and where checks are created, located and formatted.

Creating a Check Sheet & linking throughout the model - process and technique to link up all the checks in the model to ensure their status is always visible, wherever you are in the model.

A comprehensive look at tools and techniques to test models, to help identify the existence of errors and reduce risk.

Tools to help test your model - demonstration of two add-ins that can be used for diagnostic and testing purposes.

Overview of techniques to test or review a model - a high-level look and explanation of testing techniques, along with practical suggestions as to how to carry them out.

Common modelling errors & how to identify them - a selection of the errors often made in modelling that you can initially check for when commencing the review of your model.

Ways to leverage Excel tools and other modelling techniques and analysis to get the most out of a model and deliver maximum value to Management.

Documenting a model - ways to document, why you should, and the risks of inadequate documentation.

Version control in modelling - a technique to track the details and impact of changes you make to your model and how this can help when presenting to Management.

DCF Analysis & Investment Appraisal - methods to calculate a range of investment metrics in your model.

Goal Seek - an Excel tool used to backsolve the required input or assumption needed to achieve a desried output or result in a model.

Data Tables - an Excel tool used to sensitise either one or two inputs in a model, showing a specific output under each scenario, that can aid Management’s evaluation and decision-making.

Scenario Functionality - a technique for building scenarios into a model that can make it quicker and easier to switch between, for instance, Low, Base and High Cases.

Theory and Demo-based recaps of key knowledge relevant to the Excel Modeller course. Note, this content is taken from the Excel Analyst course and is video-only. The Excel Analyst course includes exercises and a much broader array of content than below.

Navigational & Action shortcuts

Anchoring in formula construction

Navigational Add-in: Arixcel

Essential Functions - INDEX, MATCH, INDEX & MATCH, TRUE & FALSE, IF, AND & OR

The first third of the course is predominantly theory-based and supported by challenging quizzes. Thereafter, theory is taught alongside practical model build tasks and quizzes. By the end of the course the participant will have developed from scratch a relatively sophisticated model that features elements of almost all the theory content.