Presentation
Course details
Presentation of attendees

• Financial modelling overview: Objectives and typical applications
• Skills needed for financial modelling
• Implementing models using Excel: Key advantages and disadvantages
• Data sets versus models: Discussion and comparison
• Model structures: organising data versus calculations
• - An overview of generic best practice approaches
• Key process stages and best practice principles at each stage
• Using sensitivity analysis at each stage of the process
• Hands-on exercises:
  • Using sensitivity techniques to create and test models with complex formulae (with application to tax calculations, royalty schemes and debt repayment schedules)
  • Implementing automatic sensitivity analysis in dynamic models (incl. DataTables)

• The relationship between sensitivity analysis, risk analysis and optimisation modelling
• Hands-on exercises:
  • Conducting breakeven analysis
  • Portfolio analysis and tax optimisation
• Recap of core Excel functions (e.g. IF, MIN,MAX, NPV, IRR, PMT, SUMPRODUCT) and selected Excel shortcuts (as required)
• Other important functions for financial modelling: An overview of lookup, information, logical, text, date and statistics functions and some of their key uses
• Hands-on exercise:
  • Creating scenarios as an extension of sensitivity techniques (incl. basic lookup functions)

• Recap of basic elements of financial statements
• Differences between historical statements and forecasting
• Key modelling issues requiring consideration at the model planning stage
• Core steps required in forecasting financial statements (an overview)
• Hands-on exercise:
  • Example of building a model which updates as actuals are included
  • First steps in building a financial statement model: revenues to EBITDA

• Hands-on exercise (continued)
  • Building to EBIT; capex and depreciation schedules
  • Use of corkscrew structures for balance sheet item
  • Calculations to net income (interest and tax calculations) [as far as possible: pre-financial close]
• Further Q&A, Discussion, Day 1 Close

• Hands-on exercises:
  • Completion of financial statement model and financial close
  • Calculation of all IS/BS/CFS itemse.g. inventory, payables, receivables (without closing final cash flow and balance)
  • Closure of financing (incl. model testing and errorchecking)
• Circular references in models: how they arise and how to deal with them
  • Intentional/unintentional; divergent/convergent/floating
  • Removal/algebraic manipulation/iterative methods
• Hands-on exercise:
  • Use of manual iterative method to break a circular reference
  • Using VBA macros to break circular references (demo)

• Techniques to ensure integration between statements (balancing the balance sheet and reconciling with the cash flow statement), and error-checking
• Hands-on exercises:
  • Building ratios for reporting (and sense-checking); profitability, liquidity etc.
  • Sensitivity and scenario analysis
• Discussion of possible generalities (e.g. tax, depreciation, financing, enhancements for specialized accounting topics or line items etc.); hands-on exercises as appropriate and time-permitting

• Introduction to capital asset pricing model and its implications
• Analysis of individual components (free return, market return, beta etc.)
• Formulae for valuing post-forecast (terminal cash flows); contexts and dangers
• Hands-on exercise:
  • Calculation of correlations and standard deviations of returns
  • Use of regression techniques
  • Cash flow valuation for forecasted cash flows
  • Calculation of NPV, IRR, payback periods

• Techniques to implement scenarios in time-based models
• Hands-on exercises:
  • Modelling scenarios for price, volume and cost decks
  • Sensitivity analysis for scenarios and cash flow

• Introduction to Excel Tables
• Introduction to PivotTables and their uses (including filters and slicers)
• Using Excel’s conditional aggregation and database functions
• Hands-on exercises:
  • 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.)

• Overview of techniques to manipulated data
• Hands-on exercises:
  • Separating fields using Text-to-columns menu (e.g. from internet download)
  • Cleaning data and finding errors using filters, conditional formatting, find/replace etc.; Inspecting for integrity, uniqueness and duplicates
  • Finding unique values and unique combinations
  • Checking for data consistency (e.g. with information and logical functions)
• Overview of lookup functions and best practices in function use and selection
• Using Lookup and text functions to manipulate data
  • Manipulating multi-currency databases
  • Combining date and text functions to create keys for combining data sets or finding matching items
  • Creatingcharts that can use multiple data sources, and with updating labels
  • Transposing and reversing time-series data
  • Creating variable-sized and dynamic ranges; variable summation/average/formulae ranges
  • Querying multi-language datasets

• Optimizingmodel layout (e.g. generic structure for input and calculation areas, and their respective benefits; multi-sheet models and related best practices; consolidation tools)
• Tools to consolidate data held in multiple worksheets (incl. lookup functions, consolidation tools and an demo/overview of VBA macros)
• Discussion of the use of lookup functions in larger models
• Hands-on exercises:
  • Creating and using multi-sheet models for scenarios and business planning
  • Use consolidation tools
  • Auditing a large model with multi-sheet lookup functions

Further uses of lookup functions in models

• Hands-on exercises:
  • Use of flag variables
  • Finding the occurrence of an event, or when a condition is met
  • Time-shifting: creating flexible start dates
• Introduction to VBA and its uses in resolving circular references, data manipulation and consolidation, (demo; hands-on work if time permits): uses in data manipulation and consolidation, implementing sensitivity analysis
• Introduction to risk modelling and Monte Carlos simulation (incl. demo) which require macros or other
• (Further) discussion of participants’ applications/issues, Q&A, course close

Closing Remarks and Certificate Distribution