Financial Modelling Course

Course details

Introduction to Financial Modeling (4 Hours)

1.  Introduction to Financial Modeling (4 Hours)

1.1  Financial Modeling Introduction

• Introduction
• What is Financial Modeling?
• Model Design
• Overview of Scenario Methods

1.2  Fundamental Tools

• Upgrading to Excel 2007/10
• Building Error Checks
• Absolute & Relative Referencing
• Calculating Project Costs
• Common Error Values

1.3  Building a Financial Model

• Model Build - Inputs & Assumptions
• Model Build - Revenue
• Model Build - Expenses
• Model Build - P&L
• Model Build - Cash flow

1.4  Model Analysis Tools

• Assumptions Documentation Tools & Hiding
• Protecting a Model
• Charting Cash vs Profit
• Modeling Scenarios
• Dynamic Charts

Financial Modeling - Intermediate (8 Hours)

2.1 About Financial Modeling

• What is Financial Modeling?
• Model Design
• Skills needed for Financial Modelling
• Best Practice in Financial Modelling

2.2 Tools

• Excel Versions
• Conditional Formatting
• Hiding & Grouping
• Bullet-proofing your model
• Goal Seeking

2.3 Financial Modeling Techniques

• What Makes a Good Financial Model?
• Strategies for Reducing Errors
• Building Error Checks
• Overview of Scenario Methods

2.4 Essential Formulas

• Cell Referencing & Named Ranges
• Logical Functions & Nesting
• Aggregation Functions
• Using a VLOOKUP Function
• Using the FORECAST / TREND Function

2.5 Building a Business Case

• Building a Business Case (Parts 1 & 2)
• Business Case Financial Calculations
• Business Case Scenario Analysis

Advanced Financial Modeling (12 Hours

3.1 Theory and Best Practice

• Choosing between Excel versions
• Overview of best practice financial modeling techniques
• Common error values in financial modeling
• Correcting  and suppressing errors
• Locating and fixing circular references
• Allowing iterative calculations when circular references are maintained

3.2 Advanced Tools and Functions

• Selecting the formula or tool which is most appropriate for each modeling situation
• Nesting formulas
• Useful functions in financial modeling such as COLUMN, CHOOSE, OFFSET, LOOKUP and TRANSPOSE
• Using LOOKUP functions in tiered pricing models (volume break discounting)
• Pros and cons of using array functions and their use in financial modeling
• Use of form controls such as spin buttons and combo boxes

3.3 Advanced Techniques

• Rebuilding an inherited 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
• Comparison of different methods of modeling escalation and growth calculations
• Presenting model output using a chart on two different axes  and chart types
• Creating a Waterfall Chart
• Pivot tables - summaries, dissect and analyze large amounts of data
• Why pivot tables are not commonly uses in financial modeling, and other alternative tools
• Automate your financial model for the user with Macros.  Recording and modifying VBA code and creating macro buttons

3.4 Scenarios & Sensitivity Analysis

• Learn different methods of what-if, scenario and sensitivity analysis using:
• Show multiple scenario outcomes simultaneously with one and two-dimensional  Data Tables
• Learn how to automatically highlight selected scenarios using complex conditional formatting
• Using the Scenario Manager
• Manual scenario building
• In-cell drop-down boxes
• Combo-box drop down boxes

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.

3.5 Practical Financial Modeling

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