Excel Modelling and Analysis

Module A of the CinFM

 

Download Course Booklet
null

Course Description

In this 3-day course, we cover the key concepts and methods required to plan, build and use financial models for decision support in business and finance.

Many Excel models are composed of building blocks that are quite common across many modelling applications. We cover these frequent logic structures and building blocks and the Excel formula to put these into place. In doing so, we cover a wide set of Excel functions, including Lookup, Logical, Date, Information, Text, as well as Excel short-cuts.

We learn key financial and economic concepts. These include methods to evaluate projects, the time value of money, discounting, return calculations, interest calculations, net present value, project evaluation, and others.

We explore the meaning of good decisions and the process requirements to generate them. We discuss the context of models, including methods to analyse economic context and business strategy, and to account for these appropriately within models.

We define the key questions that need to be addressed when planning a model. We cover tools and processes to map overall objectives into modelling requirements.

We cover the topic of sensitivity analysis and scenario analysis, and related areas. We cover a wide set of topics in modelling best practices to ensure that participants are aware and capable of building transparent, error-free and useful models.

A wide set of hands-on exercises are conducted as the course progresses. These are used not only to develop a robust knowledge of Excel, but also to reinforce the core concepts in economics, decision-making and best practices

This course is suitable for all levels, enabling early stage modellers to gain a wide set of skills, and more advanced modellers to extend and consolidate their knowledge and perspective.

Learning Objectives

 

  • Learn the common building blocks that cover most frequent Excel modelling requirements
  • Enhance knowledge of a wide range of Excel functions
  • Understand the elements required to make good decisions
  • Learn how to analyse decision context to ensure models address all relevant areas
  • Enhance skills in translating decision objectives into models
  • Learn core concepts in economics and finance
  • Understand key measures to evaluate projects, investments, and business performance
  • Develop skills in sensitivity and scenario analysis
  • Gain a better understanding of best practices in model design, building and use
  • Learn key aspects of effective graphical communication
  • Gain practical hands-on experience in building Excel model components and models
  • Realize one’s potential to work efficiently and effectively, and provide optimal decision support to your organisation

Key Topics Overview

 

  • Models as decision tools · The meaning of decision quality · Optimising decision support
  • Excel essentials · Excel functions · Short-cuts
  • Common logic and building blocks
  • Model planning · Defining objectives · Mapping objectives to model design
  • Strategy analysis · Contextual analysis · Performance measures
  • Economics of project evaluation · Break-even analysis · Payback periods · Time-value of money · Discounted cash flow · Net present value · Internal rate of return · Interest rates · Compounding · Lease/buy decisions · Amortisation and mortgages
  • Sensitivity analysis · Scenario analysis · Data Tables · GoalSeek · Optimisation
  • Lookup functions · Logical and Information functions · Date and Text functions
  • Financial functions · Statistics functions · Array functions
  • Modelling best practices · Creating flexible and transparent models · Circular references · Named ranges · Optimising model layout
  • Principles of effective visual communication · Results presentation · Graphs and charts

 Course Agenda in Detail

Day 1: Modelling Principles, Planning and Structures

1.1 Key Modelling Considerations, Common Structures and Components

  • Course overview and introduction
  • Modelling overview: Definition, scope, and skills required
  • Introduction to “decision quality”
  • Model context, scope, objectives and requirements planning
    • Assessing a business’ environment, economics, and strategy
    • Ratio analysis and performance metrics
    • Supporting high quality decisions using models: Key issues to address
  • Mapping of decision structures, logic flow and relationships: Processes and tools
    • Decision trees, influence diagrams and logic reversal
    • Sensitivity analysis thinking and report-based planning
    • Data-driven modelling
  • Core components: Overview of frequent logic structures and model building blocks

1.2 Hands-on Exercises with Selected Modelling Components

  • Excel essentials: Structure, formulas, functions, and best practices, examples
  • Hands-on exercises: Core components and Excel functions (I)
    • Calculating historical ratios and growth rates
    • Converting between time periods of different lengths (e.g. average and compound growth)
    • Forecasting using growth rates (e.g. revenues, costs)
    • Forecasting with inflation; nominal and real terms
    • Forecasting using ratios and scaling factors (e.g. balance sheet items, stock days)
    • Linear forecasting of fixed and variable costs

1.3 Sensitivity Analysis and Results Presentation

  • Sensitivity analysis, its variations and uses (e.g. absolute and relative sensitivities, single and multi-variable, role in model testing, creating scenarios, variance analysis, GoalSeek, introduction to optimisation, overview of advanced topics)
  • Hands-on exercises:
    • Creating sensitivity analysis in one and two variables
    • Building relative-form sensitivities
  • Principles of effective visual communication
  • Hands-on exercises:
    • Creation of graphs and charts

1.4 Hands-on Exercises with Selected Modelling Components

  • Hands-on exercises: Core components and Excel functions (II)
    • Comparison and logical calculations
    • Conditional calculations
  • Hands-on exercise:
    • Using sensitivities to develop and test complex model formulae (e.g. multi-rate taxation and financing structures)
    • Calculating break-even points
  • Day 1 Recap, Q&A, Discussion, Close

Day 2: Further Model Building Blocks, Excel Functions and Exercises

2.1 Hands-on Exercises with Selected Modelling Components

  • Recap of Day 1, including core building blocks required
  • Hands-on exercises: Core components and Excel functions (III)
    • Linear allocations across categories
    • Non-linear allocation to categories (for tax, financing and other calculations)
    • Time transposition and triangles (e.g. for depreciation)
  • Hands-on exercises: Core components and Excel functions (IV)
    • Aggregation of range data
    • Use of distributed data sets (multi-sheet approaches)

2.2 Lookup Functions and their Applications

  • Overview of functions and uses
  • Hands-on exercises: Core components and Excel functions (VI)
    • Building scenario and variance analysis
    • Capturing model events and linking items
    • Dynamic ranges and flexible worksheet structures
    • Time-shifting
    • Distributed worksheet-based scenario modelling and selection
  • Best practices in function selection (e.g. reasons to avoid using V- and HLOOKUP and better alternatives, use of Volatile functions etc.)
  • Introduction to approaches to link model components (e.g. corkscrew structures, separate reference or input/output ranges, Lookup functions).

2.3 Hands-on Exercises: Integrating Several Building Blocks into a Realistic Model

  • Introduction and description of modelling exercise
  • Excel short-cuts for creating formulae, model calculations and formatting
  • Hands-on exercise:
    • Creation of model components
    • Integration and linking of components
    • Sensitivity analysis and testing

Model Auditing, Best Practices, and Results Presentation

  • Auditing and troubleshooting models: key approaches and tools
  • Hands-on exercises:
    • Model auditing and dependency/precedence tracing
    • Formula evaluation and correction
    • Short-cuts
  • Introduction to core themes in best practices
    • Planning the optimum level of granularity and detail for the time axis
    • Model layout and flow, modularity and linking
    • Workbook and data structures, centralized/decentralized approaches
    • Transparency; individual calculation steps, formatting and labelling
    • Use of named ranges
  • Review of best practices in context of completed hands-on exercise
  • Day 2 Recap, Q&A, Discussion, Close

Day 3: Further Functions and Financial Calculations

3.1 Date, Logical and Array Functions and Their Applications

  • Overview of Date, Logical, array functions, and matrix calculations
  • Hands-on exercises e.g.
    • Creation of flexible time axis
    • Calculation of specific types of dates (last Friday in month)
    • Quarters, identifying and labelling months; date formats
    • Matrix calculations for cost allocation and portfolio risk and returns analysis

3.2 Interest Rates and Related Calculations

  • Interest rates, and differences to standard growth calculations
    • Interest rates, compounding types and accrual
    • Interactions of interest compounding dates with model time periods; applicable interest rates
    • Methods to calculate ending balances: advantages, disadvantages of each
    • Introduction to management of circular references
  • Hands-on exercises: Core components and Excel functions (V)
    • Calculation of interest rates
    • Examples of model with circularities

3.3: Financial Functions and Their Applications

  • Discounting and internal rate of return; meaning, comparison and uses
  • Key financial functions
  • Hands-on exercises
    • Loan amortization and mortgage calculations (various uses, variations and pitfalls)
    • Discounting and internal rate of return calculations and comparisons
    • Project evaluation
    • Analysis of a lease/buy-decision

3.4 Information and Text Functions and Their Applications

  • Overview of Information and Text functions
  • Hands-on exercises e.g.
    • Checking data integrity and consistency
    • Splitting and combining data sets
    • Models that update with actuals or as additional data is available
  • Day 3 Recap, Q&A, Discussion, Course Close