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.

You will:

• Discover how to measure and interpret the performance of your company using Excel modelling
• Create your own effective and reliable model and identify weaknesses and predict future performance
• Gain an in-depth understanding of how to build a business case
• Master key steps to get the most out of Excel functionality to improve your financial modelling
• Learn how to 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

Prerequisites

Students are expected to be reasonably regular Excel users, and should be comfortable with simple formulas and linking between workbooks.

Software used

Public courses are run in a computer laboratory and workstations are provided. Course materials are written with both Excel 2007 and 2003 instructions.  The course is demonstrated mostly using Microsoft Excel 2007 and participants can choose which version of Excel they prefer. If participants are more comfortable using their own laptops, they are most welcome to bring their own equipment. PCs are used during this course, so Apple Macintosh users may prefer to bring their own laptops.

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
• 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
• Create 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
• Present findings in a concise and meaningful way

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

Course Content

1. Model Tools

1.1 Choosing between Excel and Access

1.2 General Functions

• Use of LOOKUP functions, SUMIF, COUNTIF and nested formulas within a financial model
• How to model compounding inflation
• Calculating customer acquisition numbers from the potential pool of customers
• Modelling market penetration in a business case

1.3 Financial Functions

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

1.4 Other Useful Tools

• Hiding sections of the model
• Avoiding error displays in formulas
• Creating in-built error checks
• Using Goal Seek within Financial Models
• Macros and Financial Modelling
  

2. Modelling Techniques

2.1 Rebuilding an inherited model

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

2.2 Bullet-proofing your model

• Protect data by locking cells
• Protecting a worksheet
• Password Protection
• Building error alert formulas
• Restricting incorrect data entry with data validations

2.3 Making a model user-friendly

• Formatting
• Inserting Navigation buttons
• Hyperlinks

3. Final Model Results

3.1 Scenarios and Sensitivity Analysis

• Manual sensitivity analysis
• Creating drop-down switches for scenario selection

3.2 Presentation of Model Output

• Summarising results and display of findings
• How to create dynamic graphs

3.4 The Fine Print

• Documentation and source referencing
• Mitigate liability by including appropriate caveats and key assumptions
• Writing operation instructions

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"