2nd Financial Modelling Using Excel

Course Schedule

Day 1 - Tuesday 03 May, 2016
Introduction and Core Principles

Opening Session
Presentation
Course details
Presentation of attendees

Session 1.1: Introduction to Financial Modelling Best and Practice Principles

Financial modelling: objectives, typical applications, and skills needed

  • Core issues in model design (with exercises for discussion)
    • Data sets versus models, importance of layout and labels
    • Model structures: organising data versus calculations; approaches to optimize model layout
    • Other best practices in formatting, layout, and model flow
    • Balancing flexibility with transparency and complexity
    • Selection of the appropriate Excel functions (overview, and key issues)
    • Selected Excel short-cuts for formatting and creation of formulae
    • Key process stages and best practice principles at each stage
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Suggest and implement improvements to an existing model
Tea Break & Networking
Session 1.2: Implementing Sensitivity Analysis

The importance and nature of sensitivity analysis at each stage of the modelling process

  • The relationship between sensitivity analysis, risk analysis and optimisation modelling
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Using sensitivity techniques to create and test models with complex formulae
    • Implementing automatic sensitivity analysis in dynamic models (incl. DataTables)
Lunch Break & Networking
Session 1.3: Implementing Scenario Analysis
  • The relationship between sensitivity and scenario analysis
  • Introduction to optimisation modelling (incl. GoalSeek and Solver)
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Creating scenarios as an extension of sensitivity techniques (incl. basic lookup functions)
    • Conducting breakeven analysis
    • Portfolio analysis/optimisation
Tea Break & Networking
Session 1.4: Excel Function Review
  • Overview of main Excel function categories
    • Core arithmetic and logic, mathematical, statistical and financial functions
    • Date, text, information, and array functions
    • Lookup functions
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Use of IF, AND, COUNT, NPV, IRR, PMT, PPMT, and VDB functions
    • Selected other functions (SUMIF, IFERROR, TRANSPOSE…)
  • Further Q&A, Discussion, Day 1 Close
Day 2 - Wednesday 04 May, 2016
The Use and Analysis of Data Sets within Modelling Applications

Session 2.1: Manipulating and Cleaning Data Sets I

  • Overview of techniques to clean data
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Separating fields using text-to-columns
    • Finding errors using filters
    • Using find/replace
    • Inspecting for integrity, and finding unique values and unique combinations
    • Use of conditional formatting
Tea Break & Networking
Session 2.2: Manipulating and Cleaning Data Sets II

Use of functions to manipulate and clean data(combining lookup, text,
information, date and other functions)

  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Manipulating data in a multi-currency database into common terms
    • Combining date and text functions to create keys for combining data sets or finding matching items
    • Splitting and recombining items in data sets; consolidation of data sets into one
Lunch Break & Networking
Session 2.3: Techniques to Analyze Data Sets
  • Introduction to Excel Tables
  • Using Excel’s conditional aggregation and database functions
  • Introduction to PivotTables and their uses (including filters and slicers)
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Creating month, quarterly and year keys from a date
    • Using SUMIFS and other functions to analyze data and create queries
    • Using database functions for complex queries e.g. where Excel functions do not exist or are cumbersome
    • Using PivotTables
  • Discussion of the +/s- of each (linking to data versus labels, fixed versus flexible reports etc.)
Tea Break & Networking
Session 2.4: Further Modelling Applications of Lookup Functions
  • Recap of lookup functions and best practices in their use (e.g. why to not use VLOOKUPs)
  • Use of OFFSET and INDIRECT functions
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Time-shifting: creating flexible start dates
    • Use of flag variables
    • Finding the occurrence of an event, or when a condition is met
    • Creating flexible formulae that refer to dynamic ranges
    • Creating models where data sets can be inserted/deleted easily
  • Further Q&A, Discussion, Day 2 Close
Day 3 - Thursday 05 May, 2016
Financial Statement Modelling and Cash Flow Valuation

Session 3.1: Financial Statement Modelling: Overview of Key Concepts
and Issues

  • Introduction: Overview, uses, examples and meanings of each
  • Demonstrating financial statement linkages using basic transactions and accrual accounting
  • Differences between historical statements and forecasting
  • Core steps required in forecasting/modelling financial statements (an overview)
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Basic transactions and accrual accounting
Tea Break & Networking
Session 3.2: Financial Statement Modelling: Building Selected Elements
  • Key modelling issues requiring consideration at the model planning stage
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    •  Forecasting using growth rates
    •  Forecasting based on ratios
    •  Creating models which can be updated as actual figures are reported
    •  Building an income statement from revenue to the EBITDA level
    •  Building to EBIT; capex and depreciation schedules
Lunch Break & Networking
Session 3.3: Financial Statement Modelling: Review of Completed Model

Review of each completed step of the step-by-step process, and
issues arising at each stage

  • Dealing with circular references at financial close: approaches and methodology
  • Tips to reconcile the statements, balance the balance sheet, and detect errors
  • Review of key ratios
Tea Break & Networking
Session 3.4: Introduction to Cash Flow Valuation
  • Introduction to core principles:
    • Enterprise versus equity value
    • Discounting future cash flows
    • Cost of capital (capital asset pricing model)
    • Formulae for perpetuities; use and abuse of terminal value formulae
  • Hands-on exercises (using the “Explain, demonstrate, work along, work alone” process)
    • Implementation of terminal value formulae
    • Sensitivity analysis
  • Further Q&A, Discussion, Day 3 Close, Certificate Presentation,
Course Program
Time Topic
Day 1
08:00 to 08:30Registration & Introduction
Day 1-3
08:30 to 10:00Session One
10:00 to 10:15Tea Break & Networking
10:15 to 11:45Session Two
11:45 to 12:45Lunch Break & Networking
12:45 to 14:15Session Three
14:15 to 14:30Tea Break & Networking
14:30 to 14:00Session Four