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,