Financial Modelling in Excel

This course is part of the Financial Modelling Series:

• Introduction to Financial Modelling in Excel
  
• Financial Modelling in Excel
• Advanced Financial Modelling in Excel

This course is designed for business professionals who need to explore the practical usage of advanced excel functions in a financial model. By creating your own user-friendly financial model, you will learn to translate business concepts into a structured format to identify weaknesses and predict future performance.

Prerequisites

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheets users. It is designed for users who do use (or will use) Excel on a semi-regular basis, and are comfortable with using its basic 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 simple formulas
• Link between workbooks

This course builds on students' existing knowledge of Excel tools and functions and incorporates these into a financial model.

Software used

Public courses are run in a computer laboratory.  Each participant is provided with their own workstation and a choice of Microsoft Excel 2003 or 2010.  Participants may bring their own laptops if they prefer.

Learning Objectives

During the course, participants will create their own financial model to take away and use for future reference. You will learn how to design and create a user-friendly model which can then be used by anyone with limited knowledge of excel.

You will learn how to:

• Build a financial model from scratch, or modify and improve an inherited model
• Select the most appropriate formula to achieve the desired outcome
• Identify common errors in modelling
• Mitigate errors by building in error checks
• Prevent incorrect use of your model by protecting worksheets
• Validate data entry by setting data entry parameters
• Create a navigation page to help users find their way around your model
• Develop drop-down boxes which enable a model to produce a series of results depending on scenario variable selected
• Mitigate liability by providing assumptions
• Write instructions for use
• Gain an in-depth understanding of how to build a business case
• Communicate the results of your model clearly and concisely

Course Content

Financial Modelling Theory & Best Practice

• Overview of Financial Modelling
• Model Design, planning and steps in building your model.
• Upgrading from Excel 2003 to 2007 or 2010
• The technical, design, business and industry knowledge required for financial modelling
• Purposes of financial models

Model Tools & Functions

• Which formula or tool is most appropriate in which modelling situation?

• Selecting the correct formula

• Adjusting formulas manually

• Named ranges

• Applying absolute and relative cell referencing and it’s importance in Financial Modelling

• Use of LOOKUP functions, aggregation functions such as SUMIFS, COUNTIFS and nested formulas within a financial model and others

• How to model compounding inflation

• Calculating customer acquisition numbers from the potential pool of customers

• Modelling market penetration in a business case

Financial Functions

• IRR (Internal Rate of Return)
• NPV (Net Present Value)
• How to calculate a payback period

Modelling Techniques

• What makes a good model?
• Best practice in financial modelling
• Attributes of a good model such as user-friendly and structural features

Practical Exercise: Build an individual business case using a range of financial functions and tools utilising best practice financial modelling techniques. 

Error Prevention

• Modelling techniques and strategies to reduce potential for error

• Identifying and correcting common errors

• Avoiding error displays in formulas

• Creating in-built, self-balancing error checks and error alerts

Other Useful Tools

• Hiding sections of the model
• Using Goal Seek within Financial Models
• Macros and Financial Modelling
• Pivot tables and their use in Financial Modelling

Rebuilding an inherited model

• Formula auditing
• "Sense-checking" methodology
• Identifying formula error
• Dealing with links and the potential errors they can cause

Bullet-proofing your model

• Protect data by locking cells 
• Password Protection
• Restricting incorrect data entry with data validations
• Using worksheet protection to prevent entry
• Protecting your file using “Read Only” and password protection
• Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs

Making a model user-friendly

• Formatting
• Inserting Navigation buttons
• Hyperlinks

Scenarios and Sensitivity Analysis

• Manual sensitivity analysis
• Creating drop-down switches for scenario selection
• Adjusting inputs variables to impact outcomes
• What-if analysis
• Stress-testing

Practical Exercise: Create a best, base and worst case scenario on your model. Select from the drop-down box and watch the results change

Assumption Documentation

• Why document assumptions?
• Linking to source referencing
• Importance of assumptions when assessing risk
• Mitigate liability by including appropriate caveats and key assumptions

Presentation of Model Output

• Summarising results and display of findings
• Communicate the results of your model clearly and concisely whilst getting the key message across to the audience
• Summarising key assumptions, documentation and source referencing
• Writing operation instructions
• Summarising model data into a presentation

Comments from past students

"Very well-structured course with great explanations that link back to the business. Practical!"

"Good practical information on business modelling and forecasting. Very applicable for day to day use in business"

"Danielle's knowledge was excellent"

"Content was comprehensive - I will recommend to others"

"Intuitive and interesting"

"Explanations were clear and thorough"

"It was a very practical course and I now feel confident with Excel and Financial Modelling"